MySQL性能优化——Explain执行计划详解 ﹏ヽ暗。殇╰゛Y 2022-12-19 00:59 135阅读 0赞 **目录** Explain简介 Explain 分析 1、id列 2、select\_type 列 3、type 连接类型 4、possible\_key、key 列 5、key\_len列 6、rows 列 7、filtered 列 8、ref 列 9、Extra 列 SQL脚本 -------------------- # Explain简介 # MySQL提供了一个执行计划的工具,在MySQL架构中,SQL语句通过优化器最终生成的就是一个执行计划。通过EXPLAIN我们可以模拟优化器执行SQL查询语句的过程,来知道MySQL是怎么处理一条SQL语句的。通EXPLAIN我们可以分析语句或者表的性能瓶颈,然后进行优化。 官方地址: [MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format][MySQL _ MySQL 5.7 Reference Manual _ 8.8.2 EXPLAIN Output Format] 我们新建三张表,三张表只有主键索引,来详细讲解一下EXPLAIN。**(建表SQL在文章末尾)。** mysql> EXPLAIN SELECT * FROM book; +----+-----------+--------+----------+-----+-------------+-----+-------+----+----+--------+------+ | id |select_type|table |partitions| type|possible_keys| key |key_len|ref |rows|filtered| Extra| +----+-----------+--------+----------+-----+-------------+-----+-------+----+----+--------+------+ | 1 |SIMPLE |book |NULL | ALL |NULL | NULL| NULL |NULL| 4 | 100.00 | NULL | +----+-----------+--------+----------+-----+-------------+-----+-------+----+----+--------+------+ 1 row in set, 1 warning (0.00 sec) # Explain 分析 # 接下来我们将分析图中 explain 中每列的信息。 ## 1、id列 ## id列的编号是 select 的序列号,有几个 select 就有几个id,并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类:简单子查询、派生表(from语句中的子查询)、union 查询。 select id是SQL执行的顺序的标识,SQL从大到小的执行 * id相同时,执行顺序由上至下 * 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行 * 如果id相同,则认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行 ** 1)id 值不同,id 值越大优先级越高,越先被执行** 我们查询book表name=java的email信息 EXPLAIN SELECT email FROM contact WHERE id = ( SELECT contact_id FROM author WHERE id = ( SELECT author_id FROM book WHERE NAME = 'java' ) ); 我们先根据name=java从book表中获取author\_id,再根据author\_id从author表中获取contact\_id,根据contact\_id查询contact表获取email。 上面SQL语句执行的顺序为book —> author —> contact,在执行计划中,id依次为 3、2、1 ![20201104153652628.png][] ** 2)id值相同,由上到下顺序执行** 我们获取book表id=1和author表contact\_id =2的数据 EXPLAIN SELECT book.name,author.name,email FROM book,author,contact WHERE book.author_id = author.id AND author.contact_id = contact.id AND (book.id = 1 or contact.id = 2) 我们从执行计划中,可以看到id值一样,book表在最后,执行的顺序为:book(4条)—> author (3条)—> contact (3条) ![20201104153257543.png][] 我们往author表中新增2条数据,再次执行上面的执行计划 INSERT INTO `author`(`id`, `name`, `contact_id`) VALUES (4, '张一', 4); INSERT INTO `author`(`id`, `name`, `contact_id`) VALUES (5, '李二', 5); EXPLAIN SELECT book.name,author.name,email FROM book,author,contact WHERE book.author_id = author.id AND author.contact_id = contact.id AND (book.id = 1 or contact.id = 2) 此时执行的顺序为:contact (3条)—> author (5条)—> book(4条) ![20201104153436266.png][] 随着表数据的改变,执行顺序也会跟着改变, ** 结论:** ** id值不同,先大后小,id 值相同时,从上往下顺序执行。** ## 2、select\_type 列 ## select\_type :查询类型,表示对应行是是简单还是复杂的查询,常见的查询类型: ** 1)simple:简单查询** SIMPLE 简单查询,不包含子查询,不包含关联查询 union EXPLAIN SELECT * FROM book; ![2020110315384526.png][] ** 2)primary:复杂查询中最外层的 select** PRIMARY 子查询 SQL 语句中的主查询,也就是最外面的那层查询。 EXPLAIN SELECT * FROM author WHERE id = ( SELECT author_id FROM book WHERE NAME = 'java' ); ![2020110315453415.png][] ** 3)subquery:包含在 select 中的子查询(不在 from 子句中)** SUBQUERY 子查询中所有的内层查询都是 SUBQUERY 类型的。 EXPLAIN SELECT * FROM author WHERE id = ( SELECT author_id FROM book WHERE NAME = 'java' ); ![2020110315453415.png][] ** 4)derived:衍生查询** derived 查询包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义) EXPLAIN SELECT * FROM (SELECT max(price) FROM book) tab; ![2020110316515094.png][] ** 5)union:联合查询** union查询在 union 中的第二个和随后的 select ** 6)union result:** 从 union 临时表检索结果的 select EXPLAIN SELECT * FROM book WHERE id=1 UNION SELECT * FROM book WHERE id=2 ![20201103165757987.png][] ## 3、type 连接类型 ## EXPLAIN输出的type列描述了表是如何连接的,从最佳类型到最差类型排序: system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > range > index > all 以上访问类型除了 all,都能用到索引。 type 所有的连接类型中,常用的连接类型:system、const 、eq\_ref、ref、range、index、all。 ** 1)const** 主键索引或者唯一索引,只能查到一条数据的 SQL。 EXPLAIN SELECT * FROM book WHERE id=1; 2)system system 是 const 的一种特例,官网上的说明: > The table has only one row (= system table). This is a special case of the [const][] join type. 只有一行满足条件。例如:只有一条数据的系统表。 EXPLAIN SELECT * FROM mysql.proxies_priv; ![2020110319250157.png][] ** 注意:** ** proxies\_priv表的存储引擎是MyISAM,我们新建一张表,存储引擎为MyISAM,表里面只有一条数据,type结果也是system,将存储引擎修改为InnoDB,type的结果就会变为const。** ** 3)eq\_ref** eq\_ref 通常出现在多表的 join 查询,表示对于前表的每一个结果,,都只能匹配到后表的一行结果。一般是唯一性索引的查询(UNIQUE 或 PRIMARY KEY)。 eq\_ref 是除 const 之外最好的访问类型。 EXPLAIN SELECT * FROM book,author WHERE book.author_id=author.id; ![20201103194207159.png][] ** 4)ref** ref 查询用到了非唯一性索引,或者关联操作只使用了索引的最左前缀。 我们先在book表nam字段上建立一个索引 ALTER TABLE book ADD INDEX `idx_name`(`name`); 根据name 非唯一性索引进行查询 EXPLAIN SELECT * FROM book WHERE name = 'java'; ![2020110319521445.png][] 关联操作只使用了索引的最左前缀,我们将author表的主键字段,由id修改为联合主键(id,name) ![2020110319572987.png][] EXPLAIN SELECT * FROM book,author WHERE book.author_id=author.id; ![20201103195819164.png][] 当author表id字段为主键的时候,关联查询type为eq\_ref,当建立联合索引后,关联查询,id字段使用了最左匹配原则,使用了(id,name)联合索引,type此时为ref。 ** 每小节我们执行完修改表操作,我们都执行rollback操作,保持表最原先的结构,方便后续内容的讲解,后面就不再说明。** rollback; ** 5)range** 我们在book表重新建立了name索引,此时我们执行模糊查询 ALTER TABLE book ADD INDEX `idx_name`(`name`); EXPLAIN SELECT * FROM book WHERE name like 'java'; ![20201103200457522.png][] EXPLAIN SELECT * FROM book WHERE id BETWEEN 2 AND 4; ![20201103200618700.png][] range 索引范围扫描。 如果 where 后面是 like、between and 、 >= 、<=、in 这些,type 类型就为 range。 6)all 不走索引一定是全表扫描ALL。 EXPLAIN SELECT * FROM book WHERE price > 60; ![20201103200835341.png][] ## 4、possible\_key、key 列 ## possible\_key是可能用到的索引,key实际用到的索引,如果key为NULL 就表示没有用到索引。 possible\_key 可以有一个或者多个,可能用到索引不代表一定用到索引。 反过来,possible\_key 为空,key 可能有值吗? 我们在book表上建立2个索引,一个是name,一个是(name,price)联合索引。 ALTER TABLE book ADD INDEX `idx_name`(`name`); ALTER TABLE book ADD INDEX `idx_name_price`(`name`, `price`); 我们查询name=java的书籍,发现有2个索引,idx\_name和idx\_name\_price,最终使用了idx\_name索引, EXPLAIN SELECT * FROM book WHERE name = 'java'; ![20201104094757754.png][] 我们再来查询 author\_id = 1 的数据,由于author\_id 没有索引,所以possible\_key和key都为Null,走全表扫描。 EXPLAIN SELECT * FROM book WHERE author_id = 1; ![20201104095138301.png][] ## 5、key\_len列 ## key\_len是查询使用到索引的长度,跟索引字段的类型、长度有关。 详细的key\_len计算可以看[《Explain执行计划key\_len详解》][Explain_key_len]这篇博客。 我们在book表name和author字段分表建立索引 ALTER TABLE book ADD INDEX `idx_name`(`name`); ALTER TABLE book ADD INDEX `idx_author_id`(`author_id`); 我们根据name索引进行查询,name字段类型为varchar(255),允许为空,CHARSET=utf8mb4 ,key\_ken = 4 \* 255 + 2 + 1 = 1023 ; EXPLAIN SELECT * FROM book WHERE name = 'java'; ![20201104104400895.png][] 我们根据author\_id 索引进行查询,author\_id 字段类型为int,允许为null ,key\_len = 4 + 1 = 5; EXPLAIN SELECT * FROM book WHERE author_id = 1; ![20201104104728564.png][] ## 6、rows 列 ## MySQL认为扫描多少行才能返回请求的数据,是一个预估值,rows的值一般来说行数越小越好。 EXPLAIN SELECT * FROM book,author WHERE book.author_id = author.id and author.name ='张三'; ![20201104175233252.png][] ## 7、filtered 列 ## filtered表示存储引擎返回的数据在 server 层过滤后,剩下多少满足查询的记录数量的比例,它是一个百分比,在author表中有三条数据,我们查询name=张三的记录,只有一条,也就是33.33%符合查询结果。 EXPLAIN SELECT * FROM book,author WHERE book.author_id = author.id and author.name ='张三'; ![2020110417523656.png][] ## 8、ref 列 ## ref使用哪个列或者常数和索引一起从表中筛选数据。 EXPLAIN SELECT * FROM author,contact WHERE contact.id = author.contact_id and name ='李四'; ![20201104174856262.png][] ## 9、Extra 列 ## Extra执行计划给出的额外的信息说明,可以根据Extra来进行优化。 ** 1)Using index** Using index 用到了覆盖索引,不需要回表。 ALTER TABLE book ADD INDEX `idx_name_price`(`name`, `price`); EXPLAIN SELECT name,price FROM book WHERE name = 'java'; ![20201104193316170.png][] ** 2)Using where** Using where 使用了 where 过滤,表示存储引擎返回的记录并不是所有的都满足查询条件,需要 在 server 层进行过滤(跟是否使用索引没有关系)。 EXPLAIN SELECT * FROM book WHERE author_id = 1; ![20201104193553836.png][] ** 3)Using index condition** Using index condition索引条件下推 ALTER TABLE book ADD INDEX `idx_name`(`name`); EXPLAIN SELECT * FROM book WHERE name like 'j%'; ![20201104110251325.png][] > https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html ** 4)Using filesort** Using filesort 不能使用索引来排序,用到了额外的排序。这种情况下一般也是要考虑使用索引来优化的。 EXPLAIN SELECT * FROM book order by price; ![20201104193810629.png][] ** 5)Using temporary** Using temporary 表示用到了临时表。mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。 EXPLAIN SELECT DISTINCT(author_id) FROM book; ![20201104193940595.png][] # SQL脚本 # CREATE TABLE `author` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `contact_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `book` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `author_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `contact` ( `id` int(11) NOT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (1, 'java', 99.00, 1); INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (2, 'spring', 80.00, 1); INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (3, 'jvm', 109.00, 2); INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (4, 'mybatis', 66.00, 3); INSERT INTO author(`id`, `name`, `contact_id`) VALUES (1, '张三', 1); INSERT INTO author(`id`, `name`, `contact_id`) VALUES (2, '李四', 2); INSERT INTO author(`id`, `name`, `contact_id`) VALUES (3, '王五', 3); INSERT INTO contact(`id`, `email`) VALUES (1, 'zhangsan@163.com'); INSERT INTO contact(`id`, `email`) VALUES (2, 'lisi@qq.com'); INSERT INTO contact(`id`, `email`) VALUES (3, 'wangwu@sina.com'); [MySQL _ MySQL 5.7 Reference Manual _ 8.8.2 EXPLAIN Output Format]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html [20201104153652628.png]: /images/20221120/fca64c5a663840b089ae0c38c4a9cbfc.png [20201104153257543.png]: /images/20221120/53cfd261797a498c8e031e18ec18ef43.png [20201104153436266.png]: /images/20221120/057c77e2a1d74b45b5ba24ac5722a2f6.png [2020110315384526.png]: /images/20221120/84761ca5cbd2456896783a9601c7455d.png [2020110315453415.png]: /images/20221120/d609dd5df1d34a6280af5ca3b0f2b273.png [2020110316515094.png]: /images/20221120/34b5191317a34eb9affc1b86accdaa5b.png [20201103165757987.png]: /images/20221120/f78ada68baf74de591b7564aa7725d6d.png [const]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_const [2020110319250157.png]: /images/20221120/45726c52c6314c849de21975f92cb36a.png [20201103194207159.png]: /images/20221120/8b5ca325306e48369f949333564f38ae.png [2020110319521445.png]: /images/20221120/cffb2a888fcf4eed91814aa267f36852.png [2020110319572987.png]: /images/20221120/9d2aa86fe0b3454ca0fb4eeea6eff968.png [20201103195819164.png]: /images/20221120/2b423e2d299e4836a4803c439e57c948.png [20201103200457522.png]: /images/20221120/1ef96bc4a12648dc80d3d2090ec4fde4.png [20201103200618700.png]: /images/20221120/f3c47206649f4863bcf46828af8e20c7.png [20201103200835341.png]: /images/20221120/096ff1bcbce2474bbed811696effa495.png [20201104094757754.png]: /images/20221120/2ad568c5ffa94c5f8d5f5d7134af8ddc.png [20201104095138301.png]: /images/20221120/53d29616b7e5499bbd486fcda89353fd.png [Explain_key_len]: https://blog.csdn.net/zhangchaoyang/article/details/109501696 [20201104104400895.png]: /images/20221120/7ab8291369a2405cb24ec66874bae0e2.png [20201104104728564.png]: /images/20221120/7b049eed58ab4755b7cb93371f6198b2.png [20201104175233252.png]: /images/20221120/717fba081a754a6caf4aada24ec5d57c.png [2020110417523656.png]: /images/20221120/f11ca46ecc4a4553b1f9342865686335.png [20201104174856262.png]: /images/20221120/75b976bb8abb41ba9a83ad75009e9a9a.png [20201104193316170.png]: /images/20221120/a6be8c39f0cf42c987d4922bd7657920.png [20201104193553836.png]: /images/20221120/4dea748c6da54f6b85202a5aa25b7bde.png [20201104110251325.png]: /images/20221120/34cea676310647ef9fe9c9a84c729a3b.png [20201104193810629.png]: https://img-blog.csdnimg.cn/20201104193810629.png [20201104193940595.png]: https://img-blog.csdnimg.cn/20201104193940595.png
还没有评论,来说两句吧...