mysql 查询优化 高性能mysql笔记

衡量查询性能的三个指标

1. 返回的行数。
2. 扫描的行数：查询所需要扫描的行数。
3. 响应的时间：服务时间(执行sql的时间)+排队时间(查询在等待i/o或者在等待锁)。

优化慢查询

1. 采用索引覆盖以及延时索引。
2. 重构查询方式：复杂查询拆分为多个简单查询。
   • 切分查询：
       删除一个表中的大量数据，sql 一次执行会锁住大量数据占用资源导致性能下降。可以切分为多次执行一次删除一万比较高效，也可以增加间隔时间，从而把一次性的压力分到一个很长的时间段中。
   • 分解关联查询：
       将关联查询分解成多条简单查询。
     1.增加缓存命中率(单表查询容易命中)。
     2.减少了锁竞争。
     3.减少冗余数据的查询。
     4.扩展性提高
3. 重构表结构

MySQL 查询执行基础

  查询流程

1. 客户端与服务区进行连接通信。
2. 服务器检查是否有缓存，缓存如果命中则，直接返回缓存中的结构。否则进行下一步。
3. 对sql进行解析预处理，再由优化器生成执行计划。
4. 根据执行计划，服务器调用存储引擎的API查询。
5. 得到数据则缓存并返回给客户端。

  MySQL 客户端和服务端通信协议

  MySQL 客户端和服务端通信协议是“半双工”，客户端发送给服务器和服务器发给客户端不能同时发生，这种协议让MySQL通信简单快速，但也就无法进行流量控制，一旦一端开始了，另一端需要等它结束。所以查询语句很长的时候，参数max_allowed_packet(用于设置客户端发送到服务端的数据大小)就特别重要了。

   MySQL 的查询状态 { SHOW FULL PROCESSLIST; }

• Sleep ：线程等待客户端发送新的请求。
• Query ：线程正在查询或者正在将结果发给客户端。
• Locked ：线程等待锁释放。
• Analyzing and statistics ：线程正在收集存储引擎的统计信息。
• Copying to tmp table ：
    线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做Group By操作，要么是文件排序操作，或者是UNION操作。若后面还有on disk标记，则表示临时表放在磁盘中。
• Sorting result ： 线程正在对结果进行排序。
• Sending data ：
    表示多种情况，可能在多个状态之间发传输数据，或者在生成结果集，或者在向客户端返回数据。
  MySQL 的查询缓存
  在解析一个查询前如果存在查询缓存则检查用户权限有就返回 (缓存以 查询语句的hash值为key (对查询语句大小敏感))。

MySQL 查询优化处理

• MySQL 通过sql语句关键词生成sql执行计划。
• MySQL 优化器是基于成本的优化器 {SHOW STATUS LIKE ‘last_query_cost’} 其中MySQL 是成本(value个数据页) 。
• MySQL 评估成本时不考虑缓存它假设读取任何数据都是一次磁盘i/o。
• MySQL 优化器的最优可能和我们想的最优(执行时间短)不一样mysql是基于成本模型。

MySQL（优化器）能处理的优化类型：

1. 重新定义关联表的顺序。
2. 将外连接转化成内连接。
3. 使用等价变换规则。
   可以移除一些恒成立和一些恒不成立的判读。例如（5 = 5 AND a > 5） 会被改写为 a > 5。
4. 优化COUNT()、MIN() 和 MAX()
   例如找到某一列的最小值，这一列如果有索引只需要查询对应B-Tree索引最左端的记录。
5. 覆盖索引扫描
      索引中的列包含所有查询中需要的列的时候，只需要使用索引返回数据，不需要搜索数据行。
6. 子查询优化
      MySQL可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。
7. 提前终止查询
      当发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。
8. 等值传播
      如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列中 select * from a inner join b on a.id=b.id where a.id >1
   会应用到两个表的关联查询中 a where id>1 inner join b where id>1。
9. 列表IN()的比较
      MySQL中IN()列表中的数据线进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，是O(lgn)级别的操作，等价转换成OR查询的复杂度是O(n)

MySQL如何执行关联

   MySQL中关联不仅仅是一个查询需要到两个表匹配才叫关联，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联。

   MySQL采用嵌套循环关联操作进行关联 ，MySQL先从一个表中循环取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，依次下去，直到所有表中匹配的行为止回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回上一层次关联表。看是否能够找到，依次类推。MySQL 会重新调整查询的关联表顺序 找到最优的顺序。

• straight_join 可以控制关联的顺序比如 form t straight_join t2 on t.id=t2.id。
• n张表会有 n*(n-1)…*1中关联顺序。
• 当关联表的数量超过 optimizer_search_depth 的默认值(62) 则会 使用穷尽查询。
     [1] 所谓贪婪式，是在对问题求解时，不从整体最优上考虑，以当前情况为基础根据某个优化作为最优选择，这导致贪婪算法对问题不都能得到整体最优解，但能产生整体最优解或者是整体最优解的近似解。
     [2] 所谓穷尽式，是把各种组合全部执行一遍，如A/B/C/D四个表，两两全部组合为AB/AC/AD/BC/BD/CD，三三组合为ABC/ABD/ACD/BCD，四个组合为ABCD；无一遗漏。

