一. 明确思路

优化更需要优化的SQL(并发量更高的sql)
定位优化对象的性能瓶颈(cpu,io,网络带宽)
明确优化目标(要优化到那种程度)
从explain执行计划入手(写sql之前就要对sql的执行计划有一定的评估)
永远用小结果集驱动大的结果集(表关联用小表关联大表,如果条件允许)
尽可能在索引中完成排序(order by的列添加索引)
只取出自己需要的列，不要用select * (减少网络带宽,避免索引失效)
仅使用最有效的过滤条件(where的参数尽量都是有索引的列,并且尽量使用少的where条件)
尽可能避免复杂的join和子查询

9.1 尽量不要join超过3张表

9.2 关联的表过大时(过百万),就分多次查询(程序先查到主表的数据再用主表的数据查询关联表的数据)--这一点很重要,如果关联的表过大过多可能会造成大量的资源被锁定造成性能降低.

9.3 不要指定驱动表(尽量不要使用letf/right join来指定主表)

9.4 on关联条件都需要添加索引,避免mysql误判驱动表,具体原因看驱动表定义3
小心使用order by，group by，distinct语句
合理设计并利用索引
若在排序的时候输出过多的列，则会浪费内存(查询的列在索引内,通过索引排序,如果部分列没有索引则通过内存排序)
尽可能使用索引排序(索引本身就是排序好的)
当where使用的查询列都有索引且效率和作用都可以满足查询需求,则选择key_len比较小的列(索引长度)

二. 关联查询核心概念

2.1 驱动表定义

当进行多表连接查询时，动表的定义为

指定了联接条件时，满足查询条件的记录行数少的表为驱动表
未指定联接条件时，行数少的表为驱动表
没有索引的表作为驱动表.一般行数少的表为驱动表,但是如果行数多的表所使用的关联字段没有加索引,那就是使用没有加索引的表来做驱动表(受Index Nested-Loop Join算法影响) 如 a,b表.a表的b_id没有索引且又100条数据,b表的id有索引但只有5条数据.具体sql: select * from a join b on a.b_id=b.id; 此时mysql会选择a表作为驱动表

2.2 关联查询的概念

MySOL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，最后合并结果

2.3 关联查询实现原理

关联的列没索引走这种Block Nested-Loop Join(块嵌套循环)

关联的列有索引走这种Index Nested-Loop Join(索引嵌套循环)

2.3.1 Simple Nested-Loop Join

如图所示每次从驱动表拿一条数据去和被驱动表比较,驱动表有10条记录被驱动表就需要被循环10次,效率过低. 此模式一般不会被使用,一般都会使用Block Nested-Loop Join

Simple Nested-Loops Join算法相当简单、直接。即外表（驱动表）中的每一条记录与内表（被驱动表）中的记录进行比较判断（就是个笛卡尔积）。对于两表联接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，被驱动表的具体访问次数取决于对驱动表执行单表查询后的结果集中的记录条数。

2.3.2 Index Nested-Loop Join

如图:驱动表先匹配被驱动表关联的索引看有没有命中的数据，有命中数据再回表查询这条记录，获取其它所需要的数据，但列的数据在索引中都能获取那都不需要回表查询，效率更高！

2.3.3 Block Nested-Loop Join

因为每次从驱动表取一条数据都是磁盘扫描所有比较耗时。

这里就做了优化就是 每次从驱动表取一批数据放到内存中，然后对这一批数据进行匹配操作 。

这批数据匹配完毕，再从驱动表中取一批数据放到内存中，直到驱动表的数据全都匹配完毕。

这块内存在MySQL中有一个专有的名词，叫做 join buffer，我们可以执行如下语句查看 join buffer 的大小

show variables like '%join_buffer%'

注意join_buffer中缓存的数据是驱动表中的每一列和join所关联的列,如果查询出来的列数过多,内容过大,很容易造成join_buffer被存满,如果机器内容充足可以适当调整join_buffer大小

2.4 关联查询优化思路

尽可能减少join语句中的Nested Loop的循环总次数
优先优化Nested Loop的内层循环
保证join语句中被驱驱动表上join条件字段已经被索引
无法保证被驱动表的Join 条件字段被索引且内存资源充足的前提下，不要太音惜join Buffer的设置
并发量太高的时候，系统整体性能可能会急剧下降
复杂的 Join 语句，所需要锁定的资源也就越多，所阻塞的其他线程也就越多
复杂的Query 语句分拆成多个较为简单的 Query 语句分步执行

三. 分组查询核心概念

3.1 注意点

