<<深入理解mariadb和mysql>>之mysql优化学习记录

2/13/2017来源:SQL技巧人气:1319

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查看)