Mysql索引原理剖析与优化策略

最新推荐文章于 2023-04-24 12:44:10 发布

王者墨霜

最新推荐文章于 2023-04-24 12:44:10 发布

阅读量460

点赞数 1

文章标签： mysql sql 数据库 java 数据结构

本文链接：https://blog.csdn.net/qq_37520724/article/details/112967058

版权

Mysql索引原理剖析与优化策略

1、索引的本质

在⽣产环境中，随着数据量不断的增⻓，SQL执⾏速度会越来越慢，常⻅的⼿段就是通过索引来提升查询速度，那么究竟为什么要添加索引？应该如何正确添加索引？本课题将以MySQL为例，从索引的底层进⾏分析，⼀起探究索引相关的⼀系列问题。

MySQL官⽅对索引的定义为：索引（Index）是帮助MySQL⾼效获取数据的数据结构（有序）。所以，就可以得到索引的本质：索引是有序的数据结构。

1.1、环境

本课题以MySQL5.7为例讲解，使⽤docker进⾏部署：

1.2、索引⽂件

在MySQL中，索引实际上存储在⽂件中的，其位置与数据库数据⽂件在相同的⽬录中。

docker run --name mysql-5.7 -e MYSQL_ROOT_PASSWORD=root -d -p 13306:3306 -v mysql-5.7-data:/var/lib/mysql -v mysql-5.7-conf:/etc/mysql/conf.d mysql:5.7.31

-- 创建测试表

CREATE TABLE `tb_user` ( `id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) DEFAULT NULL,

`password` varchar(20) DEFAULT NULL,

`created` datetime DEFAULT NULL,

PRIMARY KEY (`id`), KEY `username` (`username`) USING BTREE )

ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `tb_user2` ( `id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) DEFAULT NULL,

`password` varchar(20) DEFAULT NULL,

`created` datetime DEFAULT NULL, PRIMARY KEY (`id`) )

ENGINE=MyISAM DEFAULT CHARSET=utf8;

查看数据库⽂件，路径：/var/lib/docker/volumes/mysql-5.7-data/_data/

-rw-r-----. 1 polkitd input 61 1⽉ 21 11:31 db.opt

-rw-r-----. 1 polkitd input 8668 1⽉ 21 11:36 tb_user2.frm #表结构⽂件

-rw-r-----. 1 polkitd input 0 1⽉ 21 11:36 tb_user2.MYD #MyISAM引擎类型的表数据⽂件

-rw-r-----. 1 polkitd input 1024 1⽉ 21 11:36 tb_user2.MYI #MyISAM引擎类型的索引⽂件

-rw-r-----. 1 polkitd input 8668 1⽉ 21 11:33 tb_user.frm #表结构⽂件

-rw-r-----. 1 polkitd input 114688 1⽉ 21 11:34 tb_user.ibd #InnoDB的表空间⽂件，⽤于存储数据以及索引⽂件

1.3、索引示例

此示例中，在左侧表示的2列数据，其中最左侧的为数据的物理地址，真实的地址可能并不是连续的地址。右侧是根据col2建⽴的索引结构，先把此结构看作是⼆叉树结构，每个节点分别包含索引键值和⼀个指向对应数据记录物理地址的指针。

查找数据：

在没有索引的情况下，会全表扫描数据，其时间复杂度为：O(n)，值为：7

在索引（⼆叉树）情况下，查找数据的时间复杂度为：O(log2n)，值为： 2.8073549221

可⻅，基于索引的查询要更快⼀些。

时间复杂度是指执⾏算法所需要的计算⼯作量，⼀般⽤大O符号表述。

由于二分查找每次查询都是从数组中间切开查询，所以每次查询，剩余的查询数为上⼀次的⼀半，从下表可以清晰的看出查询次数与剩余元素数量对应关系。

第⼏次查询	剩余待查询元素数量
1	N/2
2	N/(2^2)
3	N/(2^3)
…	…
K	N/(2^K)

