MySQL索引原理及其优化

迈不过友情╰ 2023-05-29 03:22 15阅读 0赞

### 文章目录 ###

*  MySQL索引原理及其优化
 *   *  一，索引原理
     *   *  1.底层数据结构
         *   *  1.为什么使用B+树实现索引？
         *  2.索引在MyISAM和innodb中的实现
         *   *  索引在MyISAM的实现
             *  索引在innodb中的实现
             *  聚集索引和非聚集索引
         *  3.哈希索引
         *  4.联合索引的B+树如何组织？
     *  二，索引优化
     *   *  1.尽量不要出现重复索引
         *  2.减少冗余索引
         *  3.where子句后边的列索引只能用上1个
         *  4.多列索引必须遵循最左匹配原则
         *  5.尽量使用覆盖索引
         *  6.前导模糊查询不能使用索引
         *  7.union，in，or都能命中索引推荐使用in
         *  8.负向条件查询不能使用索引，可以优化为in查询
         *  9.建立索引的列，不能为null，联合索引不存全为null的值
         *  10.使用索引查询时，避免强制类型转换

# MySQL索引原理及其优化 #

> 之前有写过一篇关于索引的文章，但是感觉写的不好，因此重新总结一下，当做复习，为明年的春招做准备，欢迎互关呀，共同学习，进步！

## 一，索引原理 ##

> 我们之前可能都使用过索引，但是我们有没有了解过索引的底层原理呢？

### 1.底层数据结构 ###

我们知道，**索引帮助MySQL高效获取数据的排好序的数据结构**

既然，索引是一种数据结构，我们了解原理，就有必要吃透这种数据结构

> 在这里我先抛出结论：

**MySQL中的索引是在存储引擎实现的，不同存储引擎对索引的实现可能有所不同，常用的MyISAM和innodb都使用B+树实现索引，但是这两者又有些不同。**

#### 1.为什么使用B+树实现索引？ ####

索引绝大多数情况都是用在查询操作当中，那么我们列举下我们知道的查找算法

**顺序查找：**

![在这里插入图片描述][20191102164258967.png]

对于这样一张表，如果我要做这样的查询

select * from tb_stu where stu_name = 'walker'

如果是顺序查找，那么我就得先从第一行数据，一行一行的比对，运气好的，第一行就找到了符合条件的数据行，运气不好，就得遍历到最后一行才找到，所以这样的时间复杂度为O(N)，N是数据表的行数，当数据表的数据非常多时，效率就会原来越差，IO次数随着增多

**BST二叉搜索树**

有人说，二叉搜索树的时间复杂度可达O(logN)，但是二叉搜索树有可能会退化为左斜树或者右斜树

![在这里插入图片描述][2019110216582092.png]

这样时间复杂度就会有O(logN)变为O(N)

**AVL平衡树**

有人会说，使用AVL平衡树可以避免搜索树退化为左斜树或者右斜树，但是这样又会带来一个问题，当数据量比较大时，树的层次会越来越大，这样即使时间复杂度可以达到O(logN)，但是IO次数是随着树的高度增加而增加的。

**B树**

B树能很好的解决查询效率和IO次数多的问题，但是当我做范围查询：

select * from table where class_id > 5

![img][]

B树这时就不好处理了，因为B树要多次遍历，比如，上边的sql，B树在找到第一个大于5的数据6后，还需要重新从根节点在遍历找第二个大于5的数据，所以这样IO次数也是比较不稳定的。

**B+树：**

针对上边B树的情况，使用变种的B树----B+树可以解决，B树的所有数据都储存在叶子节点，所有叶子节点组成是一条链表

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70]

当我们进行范围查询时，查到叶节点后可以遍历链表，找到我们想要的数据

![img][img 1]

所以，**B+树要比B树具有更稳定的查询效率**，因为B+树树的高度相对稳定，3层的B+树就可以存储百万条数据，所以查询次数也相对稳定O(N)，N是树的高度，在树的高度不是很高的情况下，且加上叶子层的链表，IO次数相对B树也大大减少。

**所以，大多数数据库采用B+树实现索引，但也有用B树实现索引的，例如MongoDB**

