MySQL-漫谈（三）

前言

前一阵子遇到生产服务器频繁GC问题，花了很多时间定位问题，最后通过分析dump文件定位到一个查询类SQL一次查询了十一万左右的数据，原因是入参未进行空字符判断，结果把所有符合条件的数据都查出来了，最终十一万数据放在一个list里面，服务器开始full gc，通过反思认为SQL 书写不规范导致，于是查阅了一些MySQL的约定规范。

命名规范

• 库名、表名、字段名必须使用小写字母，并采用下划线分割。
• 表名、字段名应当有注释，描述该表、字段的用途。
• 库名、表名、字段名见名知意，使用名词而不是动词。
• 库名、表名、字段名禁止超过32个字符，目的是为了统一规范、易于辨识以及减少传输量。
• 库名、表名、字段名，使用常见单词，避免使用长单词和生僻词。
• 表名不使用复数名词。
• 库名、表名、字段名禁止使用 MySQL 保留字，当库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

建库建表规范

• 库名称必须控制在 25 个字符以内，库名与应用名称尽量一致，库的命名规则必须契合所属业务的特点。

• 库名用小写（尽量不要使用除下划线、小写英文字母之外的其他字符，如果要用下划线，应该尽量保持一致的风格）

• 创建数据库时必须显示指定字符集，建议使用 utf8mb4 字符集， MySQL 5.5.3 之后增加 utf8mb4 编码，utf8mb4 是utf8 的一个扩展。许多新类型的字符，例如 emoji 这种类型的符号，utf8 不支持存储，但 utf8mb4 支持。所以，设计数据库时如果想要允许用户使用特殊符号，最好使用 utf8mb4 编码来存储，使得数据库有更好的兼容性。

• 每个表建立时必须加上表描述，方便参考理解、维护管理。

• 若没有特殊要求，存储引擎均采用默认的 InnoDB（ENGINE=InnoDB）。

• ROW_FORMAT 没有特殊需求时默认即可，不需要指定。ROW_FORMAT=Dynamic/Compressed只有在innodb_file_format=barracuda的情况下才支持，如果强制设置了，后续再对表进行DDL操作时会产生警告。

• 若没有特殊要求，统一设置为 utf8mb4，即 CHARSET=utf8mb4。关联查询时，若字符集不一致，可能会导致索引失效。

• 每个表建立时必须设置主键 id（PRIMARY KEY (id)），自增长（AUTO_INCREMENT）、步长为1，类型为整型（根据需要选择 INT 或 BIGINT。一般情况下业务表使用 BIGINT 类型，防止数据量增长后自增主键值不够用；对于数据量不会增长很多的配置表，可使用 INT 类型）。

• 表命名加上“业务名称_表的作用”，做到见名知其意。

• 同一个应用(或领域)下的表需要有相同的前缀名称。如用户类型表都以user_开头。

• 表命名做到专业、简洁、见名知意，多使用专业词汇命名，不使用拼音，以使用方便记忆、描述性强的可读性名称为第一准则，应尽量避免使用缩写或代码来命名。

• 表命名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字，应该尽量保持一致的风格。

• 表的字段顺序很重要，从前到后，按照字段的重要性和使用频率排列，create_time、update_time、remark 等字段在最后。按字段的分类归集排列，如金额相关的字段在一块，时间相关的字段在一块等。以方便查看、排查问题（注：增加字段时不采用after 和 first 关键字，原因是数仓增量采集数据时无法感知 after 的位置，如果在表中间位置加字段，会使数仓同步数据失败）。

• 限制每张表上的索引数量，建议单张表索引不超过5个，索引并不是越多越好。索引可以提高查询的效率，但会降低写数据的效率。有时不好的索引还会降低查询的效率。

• 必须把字段定义为 NOT NULL 并且提供默认值，NULL 的列使索引/索引统计/值比较都更加复杂，对 MySQL 来说更难优化。NULL 这种类型 MySQL 内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。NULL 值需要更多的存储空，无论是表还是索引中每行中的 NULL 的列都需要额外的空间来标识。

• 在一些场景下，考虑使用 TIMESTAMP 代替 DATETIME，这两种类型的都能表达"yyyy-MM-dd HH:mm:ss"格式的时间，TIMESTAMP 只需要占用 4 个字节的长度，可以存储的范围为（1970-2038）年，在各个时区，所展示的时间是不一样的。而 DATETIME 类型占用 8 个字节，对时区不敏感，可以存储的范围为（1001-9999）年。

• 在较长 VARCHAR 字段，例如 VARCHAR(100) 上建立索引时，应指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

• 索引必须创建在索引选择性选择性较高的列上,选择性的计算方式为: select count(distinct(col_name))/count(*) from tb_name;如果结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行。

• TEXT:仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。所有使用TEXT类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放。如无特殊需要，严禁开发人员使用MEDIUMTEXT、TEXT、LONGTEXT类型。

• 对于精确浮点型数据存储，需要使用DECIMAL，严禁使用FLOAT和DOUBLE。

查询规范

• SELECT 语句尽量加上limit，当只查询一条数据时加上limit 1。

• 严禁使用select * from table而不加任何where条件。

• SELECT 语句指定具体字段名称，杜绝使用 SELECT * 读取全部字段。尤其是当表中存在 text / blob 大列时就会是灾难，可能本来不需要读取这些列，但因为偷懒写成 SELECT * 导致内存 buffer pool 被这些“垃圾“数据把真正需要缓冲起来的热点数据给洗出去了。

• SELECT 语句避免使用 UNION，推荐使用 UNION ALL，并且子句个数限制在 5 个以内。因为 UNION ALL 不需要去重，节省数据库资源，提高性能。

