1.using filesort
mariadb默认采用aria存储引擎处理临时表，参数aria_sort_buffer_size
一次扫描：将查询的所有列(含排序列)放入缓存区排序 mysql 5.0以后引入
二次扫描：只将排序列和主键放入缓存区排序，然后再根据主键读取数据 mysql5.0之前的
需要使用两次扫描：
记录大小比max_length_for_sort_data设置大时
查询包含blob或text类型的数据列时
排序处理方法：
使用索引排序 extra无法显示内容
只对驱动表排序(含无连接情形)，然后再连接 extra显示using filesort
将连接结果保存到临时表后，在临时表中排序 extra显示using temporary、using filesort
前一种为流处理:检索到一条，就返回客户端一条
后两种为缓冲处理:选取符合条件的所有记录进行排序或分组，再一起返回给客户端
与排序相关变量:
show status like 'Sort%';
sort_merge_passes 多次合并的处理次数
sort_range 对通过索引范围扫描的结果进行排序的次数
sort_scan，通过全表扫描检索的结果进行排序的次数
sort_rows 目前为止已排序的全部记录数
group by 处理
1.索引处理 --与order by 处理方式相似
2.松散索引处理 using index for group-by
只对单个表查询的group by ，group by 后面字段顺序要与组合索引一致，且select 后面字段顺序也要与group by 字段一致，select后支持min或max函数
3.临时表处理
using temporary、using filesort
distinct处理:
1.不含集合函数的distinct，类似group by，能用索引
2.带集合函数，如count(distinct a)，需要临时表，不能用索引，但extra不显示using temporary
3.不带where条件的可走全索引或范围扫描
临时表：
1.mariadb在内存中使用memory引擎，在磁盘中使用aria引擎，而Mysql在磁盘中使用myisa引擎
下面查询需要临时表：
1.1 order by 与group by的数据列不同
1.2 order by 或group by的数据列不在驱动表上
1.3 同时有distinct与order by时，或无法使用索引处理distinct时
1.4 使用union时，select_type显示union result
1.5 使用union all时，select_type显示union result
1.6 执行计划select_type为derived时
后三种是使用了临时表，但不显示using temporary
2 在磁盘中创建临时表
什么情况下会创建？
2.1 存储的内容包括了blob或text类型数据
2.2 所有记录全部大小或从union、union all查询的列中存在大于512字节的数据列时
2.3 group by或distinct数据列含用大于512字节的数据列时
2.4 要存储数据的全部大小超过了tmp_table_size或max_heap_table_size设置值时
前三种直接在磁盘中创建临时表，后一种先是在内存中建临时表，超过大小，再转换到磁盘中。
max_heap_table_size--用户可以创建内存表的大小
tmp_table_size--临时表最大值
3.状态变量
show status like 'Created_tmp%tables';
Created_tmp_tables --内存和磁盘中的
Created_tmp_disk_tables---磁盘中的
Created_tmp_disk_tables/Created_tmp_tables<5%
4.带索引的内部临时表
临时表自动创建索引，通过下面命令开启:
set optimizer_switch='derived_with_keys=on';
5 ICP (using index condition）
索引条件下推
将索引条件推下到inndodb引擎层过滤数据，而不是在mysql/mariadb层过滤。
6 rowid-ordered scan、key-ordered scan
MRR(多范围读)---先通过索引读取符合where条件的记录，然按主键排序，再从实际数据文件中读取其余数据列，减少随机IO
Mysql 5.6显示using MRR
mairaDB显示rowid-ordered scan和key-ordered scan
其中mysql 5.6的using MRR只相当于rowid-ordered scan
key-ordered scan是用于主键与连接的查询中,多表连接中，其中有一个表的连接字段为主键，对符合条件的记录按连接字段进行排序，然后再连接。
开启方法：
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=8;
rowid-order scan: 从驱动表读取记录，再按连接列读取被驱动表,在读取被驱动表的索引后，根据索引的rowid(myisam或aria引擎)或主键(xtradb或innodb)进行排序，再根据排序好的顺序读取
被驱动表的记录。
key_order scan: 从驱动表读取记录，再对连接列进行排序，再根据排序顺序读取被驱动表，被驱动表是xtradb或innodb引擎且主键为连接列
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_cache_level=8;
set optimizer_switch='join_cache_hashed=on'; ---开启hash join
set optimizer_switch='join_cache_bka=on'; --开启bka (批量主键访问连接)
bka (批量主键访问连接):先读取驱动表，再将所需数据列与连接列存储到连接缓冲，当填满时，会将连接缓冲的内容传送给多范围读引擎
基于rowid-order scan和key-ordered scan，在bka中同时出现.
mrr大小由mrr_bffer_size决定，bak中的mrr是由join_buffer_size
相关状态变量：show status like 'Handler_mrr%';
只读取少量记录时，使用mrr性能反而下降，使用mrr后，可能排序无法用上索引。
索引合并 index_merge
using union:并运算，or
using sort_union
using intersect :交集，and
using sort_intersect
set optimizer_switch='index_merge_sort_intersection=on';默认为off
inner join的结果取决于inner表
outer join的结果取决于outter表
left outer join左边的表为外表(驱动表)，right outer join右边的表为外表(驱动表)。
连接算法：
简单嵌套循环
块嵌套循环
块嵌套循环散列
块索引(bka)
块索引散列(bkah)
join_cache_incremental 、join_cache_hashed、join_cache_bka 默认都有打开，通过optimizer_switch
join_cache_level（8种取值 1-8)
join_cache_space_limit--限制处理查询时最大可分配的连接缓冲的最大大小
set optimizer_switch='optimize_join_buffer_size=on';
简单嵌套循环:利用索引，大部分是这种连接方式
块嵌套循环与简单嵌套循环最大不同是在于使用连接缓存，读取驱动表数据放到join_buffer中 using join buffer
块嵌套循环散列:块表示使用连接缓存，嵌套循环表示可能会多次反复执行创建(建立hash表)与探测(扫描、计算与检索)阶段。用于数据量大的情况。
块索引连接(批量主键访问连接 bka)---先读取驱动表，再将所需数据列与连接列存储到连接缓冲，连接缓冲填满时，就会将连接缓冲中的内容传送给"多范围读"引擎。
rowid-order scan
key-order scan
两种结合+bkah
块索引散列(bkah)--使用多范围读来读取驱动表，生成hash表;读取被驱动表，并返回结果。
哪些情况适用于半连接优化：
1.查询中使用in或=ANY形式的条件时
2.子查询不带有聚合函数与having子句时
3.子查询不包含union的单一select时
4.子查询的where条件与外部查询的其它条件通过and运算符进行连接时
5.子查询不是使用连接的update或delete语句时
6.不使用事先制定的执行计划时
7.外部查询与子查询都使用实际存在的数据表时
8.外部查询与子查询都不使用straight_join提示时
1.Table pullout优化
就是将子查询改成表连接的优化，执行计划中id值相同，extra字段未显示任何信息，最好方法是查看explain extend.... 然后show warnings查看
子查询使用unique或主键检索结果只有一条时，子查询的表会作为外表(驱动表)
没有单独参数关闭，要关闭只能将semijoin关闭。
2.firstmatch优化
执行计划中id相同，主表为外表(驱动表),extra有firstmatch(主表名)，只要检测到子查询第一个符合条件的记录，就不再继续检索。无法用于group by或聚合函数的子查询优化.
optimizer_switch中的semijoin和fristmatch默认为ON
3.materialization优化
将子查询全部具体化.
具体化后的临时表在连接中用作被驱动表，并且使用distinct_key进行检索经，叫materialization-lookup，具体化后的临时表在连接中用作驱动表，并且进行全表扫描，称为materializtion
-scan.
限制：
必需不是关联子查询，可以用于group by或聚合函数的子查询，具体化情形下使用内部临时表
optimizer_switch中的semijoin和materialization默认为ON.执行计划中的select_type为materialized
4 loosescan(m..n)--用于in子查询中可能产生重复记录时
先使用using index scan访问方法读取子查询内容，然后删除重复记录时，要使用lossescan优化方法，不需要使用临时表。
使用方法：
set optimizer_switch=default;
set optimizer_switch='firstmatch=off';
set optimizer_switch='materialization=off';
执行计划中有loosescan
5.duplicate weedout优化
半连接子查询转换为一般inner join查询执行，最后再删除重复记录，可改写成inner join+group by
关联子查询也可用这种优化方法，不用group by 或聚合函数，执行计划中有start temporary和end temporary
将连接的记录放在临时表，然后删除重复记录
使用方法：
set optimizer_switch='loosescan=off'
set optimizer_switch='firstmatch=off';
set optimizer_switch='materialization=off';
非半连接子查询的优化：
materialization
in-to-exists(show warnings查看)