4分钟阅读时间
解决一个mysql慢查询
慢sql是影响系统性能的重要因素，必须解决好这个问题。
之前一直依赖惯了通过mysql慢查询文本日志来解决问题，这次遇到的情况是无法访问文本日志，只能在mysql客户端里鼓捣，经过一番周折之后，终于顺利解决问题，原先一个页面的首字节返回时间长达 24 秒，经过优化后缩短为 1 秒。以下记录操作步骤。
设置
首先，看一下慢查询日志有没有开：
show variables like '%slow%';
| Variable_name | Value |
|---|---|
| log_slow_admin_statements | ON |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /home/mysql/data3001/mysql/slow_query.log |
第 4 行为 ON，说明已经开了，如果没有开的话，就先开一下吧。
show variables like '%long%';
| Variable_name | Value |
|---|---|
| long_query_time | 1.000000 |
| performance_schema_events_stages_history_long_size | 0 |
| performance_schema_events_statements_history_long_size | 0 |
| performance_schema_events_transactions_history_long_size | 0 |
| performance_schema_events_waits_history_long_size | 0 |
第 1 行说明慢查询门槛值是 1 秒，也就是说只要任意一个查询时间超过了 1 秒的就会被记录，如果你觉得这个值有点太低，也可以改为 5 秒或者 10 秒，看你觉得多长时间的一个查询是不可接受的。
show variables like '%log_output%';
| Variable_name | Value |
|---|---|
| log_output | TABLE |
这里比较关键，log 的输出方式是TABLE，才能把结果记录在数据库表中。
use mysql;
show tables;
| Tables_in_mysql |
|---|
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
这里有一个表slow_log，它就是存储我们的慢查询日志的地方了。
desc slow_log;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| start_time | timestamp(6) | NO | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) | |
| user_host | mediumtext | NO | NULL | ||
| query_time | time(6) | NO | NULL | ||
| lock_time | time(6) | NO | NULL | ||
| rows_sent | int(11) | NO | NULL | ||
| rows_examined | int(11) | NO | NULL | ||
| db | varchar(512) | NO | NULL | ||
| last_insert_id | int(11) | NO | NULL | ||
| insert_id | int(11) | NO | NULL | ||
| server_id | int(10) unsigned | NO | NULL | ||
| sql_text | mediumblob | NO | NULL | ||
| thread_id | bigint(21) unsigned | NO | NULL |
分析
在slow_log表中，query_time是我们最关心的：
select query_time, rows_sent, rows_examined, db from mysql.slow_log where query_time > 10 and rows_sent < 100 limit 10;
| query_time | rows_sent | rows_examined | db |
|---|---|---|---|
| 00:00:17.187272 | 15 | 15733 | mydb |
| 00:00:18.948918 | 15 | 15733 | mydb |
| 00:00:18.901217 | 15 | 15733 | mydb |
| 00:00:18.590528 | 15 | 15733 | mydb |
| 00:00:17.173634 | 15 | 15733 | mydb |
| 00:00:13.013449 | 15 | 15733 | mydb |
| 00:00:15.114500 | 15 | 15733 | mydb |
| 00:00:19.771646 | 15 | 15733 | mydb |
| 00:00:21.683656 | 15 | 15733 | mydb |
| 00:00:22.271268 | 15 | 15733 | mydb |
可以看到有很多次查询虽然只返回 15 条数据，而耗时竟然长达 17 秒！是可忍，孰不可忍！我们先只取第 1 条数据看一下：
select sql_text from mysql.slow_log order by start_time desc limit 1;
结果就是这条语句：
select * from a left join b on b.parentid=a.id LEFT join d on d.memberid=b.memberid limit 0, 15;
就是这么一条简单的 sql 语句，怎么就会需要 17 秒呢？我们首先怀疑的就是有没有加索引，索引不需要在每个字段上加，但是这里的查询关系上必须有，比如a.id, b.parentid, d.memberid, b.memberid。
show index from b;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| b | 0 | PRIMARY | 1 | id | A | 6714 | NULL | NULL | BTREE |
不出所料，果然只在主键上加了索引，而关键的parentid没有索引。
添加索引
alter table b add index b_parent (parentid);
再次查看索引：
show index from b;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| b | 0 | PRIMARY | 1 | id | A | 6714 | NULL | NULL | BTREE | |||
| b | 1 | b_parent | 1 | parentid | A | 6593 | NULL | NULL | YES | BTREE |
依此类推，把我们所有缺索引的地方统统都加上索引，再次查询，时间缩短到 1 秒以内，收工！
评论