从上表可以看出N/(2^K)肯定是⼤于等于1，也就是N/(2^K)>=1，我们计算时间复杂度是按照最坏的情况进⾏计算，也就是是查到剩余最后⼀个数才查到我们想要的数据，也就是N/(2^K)=1 => N=2^K => K=log2N

需要注意的是，数据库系统⼏乎没有使⽤⼆叉查找树、红⿊树实现的，在MySQL中是使⽤的是B+Tree。

2、数据结构与算法

算法的动态演示地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

2.1、⼆分查找法

⼆分法查找，也称为折半法，是⼀种在有序数组中查找特定元素的搜索算法。

⼆分法查找的思路如下：

（1）⾸先，从数组的中间元素开始搜索，如果该元素正好是⽬标元素，则搜索过程结束，否则执⾏下⼀步。

（2）如果⽬标元素⼤于/⼩于中间元素，则在数组⼤于/⼩于中间元素的那⼀半区域查找，然后重复步骤（1）的操作。

（3）如果某⼀步数组为空，则表示找不到⽬标元素。例如，有2、5、9、12、18、25、34、58、75、86、99这11个数，如果想要查找86这个数，其查找过程如下：

可以看到，通过⼆分法查找，经过了3次查找就找到了⽬标，如果按照顺序查找的话需要10次才能找到。显然，⼆分查找法要⽐顺序查找效率更⾼（平均）。

动态演示地址：https://www.cs.usfca.edu/~galles/visualization/Search.html

⼆分查找法⼀般都是在数组中操作，⽽数组的⻓度往往是不能改变的，那么，是否存在⼀种数据结构可以既⽀持⼆分查找，⼜⽀持动态变化⼤⼩呢？答案是肯定的，那就是⼆叉树。

2.2、⼆叉树

⼆叉树（binary tree）是指树中节点的度不⼤于2的有序树，它是⼀种最简单且最重要的树。如下图：

在⼆叉数中，左⼦树的键值总数⼩于根的键值，右⼦树的键值总数⼤于根的键值。图中的键值排序输出为：2、4、5、6、8、9、10。

⼀般情况下，⼆叉树查找效率要⾼于顺序查找，但是也有特殊情况如下：

在该树中，如果查找8元素，其执⾏效率和顺序查找差不多了。为了解决这个问题，就需要将⼆叉数做平衡操作。

2.3、平衡⼆叉树

平衡⼆叉树（Balanced Binary Tree）⼜被称为AVL树，且具有以下性质：它是⼀棵空树或它的左右两个⼦树的⾼度差的绝对值不超过1，并且左右两个⼦树都是⼀棵平衡⼆叉树。

这个⽅案很好的解决了⼆叉查找树退化成链表的问题，把插⼊，查找，删除的时间复杂度最好情况和最坏情况都维持在O(log2N)。但是频繁旋转会使插⼊和删除牺牲掉O(log2N)左右的时间，不过相对⼆叉查找树来说，时间上稳定了很多。

动态演示地址：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

2.4、红⿊树

红⿊树是⼀种平衡⼆叉查找树的变体，它的左右⼦树⾼差有可能⼤于 1，所以红⿊树不是严格意义上的平衡⼆叉树（AVL），但对之进⾏平衡的代价较低。

由于每⼀颗红⿊树都是⼀颗⼆叉排序树，因此，在对红⿊树进⾏查找时，可以采⽤运⽤于普通⼆叉排序树上的查找算法，在查找过程中不需要颜⾊信息。

特征：

节点是红⾊或⿊⾊。

根节点是⿊⾊。

所有叶⼦都是⿊⾊。（叶⼦是NUIL节点）

每个红⾊节点的两个⼦节点都是⿊⾊。（从每个叶⼦到根的所有路径上不能有两个连续的红⾊节点）

从任⼀节点到其每个叶⼦的所有路径都包含相同数⽬的⿊⾊节点。

动态演示地址：https://www.cs.usfca.edu/~galles/visualization/RedBlack.html

2.5、B-Tree

