mysql语句分析

慢查询

set long_query_time=0;是将慢查询的阈值设置为0。表示这个线程接下来的的语句都会被记录到慢查询日志中。
show variables like ‘%datadir%’ 查看数据存储路径

show variables like ‘%slow_query_log%’ 查看慢查询日志存储路径

打开C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-80FPTP3-slow.log文件查看

慢查询日志存储的格式：

执行sql的主机信息

# User@Host: root[root] @ localhost [::1] Id: 9

sql的执行信息

# Query_time: 0.314725 Lock_time: 0.001232 Rows_sent: 1 Rows_examined: 0

sql的执行时间

SET timestamp=1635296227;

sql的内容

select count(*) from t;

慢查询：

1.直接查看show variables like ‘%slow_query_log%’ 查看慢查询日志存储路径

2.show global status like ‘%slow%’; 查看慢查询的记数器

3.show processlist展示的内容是从information_schema.processlist数据表查询得到。

select* from information_schema.processlist where Host like ‘10.26.201.199%’;

在 MySQL 中，会引发性能问题的慢查询

大体有以下三种可能：

索引没有设计好；

SQL 语句没写好；

MySQL 选错了索引。

涉及参数

show variables like ‘%slow_query_log%’

show global status like ‘%slow%’;

insert（P40）

insert … select 语句

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

为什么在可重复读隔离级别下，binlog_format=statement 时执行,

insert into t2(c,d) select c,d from t;

需要对表 t 的所有行和间隙加锁呢?

如果没有锁的话，就可能出现 session B 的 insert 语句先执行，但是后写入 binlog 的情况。


于是，在 binlog_format=statement 的情况下，binlog 里面就记录了这样的语句序列：

insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

这个语句到了备库执行，就会把 id=-1 这一行也写到表 t2 中，出现主备不一致。

所以需要给表 t 的所有行和间隙加锁。

insert 循环写入

扫描行数为1：

insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

扫描行数为5：

insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

explain insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

从 Extra 字段可以看到“Using temporary”字样，表示这个语句用到了临时表。

执行过程:

1.创建临时表，表里有两个字段 c 和 d。

2.按照索引 c 扫描表 t，依次取 c=4、3、2、1，然后回表，读到 c 和 d 的值写入临时表。

3.这时，Rows_examined=4。

4.由于语义里面有 limit 1，所以只取了临时表的第一行，再插入到表 t 中。这时，Rows_examined 的值加 1，变成了 5。

这个语句的执行为什么需要临时表，原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。

由于实现上这个语句没有在子查询中就直接使用 limit 1，从而导致了这个语句的执行需要遍历整个表 t。它的优化方法也比较简单，就是用前面介绍的方法，先 insert into 到临时表 temp_t，这样就只需要扫描一行；然后再从表 temp_t 里面取出这行数据插入表 t1。

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert 唯一键冲突

这个例子也是在可重复读（repeatable read）隔离级别下执行的。可以看到，session B 要执行的 insert 语句进入了锁等待状态。

已经得到修复 https://bugs.mysql.com/bug.php?id=93806

惟一键冲突导致死锁

在 session A 执行 rollback 语句回滚的时候，session C 几乎同时发现死锁并返回。

这个死锁产生的逻辑是这样的：

1.在 T1 时刻，启动 session A，并执行 insert 语句，此时在索引 c 的 c=5 上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁.

2.在 T2 时刻，session B 要执行相同的 insert 语句，发现了唯一键冲突，加上读锁；同样地，session C 也在索引 c 上，c=5 这一个记录上，加了读锁。

3.T3 时刻，session A 回滚。这时候，session B 和 session C 都试图继续执行插入操作，都要加上写锁。两个 session 都要等待对方的行锁，所以就出现了死锁。

insert into … on duplicate key update

上面这个例子是主键冲突后直接报错，如果是改写成

insert into t values(11,10,10) on duplicate key update d=100; 

的话，就会给索引 c 上 (5,10] 加一个排他的 next-key lock（写锁）。

insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

