mysql性能优化二慢查询分析、优化索引和配置

转载

yzy121403725 2017-04-10 10:21:37 博主文章分类：mysql等关系型数据库

文章标签 mysql 慢查询分析 文章分类 服务器

三、mysql性能优化-慢查询分析、优化索引和配置

基本思路：

1）性能瓶颈定位

Show命令

慢查询日志

explain分析查询

profiling分析查询

2）索引及查询优化

3）配置优化

1、查询与索引优化分析

说明：在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

show命令：

可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：

查看MySQL服务器配置信息：

查看MySQL服务器运行的各种状态值：

显示系统变量：

显示状态信息：

比较全的show命令的使用可参考：

或者http://dev.mysql.com/doc/refman/5.7/en/show.html

慢查询日志

慢查询日志开启：

慢查询日志开启方法二：

查看慢查询的设置信息

我们可以通过打开log文件查看得知哪些SQL执行效率低下

下面是一个例子：

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。

mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

有关mysqldumpslow命令的用法可以参考其帮助：#mysqldumpslow --help

下面是一个例子：

上面显示结果中就是一条慢查询，如何优化呢？

一是在entertime列上创建索引优化查询：

二是优化这个sql查询语句：

从下图可以看查询0.00秒：

总结：使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

explain分析查询

可以模拟优化器执行SQL查询语句。

优化方法：在stuname列上创建索引：

再次执行explain。

profiling分析查询

用过慢查询我们可以自动哪些SQL局域执行效率低下，然后我们再通过explain可以得知SQL语句的具体执行情况、索引的使用等。还可以结合show来查看执行状态。

如果觉得explain不够详细，则可以通过profiling来得到更准确的SQL执行信息。

1）profiling默认是关闭的，我们先来看一下profiling是开启还是关闭的

或者

2）开启profiling功能

3）执行要测试的sql语句

4）对于show profile我们还可以查看到更多的信息如内存和CPU。

5）测试完成以后，一定记住不要忘记关闭调试功能，以免影响数据库的正常使用

配置优化

下面列出了对性能优化影响较大的主要变量，

主要分为连接请求的变量和缓冲区变量。

1、连接请求的变量

1）max_connections //设置mysql的最大连接数

什么时候用到：如果服务器并发连接请求量较大，建议增大该值。

注意：如果连接数越多，mysql会为每个连接提供连接缓冲区，会开销更多的内存，所以要适当调整该值。

数值过小会报错：error 1040：too many connections

①通过mysql>show status like ‘connections’;来查看mysql服务器当前的连接数（不管成功连接与否，即使失败了记录），如果想看准确的数目，则命令是：“showprocesslist;”

②mysql>show variables like‘max_connections’ //查询最大连接数

③mysql>show status like ‘max_used_connections’ //查询响应的连接数

④max_used_connections / max_connections * 100%(当结果≈ 85%的时候为最佳)，如果两者两同，就是

max_connections设置的过低或者服务器负载已经超上限了。如果结果低于10%，则说明max_connection设置过大。

⑤那么如何修改max_connections呢？

修改/etc/my.cnf配置文件，添加如下配置：

2）back_log //设置mysql的请求队列数量

什么时候起作用：当一个mysql线程在很短时间内收到非常多的连接请求是用到。

Mysql的连接数达到max_connections的时候，新来的请求就放在队列中，当等待连接的数目超过了back_log则忽视多余的请求。

①查看当前主机的mysql进程列表

②mysql> show variables like 'back_log'； //查看back_log的设置

③如何设置back_log?

修改/etc/my.cnf配置文件，添加如下配置：

3）wait_timeout和interactive_timeout

wait_timeout:就是好久没操作了，该连接会被断开，这里设置的就是秒数，用于非交互式模式。

Interactive_timeout：跟上面意思是一样的，不过它使用的是交互式模式，即“mysql>”模式。

Interactive_timeout默认的时间数值是28800秒（即8个小时），我们可以把它调优成7200秒（即2小时）。

①对性能的影响：

（1）如果设置太小，连接会很快关闭

（2）如果设置太大，容易造成连接打开时间过长，在show processlist的时候，可以看见很多sleep状态的连接从而造成too many connections错误。

（3）一般情况下wait_timeout的值低一些

②查看wait_timeout和interactive_timeout的值

③如何修改wait_timeout和interactive_timeout的值

修改/etc/my.cnf配置文件，手动添加以下两行配置项

2、缓冲区的变量

