MySQL索引

最新推荐文章于 2021-12-16 17:24:13 发布

杨某人信了你的邪

最新推荐文章于 2021-12-16 17:24:13 发布

阅读量531

点赞数

分类专栏：数据库文章标签： mysql 索引

本文链接：https://blog.csdn.net/Remember_Z/article/details/117235124

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

最近在补mysql索引相关的知识，看了一些视频以及博客，看的同时也做了一些笔记，便于加深记忆。涉及的内容大致有索引介绍、B树和B+树、InnoDB和MyISAM引擎、回表、索引覆盖、最左匹配、索引下推、索引的匹配方式、索引失效案例以及如何进行索引优化等。

一、mysql常用引擎

InnoDB：B+树
MyIASM：B+树
MEMORY：Hash

InnoDB也支持hash，只是它是自适应hash，用户无法手动干预。

二、为何不使用二叉树或红黑树

二叉树或红黑树，会因为树的深度过深而造成io次数变多，影响数据读取效率。

计算机系统是分页读取和存储的，一般一页为4KB（8个扇区，每个扇区125B，8*125B=4KB），每次读取和存取的最小单元为一页，而磁盘预读时通常会读取页的整倍数。根据文章上述的【局部性原理】①当一个数据被用到时，其附近的数据也通常会马上被使用。②程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），所以即使只需要读取一个字节，磁盘也会读取一页的数据。平时磁盘所说的4k对齐说的就是这个。

至于磁盘分页，参考计算机操作系统的分页，分段存储管理——逻辑地址和物理地址被分为大小相同的页面，逻辑地址中叫页，物理地址中叫块。

参考：https://blog.csdn.net/xd_1437/article/details/103253632

InnoDB默认一次从磁盘读取16KB的数据，即4K*4

三、B树

如上，假设一条数据占1KB，即data的大小为1KB，在不考虑键值（紫色）和指针（绿色）的大小时，因为InnoDB一个磁盘块的大小是16KB，所以第一层，最多16条数据，也就是一个磁盘块16条数据，一个磁盘块对应16个键值和16个指针，每个指针对应一个磁盘块，所以第二层最多为16个磁盘块，第三层最多为16x16=64个磁盘块，也就是说三层B树，可存储的最大数据量为16x16x16=4096个数据，这还是忽略了键值和指针的大小之后的。

四、B+树

如上，B+树，将所有的数据全部放到了叶子节点中，键值会存在重复。假设一个指针（红色）与一个键值（绿色）的大小之和为10B，则第一层一个磁盘块共16KB就可以存16000/10=1600个指针加键值的组合，而每个指针又指向第二层的一个磁盘块，所以第二层最多有1600个磁盘块，第二层的每一个指针都指向第三层的一个磁盘块，第三层的一个磁盘块最多可存16KB/1KB=16个数据，所以三层的B+树最多可存储1600x1600x16的数据，是B树的将近10000倍。

五、树的度（阶）degree

先解释一下树的度和结点数的关系

叶子结点就是没有孩子的结点，其度为0，度为二的结点是指有两个子数的结点。比如一棵完全二叉树有三层，叶子结点就是最下面那一层的结点数，没有孩子结点，就是4，度为二的结点有3个。

实际中，如上面的B+树，根结点的度数，是由索引字段决定的，假如某个int类型键值（加指针）大小为s，则根结点度数就是16KB/s。所以在设计索引的时候，需要考虑每个键值所占空间。比如int类型、varchar类型、text类型，在创建索引的时候，肯定是首选int类型来创建索引，其他的类型会占用更多的空间，导致度变小，自然就会加深树的深度，IO次数自然也会增加。

六、InnoDB与MyISAM的B+树节点区别

InnoDB通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6子节的_rowid来作为主键。

如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，这个过程叫做回表。

如果是MyISAM，则叶子节点中存放的是数据的地址。

因此对于InnoDB，一个表对应两个文件，tableName.frm和tableName.idb，frm存储的是表的一个结构，而idb存储的是表的索引和数据。