B-Tree是为了磁盘或其它存储设备⽽设计的⼀种多叉平衡查找树，相对于⼆叉树，B-Tree每个内节点有多个分⽀，即多叉。

B-Tree中的2个概念：

度数：在树中，每个节点的⼦节点（⼦树）的个数就称为该节点的度（degree）。

阶数：定义为⼀个节点的⼦节点数⽬的最⼤值。

m阶B-Tree满⾜以下条件：

每个节点最多拥有m个⼦树

根节点⾄少有2个⼦树

分⽀节点⾄少拥有m/2颗⼦树（除根节点和叶⼦节点外都是分⽀节点）

所有叶⼦节点都在同⼀层、每个节点最多可以有m-1个key，并且以升序排列

每个⾮叶⼦节点由n-1个key和n个指针组成，key和指针互相间隔，节点两端是指针

下图展现是3阶B-Tree的示意图：

实际上，每个节点除了键值以外还包含数据，如下：

如果选⽤B-Tree作为索引存储的话（⽐如：MongoDB），每⼀个节点包含指针、数据都存储到⼀个磁盘块中（NTFS⽂件系统中，默认8个扇区组成⼀个簇，⼤⼩为4KB）：

B-Tree相对较⼆叉树的优势在于，⼀次读取⼀个磁盘块数据中包含了多个key数据，这样就可以在内存中做⽐较操作，减少了磁盘IO的操作。

插⼊或者删除元素都会导致节点发⽣裂变反应，有时候会⾮常麻烦，但正因为如此才让B树能够始终保持多路平衡，这也是B树⾃身的⼀个优势：⾃平衡；

B树主要应⽤于⽂件系统以及部分数据库索引，如MongoDB，⼤部分关系型数据库索引则是使⽤B+树实现。

2.6、B+Tree

这是⼀个3阶的B+Tree示意图：

B+Tree是在B-Tree基础上的⼀种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是⽤ B+Tree实现其索引结构。 B-Tree结构中可以看到每个节点中不仅包含数据的key值，还有data值。⽽每⼀个块的存储空间是有限的，如果data数据较⼤时将会导致每个节点（即⼀个块）能存储的key的数量很⼩，当存储的数据量很⼤时同样会导致B-Tree的深度较⼤，增⼤查询时的磁盘I/O次数，进⽽影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值⼤⼩顺序存放在同⼀层的叶⼦节点上，⽽⾮叶⼦节点上只存储key值信息，这样可以⼤⼤加⼤每个节点存储的key值数量，降低B+Tree的⾼度。

B+Tree相对于B-Tree有⼏点不同：

⾮叶⼦节点只存储键值信息。

所有叶⼦节点之间都有⼀个链指针。

数据记录都存放在叶⼦节点中。

在B+Tree中，所有叶⼦节点（即数据节点）之间是⼀种链式环结构。因此可以对B+Tree进⾏两种查找运算：

⼀种是对于主键的范围查找和分⻚查找，

另⼀种是从根节点开始，进⾏随机查找。

B+树深⼊剖析

3.1、MySQL索引为什么要使⽤B+树

⾸先需要明确的是，B树或B+树要⽐⼆叉树更适合作为索引存储，因为B树中的节点可以存储多个数据，从⽽就可以减少树的⾼度，也就减少了提升了查找性能。

那么在MySQL中为什么选择B+树⽽不选择使⽤B树呢？主要原因体现在3个⽅⾯：

B+树的磁盘读写代价更低 B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更⼩，如果把所有同⼀内部节点的关键字存放在同⼀盘块中，那么盘块所能容纳的关键字数量也越多，⼀次性读⼊内存的需要查找的关键字也就越多，相对IO读写次数就降低了。 B+树的查询效率更加稳定由于⾮终节点并不是最终指向⽂件内容的节点，⽽只是叶⼦节点中关键字的索引。所以任何关键字的查找必须⾛⼀条从根节点到叶⼦节点的路。所有关键字查询的路径⻓度相同，导致每⼀个数据的查询效率相当。由于B+树的数据都存储在叶⼦节点中，分⽀节点均为索引，⽅便扫库，只需要扫⼀遍叶⼦节点即可，但是B树因为其分⽀节点同样存储着数据，我们要找到具体的数据，需要从根节点按序开始扫描，所以B+树更加适合在区间查询的情况，所以通常B+树⽤于数据库索引。

