Mysql（高性能Myslq学习笔记）——查询优化

1.概述

首先来说一下一条sql 的完整请求流程

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段。
3. 服务器进行SQL解析，预处理，再由优化器生成对应的执行计划，
4. mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。

查询性能低的基本原因是访问的数据太多。对于低效的查询，一般通过下面两个步骤来分析很有效：

1. 确认应用是否检索大量超过需要的数据。（意味着访问了太多的行，或者太多的列。比如：常见的使用*返回所有列，或者 重复查询相同的数据这样就可以把这条数据缓存起来）。

2. 确认Mysql服务器层是否分析大量超过需要的数据行。

最简单的衡量查询开销的三个指标如下（在Mysql的慢日志中查看）：

1. 响应时间
2. 扫描行数
3. 返回行数

响应事件只是一个表面上的值，是服务器时间（数据库处理这条查询的时间）和排队时间（等待锁或者IO的时间）。

扫描行数和返回行数的比 一定程度上反应了找到数据的效率高不高。但是行的长短，是从内存中访问还是磁盘中访问速度和开销都是不同的，因此只能做一个不够完美的指标。

2.关于索引的使用

在评估查询开销时，需要考虑一行数据从表中找到某一行数据的成本。mysql 有多种访问查找方式。

时用EXPLAIN 语句， type列反映了访问类型（全表扫描、范围扫描、覆盖索引、唯一索引、常量。。）https://blog.csdn.net/dennis211/article/details/78170079可以到这篇里看一下或者查查type类型。

一般的Mysql能够使用三种方式来应用WHERE（从好到坏）：

1. 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
2. 使用覆盖索引（在Extra列中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果。在Mysql服务器层完成，但无需回表查询记录（应为使用的覆盖索引）。
3. 从数据表中返回数据，然后过滤不满足的记录（再Extra列中出现Using Where）。这在mysql服务器层完成，Mysql需要先从数据表读出记录然后再过滤。

如果发现了需要扫描大量数据只返回少数行，那么可以这样：

1. 使用覆盖所索引
2. 使用单独的汇总表
3. 重写查询，让Mysql优化器以更优的方式来处理。

索引再写一章详写

2.1延迟关联

举个栗子：tab1(col1_pk,col2,col3,col4),存在索引 tab_index(col2) 其中  col1为主键

SELECT * FROM tab1 WHERE col2 = 'aaa' and col3 LIKE '%bbbb%';

这时完全是不能使用索引的更别说覆盖索引

1.首先因为没有任何索引能够覆盖这个chaxun  

2.索引中是无法做LIKE操作的。

我们做一些改动把索引扩展为tab_index(col2,clo3,col1_pk)

SELECT  

*

FROM tab1 t1 JOIN (

    SELECT col1 from tab1  WHERE col2 = 'aaa' and col3 LIKE '%bbbb%'

)  AS t2 ON (t1.col1_pk = t2.col1_pk) 

这时  子查询的第一步 可以使用 覆盖索引先查出符合 col2='aaa'的数据  然后再LIKE匹配'%bbbb%'的数据  再用主键匹配做做关联，使用执行计划可以看到Extra字段为 using index,using where ，这种方式叫延迟关联，因为延迟了对列的访问。

2.1排序优化

Mysql有两种可以生成有序的结果：1.通过排序操作; 2.按索引顺序扫描(Explan中type列的值为index说明在使用索引排序)

在要使用索引获得有序数据时，要注意遵循B-tree的最左原则，顺序需要一样否则无法利用索引获取有序数据。扫描索引是很快的但是如果索引无法为覆盖索引，那么不得不不停的回表获取数据，基本都为随机I/O。

当无法使用索引排序时，Mysql就需要进行文件排序。如果数据量小排序会在内存中进行“快速排序”，当数据量大于内存时那么Mysql会先进行数据分块，然后对每个独立的块进行“快速排序”然后合并。Mysql的排序方式有两种：

1.两次传输排序（旧版本使用）先读取行指针和要排序的列进行排序。然后回表取所需要的数据行。

2.单次传输排序（新版本使用）直接读取查询所需的所有列，然后排序返回结果。

相比而言单次传输减少了一次回表取数据的操作速度上快了，但是由于第一次就取出了所有需要的列，到时排序所占用的空间也大了。当查询所需的列不超过max_length_for_sort_data时使用的为“单次传输排序”。Mysql在进行排序时使用的临时表所占用的比原表可能都要大，因为Mysql 会对所有记录分配足够长的空间，例如Varchar 则会默认分配完整长度，UTF-8会预留三个字节。

