在使用MySQL时，如果发现查询语句耗时，会进行排查及调优，其中常用的一个方法是用explain查看sql执行计划。
expain命令解析 explain示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 mysql> explain select * from user ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec) mysql> explain -> select count(*) -> from table_c -> where l_id <> '' -> and l_id is not null -> and l_id not in ( -> select l_id from table_l -> ) ; +----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+-------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+-------+----------+------------------------------------+ | 1 | PRIMARY | table_c | NULL | range | idx_l_id | idx_l_id | 51 | NULL | 98519 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | table_l | NULL | index_subquery | idx_l_id | idx_l_id | 387 | func | 2 | 100.00 | Using index; Full scan on NULL key | +----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+-------+----------+------------------------------------+ 2 rows in set, 1 warning (0.01 sec) mysql>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> explain -> select count(*) -> from table_c -> where l_id not in ( -> select l_id from table_l -> ) ; +----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+--------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+--------+----------+------------------------------------+ | 1 | PRIMARY | table_c | NULL | index | NULL | idx_l_id | 51 | NULL | 197037 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | table_l | NULL | index_subquery | idx_l_id | idx_l_id | 387 | func | 2 | 100.00 | Using index; Full scan on NULL key | +----+--------------------+---------------+------------+----------------+---------------+--------------+---------+------+--------+----------+------------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql>
explain参数详解
id
Columns
JSON Name
Meaning
1
id
select_id
每个select子句的标识id
2
select_type
None
select语句的类型
3
table
table_name
当前表名
4
partitions
partitions
匹配的分区
5
type
access_type
当前表内访问方式 join type
6
possible_keys
possible_keys
可能使用到的索引
7
key
key
经过优化器评估最终使用的索引
8
key_len
key_length
使用到的索引长度
9
ref
ref
引用到的上一个表的列
10
rows
rows
rows_examined，要得到最终记录索要扫描经过的记录数
11
filtered
filtered
按表条件过滤行的百分比
12
Extra
None
额外的信息说明
id SELECT识别符。这是SELECT查询序列号。
值有以下三种情况：
id相同，执行顺序由上至下。id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。 id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。
select_type 每种类型的例子
select类型，它有以下几种:
id
select_type value
JSON name
Meaning
1
SIMPLE
None
简单的SELECT语句（不包括UNION操作或子查询操作）
2
PRIMARY
None
PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询,外层的表操作为PRIMARY，内层的操作为UNION）
3
UNION
None
UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句没有依赖关系）
4
DEPENDENT UNION
dependent(true)
DEPENDENT UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
5
UNIOIN RESULT
union_result
UNION RESULT：UNION操作的结果，id值通常为NULL
6
SUBQUERY
None
SUBQUERY：子查询中首个SELECT（如果有多个子查询存在）
7
DEPENDENT SUBQUERY
dependent(true)
DEPENDENT SUBQUERY：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）
8
DERIVED
None
DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）
9
MATERIALIZED
materialized_form_subquery
MATERIALIZED：被物化的子查询
10
UNCACHEABLE SUBQUERY
cacheable(false)
UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
11
UNCACHEABLE UNION
cacheable(false)
UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）
table 当前表名
partitions 匹配的分区
type 当前表内访问方式（每种访问方式的例子 ）
性能由好到坏排序 （加粗的是比较常见的几种）：
id
type value
Meaning
1
system 表中只有一行
2
const 单表中最多有一个匹配行，primary key 或者 unique index的检索
3
eq_ref 多表连接中被驱动表的连接列上有primary key或者unique index的检索
4
ref 与eq_ref类似，但不是使用primary key或者unique index，而是普通索引。也可以是单表上non-unique索引检索
5
fulltext
使用FULLTEXT索引执行连接
6
ref_or_null
与ref类似，区别在于条件中包含对NULL的查询
7
index_merge
索引合并优化，利用一个表里的N个索引查询,key_len表示这些索引键的和最长长度。
8
unique_subquery
in的后面是一个查询primary key\unique字段的子查询
9
index_subquery
in的后面是一个查询普通index字段的子查询
10
range 单表索引中的范围查询,使用索引查询出单个表中的一些行数据。ref列会变为null
11
index Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。通常比All快，因为索引文件通常比数据文件小。它有两种情况：(1)覆盖索引 (2)用索引的顺序做一个全表扫描。
12
all 全表扫描
possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.
key 此字段是 MySQL 在当前查询时所真正使用到的索引.
key_len key_len 说明
key_len大小的计算规则:
ref ref列显示使用哪个列或常数与key一起从表中选择行。
常见的有：const，func，null，字段名。
当使用常量等值查询，显示const，
当关联查询时，会显示相应关联表的关联字段
如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
其他情况null
rows rows_examined，要得到最终记录索要扫描经过的记录数，这个数越小越好。
filterrd 按表条件过滤行的百分比
Extra是对执行计划的额外说明，包含重要信息。
例如：
id
type value
Meaning
1
const row not found
所要查询的表为空
2
Distinct
mysql正在查询distinct值，因此当它每查到一个distinct值之后就会停止当前组的搜索，去查询下一个值
3
Impossible WHERE
where条件总为false，表里没有满足条件的记录
4
Impossible WHERE noticed after reading const tables
在优化器评估了const表之后，发现where条件均不满足
5
no matching row in const table
当前join的表为const表，不能匹配
6
Not exists
优化器发现内表记录不可能满足where条件
7
Select tables optimized away
在没有group by子句时，对于MyISAM的select count(*)操作，或者当对于min(),max()的操作可以利用索引优化，优化器发现只会返回一行。
8
Using filesort 使用filesort来进行order by操作
9
Using index 覆盖索引
10
Using index for group-by
对于group by列或者distinct列，可以利用索引检索出数据，而不需要去表里查数据、分组、排序、去重等等
11
Using join buffer
之前的表连接在nested loop之后放进join buffer，再来和本表进行join。适用于本表的访问type为range，index或all
12
Using sort_union,using union,using intersect
index_merge的三种情况
13
Using temporary 使用了临时表来存储中间结果集，适用于group by，distinct，或order by列为不同表的列。
14
Using where
在存储引擎层检索出记录后，在server利用where条件进行过滤，并返回给客户端
慢查询日志 基本介绍
慢查询日志是什么？
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10秒以上的语句。由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析。
特别说明
默认情况下，MySQL数据库没有开启慢查询日志， 需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数 ，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看慢查询日志是否开以及如何开启
查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。
开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。
1 2 3 4 5 6 7 8 9 10 11 12 13 # 1、查看慢查询日志是否开启 mysql> SHOW VARIABLES LIKE '%slow_query_log%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/1dcb5644392c-slow.log | +---------------------+--------------------------------------+ 2 rows in set (0.01 sec) # 2、开启慢查询日志 mysql> SET GLOBAL slow_query_log = 1; Query OK, 0 rows affected (0.00 sec)
如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数。
1 2 3 4 5 6 7 # my.cnf [mysqld] # 1.这个是开启慢查询。注意ON需要大写 slow_query_log=ON # 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建 slow_query_log_file=/var/lib/mysql/slow.log
开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？
这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。
MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。
1 2 3 4 5 6 7 8 9 # 查看long_query_time 默认是10秒 # 只有SQL的执行时间>10才会被记录 mysql> SHOW VARIABLES LIKE 'long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
修改long_query_time的时间，需要在my.cnf修改配置文件
1 2 3 [mysqld] # 这个是设置慢查询的时间，我设置的为1秒 long_query_time=1
查新慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%slow_queries%';。
1 2 3 4 5 6 7 mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 3 | +---------------+-------+ 1 row in set (0.00 sec)
日志分析工具 日志分析工具mysqldumpslow：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 # 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息(mac中先要在环境变量中配置相关路径/usr/local/mysql/bin) root@1dcb5644392c:/usr/bin# mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default # 按照何种方式排序 al: average lock time # 平均锁定时间 ar: average rows sent # 平均返回记录数 at: average query time # 平均查询时间 c: count # 访问次数 l: lock time # 锁定时间 r: rows sent # 返回记录 t: query time # 查询时间 -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries # 返回前面多少条记录 -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time # 2、 案例 # 2.1、得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log # 2.2、得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log # 2.3、得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log # 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况 mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more
Show Profile
Show Profile是什么？
Show Profile：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
分析步骤
1、是否支持，看看当前的MySQL版本是否支持。
1 2 3 4 5 6 7 8 # 查看Show Profile功能是否开启 mysql> SHOW VARIABLES LIKE 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set (0.00 sec)
2、开启Show Profile功能，默认是关闭的，使用前需要开启。
1 2 3 # 开启Show Profile功能 mysql> SET profiling=ON; Query OK, 0 rows affected, 1 warning (0.00 sec)
3、运行SQL
1 2 3 SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000; SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;
4、查看结果，执行SHOW PROFILES;
Duration：持续时间。
1 2 3 4 5 6 7 8 9 10 mysql> SHOW PROFILES; +----------+------------+---------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------------+ | 1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling' | | 2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 | | 3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 | | 4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5 | +----------+------------+---------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec)
5、诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 # 这里的3是第四步中的Query_ID。 # 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。 mysql> SHOW PROFILE cpu,block io FOR QUERY 3; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000097 | 0.000090 | 0.000002 | 0 | 0 | | checking permissions | 0.000010 | 0.000009 | 0.000000 | 0 | 0 | | Opening tables | 0.000039 | 0.000058 | 0.000000 | 0 | 0 | | init | 0.000046 | 0.000046 | 0.000000 | 0 | 0 | | System lock | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000023 | 0.000037 | 0.000000 | 0 | 0 | | preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | Creating tmp table | 0.000041 | 0.000053 | 0.000000 | 0 | 0 | | Sorting result | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.520620 | 0.516267 | 0.000000 | 0 | 0 | | Creating sort index | 0.000060 | 0.000051 | 0.000000 | 0 | 0 | | end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000032 | 0.000064 | 0.000000 | 0 | 0 | | cleaning up | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 20 rows in set, 1 warning (0.00 sec)
Show Profile查询参数备注：
ALL：显示所有的开销信息。BLOCK IO：显示块IO相关开销（通用）。CONTEXT SWITCHES：上下文切换相关开销。CPU：显示CPU相关开销信息（通用）。IPC：显示发送和接收相关开销信息。MEMORY：显示内存相关开销信息。PAGE FAULTS：显示页面错误相关开销信息。SOURCE：显示和Source_function。SWAPS：显示交换次数相关开销的信息。
6、Show Profile查询列表，日常开发需要注意的结论：
converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！locked：死锁。
索引的创建和删除 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 /* 基本语法 */ /* 1、创建索引 [UNIQUE]可以省略*/ /* 如果只写一个字段就是单值索引，写多个字段就是复合索引 */ CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length)); /* 2、删除索引 */ DROP INDEX [indexName] ON tabName; /* 3、查看索引 */ /* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */ SHOW INDEX FROM tabName \G; /*使用ALTER命令来为数据表添加或删除索引*/ /* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */ ALTER TABLE tabName ADD PRIMARY KEY(column_list); /* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */ ALTER TABLE tabName ADD UNIQUE indexName(column_list); /* 3、该语句创建普通索引，索引值可以出现多次 */ ALTER TABLE tabName ADD INDEX indexName(column_list); /* 4、该语句指定了索引为FULLTEXT，用于全文检索 */ ALTER TABLE tabName ADD FULLTEXT indexName(column_list); /* 5、该语句删除一个索引，其余删除语句类似 */ ALTER TABLE tabName DROP INDEX indexName(column_list);
索引失效 示例数据 1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> show create table user \G ****************************************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` int(10) DEFAULT '0', `pos` varchar(30) DEFAULT NULL, `phone` varchar(11) DEFAULT NULL, `created_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name_age_pos_phone` (`name`,`age`,`pos`,`phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
违反最左前缀法则 1 2 3 如果索引有多列，要遵守最左前缀法则 即查询从索引的最左前列开始并且不跳过索引中的列 explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy';
在索引列上做任何操作 1 2 如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描 explain select * from user where left(name,5) = 'zhangsan' and age = 20 and phone = '18730658760';
索引范围条件右边的列 1 2 3 4 5 6 索引范围条件右边的索引列会失效（但不包括 不以通配符%开头的立刻语句） explain select * from user where name = 'zhangsan' and age > 20 and pos = 'cxy'; 这条语句like右边索引仍有效 explain select * from user where name like 'zhangsan%' and age > 20 ; 这条语句like右边索引会失效 explain select * from user where name like '%zhangsan' and age > 20 ;
尽量使用覆盖索引 1 2 只访问索引查询（索引列和查询列一致），减少select* explain select name,age,pos,phone from user where age = 20;
like以通配符开头（’%abc’） 1 2 索引失效 explain select * from user where name like '%zhangsan';
1 2 索引生效 explain select * from user where name like 'zhangsan%';
字符串不加单引号索引失效 1 2 进行了隐式转换 explain select * from user where name = 2000;
or连接 但并不是所有的or都会使索引失效，如果or连接的所有条件字段都设置了索引，那么还是会走索引的；一旦有一个条件没有建立索引，那么就会走全表扫描。
1 2 少用or explain select * from user where name = '2000' or age = 20 or pos ='cxy';
关于IS NULL、IS NOT NULL、!= 在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。
详细内容可参考这篇博文
查询优化 小表驱动大表
优化原则：对于MySQL数据库而言，永远都是小表驱动大表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 /** * 举个例子：可以使用嵌套的for循环来理解小表驱动大表。 * 以下两个循环结果都是一样的，但是对于MySQL来说不一样， * 第一种可以理解为，和MySQL建立5次连接每次查询1000次。 * 第一种可以理解为，和MySQL建立1000次连接每次查询5次。 */ for(int i = 1; i <= 5; i ++){ for(int j = 1; j <= 1000; j++){ } } // ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ for(int i = 1; i <= 1000; i ++){ for(int j = 1; j <= 5; j++){ } }
IN和EXISTS
1 2 3 4 5 6 7 /* 优化原则：小表驱动大表，即小的数据集驱动大的数据集 */ /* IN适合B表比A表数据小的情况*/ SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`) /* EXISTS适合B表比A表数据大的情况 */ SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);
EXISTS：
语法：SELECT....FROM tab WHERE EXISTS(subquery);该语法可以理解为：
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或是false）来决定主查询的数据结果是否得以保留。
提示：
EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。
group by group by 执行过程可看此处
使用索引优化需满足的条件可看官方文档
order by优化 数据准备 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE `talA`( `age` INT, `birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); INSERT INTO `talA`(`age`) VALUES(18); INSERT INTO `talA`(`age`) VALUES(19); INSERT INTO `talA`(`age`) VALUES(20); INSERT INTO `talA`(`age`) VALUES(21); INSERT INTO `talA`(`age`) VALUES(22); INSERT INTO `talA`(`age`) VALUES(23); INSERT INTO `talA`(`age`) VALUES(24); INSERT INTO `talA`(`age`) VALUES(25); /* 创建索引 */ CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);
案例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 /* 1.使用索引进行排序了 不会产生Using filesort */ EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`; /* 2.使用索引进行排序了 不会产生Using filesort */ EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`; /* 3.没有使用索引进行排序 产生了Using filesort */ EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`; /* 4.没有使用索引进行排序 产生了Using filesort */ EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`; /* 5.没有使用索引进行排序 产生了Using filesort */ EXPLAIN SELECT * FROM `talA` ORDER BY `birth`; /* 6.没有使用索引进行排序 产生了Using filesort */ EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`; /* 7.使用索引进行排序了 不会产生Using filesort */ EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`; /* 8.没有使用索引进行排序 产生了Using filesort */ EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;
ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。
MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况，会使用Index方式排序：
ORDER BY语句使用索引最左前列(升降序要一致)。使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列(升降序要一致)。
结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。
File Sort 如果不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法
双路排序算法：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。 取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。
单路排序算法：从磁盘读取查询需要的所有列，按照ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
由于单路排序算法是后出的，总体而言效率好过双路排序算法。
但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。
单路复用算法的优化策略：
增大sort_buffer_size参数的设置。
增大max_length_for_sort_data参数的设置。
提高ORDER BY排序速度的方式
ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。
总结
MySQL两种排序方式：Using filesort和Index扫描有序索引排序。
MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询。
