MySQL 索引
- 索引是让提高数据查询效率的数据结构。(避免全表扫描)
- 索引的实现方式:哈希索引、B+Tree 树、倒排索引。
- InnoDB B+Tree 的叶子节点指向主键id或存储数据行;MyISAM 的叶子节点指向数据行的内存地址
- MySQL 索引的最左前缀原则
索引常见模型
索引常见模型:有序数组、跳表、哈希表、搜索树
有序数组(二分法):
- 数据顺序存储,支持区间查询
- 插入数据需要移到数据,扩容需要复制数据,成本大
- 适合静态数据
哈希索引:
- 哈希冲突:拉链法、探测法
- 存储数据没有顺序,不支持区间查询
- 适合等值查询
B+树:
- 数据存储存储、叶子节点通过指针相连,支持区间查询
- B+树的树高较低,只在叶子节点存储数据进一步降低树高,可以减少磁盘访问次数
InnoDB 索引模型
在 InnoDB 中,表是根据主键顺序以B+树索引形式存放的,这种存储方式的表被称为索引组织表(聚簇索引)
根据叶子节点存储内容的不同,InnoDB 的索引分为主键索引和非主键索引(二级索引)。
二级索引涉及回表(MySQL5.6索引下推)和覆盖索引。
数据插入时索引维护
为了维护索引的有序性,数据插入时需要做必要的维护;
顺序插入数据可以避免页分裂(数据页利用率)和数据移动的开销,所以需要尽量保证主键是自增的/有序的(auto_increment
)
在选择主键时,第二需要考虑的是主键类型要尽量小,这样可以减少二级索引的空间。
如果数据不是顺序插入的,在插入后可以通过重建表优化空间
alter table t1 engine=innodb
optimize table t1
索引回表优化
索引回表优化:覆盖索引、索引下推
覆盖索引可以减少回表次数
MySQL 5.6 引入索引下推。对于索引 (name, age)
,以下查询可以找到姓名是张开头的数据,在检查 age 的值,如果不是 10 则不回表,减少回表次数
select * from t where name like '张%' and age = 10
最左前缀原则
最左前缀原则(联合索引的顺序)
- 字段区分度
- 索引复用能力
- 字段大小(如果需创建
ab
,b
两个索引,则b字段较小时可以减少空间消耗)