【MySQL】SQL语句优化

索引属性限定

1. 避免索引字段使用 null

• B+ 树索引必须先进行比较当前值后再确定下一步查找范围，null 值只能全盘扫描。
• 包含 null 值的列无法作为索引，聚合索引中只要有一列存在 null 就无法使用索引。

2. 避免使用空值判断

• 避免使用 !=、<>、NOT、is null、is not null、in、not in 操作符，输入范围不确定，会进行全盘扫描。

3. 避免在索引字段上使用函数或表达式操作

• 索引保存的是原数值，对索引字段使用函数或表达式操作，会导致引擎放弃使用索引而进行全表扫描。
• 算数函数：加减乘除、求绝对值、求余、四舍五入。
• 字符串：拼接、长度、大小写、字符替换、字符串截取、日期。
• 转换函数：CAST类型转换。

4. 在 WHERE/ORDER BY 添加索引

• 在 SELECT 输出栏添加索引没有意义，应该在选择条件 WHERE、ORDER BY 上添加索引。

5. 前缀索引

• 使用某个字段中字符串的前几个字符建立索引，减小索引字段大小，提高索引的查询速度。

6. 覆盖索引

• 从二级索引中查询得到记录，不需要通过聚簇索引查询获得，避免回表操作。

7. 自增主键

• 自增主键，每次插入的新数据都会按顺序添加到当前索引节点，不需要移动已有的数据。当页面写满，就会自动开辟一个新页面。
• 非自增主键，每次插入主键的索引值都是随机的，可能会插入到现有数据页中间，甚至需要从一个页面复制数据到另外一个页面，称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，影响查询效率。

索引语句

1. 避免使用 select *

• 会进行全盘扫描，推荐使用具体的查询字段，建立索引，提高效率。

2. 复杂子查询使用 exists 替代 in

• in 会先执行子查询保存到加索引的临时表，再和外主表做笛卡尔积，再通过条件筛选数据加入结果集，适用于子查询简单场景。
• exists 会先遍历外主表，每次遍历时，在子表根据索引匹配查找。适用于外表为小表，用小表驱动大表，子查询表越小效率越高。

3. 使用 between 替代 in

• 对于连续的数值，能用 between 就不要用 in，in 不走索引。

4. 使用 order by 需要添加索引

• order by 尽量不使用，若使用要对使用的字段添加索引。
• order by 避免使用计算表达式。

5. 避免 or 导致索引失效

• or 进行条件判断，所有字段必须都添加索引，否则会全盘扫描。
• or 后的字段可以单独添加索引，防止索引失效。

高性能 SQL

1. 使用 unionall 替代 union

• union 会在结果集合并后做排序和唯一性过滤。
• unionall 避免合并后排序，可以通过数据源自身保证唯一性。

2. 多用 limit

• limit 读取固定数量的记录，提高查询效率。
• limit 数据量过高时，先通过 where 设定索引起始位置，再用 limit 限定数量进行分页。
• limit 数据量过多时，使用 between 设置索引范围，替换 limit。

3. 使用批量插入

• 批量插入比单条插入效率高。insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);

4. group by 避免排序

• group by 默认会对结果自动排序，通过添加 order by null 避免排序。
• group by 的字段最好添加索引，可以避免创建临时表。
• having 在检索出所有记录后再对结果过滤，通过 where 提前过滤字段。

5. left join 左表使用小表

• left join 返回主表所有字段。右表非空时，left join = inner join。
• left join 主表作为驱动表，左表应选择小表，或使用 where 过滤字段，左表太大循环次数越多。

6. like 使用 左 like 效率更高：LIKE "A%"。

数值类型

1. 合理使用数值型字段

• 数据库字段使用字符型，存储引擎处理查询时会逐个比较字符，降低低查询性能，数值型数据只需要比较一次。

2. 合理使用 varchar

• char、varchar 都用于存储字符类型数据。
• char 类型实际存储的字符长度小于其定义规格，会在尾部用空格补齐，对于小的字符串数值使用 varchar 可以节省空间。

Explain

• EXPLAIN 执行计划，只能用于 select 语句前。
• 主要字段：type、key、rows。

mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1
2
3
4
5
6

1. id 执行编号，语句执行顺序，值越大，语句优先级越高，越先执行，有几个 select 就有几个 id。
2. select_type 查询类型，普通查询、联合查询、子查询。

• SIMPLE：简单查询，不包含子查询，没有使用UNION、UNION ALL、DISTINCT、GROUP BY、HAVING、LIMIT等关键字。
• PRIMARY：表示查询中包含一个或多个子查询。MySQL 会先执行主查询，再执行子查询。
• SUBQUERY：表示查询中的第一个子查询，子查询包含在 SELECT 列表中的子查询或 WHERE 子句中的子查询。
• DERIVED：表示查询中的子查询，派生表。MySQL 会先执行子查询，然后将结果存储在一个临时表中，再执行主查询。
• UNION：表示将多个查询的结果集合并成一个结果集。
• UNION RESULT：表示查询中的UNION操作的结果集。
• DEPENDENT SUBQUERY：表示查询中的子查询依赖于外部查询的结果集。MySQL 会根据外部查询的结果集来执行子查询。
• DEPENDENT UNION：表示查询中的 UNION 操作依赖于外部查询的结果集。MySQL 会根据外部查询的结果集来执行UNION操作。
• DEPENDENT UNION RESULT：表示查询中的 UNION 操作的结果集依赖于外部查询的结果集。MySQL 会根据外部查询的结果集来执行 UNION 操作。

3. type 访问类型，至少达到 range 索引范围扫描，最好能达到 ref 非唯一索引扫描。效率从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

• system：表只有一行记录（等于系统表），const 的特例，平时不会出现。

• const：结果只有一行匹配。

• eq_ref ：唯一索引扫描，使用非 null 值的唯一索引。

• ref：非唯一索引扫描，使用普通、非主键、非唯一的索引，最好能达到。

• ref_or_null：某字段既需要关联条件，也需要null值。

• index_merge：在查询过程中需要多个索引组合使用

• unique_subquery：使用唯一索引，类似 index_subquery。

• index_subquery：利用索引关联子查询，不再扫描全表。

• range：索引范围扫描，使用索引查询时限制了范围，避免了全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or， IN() 。

• index：全索引扫描，使用了覆盖索引，或使用索引进行排序，避免了数据的重排。

• all：全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。

4. table 表名或别名。
5. possible_keys 表中可以使用的索引，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
6. key 实际使用的索引，为 null 表示没有使用索引。
7. key_len 索引字段的长度。
8. ref 查找使用的索引的列或常量。
9. rows 估算查找所需记录需要读取的行数。
10. extra 额外信息。

• using filesort：无法利用索引进行排序，只能利用排序算法进行排序。
• using temporary：建立临时表来保存中间结果，查询完成之后把临时表删除。
• using index：覆盖索引，直接从索引中读取数据，而不用访问数据表。
• using where：使用 where 进行条件过滤。
• using join buffer：使用连接缓存。
• impossible where：where 语句的结果总是 false。

11. partitions 如果查询是基于分区表的话，会显示查询将访问的分区，MySQL 8 新增。
12. filtered 按表条件过滤的行百分比。 rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表）MySQL 8 新增。

---

高性能SQL
SQL优化1
SQL优化2
SQL优化3
SQL优化4
SQL

Explain
Explain结果字段