需要注意的是，执行这条语句的 affected rows 返回的是 2，很容易造成误解。实际上，真正更新的只有一行，只是在代码实现上，insert 和 update 都认为自己成功了，update 计数加了 1， insert 计数也加了 1。

insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。

而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。

insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

select

select * from T where ID=10;

1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

select语句的关键字执行顺序

书写顺序：SELECT -> DISTINCT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT

执行顺序：FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

FROM 从哪个表查。

JOIN 一个表不够，关联表。

ON 设置关联键，把不符合条件的筛选掉。

WHERE 上面三步整合成一个大表，再筛选。

GROUP BY 聚合数据（把字段相同的行组合一起，可以聚合函数max/min）。

HAVING 筛选group by的记录。

SELECT 查询出哪几列。

DISTINCT 指定字段去重。

ORDER BY 指定字段排序。

LIMIT 指定几行。

这里所谓的执行顺序只是你自己理解中的逻辑顺序，而非数据库工作时的执行顺序。

对于同一个sql语句，在数据量、数据密度、数据倾斜程度、索引分布、运行环境等等各种因素作用下，实际sql的执行计划会变得大相径庭。

所以，不要用这种所谓的执行顺序去做sql调优

MySQL 什么时候会使用内部临时表？

1.如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；

2.join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；

3.如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

count(P14)

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

count(*) 的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；

而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

MyISAM 表虽然 count(*) 很快，但是不支持事务；

show table status 命令虽然返回很快，但是不准确；

InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

mysql count(*)慢怎么解决？

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；

而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

都是没有where的情况下，如果有where myISAM引擎也会慢。

可以自己计数，然后放到innodb引擎的表中。但是条件查询也不太好支持。

mysql count(*) count(主键ID) count(1) count(字段) 的效率问题

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

order by(P16、P17)

市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

全字段排序

select city,name,age from t where city='杭州' order by name limit 1000;

explain select city,name,age from t where city='杭州' order by name limit 1000;

Extra 这个字段中的“Using filesort”表示的就是需要排序。

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

sort_buffer内存够时：使用全字段排序

全字段排序流程：

1.初始化 sort_buffer，确定放入 name、city、age 这三个字段；

2.从索引 city 找到第一个满足 city=’杭州’条件的主键 id；

3.到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；

4.从索引 city 取下一个记录的主键 id；

5.重复步骤 3、4 直到 city 的值不满足查询条件为止；

6.对 sort_buffer 中的数据按照字段 name 做快速排序；

7.按照排序结果取前 1000 行返回给客户端。

sort_buffer_size参数设置sort_buffer的大小。

如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

你可以用下面介绍的方法，来确定一个排序语句是否使用了临时文件。

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

number_of_tmp_files中看到是否使用了临时文件。

归并排序算法

内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。

sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

rowId排序

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

city、name、age 这三个字段的定义总长度是 36，我把 max_length_for_sort_data 设置为 16

新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。

rowId排序流程

1.初始化 sort_buffer，确定放入两个字段，即 name 和 id；

2.从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；

3.到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；

4.从索引 city 取下一个记录的主键 id；

5.重复步骤 3、4 直到不满足 city=’杭州’条件为止，也就是图中的 ID_Y；

6.对 sort_buffer 中的数据按照字段 name 进行排序；

7.遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

比全字段排序多了一个根据id获取city、age

sort_mode 变成了 <sort_key, rowid>

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

索引有序

MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

你可以设想下，如果能够保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，是不是就可以不用再排序了呢？

创建一个 city 和 name 的联合索引

alter table t add index city_user(city, name);


整个查询过程的流程就变成了：

1.从索引 (city,name) 找到第一个满足 city=’杭州’条件的主键 id；

2.到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；

3.从索引 (city,name) 取下一个记录主键 id；

4.重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

explain中Extra 字段中没有 Using filesort 了，也就是不需要排序了。

覆盖索引

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

创建一个 city、name 和 age 的联合索引

alter table t add index city_user_age(city, name, age);

