MySQL慢查询定位及其优化建议
本文最近更新于2021.11.23
（一）慢查询配置
慢查询的用途是用来发现执行时间长的查询语句，以便对这些语句进行优化
[mysqld] #在这里面增加，其它地方无效 #server-id=1 #log-bin=master-bin slow_query_log=1 #开启查询 slow_query_log_file=/bp/mysql.log #慢查询日志文件存放位置，注意这个比较坑，要求目标目录可写，最好还是自己创建一个目录，并-R 添加写权限，这个是网上说的，不过我实验时发现赋777权限才可以 long_query_time=1 #定义超过1秒就算是慢查询，一般是5到10秒吧 log_queries_not_using_indexes = ON #记录没有使用索引的查询
重启服务
MariaDB [(none)]> show variables like '%slow%';
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | ON #开启成功 |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /bp/mysql.log |
+---------------------+--------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]>
（二）查看慢查询
那么怎么测试慢查询是否成功呢
比较简单的一种方法就是select sleep(5);这条语句会执行5秒钟
看一下慢查询日志文件
终端中执行以下命令
mysqldumpslow -t 10 查看访问时间最长的10个sql语句 
mysqldumpslow -s c -t 10 查看访问次数最多的10个sql语句
（三）慢查询定位
这里简单说下三种方法
1.explain
MySQL自带命令，可用于对select语句的执行计划进行分析，并详细输出分析结果。例如SQL有没有用上索引，有没有全表查询，都可以通过explain查看（但不会执行这条SQL）。
使用方法，只需要在查询语句前加explain关键字即可。
type
表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型从最好到最差依次如下:
system>const>eq_ref>ref>range>index>All
一般来说，得保证查询达到range级别，最好达到ref。
system是const的特列，表中只有一行元素匹配时为system
const指常量查询，在整个查询过程中个，该表最多有一个匹配行，当使用主键或唯一索引的所有部分与常量值进行比较时，将使用const。
eq_ref：primay key或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是const之外最好的联接类型，简单的select查询不会出现这种type。
ref：相比eq_ref，不适用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。简单select查询，name是普通索引（非主键索引或唯一索引）
range：范围扫描通常出现在in(), between,>,<,>=等操作中。使用一个索引来检索给定范围的行。
index：扫描全表索引，通常比All快一些
all：即全表扫描，意味着MySQL需要从头到尾去查找所需要的行。这种情况下需要增加索引来进行优化。
2.show profile
show profile是会话级别的，进入会话后执行set profiling=on;启用show profile，退出会话后会自动关闭show profile
[root@localhost h]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.36-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> set profiling=on; #启动profiling Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> select user from mysql.user; +------+ | user | +------+ | root | +------+ 1 row in set (0.00 sec) mysql> show profiles; #默认开查看启动profiling后的最近15条SQL执行记录，可通过profiling_history_size修改，最大为100 +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00104075 | show variables like 'profiling' | | 2 | 0.00026425 | select user from mysql.user | +----------+------------+---------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> show profile all for query 1; #查看某一条SQL具体资源消耗信息，格式为show profile all for query id; +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+ | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line | +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+ | starting | 0.000066 | 0.000005 | 0.000056 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | | checking permissions | 0.000014 | 0.000001 | 0.000013 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 | | Opening tables | 0.000016 | 0.000001 | 0.000014 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5815 | | init | 0.000044 | 0.000003 | 0.000041 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 128 | | System lock | 0.000007 | 0.000001 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 | | optimizing | 0.000002 | 0.000000 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 | | optimizing | 0.000002 | 0.000000 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 | | statistics | 0.000010 | 0.000001 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 | | preparing | 0.000011 | 0.000001 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 482 | | statistics | 0.000009 | 0.000000 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 | | preparing | 0.000004 | 0.000000 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 482 | | executing | 0.000006 | 0.000001 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 | | Sending data | 0.000005 | 0.000000 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 202 | | executing | 0.000002 | 0.000000 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 | | Sending data | 0.000798 | 0.000801 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 202 | | end | 0.000007 | 0.000004 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 | | query end | 0.000005 | 0.000005 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4959 | | closing tables | 0.000003 | 0.000002 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5018 | | removing tmp table | 0.000005 | 0.000005 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | free_tmp_table | sql_tmp_table.cc | 2420 | | closing tables | 0.000005 | 0.000005 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | free_tmp_table | sql_tmp_table.cc | 2449 | | freeing items | 0.000013 | 0.000013 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5637 | | cleaning up | 0.000011 | 0.000010 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1933 | +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+ 22 rows in set, 1 warning (0.00 sec) mysql>
Duration 持续时间（耗时）
Block io 指块IO开销
Context Switches 指上下文切换开销
Cpu 指CPU开销信息
3.Performance schema
在MySQL5.7中，performance schema有很大改进，包括引入大量新加入的监控项、降低占用空间和负载，以及通过新的sys schema机制显著提升易用性。
常用SQL如下：
---查看表的访问量：(可以监控每张表访问量的情况，或者监控某个库的访问量的变化) select table_schema,table_name,sum(io_read_requests+io_write_requests) from schema_table_statistics; select table_schema,table_name,io_read_requests+io_write_requests as io_total from schema_table_statistics; ---冗余索引和未使用索引的检查：（schema_redundant_indexes和schema_unused_indexes查看索引的情况） select * from sys.schema_redundant_indexes\G select * from sys.schema_unused_indexes; (如果有冗余索引和长期未使用的索引，应该及时清理，) ---查看表自增ID使用情况： select * from schema_auto_increment_columns\G (可以使用schema_auto_increment_columns视图，就能很简单的查到每个表的自增量使用情况，甚至可以精确到某个表的自增量情况) ---监控全表扫描的sql语句： select * from sys.statements_with_full_table_scans where db='test2'\G (使用statements_with_full_table_scans视图可以查看哪些表查询使用了全表扫描，其中exec_count是执行的次数，等信息) ---查看实例消耗的磁盘I/O情况：() select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10; (查看io_global_by_file_by_bytes视图可以检查磁盘I/O消耗过大的原因，定位问题)
参考连接：
http://www.deiniu.com/article/120164.htm
https://www.pianshen.com/article/8994646542/
（四）慢查询优化
1.只列出需要用到的字段，不要列出所有字段，以节省内存
2.避免使用NULL，使用该NULL会导致该字段无法命中索引
3.使用count(*)，而不是count(某列)，后者会导致判断值是不是null，会返回非null数据，增加判断成本
4.使用like查询，尽量避免不再字段前加%("%mysql")，可以在后面带%（"mysql%"），前%不走索引
5.添加合理索引，减少磁盘IO。例如查询常用列，表数据大于1万，同时建立索引的字段长度应尽量小，并且该列数据越少重复越好
6.避免在where子句中使用!=或<>或OR
7.字段多的标分解成多个标，有些字段使用频率很低，可以将这些字段分离出来形成新表。当表数据量大时，低频自读的存在会导致查询变慢
8.增加中间表。对于需要经常联合查询的表，可以建立中间表，把需要联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表查询，已提高查询效率
9.分解关联查询。将复杂的关联查询进行分解，对每个表进行一次单表查询，然后将查询结果在应用程序中关联，很多场景下这样会更高效。把涉及左右连接等复杂查询，分解为简单select查询，查询结果在应用中传递即可
