转载:https://www.cnblogs.com/shenzikun1314/p/6396105.html
本篇深入了解查询优化和服务器的内部机制，了解MySql如何执行特定查询，从中也可以知道如何更改查询执行计划，当我们深入理解MySql如何真正地执行查询，明白高效和低效的真正含义，在实际应用中就能扬长避短。
声明：本人使用的数据库版本为MySql 5.1
一、基本原则：优化数据访问
查询性能低下的最基本原因就是访问了太多数据，一些查询要不可避免地筛选大量的数据，大部分性能欠佳的查询都可以用减少数据访问的方式进行优化。
1、首先分析应用程序是否正在获取超过需要的数据，这通常表现在获取了过多的行或列。一些查询先向服务器请求不需要的数据，再丢掉他们，这个让服务器造成了额外的负担，增加了网络开销，消耗了内存和CPU资源。
> 如果前台只需要显示15条数据，而你的查询结果集返回了100条，则要想想是否真有必要这样干了，最好使用LIMIT来限制查询的条数。
> 尽量避免使用SELECT * , 也许你并不需要所有的列，但获取所有的列将会造成覆盖索引这样的优化手段失效，也会增加磁盘I/O、内存和CPU的开销等，所以基于这种情况，尽量使用SELECT t.id, t.name ... 这种查询具体字段的SQL。
但是，SELECT * 这种稍显浪费的方式可以简化开发，增加代码的复用性(比如以后扩展了字段，就不用再改sql代码了)。
如果系统使用了持久化框架，而我们只查询了某一些字段出来，然后再直接去更新这个持久化对象时，那些未查询出来的字段就会被设置为NULL，导致数据丢失。所以，如果只查询一部分字段，要避免去更新持久化对象(亲身经历)。
在程序中，还是倡导使用SELECT t.id, t.name ... 这种形式，能更好地利用索引；如果只是显示数据，那就按需查询部分字段即可，这样能更充分利用覆盖索引；如果需要更新数据，则必须查询出所有字段。
2、其次看是否检查了过多的数据，一般从查询的执行时间、检查的行数、返回的行数来看，但这些不可作为绝对的标准。
> 看下面的这个执行计划：
第一幅图中：key表明使用了id_card索引；rows=1，表明只检查了一行数据，所以其速度是很快的。
第二幅图中：删除了索引后的执行计划，没有使用索引，检查的行数是81697，而我们只需要一行数据；而如果数据量不断增加，再与其它表关联查询的话，其性能可想而知是有多低效。
所以，查看是否检查了过多的行，使用一些优化手段如利用好索引或者重构查询尽量去减少检查的行数。
> 再看下面这个执行计划：
这个查询联接了多张表，仅第一张表就检查了10W行(而我们只需要15行)，然后再与其它表进行联接，再排序，效率自然低下了。而其它检查出只有一行的表，可看出其使用了索引列进行联接，可见使用好索引的高效。
看第二幅图：使用了一个子查询以减少检查的行数，加上id列本身是排好序的，所以Extra列可以看到没有使用临时表进行文件排序了，在第一幅图中，使用临时表排序(using temporyary,using filesort)是很耗时的。
二、重构查询
有些时候我们需要重写查询以获取更好的性能，尽管得到的结果可能不同，也许最终程序的代码也会和查询一起被改。
1、是否可以把一个耗时的复杂查询分解成多个简单的查询。
> 平时我们更倡导用尽可能少的查询做尽可能多的事情，这样可以减少网络通信开销，能减少查询解析和优化的步骤，以及代码上似乎更优雅。
但是在MySql中，MySql被设计成可以很高效地连接和断开服务器，而且能很快地响应精简的查询。在现代网络下，MySql在一般的服务器上每秒钟可以处理50000个查询。因此，对于一些耗时的复杂查询，可以通过分解查询以得到更高的效率。
2、分解联接，把一个多表联接分解成多个单表查询，然后在应用程序端实现联接
> 例如有如下的一个连接查询：
SELECT * FROM tag JOIN tag_post ON tag.id = tag_post.tag_id WHERE tag.title = 'test';
分解成两个查询：
SELECT * FROM tag WHERE tag.title = 'test'; -- 假设返回id有 (10,11,12,13,14,15);
SELECT * FROM tag_post WHERE tag_id IN (10,11,12,13,14,15);
这样分解查询，看似浪费，但其针对一些耗时的多表联接能带来很好的性能提升：
》 缓存的性能更高：上面的查询已经被缓存起来，下次再查询tag.title = 'test'，则会直接从缓存中取出；第二条IN操作，下次查询(11,12,14, 20,25)，对于11,12,14则直接从缓存中取出，只去读取20,25。如果一个表经常改变，分解联接可以减少缓存失效的次数。
》 可以减少多余的行访问，联接操作，每从tag表中检查一行，就会去tag_post中去检查。
> 什么时候使用分解联接更好： 可以缓存早期查询的大量数据 , 数据分布在不同的服务器上 , 对于大表使用IN()替换联接
三、MySql如何优化和执行查询
下面这幅图显示了查询的执行路径：
① 客户端将查询发送到服务器；
② 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。
③ 服务器解析，预处理和优化查询，生成执行计划。
④ 执行引擎调用存储引擎API执行查询。
⑤ 服务器将结果发送回客户端。
1、客户端将查询发送到服务器
>首先需要知道，客户端用一个数据包将查询发送到服务器，一旦客户端发送了查询，剩下的就是等待结果。如果一个查询过大，比如批量插入，有时会出现"MySQL server has gone away"的错误，导致的原因可能就是传送的数据太大，导致连接断开了，可以通过 SHOW VARIABLES LIKE "max_allowed_packet" 命令查看你的服务器所允许传送的最大数据，可在my.ini里配置。
> 服务器发送的响应由许多数据包组成，服务器发送响应的时候客户端必须接收完整的结果集，不能只提取几行数据后要求服务器停止发送剩下的数据。所以，使用LIMIT来获取你所需要的数据行数。
> 每个MySql连接，或者叫线程，在任意一个给定的时间都有一个状态来标识正在进行的事情。可以使用 SHOW [FULL] PROCESSLIST 命令来查看哪些线程正在运行，及其查询状态，Command列显示了状态。
一些常见的状态：其它的查找MySql手册
Sleep 线程正在等待客户端，以向它发送一个新语句
Query 线程正在执行查询或往客户端发送数据
Locked 该查询被其它查询锁定
Copying to tmp table on disk 临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器
Sending data 线程正在为SELECT语句处理行，同时正在向客户端发送数据
Sorting for group 线程正在进行分类，以满足GROUP BY要求
Sorting for order 线程正在进行分类，以满足ORDER BY要求
2、服务器检查查询缓存
> 在解析一个查询之前，如果开启了缓存，MySql会检查查询缓存，进行大小写敏感的哈希查找。即使查询和缓存中的查询只有一个字节的差异，也表示不匹配，查询就会进入下一步。
> MySql查询缓存保留了查询返回给客户端的完整结果，当缓存命中的时候，服务器马上返回保存的结果(会先检查权限)，并跳过解析、优化和执行步骤。查询缓存保留了查询使用过的表，如果表发生了改变(如update)，那么缓存的数据就失效了。
3、服务器解析、优化，生成执行计划
> 如果查询缓存中没有，下一步就是将查询转变成执行计划，包括解析、预处理和优化的过程。这个过程的任何一步都有可能出现错误，比如语法错误等。这里我们可以看到平时出现的大部分错误是从哪一步抛出来的。
> 首先是解析器将查询分解成一个个标识，然后构造一颗“解析树”，解析器保证查询中的标识都是有效的，会检查其中的基本错误，比如字符串上面的引号没有闭合等。
> 然后预处理器检查解析器生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。最后，预处理器检查权限。
> 之后，优化器把解析树变成执行计划。一个查询通常可以有很多种执行方式，并且返回同样的结果，优化器的任务就是找到最好的方式。
》 MySql使用的是基于开销的优化器。它会预测不同的执行计划的开销，并且选择开销最小的一个。可以使用 SHOW STATUS LIKE "Last_query_cost" 命令查看查询的开销(但不能作为绝对标准)。如下表名最近一个查询会造成29728次随机读取。
》 但是优化器并不总是能选择最好的方案。比如统计数据可能是错误的，服务器依赖于存储引擎提供的统计，它可能很准确，可能很不准确。再比如优化器不会估算每一个可能的执行计划，所以它可能会错过优化方案。
》 MySql执行计划是树形结构，目的是指导执行引擎产生结果。最终的计划中包含了足够的信息来重建查询。可以对某个查询使用EXPLAIN EXTENDED 命令，并在结尾加上 SHOW WARNINGS，就可以看到重建后的查询。
下图中，结果1显示了查询执行计划，结果2中Message显示了MySql优化后的查询语句，也是最终执行的语句，可以复制出来看看。
》 在后面可以看到MySql处理的一些优化类型。
4、查询执行引擎执行查询
> MySql查询执行引擎使用执行计划来处理查询。和优化部分相反，执行部分不会很复杂。MySql按照执行计划的指令进行查询(执行计划时一个数据结构)。计划中的许多操作都是通过存储引擎提供的方法来完成的。
5、返回结果到客户端
> 执行计划的最后一步是将结果发送到客户端，即使查询没有结果要返回，服务器也会对客户端的联接进行应答，比如有多少行受了影响。
> 如果查询是可缓存的，MySql会在这时缓存查询。
> 根据MySql的执行机制，一旦它处理了最后一个表并且成功地产生了一行输出，他就会把这个结果发送到客户端。这样的好处是，服务器不用把这一行保存在内存中，二是服务端可以尽快的开始工作。
四、MySql能处理的一些优化类型
MySql的优化器是相当复杂的，它使用了很多优化技巧把查询转换为执行计划。下面列出了MySql能处理的一些优化类型，以便我们去了解MySql优化器能够做的工作。
但是，“不要试着比优化器更聪明”，不要想着去做一些优化器做的事情，有可能只会让自己的查询变得更复杂，更难以维护，除非你确实明白那样做所带来的影响。通常，应该让优化器按照自己的方式来优化查询。
你也可以通过EXPLAIN EXTENDED SELECT ... ... ; SHOW WARNINGS; 查看最终优化后的执行sql。
1、对联接中的表重新排序
> MySql优化器中最重要的部分是联接优化器，它决定了多表查询的最佳执行顺序。通常可以用不同的顺序联接几个表，然后得到同样的结果。联接优化器评估不同执行计划的开销，并且选择开销最低的计划。后面再对联接查询细讲。
2、 将外连接转换成内连接
> 外联接并不总是按照外联接的方式进行，优化器有时能够将外连接转换为等价的内联接，以便适应其它的优化，比如排序
3、 代数等价法则
> MySql使用代数化转换来简化并且规范化表达式。它可以隐藏或减少变量，移除不可能的限制和常量条件。
> 比如，为了方便，我们可能经常会这样干：WHERE 1=1 ，可以看到优化后的结果，1=1是被移除了的。 再比如，(a<b AND a=100) 被转换为 (b>100 AND a=100)；条件如果是数字，直接比较数字的效率是最高的。
4、 优化COUNT()、MIN()、MAX()
> 查找某列最大/最小值，该列又有索引，查找最大值，则会直接找最后一行；最小值，则直接找第一行。因为索引已经排好序了。可以从EXPLAIN中看到：“选择被优化掉的表”
> COUNT()，对于MyIsam引擎总是保留着表行数的精确值，查找所有行则直接取出，速度很快。
> 但是，如果MIN()/MAX()查询的列上没有索引，则会进行全表扫描。
5、如果一个表达式可以被简化为一个常量，那么这个表达式就会被转换。 在WHERE 、USING、ON这些连接条件强制值相等的条件中，常量具有传递性
> 可以看到等值联接被转换为了两个常量表达式。但这并不需要我们去手动写成两个常量表达式，优化器自会去做这些事情。
6、覆盖索引
> 先简单说下覆盖索引，以后再详细讨论索引的细节：覆盖索引简单的说就是索引上包含了该列的数据，如果是组合索引的话，就包含多列。比如有一个覆盖索引：index_idCard_name(id_card, name)，如果只查询id_card和name的话，则可以通过id_card快速定位到该索引，并从索引中取出这两列数据，从而避免了从磁盘中读取该行数据。如果你还另外读取了其它列，也会去读取该行(这就涉及到随机I/O的开销了)。
> 当索引包含查询需要的列时，MySql就可以使用索引来避免读取行数据。
> 对于索引，优化器如果使用了一些复杂的算法来处理复杂的查询语句，有可能会使用大量的CPU和内存资源，MySql并不会去考虑这些开销，它只管如何高效地读取数据；这时候查询看上去开销较低，实际上比整表扫描还慢。
> 如果因为优化器的限制而运行得很慢，可以通过IGNORE INDEX命令禁止一些索引。
7、 子查询优化
> MySql可以将某些类型的子查询转换成相等的效率更高的形式，把它们简化为索引查找，而不是独立的多个查询。后面再详细讨论子查询优化。
> 但是，MySql有时把子查询优化得很差。可以看到一个本来想要的IN列表被优化成了一个关联查询。类似于这种，可以自己手动改写成JOIN关联的方式。
8、 早期终结
> 一旦满足查询或某个步骤的条件，MySql就会立即停止处理该查询，或者该步骤。比如LIMIT子句，只要满足LIMIT的数目，就会停止查询。
> 再比如，检查到一个不可能的条件，他就会停止整个查询，这个查询在优化阶段就停止了。
9、比较IN()里面的数据
> MySql会对IN()里面的数据进行排序，然后用二分法查找某个值是否在列表中，这个算法的效率是O(Log n)。
> 也许你会想到用OR代替IN()，等同的OR子句的查找效率是O(n)，在列表很大的时候，OR子句会慢很多。
10、 MySql不会让你在对同一个表进行UPDATE的同时运行SELECT。
> 比如下面的更新，同时查询，会报错误：You can't specify target table 'xp_user' for update in FROM clause； 你需要知道有这么一个限制。
> 但是，你可以以一种变通的方式来执行，比如使用关联。
五、联接查询
1、MySql的联接执行策略
MySql的联接执行策略很简单，它把每个联接都看成一个嵌套循环，这意味着MySql用一个循环从表中读取数据，然后再用一个嵌套循环从下一个表中发现匹配数据。它不停地持续这个过程，当发现一行匹配的数据时，再根据SELECT子句中的列输出；接着，查找下一个匹配的行。
> 比如下面这个联接查询：
SELECT S.id, S.name, S.phone, C.name FROM student S, clazz C WHERE S.clazzid = C.id AND S.id < 5;
MySql会先从student表中建立一个循环，查出一个满足id<5的行，
然后去clazz表建一个循环，查找满足S.clazzid=C.id的行，
找到一行后，就输出SELECT的列，然后继续查找下一行，
遍历完了之后，再回到student的循环，继续查找，重复上面的步骤，直至全部查找出来。
> 再看下面的这个左连接查询：
SELECT S.id, S.name, S.phone, C.name FROM student S LEFT JOIN clazz C ON S.clazzid = C.id WHERE S.id < 5;
同样，先在student表中建立一个循环，查找一个满足id<5的行，
然后去clazz表建一个循环，查找满足S.clazzid=C.id的行，
如果找到了满足的行，则输出SELECT的列；如果没找到，则输出student相关SELECT的列，而clazz表的列则输出NULL，这就是左连接
遍历完后，再回到student的循环，继续查找，重复步骤，直至全部查找出来。
> 从本质上来说，MySql以同样的方式执行每一种查询。例如，在处理FROM子句中的查询时，它会先执行子查询，并且把结果放到临时表里面，然后把临时表当成普通表进行下一步处理，因而它叫衍生表(Derived Table)。看图一
> MySql也使用临时表来处理联合(UNION)，MySql将UNION看成一系列的单个查询，它们将结果写入临时表中，最后再读取出来组成最终结果。看图二
> MySql会把所有的右联接(RIGHT [OUTER] JOIN)改写成等价的左连接(LEFT [OUTER] JOIN)，可以通过SHOW WARNINGS查看最终的执行语句。看图三
在MySql中，每个单个查询都是一个联接，所以从临时表读取数据实际也是联接。
顺便一提：临时表没有索引，只是存放数据，所以原表的索引，约束等都是不起作用的。
>正是因为MySql的这种联接策略，所以MySql不支持全外联接(FULL OUTER JOIN)，全外联接不能用嵌套循环，因为在检索第一个表时可能就没有匹配的数据。
图一：
图二：
图三：
2、联接优化器
> MySql优化器中最重要的部分是联接优化器，它决定了多表查询的最佳执行顺序，这样一般可以减少读取的行数。通常可以用不同的顺序联接几个表，然后得到同样的结果。联接优化器评估不同的执行计划的开销，并且选择开销最低的计划。
> 对联接重新排序通常是一种非常有效的优化手段。但重新排序有时并不是最佳的执行计划，这是可以使用STRAIGHT_JOIN参数，并且按照你认为最佳的方式来组织联接的顺序；但是这种情况是很少见的，联接优化器比人更能精确的计算开销。
六、优化特定类型的查询
1、优化COUNT
> 通常来说，使用了COUNT的查询很难优化，因为他们需要统计很多行(访问很多数据)。在MySql内部优化它的唯一其他选择就是使用覆盖索引。还不够，就需要考虑更改应用程序的架构。可以考虑使用汇总表，还可以利用外部缓存系统。
> COUNT有两个不同的工作方式：统计值的数量和统计行的数量。值是一个非空的表达式(NULL意味着没有值)。
如果COUNT()的括号中定义了列名或其它表达式，COUNT就会统计这个表达式有值的次数。
COUNT的另一种形式就是统计结果中行的数量。当MySql知道括号中的表达式永远都不会为NULL的时候，它就会按这种方式工作。例如COUNT(*)，它是COUNT的一种特例，它不会把通配符*展开成所有的列，而是忽略所有的列并统计行数。
> 关于COUNT(NULL)的应用：当你统计某列不同值的数量时，可以像下面这样写SQL
1.使用SUM()函数：SELECT SUM(IF(c1='red', 1, 0)) AS red, SUM(IF(c1='blue'), 1, 0) AS blue, SUM(IF(c1='black'), 1, 0) AS black FROM color;
2.使用COUNT()：SELECT COUNT(c1='red' OR NULL) AS red, COUNT(c1='blue' OR NULL) AS blue, COUNT(c1='black' OR NULL) AS black FROM color;
2、优化联接
> 确保ON或USING使用的列上有索引。
> 确保GROUP BY或ORDER BY只引用了一个表中的列，这样，MySql可以尝试对这些操作使用索引。
3、优化GROUP BY和DISTINCT
> 在很多情况下，MySql对这两种方式的优化方式基本都是一样的。实际上，优化过程要求他们可以互相转化。通常来说，索引是优化它们的一种重要的手段。
> 当不能使用索引的时候，MySql有两种优化GROUP BY的策略：使用临时表或文件排序进行分组。任何一种方式对于特定的查询都有可能是高效的。可以使用SQL_SMALL_RESULT强制MySql选择临时表，或者使用SQL_BIG_RESULT强制它使用文件排序。
4、优化LIMIT和OFFSET
> 一个常见的问题是偏移量很大，比如查询使用了LIMIT 10000, 20，它就会产生10020行数据，并且丢掉前10000行。这个操作的代价非常高。
> 一个提高效率的简单技巧就是在覆盖索引上进行偏移，而不是对全行数据进行偏移。可以将从覆盖索引上提取出来的数据和全行数据进行联接，然后取得需要的列。这会更有效率。
> 一个较好的设计是把页面调度放到"下一页"链接上，假设每页只显示20个结果，那么查询就应该LIMIT 21行数据，但是只显示20行，如果结果中有第21行，则有下一页。
> 可以提取并缓存大量数据，比如1000行数据，然后从缓存中获取后续页面的数据。
七、查询优化提示
如果不满意MySql优化器选择的优化方案，可以使用一些优化提示来控制优化器的行为。可以将适当的提示放入查询中，它只会影响当前的查询。
1、DELAYED：这个提示用于INSERT和UPDATE。
应用了这个提示的语句会立即返回并将待插入的列放入缓冲区中，在表空闲的时候再执行插入。它对于记录日志很有用，对于某些需要插入大量数据也很有用。它有很多限制，比如，延迟插入不能运行于所有的存储引擎上，并且无法使用LAST_INSERT_ID();
2、STRAIGHT_JOIN：
这个提示可用于SELECT语句中SELECT关键字后面，也可以用于联接语句。它的一个用途是强制MySql按照查询中表出现的顺序来联接表；另一个用途是联接两个表时，强制这两个表按照顺序联接。
3、SQL_SMALL_RESULT和SQL_BIG_RESULT
用于SELECT语句。它们告诉MySql在GROUP BY或DISTINCT查询中如何并且何时使用临时表。SQL_SMALL_RESULT告诉优化器结果集会比较小，可以放在索引过的临时表中，以避免对分组后的数据排序。SQL_BIG_RESULT表明结果集比较大，最好使用磁盘上的临时表排序。
4、SQL_BUFFER_RESULT
这个提示告诉优化器将结果放在临时表中，并且尽快释放掉表锁。
5、SQL_CACHE和SQL_NO_CACHE
SQL_CACHE表明将查询缓存；SQL_NO_CACHE则相反。
6、USING INDEX、IGNORE INDEX和FORCE INDEX
这几个提示告诉优化器从表中寻找行的时候使用或忽略索引。
好了 至此也算是简单的深入了解了MySql内部的运行机制，相信这对于以后学习高效运用MySql是非常有帮助的。