### 2.索引在MyISAM和innodb中的实现 ###

以这个表为例，col是主键

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 1]

#### 索引在MyISAM的实现 ####

MyIsam引擎使用B+树来实现索引，在B+树的叶子节点数据域中存放的mysql表数据记录的地址

![åœ¨è¿™é‡Œæ’å¥å›¾ç‰‡æè¿°][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 2]

在col2上建立一个普通索引，也是一样的存放

![åœ¨è¿™é‡Œæ’å¥å›¾ç‰‡æè¿°][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 3]

所以特点就是：**数据和索引是分开存放的**， 在进行sql查询时，会根据查询语句中的索引条件，在b+树中找到相应的叶子节点，取出相应的数据地址，然后根据地址获取相关表记录

#### 索引在innodb中的实现 ####

虽然innerDB也使用b+树作为索引结构，但是实现方式和myisam不同，在主键索引中，b+树叶子节点数据域中存储的是数据库表相关列的完整记录

![åœ¨è¿™é‡Œæ’å¥å›¾ç‰‡æè¿°][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 4]

在其他索引中，比如col3建立了一个普通索引，存放的是该列对应的主键值

![åœ¨è¿™é‡Œæ’å¥å›¾ç‰‡æè¿°][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 5]

这样我们在利用col3查数据时，就会先找出对应的主键值，然后在从主键索引中找出对应的行数据，这种也叫作回表查询， 先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

所以说，**innodb实现的主键索引叶节点保存了完整数据，非主键索引叶节点保存主键值**

**在innodb中实现索引为还是那么必须有主键？**

*  因为innodb中是通过主键索引去组织数据，如果没有主键索引，MySQL会自动找出能唯一标识行数据的一列作为主键列，如果没有则会生成一个默认的隐藏列作为主键，去维护叶数据

**为什么尽量使用整形自增主键？**

*  比如，我现在不使用整形自增主键，我使用uuid作为主键，也能起到主键唯一标识的作用，但是uuid是字符串，字符串在比较上比整形要麻烦，所以还是推荐使用整形主键
 *  对于为什么推荐自增，因为，自增的主键每次都比原来B+树中的主键大，所以，在新增数据时都会将节点添加到最后一个叶子节点，而减少了B+树维护平衡的消耗

#### 聚集索引和非聚集索引 ####

上边提到的

*  在innodb上实现的索引也被称为聚集索引
 *  在MyISAM中实现的索引被称为非聚集索引

对比：

*  聚集索引在进行主索引查询时效率很高，直接找到相关叶子节点就可以找到相关数据记录，非聚集索引还需要根据数据地址去寻找数据记录
 *  但是聚集索引在辅助索引查询时效率低非聚集索引低，因为需要检索两次b+树，第一次找到主键值，第二次根据主键值找到相关记录、

### 3.哈希索引 ###

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能，但是hash适用于 = 或者 in的等值查询

### 4.联合索引的B+树如何组织？ ###

如果是联合索引的话，一个联合索引还是维护一棵B+树，但是这个B+树每一个节点是多个列的，意思就是说，原来独立索引，一个节点一个列嘛，现在联合索引节点是多列的，列的顺序按照联合索引顺序排，比如联合索引key(a，b)，会先检索a，然后在检索b

## 二，索引优化 ##

### 1.尽量不要出现重复索引 ###

第一步

**尽量不要出现重复索引，何为重复索引？重复索引是指相同的列以相同的顺序建立的同类的索引，比如主键和唯一索引**

create table test(
    	id int not null primay key,
        name varchar(10) not null,
    	title varchar(50) not null,
        unique(id)
    )engine = innodb

就像这里边的id既是唯一索引也是主键，就是重复索引.

### 2.减少冗余索引 ###

第二：

**减少冗余索引，什么是冗余索引就是指多个索引的前缀列相同，或是在联合索引中包含了主键索引**

create table test(
    	id int not null primay key,
        name varchar(10) not null,
    	title varchar(50) not null,
        key(name,id)
    )engine = innodb

上边就是在联合索引中包含了主键索引，导致索引冗余，因为innodb的特性，innodb会在没个索引的后边加上主键索引，

