分析MySQL数据库的各项优化指标

对于MySQL数据库中，千万级别或者上亿级别的大表如何优化？首先需要考虑执行计划优化SQL语句和索引，然后再考虑前段加缓存memcached、Redis数据库，如果还达不到效果，就要使用MySQL数据库集群，配置读写分离架构，配置MySQL表分区，配置MyCat分表分库等。

1 、慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

如果MySQL数据库是主－从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。另外，MySQL有自带的命令mysqldumpslow可进行查询。

– 在参数文件my.cnf添加记录慢查询的代码

[root@db01 ~]# vim /mysql/data/3306/my.cnf
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/db01-query.log
long_query_time=10
log-error=/mysql/log/3306/db01-error.log
log_queries_not_using_indexes = 1 #记录所有没有使用到索引的查询语句
min_examined_row_limit=1000 #记录那些由于查找了多于1000次而引发的慢查询
log_slow_admin_statements = 1
           #记录那些慢的 optimize table，analyze table 和 alter table 语句
log_slow_slave_statements = 1 #记录由Slave所产生的慢查询
[root@db01 ~]# mysql -uroot -proot3306
mysql> show variables like ‘%slow%’;
mysql> show global status like ‘%slow%’;
mysql> show variables like ‘long_query_time’;
mysql> exit;
– 得到返回记录集最多的10个SQL
[root@db01 ~]# mysqldumpslow -s r -t 10 /mysql/log/3306/db01-query.log
– 得到访问次数最多的10个SQL
[root@db01 ~]# mysqldumpslow -s c -t 10 /mysql/log/3306/db01-query.log
– 得到按照时间排序的前10条里面含有左连接的查询语句
[root@db01 ~]# mysqldumpslow -s t -t 10 -g “left join” /mysql/log/3306/db01-query.log
– 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况
[root@db01 ~]# mysqldumpslow -s r -t 20 /mysql/log/3306/db01-query.log | more

2 、连接数

MySQL最大连接数的默认值是100，这个数值对于并发连接很多的数据库的应用是远不够用的，当连接请求大于默认连接数后，就会出现无法连接数据库的错误，因此我们需要把它适当调大一些。在使用MySQL数据库的时候，经常会遇到这么一个问题是“Can not connect to MySQL server. Too many connections”的1040错误，这是因为访问MySQL且还未释放的连接数目已经达到MySQL的上限。通常，MySQL的最大连接数默认是100，最大可以达到16384。

常用的修改最大连接数的最常用的两种方式：命令行修改最大连接数、通过修改配置文件来修改MySQL最大连接数。

通过命令行查看和修改最大连接数（max_connections），只在MySQL当前服务进程有效，一旦MySQL服务重启，又会恢复到初始状态。因为MySQL启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。

– 通过命令行查看和修改最大连接数（max_connections）
[root@db01 ~]# mysql -uroot -proot3306
mysql> set global max_connections=1000;
mysql> show variables like ‘max_connections’;
mysql> exit;

通过修改配置文件来修改MySQL最大连接数（max_connections）。这种方式说来很简单，只要修改MySQL数据库配置文件my.cnf的参数max_connections即可。例如，将此参数改为max_connections=1000，然后重启MySQL服务。

其他需注意的是在编程时，由于用MySQL语句调用数据库时，在每次之执行语句前，会做一个临时的变量用来打开数据库，所以你在使用MySQL语句的时候，记得在每次调用完MySQL之后就关闭MySQL临时变量。

另外对于访问量大的，可以考虑直接写到文本中，根据预测的访问量，先定义假若是100个文件文件名，需要的时候，再对所有文本文件中的数据进行分析，再导入数据库。如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。

最大连接数占上限连接数的 85%左右，如果发现比例在 10%以下，MySQL 服务器连接数上限设置 的过高了。因此，Max_used_connections / max_connections * 100% 大约占比85%左右。

3 、配置MySQL存储引擎内存缓存

针对MyISAM存储引擎，参数key_buffer_size 对MyISAM表来说非常重要。一般情况下，设置key_buffer_size = 2000M，或者key_buffer_size = 1G。如果是索引缓冲，设置可用内存的30%—40%，MyISAM 表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，在大多数情况下数据比索引大多了。

