B-Tree索引
例如key(last_name,first_name,dob)
- 有效索引
- 全值匹配
- 匹配最左前缀 -> last_name=‘Alien'
- 匹配列前缀 -> list_name like ‘J%'
- 匹配范围 -> last_name > ‘Alien'
- 精准+范围 -> last_name=‘Alien’ and first_name like ‘J%’
- 无效索引
- 不是最左查询 -> first_name=‘Bill’
- 不能跳过索引中的列 -> last_name=’Smith’ and dob=‘1971’
- 如果查询某个列的范围,则其右边所有列都无索引 -> last_name’Smith’ and first_name like ‘J%’ and dob=‘1971’(最后的dob不走索引)
哈希索引
只有精准匹配所有列的查询才有效通过f(key)=v存储及映射(v存储行指针)
- 优点:紧凑,非常快
- 缺点:
1只有行指针,还要去表内读取行内容
2无法排序
3不支持部分索引
4只支持等值查询,不支持范围查询
索引的优点
1减少服务器需要扫描的数据量
2避免排序和临时表
3将随机I/O变为顺序I/O
高性能索引策略
- 独立的列
索引列不能是表达式的一部分,也不能是函数参数,如 where actor_id+1=5 where to_days(date_col)<10 - 前缀索引与索引选择性
列太长不适合建立索引,对于Blob,Text,Varchar类型的字段,截取前缀建立索引,长度取决于区分度(占总长比例),如 add index idx_c7(city(7)) - 多列索引(联合索引)
对单列索引需要的话可以升级为联合索引 - 选择合适的索引列顺序
区分度大的放在前面 -
聚簇索引
术语聚簇表示 数据行 与相邻的 键值 存储在一起
聚簇索引将索引和数据保存在一个B-Tree中
InnoDB
主键索引就是聚簇索引
聚簇索引本身”就是"表.是基于主键存储的数据
二级索引的指针就是主键
主键应当顺序,单调递增,避免页分裂和碎片 -
覆盖索引
如果一个(联合)索引包含(覆盖)所有需要查询的值,就称之为覆盖索引 -> 就是说可以通过索引直接获取需要查询的数据
覆盖索引就是联合索引的最优场景,因为无需回表(回表会有随机I/O问题所以很慢) - 使用索引扫描来做排序
a.通过排序操作 film -> using filesort 表示普通排序
b.按索引顺序扫描 explain -> type:index 表示索引排序
满足最左前缀要求走索引排序(也就是把order by的内容也当做where条件的一部分,一起检查是否走索引) - 压缩索引
- 冗余和重复索引
尽量拓展已有索引,而不是新索引(不是绝对,需要综合考虑) - 未使用的索引
直接删除 - 索引和锁
使用索引可以让查询查更少的行
InnoDB只有在访问行时才会对其加锁.二级索引使用共享锁,主键索引使用排他锁
实践技巧
- 需要考虑表上所有的选项
实际上最常使用的列放在最左边,反而不是理论上的区分度最大的列放在最左边 - 将范围查询转为In(),走索引
表达式也可以转为In()查询,注意In()的范围太大可能会使查询次数过多.如 a in (1,2,3,4) and b in (1,2,3) and c in (1,2) 总查询次数为432=24次 - 将范围查询移到索引最后
反正范围查询不走索引 - 用定时任务将部分列转为状态值,走索引过滤
通过这个方法能够支持多范围的条件查询 - 排序优化
增加一些特殊的索引用来排序
举个例子(大表分页查询常见) 如索引 idx(sex,rating)
select * from profiles where sex=‘M' order by rating limit 10
升级后
select * from profiles where sex=‘M' order by rating limit 1000000,10
随着偏移量的增加MySQL需要花费大量的时间来扫描需要丢弃的行
为了减少扫描需要丢弃的行,只查ID(二级索引存储的值就是主键,所以无需回表扫描丢弃的行)
select * from profiles where id in(
select id from profiles where sex=‘M’ order by rating limit 1000000,10
)
- 碎片
减少索引与索引列的碎片(不要物理删除数据,会造成数据存储不连续)
整理碎片 Alter table <table> Engine=InnoDB
总结
1单行访问很慢(随机I/O)
2按顺序访问很快(1顺序I/O,2排序)
3覆盖索引很快(无需回表)
选择合适索引避免单行查找
尽可能使用原生数据,避免排序操作
尽可能使用覆盖索引