对于 city 字段的值相同的行来说，还是按照 name 字段的值递增排序的，此时的查询语句也就不再需要排序了。

执行流程就变成了：

1.从索引 (city,name,age) 找到第一个满足 city=’杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；

2.从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

3.重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

 联合索引后，查询语句的执行流程.webp)

explain中Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。

并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。

order by rand()(P17)

这个用户每次访问首页的时候，都会随机滚动显示三个单词。

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

select word from words order by rand() limit 3;

explain select word from words order by rand() limit 3;

Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。

执行流程：

1.创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。

2.从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。

3.现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。

4.初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。

5.从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。

6.在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。

7.排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。


把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法回表了？

其实不是的。如果你创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

这也就是排序模式里面，rowid 名字的来历。实际上它表示的是：每个引擎用来唯一标识数据行的信息。

对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；

对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；

MEMORY 引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。

当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

为了复现这个过程，我把 tmp_table_size 设置成 1024，把 sort_buffer_size 设置成 32768, 把 max_length_for_sort_data 设置成 16。

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

因为将 max_length_for_sort_data 设置成 16，小于 word 字段的长度定义，所以我们看到 sort_mode 里面显示的是 rowid 排序，这个是符合预期的，参与排序的是随机值 R 字段和 rowid 字段组成的行。

这时候你可能心算了一下，发现不对。R 字段存放的随机值就 8 个字节，rowid 是 6 个字节（至于为什么是 6 字节，就留给你课后思考吧），数据总行数是 10000，这样算出来就有 140000 字节，超过了 sort_buffer_size 定义的 32768 字节了。但是，number_of_tmp_files 的值居然是 0，难道不需要用临时文件吗？

这个 SQL 语句的排序确实没有用到临时文件，采用是 MySQL 5.6 版本引入的一个新的排序算法，即：优先队列排序算法。接下来，我们就看看为什么没有使用临时文件的算法，也就是归并排序算法，而是采用了优先队列排序算法。

优先队列算法

而优先队列算法，就可以精确地只得到三个最小值，执行流程如下：

1.对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；

2.取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；

3.重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。

整个排序过程中，为了最快地拿到当前堆的最大值，总是保持最大值在堆顶，因此这是一个最大堆。

OPTIMIZER_TRACE 结果中，filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。

select city,name,age from t where city='杭州' order by name limit 1000  ;

这里也用到了 limit，为什么没用优先队列排序算法呢？原因是，这条 SQL 语句是 limit 1000，如果使用优先队列算法的话，需要维护的堆的大小就是 1000 行的 (name,rowid)，超过了我设置的 sort_buffer_size 大小，所以只能使用归并排序算法。

group by （P37）

select id%10 as m, count(*) as c from t1 group by m;

这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序后输出。

在 Extra 字段里面，我们可以看到三个信息：

Using index，表示这个语句使用了覆盖索引，选择了索引 a，不需要回表；

Using temporary，表示使用了临时表；

Using filesort，表示需要排序。

执行流程：

1.创建内存临时表，表里有两个字段 m 和 c，主键是 m；

2.扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；

​ 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);

​ 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；

3.遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。


参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

把内存临时表的大小限制为最大 1024 字节，并把语句改成 id % 100，这样返回结果里有 100 行数据。但是，这时的内存临时表大小不够存下这 100 行数据，也就是说，执行过程中会发现内存临时表大小到达了上限（1024 字节）。

那么，这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。

group by 优化方法 – 索引

group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

如果可以确保输入的数据是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个过程：

当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集里的第一行就是 (0,X);

当碰到第一个 2 的时候，已经知道累积了 Y 个 1，结果集里的第二行就是 (1,Y);

按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。

MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。

alter table t1 add column z int generated always as(id % 100), add index(z);

如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引，来解决这个问题

select z, count(*) as c from t1 group by z;

group by 优化方法 – 直接排序

如果碰上不适合创建索引的场景，我们还是要老老实实做排序的。

如果我们明明知道，一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。

那么，我们就会想了，MySQL 有没有让我们直接走磁盘临时表的方法呢？

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

