数据库查询流程

Untitled

  1. mysql收到sql语句后，会在分析器中先判断下SQL语句有没有语法错误
  2. 接下来是优化器，在这里会根据一定的规则选择该用什么索引
  3. 最后通过执行器去调用存储引擎的接口函数

Buffer pool

Untitled

buffer pool 用于存放索引页，通过索引页加速查询，得到数据页的具体位置，如果这些数据页不在buffer pool中，则从磁盘里加载进来。

Untitled

慢查询分析

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.06811025 | select * from user where age>=60                  |
|        2 | 0.00151375 | select * from user where gender = 2 and age = 80  |
|        3 | 0.00230425 | select * from user where gender = 2 and age = 60  |
|        4 | 0.00070400 | select * from user where gender = 2 and age = 100 |
|        5 | 0.07797650 | select * from user where age!=60                  |
+----------+------------+---------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

如果你想看某条SQL语句的具体耗时，那么可以执行以下的命令

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000034 |
| init                 | 0.000032 |
| System lock          | 0.000027 |
| optimizing           | 0.000020 |
| statistics           | 0.000058 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sending data         | 0.067701 |
| end                  | 0.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

可以看出Sending data的耗时最大，这个是执行器开始查询数据并将数据发送到客户端的耗时

使用explain

Untitled