MySQL 索引

 

索引的常见模型

 

hash 索引、数组索引、树索引

 

索引是属于存储引擎内的内容，由存储引擎来提供。

 

InnoDB 索引模型

b+树

 

基于主键索引和普通索引的查询有什么区别？

 

主键索引内存储的是行数据

普通索引存储的是主键数据

 

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

 

索引维护

可能会出现页分裂，原因是在索引中间插入了一条新的记录，如果数据是有序的话，便不会有这个问题，会追加到后面。

 

使用自增id可以避免这个页分裂的问题。页分裂会导致空间利用率降低

 

当两个相邻页的利用率很低的时候会进行页合并

 

尽可能保证主键的值小，这也是使用自增id的原因

因为普通索引存储的值是主键索引的值，所以主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

 

有没有什么场景适合用业务字段直接做主键的呢？

只有一个索引；该索引必须是唯一索引。你一定看出来了，这就是典型的 KV 场景。

 

回表

在使用普通索引查找数据之后，得到的是主键值，需要通过主键索引继续查找这行完整的记录，这步操作称为回表

 

覆盖索引

避免回表的时间浪费，可以通过查询结果的控制，来规避回表，比如主键字段 id 索引字段 name 当你通过 name 查询 id 的时候，就不回在通过主键值再查一次了。因为你可以直接得到你想要的结果。

select id from t where t.name = 'sd'

 

最左前缀原则

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

索引可以被使用，联合索引可以被有效的利用来定位一条记录。

 

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

 

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

 

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

 

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

 

在建立联合索引的时候，如何安排索引内的字段顺序。

 

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

 

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

 

索引下推

 

MySQL 5.6 新功能索引下推。可以减少回表次数，在索引查找时进行一次数据过滤