MySQL 注意事项

数据模型与索引设计

字符集
一般使用utf8，需要使用表情时使用utf8mb4。排序规则一般使用utf8_general_ci。
排序规则：
- _cs：区分大小写
- _ci：不区分大小写
- _bin：以二进制数据存储，且区分大小写
索引
1. 最左前缀匹配原则
  
  联合索引可以用于包含索引中所有列的查询条件的语句, 或者包含索引中的前 N 列
2. 覆盖索引
  
  从非聚簇索引中就能查到的记录，而不需要查询聚簇索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。
  - 聚簇索引：按创建索引时指定的列的顺序物理地排列表内容
  - 非聚簇索引：由索引键和指定数据块的标记组成
3. 索引列设置为 NOT NULL, 否则会全表扫描
4. 索引失效
  1. 索引列出现了隐式类型转换
  2. 在使用 CAST 函数时，需要保证字符集一样
  3. 如果 WHERE 条件中含有 OR，除非 OR 条件中的所有列都是索引列，否则 MySQL 不会选择索引
  4. 对于多列索引，若没有使用前导列，则 MySQL 不会使用索引
  5. 在 WHERE 子句中，如果索引列所对应的值的第一个字符由通配符开始，索引将不被采用
  6. 如果 MySQL 估计使用全表扫描要比使用索引快，那么 MySQL 将不会使用索引
  7. 如果对索引字段进行函数、算数运算或其他表达式操作，那么 MySQL 也不使用索引

查询数据

过滤与查找数据
1. 查找不匹配或缺失的记录
  
  在子查询返回结果集大的时候使用 EXISTS 通常比 IN 更快，另一种是 LEFT JOIN 结合WHERE。
  - EXISTS：数据库引擎在找到第一行会停止运行子查询
  - IN：数据库引擎会检索所有行
2. 按时间范围正确地过滤日期和时间
  1. 不要依赖隐式日期转换，使用显式转换函数 CONVERT() 来处理日期字符。
  2. 不要将函数应用于日期和时间列，否则查询不能使用索引。
  3. 舍入误差可能导致日期和时间值不正确。使用 >= 和<代替BETWEEN。可以使用DATEADD()
3. 书写可参数化搜索的查询以确保引擎使用索引
  1. 使用=、>、<、>=、<=、BETWEEN、ISNULL、LIKE(不包括前导通配符)。
  2. 不要再 WHERE 子句中的一个或多个字段上使用函数。
  3. 不要对 WHERE 子句中的字段进行算术运算。
聚合
1. 理解 GROUP BY 工作原理
  1. FROM子句生成数据集
  2. WHERE子句过滤由 FROM 子句生成的数据集
  3. GROUP BY子句聚合由 WHERE 子句过滤的数据集
  4. HAVING子句过滤由 GROUP BY 子句聚合的数据集
  5. SELECT子句转换过滤的聚合数据集
  6. ORDER BY子句对变换后的数据集进行排序
2. 使用 OUTER JOIN 时避免获取错误的 COUNT()
  
  数据库版本没有 RANK() 函数时，可以使用 COUNT() 子查询来实现。
  1. 使用 COUNT(*) 来统计所有记录的总数，也包括空值的记录。
  2. 使用 COUNT() 仅统计列值不为 NULL 的记录的总数。
  3. 有时一个子查询甚至一个关联子查询也会比 GROUP BY 更有效率。
关联子查询: 子查询中的部分条件依赖于外部查询中正在处理的当前记录值。
1. 避免使用 GROUP BY 来查找最大或最小值
  
  使用 LEFT JOIN 自身以及 WHERE 来查询，ON子句的列添加索引
2. GROUP_COUNT()中也可以使用 ORDER BY

排序

排序时对 Null 值的处理

对查询结果进行排序，但该字段可能为 Null，需要将 Null 值排到前面或后面。