1）key_buffer_size //设置索引缓冲区大小。决定了索引的处理速度。

①可通过检查Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。

说明：key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好。

Key_read_requests:所有索引的读请求

Key_reads:索引未命中缓存数（即不是通过缓存的索引读请求，而是直接通过读取硬盘）。

注意：key_buffer_size只对myisam存储引擎表起作用。即使你不使用myisam表，但是内部的临时磁盘表还是myisam表，它也要使用该值。

②检查状态值，查看created_tmp_disk_tables的值

③如何调整key_buffer_size的值，默认是8M。

修改/etc/my.cnf配置文件，添加下面这一行配置项

2）query_cache_size //简称（QC），设置查询缓冲区的大小，并将查询的结果直接存放在缓存区中。

说明：今后执行同样（同样：是字母大小写法也一样，有无空格也一

样，空几个格也要严格一样）的select语句，则直接从缓冲区中读取。

①通过检查状态值’Qcache%’，可以知道query_cache_size设置是否合理

碎片过多了，需要清理。通过“flush query cache”命令对缓存碎片进行清除。注意：当一个表更新后，则与它相关的cache_blocks（缓存块）都被清空，但是这个缓存快还是不能用，必须通过“flush query cache”命令清除后才能被使用。

Qcache_free_memory:是query_cache_size的剩余的还未被使用缓存区大小。通过该值可以知道缓存区是否够用还是过多造成浪费了。

Qcache_hits:有多少次查询是从缓存区直接查询到的。该值越大，明缓存的效果越明显。

Qcache_inserts:有多少次查询不是从缓存区查询到的，而是从硬中查找的，查询到结果后，把结果插入到缓存区里面，为了下次查时候用。该值越大，说明缓存区使用的较少。

Qcache_lowmem_prunes:有多少条sql缓存是因为缓存区大小不足而被新的sql缓存给挤出了缓存区。通过qcache_lowmem_prunes和qcache_free_memory相互结果，更能真实的反映出缓存区大小是否真的够用。如qcache_lowmem_prunes这个数值不断增长，说明碎片非常的严重者缓存区太小了（可以通过查看qcache_free_blocks qcache_free_memory来判断到底是属于哪一种）

Qcache_not_cached:是指不适合缓存的查询SQL语句的数量，这些询语句不是select语句，而是像now()之类的函数（now是一个数，作用是获取当前mysql服务器的系统时间，因为时间是一个变的量，所以不适合做缓存插入到缓存区）

Qcache_queries_in_cache:是指当前缓存区中缓存的SQL语句数量。

Qcache_total_blocks:是指当前缓存区中缓存块（blocks）的数量。

②查询mysql服务器的query_cache的配置：

Query_cache_type:返回的值是off表示不缓存任何查询，即查询缓存功能没有开启

Query_cache_limit:超过该值大小的查询结果不会缓存(默认值是1M，即超过1M的查询缓存结果不缓存到缓存区)

Query_cache_min_res_unit:缓存块的最小大小，默认是4KB一个块。

Query_cache_size:查询的缓存区的大小（注意：QC存储最小单位是1024字节，你设定的QC的值必须是1024字节的倍数）

Query_cache_type:缓存的类型，即决定缓存什么样的查询，这个值必须是数字。（一般我们设置为1就可以了）

Query_cache_wlock_invalidate:当有其他客户端正在对MyISAM表进行写操作时，如果查询在缓存区中，是否返回缓存的结果还是等写操作完成再读表获取结果，默认是等写操作完成后再向表读取结果。

③修改query_cache_size 的大小以及缓存的类型

修改/etc/my.cnf配置文件,添加下面两行配置项：

④验证是否真正开启了

⑤缓存区中缓存的限制：

建议通过精确设置的方式，仅仅让合适的表的数据可以进入查询缓存区，仅仅让某些查询语句的查询结果被缓存。

3）max_connect_errors //设置最大失败连接次数

①目的：阻止过多尝试失败的客户端以防止暴力破解密码的情况。

当超过指定次数，则禁止该主机登录mysql，而不是禁止该用户登录，禁止有效期是永久的，但是可以通过重启mysql服务或通过“flush hosts”命令清空主机的相关信息。

②怎么添加max_connect_errors的值

修改/etc/my.cnf配置文件，添加下面的一行配置项：

4）sort_buffer_size //为order by或group by语句的结果分配一个缓冲区