**还有前缀列相同就是下边说到的联合索引的情况，**

**建立了联合索引（a，b，c）就没必要建立一个独立索引a**

### 3.where子句后边的列索引只能用上1个 ###

第三：

注意where子句后边的列不能都加上索引

select * from table where age = 1 and price > 100

where子句后边的age和price如果两个都是独立的索引，那么同时只能用上1个，此时应该建立age和price的联合索引

### 4.多列索引必须遵循最左匹配原则 ###

第四：

**多列索引必须遵循最左匹配原则，索引顺序应该遵循离散度大的列放的越前**

> 先说最左匹配原则

所谓最左匹配原则，就是假如我创建了联合索引index(a,b,c)

如果，我在查询中：

where a = 1 and b = 1 and c = 1	#索引中abc三列都走
    where a = 1 and b = 1 #索引中ab两列走
    where a = 1 and c = 1 #索引中ac两列走

所以从上面可以看出，最左匹配中的一个要求：**索引中排第一的列必须出现，索引才会生效**，比如上边是索引生效的组合可以有：

abc
    ab
    ac
    a

所以不生效的如：

bc
    b
    c

上面都是等值查询，下边涉及到范围查询

1. where a = 1 and b = 1 and c > 1
    2. where a = 1 and b > 1 and c = 1

上边的where子句中只有第一句会走索引，走索引中的abc列，第二句不走列c，这涉及到多列索引中第二个要求：**当在查询中出现范围查询时， 存储引擎不能使用索引中范围条件右边的列**

还有，建立了联合索引(a,b,c)就没必要建立独立索引（a）

**如果有order by或者group by的情景，也要注意索引的有序性**

比如：

where a = ? and b = ? order by c

这样可以建立key(a,b,c)的联合索引，order by 最后的字段是组合索引的一部分且放在组合索引最后，避免出现文件排序fileSort

### 5.尽量使用覆盖索引 ###

第五：

\*\*尽量使用覆盖索引（只访问索引的查询（索引列包含的查询列）减少select \*\*\*

比如在登录验证中：

select user_time from user where user_name = ? and password = ?

可以建立key(user\_name,password,user\_time)的联合索引

### 6.前导模糊查询不能使用索引 ###

第六：

**前导模糊查询不能使用索引**

select * from table where title like  '%ax'

该语句属于前导模糊查询，即使title是索引，也不能使用到

**非前导模糊查询可以使用索引**

select * table from title like 'abc%'

### 7.union，in，or都能命中索引推荐使用in ###

第七：

**union，in，or都能命中索引，但是cpu耗费上，union < in < or**

**所以一般推荐使用in**

比如：

select * from table where a = 1
    union
    select * from table where a = 2

直接告诉mysql怎么做，cpu耗费最少，但是一般推荐使用in

select * from table where a in (1,2)

or的话，cpu耗费最大，不建议

select * from table where a = 1 or a = 2

### 8.负向条件查询不能使用索引，可以优化为in查询 ###

第八：

**负向条件查询不能使用索引，可以优化为in查询，负向条件有：!=，< >，not in , not exists , not like**

### 9.建立索引的列，不能为null，联合索引不存全为null的值 ###

第九：

**建立索引的列，不能为null，联合索引不存全为null的值**

### 10.使用索引查询时，避免强制类型转换 ###

第十：

在使用索引查询时，避免强制类型转换，强制类型转换会导致全表扫描，例如，phone本来是varchar类型

select * from table where phone = 123456789

这样就不能命中索引.

应改为：

select * from table where phone = '123456789'

[20191102164258967.png]: https://img-blog.csdnimg.cn/20191102164258967.png
[2019110216582092.png]: https://img-blog.csdnimg.cn/2019110216582092.png
[img]: https://img-blog.csdn.net/20180324232514619?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfcnlhbg==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191102171136500.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ==,size_16,color_FFFFFF,t_70
[img 1]: https://img-blog.csdn.net/20180325111559207?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pfcnlhbg==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191102175254964.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190809210653928.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190809210820897.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190809211253940.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190809211347423.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTkyMjI4OQ==,size_16,color_FFFFFF,t_70