• SELECT … WHERE … IN (…)，IN 值不要过多，限制在 500 以内，会增加底层扫描，影响查询效率。因为哪怕是基于索引的条件过滤，如果优化器意识到总共需要扫描的数据量超过 30% 时，就会直接改表执行计划为全表扫描，不再使用索引。

• SELECT 查询时必须加上 limit 限制查询行数，避免慢查询。前言中就是缺乏limit 限制导致查询到大量数据，出现full GC。

• 生产环境禁止使用 hint，hint 是用来强制改变 MySQL 执行计划，如 FORCE INDEX、IGNORE KEY、STARAIGHT JOIN 等，但随着数据量变化我们无法保证自己当初的预判是正确的，因此要充分相信 MySQL优化器。

• WHERE 条件中等号左右两边的字段类型必须一致，否则无法利用索引。

• WHERE 子句中禁止只使用全模糊的 LIKE 条件进行查找，必须有其他等值或范围查询条件。

• 索引列禁止使用函数或表达式，否则无法利用索引。如WHERE length(name)=’Admin’或WHERE user_id+2=10023。

• OR 语句慎用，OR语句可能会导致索引失效，尽量使用IN 代替OR，IN是范围查找，MySQL内部会对IN的列表值进行排序后查找，比OR效率更高。

  OR的效率是n级别， in的效率是log(n)级别。

• 使用 EXPLAIN 判断 SQL 语句是否合理使用了索引，尽量避免 Extra 列出现 Using FileSort，Using Temporary。

• 建议不要使用LIKE '%value'的形式，因为 MySQL 仅支持最左前缀索引，即LIKE '%value'不走索引而LIKE 'value%'是可以走一部分索引的，实际开发中可以使用ES来代替。

• 禁止在循环中CRUD。减少与数据库交互次数，尽量采用批量SQL语句。

• 禁止在where从句中对列进行函数转换和计算，会导致索引无效。

• 关联查询规范

  • 不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 JOIN 来代替子查询。
  • 多表 JOIN 不要超过3个表。
  • 多表 JOIN 时，要把过滤性最大（不一定是数据量最小，而是指加了 WHERE 条件后过滤性最大的那个）的表选为驱动表。
  • 如果 JOIN 之后有排序，排序字段只有在属于驱动表的情况下，才能利用驱动表的索引完成排序。
  • JOIN 的关联字段在不同表中的类型和命名要一致。

• ORDER BY、GROUP BY 使用规范

  • 减少使用 ORDER BY，和业务沟通能不排序就不排序或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句都比较耗费 CPU。
  • 包含了 ORDER BY、GROUP BY、DISTINCT 这些查询的语句，必须加 LIMIT，限制行数控制在 1000 以内。
  • ORDER BY、GROUP BY、DISTINCT 尽量利用索引直接检索出排序好的数据。
  • 多字段联合索引情况下，WHERE 中过滤条件的字段顺序无需和索引一致，但如果有排序、分组则必须一致，比如联合索引idx(a,b,c)。
  • GROUP BY 时会按照一定规则进行排序，如果业务上对顺序没有要求，可以加 ORDER BY NULL 提高查询效率。

• 不允许使用属性隐式转换，如根据用户手机号查询用户名字，select user_name from user_info where account = '13812345678' 与select user_name from user_info where account = 13812345678 语句是完全不同的，后者不会走索引会扫描全表。

DML（data manipulation language）数据操纵规范

• INSERT 语句需要指定具体字段名称，禁止写成INSERT INTO table VALUES(…)。

• 事务涉及的表必须全部是 InnoDB 表，否则一旦失败不会全部回滚。

• 禁止在业务的更新类 SQL 语句中使用 JOIN，比如UPDATE t1 JOIN t2 …。

• 事务中 INSERT | UPDATE | DELETE | REPLACE 语句操作的行数控制在 2000 以内，一次性提交过多的记录会导致线上 I/O 紧张，出现慢查询，引起主从同步延迟。

• UPDATE、DELETE 语句需要根据 WHERE 条件添加索引。

• 批量操作数据时，需要控制事务处理间隔时间，进行必要的 sleep，一般建议 5-10 秒。

• 禁用 procedure、function、trigger、views、event、外键约束，因为它们会消耗数据库资源，降低数据库集群可扩展性。推荐都在程序端实现。

• 禁止使用关联子查询，如UPDATE t1 SET … WHERE name IN(SElECT name FROM user WHERE …)效率极其低下。

• 禁止联表更新语句，如UPDATE t1,t2 WHERE t1.id=t2.id …。

• 禁用 INSET INTO … ON DUPLICATE KEY UPDATE …原因如下：

  • 在高并发环境下，容易发生死锁 。
  • 会造成自增主键 id 不连续，假设原来有数据 300 条，用 DUPLICATE KEY 插入 100 条数据，其中前 99 条都是和原来有重叠的，只有最后一条是新增的，那么最后一条 id 会从 400 开始。

• 禁用INSERT IGNORE …，在高并发环境下，容易发生交叉死锁。

• UPDATE / DELETE 禁止使用关联子查询，如 UPDATE t1 SET … WHERE name IN(SELECT name FROM user WHERE …)。

• UPDATE / DELETE 禁止联表更新，如UPDATE t1,t2 WHERE t1.id=t2.id …。

• UPDATE / DELETE 禁止使用 JOIN，比如UPDATE t1 JOIN t2 …。

• UPDATE / DELETE 禁止使用模糊查询 LIKE，可能会出现UPDATE / DELETE 表中所有数据。

• 禁止在数据库中明文储存密码等关键数据。