Mysql系列之 - 索引

Tip：生活不易，码农辛苦
         我是小刀，在互联网中夹缝求生 我希望你开心…

何为索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的出现是为了提高数据库查询效率，就像书的目录一样。

常见的索引模型：

哈希表
有序数组
搜索树

哈希表这种结构适用于只有等值查询的场景，比如 Memcached及其他一些nosql引擎。由于哈希表存储不是有序的，因此做区间查询的速度是很慢的。
而有序数组在等值查询和范围查询场景中的性能就都非常优秀。有序数组只适用于静态存储引擎，由于它是有序存储，因此在插入数据时，要挪动后面的所有数据，成本太高。
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。因此使用N叉数，而不是二叉树。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

InnoDB 的索引模型

1 .在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用的是B+树索引模型。
2 .每一个索引在 InnoDB 里面对应一棵 B+ 树。B+树能够很好地配合磁盘的读写特性，减少单词查询时访问磁盘的次数。
3 .主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引。
4 .非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引。
5 .普通索引的查询方式是先搜索普通索引树，找到主键值，再去搜索主键索引树。这个过程也被称为回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应尽量使用主键查询。
6 . 数据页的分裂与合并：当数据页已满时，有新的数据插入就会申请新的数据页，并把部分数据移动过去，这个过程称为数据页的分裂，不仅性能会受影响，而且数据页的利用率也会降低。当相邻的两个数据页由于数据删除，导致利用率很低时，就会将数据页合并。
从性能和存储空间方面考量，自增主键往往是更合理的选择。

使用InnoDB主键应该知道的事项

因为InnoDB的索引的方式通过主键聚集数据，严重依赖主键。索引如果没有定义主键，那么InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点有：

       1.可以把相关数据存储在一起，减少数据查询时的磁盘I/O
       2.数据访问更快，因为聚簇索引就是表，索引和数据保存在一个B+Tree中
       3.使用索引覆盖的查询时可以直接使用页节点中的主键值

聚簇索引的缺点有：

       1.插入速度严重依赖插入顺序
       2.更新聚簇索引列的代价很高，因为会强制InnoDB把更新的列移动到新的位置
       3.基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能会导致“页分裂”。当行的主键值要求必须将这一行插入到已满的页中时，存储引擎会将该页分裂为两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的存储空间。

为什么普通索引比唯一索引效率高？

查询时：
    普通索引查出数据页，数据页读入内存，判断是否有相等的数据，返回数据。
    唯一索引查出数据页，数据页读入内存，直接返回数据。
    虽然普通索引多了一步判断，但是数据是以页为单位读入内存的，判断大概率是内存操作，消耗很小，可以忽略。
更新时：
    普通索引直接更新内存或者缓存到 change buffer 中，结束。
    唯一索引更新时需要判断是否有数据冲突，所以无法利用 change buffer，当数据页不在内存时，必须读磁盘写入内存再做判断，效率低于普通索引。

补充

有普通索引查询主键值，再回到主键索引树搜索的过程称为回表。那如何优化索引避免回表呢：

覆盖索引。指的是普通索引树上，节点已经包含了要查询的信息，也就是普通索引“覆盖了”我们的查询需求，因此就不用再使用主键查询，避免了回表。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的优化手段。

最左前缀原则。

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
联合索引如何安排字段顺序：
如果可以通过改变字段顺序，从而少维护一个索引，那么将是优先考虑的
第二个考虑原则是空间

索引下推

而 MySQL 5.6 引入的索引下推优化，在索引遍历过程中，对索引包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

后记

其实数据库索引调优是一项技术活，不能仅仅靠理论，因为实际情况千变万化，而且MySQL本身存在很复杂的机制，如查询优化策略和各种引擎的实现差异等都会使情况变得更加复杂。但同时这些理论是索引调优的基础，只有在明白理论的基础上，才能对调优策略进行合理推断并了解其背后的机制，然后结合实践中不断的实验和摸索，从而真正达到高效使用MySQL索引的目的。