SQL精华总结索引类型优化SQL优化事务大表优化思维导图

SQL精华总结索引类型优化SQL优化事务大表优化思维导图

技术杂谈小彩虹2021-07-12 10:49:4480A+A-

索引类型

从数据结构角度:

  1. B+树索引,
  2. hash索引,基于哈希表实现,只有全值匹配才有效。以链表的形式解决冲突。查找速度非常快 O(1)
  3. 全文索引,查找的是文本中的关键词,而不是直接比较索引中的值,类似于搜索引擎做的事情。
  4. 空间数据索引(R-Tree),MyISAM支持空间索引,可以用作地理数据存储,会从所有维度来索引数据,有效的使用任意维度来组合查询。

从物理存储角度:

  1. 聚簇索引,InnoDB同一个结构保存了索引和数据行,叶子节点的data域存放了行的全部数据。

    优点:数据访问快。将索引和数据保存在同一个B+树种,因此比非聚簇索引快。可以把相关数据保存到一起。例如根据用户ID来聚集数据,只需从磁盘读取少量数据页就可以获得用户ID的全部邮件。

    缺点:插入速度验证依赖于插入顺序,乱序写入会导致频繁的页分裂移动大量数据。更新代价很高,因为要将每个被更新的行移到到新位置。面临页分裂问题,当某个页已满时,存储引擎会讲该页分成两个页面来容纳该行。

    尽可能的按主键顺序插入数据,并且尽可能的使用单调增加的聚簇键的值来插入新行。

  2. 非聚簇索引,MyISAM索引文件和数据文件是分离的,叶子节点的data域存放的是行数据的地址

    优点: 更新代价比聚集索引要小 。

    缺点:跟聚集索引一样,非聚集索引也依赖于有序的数据。可能会二次查询(回表) ,当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。

从逻辑角度:

  1. 普通 /单列索引,普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和NULL。INDEX index_name ( column )
  2. 唯一索引,唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为NULL,一张表允许创建多个唯一索引。UNIQUE ( column )
  3. 主键索引,数据表的主键列使用的就是主键索引PRIMARY KEY ( column)
  4. 联合索引,指多个字段上创建的索引 INDEX index_name ( column1, ... ),使用时最左匹配原则
  5. 前缀索引,索引字符串的一部分INDEX index_name ( column(10) )
  6. 全文索引,查找的是文本中的关键词,而不是直接比较索引中的值,类似于搜索引擎做的事情。FULLTEXT ( column)

从表现形式角度:

  1. 主键索引,数据表的主键列使用的就是主键索引。InnoDB中,当没有显式的指定表的主键时,InnoDB会自动先检查表中是否有唯一索引的字段,如果有,则选择该字段为默认的主键,否则InnoDB将会自动创建一个6Byte的自增主键。
  2. 二级 / 辅助索引,除开主键索引之外的都叫二级索引。在MyISAM中二级索引和主键索引的结构大致相同。在InnoDB中二级索引的叶子节点存储的是主键值,通过主键值定位行数据,需要两次索引查找。使用主键值当做指针会让二级索引占更多的空间,但是移动行时无需更新二级索引。
  3. 覆盖索引,如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。覆盖索引即需要查询的字段正好是索引的字段,那么直接根据该索引,就可以查到数据了, 而无需回表查询。

索引优化SQL排查调优

  1. 打开慢查询日志,查看慢查询
  2. 首先在线下环境explain看一下执行计划,是否符合预期,例如查看①key字段是否使用到索引,使用到什么索引。②type字段是否为ALL全表扫描。③row字段扫描的行数是否过大,估计值。MySQL数据单位都是页,使用采样统计方法。④extra字段是否需要额外排序,就是不能通过索引顺序达到排序效果;是否需要使用临时表等。⑤如果是组合索引的话通过key_len字段判断是否被完全使用。
  3. 了解业务场景。看业务怎么使用这个sql,做针对性优化。
  4. 排除缓存的干扰。如果线程RT(响应时间)快时慢的话就可能是缓存的问题,因为对表进行更新的话先关缓存都会失效
  5. 使用覆盖索引。一个索引包含所有需要查询的字段的值。直接根据该索引就可以查到数据,无需回表查询。比如InnoDB二级索引包含主键值,通过二级索引查找主键值就会用到覆盖索引,无需回表
  6. 使用组合索引。比如根据名称查库存就可以建立联合索引,不需要根据名称查出主键之后在查库存了,但需要考虑业务场景,避免占据较大空间。
  7. 注意最左前缀原则,按照定义的顺序写sql。如果一个模糊查询只使用到一个组合索引的最左索引,那这样还是能用到这个联合索引,并不需要新建一个单独的索引。
  8. 选择合适的索引列顺序。当不需要考虑排序和分组时,将选择性最高的列放到索引的最前列通常是很好的。
  9. 索引下推,mysql5.6之后官方自动优化。比如联合索引(name,age),根据name的like查询并且age在一定区间内查找所有匹配的行数据时,就只有name可以用到索引,age并不会。在优化后,name用到索引之后,会直接再从索引中匹配满足条件的age,之后再回表查询,这样需要回表查询的数据就相比减少了。其实就是充分利用了索引中的数据,尽量在查询出整行数据之前过滤掉无效的数据。
  10. 使用前缀索引。当要给字符串加索引时,可以使用前缀索引,节省资源占用。如果前缀区分度不高可以倒序存储或者是存储hash。
  11. 注意隐式类型转换。比如id是字符类型,查询的使用使用int类型会相当于加了类型转换函数,用不上索引。两个表的字符集不一样也会导致,例如utf8mb4(可以超过3字节)和utf8(最多3字节)
  12. 被频繁更新的字段应该慎重建立索引,不被经常查询的字段没有必要建立索引。
  13. 遵循索引设计准则三星索引,但一般难以满足,需要依赖实际成本和业务场景。① WHERE 后面参与查询的列可以组成了单列索引或联合索引。② 避免排序,即如果 SQL 语句中出现 order by colulmn,那么取出的结果集就已经是按照 column 排序好的,不需要再生成临时表。③尽量使用覆盖索引
  14. 注意索引失效场景。

思维导图

sql高级V2.0

喜欢可以点个赞

点击这里复制本文地址 以上内容由权冠洲的博客整理呈现,请务必在转载分享时注明本文地址!如对内容有疑问,请联系我们,谢谢!

支持Ctrl+Enter提交

联系我们| 本站介绍| 留言建议 | 交换友链 | 域名展示
本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除

权冠洲的博客 © All Rights Reserved.  Copyright quanguanzhou.top All Rights Reserved
苏公网安备 32030302000848号   苏ICP备20033101号-1

联系我们