深入理解MySQL索引

什么是索引

索引其实就是一种存储结构，对数据库中的特定列的数值进行排序，并且提供指向列中数值的指针，来完成快速查询。类似于图书的目录，在数据量越大的时候它的作用可能就越明显。使用索引需要付出一定的代价，它需要占用一定的空间（用空间换取时间），同时对数据变动的时候也需要进行动态维护。

索引模型

Hash

哈希表是一种以键 - 值（key-value）存储数据的结构。把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。它的优点就是索引只存储哈希码及行指针，所以索引的数据结构非常的紧凑，这也让哈希索引查找速度非常快。

缺点：

• 它仅仅接受等值查询，不支持范围查询。它存储的hash值是经过hash运算之后的，和运算前不一定一样。
• 存在hash冲突，会拉出一条链表，会影响查询速度，此时需遍历索引中的行指针。遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

memory默认索引模型

有序数组

数组通病。适用于读多写少的场景，插入效率太差。

BTREE

“N”叉树。（二叉树深度太大，查询起来太费时）。

这里我们需要区分一下B树和B+树。

B树数字和结点存放在一起，叶子节点不相连。

B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。

B树的优点：对于靠近根节点的热点数据查询速度很快。

B+树的优点：

1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

4、B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

InnoDB默认使用B+树 （N为1200左右，树高一般为4，可存储17亿数据）

特点：

• 支持范围查询、排序、模糊查询等
• 支持多种索引种类（联合索引）
• 可以升级，InnoDB中对热点数据采用自适应Hash，使之具备Hash的功能

查看innodb一页的大小

show variables like 'innodb_page_size';

修改需要到配置文件里，默认是16K.一般越小，内存划分粒度越大，使用率越高，但是会有其他问题，就是限制了索引字段还有整行的大小。一般来说8k的性能比16要好，但是CPU压力要更大，具体选择看需求。

常见索引类型

普通索引

MRR

set optimizer_switch='mrr=on';

按照主键值排好序，再顺序的读取数据。

1、磁盘和磁头不再需要来回做机械运动；

2、可以充分利用磁盘预读

排序是需要内存的，内存的大小由参数 read_rnd_buffer_size 来控制，太小了就不会启用MRR

select @@read_rnd_buffer_size;
set read_rnd_buffer_size = xxx
MRR 在本质上是一种用空间换时间的算法。MySQL 不可能给你无限的内存来进行排序，如果 read_rnd_buffer 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限，如此循环。```

唯一索引

用关键字UNIQUE定义，索引字段的值必须唯一，但允许有空值（可以有多个null）。如果是多字段的组合，则组合必须唯一。在对该字段进行增或改时，首先会检查是否重复，如果有，则报出duplica错误，拒绝操作。

由于索引具备唯一性，查找到第一个满足条件的记录后，就会停止检索。在最坏的情况下，普通索引比唯一索引多进行一次IO读取，但是概率极小。

当唯一索引进行更新操作的时候 会先进行一次查找判断数据是否唯一，再进行和普通索引一样的操作。如果数据不在内存，唯一索引的代价会更大，因为它不能使用change buffer。

从性能角度出发优先考虑非唯一索引。

存在唯一键冲突时的避免策略

1.使用insert ignore语句

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据.

insert ignore into table(xx) values(xx);

2.使用replace into语句

replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。

replace into table(xx) values(xx);

3.使用insert on duplicate key update语句

如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。

insert into table(xxx) values(xxx) on
duplicate key update xxx=VALUES(xxx);

2、3的区别在于，3在原数据上进行修改，2先删除再插入新的值，如果存在自增id，那么自增id会改变。

主键索引

用关键字primary定义。

主键不能有重复值且不能为null。（唯一索引的特例）

主键索引一般建议使用数据表的自增唯一主键来作为主键索引使用。（有序+占用空间小）

在没有默认主键的情况下会生成一个6byte空间的自动增长主键。InnDB是聚簇索引，它必须通过主键来定位数据。

联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。

创建形式：key index (a,b,c)

原则：最左前缀

索引下推：对于不满足最左前缀原则的部分，在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

覆盖索引

联合索引的最优情况。

前缀索引

若该字段是一个非常长的字符串类型，可以选择前几个字符为索引。

需要注意选择字符的长度，关系到命中率。