针对InnoDB存储引擎，设置参数innodb_buffer_pool_size = 8000M，或者设置更高些innodb_buffer_pool_size = 15G，用于缓存数据、索引、锁、插入缓冲、数据字典等，不超过物理内存的80%。总而言之，innodb_buffer_pool_size对InnoDB表来说非常重要。

4 、配置MySQL临时表

每次创建临时表，Created_tmp_tables在增加，如果是在磁盘上创建临时表，那么Created_tmp_disk_tables 也后增加。Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100%小于或等于 25%左右。

因此，MySQL数据库对临时表的配置是，只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表，可以通过使用两个命令查看临时表的相关信息。

– 查看临时表的配置信息
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status like ‘created_tmp%’;
mysql> show variables where Variable_name in (‘tmp_table_size’, ‘max_heap_table_size’);
mysql> exit;

5 、配置Open Table

Open_tables表示当前打开表的数量，Opened_tables表示打开过的表总数量。如果 Opened_tables数量过大，说明配置中的table_open_cache的值可能太小，比较合适的值为Open_tables / Opened_tables * 100% 大于或等于85% ，Open_tables / table_open_cache * 100% 小于或等于95%。

接下来查询一下MySQL数据库的与table_cache相关的值。

– 查询参数Open_tables的信息
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status where Variable_name in (‘Opened_tables’, ‘Open_tables’);
mysql> show variables like ‘table_open_cache’;
mysql> exit;

6、 MySQL进程

在MySQL配置文件中，设置了thread_cache_size之后，当客户端断开时，MySQL处理此客户的线程将会缓存以响应下一个客户端而不是销毁，前提是缓存数未达上限。

而Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL数据库一直在创建线程，这也是显示消耗系统资源的一个重要参数，在生产环境中，可以适当增加配置文件中thread_cache_size值。

– 查询MySQL进程
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status like ‘Thread%’;
mysql> show variables like ‘thread_cache_size’;
mysql> exit;

7、 查询缓存（Query Cache）

查询缓存可以看做是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）且开启了查询缓存，那么第二次查询就直接从查询缓存中取结果，可以通过下面的SQL来查看缓存命中次数（是个累加值）。

– 查询缓存（Query Cache）
[root@db01 ~]# mysql -uroot -proot3306
mysql> show variables like ‘%query_cache%’;
mysql> show global status like ‘qcache%’;
mysql> exit;

关于“%query_cache%”相关参数的具体作用如下：

◈ query_cache_limit，超过此大小的查询将不缓存；

◈ query_cache_min_res_unit，缓存块的最小大小，是一柄”双刃剑”，默认4KB，设置值大对大数据查询有好处，但如果查询都是小数据查询，就容易造成内存碎片和浪费；

◈ query_cache_size，查询缓存大小；

◈ query_cache_type，缓存类型，决定缓存什么样的查询，示例中表示不缓存select sql_no_cache 查询；

◈ query_cache_wlock_invalidate，当有其他客户端正在对MyISAM表进行写操作时，如 果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

关于“qcache%”相关参数的具体作用如下：

◈ Qcache_free_blocks，缓存中相邻内存块的个数。数目大说明可能有碎片，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块；

◈ Qcache_free_memory，缓存中的空闲内存；

◈ Qcache_hits，每次查询在缓存中命中时就增大；

◈ Qcache_inserts，每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；

◈ Qcache_lowmem_prunes，缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看，如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。与上面的free_blocks和free_memory 可以提示属于哪种情况；

◈ Qcache_not_cached，不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT语句或者用了“now()”之类的函数；

◈ Qcache_queries_in_cache，当前缓存的查询和响应的数量；

◈ Qcache_total_blocks，缓存中块的数量；

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%。如果查询缓存碎片率超过 20%，可以用“FLUSH QUERY CACHE”整理缓存碎片，或者如果查询都是小数据量，试试减小query_cache_min_res_unit的值。

