关系数据库之mysql三：从一条sql的生命周期说起-tutorial mysql-php.cn

mysql教程栏目介绍关系数据库的sql的生命周期。

关系数据库之mysql三：从一条sql的生命周期说起

MYSQL Query Processing

sql的执行过程和mysql体系架构基本一致

执行过程：

连接器：

建立与 MySQL 的连接，用于查询SQL语句，判断权限。

查询缓存：

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中
如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，提升效率

分析器：

对 SQL 语句进行硬解析，分析器先会做词法分析。分析SQL 语句的组成成分。判断输入的 SQL 语句是否满足语法规则。

优化器：

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。不同的执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器：

有索引：第一次调用的是取满足条件的第一行这个接口，之后循环取满足条件的下一行这个接口，最终把查询结果返回客户端
无索引：调用 InnoDB 引擎接口取这个表的第一行，判断sql查询条件，如果不是则跳过，如果是则将这行存在结果集中；调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

理解执行计划

EXPLAIN命令输出MySQL将如何执行你的SQL语句，但不会返回数据

如何使用

[root@localhost][(none)]> explain select * from 表名 where project_id = 36;
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| id | select_type | table                    | partitions | type | possible_keys | key        | key_len | ref   | rows   | filtered | Extra |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | 表名                     | NULL       | ref  | project_id    | project_id | 4       | const | 797964 |   100.00 | NULL  |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+复制代码

Salin selepas log masuk

id

id相同执行顺序由上至下
id不同，id值越大优先级越高，越先被执行

select_type

SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
PRIMARY：查询中包含子部分，最外层查询则被标记为 primary
DERIVED：是子查询from的一部分
DEPENDENT SUBQUERY：子查询中的第一个SELECT，子查询依赖于外层查询的结果
SUBQUERY 表示在 select 或 where 列表中包含了子查询，
MATERIALIZED：表示 where 后面 in 条件的子查询
UNION：表示 union 中的第二个或后面的 select 语句
UNION RESULT：union 的结果

table

表对象

type

system > const > eq_ref > ref > range > index > ALL（查询效率）

system：表中只有一条数据，这个类型是特殊的const类型
const：针对于主键或唯一索引的等值查询扫描，最多只返回一个行数据。速度非常快，因为只读取一次即可。
eq_ref：此类型通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是=，查询效率较高
ref：此类型通常出现在多表的join查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询
range：范围扫描这个类型通常出现在 <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中
index：索引树扫描
ALL：全表扫描（full table scan）

possible_keys

可能使用的索引，注意不一定会使用
查询涉及到的字段上若存在索引，则该索引将被列出来
当该列为NULL时就要考虑当前的SQL是否需要优化了

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示NULL。
查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

key_length

索引长度

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

返回估算的结果集数目，并不是准确的值

filtered

示返回结果的行数占需读取行数的百分比， filtered 的值越大越好

extra

Using where：表示优化器需要通过索引回表，之后到server层进行过滤查询数据
Using index：表示直接访问索引就足够获取到所需要的数据，不需要回表
Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）
Using index for group-by：使用了索引来进行GROUP BY或者DISTINCT的查询
Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大
Using temporary 临时表被使用，时常出现在GROUP BY和ORDER BY子句情况下。（sort buffer或者磁盘被使用）

光看 filesort 字面意思，可能以为是要利用磁盘文件进行排序，实则不全然。当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后行行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。

提高查询效率

正确使用索引

为解释方便，来一个demo：

DROP TABLE IF EXISTS user; 
CREATE TABLE user( 
id int AUTO_INCREMENT PRIMARY KEY, 
user_name varchar(30) NOT NULL, 
gender bit(1) NOT NULL DEFAULT b’1’, 
city varchar(50) NOT NULL, 
age int NOT NULL 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE user ADD INDEX idx_user(user_name , city , age); 
复制代码

Salin selepas log masuk

什么样的索引可以被使用？

**全匹配：**SELECT * FROM user WHERE user_name='JueJin'AND age='5' AND city='上海';（与where后查询条件的顺序无关）
匹配最左前缀：(user_name )、(user_name, city)、(user_name , city , age)（满足最左前缀查询条件的顺序与索引列的顺序无关，如：(city, user_name)、(age, city, user_name)）
**匹配列前缀：**SELECT * FROM user WHERE user_name LIKE 'W%'
**匹配范围值：**SELECT * FROM user WHERE user_name BETWEEN 'W%' AND 'Z%'

什么样的索引无法被使用？

**where查询条件中不包含索引列中的最左索引列，则无法使用到索引： **

SELECT * FROM user WHERE city='上海';

SELECT * FROM user WHERE age='26';

SELECT * FROM user WHERE age='26' AND city=‘上海';

**即使where的查询条件是最左索引列，也无法使用索引查询用户名以N结尾的用户： **

SELECT * FROM user WHERE user_name LIKE '%N';

**如果where查询条件中有某个列的范围查询，其右边的所有列都无法使用索引优化查询： **

SELECT * FROM user WHERE user_name='JueJin' AND city LIKE '上%' AND age=31;

**索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询： **

SELECT * FROM user WHERE user_name=concat(user_name,'PLUS');

选择合适的索引列顺序

在组合索引的创建中索引列的顺序非常重要，正确的索引顺序依赖于使用该索引的查询的查询方式
对于组合索引的索引顺序可以将选择性最高的列放到索引最前列，该法则与前缀索引的选择性方法一致
并不是说所有的组合索引的顺序都使用该法则就能确定，还需要根据具体的查询场景来确定具体的索引顺序

覆盖索引条件

如果一个索引中包含所有要查询的字段的值，那么就称之为覆盖索引

SELECT user_name, city, age FROM user WHERE user_name='Tony' AND age='28' AND city='上海';

因为要查询的字段(user_name, city, age)都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

使用索引进行排序

在排序操作中如果能使用到索引来排序，那么可以极大地提高排序的速度，要使用索引来排序需要满足以下两点即可：

ORDER BY子句后的列顺序要与组合索引的列顺序一致，且所有排序列的排序方向(正序/倒序)需一致
所查询的字段值需要包含在索引列中，及满足覆盖索引

排序可用demo：

SELECT user_name, city, age FROM user_test ORDER BY user_name;
SELECT user_name, city, age FROM user_test ORDER BY user_name,city;
SELECT user_name, city, age FROM user_test ORDER BY user_name DESC,city DESC;
SELECT user_name, city, age FROM user_test WHERE user_name='Tony' ORDER BY city;

排序不可用demo：

SELECT user_name, city, age FROM user_test ORDER BY user_name gender;
SELECT user_name, city, age, gender FROM user_test ORDER BY user_name;
SELECT user_name, city, age FROM user_test ORDER BY user_name ASC,city DESC;
SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'W%' ORDER BY city;