mysql之聚合

count(*)

MyISAM的count(*)方式为总行数记录在磁盘上，执行直接读取当前总数数据，效率高。
InnoDB则是将数据一行一行的数据从引擎中取出后，然后累加计数。

为啥 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？
因为同一时间的多个查询，由于多版本MVCC控制原因，应该返回多少行是由版本可见性进行区分是否当前数据可见。可见后才会进行返回计数。
InnoDB对count()的优化：因为主键索引树的叶子节点存储真正的数据，其他索引结构只存了索引字段与主键，因为当前是count()查询不需要具体字段值，所以找一个最小的索引树进行遍历，得到的逻辑结果也是一样的。
数据库系统设计通用法则：保证逻辑前提下，减少扫描的数据量。
例如：两个索引字段，A字段长度20，B字段10，故选择B字段。

应用设计层解决办法：
1缓存系统保存计数值。对精度要求不高的可以使用
优点：快，不用查询数据库。
缺点:新增和删除完成与更新缓存统计中间不是原子操作，会有误差。异常重启丢失（全量查询同步）。

2数据库保存计数：
优点：通过事务保证逻辑完整性，崩溃恢复。
注意：将修改统计总数的变更语句放到事务的最后， 减少冲突时间。因为同时可能会有多个新增或删除。 将锁冲突最激烈的放在事务最后执行。

各种count()用法不同

count(A)语义介绍：如果A字段实际数据值不为null,则累计值+1，反之亦然。
count(*)：innodb针对单独做了优化，定义为取行数。（遍历不取数据）
count(主键)： innodb遍历所有的主键并返回给server层，server判断不为空则累加。
count(1)：innodb遍历整张表单不取值，server对返回的每一行放入一个数字1，判断不可能为空，然后进行累加。
理解为：count(1)返回是一个空行不拷贝数据等，server层给这行数据加一个数字1。count(主键)会拷贝数据然后传递给server，比count(1)性能低。

order by

CREATE TABLE `t` ( 
`id` int(11) NOT NULL, 
`A` varchar(16) NOT NULL,
 `B` varchar(16) NOT NULL, 
 PRIMARY KEY (`id`), 
 KEY `B` (`B`)) ENGINE=InnoDB;

order by null 阻止排序。

例子：select * from t where A=XX order by B limit 10;
一个简单的order by执行过程：
这条语句mysql会给每个线程分配一块内存用作排序，称为sort_buffer。 排序在server层。
1获取所有字段A=XX的数据行；
2每取一行放入sort_buffer中；
3获取完之后对sort_buffer进行排序按照B字段排序；
4排序后返回给执行方。
重点区别： sort_buffer_size参数控制sort_buffer可以排序的最大数据量。
排序的数据量小于 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表示使用了12个文件，外部排序默认使用归并排序算法，分成12份后各自排序，然后集合排序。 number_of_tmp_files的文件个数根据数量量和sort_buffer_size决定。
number_of_tmp_files为0时，说明没有使用临时文件，直接在内存排序了。

examined_rows： 表示排序的行数是4000行。
sort_mode：packed_additional_fields意思是对字符串做紧凑处理，实际占用空间。
rows：实际扫描行数。

更改排序算法 —只放入排序字段和主键，最后回表

SET max_length_for_sort_data = 20;
用于排序控制单行长度大于20，则更换排序算法。

rowid 排序

例子：查询返回三个字段ID，A,B三个字段总长度大于20则更换排序算法，具体流程如下：
1初始化 sort_buffer，放入排序字段 B 和 id；
2根据A条件查询数据行，然后回表到主键索引获取B和ID放入sort_buffer；
3继续2的步骤，直至不满足A条件；
4对sort_buffer按照B字段排序；
5排序好后取出前10条ID，回表查所有数据返回。

此时的查询临时表结果：

sort_mode 变成了 ，表示参与排序的只有 B和 id 这两个字段。
number_of_tmp_files 变成 10 了，是因为这时候参与排序的行数虽然仍然是 4000 行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。

优先队列排序算法

MySQL 5.6 版本
维护堆的大小小于<sort_buffer_size，则使用的是当前算法。 如果大于sort_buffer_size，则使用归并

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

group by

使用临时表：
select id%10 as m,count(*) as c from t1 group by m;
创建内存临时表，表里有两个字段 m 和 c，主键是 m；
扫描t1表，依次获取放入内存临时表,
计算id%10的值记为x，如果临时表没有数据为X的m字段，则新增（x,1）
如果有则(x,c+1)，遍历完成后根据m排序，返回

在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引（如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引，来解决这个问题）。

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