order by 字句中的字段加索引(扫描索引即可，内存中完成，逻辑io )
若不加索引的话会可能会启用一个临时文件辅助排序(落盘，物理io)
order by排序可利用索引进行优化，order by子句中只要是索引的前导列都可以使索引生效，可以直接在索引中排序，不需要在额外的内存或者文件中排序
不能利用索引避免额外排序的情况，例如:排序字段中有多个索引，排序顺序和索引键顺序不一致(非前导列)
MySQL对于不能利用索引避免排序的SQL，数据库不得不自己实现排序功能以满足用户需求，此时SQL的执行计划中会出现“Using filesort”，这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要由sort buffer size参数与结果集大小确定。MySQL内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法:快速排序、归并排序和堆排序

3.2 排序算法

3.2.1 常规排序

从表t1中获取满足WHERE条件的记录
对于每条记录，将记录的主键+排序键(id,col2)取出放入sortbuffer
如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序;否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)
若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的
循环执行上述过程，直到所有满足条件的记录全部参与排序
扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3)
将获取的结果集返回给用户

3.2.2 优化排序

常规排序方式除了排序本身，还需要额外两次IO。优化的排序方式相对于常规排序，减少了第二次IO。主要区别在于，放入sortbuffer不是id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。这种方式的代价在于，同样大小的sort buffer，能存放的(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO

3.2.3 优先队列排序

5.6及之后的版本针对Order by limit M，N语，在空间层面做了优化，加入了一种新的排序方式--优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limit M，N这类排序的问题虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sortbuffer空间即可，对于M，N很小的场景，基本不会因为sortbuffer不够而导致需要临时文件进行归并排序的问题。对于升序采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序采用小顶堆，最终堆中的元素组成了最大的N的元素

5.6版本堆排序问题:

针对limitMN的语采用了优先队列，而优先队列采用堆实现比如上述的例子order by idc limit 0，3 需要采用大小为3的大顶堆;limit 3，3需要采用大小为6的大顶堆。由于idc为3的记录有3条，而堆排序是非稳定的(对于相同的key值，无法保证排序后与排序前的位置一致)，所以导致分页重复的现象。为了避免这个问题，我们可以在排序中加上唯一值，比如主键id，这样由于id是唯一的，确保参与排序的key值不相同

3.3 关注配置

# 查看排序内容内存缓冲区
show variables like '%sort_buffer_size%';
# 修改sort_buffer_size为2m
SET GLOBAL sort_buffer_size = 2 * 1024 * 1024;

# 查看随机读取缓冲区
show variables like '%read_rnd_buffer_size%';
# 修改read_rnd_buffer_size为256kb
SET GLOBAL read_rnd_buffer_size = 256 * 1024;

# 查看排序内容长度(默认4k)
show variables like '%max_length_for_sort_data%';
# 修改max_length_for_sort_data为16kb
SET GLOBAL max_length_for_sort_data = 8 * 1024;

3.3.1 sort_buffer_size

简称排序内容内存缓冲区,排序会开辟一块内存用来把需要排序的内容放到内存中排序,如果排序内容超出sort_buffer_size需要多次排序会把排序好的内容放到临时文件中(使用归并排序算法).

增大sort_buffer_size并不是为了让 MySQL选择第二种排序算法，而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段，因为分段会造成MySQL不得不使用临时表来进行交换排序。

sort_buffer_size是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB，这里我们设置为2M,默认256k,单位b

3.3.2 read_rnd_buffer_size

是MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲提高读取的效率。

3.3.3 max_length_for_sort_data

在MySQL中，排序算法分为两种，一是只加载排序字段到内存，排序完成后再到表中取其他字段，二是加载所有需要的字段到内存，显然第二种节省了IO操作，所以更快。决定使用哪种算法是通过参数max_length_for_sort_data来决定的，当所有返回字段的最大长度小于这个参数值时，MySQL就会选择第二种算法，反之使用第一种。所以，如果有充足的内存让MySQL存放须要返回的非排序字段，就可以加大这个参数的值来让MySQL选择第二种排序算法。
当内存不是很充裕时，不能简单地通过强行加大上面的参数来强迫MySQL去使用高效算法，否则可能会造成MySQL不得不将数据分成很多段，然后进行排序，这样可能会得不偿失，此时就须要去掉不必要的返回字段，让返回结果长度适应max_length_for_sort_data参数的限制。

3.3.4 总结

MySQL中排序过程受系统参数 sort_buffer_size 和 max_length_for_sort_data 的影响。当查询量过大时，超过 sort_buffer_size 的大小，那么就会借助磁盘文件进行排序。如果查询的字段过多，每一行记录的查询字段长度之和超过 max_length_for_sort_data 后，MySQL 会认为数据量过大，可能会超过 sort_buffer_size，因此会选择使用 rowid 排序。

3.4 排序sql案例

explain select idc, name from t3 where id>2 and id<10 order by idcname,id\G

分别在查询字段，where条件，排序字段上做出各种可能的组合主要就是看有无索引，索引在以上三个关注点上的生效情况

3.5 小技巧

控制台查看sql执行计划格式化为竖排键值对,在sql末尾加\G

explain select idc, name from t3 where id>2\G

mysql慢查询优化