参考文献
索引
- 索引的本质是一种数据结构
- 索引一般存储于磁盘上, 所以受磁盘I/O影响
- 磁盘需要寻道/寻址的时间, 而且有一次读入连续的一段数据的特点(局部性原理/磁盘预读), 所以高度低的 B-Tree/B+Tree 比红黑数更适合作为索引的数据结构, B-Tree/B+Tree 高度低, 磁盘I/O次数少, 效率更高
- B-Tree 所有节点上都有data域, B+Tree 只有叶子节点有data域, 而且有的进一步优化, 增加顺序访问指针
聚集索引
InnoDB 使用聚集索引
- 索引结构为 B+Tree
- 数据文件本身就是索引文件, 叶节点data域保存了完整的数据记录
- 索引的key为数据表的主键, 表数据文件本身就是主索引
- InnoDB 要求表必须有主键(没有会自动生成一个隐含字段作为主键)
- InnoDB 辅助索引都引用主键作为data域(所以需要检索两遍)
- InnoDB 不建议使用过长的字段做主键(会导致辅助索引过大)
- InnoDB 不建议使用非单调字段作为主键(会导致插入数据时候分裂调整频繁而低效, 使用自增字段作为主键是一个好选择)
非聚集索引
MyISAM 使用非聚集索引
- 索引结构为 B+Tree
- 索引文件和数据文件是分离的, 索引文件仅保存数据记录的地址
- MyISAM 索引的key为数据表的键, data域为数据记录的地址
- MyISAM 不要求表有主键
- MyISAM 辅助索引的data域也为数据记录的地址
最左前缀原理
单列索引对一个字段进行索引, 联合索引对多个字段进行索引
查询sql是否能利用联合索引跟查询的字段和联合索引的字段顺序有关:
MySql的查询优化器会自动调整where子句的条件顺序以适用索引
假设有联合索引索引: <k1,k2,k3>
以下查询可命中索引:
|
|
以下查询无法命中索引:
|
|
索引选择
索引虽然可以加快查询速度, 但是索引文件本身需要存储空间, 而且会加重插入/删除/修改记录时的负担, 也增加MySQL运行时维护索引的负担
- 2000条以内记录没必要建立索引
- 索引的选择性(Selectivity)低的字段不建议建立索引, 比如性别
- 长字段建立前缀索引
- 前缀索引兼顾索引大小和查询速度,缺点是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)
前缀索引长度分析sql:
|
|
InnoDB 主键选择
InnoDB上尽可能采用业务无关的自增字段作为主键
- 如非必要, 选择一个与业务无关的自增字段作为主键
- 使用随机主键会增加开销, MySQL频繁移动/分页操作造成大量的碎片