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

MySQL索引对读写的影响

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

一、减慢了写入的速度

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

二、加快了读出的速度

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

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

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

普通索引

普通索引的原理就比较简单了，通过有序的平衡树查找而已，这里就不再赘述。大概就是下面的结构，然后查询：

但是，如果在某一列建立了索引，那么它只对该列的查找有效，比如在xuser_name这一列上建立了索引，那么它只对查询条件为xuser_name='xiang’这种查询语句有效。
示例：
查询xuser_name = 'xiang’的数据行
select * from xuser where xuser_name=‘xiang’;
show PROFILES
未创建前的运行速度：

创建索引
create index normal_test on xuser(xuser_name)
创建索引后的执行速度

是组合索引（最左原则）

因为并不是所有的组合索引都是有效的。
比如：在xuser表中，建立了联合索引index(xuser_sex,xuser_name,xuser_age)，然后想要查询xuser_sex =‘1’ and xuser_age='18’的记录行，是几乎没有优化效果的。
因为联合索引是按顺序来的，联合索引的数据结构大概是下面这种 （这是最左原则）

想要查询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、联合索引的顺序，要将经常要用的、选择性高的放在前面。