索引是帮助数据库高效获取数据的数据结构。索引是加速查询的常用技术手段。在设计索引时,要遵循索引设计规范,避免不必要的踩坑。
【推荐】索引存储结构推荐BTREE
InnoDB和MyISAM存储引擎表,索引类型必须为BTRER,MEMORY表可以根据需要选择HASH或者BTREE类型索引。
【建议】单个表上的索引个数不能超过7个
索引在加速查询的同时,也会带来写入速度降低的问题(写入数据的同时,要更新索引)。应限制单表上索引个数。
【建议】利用覆盖索引来进行查询操作
覆盖查询即是查询只需要通过索引即可拿到所需数据,而不需要再次回表查询,所以效率相对很高。所谓的覆盖索引,就是索引上存储的某一条记录索引列的值,如果一个查询只需要访问索引中的数据,而无需访问实际的表时,就可以实现覆盖索引的效果。
【强制】使用联合索引时要遵循最左前缀匹配原则
所谓的联合索引就是基于多个列创建索引,也称"多码索引"、"组合索引"等。举例来说,一个表有A、B、C、D等列,如果声明(A,B,C)为一个索引,那么这个索引就是一个联合索引。联合索引遵循最左前缀匹配原则。所谓最左前缀匹配的原则,就是最左优先,在检索数据时从联合索引的最左边开始匹配,联合索引的第一个字段必须出现在查询组句中,这个索引才会被用到,对(A,B,C)的索引,数据库会同时际建立了(A)、(A,B)、(A,B,C)三个索引。
基于以上介绍,在使用联合索引时,为遵循最左匹配原则,要将使用最频繁的列放在最左,这样就能使用到索引。如上述联合索引,A应该是访问频率频率最高的列。
此外,既然联合索引会默认创建一些索引,所以没有必要重复创建索引。比如先定义(A,B,C)联合索引,则没有必要再定义(A)索引和(A,B)索引。
最后,在使用联合索引的时候,要注意联合索引无法覆盖的场景,如对于(A,B,C)联合索引,如果需要再B上使用索引,联合索引是无法覆盖的,需要单独基于B创建索引。
【推荐】多表关联查询时,保证被关联的字段有索引
多表关联查询会引入性能问题,如果表的规模很大。对于大数据量场景,需要保证被关联的字段有索引。
【建议】频繁更新的列不建议创建索引
索引不是没有代价的,索引在加速数据的检索速度的同时,因为需要维护索引对应的实现结构,当对表中的数据进行增加、删除和修改的时候,会降低增/改/删的执行效率。所以,对于需要频繁更新的字段,不建议建立索引。如果需要加速这部分数据的查询性能,首先考虑业务场景是否合理,其次考虑当前表结构设计是否合理,看看能否将业务需要的字段变成不频繁更新的列。
【强制】使用索引时,要考虑索引生效、失效的场景
索引在sql中的where子句、order by子句、join子句、select子句中会生效。但是,也要注意索引不生效的情况。索引失效的场景可以参考笔者之前的WIKI。常见的索引失效场景有:
(1) 查询条件中有or。此时,必须保证所有or相关的字段都有索引才能生效。所以要谨慎使用or语句。
(2) like语句以%开头。模糊查询时,使用%且将其放在开头,会导致索引失效。
(3) 如果存在类型转换(如存储是字符串,查询的时候未用引号,则会进行类型转换),索引会失效。
(4) 索引列参与计算会导致索引失效(如执行算数运算或使用函数)。
(5) 违背最左匹配原则。
(6) 如果Mysql 评估全表扫描要比使用索引要快,则索引失效(数据量很少,使用索引不会提升查找性能)。
【建议】如果不明确查询是否使用到索引,可使用执行计划判断
执行计划除了用来分析查询性能,还可以判断查询是否使用到了索引。执行计划的使用可以参考这篇WIKI。文章来源:https://www.toymoban.com/news/detail-666341.html
参考
https://zhuanlan.zhihu.com/p/391673897 第32期:索引设计(索引设计详细规范)
https://zhuanlan.zhihu.com/p/354375388 汇总篇 | MySQL数据库设计开发规范
https://zhuanlan.zhihu.com/p/339441666 超全的数据库建表、SQL、索引规范
https://blog.csdn.net/USTC_Zn/article/details/94356505 数据库使用规范(索引规范,SQL规范,表设计规范等)
https://blog.51cto.com/u_2820398/6034597 索引与查询性能优化文章来源地址https://www.toymoban.com/news/detail-666341.html
到了这里,关于索引设计规范的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!