count

InnoDB 引擎执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
这跟InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count() 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。
效率：count(字段)<count(主键 id)<count(1)≈count(*)

order by

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

1. 全字段排序
   对city加索引

   
   image.png
   image.png

如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序，采用归并排序算法。
先扫描找到符合条件的记录返回server层，server层排序返回给客户端，或再调引擎层接口，回表，拿到数据再返回客户端

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

   
   image.png
   

   多了一次回表操作

3. 排序优化
   排序是比较消耗内存、cpu的，排序的原因是原数据无序，假如提前建立好索引，则可避免排序
   alter table t add index city_user(city, name);
   建立city、name索引避免了排序，但是无法避免回表

   
   image.png
   

   alter table t add index city_user_age(city, name, age);
   建立覆盖索引，避免回表

   
   image.png

join

1. Index Nested-Loop Join
   select * from t1 straight_join t2 on (t1.a=t2.a);
   若被驱动表的关联字段是索引字段，则算法为Index Nested-Loop Join
   

   image.png
   image.png
   假设被驱动表的行数是 M，驱动表的行数是 N，整个扫描执行过程，近似复杂度是 N + N2log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

2. Block Nested-Loop Join
   若被驱动表的关联字段不是索引字段，则算法为Block Nested-Loop Join
   

   image.png
   image.png
   两个表都做一次全表扫描，所以总的扫描行数是 M+N；内存中的判断次数是 MN
   假如数据量太大，join_buffer放不下，就分段放
   image.png
   扫描行数是 N+KM；内存判断 N*M 次。即扫描行数变大，所以尽量避免再被驱动表无可用索引时做join
   BNL 算法对系统的影响主要包括三个方面：

• 可能会多次扫描被驱动表，占用磁盘 IO 资源；
• 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
• 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率

3. 总结
   如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
   如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
   如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。所以，这个问题的结论就是，总是应该使用小表做驱动表。
   在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

group by

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

image.png
image.png
这个例子里由于临时表只有 10 行，内存可以放得下，因此全程只使用了内存临时表。但是，内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。超过时就会把内存临时表转成磁盘临时表。
如果不需要排序
select id%10 as m, count() as c from t1 group by m order by null;
总结一些使用的指导原则：

• 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
• 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
• 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

长时间不返回原因

1. 等MDL锁

   
   image.png

2. 等行锁

   
   image.png
   image.png

3. 等flush

   
   image.png

4. 可重复读隔离级别，长事务

   
   image.png
   image.png
   

   带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。