执行流程:

1.初始化 sort_buffer，确定放入一个整型字段，记为 m；

2.扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；

3.扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；

4.排序完成后，就得到了一个有序数组。


group by指导原则

1.如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

2.尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；

3.如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；

4.如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

distinct 和 group by 的性能(p44)

select a from t group by a order by null;
select distinct a from t;

没有了 count(*) 以后，也就是不再需要执行“计算总数”的逻辑时，第一条语句的逻辑就变成是：按照字段 a 做分组，相同的 a 的值只返回一行。而这就是 distinct 的语义，所以不需要执行聚合函数时，distinct 和 group by 这两条语句的语义和执行流程是相同的，因此执行性能也相同。

这两条语句的执行流程:

1.创建一个临时表，临时表有一个字段 a，并且在这个字段 a 上创建一个唯一索引；

2.遍历表 t，依次取数据插入临时表中：

​ 如果发现唯一键冲突，就跳过；

​ 否则插入成功；

3.遍历完成后，将临时表作为结果集返回给客户端。

limit(P17)

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。

limit 有限数时触发优先队列排序算法

归并排序算法会把所有的记录都排好序，优先队列排序算法会按照limit数量形成一个堆，然后再取数据跟堆里的数据进行比较。

优先队列排序算法也受sort_buffer_size的大小限制，如果超过这个内存，mysql还是会在磁盘上进行归并排序。

join(P34、P35)

在实际生产中，关于 join 语句使用的问题，一般会集中在以下两类：

1.我们 DBA 不让使用 join，使用 join 有什么问题呢？

2.如果有两个大小不同的表做 join，应该用哪个表做驱动表呢？

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

可以看到，这两个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储过程 idata() 往表 t2 里插入了 1000 行数据，在表 t1 里插入的是 100 行数据。

Index Nested-Loop Join(索引嵌套循环联接)

select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。

explain select * from t1 straight_join t2 on (t1.a=t2.a);

在这条语句里，被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引，因此这个语句的执行流程是这样的：

1.从表 t1 中读入一行数据 R；

2.从数据行 R 中，取出 a 字段到表 t2 里去查找；

3.取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；

4.重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

这个过程是先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ。


在这个流程里：

1.对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行；

2.而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描 100 行；

3.所以，整个执行流程，总扫描行数是 200。

能不能使用 join?

不使用join,拿程序做的步骤。

1.执行select * from t1，查出表 t1 的所有数据，这里有 100 行；

2.循环遍历这 100 行数据：

​ 从每一行 R 取出字段 a 的值 $R.a；

​ 执行select * from t2 where a=$R.a；

​ 把返回的结果和 R 构成结果集的一行。

可以看到，在这个查询过程，也是扫描了 200 行，但是总共执行了 101 条语句，比直接 join 多了 100 次交互。除此之外，客户端还要自己拼接 SQL 语句和结果。显然，这么做还不如直接 join 好。

怎么选择驱动表？

在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。

因此整个执行过程，近似复杂度是 N + N2log2M。

显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

如果你没觉得这个影响有那么“显然”， 可以这么理解：N 扩大 1000 倍的话，扫描行数就会扩大 1000 倍；而 M 扩大 1000 倍，扫描行数扩大不到 10 倍。

通过上面的分析我们得到了两个结论：

1.使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

2.如果使用 join 语句的话，需要让小表做驱动表。

但是，你需要注意，这个结论的前提是“可以使用被驱动表的索引”。

Simple Nested-Loop Join(简单嵌套循环联接)

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表 t2 的字段 b 上没有索引，因此再用图 2 的执行流程时，每次到 t2 去匹配的时候，就要做一次全表扫描。

你可以先设想一下这个问题，继续使用图 2 的算法，是不是可以得到正确的结果呢？如果只看结果的话，这个算法是正确的，而且这个算法也有一个名字，叫做“Simple Nested-Loop Join”。

但是，这样算来，这个 SQL 请求就要扫描表 t2 多达 100 次，总共扫描 100*1000=10 万行。

