MySQL索引

参考文献

MySQL索引背后的数据结构及算法原理

索引

1. 索引的本质是一种数据结构
2. 索引一般存储于磁盘上, 所以受磁盘I/O影响
3. 磁盘需要寻道/寻址的时间, 而且有一次读入连续的一段数据的特点(局部性原理/磁盘预读), 所以高度低的 B-Tree/B+Tree 比红黑数更适合作为索引的数据结构, B-Tree/B+Tree 高度低, 磁盘I/O次数少, 效率更高
4. B-Tree 所有节点上都有data域, B+Tree 只有叶子节点有data域, 而且有的进一步优化, 增加顺序访问指针

聚集索引

InnoDB 使用聚集索引

1. 索引结构为 B+Tree
2. 数据文件本身就是索引文件, 叶节点data域保存了完整的数据记录
3. 索引的key为数据表的主键, 表数据文件本身就是主索引
4. InnoDB 要求表必须有主键(没有会自动生成一个隐含字段作为主键)
5. InnoDB 辅助索引都引用主键作为data域(所以需要检索两遍)
6. InnoDB 不建议使用过长的字段做主键(会导致辅助索引过大)
7. InnoDB 不建议使用非单调字段作为主键(会导致插入数据时候分裂调整频繁而低效, 使用自增字段作为主键是一个好选择)

非聚集索引

MyISAM 使用非聚集索引

1. 索引结构为 B+Tree
2. 索引文件和数据文件是分离的, 索引文件仅保存数据记录的地址
3. MyISAM 索引的key为数据表的键, data域为数据记录的地址
4. MyISAM 不要求表有主键
5. MyISAM 辅助索引的data域也为数据记录的地址

最左前缀原理

单列索引对一个字段进行索引, 联合索引对多个字段进行索引

查询sql是否能利用联合索引跟查询的字段和联合索引的字段顺序有关:

MySql的查询优化器会自动调整where子句的条件顺序以适用索引

假设有联合索引索引: <k1,k2,k3>

以下查询可命中索引:

select * from table where k1 = '1' and k2 = '2' and k3 = '3'; # 全命中
select * from table where k1 = '1' and k2 = '2'; # 部分命中
select * from table where k1 like '1%'; # 右边模糊
select * from table where k1 > '1'; # 范围匹配
select * from table where k1 in ('1','2','3'); # 多值精确匹配

以下查询无法命中索引:

select * from table where left(k1,2) = '21'; # 使用函数
select * from table where k1 like '%2'; # 左模糊
select * from table where k2 = '2'; # 无法匹配
select * from table where k3 = '2'; # 无法匹配

索引选择

索引虽然可以加快查询速度, 但是索引文件本身需要存储空间, 而且会加重插入/删除/修改记录时的负担, 也增加MySQL运行时维护索引的负担

1. 2000条以内记录没必要建立索引
2. 索引的选择性（Selectivity）低的字段不建议建立索引, 比如性别
3. 长字段建立前缀索引
4. 前缀索引兼顾索引大小和查询速度，缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）

前缀索引长度分析sql:

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM user_info;

InnoDB 主键选择

InnoDB上尽可能采用业务无关的自增字段作为主键

1. 如非必要, 选择一个与业务无关的自增字段作为主键
2. 使用随机主键会增加开销, MySQL频繁移动/分页操作造成大量的碎片

赏

谢谢你请我吃糖果

支付宝

微信