mysql索引查询_聊聊Mysql索引查询技巧

mysql的索引是日常开发中用到比较多的概念，对于千万级的表来说，能充分的利用索引，便能充分的提高查询效率。之前都是用到什么就查什么，没有一个总体汇总的体系。索引使用的经验真的是和自己实际的使用关系很大，以InnoDB为例简单的总结一下：

1.索引优点

索引大大减小了服务器需要扫描的数据量。

索引可以帮助服务器避免排序和临时表。

索引可以将随机I/O变为顺序I/O。

2.主键查询走索引

对于索引来说，最基本的就是主键索引了，在建表的时候都会指定主键。主键也默认的会建立索引，我们一般使用的索引都是Btree索引，大概结构如下：

Paste_Image.png

MyISAM和InnoDB索引结构有很大差异，这里以InnoDB为例，InnoDB的叶节点存储的是数据的行，而除了主键之外的列索引存储的是主键key，也就是说在查询的时候需要二次查询，先通过列索引找到主键，再通过主键索引找到row。而MyISAM的主键索引和列索引一致，只不过主键不可以重复，但是列可以。

上面的索引结构图告诉我们，在查询时，能使用主键查询尽量不要用列查询，因为会带来二次查询。

2.单个索引还是组合索引

上面我们提到一个查询过程，其实对于普通列的查询会转化为两次查询，那么我们应该尽量能在列的查询中过滤掉尽可能多的数据。

单个列索引查询也有个限制，如果查询的列不是独立的，那么Mysql不会使用索引。独立的列指查询中的列不能使用表达式，也不是函数的参数。

select uname from user where rootid + 1 = 10;

上面的sql是不会走索引的，因为需要对所有的rootid列进行计算，所以要全表扫描，如果改成下面的语句则可以避免

select uname from user where rootid = 9;

所以索引列是不可以做运算的。

在老版本的Mysql中是只能用一个索引进行查询的，在5.0之后的Mysql，引入了“索引合并”的概念。虽然能利用多个索引查询，但是索引合并不是很合理的：

在做and操作的时候单个索引(组合)的性能是远好于多个独立索引的。而且在and时，通常只能利用多个索引中的一个进行查询。

对于or操作，对于多个列索引的情况下，需要耗费大量的CPU和内存在数据的缓存、合并和排序上。

优化器不会把所有这些消耗计算到“查询成本”中，导致查询成本被远远低估。

所以说，其实“索引合并”只是一个优化方案，多于多个索引的扫描后计算，明显性能上是要差于单索引的查找。

下面看一个单列查询的例子：

Paste_Image.png

这里我的mobile和uname字段都是索引字段，但是从执行计划中可以看到只是走了uname的索引。也就是说mobile的索引是没有用到的，那么用什么方法让查询也利用到mobile索引呢？

针对我们经常查询的多列场景，我们可以建组合索引，组合索引在可以尽可能多的运用列的查询规则。说到组合索引那么必须说一下最左前缀原则：

最左前缀原则指的的是在sql where 子句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要 出现非顺序出现、断层都无法利用到多列索引。

假如：现在对于一个table建立了一个组合索引(uname，age，mobile)，那么对于查询时where语句查询条件必须为(uname，age，mobile)或(uname，age)或(uname)，此时才能利用组合索引。为什么呢？

因为我们知道mysql为btree索引，对于每一个点索引数据都会维护一个(uname，age，mobile)的复合数据结构，我们在Query的时候一定会以一个字段为第一个匹配，第一个字段匹配之后选取第二个字段进行匹配依次类推。那么对于(age，mobile)、(age)、(monile)这种情况没有第一个匹配项自然不会走索引，而(uname，mobile)也不会走索引。

下面例子的索引是(uname,mobile,email)的组合索引：

当使用(uname,mobile,email)时：

Paste_Image.png

可以看出查询全部走了索引。

当使用(uname,mobile)时：

Paste_Image.png

此时也走了索引。

但是如果是(uname,email)时：