不使用临时表：
select a,count(*) as c from t1 group by a; a字段有索引则不使用临时表。

order by rand()

内存临时表

例子：select word from words order by rand() limit 3;
使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。
1：创建内存临时表，表中两个字段 浮点数类型 A, 字符串类型 B。
2：首先将所需要的字段word查询出放入内存临时表的字段B中，根据B值rand()生成一个随机数放入字段A。查询出所有word数据。
3：初始化sort_buffer，sort_buffer中两个字段一个为临时表的随机数A，一个为这条数据的rowID,
4:进行排序，得到前条数据根据rowID去内存临时表中找数据返回。

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

磁盘临时表

tmp_table_size 参数配置 默认16MB
如果临时表大小超过 > tmp_table_size ，则使用磁盘临时表。
磁盘临时表的默认是InnoDB：internal_tmp_disk_storage_engine参数控制

条件字段函数操作

字段：t_modified datetime DEFAULT NULL,
查询语句条件：month(t_modified)=7
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。最后使用全部遍历。
（放弃索引树的搜索功能：因为索引树是有序的所以保证了搜索查询的效率，经过函数操作后，可能会变成无序，所以会走遍历所有）
如果主键索引大小比当前字段大，则会选择遍历t_modified索引，反正则遍历主键索引。

隐式类型转换

字符串与数字

mysql中：字符串和数字做比较的话，是将字符串转换成数字。

隐式字符编码转换

字符集：utf8mb4，utf8
因为utf8mb4比utf8包含更多，所有utf8mb4是超集。mysql内部碰到不同字符集操作时会将utf8 字符串转成 utf8mb4 字符集，再做比较。（原则：按数据长度增加的方向）

发生join等操作时，且两张表字段字符编码不相同，被驱动表字符编码不是超集，驱动表是超集，虽然两表查询条件为索引字段，也是不会走索引的，因为需要对被驱动表做字段的字符集转换，转换就是对跳转字段做函数操作，遵循上方的原则 ，条件字段做函数操作会丧失搜索索引效果。

Multi-Range Read优化

select * from t1 where a>=1 and a<=100;
a有索引，但是查询需要返回的是全部数据。
MRR的优化为获取将符合a的数据的主键id临时存储，然后对id进行升序排序，然后进行回表查询。
核心：将随机io变成顺序io。

join

t1数据少，t2数据多，t2的a有索引

Index Nested-Loop Join --NLJ

select * from t1 straight_join t2 on (t1.a=t2.a);
使用小表作为驱动表，被驱动表的关联字段需要有索引。
straight_join 命令是使用固定的链接方式，t1是驱动表，t2被驱动表。
类似于嵌套查询， 遍历全表t1,每拿到一条数据则拿出a值去t2表中查询符合条件的，因a有索引所以搜素很快。

Simple Nested-Loop Join --NLJ

select * from t1 straight_join t2 on (t1.a=t2.b);
因为表t2的b没有索引，导致每次嵌套查询都会全表扫描。扫描行=t1.count * t2.count

Block Nested-Loop Join --BNL

select * from t1 straight_join t2 on (t1.a=t2.b);
这个算法会将t1的数据放入线程内存 join_buffer中，也就是会将整张表放入。然后遍历t2的每一行数据于内存的join_buffer数据进行对比。满足条件则放入结果集。（和上一个算法的不同，判断次数同样多，但是将判断放在了内存里面进行，相比扫描一次判读一次效率会高好多）
缺陷：数据量大会导致内存都是当前数据，影响内存淘汰策略，导致内存命中率降低。
可能会多次扫描被驱动表，占用磁盘 IO 资源；
判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。
如果放不下表 t1 的所有数据话，策略很简单，就是分段放。 假设join_buffer设定xx容量，可以存放t1的一半数据，放满后，遍历t2表一一对比。遍历完后，清空join_buffer，读取t1未进入过join_buffer的数据，放满后或者读取完数据。继续进行遍历t2表一一对比。

Batched Key Access

简单的说是利用BNL中的join_buffer概念，用作于NLJ上。

使用BKA对BNL优化
select * from t1 straight_join t2 on (t1.a=t2.b);
t2的b字段是没有索引的，且t2数据量很大，这条语句如果使用bnl会消耗非常多的资源。
优化方式：根据b条件全表扫描一次t2,将符合条件的数据放入一个临时表中，为了使用BKA,将临时表数据字段b加上索引，此时将临时表和表t1进行join操作。

hash join

将两条语句拆开两次查询，拿到结果后在客户端创建hash结构，进行去对比筛选。