大纲
- count
- order by
- join
一、count
在innodb中count的结果与事务隔离级别有关，存储引擎遍历整库将符合可见性的行返回给server层，由sever层判断不为null后做累加。如果是count字段的话，还要进行行数据解析，字段拷贝等操作。count(*)进行过专门的性能优化。
所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，
二、order by
排序空间：mysql会为每一个线程分配一块内存sort_buffer用于排序，当这个内存空间不够的时候（通过sort_buffer_size设置大小默认32k），会使用磁盘空间排序。执行计划中number_of_tmp_files为0，说明使用的就是内存排序，为n使用的就是磁盘排序，将排序数据分成n个文件（然后利用归并排序得到结果，归并排序：先局部有序再整体有序）。
排序方式：
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 ;排序方式有2种：1.全字段排序 2.rowid排序
单行字段总和大于参数max_length_for_sort_data用rowid排序，小于等于用全字段排序。
全字段排序过程：将所有select返回字段、排序字段都放入内存中，排序后直接返回。优点：所有字段进入排序空间，排序后直接返回，相对rowid排序少一次回表。
rowid排序过程：仅将主键与排序字段放入内存中，排序后再通过主键回表查询其他字段返回。优点：单行占据更少空间，排序所需总空间更少。
排序字段天然有序：如果排序字段在索引上，有天然有序性则无需排序，例如建立索引下面的索引，保持同一个city中name的有序性
alter table t add index city_user_age(city, name);
思考1：如果查询语句变成如下，则name不再具有天然有序性，因为name是按照每一个city分别排序，这时如何利用索引有序性？
mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;如果希望不在sort_buffer种排序，需要改成如下语句，然后在应用中汇总并排序
select id,name from t where city="杭州" order by name limit 100;
select id,name from t where city="苏州" order by name limit 100;
思考2：如果语句变成如下，排序过程发生怎样变化
select city,name,age from t where city='杭州' order by name limit 10 ;当limit条数较少的情况下，sort_buffer中将保存一个优先队列而不是利用临时文件做全排序
思考3：如果将语句变成如下，排序过程发生怎样变化
select name from t order by rand() limit 3;当排序字段不在表中的时候，会先生成临时表，将排序字段生成到临时表中，临时表分为内存和硬盘2种，由tmp_table_size控制默认16m。再放入sort_buffer，扫描临时表同样也是要计入总的扫描行数的，也是一种额外开销。
三、join
Index Nested-Loop Join：被驱动表上的关联字段有索引。
假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数，记为log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。
假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。
整个过程的近似复杂度是 N + N*2*log2M。因此应该用结果行数少的表驱动结果行数多的表。
Multi-Range Read优化-单次回表时是一个随机io操作，在批量查询的时，在索引上过滤出符合条件的值放入read_rnd_buffer中根据主键排序变成一个有序数组，这样批量回表将随机io变成顺序io。
Batched Key Access优化-驱动表查询出结果集后，在Join被驱动表时就可以用上MRR，join关联到数据越多这种优化越明显。
开启MRR、BKA：前两个参数的作用是要启用MRR，BKA算法的优化要依赖于MRR。
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Block Nested-Loop Join：被驱动表上的关联字段没有索引，驱动表被查询出来的结果集暂存在join_buffer（通过join_buffer_size设置）中，然后被驱动表查询结果集和join_buffer中数据在内存中做比较。最终比较次数=驱动表行数M*被驱动表行数N。
假设驱动表中的结果集无法一次性放入join_buffer中，则需要分多次放入，每次放入都需要重新查询被驱动表，而最终在内存中比较次数不变，如果join_buffer放结果集小的表则可以减少被驱动表扫描次数。因此应该用结果行数少的表驱动结果行数多的表。
Hash join：MYSQL8新特性对Block Nested-Loop Join的优化
驱动表在join_buffer中建立以关联字段为key的哈希表，然后在被驱动表中扫描出结果进行比对，hash表太大将多次放入join_buffer中。因此应该用结果行数少的表驱动结果行数多的表。