在mysql中无论何种排序都是种成本很高的操作，应该尽量避免对大量数据的排序。

注：

3.查询的切分/分解

Mysql 在设计上 连接和断开连接都很轻量级，这使得返回一个小的查询结果方面很高效，因此我门在对待一个大的查询或者复杂查询的情况下 可以做切分或者分解处理

3.1查询的切分

例如删除旧的历史数据，如果使用一条大的语句直接删除可能会导致一次锁住很多数据、占满事物日志、耗尽系统资源、阻塞很多小的但是重要的查询。如果切分成多次执行那么就可以经可能的避免这样的问题：

rows_affected = 0

do {

      rows_affected = do_query{

              DELETE FROM table_name WHERE  column < 'xxxxx' LIMIT 10000 

     }

} while rows_affected > 0

一次删除一万行相对高效而且对服务器影响相对小的做法，同时的如果每次删除之后都暂停一会再做下一次的删除，这样可以把压力分散到一个很长的时间段内，大大的降低对服务器的影响减少锁等待的时间。

3.2查询的分解

有时候对关联查询进行分解然后将结果在应用程序中进行关联也是一个提高性能的选择，这样做的好处有几点

1. 简单查询可以更好的命中查询缓存。
2. 将查询分解后可以减少锁的竞争
3. 应用层做关联可以更容易对数据库进行拆分，更容易左到高性能和可扩展性
4. IN() 带替关联查询 我们可以做排序进行有序的查询，比无序随机关联查询更快（在Mysql中IN()会对列表中的数据先进行排序然后再通过二分查找来确定列表中的值是否满足条件 并非等同于多个OR）
5. 减少冗余查询，一条可能要使用多次的查询记录值查询一次就好
6. 相当于在应用中实现了哈希关联 （待完善）

当可以缓存单个查询、可以将数据分布到多个服务器上、可以使用in（）的方式来代替、查询中使用同一个数据表时，这几种情况将关联放到应用程序中都是可以更加高效

4.特定类型查询优化

4.1COUNT()

count() 函数有两种不同的作用，一个是统计的是指定列所有非空的数量，一个是统计行数 统计行数时使用count(*)就好，它不会扩展成所有列，而只是去记录行数。如果并不需要统计出精确值 使用explain出的优化器估值也是个不错的选择。

4.2关联查询

1.当优化器的关联顺序时 TAB_A，TAB_B 用c列做关联时，确保TAB_B的c列上有索引。

2.确保GROUP_BY和ORDER_BY的表达式只涉及到一个表中的列，否则Mysql无法通过索引来优化这个过程。

4.3LIMIT分页

当分页偏移量非常大时如 limit 1000000,20 这样会抛弃前1000000条数据只返回后20条数据。这种查询的有话要么在页面中限制分页数量上线，要么优化最大偏移量的性能。最大偏移量性能的优化大体分三种：

1.尽量使用索引覆盖扫描而不是所有列，然后再做一次关联操作返回所有需要的列，例：

2.将LIMIT查询转换为已知位置的查询，通过范围扫描获取结果。例：在一个位置列上有缩影，并且预先计算出边界值。

3.如果可以使用书签记录上次获取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描。假设

SELECT * FROM TAB_A ORDER BY rental_id DESC limit 20

获取了rental_id 为16049 —— 16030的记录那么下次查询就可以从16030开始

SELECT * FROM TAB_A WHERE rental_id < 16030 ORDER BY rental_id DESC limit 20。这样无论翻到到多后面性能都会还不错。

4.4 UNION

Mysql通过临时表的方式来执行UNION查询，因此需要手工的将WHERE，LIMIT，ORDER BY等子句手工的写到各个子查询中，已便优化器可以充分优化。除非非常需要服务器消除重复否则一定要使用UNION ALL否则临时表会被加上DISTINCT选项，对整个临时表进行数据唯一性检索，代价非常高。 

 

4.5 静态查询分析（Percona Toolkit中的pt-query-advisor）

 

5. 总结

除了表结构，查询，索引等这些基础手段，分表分区和查询缓存等等也可以提高性能，后面看到用到了再继续写。比较菜有什么问题或者写的不对的地方还请各位大佬指正。