一本彻底搞懂MySQL索引优化EXPLAIN百科全书

1、MySQL逻辑架构

日常在CURD的过程中,都避免不了跟数据库打交道,大多数业务都离不开数据库表的设计和SQL的编写,那如何让你编写的SQL语句性能更优呢?

先来整体看下MySQL逻辑架构图:

MySQL逻辑架构图

MySQL整体逻辑架构图可以分为Server和存储引擎层。

Server层:

Server层涵盖了MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),以及存储过程、触发器、视图等跨存储引擎的实现也在这一层来实现。

连接器:负责跟客户端建立连接、获取权限、维持和管理连接。

分析器:SQL词法分析,SQL语法分析

优化器:索引选择,选择一个执行效率高的,生成执行计划

执行器:操作引擎,返回执行结果

...

查询缓存:执行SQL语句之前,先查缓存,缓存结果可能是以key-value对方式存储的,key 是查询的语句,value 是查询的结果。

存储引擎层:

负责数据的存储和提取,是一种插件式的架构方式。支持 InnoDB、MyISAM、Memory 等多个存储引擎。MySQL 5.5.5版本开始默认存储引擎是 InnoDB,也是目前常用的存储引擎。

今天我们来看下详细看下优化器里的执行计划如何分析,要分析一个 SQL 的执行效率,就要会看执行计划,根据执行计划优化 SQL,使其能达到高效查询的目的。

一条查询语句需要经过 MySQL 查询优化器的各种基于成本和规则,优化后会生成一个所谓的执行计划。

那么这个执行计划主要展示具体执行查询的方式,比如多表连接的顺序是多少,表里包含多个索引,每个表采用什么访问方法来具体执行查询等。

而设计 MySQL 的大佬是非常贴心的,知道开发的朋友们都是亲自写 SQL 的,但是写出 SQL 容易,想写出性能高的 SQL 可不简单。

所以,大佬提供了 Explain 语句来帮我们查询某个查询语句的具体执行计划。

2、SQL 执行计划解析

本文带大家看懂 EXPLAIN 语句,必须要熟悉各项输出是做什么的,从而有针对性的提升SQL 查询语句的性能。

列名 用途
id   每一个SELECT关键字查询语句都对应一个唯一id  
select_type   SELECT关键字对应的查询类型  
table   表名  
partitions   匹配的分区信息  
types   单表的访问方法  
possible_keys   可能用到的索引  
key   实际使用到的索引  
key_len   实际使用到的索引长度  
ref   当使用索引列等值查询时,与索引列进行等值匹配的对象信息  
rows   预估需要读取的记录条数  
filtered   某个表经过条件过滤后剩余的记录条数百分比  
Extra   额外的一些信息  

为了方便解释上面的执行计划各项输出的含义,下面创建三张数据库表。

数据库创建三张表: DROP TABLE IF EXISTS user; CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO user (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18'); DROP TABLE IF EXISTS `group`; CREATE TABLE `group` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `group` (`id`, `name`) VALUES (1,'group1'),(2,'group2'),(3,'group3'); DROP TABLE IF EXISTS user_group; CREATE TABLE `user_group` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `group_id` int(11) NOT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), KEY `idx_group_id` (`group_id`), KEY `idx_user_group_id` (`user_id`,`group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO user_group (`id`, `user_id`, `group_id`, `remark`) VALUES (1,1,1,'bak1'), (2,2,2,'bak2'), (3,3,3,'bak3'); EXPLAIN 执行计划参数详解:

下载了最新的 MySQL8.0+ 版本,直接执行 EXPLAIN ,对比了 MySQL 5.0+ 版本执行的 EXPLAIN EXTENDED 命令同样都提供了一些查询优化的信息。除了执行计划各项输出参数外,额外还有 filtered 列,是一个百分比的值,rows * filtered/100 可以估算出将要和 EXPLAIN 中前一个表进行连接的行数 。

如下所示:

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zyxwsz.html