索引的失效与优化
一、MySql 索引存储结构
索引是在 MySql 的存储引擎中实现,不同存储引擎支持不同的索引
B+树索引是由 B+树实现,是有序排列存储的,在排序和范围查找方面具有优势
Hash 索引适合 key-value 键值对查询,只有 Memory 存储引擎支持 Hash 索引,查询数据的时间复杂度为 O(1),查询性能要比其他索引优越
创建表时,无论 InnoDB 还是 MyISAM,默认创建一个主键索引,默认使用 B+树索引
InnoDB 默认创建的主键索引是聚簇索引(Clustered Index),其他索引属于辅助索引(Secondary Index),也被称为二级索引或非聚簇索引,主键索引的叶子节点存储的是行记录
MyISAM 存储引擎使用的是辅助索引,索引中的每一个叶子节点仅仅记录的是每行数据的物理地址,即行指针
二、索引优化
1、覆盖索引优化查询
从辅助索引中查询得到记录,不需要通过聚簇索引查询获得,MySql 将其称为覆盖索引
比如查询商品的商品名称、商品价格,则可以通过建立商品编号、商品名称、商品价格的联合索引,不同回表便得到数据信息
统计数量可以通过覆盖索引实现,即借助辅助索引查询统计数量
2、自增字段作主键优化查询
InnoDB 主键索引是聚簇索引,叶子节点存储的是行记录,设置自增字段,每次插入数据时会按照顺序添加到当前索引节点的位置,不需要移动已有的数据
使用非自增主键,每次插入主键的索引都是随机的,因此每次插入新的数据时,会插入到中间位置,导致的问题不得不移动数据满足新增数据的插入
3、前缀索引优化
前缀索引就是使用某个字段中字符串的前几个字符建立索引
索引文件存储在磁盘中,磁盘最小单元是页,一个页默认大小为 16KB,如果每个索引值大小为 2KB,当索引数据很大时,需要遍历大量页,导致性能很差
减小索引字段大小,增加一个页中的索引数量,提高查询性能
前缀索引也有一定的局限性,order by 无法使用前缀索引,无法把前缀索引作为覆盖索引
4、防止索引失效
Memory 存储引擎实现的 Hash 索引只有在=条件下起作用,其他情况就无法起作用了
以 %开头的 LIKE 模糊进行 select 字段查询,而不是统计数量时,无法使用索引查询数据
使用联合索引进行查询,遵循最左匹配原则,使用索引中最左边的列进行查询,才能使用联合索引
order 表中建立一个复合索引 idx_user_order_status(order_no, status, user_id),如果我们使用 order_no、order_no+status、order_no+status+user_id 以及 order_no+user_id 组合查询,则能利用到索引;而如果我们用 status、status+user_id 查询,将无法使用到索引
查询条件中使用 OR,OR 的前后条件中有一个列没有索引,涉及的索引不会使用
对索引进行函数操作或者表达式计算也会导致索引的失效
评论