排序

MySQL 有两种排序算法:

1. 两次传输：第一次去除要排序的列进行排序，第二次根据排序好的行再去表中查找所有的数据行(此时会产生大量随机io) MyISAM 非常依赖操作系统所以MyISAM成本更高，当时这样有利于排序缓冲区中可以容纳更多的数据。
2. 单次传输：单次传输会把所有数据去除进行排序这样只需要一次顺序io无需随机io，缺点数据量大占空间 会使更多的排序块合并。
      可以通过max_lenght_for_sort_data 的大小来设置使用哪种排序算法。
   MySQL 查询出第一条结果时就会逐步返回给客户端如果需要缓存此时也会缓存在缓存中 这样可以节省空间。

MySQL 查询优化器的局限性

关联子查询

   MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。

SELECT * FROM sakia.film WHERE film_id IN( SELECT film_id FROM sakia.film_actor WHERE actor_d = 1)

我们希望MySQL能够先执行内层子查询，这个子查询通过索引来查找，应该会很快，事实上，MySQL 并不这么干，它会把从查询这样优化：

SELECT * FROM sakia.film WHERE EXIST (SELECT * FROM sakia.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id);

MySQL把外层压人到子查询中处理，如果外层的表是非常大的表的话，这个查询的性能就会非常的糟糕。

1. 改进方案：SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;

2. SELECT *FROM a WHERE id in (select id from b)当a表中的数据量小b表中的数据量大可以优化为 SELECT *FROM a WHERE EXISTS( SELECT * FROM b WHERE a.id = b.id);

union 查询

  外部条件无法应用到内部 (select * from a ) union all (select * from a2) limit 10 无法将limi 10 应用到 内部如：
  (select * from a limit 10 ) union all (select * from a2 order by id) 如果可以应用到内部就无需再去查询下一个union的表了 无法应用到内部会产生临时表再去limi 10 这样成本变高。

等值传递

  如果两个表关联 用的是id 然后又有一个select a inner join b on a.id=b.id and id in(1,2,3,3…很大) 此时这个in 会应用到两个表的查询中 会使得查询变慢。

索引合并优化

1. MySQL 不支持多核并发查询 (有一个查询 select * from t 这个表数据很多 其他数据库可以把表分成多份 多个cpu 去读取 mysql 只支持一个单核去读取)
2. mariadb 支持hash索引其他的不支持
3. mysql 不支持松散索引扫描 如 index(a,b,c) ； 但是 或者 但是 是不行的。

 select * from t where b=1 无法使用索引
 select max(b) from t group by a  可以
 select max(b) from t group by a ,c 可以
 select max(c) from t group by a ,b 无法使用索引

   explan 会有 user_index_for_group_by 松散索引。

4. 最大值最小值优化 假如你要获取的最大值或最小值的列是有序的 可以 select a from t limit 1 即可获取最小值
5. 在统一表查询更新需要给表起一个别名 update t inner join (select id,b from t) t2 on t2.id=t.id set t.a=t2.b

查询优化器的提示hint

  在查询中加入相应的提示，可控制该查询的执行计划

• hight_priority 和 low_priority 设置查询的优先级控制执行顺序。当多个语句同时访问某一个表时，哪些语句的优先级高、低

  • hight_priority：select语句，mysql将其放在表队列的前面；insert语句，简单抵消全局low_priority设置的影响。
  • low_priority：让语句一直等待
      对使用表锁的引擎有效，不要在innodb或有细粒度锁机制 并发控制的引擎中使用：导致并发插入被禁。

• delayed：对insert replace有效。
    MySQL 将 使用该提示的语句 立即返回给客户端，将插入的行数据放入到缓存区 在表空闲时批量将数据写入,不是所有的存储引擎都支持，会导致last_insert_id()无法正常工作 比较适合日志表的大批量插入客户端无需等待。

• straight_join： 关联顺序相关。

  • 放在select后：查询中所有表按在语句中顺序进行关联
  • 可放任何两个关联表的名字间：固定器前后两表的关联顺序
  • 可使explain语句查优化器选择的关联顺序，然后使用该提示重写查询，在看顺序，升级需重审视查询

• sql_small_result和sql_big_result：分组和排序。
  告诉优化器对group by或distinct查询如何使用临时表及排序

  • small：告诉器结果集会很小，可将结果集放在内存的索引临时表，避免排序
  • big：告诉器结果集可能非常大，建议使用磁盘临时表做排序操作

• sql_buffer_result:告诉器将查询结果放入到一临时表，尽可能快地释放表锁(这样会占用更多的服务端内存)。

• sql_cache和sql_no_cache：这个结果集是否应该缓存在查询缓存中。

• sql_calc_found_rows：让返回的结果集包含更多信息，不应该使用，可通found_row()获得这个值。