Paste_Image.png

这时候只是走了uname的索引。

如果为(mobile,email)时：

Paste_Image.png

此时完全没有走索引。

4.索引的区分度

索引的区分度，主要是衡量索引值不相同的程度，区分度越大，越有利于索引的查询。

设想一下，对于sex列，列值只有male和female，那么也就是说列中绝大多数值都是重复的，那么用此索引进行row的查找其实意义并不大。所以这样的列建索引的意义并不大。

另一种场景，对于列值比较长的列，我们往往不能将整个列做索引，因为这样会导致索引过大，降低索引效率。我们需要取列值的前缀进行索引，那么索引前缀的大小选择就需要计算区分度。

索引的区分度计算主要计算是通过 不重复的索引值/数据表的总记录数。区分度越高，索引查询时会让mysql在查询时过滤掉更多的行。值越接近1，证明区分度越高。

5.组合索引的顺序和区分度：

上面说到了区分度越高的越容易用来做索引，因为区分度高的列可以很容易的过滤掉很多的数据。对于组合索引来说，在考虑索引的顺序的时候也是要考虑数据的分布，也就是区分度。对于多个列构成的组合索引，在查询过滤的时候也是和列的位置有关的，这也是最左前缀规则说的事情，也就是说如果在第一次能过滤掉大量的数据，那么后续的索引匹配就能减少很多消耗。所以在选择索引顺序的时候最好是要考虑到区分度的问题，将区分度比较高的列放在前面。

6.利用索引进行排序

Mysql可以通过两种方式达到排序的效果：

进行排序计算

按照索引顺序扫描

对于后者，扫描索引是很快的。但是如果索引不能覆盖查询所需的全部列，那么对于每一次查询都会回表查询一次行。这基本都是随机IO。这种情况下按照索引顺序读取数据反而会慢于全表扫描。

只有当索引的列顺序和Order By子句的顺序完全一致时，并且所有的列的排序方向都一样时，才能使用索引对子句进行排序。

也就是说索引的排序必须保证最左前缀规则，当然也有例外，就是在where子句中指定索引列为常量，同时保证where中条件和order by中条件满足最左前缀规则。首先看一下满足最左前缀规则的order by：

explain select uname from testuser order by uname,mobile;

Paste_Image.png

上面的表的索引是(uname, mobile, addtime)，可以看到，查询走了组合索引，同时查询使用了覆盖索引。

除了上述的方式，还可以这么写：

explain select uname from testuser where uname = 'zhangsan' order by mobile,addtime;

Paste_Image.png

可以 看出来Extra中没有filesort,证明排序走了索引。

当排序条件顺序不一致时：

explain select uname from testuser where uname = 'zhangsan' order by mobile asc,addtime DESC;

Paste_Image.png

可以看到，Extra中出现了filesort，也就是说排序没有用到索引，所以排序顺序不一致的情况下是不能利用索引进行排序的。

上面都是针对组合索引的，单列索引对于索引排序意义不大，order by 只有在使用排序字段索引时才会有用。

针对排序这种场景，其实还可以在业务中实现排序，这样能大大的减轻数据库的压力，不至于因为一个查询而影响其他业务。

7.应该注意的几点

在使用索引查询的时候，需要保证索引类型和查询的数据类型一致，经常混用的是用int型查询varchar类型的数据或反过来，这样会导致索引失效。

range查询要尽量放在后面，因为在range后面的查询不会走索引，这一点在设计索引的室友要注意

Like查询不能前缀模糊匹配，也就是说不可以like ‘%123’。因为like的后缀模糊 like ‘123%’可以转化为range查询，但是前缀模糊不可以。

索引不是越多越好，索引十分大时不仅会影响查询效率，同时会为数据的插入造成很大的负担。

对于重复索引需要删除，规划好索引是高效率的前提。

以上是最近学习mysql索引的笔记，如果有什么不正确的地方，恳请指出，找错改错的过程很重要。勿忘初心，方得始终~