SELECT
    column1,
    CASE WHEN ISNULL(column1) THEN 0 ELSE 1 END AS is_null
FROM
    table1
ORDER BY
    is_null, column1

依据条件逻辑动态调整排序项

按照某个条件逻辑来选择不同的排序列。

SELECT
    column1
FROM
    table1
ORDER BY
    CASE WHEN column1 = 'a' THEN column2 ELSE column3 END

Explain type 字段

system

表只有一行，这是一个const type 的特殊情况
const

最多只有一行匹配
eq_ref

只匹配到一行的时候。除了 system 和const之外，这是最好的连接类型了。当我们使用主键索引或者唯一索引的时候，且这个索引的所有组成部分都被用上，才能是该类型
ref

触发联合索引最左原则，或者这个索引不是主键，也不是唯一索引
fulltext

使用全文索引的时候才会出现
ref_or_null

这个查询类型和 ref 很像，但是 MySQL 会做一个额外的查询，来看哪些行包含了 NULL
index_merge

在一个查询里面很有多索引用被用到，可能会触发 index_merge 的优化机制
unique_subquery

此类型将 eq_ref 替换为 IN 形式的某些子查询。
unique_subquery 只是一个索引查找函数，可以完全替换子查询以提高效率
index_subquery

类似于 unique_subquery，但是它在子查询里使用的是非唯一索引
range

只有给定范围内的行才能被检索，使用索引来查询出多行。输出行中的类决定了会使用哪个索引。 key_len 列表示使用的最长的 key 部分。这个类型的 ref 列是 NULL
index

index 类型和 ALL 类型一样，区别就是 index 类型是扫描的索引树。以下两种情况会触发：
1. 如果索引是查询的覆盖索引，就是说索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。在这种情况下，explain 的 Extra 列的结果是 Using index。仅索引扫描通常比 ALL 快，因为索引的大小通常小于表数据。
2. 全表扫描会按索引的顺序来查找数据行。使用索引不会出现在 Extra 列中。
ALL

全表扫描。通常，可以通过添加索引来避免 ALL。

注意

Limit 偏移量大时可以先单独查询 Id 再关联，或者记录上次查询的最大 Id。
对于 MAX()和 MIN()函数可以考虑使用 ORDER BY 和 LIMIT 1 代替。
确保 GROUP BY 和 ORDER BY 只涉及一个表中的字段，这样才可能使用索引。
GROUP BY 会自动按照分组的字段升序排序，可直接使用 DESC 和 ASC 排序，使用 ORDER BY NULL 不进行排序。
GROUP BY * WITH ROLLP 得到每个分组以及每个分组汇总级别的值。
一旦使用 DISTINCT 和 GROUP BY 需要产生临时表。
复杂的 OR 条件可以使用多条 SELECT 语句和连接它们的 UNION 语句。
如果无需去重请使用 UNOIN ALL，UNION 如果需要排序只需要在最后一条语句上使用ORDER BY。
如果数据检索是最重要的，则你可以通过在 INSERT 和 INTO 之间添加关键字 LOW_PRIORITY，指示 MySQL 降低 INSERT 语句的优先级，如INSERT LOW_PRIORITY INTO。

参考：

Effective SQL 编写高质量 SQL 语句的 61 个有效方法
Java 工程师修炼之道

数据模型与索引设计

字符集

索引

最左前缀匹配原则

覆盖索引

索引列设置为 NOT NULL, 否则会全表扫描

索引失效

查询数据

过滤与查找数据

查找不匹配或缺失的记录

按时间范围正确地过滤日期和时间

书写可参数化搜索的查询以确保引擎使用索引

聚合

理解 GROUP BY 工作原理

使用 OUTER JOIN 时避免获取错误的 COUNT()

避免使用 GROUP BY 来查找最大或最小值

GROUP_COUNT()中也可以使用 ORDER BY

排序

排序时对 Null 值的处理

依据条件逻辑动态调整排序项

Explain type 字段

注意