《MYSQL性能调优与框架设计》-总结

Query 语句优化的基本思路和原则
1. 优化更需要优化的Query；

一般来说，高并发低消耗（相对）的Query 对整个系统的影响远比低并发高消耗的Query 大。
假设有一个Query 每小时执行10000 次，每次需要20 个IO。另外一个Query 每小时执行10 次，
每次需要20000 个IO。
我们先通过IO 消耗方面来分析。可以看出，两个Query 每小时所消耗的IO 总数目是一样的，都是
200000 IO/小时。假设我们优化第一个Query，从20 个IO 降低到18 个IO，也就是仅仅降低了2 个IO，
则我们节省了2 * 10000 = 20000 （IO/小时）。而如果希望通过优化第二个Query 达到相同的效果，
我们必须要让每个Query 减少20000 / 10 = 2000 IO。我想大家都会相信让第一个Query 节省2 个IO
远比第二个Query 节省2000 个IO 来的容易。
其次，如果通过CPU 方面消耗的比较，原理和上面的完全一样。只要让第一个Query 稍微节省一
小块资源，就可以让整个系统节省出一大块资源，尤其是在排序，分组这些对CPU 消耗比较多的操作中
尤其突出。

1. 定位优化对象的性能瓶颈；

   在拿到一条需要优化的Query 之后，我们首先要判断出这个Query 的瓶颈到底是IO 还是
   CPU。到底是因为在数据访问消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资
   源？一般来说，在MySQL 5.0 系列版本中，我们可以通过系统自带的PROFILING 功能很清楚的找出一个
   Query 的瓶颈所在。

2. 明确的优化目标；
   当我们定为到了一条Query 的性能瓶颈之后，就需要通过分析该Query 所完成的功能和Query 对
   系统的整体影响制订出一个明确的优化目标
   一般来说，我们首先需要清楚的
   了解数据库目前的整体状态，同时也要清楚的知道数据库中与该Query 相关的数据库对象的各种信息，
   而且还要了解该Query 在整个应用系统中所实现的功能
   比如调整schema 设计，调整索引组成等，可能都是需要的。而如果该Query 所实现的是一些并不
   是太关键的功能，那我们可以让目标更偏向悲观值一些，而尽量保证其他更重要的Query 的性能。这种
   时候，即使需要调整商业需求，减少功能实现，也不得不应该作出让步。

3. 从Explain 入手；
   Explain 只是用来获取一个Query 在当前状态的数据库中的执行计划，在优化动手之前，我们比需要根据优化目标在自己头脑中有一个清晰的目标执行计划。
   在优化任何一个SQL 语句之前，都应该在自己头脑中已经先有一个预定的执行计划，然后通过不断的调整尝试，再借
   助Explain 来验证调整的结果是否满足自己预定的执行计划。对于不符合预期的执行计划需要不断分析
   Query 的写法和数据库对象的信息，继续调整尝试，直至得到预期的结果。

基本原则：
5. 多使用profile
6. 永远用小结果集驱动大的结果集；
驱动结果集越大，所需要循环的此时就越多，那么被驱动表
的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑IO 很少，循环次数多了，总量自然也
不可能很小，而且每次循环都不能避免的需要消耗CPU ，所以CPU 运算量也会跟着增加。
在优化Join Query 的时候，最基本的原则就是“小结果集驱动大结果集”，通过这个原则
来减少嵌套循环中的循环次数，达到减少IO 总量以及CPU 运算的次数。

1. 尽可能在索引中完成排序；

2. 只取出自己需要的Columns；

   对于任何Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的Column 越多，
   需要传输的数据量自然会越大，不论是从网络带宽方面考虑还是从网络传输的缓冲区来看，都是一个浪
   费。
   如果是需要排序的Query 来说，影响就更大了。在MySQL 中存在两种排序算法，一种是在
   MySQL4.1 之前的老算法，实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取
   出，然后在我们所设定的排序区（通过参数sort_buffer_size 设定）中进行排序，完成排序之后再次
   通过行指针信息取出所需要的Columns，也就是说这种算法需要访问两次数据。第二种排序算法是从
   MySQL4.1 版本开始使用的改进算法，一次性将所需要的Columns 全部取出，在排序区中进行排序后直
   接将数据返回给请求客户端。改行算法只需要访问一次数据，减少了大量的随机IO，极大的提高了带有
   排序的Query 语句的效率。但是，这种改进后的排序算法需要一次性取出并缓存的数据比第一种算法
   要多很多，如果我们将并不需要的Columns 也取出来，就会极大的浪费排序过程所需要的内存。在
   MySQL4.1 之后的版本中，我们可以通过设置max_length_for_sort_data 参数大小来控制MySQL 选择
   第一种排序算法还是第二种排序算法。当所取出的Columns 的单条记录总大小
   max_length_for_sort_data 设置的大小的时候，MySQL 就会选择使用第一种排序算法，反之，则会选
   择第二种优化后的算法。为了尽可能提高排序性能，我们自然是更希望使用第二种排序算法，所以在
   Query 中仅仅取出我们所需要的Columns 是非常有必要的。

