写点什么

索引的失效与优化

作者:andy
  • 2023-05-14
    北京
  • 本文字数:1066 字

    阅读完需:约 3 分钟

一、MySql 索引存储结构


索引是在 MySql 的存储引擎中实现,不同存储引擎支持不同的索引



B+树索引是由 B+树实现,是有序排列存储的,在排序和范围查找方面具有优势


Hash 索引适合 key-value 键值对查询,只有 Memory 存储引擎支持 Hash 索引,查询数据的时间复杂度为 O(1),查询性能要比其他索引优越


创建表时,无论 InnoDB 还是 MyISAM,默认创建一个主键索引,默认使用 B+树索引


InnoDB 默认创建的主键索引是聚簇索引(Clustered Index),其他索引属于辅助索引(Secondary Index),也被称为二级索引或非聚簇索引,主键索引的叶子节点存储的是行记录


MyISAM 存储引擎使用的是辅助索引,索引中的每一个叶子节点仅仅记录的是每行数据的物理地址,即行指针


二、索引优化


1、覆盖索引优化查询


从辅助索引中查询得到记录,不需要通过聚簇索引查询获得,MySql 将其称为覆盖索引


比如查询商品的商品名称、商品价格,则可以通过建立商品编号、商品名称、商品价格的联合索引,不同回表便得到数据信息


统计数量可以通过覆盖索引实现,即借助辅助索引查询统计数量


EXPLAIN SELECT count(*) FROM sxyp_user;
复制代码



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 的前后条件中有一个列没有索引,涉及的索引不会使用


对索引进行函数操作或者表达式计算也会导致索引的失效


用户头像

andy

关注

强准备 + 强执行 + 强信仰 2019-11-21 加入

以前是T型人才,当下是π型人才,未来是梳子型人才

评论

发布
暂无评论
索引的失效与优化_andy_InfoQ写作社区