5索引

B-Tree索引

例如key(last_name,first_name,dob)

1. 有效索引
   • 全值匹配
   • 匹配最左前缀 -> last_name=‘Alien'
   • 匹配列前缀 -> list_name like ‘J%'
   • 匹配范围 -> last_name > ‘Alien'
   • 精准+范围 -> last_name=‘Alien’ and first_name like ‘J%’
2. 无效索引
   • 不是最左查询 -> 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

高性能索引策略

1. 独立的列
   索引列不能是表达式的一部分,也不能是函数参数,如 where actor_id+1=5 where to_days(date_col)<10
2. 前缀索引与索引选择性
   列太长不适合建立索引,对于Blob,Text,Varchar类型的字段,截取前缀建立索引,长度取决于区分度(占总长比例),如 add index idx_c7(city(7))
3. 多列索引(联合索引)
   对单列索引需要的话可以升级为联合索引
4. 选择合适的索引列顺序
   区分度大的放在前面
5. 聚簇索引
   术语聚簇表示 数据行 与相邻的 键值 存储在一起
   聚簇索引将索引和数据保存在一个B-Tree中
   InnoDB
   主键索引就是聚簇索引
   聚簇索引本身”就是"表.是基于主键存储的数据
   二级索引的指针就是主键
   主键应当顺序,单调递增,避免页分裂和碎片
6. 覆盖索引
   如果一个(联合)索引包含(覆盖)所有需要查询的值,就称之为覆盖索引 -> 就是说可以通过索引直接获取需要查询的数据
   覆盖索引就是联合索引的最优场景,因为无需回表(回表会有随机I/O问题所以很慢)
7. 使用索引扫描来做排序
   a.通过排序操作 film -> using filesort 表示普通排序
   b.按索引顺序扫描 explain -> type:index 表示索引排序
   满足最左前缀要求走索引排序(也就是把order by的内容也当做where条件的一部分,一起检查是否走索引)
8. 压缩索引
9. 冗余和重复索引
   尽量拓展已有索引,而不是新索引(不是绝对,需要综合考虑)
10. 未使用的索引
    直接删除
11. 索引和锁
    使用索引可以让查询查更少的行
    InnoDB只有在访问行时才会对其加锁.二级索引使用共享锁,主键索引使用排他锁

实践技巧

1. 需要考虑表上所有的选项
   实际上最常使用的列放在最左边,反而不是理论上的区分度最大的列放在最左边
2. 将范围查询转为In(),走索引
   表达式也可以转为In()查询,注意In()的范围太大可能会使查询次数过多.如 a in (1,2,3,4) and b in (1,2,3) and c in (1,2) 总查询次数为432=24次
3. 将范围查询移到索引最后
   反正范围查询不走索引
4. 用定时任务将部分列转为状态值,走索引过滤
   通过这个方法能够支持多范围的条件查询
5. 排序优化
   增加一些特殊的索引用来排序
   举个例子(大表分页查询常见) 如索引 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
)

6. 碎片
   减少索引与索引列的碎片(不要物理删除数据,会造成数据存储不连续)
   整理碎片 Alter table <table> Engine=InnoDB

总结

1单行访问很慢(随机I/O)
2按顺序访问很快(1顺序I/O,2排序)
3覆盖索引很快(无需回表)

选择合适索引避免单行查找
尽可能使用原生数据,避免排序操作
尽可能使用覆盖索引