MyISAM则对应三个文件，tableName.frm、tableName.MYD、tableName.MYI，frm与InnoDB一样，存储的是表的结构，MYI存储的是索引，MYD存储的才是真正的数据。

七、为啥建议自增主键

B+树中，如果是自增主键，那么在查询数据时的索引效率自然不必说，在插入数据的时候，每次都是往最后面插入，如果是乱序的主键，则在插入的数据就有很大可能会往中间的某个块进行插入，如果这个块已经满了，那么就涉及到页分裂，也就意味着有页合并。每次页分裂及页合并都会影响到上级节点，在并发量大的时候，会非常影响性能。所以在单机中强烈推荐自增以减少页分裂和页合并的次数，在分布式中，则大多使用雪花算法来生成主键。

八、回表

假设user表两个字段，一个主键索引id，另一个普通索引name，在使用name字段作为查询条件时，如select * from user where name='张三'，此时会先走name索引对应的B+树，找到对应叶子节点，叶子节点存的是这个数据对应的主键，再通过这个主键去查id字段对应的主键B+树，最终找到对应的数据。

InnoDB有两类索引，聚焦索引（主键索引）和二级索引（非主键索引），如果是主键查询，则根据主键直接就能找到对应的数据，如果使用非主键索引查询，则先走非主键索引树，再走主键索引树，会增加IO次数，效率会变低，所以查询数据时推荐使用主键索引进行查询。

九、索引覆盖

还是接着上面的user表，假设有两个sql语句，第一个是select * from user where name='张三'，第二个是select id from user where name='张三'，这两个sql语句中，第一个会发生回表，但第二个不会发生回表，因为第二个在name字段对应的索引树中的查询结果就是主键id，不需要再到id主键索引树中再查一次了。

再比如user表中有索引idx_nae(name,age,email)，select name,age,email from user也不需要回表，因为查询使用的索引中idx_nae已经包含了所有需要的字段；或者再加一个id字段，select id,name,age,email from user where name = 'Mary'也不会回表，因为id正是索引中存放的结果。

即：只要当前查询语句中的所有字段都在当前使用到的索引中，那就不需要回表。

十、最左匹配

最左匹配涉及到组合索引，假设user表中，创建了一个组合索引idx_n_a(name,age)，索引字段是name和age，会出现下面4种情况：

where name=? and age=?
where name=?
where age=?
where age=? and name=?

最左匹配就类似于查找地址，先查询省再查询市，最后才是查询区。所以在上面的idx_n_a这个索引中，只有走了name才会走age，所以上面的4个语句中，第一个和第二个肯定是会走索引的，第三个不会走索引，但第四个也是会走索引的。

为啥第四个会走呢，原因在于mysql的优化器，优化器，一种是CBO（基于成本的优化），另一种是RBO（基于规则的优化），现在的数据库大多都是CBO。

在上面的第四个语句中，优化器会将name字段和age字段的顺序进行调整，所以最终会走索引。

十一、索引下推

select * from user where name=? and age=?

在没有索引下推之前（5.6还是5.7版本前），mysql会先从存储引擎中拉取数据（根据name进行筛选），拉取到数据后，mysql server再根据age进行数据筛选；

有了索引下推后，拉取数据的时候直接根据name和age来获取数据，不需要server进行数据筛选。

索引下推唯一的缺点就是需要在磁盘上多做数据筛选，原来的筛选是放在内存中的，现在放到了磁盘中进行查找数据的环节，这样做看起来成本较高，但是别忘了，数据是排序的，所有的数据是聚集存放的，所以性能上不会有影响，反而会因为大大减少了IO量而提升性能，而且现在的磁盘性能也远比以前的高。

十二、索引长度计算

索引的长度与字符集有关，如果是utf8，则一个字符对应3个字节，如果是gbk，则两个字节，如果是拉丁，则1个字节，int类型则都是4个字节（跟java中一样）。

所以 varchar(16) 的字段所占索引大小为 16x3=48个字节，如果字段允许NULL，则再加一个字节，为49字节

int(11) 字段所占索引大小为4个字节，如果允许为NULL，则再加一个字节，为5个字节。