3. 仅仅使用最有效的过滤条件；

   让查询走的索引尽可能小，如count(1),count(*)只会找二级索引中最短的索引进行查找
   看explain的keyLen,走的索引越小越好

4. 尽可能避免复杂的Join 和子查询；

   我们的Query 语句所涉及到的表越多，所需要锁定的资源就越多。也就是说，越复杂的Join 语句，所需要锁定的资源也就越多，所
   阻塞的其他线程也就越多。相反，如果我们将比较复杂的Query 语句分拆成多个较为简单的Query 语
   句分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。
   将复杂Join 语句分拆成多个简单的Query 语句之后，那不是我们的网络
   交互就会更多了吗？网络延时方面的总体消耗也就更大了啊，完成整个查询的时间不是反而更长了吗？
   是的，这种情况是可能存在，但也并不是肯定就会如此。我们可以再分析一下，一个复杂的Join Query
   语句在执行的时候，所需要锁定的资源比较多，可能被别人阻塞的概率也就更大，如果是一个简单的
   Query，由于需要锁定的资源较少，被阻塞的概率也会小很多。所以较为复杂的Join Query 也有可能
   在执行之前被阻塞而浪费更多的时间。而且，我们的数据库所服务的并不是单单这一个Query 请求，还
   有很多很多其他的请求，在高并发的系统中，牺牲单个Query 的短暂响应时间而提高整体处理能力也是
   非常值得的。优化本身就是一门平衡与取舍的艺术，只有懂得取舍，平衡整体，才能让系统更优。

前面4 点可以理解为Query 优化的一个基本思路，后面部分则是我们优化中
的基本原则

查看某个查询慢的原因定位：
show profiles
该命令可以查找出最近执行的命令id

show profile cpu, block io for query 6;
该命令可以查询这个命令的执行cpu,IO读写情况，方便定位问题，性能瓶颈

建立索引的注意点：
为什么不是越多索引越好？
因为索引建多了，update，insert只要涉及这个列，都会对索引进行更新，并且可能导致重新建立，分裂页，割裂页情况
还会导致索引数据庞大，导致整个数据表信息很大

怎么避免随机I/O：
如果某列的值是一些枚举类型，则不适合建立独立索引，因为查询到的时候，会发现符合条件的很多个，然后各自带的
主键ID随机性很强，再次去主键索引获取行信息时，会出现很大的随机I/O

这也是在查询时，如果查询符合条件的结果集很大时，优化器会自动按照全表扫描，而不走索引
如：2017-08-08 < createTime 2017-08-09,且CreateTime建立了索引，如果此时符合条件的结果集占
全表的比例很大，优化器会放弃CreateTime索引，直接全表扫描

但是对于枚举类型列，可以建立多列索引，只要查询符合前缀查询，就会很快

BTree的优点之一：
范围查询
用来避免排序

Hash索引：
范围查询不可以

Full-text 全文索引，只是从中取出一些字段作为索引罢了，参见lance,像查字典一样的搜搜

前缀索引：列值前面几个字做索引，和Full-text类似

压力测试：
mysqlslap –create-schema=example –query=”SELECT * FROM group_message WHERE
author = ‘3’ AND subject LIKE ‘weiurazs%’” –iterations=10000

对query语句执行1万次，计算出平均执行时间和最大最小执行时间

总结怎么选择索引进行查询：
1. 对于单键索引，尽量选择针对当前Query 过滤性更好的索引；
2. 在选择组合索引的时候，当前Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好；
3. 在选择组合索引的时候，尽量选择可以能够包含当前Query 的WHERE 子句中更多字段的索
引；
4. 尽可能通过分析统计信息和调整Query 的写法来达到选择合适索引的目的而减少通过使用
Hint 人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。

限制条件：
1. MyISAM 存储引擎索引键长度总和不能超过1000 字节；

1. BLOB 和TEXT 类型的列只能创建前缀索引；

2. MySQL 目前不支持函数索引；

3. 使用不等于（!= 或者<>）的时候MySQL 无法使用索引；
   经测验，大于小于的时候，用beettten and更好，而且，<>可以使用索引

4. 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；

5. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；

6. 使用LIKE 操作的时候如果条件以通配符开始（ ‘%abc…’）MySQL 无法使用索引；

7. 使用非等值查询的时候MySQL 无法使用Hash 索引；

下一篇：join优化
在MySQL 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，他没有其他很多数据库
所提供的Hash Join，也没有Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表
的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数
据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据，
再一次通过循环查询条件到第三个表中查询数据，如此往复。

总结：驱动表---for循环---访问下一个表---for循环下一个表

因此，减少驱动表的结果集，可以减少下一个表的访问次数，对于高并发，可以减少I/O

方法：1、以小的结果集驱动大的结果集

  2、实在没有索引可走，直接join buffer

下一篇：排序优化
ORDER BY，GROUP BY 和 DI STI NCT 优化

