今天MySQL数据库栏目介绍相关执行计划。
今天MySQL数据库栏目介绍相关执行计划。 MySQL 系列的第三篇博客,主要内容是 MySQL 中关于 Explain 执行计划的分析,假如你已经知道如何分析执行计划,那么对于 SQL 调优也就信手拈来了。 纵观众多一二线大厂招聘时的岗位要求,但凡设计数据库的必定会要求有 SQL 调优的经验,这几乎已经成为与 Spring 不相上下的“八股文”类面试题。 要想进行 SQL 调优,首先需要知道 SQL 的执行情况,最直观的感觉当然是 SQL 语句执行的时间,然而除此之外,我们还可以通过执行计划来分析 SQL 语句的执行情况,从而进行调优。 1. Explain 简述Explain 语句可以查看 MySQL 是如何执行这条 SQL 语句的,包括使用索引情况、扫描行数等,这些信息对于 SQL 调优来说十分重要,所以首先得看懂执行计划。 mysql> explain select * from user where name='one'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| 1 | SIMPLE | user | NULL | ref | a | a | 13 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码 以上是一条简单查询语句的执行计划,这张表一共有12个字段,分别代表不同的含义,下面一一叙述。
以上只是对执行计划表各个字段的名词解释,接下来我会通过实际的例子来帮助大家(我自己)更好地理解其中 2. Explain 详述2.1 示例表结构首先介绍本文中将用到的示例表表结构以及数据行: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(36) DEFAULT NULL COMMENT '姓名', `age` int(11) NULL DEFAULT NULL COMMENT '年龄', `email` varchar(36) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_age_name`(`age`, `name`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1;复制代码 通过函数向表中插入1000000条测试数据。 CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()begin declare i int; set i=1; while(i<=1000000)do insert into user(id,name,age,email) values(i, CONCAT('name',i), i % 50, concat(i,'name@email.cn')); set i=i+1; end while;end复制代码 2.2 select_type in Explain执行计划中
mysql> explain select * from user where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码 2.2.1 PRIMARY若查询语句中包含任何复杂的子部分,那么最外层部分会被标记为 mysql> explain select * from user where id=(select id from user where id=1); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| 1 | PRIMARY | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)复制代码 在这条 SQL 语句的执行计划中,第一条执行的 SQL,即 2.2.2 SUBQUERY包含在 select 或 where 内容中的子查询会被标记为 2.2.3 DERIVED包含在 FROM 关键字后的子查询(即将子查询的结果视为「表」),被视为「表」的子查询会被标记为 mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1; +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码 从执行计划中可以看到,第二条执行的 SQL,即
2.3 type in Explain
为了能更好地理解各个类型的含义,我对上述每一种类型都举出了相应的示例。
2.3.1 ALL
mysql> explain select * from user where age+2=20; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1002301 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码 可以看到 我们知道在 where 查询条件中,不应该对查询字段使用函数或表达式(应该写在等号不等号右侧),不了解此内容的可以看看我的上一篇博客 —— 我所理解的MySQL(二)索引。 这条查询语句在优化后应该是: mysql> explain select * from user where age=18; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39360 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码 2.3.2 index
同时,由于索引树本身就是有序的,可以避免排序。 mysql> explain select id,age from user where name='name1'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| 1 | SIMPLE | user | NULL | index | NULL | idx_age_name | 116 | NULL | 1002301 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码 示例查询语句如上所述,当查询条件存在于联合索引 2.3.3 range
需要注意的是,若使用 mysql> explain select * from user where age>18 and age<20; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| 1 | SIMPLE | user | NULL | range | idx_age_name | idx_age_name | 5 | NULL | 36690 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.01 sec) mysql> explain select * from user where age=18 or age=20; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| 1 | SIMPLE | user | NULL | range | idx_age_name | idx_age_name | 5 | NULL | 78720 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码 2.3.4 index_merge
MySQL 在 where 语句中存在多个查询条件,并且其中存在多个字段可以分别使用到多个不同的索引,在这种情况下 MySQL 可以对多个索引树同时进行扫描,最后将它们的结果进行合并,如: mysql> explain select * from user where id=1 or age=18; +----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| 1 | SIMPLE | user | NULL | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4 | NULL | 39361 | 100.00 | Using sort_union(idx_age_name,PRIMARY); Using where | +----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+1 row in set, 1 warning (0.00 sec)复制代码 上面这条查询语句中的 id=1 和 age=18 分别使用到了 2.3.5 ref
比如在介绍全表扫描中优化后 SQL 的访问类型就是 2.3.6 eq_ref
mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10; +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| 1 | SIMPLE | user1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1002301 | 100.00 | NULL | | 1 | SIMPLE | user2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | all_in_one.user1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+2 rows in set, 1 warning (0.00 sec)复制代码 2.3.7 const
mysql> explain select * from user where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码 2.4 key_len in Explain在上一篇博客 —— 我所理解的MySQL(二)索引 中 2.4.1 字符类型字符类型的字段若作为索引列,它的索引长度 = 字段定义长度 字符长度 + 是否默认NULL + 是否是变长字段*,其中:
举个例子,为上述实例表中添加一个字符类型字段的索引。 alter table user add index idx_name(`name`);复制代码 然后通过 name 字段去做查询,查看执行计划。 mysql> explain select * from user where name='name1'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_name | idx_name | 111 | const | 2 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)复制代码 可以看到,执行计划中 根据上述索引长度的计算公式,name 列字段定义长度为36,字符集类型为默认的 utf8,该字段默认允许 NULL,同时该字段是可变长字段 varchar。 所以 2.4.2 其他定长类型对于定长类型的字段,其索引长度与它的数据类型长度是一致的。
需要注意的是,若该字段允许默认值为 NULL,与字符类型一样,其索引长度也需要加上1。 mysql> explain select * from user where age=1; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39366 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码 如上面这个示例(本示例中索引只用到了 age 字段),age 字段为 int 类型,其索引长度本应为 4,但由于 age 字段默认允许为 NULL,所以它的索引长度就变成了5。 2.5 rows in Explain扫描行数在执行计划中其实是一个估值,MySQL 会选择 N 个不同的索引数据页,计算平均值得到单页索引基数,然后再乘以索引页面数,就得到了扫描行数的估值。 扫描行数就是优化器考量索引执行效率的因素之一,一般而言扫描行数越少,执行效率越高。 2.6 Extra in Explain执行计划中
以上就是我所理解的MySQL之三:执行计划的详细内容,更多请关注模板之家(www.mb5.com.cn)其它相关文章! |