MySQL应用技术7 — 性能优化简析
声明：本文来自于薛晓满同学的分享记录。
MySQL应用技术1 — MySQL架构简介
MySQL应用技术2 — 事务简介
MySQL应用技术3 — MVCC
MySQL应用技术4 — 数据类型选择
MySQL应用技术5 — 约束与范式
MySQL应用技术6 — 数据库中的锁
一、 缓存
数据库在接收到查询语句后会先判断是否命中缓存，如果命中则会直接返回缓存的数据结果。
查询缓存会保存最新数据结果，所有表数据的任何变化（INSERT、UPDATE、DELETE或其他可能产生数据数据变化的操作）都会刷新查询缓存。因此查询缓存特别适用于表数据变化不频繁，且有相同语句频繁查询的场景。推荐 99% 以上只读，很少更新的情况下可以考虑开启查询缓存。
具体了解可以参考：线上环境到底要不要开启query cache
目前常用的做法是在应用程序层面增加缓存。
二、 SQL
1. 查询需要的数据
(1) 查询需要的行
当明确知道需要返回的记录的条数时，使用 LIMIT 限制查询数据的条数。当查询结果条数满足时，会提前终止查询返回结果。
例如：当我们知道某条 SQL 的返回结果只可能是一条记录时，就使用 LIMIT 1 。
(2) 查询需要的列
只查询需要的列，有助于减少额外的 I/O、内存和 CPU 消耗，并且可能会利用覆盖索引返回数据，无须再回表查询。
2. 重构查询
(1) 切分查询
当一条 SQL 影响的行数很大时，可以每次只操作一定量的行数，在应用程序层面循环处理至所有数据都已进行完操作。
例如：我们要删除 id 小于 1000000 的所有数据，可以使用如下写法：
DELETE FROM log WHERE id < 1000000 LIMIT 10000;
应用程序层面可以使用如下写法：
int deletedRows = 0;
do {
deletedRows = dao.deleteLogs();
} while(deletedRows > 0);
优点
- 减少锁的持有时间
- 减少 MySQL 的复制延迟
- 分散数据库的压力
(2) 分解关联查询
将关联查询分解成几个单表查询语句执行，在应用程序中进行关联。
例如：查询一班所有学生的成绩
SELECT * FROM score JOIN student ON student.id = score.student_id WHERE class = '一班';
就可以分解成两个单表查询
SELECT student_id WHERE class = '一班'; -- 假设返回结果为 1,2,3,4
SELECT * FROM score WHERE student_id IN (1,2,3,4);
优点
- 减少锁竞争
- 提升查询效率
- 提高代码复用率
(3) 最大值和最小值优化
对于 MIN() 和 MAX() 查询，可以在相应的字段加上索引，然后 MySQL 会使用最优方式 SELECT tables optimized away 通过索引直接一次定位到所需的数据行完成整个查询。
SELECT MAX(score) FROM math_score WHERE score >= 60
SELECT MAX(score) FROM math_score WHERE score >= 60 AND exam_id = 1
若 SELECT 的字段和查询条件中的字段均为同一个索引字段，则会使用最优方式；否则会使用索引。
(4) LIMIT 分页优化
MySQL 并不是跳过 offset 行，而是取 offset + N 行，然后放弃前 offset 行，返回 N 行数据。所以数据库库需要先扫描大量无用的行，导致执行效率降低。
SELECT * FROM person ORDER BY `name` LIMIT 100000, 10;
优化方案：
-
使用覆盖索引扫描
SELECT id FROM person ORDER BY `name` LIMIT 100000, 10; SELECT * FROM person WHERE id in (...);先利用覆盖索引扫描，使得第一个查询只需要扫描
name字段的索引，而不用去回表查询，大大减少了需要扫描的数据量；然后用第二个查询使用主键查询返回结果。 -
使用标记
SELECT * FROM person WHERE `name` > '赵一' ORDER BY `name` LIMIT 10;选用排序的字段为标记，会使用该字段索引。
(5) UNION 优化
除非确实需要服务器消除重复的行，否则一定要使用 UNION ALL 。如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。
(7) 多表时使用表名
当在 SQL 语句中连接多个表时，使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间，并减少相同列名引起的歧义语法错误。
3. 事务优化
避免使用大事务操作，以提高系统并发能力。
将与 SQL 无关的校验和参数组装等逻辑放在事务外进行，事务中尽量只进行 SQL 的执行。
三、 索引
1. 联合索引将区分度较高的字段放在前面
idx_name_age_gender (name,age,gender) 就比 idx_gender_age_name (gender,age,name) 好很多。
当我们使用第一个联合索引进行查询时，会快速过滤掉大量数据，从而减少查询时间。
2. 匹配索引最左前缀
-
当我们建了一个联合索引
idx_name_age_gender (name,age,gender)后，则其最左前缀字段的索引都不需要再建立，即：idx_name_age (name,age)和idx_name (name)不需要再建立。 -
写
WHERE条件时，要按照联合索引的顺序写条件，如果有跳过索引的一个字段，则其后的索引不生效。 -
联合索引的顺序是根据最左前缀匹配，与
WHERE条件后字段的先后顺序无关。
3. 减少索引冗余
不好的索引不但不会起作用，反而给数据库带来负担，插入、修改都会重新调整索引结构，存储成本增加，写效率降低，同时数据库系统也要消耗资源去维护。
- 能扩展原有索引，就不新建索引
- 能用单索引，就不用联合索引
- 索引尽量建立在小字段、区分度较高的字段上
- 尽量只给最频繁的语句建立索引
4. 范围查询尽量联合索引放在后面
范围查询（<, > , BETWEEN , LIKE）之后的索引（联合索引中的顺序）无效。
5. 索引字段不能参与计算、不能使用函数
age + 1 < 13 和 ADDDATE(date, 1) = '2019-02-28 18:00:00'这样的条件不会使用索引，可以将其转换等价的操作，如：age < 13 - 1 和 date = SUBDATE('2019-02-28 18:00:00', 1) ；也可以直接在内存中计算好再查询。
6. 避免一切隐式转换
隐式转换会有额外开销。
SELECT * FROM person WHERE `name` = 17; --不走索引
SELECT * FROM person WHERE `name` = '17' and age = '17'; --走索引
字符字段与数字类型做比较判断不会走索引，而数字字段与字符类型做比较会走索引。
7. 减少使用 NOT IN 、 NOT EXISTS 、 !=
使用这些条件时，通常选取的结果集比较大，所以 MySQL 不会使用索引，而进行全部表扫描。
如果确定返回的结果集比较小，那么可以调整逻辑上等价的条件，比如将 != 替换成 > 或 < 等方式。
也可以尽量将联合索引中的这些字段往后放。
8. 减少 null 字段
如果对字段进行 null 判断，则不会使用索引，而进行全部表扫描。
可以采用默认值的方式避免 null 字段产生。
