mysql 索引

目标：数据量大的时候，构建轻量的索引表，加快检索效率

常见索引类型

哈希表key-value：适合等值查询，不适合范围查询（广播）

数组：适合等值查询、范围查询，顺序写入能力强，随机写（插入）能力差；适合静态历史数据；

N叉搜索树，比如：B+树：左边儿子节点 < 父节点 <= 右边儿子节点，B+树叶子节点存数据，非叶子节点存【索引+指向的地址】数据块*；N值取决于数据块*的大小、数据页一页的大小（默认16KB）

innodb索引模型

主键索引

• 主键索引最好使用自增整数，自增有序插入效率高，减少页分裂 SMO

• 二级索引的叶子节点上存这主键索引，主键索引越小（比如int < varchar），二级索引的检索效率越好

• 主键索引可以由多个字段联合组成

• 主键索引创建后不能重建，重建就相当于整个表重建了

聚簇索引

聚簇索引的顺序影响数据物理存储顺序

• 主键索引是聚簇索引
• 没有主键，第一非空唯一索引是聚簇索引
• 没主键、没唯一索引，innodb会创建隐藏的row_id作为主键、聚簇索引

二级索引

• 二级索引的叶子节点是主键索引，二级索引找到后，若select *，就需要一个一个回表查询
• 二级索引可以重建，常见优化：索引由于删除、页分裂等原因导致数据页有空洞，重建索引可以将数据按序整理，索引更紧凑、更节省空间

覆盖索引（优化）

select (name, id) from t where name="zly";  // 存在name二级索引时，需要查找的字段在二级索引上都存在，就不需要回表查询，这个索引就是覆盖索引

联合索引

select (name, age) from t where name="zly"; // 若name,age在业务上常一其出现，就可以建name,age的联合索引；name有序，在同一个name的前提下，age有序 

有了上面这个索引，就不需要在建name索引了（重复）

场景优化

索引个数

当name，age都需要独立索引，还需要联合索引时，推荐创建age单独索引，(name, age)联合索引

原则：尽可能少的创建索引，索引（数据块*）的大小尽可能小

最左前缀原则

最左前缀可以是最左前缀字符串，也可以是字段；

select (name, age) from t where name like "张%"; 

索引下推（icp）

// 有 （name, age）索引
select * from t where name like '张%' and age=10 and ismale=1; // 以前mysql找了name前缀就回表了，现在找了name和age之后再回表找ismale判断

选择普通索引还是唯一索引

查找性能对比

普通索引和唯一索引的查找性能相似

唯一索引找到就返回，普通索引找到下一个不满足的就返回，所以普通索引比唯一索引查找次数的多。但innodb找到一个值时会将相邻的数据（一页）都放进内存中，在内存中多找一次的影响可忽略；

更新性能对比

普通索引在能使用change buffer的情况，性能更好

change buffer 优化

内存存在该数据就直接更新，不存在时，在不影响数据一致性的前提下，更新操作缓存在change buffer中；等到下一次查询这条数据时在将change buffer的改动应用到内存（这个动作叫merge），内存数据变为脏页，走脏页落盘的流程。

• merge时机：查询这条数据时、数据库正常关闭时、定期merge

change buffer相当于延迟执行更新操作，目的是减少写盘次数，适用场景：写多读少，比如历史数据、机械硬盘（写慢）

• change buffer是物理变更日志
• change buffer用的是buffer pool中的内存 innodb_change_buffer_max_size设置最大值，最大50%
• change buffer 优化只能用在普通索引上，不能用在唯一索引上。因为唯一索引如果不存在在内存上，必须去磁盘找，需要判断数据的唯一性

redo log和change buffer 的关系

change buffer是redo log优化的子集。change buffer主要节省的是写操作时需要随机读磁盘的IO消耗，redo log主要节省的是写时需要随机写磁盘的IO消耗（转为顺序写）。

崩溃恢复

change buffer更新会写入redo log buffer中；如果服务崩溃，将从redo log中重建change buffer。

• 重建指的是通过redo log到change buffer 持久化中找到对应的日志，而不是redo log可以生成change buffer

对于长字符串如何建立索引

像是身份证号、邮箱、名称这种超长的唯一字符串如何建立索引

• 业务数据量不大，直接存成唯一索引
  • 字段占用空间大，索引效率低
• 前缀索引：节省空间，但会增加查询扫描的次数，并且不能够使用覆盖索引
• 倒序索引，再创建前缀索引，不支持范围查询
• hash索引：查询性能稳定，不支持范围查询，需要额外的存储字段和计算消耗

前缀索引

前缀索引可以对比几个前缀长度下，那个前缀区分度最高，选择那个作为索引，比如name(6)

前缀区分度计算：select count(distinct left(name, 6)) as L6;

问题

前缀可能重复，比直接完整查询会增加扫描次数；

前缀索引不支持覆盖索引，这个索引上没有name字段的完整数据，所以select name需要回表；

对于前缀区分度不高的比如身份证，也不无法使用这个索引；

倒序索引

比如像身份证这种最后几个数是随机数，区分度高的可以考虑使用倒序前缀索引

select * from t where id_card = reverse('123');

hash索引

创建额外的hash索引字段，每次插入时进行crc32计算，占用4字节

alter table t add id_card_crc int unsigned, add index(id_card_crc)
select * from t where id_card_crc=crc32('123') and id_card='123'

倒序前缀索引对比hash索引

• 倒序前缀索引直接在主键索引上，在每次读写时额外调用一次reverse()函数；不支持范围查询
• hash索引需要额外的字段维护，每次在写入时执行一次crc32()函数，查询效率稳定；不支持范围查询