MySQL索引&&开发规范

时间:2022-12-20 03:44:09

规范总结

索引规范

默认添加的索引都是BTree索引。Innodb只支持BTree索引。

设计索引原则

- 最适合索引的列是WHERE子句中的列,而不是SELECT中的列。
- 如果索引的字段很长,使用前缀索引
- 删除不常用索引
- 建议单表不超过5个
  • 单表索引不超过5个【索引可以提高查询效率,但是会减小插更新效率】

  • 每个 Innodb 表必须有个主键,【主键建议使用自增 ID 值】

  • 索引列建议

    • 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
    • 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
    • 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
    • 多表 join 的关联列
  • 避免建立冗余索引和重复索引

    • 重复索引示例:primary key(id)、index(id)、unique index(id)
    • 冗余索引示例:index(a,b,c)、index(a,b)、index(a)
  • 覆盖索引

    to be continue

  • 外键约束取舍

开发规范

  • 禁止使用 SELECT * 必须使用 SELECT 查询

  • 用join操作代替子查询

  • 使用join关联的表最好不超过5个

  • 对应同一列进行 or 判断时,使用 in 代替 or

  • 禁止使用 order by rand() 进行随机排序

    【推荐在程序中获取一个随机值,然后从数据库中获取数据的方式。】

  • WHERE 从句中禁止对列进行函数转换和计算

    【对列进行函数转换或计算时会导致无法使用索引】

  • 在明显不会有重复值时使用 UNION ALL 而不是 UNION

    • UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
    • UNION ALL 不会再对结果集进行去重操作
  • 拆分复杂的大 SQL 为多个小 SQL

    • 大 SQL 逻辑上比较复杂,需要占用大量 CPU 进行计算的 SQL
    • MySQL 中,一个 SQL 只能使用一个 CPU 进行计算
    • SQL 拆分后可以通过并行执行来提高处理效率

索引设计规范

1. 限制每张表上的索引数量,建议单张表索引不超过 5 个

索引并不是越多越好!索引可以提高效率同样可以降低效率。

索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加 MySQL 优化器生成执行计划的时间,同样会降低查询性能。

2. 每个 Innodb 表必须有个主键

Innodb 是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引,但是表的存储顺序只能有一种。

Innodb 是按照主键索引的顺序来组织表的

  • 不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引)
  • 不要使用 UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长)
  • 【主键建议使用自增 ID 值】

  1. 常见索引列建议
  • 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
  • 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
  • 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
  • 多表 join 的关联列

  1. 避免建立冗余索引和重复索引(增加了查询优化器生成执行计划的时间)
  • 重复索引示例:primary key(id)、index(id)、unique index(id)
  • 冗余索引示例:index(a,b,c)、index(a,b)、index(a)

  1. 对于频繁的查询优先考虑使用覆盖索引

覆盖索引:就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引

覆盖索引的好处:

  • 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的,对于 Innodb 来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了 IO 操作,提升了查询效率。
  • 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的,对于 IO 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 IO 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

6.索引 SET 规范

尽量避免使用外键约束

  • 不建议使用外键约束(foreign key),但一定要在表与表之间的关联键上建立索引
  • 外键可用于保证数据的参照完整性,但建议在业务端实现
  • 外键会影响父表和子表的写操作从而降低性能

数据库 SQL 开发规范

  1. 禁止使用 SELECT * 必须使用 SELECT 查询

原因:

  • 消耗更多的 CPU 和 IO 以网络带宽资源
  • 无法使用覆盖索引
  • 可减少表结构变更带来的影响
  1. 避免使用子查询,可以把子查询优化为 join 操作

通常子查询在 in 子句中,且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因:

子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

由于子查询会产生大量的临时表也没有索引,所以会消耗过多的 CPU 和 IO 资源,产生大量的慢查询。

  1. 避免使用 JOIN 关联太多的表

对于 MySQL 来说,是存在关联缓存的,缓存的大小可以由 join_buffer_size 参数进行设置。

在 MySQL 中,对于同一个 SQL 多关联(join)一个表,就会多分配一个关联缓存,如果在一个 SQL 中关联的表越多,所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作,同时 join_buffer_size 设置的也不合理的情况下,就容易造成服务器内存溢出的情况,就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说,会产生临时表操作,影响查询效率,MySQL 最多允许关联 61 个表,建议不超过 5 个。

  1. 对应同一列进行 or 判断时,使用 in 代替 or

in 的值不要超过 500 个,in 操作可以更有效的利用索引,or 大多数情况下很少能利用到索引。

  1. 禁止使用 order by rand() 进行随机排序

order by rand() 会把表中所有符合条件的数据装载到内存中,然后在内存中对所有数据根据随机生成的值进行排序,并且可能会对每一行都生成一个随机值,如果满足条件的数据集非常大,就会消耗大量的 CPU 和 IO 及内存资源。

推荐在程序中获取一个随机值,然后从数据库中获取数据的方式。

  1. WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐:

where date(create_time)='20190101'

推荐:

where create_time >= '20190101' and create_time < '20190102'
  1. 在明显不会有重复值时使用 UNION ALL 而不是 UNION
  • UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
  • UNION ALL 不会再对结果集进行去重操作
  1. 拆分复杂的大 SQL 为多个小 SQL
  • 大 SQL 逻辑上比较复杂,需要占用大量 CPU 进行计算的 SQL
  • MySQL 中,一个 SQL 只能使用一个 CPU 进行计算
  • SQL 拆分后可以通过并行执行来提高处理效率

参考资料

一份非常完整的MySQL规范

https://baijiahao.baidu.com/s?id=1622786252178335118&wfr=spider&for=pc