这还只是两个小表，如果 t1 和 t2 都是 10 万行的表（当然了，这也还是属于小表的范围），就要扫描 100 亿行，这个算法看上去太“笨重”了。

当然，MySQL 也没有使用这个 Simple Nested-Loop Join 算法，而是使用了另一个叫作“Block Nested-Loop Join”的算法，简称 BNL。

Block Nested-Loop Join(块嵌套循环联接)

explain select * from t1 straight_join t2 on (t1.a=t2.b);

显示使用use where; use join buffer(Block Nested Loop)

这时候，被驱动表上没有可用的索引，算法的流程是这样的

1.把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；

2.扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。


explain select * from t1 straight_join t2 on (t1.a=t2.b);

可以看到，在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做 100 次判断，总共需要在内存中做的判断次数是：100*1000=10 万次。

前面我们说过，如果使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是 10 万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。

在这种情况下，应该选择哪个表做驱动表?

假设小表的行数是 N，大表的行数是 M，那么在这个算法里：

1.两个表都做一次全表扫描，所以总的扫描行数是 M+N；

2.内存中的判断次数是 M*N。

可以看到，调换这两个算式中的 M 和 N 没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。

然后，你可能马上就会问了，这个例子里表 t1 才 100 行，要是表 t1 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。我把 join_buffer_size 改成 1200，再执行：

select * from t1 straight_join t2 on (t1.a=t2.b);

执行过程就变成了：

1.扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；

2.扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；

3.清空 join_buffer；

4.继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。


图中的步骤 4 和 5，表示清空 join_buffer 再复用。

这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去 join”。

可以看到，这时候由于表 t1 被分成了两次放入 join_buffer 中，导致表 t2 会被扫描两次。虽然分成两次放入 join_buffer，但是判断等值条件的次数还是不变的，依然是 (88+12)*1000=10 万次。

假设，驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M。

注意，这里的 K 不是常数，N 越大 K 就会越大，因此把 K 表示为λ*N，显然λ的取值范围是 (0,1)。

所以，在这个算法的执行过程中：

1.扫描行数是 N+λNM；

2.内存判断 N*M 次。

显然，内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在 M 和 N 大小确定的情况下，N 小一些，整个算式的结果会更小。

所以结论是，应该让小表当驱动表。

刚刚我们说了 N 越大，分段数 K 越大。那么，N 固定的时候，什么参数会影响 K 的大小呢？（也就是λ的大小）答案是 join_buffer_size。join_buffer_size 越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少。

这就是为什么，你可能会看到一些建议告诉你，如果你的 join 语句很慢，就把 join_buffer_size 改大。

总结

第一个问题：能不能使用 join 语句？

1.如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；

2.如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

第二个问题是：如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

1.如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；

2.如果是 Block Nested-Loop Join 算法：

​ 在 join_buffer_size 足够大的时候，是一样的；

​ 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。

什么叫作“小表”。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

Multi-Range Read 优化(索引多范围查找)

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

select * from t1 where a>=1 and a<=100;

如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这就是 MRR 优化的设计思路。此时，语句的执行流程变成了这样：

1.根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;

2.将 read_rnd_buffer 中的 id 进行递增排序；

3.排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

另外需要说明的是，如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch=”mrr_cost_based=off”。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

MRR 优化后的执行流程:


explain select * from t1 where a>=1 and a<=100;

explain 结果中，我们可以看到 Extra 字段多了 Using MRR，表示的是用上了 MRR 优化。而且，由于我们在 read_rnd_buffer 中按照 id 做了排序，所以最后得到的结果集也是按照主键 id 递增顺序的，也就是与上面结果集中行的顺序相反。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access(批量key访问)

NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。

一行行去join慢，批量去join，这就是Batched Key Access。

批量存储的位置是join_buffer。


如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。

BNL 算法的性能问题

大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

BNL 算法对系统的影响主要包括三个方面：

1.可能会多次扫描被驱动表，占用磁盘 IO 资源；

2.判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；

3.可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

BNL 转 BKA

一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成 BKA 算法了。

