简介

索引（Mysql里面成为键（KEY））是存储引擎用于快速找到记录的数据结构。

我们都知道，索引对于大数据量的查询来说非常有用；当然，不恰当的索引在数据量很大的时候对性能的影响也会很大。

索引类型

B-Tree（实际上是B+Tree）

正常情况下，我们说的Mysql的索引就是B+Tree实现的，其每个叶子节点有子叶子节点的指针，这些指针同时还存储这其子叶子节点的值的上下限。B+Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。

其他

空间数据索引（R-Tree）、全文索引、其他第三方索引

B+Tree索引使用举例

索引的优点

除了可以快速定位到要查的数据行外，以B+Tree为例，其是顺序存储的，所以还可以用来做ORDER BY或GROUP BY操作；另外索引中存储了实际的值，如果查询的值只存在与索引中的话可以直接从索引中得到。

1. 索引大大的减少了数据库需要检索的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机I/O变为顺序I/O。

索引优化

独立的列

就是所用的索引不能是表达式或者函数的一部分。

比如where id + 1 = 5

前缀索引和索引选择性

前缀索引

前缀索引是指如果是varchar类型的列，只用前面的一部分作为索引，从而节省索引空间，但是这样就不能直接从索引中得到数据了（因为索引只存了一部分）。

索引选择性

索引选择性是指不重复的索引值（也叫基数）与所有数据（#T）的比。范围是1/#T到1之间。当然选择性为1的时候是最好的，一般我们的主键索引就是1。

文本类型的列mysql是不允许把全部内容作为索引的。这时候我们就要选择足够长的内容保证选择性，而又不能太长导致存储空间过大。

书中给出的方案就是先以前十为样本，看总量（COUNT(*)），然后截取量（LEFT(xxx, n)）一点点增加，如果和总量相似，则可以把这个长度作为前缀。

另外一个方案就是COUNT(DISTINCT xxx)/COUNT(*)与COUNT(DISTINCTLEFT(xxx, n))/COUNT(*)比较，n一点点增大，当比例比较相似的时候则可以使用此长度。

多列索引

多列索引需要注意的一个问题就是索引的顺序。有的人以为为每列都做索引比较好，其实这是个错误的认识。虽然mysql后来有了索引合并的功能，但是性能并不是很好，同时也说明这个索引建立的不是最优。

索引列顺序

B+Tree是按照顺序存储的索引，当有多列索引存在时，mysql是首先按照第一列的顺序存储，之后按照第二列顺序存储。

有个三星索引的概念：索引将相关的记录放在一起获得第一星；如果索引中的数据顺序和查找中的排列顺序一致，获得第二星；如果索引中的列包含了查询中包含的全部列则获得第三星。

通常情况下，索引的排序应该是，排序列、分组列、选择性列排序。

聚簇索引

聚簇就是聚集在一起的意思嘛。

其实一般情况下，我们定义的主键就会是聚簇索引，而如果我们不定义主键的话，mysql也会选择一个非空的唯一索引来作为聚簇索引，如果没有这样的索引，mysql也会隐式的给我们定义一个主键作为聚簇索引。

优点

相关的数据保存在一起。
访问数据快，聚集索引和数据行同时保存在BTree中。
使用覆盖索引的时候可以直接使用页节点上的主键值。

缺点

如果数据都在内存，则聚簇索引就没什么用了。
不是顺序插入的话速度会比较慢。（其实用自增逐渐就可以了，UUID这种就会超级慢）
更新聚簇索引的列的代价很高。
如果行比较稀疏的话全表扫描会比较慢。
二级索引包含主键、二级索引需要两次查询（先找到主键、再通过主键查询数据行）。

非聚簇索引（二级索引）

二级索引就是除了主键外的其他索引。

由于主键及数据行存储在了B+Tree中，通过主键索引的查询会非常快，但是二级索引至存储了主键，所以需要造成二次查询。

覆盖索引

索引查询数据确实会很快，但是Mysql也可以直接从索引中获取数据。如果要查询的所有数据都在索引中，Mysql就不需要再去数据行中读取数据了。

如果一个索引包含所有要查询的字段，那么这个索引称为覆盖索引。

而二级索引都会额外包含主键索引，所以二级索引列及主键也可以成为覆盖索引，完成查询。

使用索引扫描做排序

Mysql有两种方式排序，一种是对结果排序，一种是直接通过索引扫描排序。

要用索引排序，除非索引列的顺序和ORDER BY的顺序一致并且顺序（要么DESC，要么ASC）也一致，才能使用索引扫描排序（如果不一致，可以把其中之一改为相反数或反串）。

而这个排序的限制是ORDER BY子句的第一个字段必须为索引的最左前缀，否则就不能用索引排序。

这里有个例外，就是如果排序索引的前面列被指定为常数，则可以使用。

比如有索引(date, id1, id2)，语句如下：

select … from xxx where date = ‘2015-04-30’ ORDER BY id1, id2

这时候第一个索引date为常量，可以使用第二、三列索引完成索引排序。

冗余索引和重复索引

有的人可能对一个字段做了多种类型的索引，这其实是多余的（重复索引）。

但是冗余索引和重复索引还是不一样的。如果创建了索引(A, B)又创建了索引(A)则是冗余索引，其实(A)就是(A, B)的前缀，完全可以用(A, B)取代（对于B+Tree来说）。但如果创建索引(B)就不一样了。

未使用的索引

有些索引我们当初建表的时候想当然的就创建了，但可能实际上我们根本就没有使用。大家都知道索引会导致表数据更新的时候的效率变低，这样的索引实际上就是累赘，我们应该删除他们。

我们可以使用Percona Toolkit的pt-index-usage来读取查询日志，并对日志中的每条查询进行EXPLAIN，然后打印出关于索引和查询的报告。这样我们就会发现哪些索引几乎没有使用过，可以果断删掉了。

本文原创于赵伊凡BLOG

©原创文章，转载请注明来源： 赵伊凡's Blog
©本文链接地址: 高性能mysql读书笔记2（索引1）