查询缓存利用率=(query_cache_size - Qcache_free_memory) / query_cache_size * 100%。查询缓存利用率在 25%以下的话说明 query_cache_size 设置的过大，可适当减小；查询缓存利用率在80%以上，而且Qcache_lowmem_prunes 大于50，说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%。示例，查询缓存碎片率=20.46%，查询缓存利用率= 62.26%，查询缓存命中率 = 1.94%，则命中率很差，可能写操作比较频繁，而且有些碎片。

8、 排序使用情况

MySQL首先会尝试在内存中做排序，使用的内存大小由系统变量Sort_buffer_size决定，如果它的大小不够把所有的记录都读到内存中，MySQL就会把每次在内存中排序的结果存到临时文件中，等MySQL找到所有记录之后，再把临时文件中的记录做一次排序。 但盲目的增加Sort_buffer_size并不一定能提高速度。

– 查询排序使用情况
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status like ‘sort%’;
mysql> show variables like ‘Sort%’;
mysql> exit;

9 、文件打开数

MySQL文件打开数合适的设置Open_files / open_files_limit * 100% 小于或等于75%。

– 查询文件打开数
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status like ‘open_files’;
mysql> show variables like ‘open_files_limit’;
mysql> exit;

10 、表锁情况

MySQL数据库表锁主要涉及两个参数：Table_locks_immediate和Table_locks_waited，前者表示立即释放表锁数，后者表示需要等待的表锁数。如果Table_locks_immediate / Table_locks_waited大于5000，最好采用InnoDB引擎。

– 查询表锁情况
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status like ‘table_locks%’;
mysql> exit;

11 、表扫描情况

计算表扫描率： Handler_read_rnd_next / Com_select。如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过 8MB。这个时候就要分析相应的SQL语句了，比如从慢查询日志中找到SQL，然后进行优化与分析。

– 查询表扫描情况
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status like ‘handler_read%’;
mysql> show global status like ‘com_select’;
mysql> show variables like ‘read_buffer_size’;
mysql> exit;

12、 查询吞吐量QPS与TPS

QPS（Query Per Second，每秒查询率）是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准，经常用每秒查询率来衡量系统服务器的机器的性能。

TPS（Transaction Per Second，每秒事务量）是指每秒钟系统能够处理的交易或事务的数量，是衡量系统处理能力的重要指标。

一个系统的吞度量（承压能力）与request对CPU的消耗、外部接口、IO等等紧密关联。单个reqeust 对CPU消耗越高，外部系统接口、IO影响速度越慢，系统吞吐能力越低，反之越高。

系统吞吐量几个重要参数：QPS（TPS）、并发数、响应时间。QPS（TPS）：每秒钟request/事务数量；并发数是系统同时处理的request/事务数；响应时间，一般取平均响应时间。理解了上面三个要素的意义之后，就能推算出它们之间的关系：QPS（TPS）=并发数/平均响应时间，或者并发数=QPS*平均响应时间。

一个系统吞吐量通常由QPS（TPS）、并发数两个因素决定，每套系统这两个值都有一个相对极限值，在应用场景访问压力下，只要某一项达到系统最高值，系统的吞吐量就上不去了，如果压力继续增大，系统的吞吐量反而会下降，原因是系统超负荷工作，上下文切换、内存等等其它消耗导致系统性能下降。

– QPS = Questions(or Queries) / seconds
[root@db01 ~]# mysql -uroot -proot3306
mysql> show global status like ‘Question%’;
mysql> show global status like ‘uptime’;
– TPS = (Com_commit + Com_rollback) / seconds
mysql> show global status like ‘com_commit’;
mysql> show global status like ‘com_rollback’;
– 查看增查删改的执行次数
mysql> show global status like ‘com_insert%’;
mysql> show global status like ‘com_select%’;
mysql> show global status like ‘com_update%’;
mysql> show global status like ‘com_delete%’;
– key Buffer命中率，key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%，key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
mysql> show global status like ‘key%’;
– InnoDB Buffer命中率，innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
mysql> show status like ‘innodb_buffer_pool_read%’;
mysql> exit;