3.2、MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现⽅式是不同的，下⾯我们针对 MyISAM和InnoDB两个存储引擎的索引实现⽅式进⾏讨论学习。

3.2.1、MyISAM索引实现

MyISAM引擎使⽤B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这⾥设表⼀共有三列，假设我们以Col1为主键，上图是⼀个MyISAM表的主索引（Primary key）示意。

可以看出MyISAM的索引⽂件仅仅保存数据记录的地址，在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯⼀的，⽽辅助索引的key可以重复。如果我们在Col2上建⽴⼀个辅助索引，则此索引的结构如下图所示：

同样也是⼀颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为⾸先按照 B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引⽅式也叫做“⾮聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

3.2.2、InnoDB索引实现

InnoDB中的索引结构与MyISAM的索引结构有很⼤的不同。第⼀个重⼤区别是InnoDB的数据⽂件本身就是索引⽂件。在MyISAM中，索引⽂件和数据⽂件是分离的，索引⽂件仅保存数据记录的地址。在InnoDB中，表数据⽂件本身就是按B+Tree组织的⼀个索引结构，这棵树的叶节点data域保存了完整的数据记录，这个索引的key是数据表的主键，所以InnoDB表数据⽂件本身就是主索引。

从图中可以看到，叶⼦节点包含了完整的数据记录。这种索引叫做聚集索引。

因为InnoDB的数据⽂件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会⾃动选择⼀个可以唯⼀标识数据记录的列作为主键，如果不存在这种列，则MySQL⾃动为InnoDB表⽣成⼀个隐含字段作为主键，这个字段⻓度为6个字节，类型为⻓整形。（⾯试考题）

第⼆个与MyISAM索引的不同是，InnoDB的辅助索引data域存储相应记录主键的值⽽不是地址。换句话说，InnoDB的所有辅助索引都引⽤主键作为data域。例如，下图为定义在Col3上的⼀个辅助索引：

这⾥以英⽂字符的ASCII码作为⽐较准则。聚集索引这种实现⽅式使得按主键的搜索⼗分⾼效，但是辅助索引搜索需要检索两遍索引：⾸先检索辅助索引获得主键，然后⽤主键到主索引中检索获得记录。

InnoDB的B+ 树索引的特点是⾼扇出性，因此⼀般树的⾼度为2~4层，这样我们在查找⼀条记录时只⽤I/O 2~4次。当前机械硬盘每秒⾄少100次I/O/s，因此查询时间只需0.02~0.04s。

4、索引的使⽤

4.1、主键索引

在Innodb存储引擎中，每张表都会有主键，数据按照主键顺序组织存放，如果表定义时没有显式定义主键，则会按照以下⽅式选择或创建主键：

(1)先判断表中是否有"⾮空的唯⼀索引"，如果有

如果仅有⼀条"⾮空唯⼀索引"，则该索引为主键

如果有多条"⾮空唯⼀索引"，根据索引索引的先后顺序，选择第⼀个定义的⾮空唯⼀索引为主键。

(2)如果表中⽆"⾮空唯⼀索引"，则⾃动创建⼀个6字节⼤⼩的指针作为主键，但是该主键是不能被查询的。

测试：

--创建表