①目的是加快下次order by、group by语句的查询速度，是一个connection级参数，当这些SQL语句第一次把结果存入到该order by或group by语句专享额缓冲区里面的时候，mysql就会一次性的为该SQL语句分配设置好缓存大小。

②怎么添加sort_buffer_size的值

修改/etc/my.cnf配置文件，添加下面的一行配置项：

例如：500个连接（即有500个order by或group by语句）将会消耗 500*sort_buffer_size(2M)=1G内存

5）max_allowed_packet=32M //限制mysql服务器接收的客户端发来的数据包的大小（即客户端存入的数据的大小）

该配置项的最大值是1G，但是设置的值必须是1024字节的整数倍。

6）join_buffer_size=2M //用于表间关联的缓存的大小

和sort_buffer_size一样，该参数对应的分配缓存大小也是每个连接独享。

7）thread_cache_size=300 //服务器线程数量的缓存

这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的（即缓存数量名额以用完），那么这个线程将被退回去并重新创建（再来进行线程缓存数请求，如果还没有名额，再退回去）,如果有很多新的线程，增加这个值可以改善系统性能。

说明：因为我没有在my.cnf文件开启这个配置项，所以空闲线程是为0。

3、配置innodb的几个变量

1）innodb_buffer_pool_size //相当于key_buffer_size对myisam的作用

①作用：InnoDB使用该参数指定缓冲区的大小来缓冲数据和索引。

对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

建议：对于2G内存的机器，推荐值是1G（50%）。

②修改my.cnf配置文件

2）innodb_flush_log_at_trx_commit //主要控制了innodb将日志缓冲区中的数据写入日志文件并刷新磁盘的时间点，取值分别为0、1、2三个

说明：

①0、1、2的说明

0，表示当事务提交时，并不立即任何操作，而是每秒钟将日志缓冲区中的数据直接写入日志文件并刷新磁盘一次；

1，每次事物的提交都会引起日志文件写入、刷新磁盘的操作，确保了事务的ACID；

2，每次事务提交先把日志从日志缓冲区写入到操作系统的缓冲区，然后每秒钟完成一次刷新到的磁盘操作。

②实际测试

设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。

③mysql官方建议

尽量将插入操作合并成一个事务，这样可以大幅提高速度。

3）innodb_thread_concurrency = 0 //设置innodb线程的并发数量，默认为0，即不限制。

说明：若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议的设置为8。

4)innodb_log_buffer_size //确定日志文件所用的缓存大小

以M为单位。缓冲区越大越能提高性能，对于较大的事务，可以增大缓存大小。

建议设置为32M

5）innodb_log_file_size = 50M //确定数据日志文件的大小

以M为单位。更大的设置可以提高性能。

6）innodb_log_files_in_group=3 //循环方式将日志文件写到多个文件中

为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

7）read_buffer_size = 1M //mysql读入缓冲区大小

对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。

和 sort_buffer_size一样，该参数对应的分配缓存是每个连接独享。

8）read_rnd_buffer_size = 16M //MySql 的随机读（查询操作）缓冲区大小

当按任意顺序如，按照排序顺序)，将分配一个随机读缓存区。

MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

9）bulk_insert_buffer_size = 64M //批量插入数据缓存大小

批量插入数据缓存大小，可以有效提高插入效率，默认为8M。

10)binary log

①binlog_cache_size = 2M //为每个连接分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M

②max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大缓存的大小。

③max_binlog_size = 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。

④expire_logs_days = 7 //定义了mysql清除过期日志的时间。二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。mysqladmin flush-logs 命令也可以重新开始新的binary log。

下面进行一个额外的测试

在优化之前执行mysqlslap工具进行测试

[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf--concurrency=10 --iterations=1 --create-schema='test1' --query='select * fromtest1.tb1' --engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose

显示结果：

Benchmark

Running forengine innodb

Average numberof seconds to run all queries: 13.837seconds

Minimum numberof seconds to run all queries: 13.837 seconds

Maximum numberof seconds to run all queries: 13.837 seconds

Number ofclients running queries: 10

Average numberof queries per client: 200

优化之后执行mysqlslap工具进行测试

显示结果：

Benchmark

Running forengine innodb

Average numberof seconds to run all queries: 4.199seconds

Minimum numberof seconds to run all queries: 4.199 seconds

Maximum numberof seconds to run all queries: 4.199 seconds

Number ofclients running queries: 10

Average numberof queries per client: 200

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql性能优化二慢查询分析、优化索引和配置

mysql性能优化二慢查询分析、优化索引和配置

51CTO博客