• for update和lock in share mode：只对实现了行级锁的引擎有效
  控制select语句锁机制，会对符合查询条件的数据行加锁，避免使用，锁挣用对于没有用到id的覆盖索引查询无效 因为没有用到id 因为行的信息保存在主键中。

• use index 、ignore index和force index：使用或不使用哪些索引来查询记录。

  • 5.1及后可通过for order by 和for group by指定是否对排序和分组有效。
  • force index同use index（除force index会告诉优化器全表扫描成本高于索引扫描）。

5.6新增：

1. optimizer_search_depth:控制优化器在穷举执行计划时的限度。
2. optimizer_prune_level:默认打开，让优化器据需要扫描的行数来决定是否跳过某些执行计划。
3. optimizer_switch：包含些开启/关闭优化器特性的标志位。
     前两个参数控制优化器走一些捷径：让优化器处理复杂sql时仍高效，但也可能错过些真正最优的执行计划
     自定义设置优化器提示 ，可能使新版的优化策略失效，耍小聪明 不太好。

优化特定类型的查询

  多数优化技巧和特定版本有关，注意版本

1. 优化count查询
   1. count(列) 统计某个列的行数，如果count()指定列或列的表达式，统计的是这个表达式有值的结果数(不统计null);
   2. count( * ) 统计结果集的行数。
        MyISAM没有where条件的count(*)非常快是因为MyISAM 引擎保存了表的总行数所以快 。 当有where 条件就和其他一样了。
   3. 取小的范围： 比如要取得id>5的数据 那么可以用总数据量减去 id<5的数据量(MyISAM 引擎)、
   4. 近似值: explan 查询然后获得 记录数的近似值
   5. 获取多个count 可以用sum 例如：

select  SUM(IF((age=18),1, 0))  AS age18 ,SUM(IF((age=19),1, 0))  AS age19  from table

 6. 汇总表:把数据定时查询放到汇总表中
 7. 快速简单和精确只能实现其中两个

2. 优化关联查询

   1. 确保on或using子句中列上有索引，无其他理由，只在关联顺序的第二张表相应列建索引。
   2. group by 和order by 的表达式只涉及一个表中的列，才能使用索引。
   3. 版本升级，注意关联语法、运算符优先级等可能发生变化的地方。

3. 优化子查询
   尽可能使用关联查询代替，mysql5.6及更新的版本或mariadb可忽略

4. 优化group by和distinct

   1. 可使用索引来优化。
   2. 无法使用索引时：
      1. group by使用临时表或文件排序做分组，可通过提示sql_big_result和sql_samll_result‘指挥’优化器。
      2. 对关联查询做分组，且按照查找表的某个列进行分组，采用查找表的标识列(on t.id=t2.id id就是标识列)来分组的效率会更高。
   3. 优化group by with rollup：
      1. 如对返回分组结果再做一次超级聚合，可使用with rollup实现，但是要尽可能将with rollup功能转移到程序中处理因为group by a,b with rollup 其中 with rollup 会按照 a分组然后把数据加入到结果集。

5. 优化limit分页

   1. limit 0,10 这样会扫描所有的记录然后提取10条 尽量避免 因该用 limit 10。
   2. 偏移量很大但是要的数据很少，优化 要么在页面中限制分页的数量 要么优化大偏移量的性能。
   3. 尽可能使用索引覆盖扫描，据需要做一次关联操作再返回需要的列。
   4. 延迟关联：提效率，扫描尽可能少的页，获取要访问的记录后据关联列回原表查询需的列select a,b from t inner join (select id from t where name='ss’limit 5)t2 where t2.id=t.id。
   5. 将limit查询转换为已知位置的查询，让mysql通过范围扫描获得对应的结果。
   6. 预先计算出边界值假如id是连续的 form table where id>10 limit 10 下一次就是 id>20 limit 10 。

6. 优化SQL_CALC_FOUND_ROWS：
   分页时，技巧在limit语句中加SQL_CALC_FOUND_ROWS提示，可获得去掉limit后满足条件的行数，作为分页的总数，会扫描all满足条件的行、抛弃不需要的行，代价可能有点高。

   1. 将具体的页数换成“下一页”按钮，每页显示20条但查询21条哦，21条存在则有数据，否则没有数据(哈哈~优秀优秀）。
   2. 先获取缓存较多数据，每次分页从缓存中获取：程序据结果集大小采取不同策略，<1000 显示all分页链接 >1000 灵活设计 比 找到all再抛弃效率高。
   3. 使用explain结果中的rows值作为结果集总数的近似值，需精确结果再count（*）。

7. 用户union查询

   1. 通过创建、填充临时表来执行union查询，很多优化策略在union中无法很好使用。
   2. 手工将where 、limit、 order by 等下推到union各个子查询中，让优化器充分利用这些条件进行优化。
   3. 除非需要服务器消除重复行，否则一定要使用union all ，无all ，mysql会给临时表加上distinct：导致对临时表做唯一性查询，代价高。

8. 静态查询分析
   percona toolkit 的pt-query-advisor能解析查询日志、分析查询模式、给出all可能会有潜在问题的查询、给出建议。