MySQL的优化点总结---通过计算多种状态的百分比看MySQL的性能情况
1 读写比例：
show global status like 'com_select'; 获得服务器启动到目前查询操作执行的次数；
show global status like 'com_insert'; 获得服务器启动到目前插入操作执行的次数；
show global status like 'com_update'; 获得服务器启动到目前更新操作执行的次数；
show global status like 'com_delete’; 获得服务器启动到目前删除操作执行的次数；
计算读百分比：
select concat(com_select / (com_select+com_insert+com_update+com_delete)* 100,'%');
计算写百分比：
select concat(com_insert+com_update+com_delete / (com_select+com_insert+com_update+com_delete) * 100,’%’);
通过检查数据库读写比例，可反映出应用是读密集型还是写密集型。
2 慢查询比例：
开启慢查询日志：
slow_query_log = 1 # 开启慢查询日志
log_output = FILE|TABLE # 指定日志存储方式，默认为 file
slow_query_log_file = slow-query.log # 指定慢查询日志文件位置
long_query_time = 1 # 执行及响应时间超过该参数设置的值记录日志
show global status like 'com_insert'; 获得服务器启动到目前插入操作执行的次数；
show global status like 'com_update'; 获得服务器启动到目前更新操作执行的次数；
show global status like 'com_delete’; 获得服务器启动到目前删除操作执行的次数；
计算读百分比：
select concat(com_select / (com_select+com_insert+com_update+com_delete)* 100,'%');
计算写百分比：
select concat(com_insert+com_update+com_delete / (com_select+com_insert+com_update+com_delete) * 100,’%’);
通过检查数据库读写比例，可反映出应用是读密集型还是写密集型。
2 慢查询比例：
开启慢查询日志：
slow_query_log = 1 # 开启慢查询日志
log_output = FILE|TABLE # 指定日志存储方式，默认为 file
slow_query_log_file = slow-query.log # 指定慢查询日志文件位置
long_query_time = 1 # 执行及响应时间超过该参数设置的值记录日志
show global status like 'Slow_queries '; # 获得服务器启动到目前慢查询操作记录的次数；
注意，慢查询包括 select 、 update 以及 delete ，没有 insert 。
计算慢查询比例：
select concat(Slow_queries / (Com_select+Com_update+Com_delete) * 100,’%’);
通过计算慢查询比例，可反映出数据库运行效能。
3 连接数检查：
show global status like 'max_connections'; # 获得数据库运行的最大连接数 //允许的最大连接数
show global status like 'Max_used_connections'; # 获得最大一次的连接数 //最大突发并行连接数
show global status like 'connections'; # 获得数据库运行到目前，总共被连接了多少次 //登陆的次数
show global status like 'Threads_connected'; # 获得当前连接数
show global status like 'Threads_running'; # 获得当前正在运行的连接数
mysql> show global status like 'Threads_connected'; //两次 mysql -uroot -p +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 2 | +-------------------+-------+ 1 row in set (0.01 sec) mysql> show global status like 'Threads_running'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Threads_running | 1 | +-----------------+-------+ 1 row in set (0.00 sec)
mysql> show processlist; +----+------+-----------+-------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+-------+------------------+ | 2 | root | localhost | mysql | Sleep | 4 | | NULL | | 3 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+-------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)
计算当前连接数的比例：
select concat(Threads_connected / max_connections *100,'%'); # 计算最大一次的连接数比例：
select concat(Max_used_connections / max_connections *100,'%');# 通过连接数检查，可得知数据库在不同时间段被请求的压力
4 线程缓存：
show global status like 'Connections'; # 获得数据库运行到目前，总共被连接了多少次
show global status like 'Threads_created'; # 获得数据库运行到目前，创建连接线程的次数
计算连接线程缓存命中率：
select concat((Connections-Threads_created) / Connections × 100,’%’);
通过计算连接线程缓存命中率，可反映出连接线程的命中情况，命中率越大越好。
如果命中率过低，则表示缓存连接线程的数量过少，可以考虑加大 thread_cache_size 的值。
5 表缓存：
show global status like 'Table_open_cache_misses'; # 新打开的表的次数。 不命中
show global status like 'Table_open_cache_hits'; # 从表缓存中拿已打开的表的次数，该状态变量 5.6 才开始存在 命中
show global status like 'Opened_tables'; # 打开表的总次数
通过已知自己数据库中有多少表，再观察 Opened_tables 的值，可以得知表缓存的数量是否合理，如果打开表的次数大于数据库中已有的表数
量，则表示 table_open_cache 的值不够，可以考虑加大。
计算表缓存的命中率：
select concat(Table_open_cache_hits /(Table_open_cache_misses + Table_open_cache_hits) * 100,’%’);
通过计算表缓存的命中率，可反映出表缓存的情况，该比例越大越好
mysql> create table ttt (a int); Query OK, 0 rows affected (0.22 sec) mysql> show global status like 'Table_open_cache_hits'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_open_cache_hits | 31 | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> show global status like 'Table_open_cache_misses'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Table_open_cache_misses | 116 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> show global status like 'Opened_tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 116 | +---------------+-------+ 1 row in set (0.00 sec) mysql> select * from ttt; //第一次打开 Empty set (0.01 sec) mysql> show global status like 'Opened_tables'; //+1 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 117 | +---------------+-------+ 1 row in set (0.02 sec) mysql> show global status like 'Table_open_cache_misses'; //table缓存中没有表，丢失 +1 +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Table_open_cache_misses | 117 | +-------------------------+-------+ 1 row in set (0.01 sec) mysql> show global status like 'Table_open_cache_hits'; //缓存未命中,不变 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_open_cache_hits | 31 | +-----------------------+-------+ 1 row in set (0.01 sec) mysql> select * from ttt; //在打开表 Empty set (0.00 sec) mysql> show global status like 'Table_open_cache_hits'; //表缓存命中 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_open_cache_hits | 32 | +-----------------------+-------+ 1 row in set (0.01 sec)
6 临时表：
show global status like 'Created_tmp_disk_tables'; # 查看在磁盘上创建临时表的次数
show global status like 'Created_tmp_tables'; # 查看创建临时表的总次数，包括在内存中和磁盘。
如果发现在磁盘上创建临时表的次数过多，则表示临时表的缓存区内存不够，可以考虑加大 tmp_table_size 和 max_heap_table_size 的值。
计算在磁盘上创建临时表的比例：
select concat(Created_tmp_disk_tables / Created_tmp_tables * 100,’%’);
通过计算在磁盘上创建临时表的比例，可反映出数据库的使用临时表的情况，该比例越小越好。
7 额外的排序：
show global status like 'Sort_merge_passes'; # 在磁盘中进行额外排序的次数
show global status like 'Sort_scan'; # 通过表扫描进行排序的总次数，也就是额外排序的总次数
如果发现在磁盘上进行排序的次数过多，则表示排序缓冲区内存不够，可以考虑加大 sort_buffer_size 的值。
计算磁盘排序的比例：
select concat(Sort_merge_passes / Sort_scan * 100,'%');
通过计算在磁盘上进行额外排序的比例，可反映出数据库排序的情况，该比例越小越好。
8 binlog 缓冲：
show global status like 'Binlog_cache_disk_use'; # 在磁盘上创建临时文件用于保存 binlog 的次数
show global status like 'Binlog_cache_use'; # 缓冲 binlog 的总次数，包括 binlog 缓冲区和在磁盘上创建临时文件保存 binlog 的总次数
如果发现在磁盘上创建临时文件保存 binlog 的次数过多，则表示 binlog 缓冲区内存不够，可以考虑加大 binlog_cache_size 的值。
计算在磁盘上创建临时文件保存 binlog 的比例：
select concat(Binlog_cache_disk_use / Binlog_cache_use * 100,’%’);
通过计算在磁盘上创建临时文件保 binlog 的比例，可反映出数据库 binlog 的情况，该比例越小越好。
9 redo 日志：
select global status like 'Innodb_log_waits'; # 查看 innodb redo 日志等待缓冲区刷新的次数。
当 redo 缓冲区容纳不下事务产生的 redo 日志时，本次事务产生的 redo 日志在写入 redo 缓冲区之前就必须等待 redo 缓冲区有足够的空间才能写入。
如果发现 redo 日志等待刷新的次数过多，则表示 innodb redo 日志缓冲区的大小不够，可以考虑加大 innodb_log_buffer_size 的值。
10 InnoDB 缓存：
show global status like 'Innodb_buffer_pool_read_requests'; # 读取页的总次数
show global status like 'Innodb_buffer_pool_read'; # 从磁盘读取页的次数
如果发现从磁盘读取页的次数过多，则有可能是因为 innodb 缓冲池的大小不够，此时可以考虑加到 innodb_buffer_pool_size 的值。
计算 innodb 缓存命中率：
select concat((Innodb_buffer_pool_read_requests - Innodb_buffer_pool_read) / Innodb_buffer_pool_read_requests * 100,’%’);
通过计算 innodb 缓存命中率，可反映出 innodb 缓存的效率，该比例越大越好