但是，有时候你确实会碰到一些不适合在被驱动表上建索引的情况。比如下面这个语句：

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

我们在文章开始的时候，在表 t2 中插入了 100 万行数据，但是经过 where 条件过滤后，需要参与 join 的只有 2000 行数据。如果这条语句同时是一个低频的 SQL 语句，那么再为这个语句在表 t2 的字段 b 上创建一个索引就很浪费了。

但是，如果使用 BNL 算法来 join 的话，这个语句的执行流程是这样的：

1.把表 t1 的所有字段取出来，存入 join_buffer 中。这个表只有 1000 行，join_buffer_size 默认值是 256k，可以完全存入。

2.扫描表 t2，取出每一行数据跟 join_buffer 中的数据进行对比，

​ 如果不满足 t1.b=t2.b，则跳过；

​ 如果满足 t1.b=t2.b, 再判断其他条件，也就是是否满足 t2.b 处于[1,2000]的条件，如果是，就作为结果集的一部分返回，否则跳过。

使用临时表优化

如果这条join语句同时是一个低频的 SQL 语句，那么再为这个语句在被驱动表的字段 b 上创建一个索引就很浪费了。

这时候，我们可以考虑使用临时表。使用临时表的大致思路是：

1.把表 t2 中满足条件的数据放在临时表 tmp_t 中；

2.为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引；

3.让表 t1 和 tmp_t 做 join 操作。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

过程的消耗:

1.执行 insert 语句构造 temp_t 表并插入数据的过程中，对表 t2 做了全表扫描，这里扫描行数是 100 万。

2.之后的 join 语句，扫描表 t1，这里的扫描行数是 1000；join 比较过程中，做了 1000 次带索引的查询。相比于优化前的 join 语句需要做 10 亿次条件判断来说，这个优化效果还是很明显的。

总体来看，不论是在原表上加索引，还是用有索引的临时表，我们的思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。

扩展 -hash join

看到这里你可能发现了，其实上面计算 10 亿次那个操作，看上去有点儿傻。如果 join_buffer 里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是 10 亿次判断，而是 100 万次 hash 查找。这样的话，整条语句的执行速度就快多了吧？

这，也正是 MySQL 的优化器和执行器一直被诟病的一个原因：不支持哈希 join。并且，MySQL 官方的 roadmap，也是迟迟没有把这个优化排上议程。

实际上，这个优化思路，我们可以自己实现在业务端。实现流程大致如下：

1.select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。

2.select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。

3.满足匹配的条件的这行数据，就作为结果集的一行。

总结

在这些优化方法中：

1.BKA 优化是 MySQL 已经内置支持的，建议你默认使用；

2.BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；

3.基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；

4.MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

如果用 left join 的话，左边的表一定是驱动表吗？

使用 left join 时，左边的表不一定是驱动表。

如果两个表的 join 包含多个条件的等值匹配，是都要写到 on 里面呢，还是只把一个条件写到 on 里面，其他条件写到 where 部分？

如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面。

explain 和 show warnings 可以查看优化器，给你优化过后的sql。

union(P37)

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;

  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

(select 1000 as f) union (select id from t1 order by id desc limit 2);

这条语句用到了 union，它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。

explain (select 1000 as f) union (select id from t1 order by id desc limit 2);

第二行的 key=PRIMARY，说明第二个子句用到了索引 id。

第三行的 Extra 字段，表示在对子查询的结果集做 union 的时候，使用了临时表 (Using temporary)。

这个语句的执行流程是这样的：

1.创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。

2.执行第一个子查询，得到 1000 这个值，并存入临时表中。

3.执行第二个子查询：

拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；

取到第二行 id=999，插入临时表成功。

4.从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。


可以看到，这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键 id 的唯一性约束，实现了 union 的语义。

顺便提一下，如果把上面这个语句中的 union 改成 union all 的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

explain (select 1000 as f) union all (select id from t1 order by id desc limit 2);

可以看到，第二行的 Extra 字段显示的是 Using index，表示只使用了覆盖索引，没有用临时表了。