mysql-调优

最新推荐文章于 2023-03-24 22:43:05 发布

enjoymeizi

最新推荐文章于 2023-03-24 22:43:05 发布

阅读量298

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_36483100/article/details/129403108

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

性能分析工具

参照链接

long_query_time ：默认10秒 SHOW VARIABLES LIKE '%long_query_time%'; ,设置set [global] long_query_time = 1;
slow_query_log：查看慢查询日志SHOW VARIABLES LIKE '%slow_query_log%';设置开启SET GLOBAL slow_query_log='ON';
slow_query_log_file：慢查询日志保存
查看系统慢查询记录 SHOW GLOBAL STATUS LIKE '%Slow_queries%';
min_examined_row_limit: 这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。默认值为0SHOW VARIABLES LIKE 'min_examined_row_limit%';

mysqldumpslow

windows 下没有这个工具,查看命令帮助 mysqldumpslow --help
命令

# 得到返回记录集最多的 10 个 SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 

# 得到访问次数最多的 10 个 SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log 

# 得到按照时间排序的前 10 条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 

# 另外建议在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

查看 SQL 执行成本：SHOW PROFILE

查看show variables like 'profiling';,打开set profiling = 'ON';
查看当前会话有哪些profilesshow profiles;
查询最近一次的开销show profile; show profile cpu,block io for query 2;

EXPLAIN

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                         |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9595 |   100.00 | NULL                          |
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
2 rows in set, 1 warning (0.00 sec)

table

不论我们的查询语句有多复杂，包含了多少个表，到最后也是需要对每个表进行单表访问的，所以 MySQL 规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名（有时不是真实的表名字，可能是简称）。

id

① id 如果相同，可以认为是一组，从上往下顺序执行
② 在所有组中，id 值越大，优先级越高，越先执行
③ 关注点：id 号每个号码，表示一趟独立的查询,一个 SQL 语句的查询趟数越少越好

select_type

（1）一条大的查询语句里边可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句，而每个 SELECT 关键字的 FROM 子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的。

（2）MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下 select_type 都能取哪些值，请看官方文档:
在这里插入图片描述

partition（可略）

（1）代表分区表中的命中情况，非分区表，该项为 NULL。一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL。
（2）如果想详细了解，可以如下方式测试。创建分区表：

# 创建分区表，
# 按照 id 分区，id < 100 为 p0 分区，其它为 p1 分区
CREATE TABLE user_partitions (
id INT auto_increment,
NAME VARCHAR(12), 
PRIMARY KEY (id)
)
PARTITION BY RANGE(id)(
	PARTITION pe VALUES less than(100),
	PARTITION p1 VALUES less than MAXVALUE
);

DESC SELECT * FROM user_partitions WHERE id > 208;

type（重要）

执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，又称“访问类型”，其中的 type 列就表明了这个访问方法是什么，是较为重要的一个指标。比如，看到 type 列的值是 ref，表明 MySQL 即将使用 ref 访问方法来执行对 s1 表的查询。
完整的访问方法如下：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL（访问类型越往后，说明效果越差）。

system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory 等，那么对该表的访问方法就是 system。INNODB插入一条记录是all。
const：当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const。
eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。
ref: 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。
ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是 ref_or_null。
index_merge：单表访问方法时在某些场景下可以使用 Intersection、Union、Sort-Union 这三种索引合并的方式来执行查询
unique_subquery：unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是unique_subquery。
range：如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法。
index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。
ALL ：全表扫描。

小结

一般来说，这些访问方法中除了 ALL 这个访问方法外，其余的访问方法都能用到索引，除了 index_merge 访问方法外，其余的访问方法都最多只能用到一个索引。
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
其中比较重要的几个提取出来（见上面的加租访问方法）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

possible_keys 和 key

possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
key 列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。比方说下面这个查询：

```mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上述执行计划的 possible_keys 列的值是 idx_key1, idx_key3，表示该查询可能使用到 idx_key1, idx_key3 这两个索引，然后 key 列的值是 idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用 idx_key3 来执行查询比较划算。

key_len（重要）

key_len 指实际使用到的索引长度（单位是字节数），帮忙检查是否充分地利用上了索引，对于同一个索引来说，其值越大越好。它主要针对于联合索引，有一定的参考意义
key_len 的长度计算公式：

varchar(10)变长字段且允许 NULL = 10 * (character set:utf8 = 3, gbk = 2, latin1 = 1) + 1(NULL) + 2(变长字段) 

varchar(10)变长字段且不允许 NULL = 10 * (character set:utf8 = 3, gbk = 2, latin1 = 1) + 2(变长字段)

char(10)固定字段且允许 NULL = 10 * (character set:utf8 = 3, gbk = 2, latin1 = 1) + 1(NULL) 

char(10)固定字段且不允许 NULL = 10 * (character set:utf8 = 3, gbk = 2, latin1 = 1)

ref

ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       | 9595 |   100.00 | NULL  |
|  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.s1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL | 9595 |   100.00 | NULL                  |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | func |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

rows（重要）

rows：预估的需要读取的记录条数，值越小越好。

filtered

filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比。

Extra

我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。MySQL 提供的额外信息有好几十个，这里只挑比较重要的额外信息来介绍

No tables used：当查询语句的没有 FROM 子句时将会提示该额外信息。EXPLAIN SELECT 1;
Impossible WHERE：查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。 EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
Using where：当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在Extra列中会提示该额外信息。此外，当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述额外信息。EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';,EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
No matching min/max row：当查询列表处有 MIN 或者 MAX 聚合函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息。EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在 Extra 列将会提示该额外信息。比方说下边这个查询中只需要用到 idx_key1 而不需要回表操作：EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  365 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

key1 > ‘z’ 可以使用到索引， key1 LIKE ‘%a’ 却无法使用到索引
优化有之前>‘z’ 直接回表改成过滤 like ‘%a’ 后回表，
这种操作叫做索引条件下推

如果在查询语句的执行过程中将要使用索引条件下推这个特性，在 Extra 列中将会显示 Using index condition。

Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
Not exists：当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息。EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
Using intersect(…) 、 Using union(…) 和 Using sort_union(…)：如果执行计划的Extra列出现了 Using intersect(…) 提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的 … 表示需要进行索引合并的索引名称；如果出现了 Using union(…) 提示，说明准备使用 Union 索引合并的方式执行查询；如果出现了 Using sort_union(…) 提示，说明准备使用 Sort-Union 索引合并的方式执行查询。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
Zero limit：当 LIMIT 子句的参数为 0 时，表示不打算从表中读出任何记录，将会提示该额外信息。EXPLAIN SELECT * FROM s1 LIMIT 0;
Using filesort：很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL 把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的 Extra 列中显示 Using filesort 提示。EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
Using temporary：在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT、GROUP BY、UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示Using temporary 提示。EXPLAIN SELECT DISTINCT common_field FROM s1;

小结

（1）EXPLAIN 不考虑各种 Cache；
（2）EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作；
（3）EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
（4）EXPLAIN 的部分统计信息是估算的，并非精确值；

优化

索引失效案例

全职匹配

若一个字段在多个索引中使用，查询条件涉及多个字段，优化器会选择满足多个字段的索引

最佳左前缀匹配原则

① 最左优先，在检索数据时从联合索引的最左边开始匹配。
② 对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
③ 如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引将会失效。

主键插入顺序

对于一个使用 InnoDB 存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽小忽大的话，则可能会造成页面分裂和记录移位。

计算、函数、类型转换（自动或手动）导致索引失效

类型转换导致索引失效

范围条件右边的列索引失效

不等于（!= 或者<>）索引失效

is null可以使用索引，is not null无法使用索引

结论：最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如可以将 INT 类型的字段，默认值设置为 0。
拓展：同理，在查询中使用 not like 也无法使用索引，只能用全表扫描。

like 以通配符 % 开头索引失效

OR 前后存在非索引的列，索引失效

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

数据库和表的字符集统一使用utf8mb4

统一使用 utf8mb4（5.5.3版本以上支持），兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

总结

① 对于单列索引，尽量选择针对当前 query 过滤性更好的索引；
② 在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
③ 在选择组合索引的时候，尽量选择能够包含当前 query 中的 where 子句中更多字段的索引。
④ 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

关联查询优化

采用左外链接

驱动表与被驱动表已经确定，关联字段上存在索引则会使用索引

采用内链接

查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，即“小表驱动大表”。特别需要注意的是，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

join原理

join 方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL 5.5 版本之前，MySQL 只支持一种表间关联方式，就是嵌套循环 (Nested Loop Join)。如果关联表的数据量很大，则 join 关联的执行时间会非常长。在 MySQL 5.5 以后的版本中，MySQL 通过引入BNLJ 算法来优化嵌套执行。

Simple Nested-Loop Join（简单嵌套循环连接）

从表 A 中取出一条数据 1，遍历表 B，将匹配到的数据放到 result…以此类推，驱动表 A 中的每一条记录与被驱动表 B 的记录进行判断,表 A 数据 100 条，表 B 数据 1000 条计算（这里指过滤之后的），则 A * B = 10 万次,A值越小越好

lndex Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join 优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。因此相比之下，被驱动表的索引是主键索引，效率会更高。

Block Nested-Loop Join（块嵌套循环连接）

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer 缓冲区，将驱动表 join 相关的部分数据列（大小受 join buffer 的限制）缓存到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

这里缓存的不只是关联表的列，select 后面的列也会缓存起来。在一个有 N 个 join 关联的 SQL 中会分配 N - 1 个 join buffer。所以查询的时候尽量减少不必要的字段，可以让 join buffer 中可以存放更多的列。

参数
- block_nested_loop 通过show variables like '%optimizer_switch%' 查看block_nested_loop的状态，默认开
- join_buffer_size
  驱动表能不能一次加载完，要看 join buffer 能不能存储所有的数据，可以通过下面的语句进行查看，默认情况下 join_buffer_size=256k，当然也可以自行设置。join_buffer_size 的最大值在 32 位系统可以申请 4G，而在 64 位操做系统下可以申请大于 4G 的 Join Buffer 空间（64位 Windows 除外，其大值会被截断为 4G 并发出警告）。show variables like '%join_buffer%';

join 小结

整体效率比较：INLJ > BNLJ > SNLJ。
永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量，小的度量单位指的是表行数 * 每行大小）。
为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）。
增大 join buffer size 的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）。
减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）。
需要 join 的字段，数据类型保持绝对一致。
-能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 JOIN 来代替子查询。
-衍生表建不了索引。

Hash Join

从 MySQL 的 8.0.20 版本开始将废弃 BNLJ，因为从 MySQL 8.0.18 版本开始就加入了 hash join，默认都会使用 hash join。
Nested Loop：对于被连接的数据子集较小的情况，Nested Loop 是个较好的选择。
Hash Join 是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用 Join Key 在内存中建立散列表，然后扫描较大的表并探测散列表，找出与 Hash 表匹配的行。
① 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
② 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高 I/O 的性能。
③ 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是 Join 的重型升降机。Hash Join 只能应用于等值连接（例如 WHERE A.COL1=B.COL2），这是由 Hash 的特点决定的。

子查询优化

子查询效率不高
① 执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU 和 I/O 资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
可以使用连接 (JOIN) 查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

可以使用连接 (JOIN) 查询来替代子查询

排序优化

为啥需要在order by上加索引

在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序：

在 lndex 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

优化建议

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中是为了避免全表扫描，在 ORDER BY 子句是为了避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，还是要避免，以提高查询效率。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

测试

全表全字段查询根据建立索引字段排序不一定会使用索引，回表需要成本，未必比全表扫描高
查询部分字段，索引生效
查询所有字段，但使用 limit，索引生效
order by 时规则不一致，索引失效（顺序错，不索引；方向反，不索引）

小结

INDEX a_b_c(a,b,c) order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC 如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c 不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

两个索引同时存在，mysql自动选择最优的方案。但是随着数据量的变化，选择的索引也会随之变化的。
当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

filesort 算法：双路排序和单路排序

双路排序（慢）

MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。

5.5.2.单路排序（快）

从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序 I/O，但是它会使用更多的空间，因为它把每一行都保存在内存中了

问题

由于单路排序是后出的，总体而言好过双路排序；
但是单路排序也存在一定的缺陷：
1. 在 sort_buffer 中，单路排序要比多路排序要多占用很多空间，因为单路排序是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排…从而多次有 I/O。
2. 单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

优化策略

尝试提高 sort_buffer_size。
不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程 (connection) 的 1M-8M 之间调整。再 MySQL 5.7 中，InnoDB 存储引擎默认值是 1048576 字节，即 1MB，查看语句:SHOW VARIABLES LIKE '%sort_buffer_size%';
尝试提高 max_length_for_sort_data。

提高这个参数，会增加用改进算法的概率。show VARIABLES LIKE ' %max_length_for_sort_data%' ，默认1024字节
但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显的症状是就高的磁盘 l/O 活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。

Order by 时 select * 是一个大忌，最好只查询需要的字段。

当查询的字段大小总和小于 max_length_for_sort_data，而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法――单路排序，否则用老算法—―多路排序。
两种算法的数据都有可能超出 sort_buffer_size 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size。

GROUP BY 优化

group by 使用索引的原则几乎跟 order by 一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则。
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。
where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了。
减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU资源是极其宝贵的。
包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。

分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10，此时需要 MySQL 排序前 2000010 条记录，但仅仅返回 2000000 - 2000010 之间的记录，其他记录丢弃，查询排序的代价非常大。

优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
优化思路2
该方案适用于主键自增的表，可以把 limit 查询转换成某个位置的查询。

覆盖索引

什么是覆盖索引

一个索引包含了满足查询结果的数据就叫做覆盖索引。
非聚簇复合索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列，即建索引的字段正好是覆盖查询条件中所涉及的字段。

索引列+主键包含 SELECT 到 FROM 之间查询的列。

覆盖索引优缺点

优点

避免 Innodb 表进行索引的二次查询（回表），减少io操作
可以把随机 I/O 变成顺序 I/O 加快查询效率；
由于覆盖索引是按键值的顺序存储的，对于 I/O 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 I/O 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 I/O 转变成索引查找的顺序 I/O。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

缺点

索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

字符串添加索引

前缀索引

好处：
用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。区分度越高越好。因为区分度越高，意味着重复的键值越少。
影响
结论：使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

索引下推

什么是索引下推？

索引下推 (Index Condition Pushdown, ICP) 是 MySQL 5.6 中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。
如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估 WHERE 后面的条件是否保留行。
启用 ICP 后（一般是默认开启的），如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则 MySQL 服务器会把这部分 WHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
① 优点：ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。
② 缺点：ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。

索引下推的开启与关闭

默认情况下启用索引条件下推。
SET optimizer_switch = 'index_condition_pushdown=off';SET optimizer_switch = 'index_condition_pushdown=on';
当使用索引条件下推时，EXPLAIN 语句输出结果中 Extra 列内容显示为 Using index condition。

ICP 的使用条件

(1）只能用于二级索引 (secondary index)；
（2）explain显示的执行计划中 type 值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
（3）并非全部where条件都可以用ICP筛选，如果 where 条件的字段不在索引列中，还是要读取整表的记录到 server 端做 where 过滤。
（4）ICP 可以用于 MyISAM 和 InnnoDB 存储引擎
（5）MySQL 5.6 版本的不支持分区表的 ICP 功能，5.7 版本的开始支持。
（6）当 SQL 使用覆盖索引时，不支持 ICP 优化方法。

唯一索引与普通索引

查询过程

性能微乎其微

更新过程

change buffer

更新的数据页在内存中则直接更更新
更新的数据页不在， InooDB 会将这些更新操作缓存在 change buffer 中，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭 (shutdown) 的过程中，也会执行 merge 操作
如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
唯一索引的更新就不能使用 change buffer ，实际上也只有普通索引可以使用。
考虑到更新性能，尽量使用普通索引
更新后需要即可查询记录，应该关闭 change buffer

其他优化策略

EXISTS 和 IN 的区分

标准，小标驱动大表

SELECT * FROM A WHERE cc IN (SELECT ce FROM B)
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

当 A 小于 B 时，用 EXISTS
当 B小于 A 时，用 IN

COUNT(*) 与 COUNT(具体字段)效率

count(1)，其实就是计算一共有多少符合条件的行。1并不是表示第一个字段，而是表示一个固定值。我们可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。
count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

count(1)和count(*)：

都为统计所有记录数，包括null
mylsam 中维护了row_count 字段，查询0（1）

count(字段)：

统计字段列的行数，不包括null
要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于 COUNT(*) 和 COUNT(1) 来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

关于 SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：
① MySQL 在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。
② 无法使用覆盖索引。

LIMIT 1 对优化的影响

（1）针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

（2）如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1了。

主键

自增 ID 的问题

可靠性不高
存在自增 ID 回溯的问题，这个问题直到最新版本的 MySQL 8.0 才修复。
安全性不高
对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户 ID 的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。
性能差
自增 ID 的性能较差，需要在数据库服务器端生成。
交互多
业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
局部唯一性
最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

业务字段做主键

选择卡号 (cardno)
千万不能把会员卡号当做主键。当用户注销后，卡号修改用户信息，会与业务情况不符合
选择会员电话或身份证号
（1）会员电话可以做主键吗？不行的。在实际操作中，手机号也存在被运营商收回，重新发给别人用的情况。
（2）那身份证号行不行呢？好像可以。因为身份证决不会重复，身份证号与一个人存在一一对应的关系。可问题是，身份证号属于个人隐私，顾客不一定愿意给你。要是强制要求会员必须登记身份证号，会把很多客人赶跑的。其实，客户电话也有这个问题，这也是我们在设计会员信息表的时候，允许身份证号和电话都为空的原因。
（3）所以，建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。