MYSQL性能优化一直是个头痛的问题,目前大多都是直接把页面html静态页面或直接使用了缓存技术,下面我就mysql本身的性能优化来分享一下.
安装时优化参数配置提高服务性能
在Linux下安装Mysql采用默认配置安装的Mysql却未必是工作在最佳性能状态的,需要对其进行优化,一般认为在Mysql的配置文件中,下列系统参数是比较关键的:
(1)interactive_timeout:
服务器在关闭它前在一个交互连接上等待行动的秒数,一个交互的客户被定义为对 mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户,默认数值是28800,我把它改为7200.
(2)back_log:
要求 MySQL 能有的连接数量,当主要MySQL线程在一个很短时间内得到非常多的连接请求,这个参数就会起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程.
back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。每个操作系统在这个队列大小上都有它自己的限制,试图设定back_log高于操作系统的限制将是无效的。
在mysql中back_log的设置取决于操作系统,在linux下这个参数的值不能大于系统参数tcp_max_syn_backlog的值,通过以下命令可以查看tcp_max_syn_backlog的当前值:
cat/proc/sys/net/ipv4/tcp_max_syn_backlog
通过以下命令进行修改sysctl -w net.ipv4.tcp_max_syn_backlog=n
深入探讨一点:tcp/ip网络一般会有如下过程,从生成socket到bind端口在listen进而建立连接,具体到listen,就是listen(int fd, int backlog)的调用,这里backlog和mysql中back_log具有一定的关系,即操作系统backlog的要不小于mysql中back_log的值,在linux内核2.6.6中backlog在/include/net/tcp.h中由TCP_SYNQ_HSIZE变量定义.
观察一下主机进程列表,如果发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect| NULL | login | NULL 的待连接进程时,就有必要加大 back_log 的值了,默认数值是50,我把它改为500.
(3)max_connections:
允许的同时客户的数量,增加该值增加 mysqld 要求的文件描述符的数量,这个数字应该增加,否则,你将经常看到 Too many connections 错误,默认数值是100,我把它改为1024.
(4)key_buffer_size:
索引块是缓冲的并且被所有的线程共享,key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多,如果你使它太大,系统将开始换页并且真的变慢了,默认数值是8388600(8M),我的MySQL主机有2GB内存,所以我把它改为 402649088(400MB).
是否要增加这个参数的值主要看以下两点:
1、Key_reads/Key_read_requests:比例应该接近于0.01甚至越小越好
2、Key_writes/Key_write_requests:比例接近1较好
解决的办法当然是增加key_buffer_size的值啦,来实在的到控制台下面运行:
SET GLOBAL key_buffer_size=16777216;
这是设置全局的,如果只是当前会话的话,将GLOBAL换成SESSION即可.
(5)record_buffer:
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区,如果你做很多顺序扫描,你可能想要增加该值,默认数值是131072(128K),我把它改为16773120 (16M).
(6)sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区,增加这值加速ORDER BY或GROUP BY操作,默认数值是 2097144(2M),我把它改为 16777208 (16M).
(7)table_cache:
为所有线程打开表的数量,增加该值能增加mysqld要求的文件描述符的数量,MySQL对每个唯一打开的表需要2个文件描述符,默认数值是64,我把它改为512.
(8)thread_cache_size:
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可 以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。
(9)wait_timeout:
服务器在关闭它之前在一个连接上等待行动的秒数,默认数值是28800,我把它改为7200.
通过设置tmp_table_size选项来增加一张临时表的大小,例如做高级GROUP BY操作生成的临时表,如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果,建议尽量优化查询,要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于硬盘的MyISAM表.
- mysql> show global status like ‘created_tmp%‘;
-
- +——————————–+———+
-
- | Variable_name | Value |
-
- +———————————-+———+
-
- | Created_tmp_disk_tables | 21197 |
-
- | Created_tmp_files | 58 |
-
- | Created_tmp_tables | 1771587 |
-
- +——————————–+———–+
每次创建临时表，Created_tmp_tables增加,如果临时表大小超过tmp_table_size,则是在磁盘上创建临时表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配置是:
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables /
Created_tmp_tables * 100% ＝1.20%，应该相当好了
默认为16M,可调到64-256最佳,线程独占,太大可能内存不够I/O堵塞.
注:参数的调整可以通过修改 /etc/my.cnf 文件并重启 MySQL 实现,很明显的,根据服务器的硬件配置的不同,和mysql数据库负载的不同,参数的设置也是不同的,所以大家不要照搬上面的参数,而是要根据不同的硬件和负载修改为最适合自己的参数.
慢查询分析、优化索引和配置
索引及查询优化 索引的类型
Ø 普通索引:这是最基本的索引类型,没唯一性之类的限制.
Ø 唯一性索引:和普通索引基本相同,但所有的索引列值保持唯一性.
Ø 主键:主键是一种唯一索引,但必须指定为”PRIMARY KEY”.
Ø 全文索引:MYSQL从3.23.23开始支持全文索引和全文检索,在MYSQL中,全文索引的索引类型为FULLTEXT,全文索引可以在VARCHAR或者TEXT类型的列上创建.
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储,空间列类型的索引使用R-树,MEMORY表支持hash索引.
单列索引和多列索引(复合索引)
索引可以是单列索引,也可以是多列索引,对相关的列使用索引是提高SELECT操作性能的最佳途径之一.
多列索引:
MySQL可以为多个列创建索引,一个索引可以包括15个列,对于某些列类型,可以索引列的左前缀,列的顺序非常重要.
多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组,一般来说,即使是限制最严格的单列索引,它的限制能力也远远低于多列索引.
最左前缀:多列索引有一个特点,即最左前缀(Leftmost Prefixing),假如有一个多列索引为key(firstname lastname age),当搜索条件是以下各种列的组合和顺序时,MySQL将使用该多列索引:
- firstname，lastname，age
-
- firstname，lastname
-
- firstname
也就是说,相当于还建立了key(firstname lastname)和key(firstname).
索引主要用于下面的操作:
Ø 快速找出匹配一个WHERE子句的行.
Ø 删除行,当执行联接时,从其它表检索行.
Ø 对具体有索引的列key_col找出MAX()或MIN()值,由预处理器进行优化,检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下,MySQL为每个MIN()或MAX()表达式执行一次关键字查找,并用常数替换它,如果所有表达式替换为常量,查询立即返回,例如:
SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;
Ø 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表,如果所有关键字元素后面有DESC,关键字以倒序被读取.
Ø 在一些情况中,可以对一个查询进行优化以便不用查询数据行即可以检索值,如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列,为了更快,可以从索引树检索出值.
SELECT key_part3 FROM tb WHERE key_part1=1
有时MySQL不使用索引,即使有可用的索引,一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时,在这种情况下,表扫描可能会更快些,然而,如果此类查询使用LIMIT只搜索部分行,MySQL则使用索引,因为它可以更快地找到几行并在结果中返回,例如:
合理的建立索引的建议:
(1)越小的数据类型通常更好,越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快.
(2)简单的数据类型更好,整型数据比起字符,处理开销更小,因为字符串的比较更复杂,在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间,以及用整型数据类型存储IP地址.
(3)尽量避免NULL,应该指定列为NOT NULL,除非你想存储NULL,在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂,你应该用0、一个特殊的值或者一个空串代替空值,这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点.
1. 当结果集只有一行数据时使用LIMIT 1
2. 避免SELECT *，始终指定你需要的列
从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
3. 使用连接（JOIN）来代替子查询(Sub-Queries)
连接（JOIN）.. 之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作.
4. 使用ENUM、CHAR 而不是VARCHAR,使用合理的字段属性长度.
5. 尽可能的使用NOT NULL
6. 固定长度的表会更快
7. 拆分大的DELETE 或INSERT 语句
8. 查询的列越小越快
Where条件
在查询中,WHERE条件也是一个比较重要的因素,尽量少并且是合理的where条件是很重要的,尽量在多个条件的时候,把会提取尽量少数据量的条件放在前面,减少后一个where条件的查询时间.
有些where条件会导致索引无效:
Ø where子句的查询条件里有！=，MySQL将无法使用索引。
Ø where子句使用了Mysql函数的时候，索引将无效，比如：select * from tb where left(name, 4) = ‘xxx’
Ø 使用LIKE进行搜索匹配的时候，这样索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 时索引无效 |
