Database Knowledge and Question

本文最后更新于 2 分钟前，文中所描述的信息可能已发生改变。

SQL 使用规范 ​

  • 禁止 SELECT *：只取需要的字段，避免宽表回表和无用数据。
  • 禁止无条件全表扫描：必须带 WHERE 条件。
  • TEXT / 大字段：避免与普通字段混存；不需要就不要查。
  • WHERE 条件字段不能加函数 / 运算：避免索引失效。
  • 分页必须有 ORDER BY，避免结果集乱序。
  • 避免 OR：用 IN/UNION 替代（IN 元素数 < 300）。
  • 禁止前缀模糊查询：LIKE '%xxx'，只能 xxx%。
  • 少用子查询：用 JOIN 代替，避免临时表和无索引。
  • 多表 JOIN：小结果集表优先做驱动表。
  • 大偏移分页优化：用索引范围过滤再 LIMIT。

索引设计规范 ​

  1. 正确理解和计算索引字段的区分度，下面是计算规则区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。我们创建索引的时候，尽量选择区分度高的列作为索引。计算规则：selecttivity = count(distinct c_name)/count(\*)。

  2. 正确理解和计算前缀索引的字段长度，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。计算规则：select count(distinct left(c_name , calcul_len)) / count(\*) from t_name;。下面长度为 6 的时候是最佳状态。

    shell
    mysql>  SELECT
    count(DISTINCT LEFT(empname, 3)) / count(*) AS sel3,
    count(DISTINCT LEFT(empname, 4)) / count(*) AS sel4,
    count(DISTINCT LEFT(empname, 5)) / count(*) AS sel5,
    count(DISTINCT LEFT(empname, 6)) / count(*) AS sel6,
    count(DISTINCT LEFT(empname, 7)) / count(*) AS sel7
FROM
    emp;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set

  3. 联合索引注意最左匹配原则：按照从左到右的顺序匹配，MySQL 会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。如 depno=1 and empname>'' and job=1 ，如果建立(depno,empname,job)顺序的索引，empname 和 job 是用不到索引的。

  4. 应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

  5. 正确判断是否使用联合索引（ 策略篇 联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

  6. 避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

  7. 避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

  8. 模糊查询’%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%'是可以有效利用索引。

  9. 索引覆盖排序字段，这样可以减少排序步骤，提升查询效率。

  10. 尽量的扩展索引，非必要不新建索引。比如表中已经有 a 的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

  11. 无需强制索引顺序，比如 建立(depno,empno,jobno)顺序的索引，你可以是 empno = 1 and jobno = 2 and depno = 8。因为 MySQL 的查询优化器会根据实际索引情况进行顺序优化，所以这边不强制顺序一致性。但是同等条件下还是按照顺序进行排列，比较清晰，并且节省查询优化器的处理。

  • 索引区分度 > 0.2 才有价值。计算规则：select count(distinct col)/count(*) from t;。
  • 组合索引：遵循最左前缀原则，把区分度最高的字段放前面。
  • 避免外键：完整性约束由程序保证。
  • 字符串索引用前缀索引，注意区分度。
  • 单表索引数量 ≤ 5，写多读少场景尽量少建索引。
  • 排序/分组字段加到索引后面，形成覆盖索引。
  • 联合索引注意最左匹配原则，范围查询会截断匹配，后续字段无法用索引。
  • 正确判断是否使用联合索引，应避免索引下推（IPC），减少回表操作，提升效率。
  • 应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。
  • 避免索引失效：
    • 对索引字段做函数/运算符操作。
    • 类型不一致（int vs string）。
    • LIKE '%xxx%' 模糊前缀。
  • 扩展已有索引，避免重复建新索引。

慢 SQL 优化步骤 ​

  1. 先运行查看实际耗时，判断是否真的很慢（注意设置SQL_NO_CACHE）。
  2. EXPLAIN 查看执行计划，是否与 1 预期一致（从锁定记录较少的表开始查询）。
  3. 高区分度优先策略：WHERE 条件单表排查，锁定最小返回记录表的条件。 就是查询语句的 WHERE 都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高。区分度高的字段往前排。
  4. 涉及ORDER BY、GROUP BY 形式的 sql 语句的表优先排查。
  5. 了解业务方的使用场景，根据使用场景适时调整。
  6. 增加索引时参照建索引设计规范。
  7. 观察结果，不符合预期继续从 1 开始分析。

常见问题 ​

  • 数据库结构优化
  • MySQL 数据库 CPU 使用率飙升的原因
  • 为什么要分库分表
  • 分库分表的具体实施策略
  • 分库分表存在那些问题
  • 什么是 MySQL 主从复制
  • MySQL 主从复制的工作原理
  • MySQL 读写分离的实现方案
Explain 的作用是什么？

Explain 是 MySQL 提供的用于分析查询语句的工具，可以帮助开发人员分析查询语句的性能，找出可能存在的问题。重要的关注指标包括：select_type、type、possible_keys、key、rows 等。

字段说明
id查询的序列号，表示查询中执行 select 子句或操作表的顺序
select_type查询的 select 子句类型，主要有：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT 等
table查询的表
type查询的类型方式，主要有：system、const、eq_ref、ref、range、index、all 等
possible_keys可能使用的索引，表示 MySQL 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。这个趋向于指导性作用。
key实际使用的索引，显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL
key_len使用的索引的长度
ref显示索引的哪一列被使用了，如果可能的话，是一个常数
rowsMySQL 查询时预估扫描的行数
Extra额外的信息，包括不适合在其他列中显示但是还是重要的信息
慢 SQL 优化如何实现？

定位慢 SQL：

  1. 开启慢查询日志，在 mysql.slow_log 或文件中找到慢 SQL。

    sql
    SET GLOBAL slow_query_log = on; -- 默认关闭
SET GLOBAL long_query_time = 1; -- 记录执行时间超过 1s 的 SQL

  2. 使用 EXPLAIN 分析执行计划，查看是否命中索引、扫描行数（rows）、是否使用 filesort / temporary。

    sql
    EXPLAIN SELECT * FROM user WHERE name='helltractor';

常见优化手段：

  1. 索引优化
    • 区分度：根据区分度判断是否建立索引。
    • 建立合适索引：单列索引、联合索引、覆盖索引。
      • 联合索引注意最左前缀原则，避免索引下推，减少回表次数。
      • 进行排序、分组字段需要添加在索引后面，形成覆盖索引。
    • 避免索引失效的场景：
      • 联合索引遇到范围匹配。
      • LIKE '%xxx' 前缀模糊匹配。
      • 在索引列上进行计算或函数操作：WHERE YEAR(date)=2025。
      • 类型不一致的比较：WHERE id = '123'（id 是 int）。
  2. SQL 语句优化
    • 避免 SELECT *的使用，造成不必要的回表。
    • 避免 IN 大量子查询，替换为 JOIN 或 EXISTS。
    • 减少 OR的使用，替换UNION ALL或IN。
    • 避免索引失效的场景发生。
材料计算常用资源