mysql 高性能日记之索引(持续更新)

本文仅限于自己读写的笔记,需要具有一定 mysql(inodb,myisam 引擎)基础的高端玩家,不感兴趣的玩家们就不用在意了

Inodb 引擎

1,每个新建索引,都需要考虑清楚看是否是必须的,很多新建的索引不仅不会提高 sql 语句的效率,反而会增加维护索引的成本

     对于 Inodb 的 B-Tree,如果是非聚簇索引,每次检索都需要进行两次(本身+主键,此处不过多解释),所以当存在索引 (B),A是主键,就没有必要再建立索引(B, A),除非需要 order by a 才需要用到组合索引。

MyISAM 引擎

1,默认开启索引前缀压缩,对于 CPU 密集型业务需要手动关闭该功能,MyISAM 为了降低 IO 的压力,将索引块进行前缀压缩,比如 "aaaa"-"aaaabbb" 两个索引块在内存中为 "aaaa"-"4.bbb",解压时会消耗一定的 CPU 算力。

公共问题

1,扩展索引时,也需要考虑是否会影响到旧索引的性能

     原本存在索引(A,qps 超高),为了整合索引,将 B(VCHAR1024) 加入原索引构成新索引(A, B),由于加入新的列(新列超长,会极大影响到旧查询效率)。

2,对于两个表 A {primary_key: app_id,column:xxx};B {primary_key: account_id,app_id},其中 A 表的 app_id 和 B 表的 app_id 是同一个属性,如果业务给定一个 account_id,需要返回这个用户下的所有 app 信息,相信不少同学会这样写

      a. select * from A where app_id in (select app_id from B where account_id = %d)

      b. select * from A join (select app_id from B where account_id = %d) as C using (app_id)

      上述两种写法应该是大部分开发者都会优先考虑的 sql 语句(正向思维),但 Mysql 优化器并不会优化上面两种 sql 语句,而是会按从左到右的顺序,现对 A 表进行全表遍历,然后与 B 表查出的数据进行 using 比较返回有效数据。所以需要大家反向去写 sql 语句;这个问题是在 mysql(5.6) 之前的问题,在优化器中会把 a 语句转换为 select * from A where exists (select app_id from B where account_id = %d),所以才会产生上述的问题,这里写这些不是为了误导大家,而是提醒大家任何语句都不要想当然理解其原理,而是需要实践出真理,否则一定会产生血的教训

      c. select * from B where account_id = %d join (select * from A) as C using (app_id)

      当然,如果在两个表中都没有可供 where 使用的有效索引,那就老老实实全表遍历吧。

posted @ 2019-10-28 17:27  不想写代码的DBA  阅读(179)  评论(0编辑  收藏  举报