CREATE TABLE `tb_test1` (

`id` int(11) NOT NULL, -- ⾮空

`name` varchar(20) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

UNIQUE KEY `id` (`id`) USING BTREE -- 唯⼀索引

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插⼊数据

INSERT INTO `tb_test1` (`id`, `name`, `age`) VALUES ('1', 'zhangsan', '20');

INSERT INTO `tb_test1` (`id`, `name`, `age`) VALUES ('2', 'lisi', '21');

--查询，_rowid就是视为主键，如果表⾥设置了主键之后，_rowid就是对应主键。 SELECT *, _rowid FROM tb_test1;

--查询结果可以看到_rowid的值与id值相同

+----+----------+------+--------+

| id | name | age | _rowid |

+----+----------+------+--------+

| 1 | zhangsan | 20 | 1 |

| 2 | lisi | 21 | 2 |

+----+----------+------+---------+

--⽆索引的表测试

CREATE TABLE `tb_test2` (

`id` int(11) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

`age` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--查询

SELECT *, _rowid FROM tb_test2;

--出错：[Err] 1054 - Unknown column '_rowid' in 'field list'

#解决错误

#[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

vim /var/lib/docker/volumes/mysql-5.7-conf/_data/mysql.cnf

#写入如下配置

[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY _ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#重启容器

docker restart mysql-5.7

4.2、联合索引

联合索引就是将表中的多个列⼀起进⾏索引，需要注意的是，联合索引是有顺序的，⽐如：A、B、C列的索引与A、C、B列的索引是不⼀样的。

4.2.1、表数据

--创建表

CREATE TABLE `tb_contact` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`index_code` varchar(1) DEFAULT NULL COMMENT '索引编号',

`surname` varchar(5) DEFAULT NULL COMMENT '姓',

`name` varchar(10) DEFAULT NULL COMMENT '名',

`mobile_code` varchar(11) DEFAULT NULL COMMENT '⼿机号',

`created` datetime DEFAULT NULL,

`updated` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `index_code` (`index_code`,`surname`,`name`

) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

--插⼊测试数据

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('1', 'Z', 'zhang', 'san', '13911111111', '2020-09-01 15:33:21', '2020-09-01 15:33:15');

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('2', 'L', 'li', 'si', '13922222222', '2020-09-02 15:33:29', '2020-09-02 15:33:25');

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('3', 'W', 'wang', 'wu', '13933333333', '2020-09-03 15:33:37', '2020-09-03 15:33:34');

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('4', 'Z', 'zhao', 'liu', '13944444444', '2020-09-04 15:33:43', '2020-09-04 15:33:40');

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('5', 'L', 'liu', 'hulan', '13955555555', '2020-09-05 15:33:52', '2020-09-05 15:33:47');

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('6', 'L', 'lei', 'jun', '13966666666', '2020-09-06 15:34:02', '2020-09-06 15:33:57');

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('7', 'M', 'ma', 'yun', '13977777777', '2020-09-07 15:34:12', '2020-09-07 15:34:08');

INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`, `created`, `updated`) VALUES ('8', 'Q', 'qian', 'laoda', '13988888888', '2020-09-08 15:34:18', '2020-09-08 15:34:15');

在联系⼈表中，id为主键，index_code,surname,name这三个字段建⽴了联合索引。

4.2.2、底层数据结构

联合索引的底层结构也是基于B+树的，其结构示意图如下：

InnoDB会使⽤主键索引在B+树维护索引和数据⽂件，然后我们创建了⼀个联合索引（index_code,surname,name）也会⽣成⼀个索引树，同样是B+树的结构，只不过它的data部分存储的是联合索引所在⾏的主键值。

对于联合索引来说只不过⽐单值索引多了⼏列，⽽这些索引列全都出现在索引树上。对于联合索引，存储引擎会⾸先根据第⼀个索引列排序，如上图中第⼀个索引列，如：L、L、L、Q、M、W、Z、Z 是根据英⽂字⺟正序排序的；

如果第⼀列相等则再根据第⼆列排序，依次类推就构成了上图的索引树，如：L lei jun 、L li si、L liu hulan等。

4.2.3、联合索引的查询

如果查询mayun⽤户的⼿机号码，需要执⾏的SQL为：

SELECT * FROM tb_contact WHERE index_code = 'M' AND surname = 'ma' AND name = 'yun'

联合索引的执⾏过程如下：

⾸先从根节点开始查找，根节点⼀般是常驻内存中的，第⼀个列为index_code，其值为：M，在L与W之间，会继续向⼦节点查询：

在查找到⼦节点后，将⼦节点数据从磁盘加载到内存中，采⽤⼆分法进⾏查找，找到M ma yun数据符合条件，再继续查找⼦节点，读取到⼦节点中的data数据，其数据就是这条记录的主键，然后再通过主键索引查询数据，最终将在主键索引中查询到数据：

4.2.4、最左前缀原则

在使⽤联合索引时，必须按照索引的顺序查询，例如：

SELECT * FROM tb_contact WHERE index_code = 'M' AND surname = 'ma' AND name = 'yun' --索引会⽣效

SELECT * FROM tb_contact WHERE index_code = 'M' AND surname = 'ma' --索引会⽣效

SELECT * FROM tb_contact WHERE index_code = 'M' --索引会⽣效

SELECT * FROM tb_contact WHERE surname = 'ma' AND name = 'yun' --索引不会⽣效

通过索引编号+姓+名就能定位到⼿机号，因为在索引结构中是排好序的

如果没有使⽤索引编号，那么整体来看就是混乱⽆序的，就⽆法使⽤排好序的索引，所以索引就不会⽣效

这就是最左前缀原则。

4.3、EXPLAIN

使⽤EXPLAIN可以查看SQL语句的执⾏计划，从⽽可以知道SQL的瓶颈在哪⾥，就可以有针对性的进⾏优化了。

⽤法：

EXPLAIN <SELECT语句>

--举例：

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'M'

上图所展现出的就是其执⾏计划中的信息，根据这些信息进⾏SQL性能的判断。

在查询中的每个表会输出⼀⾏信息，如果有两个表通过 join 连接查询，那么会输出两⾏，表的含义⽐较⼴泛：可以是⼦查询、⼀个 union 结果等。需要注意的是，在MySQL5.7.3以后的版本，EXPLAIN已经默认添加了EXTENDED参数，在结果中添加了 filtered结果字段，filtered是指返回结果的⾏占需要读到的⾏(rows列的值)的百分⽐。紧跟着执⾏SHOW WARNINGS;语句查看优化后的查询语句。

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'M' AND surname = 'ma'; SHOW WARNINGS;

⼀般⽽⾔，优化的结果建议并不准确，仅做参考。

4.3.1、id列表

id列的编号是 select 的序列号，有⼏个 select 就有⼏个id，并且id的顺序是按 select 出现的顺序增⻓的。其值越⼤执⾏优先级越⾼，id相同则从上往下执⾏，id为NULL最后执⾏。

--⼦查询

EXPLAIN SELECT (SELECT 1 FROM tb_contact LIMIT 1) FROM tb_contact

结果：

4.3.2、select_type

表示SELECT语句的类型。

有以下⼏种值：

SIMPLE：表示简单查询，其中不包含连接查询和⼦查询。

2、 PRIMARY：表示主查询，或者是最外⾯的查询语句。

3、 UNION：表示连接查询的第2个或后⾯的查询语句。

4、 DEPENDENT UNION：UNION中的第⼆个或后⾯的SELECT语句，取决于外⾯的查询。

5、 UNION RESULT：连接查询的结果。

6、 SUBQUERY：⼦查询中的第1个SELECT语句。

7、 DEPENDENT SUBQUERY：⼦查询中的第1个SELECT语句，取决于外⾯的查询。

8、 DERIVED：SELECT(FROM ⼦句的⼦查询)。

4.3.3、table

计划执⾏的表，当 from ⼦句中有⼦查询时，table列是<derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执⾏ id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select ⾏id。

4.3.4、type

这⼀列表示关联类型或访问类型，即MySQL决定如何查找表中的⾏，查找数据⾏记录的⼤概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

system

表仅有⼀⾏，这是const类型的特列，平时不会出现，这个也可以忽略不计。

const

数据表最多只有⼀个匹配⾏，因为只匹配⼀⾏数据，所以很快，常⽤于PRIMARY KEY或者 UNIQUE索引的查询，可理解为const是最优化的。

eq_ref

primary key 或 unique key 索引的所有部分被连接使⽤，最多只会返回⼀条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

ref

相⽐ eq_ref，不使⽤唯⼀索引，⽽是使⽤普通索引或者唯⼀性索引的部分前缀，索引要和某个值相⽐较，可能会找到多个符合条件的⾏。

range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使⽤⼀个索引来检索给定范围的⾏。

Index

和ALL⼀样，不同就是mysql只需扫描索引树，这通常⽐ALL快⼀些。

ALL

即全表扫描，意味着mysql需要从头到尾去查找所需要的⾏。通常情况下这需要增加索引来进⾏优化了

4.3.5、possible_keys

这⼀列显示查询可能使⽤哪些索引来查找。

explain 时可能出现 possible_keys 有列，⽽ key 显示 NULL 的情况，这种情况是因为表中数据不多， mysql认为索引对此查询帮助不⼤，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where ⼦句看是否可以创造⼀个适当的索引来提⾼查询性能，然后⽤ explain 查看效果。

4.3.6、key

显示MySQL实际决定使⽤的键(索引)。如果没有选择索引,键是NULL。

可以强制使⽤索引或者忽略索引：

4.3.7、key_len

这⼀列显示了mysql在索引⾥使⽤的字节数，通过这个值可以算出具体使⽤了索引中的哪些列。 key_len计算规则如下：

字符串

char(n)：n字节⻓度

varchar(n)：2字节存储字符串⻓度，如果是utf-8，则⻓度 3n + 2

数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节　　

时间类型　

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最⼤⻓度是768字节，当字符串过⻓时，mysql会做⼀个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

4.3.8、ref

这⼀列显示了在key列记录的索引中，表查找值所⽤到的列或常量，常⻅的有：const（常量），func， NULL，字段名（例：film.id）

4.3.9、rows

这⼀列是mysql估计要读取并检测的⾏数，注意这个不是结果集⾥的⾏数。

4.3.10、Extra

这⼀列展示的是额外信息。常⻅的重要值如下：

Distinct：MySQL发现第1个匹配⾏后,停⽌为当前的⾏组合搜索更多的⾏。

Not exists：MySQL能够对查询进⾏LEFT JOIN优化,发现1个匹配LEFT JOIN标准的⾏后,不再为前⾯的的⾏组合在该表内检查更多的⾏。

range checked for each record (index map: #)：MySQL没有发现好的可以使⽤的索引,但发现如果来⾃前⾯的表的列值已知,可能部分索引可以使⽤。

Using filesort（重点）：MySQL会对结果使⽤⼀个外部索引排序，⽽不是按索引次序从表⾥读取⾏。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和⾏指针，然后排序关键字并按顺序检索⾏信息。这种情况下⼀般也是要考虑使⽤索引来优化的。

Using index（重点）：从只使⽤索引树中的信息⽽不需要进⼀步搜索读取实际的⾏来检索表中的列信息。

Using temporary（重点）：MySQL需要创建⼀张临时表来处理查询。出现这种情况⼀般是要进⾏优化的，⾸先是想到⽤索引来优化。

Using where：MySQL服务器将在存储引擎检索⾏后再进⾏过滤。就是先读取整⾏数据，再按 where 条件进⾏检查，符合就留下，不符合就丢弃。

Using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中是⼀个前导列的范围；

Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。

Using index for group-by：类似于访问表的Using index⽅式,Using index for group-by表示 MySQL发现了⼀个索引,可以⽤来查询GROUP BY或DISTINCT查询的所有列,⽽不要额外搜索硬盘访问实际的表。

NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着⽤到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地⽤到了索引，也不是完全没⽤到索引

4.4、覆盖索引与回表查询

什么是回表查询？

先定位主键值，再定位⾏记录，它的性能较扫⼀遍索引树更低，这就是回表查询。

什么是覆盖索引？

只需要在⼀棵索引树上就能获取SQL所需的所有列数据，⽆需回表，速度更快。

举例：

EXPLAIN SELECT * from tb_contact WHERE index_code = 'L'; -- 回表查询

EXPLAIN SELECT id,name from tb_contact WHERE index_code = 'L'; -- 覆盖索引

需要回表查询：

using index ：使⽤覆盖索引的时候就会出现

using index condition：查找使⽤了索引，但是需要回表查询数据

EXPLAIN SELECT id,name from tb_contact WHERE index_code = 'Z' ORDER BY mobile_code;

using index & using where：查找使⽤了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

EXPLAIN SELECT id,name from tb_contact WHERE index_code = 'Z' ORDER BY surname;

4.5、实例

4.5.1、实例1

实例1：联合索引中，不能范围条件右侧的索引列

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'L' AND surname = 'lei' AND name = 'jun';

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'L' AND surname > 'lei' AND name = 'jun';

从索引⻓度来看，3个索引都已经⽣效，(3*1+2) + (3*5+2)+(3*10+2)+1+1+1=57

从索引⻓度来看，只⽣效了前2个索引，(3*1+2) + (3*5+2)+1+1=24

原因：在底层的B+tree结构中，在已确定第⼀个索引的情况下，第⼆个索引是有序存储的，那么按照第⼆个索引的范围查找，就不需要第三个索引的参与了。

4.5.2、实例2

实例2：索引条件使⽤<>时，索引会失效

EXPLAIN SELECT * FROM tb_contact WHERE index_code <> 'L'

可以看到，查询类型为ALL，也就是进⾏了全表扫描，因为不等于没有办法使⽤索引结构进⾏查询。

4.5.3、实例3

实例3：Impossible WHERE

EXPLAIN SELECT id FROM tb_contact WHERE id IS NULL

id本身就不会为null，所以条件为IS NULL是不可能发⽣的条件，因此会出现Impossible WHERE，不要误以为是索引不⽣效。

4.5.4、实例4

实例4：like查询

EXPLAIN SELECT * FROM tb_contact WHERE index_code LIKE '%L'; -- 索引不⽣效

EXPLAIN SELECT * FROM tb_contact WHERE index_code LIKE 'L%'; -- 索引⽣效

EXPLAIN SELECT index_code,surname,name FROM tb_contact WHERE index_code LIKE '%L%'; -- 使⽤覆盖索引优化

4.5.5、实例5

实例5：GROUP BY 索引使⽤

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'L' ORDER BY surname; -- 使⽤index_code索引查询，surname索引进⾏排序

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'L' ORDER BY name;

-- 使⽤ index_code索引查询，由于跳过了surname针对name排序，出现了Using filesort

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'L' ORDER BY surname,name; -- 使⽤index_code索引查询，surname,name索引进⾏排序

EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'L' ORDER BY name,surname; -- 使⽤index_code索引查询，name,surname的顺序与索引顺序不⼀致，导致了Using filesort

总结：

MySQL⽀持两种⽅式的排序filesort和index，

Using index是指MySQL扫描索引本身完成排序。

index效率⾼，filesort效率低。

order by满⾜两种情况会使⽤Using index

order by语句使⽤索引最左前列。

使⽤where⼦句与order by⼦句条件列组合满⾜索引最左前列。

尽量在索引列上完成排序，遵循索引建⽴（索引创建的顺序）时的最左前缀原则。

如果order by的条件不在索引列上，就会产⽣Using filesort。

王者墨霜

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mysql索引原理剖析与优化策略

Mysql索引原理剖析与优化策略1、索引的本质在⽣产环境中，随着数据量不断的增⻓，SQL执⾏速度会越来越慢，常⻅的⼿段就是通过索引来提升查询速度，那么究竟为什么要添加索引？应该如何正确添加索引？本课题将以MySQL为例，从索引的底层进⾏分析，⼀起探究索引相关的⼀系列问题。MySQL官⽅对索引的定义为：索引（Index）是帮助MySQL⾼效获取数据的数据结构（有序）。所以，就可以得到索引的本质：索引是有序的数据结构。1.1、环境本课题以MySQL5.7为例讲解，使⽤doc...
复制链接

扫一扫