十三、索引匹配方式

全值匹配：和索引中的所有列进行匹配 where name=? and age=?
匹配最左前缀：只匹配前面的几列 where name=?
匹配列前缀：可以匹配某一列的值的开头部分

where name like 'J%' 索引生效

where name like '%Y' 索引失效，因为%表示1~n个字符，代表着所有的数据都能匹配到，索引就没意义了。
匹配范围值：查找某一个范围的数据 where name > 'Mary'
精确匹配某一列并范围匹配另一列：可以查询第一列的全部和第二列的部分

假设存在索引 idx_nae(name,age,email)

where name = 'Mary' and age > 18 and email = 'mary@xxx.xx' 会走索引且走了name的全部，age的一部分，email不走
只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是索引覆盖

select name, age, email from user where name = 'Mary' and age = 18 and email = 'mary@xx.xx'或select id from user where name = 'Mary'等类似的

十四、索引案例

1、使用`or`会不会走索引？

假设有表test_abc共3列，a、b、c，字段类型都是int(11)，创建组合索引idx_abc(a,b,c)

-- 使用or，查询所有列或其中某几列，最终会走索引
select * from test_abc where a=1 or b=2
select a from test_abc where a=1 or b=2
-- 或
select a,b,c from test_abc where a=1 or b=2
-- 或 a=1 or a=2
select * from test_abc where a=1 or a=2

现在往test_abc这个表中加一个字段d，类型还是int(11)，此时

-- 下面的语句不会走索引
select * from test_abc where a=1 or b=2 -- c的条件加不加没影响 or c=3
select a,b,c,d from test_abc where a=1 or b=2

-- 下面的sql语句都会走索引
select a from test_abc where a=1 or b=2
select a,b,c from test_abc where a=1 or b=2
select a,b,c from test_abc where a=1 or b=2 or c=3

所以使用or作为条件连接时，索引生效不生效要看具体情况，如果查询的列都在同一个索引中，则最终会走索引。这里强调是同一个索引，假设再加一个索引idx_d(d)，这时，也是不会生效的。

2、使用`in`会不会走索引

还是接着上面的test_abc表，4个字段a、b、c、d都是int(11)，都允许为NULL，有索引idx_abc(a,b,c)，每个字段在索引中长度为5字节。

-- 会走索引，因为要查询的字段都在索引中
select a,b,c from test_abc where a in (1,2,3)
select a,c from test_abc where a in (1,2,3) or b in (2,3,4)

-- 不会走索引，因为要查询的字段中，有部分字段不在索引中
select * from test_abc where a in (1,2,3)
select a,b,d from test_abc where a in (1,2,3)
select d from test_abc where a in (1,2,3)

如上，使用in与使用or一样，都会根据要查询的列是否都在同一个索引中，如果在则走索引，如果不在，则不走索引。

3、模糊搜索%会不会走索引？

这点在上面第十三点的索引匹配方式中已经提到过了。

4、存在范围（大于小于）查找时哪些字段走索引？

还是以上面的表test_abc为例

-- 会走索引，走了a字段、b的一部分，c不走，到b这里就已经被截断了
select * from test_abc where a =1 and b>1 and c=2

十五、聚簇索引与非聚簇索引

这两个不是索引类型，而是指数据的存储方式，聚簇索引指的是数据行跟相邻的键值紧凑的存储在一起，非聚簇索引则是将数据文件跟索引文件分开存放。

所以InnoDB是聚簇索引，MyISAM则是非聚簇索引

十六、索引优化细节

还是使用上面的user表作为示例，主键索引id，组合索引idx_nae(name,age,email)

1、表达式优化

当使用索引列进行查询时，尽量不要使用表达式，把计算放到业务层而不是数据库层。即不要使用 select id from user where id + 1 = 5 这样条件的左边含有表达式的。

-- 会走主键索引
select id from user where id=5;
-- 也会走主键索引
select id from user where id=4+1;
-- 会走组合索引idx_nae
select id from user where id+1=6;
-- 不走索引
select * from user where id+1=6;