MySQL最左原则及索引对读写的影响

╰+哭是因爲堅強的太久メ 2022-01-20 22:29 220阅读 0赞

## MySQL索引对读写的影响 ##

我们知道MySQL的索引是B+树的数据结构，但是MySQL的索引到底是如何影响到数据库的效率的呢？主要有两点：

## 一、减慢了写入的速度 ##

几乎所有的人都知道索引可以加快查询（也就是读出）的速度，但并不是所有的人都知道，索引会减慢写的速度（比如之前的我……）。  
之前说过，MySQL的索引是B+树的数据结构，这样的结构可以有效的提升查询的速度，因为它是有序的，而有序的平衡树查找起来是很快的。  
但是在进行增删改的时候，B+树的弊端就出现了。每进行一次增删改的操作时，都要改变一次B+树的结构（当然也可能不变，但是检查一次是否平衡是肯定要的），以保证B+树是有序、平衡的。  
数据量小的时候可能看不出来，但是数据量一旦大了，就会非常的影响性能。  
因此，是不可以无限制的增加索引的，需要根据具体的情况来设计索引。  
比如：如果某个表的增删改操作频率不高，但是查询的频率很高，那么可以根据查询的字段增加索引。而如果某个表的增删改操作频率很高，但是查询的频率很低，那么可以少添加索引或者不添加索引。  
索引的数量当然是越少越好，当然也需要根据具体情况来判断。

## 二、加快了读出的速度 ##

索引最大的优点就是加快了查询的速度，但是索引到底是怎么工作的呢？怎样设计索引才能使得查询效率最高呢？  
注意：如果表中只有一两条的少量数据，那么不建索引快一些，毕竟索引还要走树的结构。但是一两条数据的表也不需要什么鬼查询优化……

首先看看索引的类型：  
1、唯一索引：UNIQUE INDEX ，不允许有重复值，可以为null。  
2、主键索引：是特殊的唯一索引，不允许有null，一张表中只能有一个  
3、全文索引：主要是为查询文本的，像LIKE "%word%"这类模糊查询。  
4、普通索引：INDEX一般的索引。  
5、组合索引：多列一起建立一个索引。

唯一索引和全文索引都是特殊的索引，需要在特定的情况下使用，最常用的就是普通索引和组合索引了。

## 普通索引 ##

普通索引的原理就比较简单了，通过有序的平衡树查找而已，这里就不再赘述。大概就是下面的结构，然后查询：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70]  
但是，如果在某一列建立了索引，那么它只对该列的查找有效，比如在xuser\_name这一列上建立了索引，那么它只对查询条件为xuser\_name='xiang’这种查询语句有效。  
示例：  
查询xuser\_name = 'xiang’的数据行  
select \* from xuser where xuser\_name=‘xiang’;  
show PROFILES  
未创建前的运行速度：  
![在这里插入图片描述][20190606194624370.png]

创建索引  
create index normal\_test on xuser(xuser\_name)  
创建索引后的执行速度  
![在这里插入图片描述][20190606194634663.png]

## 是组合索引（最左原则） ##

因为并不是所有的组合索引都是有效的。  
比如：在xuser表中，建立了联合索引index(xuser\_sex,xuser\_name,xuser\_age)，然后想要查询xuser\_sex =‘1’ and xuser\_age='18’的记录行，是几乎没有优化效果的。  
因为联合索引是按顺序来的，联合索引的数据结构大概是下面这种 （这是最左原则）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70 1]  
想要查询xuser\_sex =‘1’ and xuser\_age='18’的记录行，但是没有中间的xuser\_name搭桥，索引只能遍历所有xuser\_sex=1的记录，找其中xuser\_age=18的,而无法利用树的结构。  
而这个索引对于查找xuser\_sex=‘1’ and xuser\_name='xue’的记录是有效的，因为查到xuser\_sex='1’的记录后，可以直接通过有序的树结构，快速获取到xuser\_name=‘xue’ 的记录，中间没有间隔。  
因此，如果建立了index(a,b,c)，这样的联合索引，它只对abc、ab、a型的查找有效，对于ac、bc、c、ca、cab型等是没有效果或者效果很差的（c型是完全不走索引的；ac型还走了索引，查了a，但是查不了c）。

**当然索引也不限于where的语句。order by的也可以。比如：  
a=‘1’ order by b;这样的查询条件也是可以使用索引的。**

**还有条件的顺序是可以变化的，比如where b=1 and a=0，这种也是可以用到索引的，因为MySQL会自动进行优化，尽量使用索引**

更加详细的见：  
https://www.cnblogs.com/joyber/p/4349604.html

## 优化索引 ##

1、越少越好，针对实际情况，选择最需要的索引。  
2、索引上不能使用表达式或函数，比如a字段上有索引，where 中有sub(a,…) =1，这样是不会使用a的索引的。但是a=sub(xxx)，这样的是可以的，它可以走索引。  
3、联合索引的顺序，要将经常要用的、选择性高的放在前面。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70]: /images/20220121/017fdcc64ec44fa186952df6802d7b24.png
[20190606194624370.png]: /images/20220121/91fe5f574d3241c49a3768a81c7ee61c.png
[20190606194634663.png]: /images/20220121/b12c5084ebc54370962fa9abfdcabb3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70 1]: /images/20220121/870ea5116a284a8c8011d45919f53247.png