索引的常见模型
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 新功能索引下推。可以减少回表次数,在索引查找时进行一次数据过滤