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

相关文章