聊聊mysql索引

聊聊mysql索引

描述

mysql索引是空间换时间的一种方式，主要是提高查询效率，相应的要付出额外的存储空间，mysql索引使用的是B+树。

常见的索引模型

哈希表，有序数组，搜索树。

为什么是B+树

B+树的特点：

• 节点的子树数和关键字数相同
• 节点的关键字表示的是子树中的最大数，在子树中同样含有这个数据
• 叶子节点包含了全部数据，同时符合左小右大的顺序

如果N为1200，树高为4，则可以存1200的3次方数据约17亿。查询一个值最多访问3次磁盘。减少了磁盘io访问次数。

InnoDB索引模型

InnoDB使用的B+树索引，每一个索引对应一个B+树。索引主要分为两类，主键索引和非主键索引。主键索引（聚簇索引）叶子节点存储的是整行数据。
非主键索引（二级索引）叶子节点存储的是主键的值。

因此查询的时候如果是走主键索引查询的话就直接查出来了数据行。如果走普通索引第一步是先通过索引查询到主键值，然后通过主键值再查询一次数据值，俗称回表。

索引维护

为了保持索引的有序性，因此我们再插入和删除的时候需要做索引维护。
如果插入的是最大值则比较简单只需要再最后面插入值即可。如果是插入中间值需要逻辑后移后面的数据，空出所需要的位置。这种情况比较复杂，涉及到页分裂的问题，我们每个索引页大概16kb的数据。因此在插入和删除数据时均会涉及到性能方面的影响。
同理，我们主键使用自增索引（id bigint(20) unsigned NOT NULL AUTO_INCREMENT Comment ‘主键id’,）主要会有两方面考量，第一，每次插入一条新数据，都是追加操作，不会涉及到其他数据的改动，也不会触发叶子节点的分裂。第二，bigint就8个字节，因为普通索引的叶子节点存储的是主键的值，因此节省了空间。

索引优化

1. 减少回表：当涉及到多个字段查询时，可以通过创建组合索引的方式减少回表。
2. 主键索引尽量使用自增主键。从空间和性能上考量。
3. 字段散列不够大，或者字段内容过长是不适合加索引。
4. explain关键字去查看sql的执行情况。
5. 当需要排序时尽量使用索引字段order by。索引自身已经排过序了。
6. 字符串建索引时，可以前缀匹配字符串，为了增加字符串的散列度可以字符串翻转，或者hash运算等。