在MySQL 中，ORDER BY 的实现有如下两种类型：
◆ 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端
要求的有序数据返回给客户端；
◆ 另外一种则需要通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数
据返回给客户端。

在MySQL 第二种排序实现方式中，必须进行相应的排序算法来实现数据的排序。MySQL 目前可以通
过两种算法来实现数据的排序操作。
1. 取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在Sort
Buffer 中进行实际的排序操作，然后利用排好序之后的数据根据行指针信息返回表中取得客户端请
求的其他字段的数据，再返回给客户端；
2. 根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字
段存放在一块内存区域中，然后在Sort Buffer 中将排序字段和行指针信息进行排序，最后再利用
排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集，再按照顺
序返回给客户端。

总结下上面的排序算法：
1、先取id到sort buffer，再排id, 然后回表找数据，两次访问数据库
2、直接把id加行信息全部拿出来，再排序，最后返回，一次访问，但占用内存大

排序的实现方式：

第一种排序：sort buffer排序，第一个表join就可以排序出来的时候
先根据排序条件将数据进行了一次
filesort，也就是排序操作。

第二种排序：临时表排序
可能需要排序的字段同时存在于两个表中，或者
MySQL 在经过一次Join 之后才进行排序操作。

优化方法：
0.能走索引排序直接走索引排序，多列索引的重要性
1. 加大max_length_for_sort_data 参数的设置；
在MySQL 中，决定使用第一种老式的排序算法还是新的改进算法的依据是通过参数
max_length_for_sort_data 来决定的。当我们所有返回字段的最大长度小于这个参数值的时候，
MySQL 就会选择改进后的排序算法，反之，则选择老式的算法。所以，如果我们有充足的内存让
MySQL 存放需要返回的非排序字段的时候，可以加大这个参数的值来让MySQL 选择使用改进版的排
序算法。
2. 去掉不必要的返回字段；
当我们的内存并不是很充裕的时候，我们不能简单的通过强行加大上面的参数来强迫MySQL 去
使用改进版的排序算法，因为如果那样可能会造成MySQL 不得不将数据分成很多段然后进行排使用
序，这样的结果可能会得不偿失。在这种情况下，我们就需要去掉不必要的返回字段，让我们的返
回结果长度适应max_length_for_sort_data 参数的限制。
3. 增大sort_buffer_size 参数设置；
增大sort_buffer_size 并不是为了让MySQL 可以选择改进版的排序算法，而是为了让MySQL
可以尽量减少在排序过程中对需要排序的数据进行分段，因为这样会造成MySQL 不得不使用临时表
来进行交换排序。

Group By:优化
条件：多列索引–(group_id,user_id,gmt_create)

1、松散（Loose）索引扫描实现GROUP BY
-> SELECT user_id,max(gmt_create)
-> FROM group_message
-> WHERE group_id < 10
-> GROUP BY group_id,user_id\G

2、使用紧凑（Tight）索引扫描实现GROUP BY
-> SELECT max(gmt_create)
-> FROM group_message
-> WHERE group_id = 2
-> GROUP BY user_id\G
紧凑索引扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有
满足条件的索引键，然后再根据读取恶的数据来完成GROUP BY 操作得到相应结果

3、-> SELECT max(gmt_create)
-> FROM group_message
-> WHERE group_id > 1 and group_id < 10
-> GROUP BY user_id\G
查询后，group是无序的

group_id 并不是一个常量条件，而是一个范围，而且GROUP BY
字段为user_id。所以MySQL 无法根据索引的顺序来帮助GROUP BY 的实现，只能先通过索引范围扫描
得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成GROUP BY。

优化方案：
1. 尽可能让MySQL 可以利用索引来完成GROUP BY 操作，当然最好是松散索引扫描的方式最佳。
在系统允许的情况下，我们可以通过调整索引或者调整Query 这两种方式来达到目的；
2. 当无法使用索引完成GROUP BY 的时候，由于要使用到临时表且需要filesort，所以我们必须
要有足够的sort_buffer_size 来供MySQL 排序的时候使用，而且尽量不要进行大结果集的GROUP
BY 操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据copy 到磁盘上面再进行
操作，这时候的排序分组操作性能将是成数量级的下降；

DISTINCT 的实现与优化：
DISTINCT 的实现和GROUP BY 的实现也基本差不多，没有太大的区别。同样可以通过松
散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT 的时候，MySQL
只能通过临时表来完成。但是，和GROUP BY 有一点差别的是，DISTINCT 并不需要进行排序
1/2两种方法和group by原理一致
在第三种方法时，不需要排序

1、SELECT DISTINCT group_id
-> FROM group_message\G

2、SELECT DISTINCT user_id
-> FROM group_message
-> WHERE group_id = 2\G

3、SELECT DISTINCT user_id
-> FROM group_message
-> WHERE group_id > 1 AND group_id < 10\G

4、SELECT DISTINCT max(user_id)
-> FROM group_message
-> WHERE group_id > 1 AND group_id < 10
-> GROUP BY group_id\G
第4中进行了排序，第三种没有排序

order by null