1、为什么查询速度会慢？
响应时间过长。如果把查询看做是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。
查询的生命周期:
客户端->服务器->服务器上解析->生成执行计划->执行->返回结果给客户端。
其中”执行”包括大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
2、慢查询基础：优化数据访问
2.1、什么是慢查询？
慢查询，顾名思义，执行很慢的查询。有多慢？超过 long_query_time 参数设定的时间阈值（默认10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。
然而，慢查询日志默认是不开启的，也就是说一般人没玩过这功能。如果你需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。
2.2、是否向数据库请求了不需要的数据
**查询性能低下的最基本原因就是访问了太多数据**，一些查询要不可避免地筛选大量的数据，大部分性能欠佳的查询都可以用**减少数据访问**的方式进行优化。
首先分析应用程序是否正在获取超过需要的数据，这通常表现在获取了过多的行或列。一些查询**先向服务器请求不需要的数据，再丢掉他们**，这个让服务器造成了额外的负担，增加了网络开销，消耗了内存和CPU资源。
如果前台只需要显示15条数据，而你的查询结果集返回了100条，则要想想是否真有必要这样干了，最好使用LIMIT来限制查询的条数。
-
尽量避免使用SELECT *** , 也许你并不需要所有的列，但获取所有的列将会造成覆盖索引**这样的优化手段失效，也会增加磁盘I/O、内存和CPU的开销等，所以基于这种情况，尽量使用SELECT t.id, t.name ... 这种查询具体字段的SQL。
但是，SELECT * 这种稍显浪费的方式可以简化开发，增加代码的复用性(比如以后扩展了字段，就不用再改sql代码了)。
在程序中，还是倡导使用SELECT t.id, t.name ... 这种形式，能更好地利用索引；如果只是显示数据，那就按需查询部分字段即可，这样能更充分利用覆盖索引；如果需要更新数据，则必须查询出所有字段。
2.3、MySQL是否在扫描额外的记录
看是否检查了过多的数据，一般从查询的执行时间、检查的行数、返回的行数来看，但这些不可作为绝对的标准。
-
看下面的这个执行计划：
第一幅图中：key表明使用了id_card索引；rows=1，表明只检查了一行数据，所以其速度是很快的。第二幅图中：删除了索引后的执行计划，没有使用索引，检查的行数是81697，而我们只需要一行数据；而如果数据量不断增加，再与其它表关联查询的话，其性能可想而知是有多低效。
所以，查看是否检查了过多的行，使用一些优化手段如利用好索引或者重构查询尽量去减少检查的行数。
imageimage -
再看下面这个执行计划：
这个查询联接了多张表，仅第一张表就检查了10W行(而我们只需要15行)，然后再与其它表进行联接，再排序，效率自然低下了。而其它检查出只有一行的表，**可看出其使用了索引列进行联接，可见使用好索引的高效**。看第二幅图：使用了一个子查询以减少检查的行数，加上id列本身是排好序的，所以Extra列可以看到没有使用临时表进行文件排序了，在第一幅图中，使用临时表排序(using temporyary,using filesort)是很耗时的。
3、重构查询的方式
有些时候我们需要重写查询以获取更好的性能，尽管得到的结果可能不同，也许最终程序的代码也会和查询一起被改。
3.1、切分查询
是否可以把一个耗时的复杂查询分解成多个简单的查询？
平时我们更倡导用尽可能少的查询做尽可能多的事情，这样可以减少网络通信开销，能减少查询解析和优化的步骤，以及代码上似乎更优雅。
但是在MySql中，MySql被设计成可以很高效地连接和断开服务器，而且能很快地响应精简的查询。在现代网络下，MySql在一般的服务器上每秒钟可以处理50000个查询。因此，对于一些耗时的复杂查询，可以通过分解查询以得到更高的效率。
3.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()替换联接
4、查询执行的基础
4.1、MySQL客户端/服务器通信协议
下面这幅图显示了查询的执行路径：
① 客户端将查询发送到服务器。
② 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。
③ 服务器解析，预处理和优化查询，生成执行计划。
④ 执行引擎调用存储引擎API执行查询。
⑤ 服务器将结果发送回客户端。
首先需要知道，客户端用一个数据包将查询发送到服务器，一旦客户端发送了查询，剩下的就是等待结果。如果一个查询过大，比如批量插入，有时会出现"MySQL server has gone away"的错误，导致的原因可能就是传送的数据太大，导致连接断开了，可以通过 SHOW VARIABLES LIKE "max_allowed_packet" 命令查看你的服务器所允许传送的最大数据，可在my.ini里配置。
服务器发送的响应由许多数据包组成，**服务器发送响应的时候客户端必须接收完整的结果集**，不能只提取几行数据后要求服务器停止发送剩下的数据。所以，使用LIMIT来获取你所需要的数据行数。
每个MySql连接，或者叫线程，在任意一个给定的时间都有一个状态来标识正在进行的事情。可以使用 **SHOW [FULL] PROCESSLIST** 命令来查看哪些线程正在运行，及其查询状态，Command列显示了状态。
一些常见的状态：
| 状态 | 作用 |
|---|---|
| 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要求 |
4.2、查询缓存
在解析一个查询之前，如果开启了缓存，MySql会检查查询缓存，进行大小写敏感的哈希查找。即使查询和缓存中的查询只有一个字节的差异，也表示不匹配，查询就会进入下一步。
MySql查询缓存保留了查询返回给客户端的完整结果，当缓存命中的时候，服务器马上返回保存的结果(会先检查权限)，并跳过解析、优化和执行步骤。查询缓存保留了查询使用过的表，如果表发生了改变(如update)，那么缓存的数据就失效了。
4.3、查询优化处理
如果查询缓存中没有，下一步就是**将查询转变成执行计划**，包括**解析、预处理和优化**的过程。这个过程的任何一步都有可能出现错误，比如语法错误等。这里我们可以看到平时出现的大部分错误是从哪一步抛出来的。
**解析**：首先是解析器将查询分解成一个个标识，然后构造一颗“解析树”，解析器保证查询中的标识都是有效的，**会检查其中的基本错误**，比如字符串上面的引号没有闭合等。
预处理：然后预处理器检查解析器生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。最后，预处理器检查权限。
优化：之后，优化器把解析树变成执行计划。一个查询通常可以有很多种执行方式，并且返回同样的结果，优化器的任务就是找到最好的方式。
4.4、查询执行引擎
MySql查询执行引擎使用执行计划来处理查询。和优化部分相反，执行部分不会很复杂。**MySql按照执行计划的指令进行查询(执行计划时一个数据结构)。计划中的许多操作都是通过存储引擎提供的方法来完成的**。
4.5、返回结果给客户端
执行计划的最后一步是将结果发送到客户端，即使查询没有结果要返回，服务器也会对客户端的联接进行应答，比如有多少行受了影响。
如果查询是可缓存的，MySql会在这时缓存查询。
根据MySql的执行机制，一旦它处理了最后一个表并且成功地产生了一行输出，他就会把这个结果发送到客户端。这样的好处是，服务器不用把这一行保存在内存中，二是服务端可以尽快的开始工作。
5、MySQL查询优化器
MySql的优化器是相当复杂的，它使用了很多优化技巧把查询转换为执行计划。下面列出了MySql能处理的一些优化类型，以便我们去了解MySql优化器能够做的工作。
你也可以通过EXPLAIN EXTENDED SELECT ... ... ; SHOW WARNINGS; 查看最终优化后的执行sql。
5.1、关联子查询
描述:
因为select …from table1 t1 where t1.id in(select t2.fk from table2 t2 wheret2.id=’…’) 类型的语句往往会被优化成 select …. From table1 t1 where exists (select* from table2 t2 where t2.id=’…’ and t2.fk=t1.id), 由于在进行tabl2查询时，table1的值还无法确定， 所以会对table1进行全表扫描
解决方案:
尽量用 INNER JOIN 替代 IN()，重写成 select * from table1 t1 inner join table2 t2 using (id) where t2.id=’…’
5.2、UNION的限制
描述:
UNION操作不会把UNION外的操作推送到每个子集
解决方案:
为每个子操作单独的添加限制条件
例如 学生表有10000条记录,会员表有10000表记录，如果想按照姓名排序取两个表的前20条记录，如果在各个子查询中添加limit的话，则最外层的limit操作将会从40条记录中取20条,否则是从20000条中取20条
(select name from student order by name limit 20) union all (select name from memberorder by member limit 20) limit 20
5.3、等值传递
在进行查询操作的时候 IN,ON,Using,等操作往往会把一个列表的值在多个表之间共享，而优化器为了优化的方便会把列表里的值为每个相关表都拷贝一份,如果这个列表非常的大，会对性能造成一定的影响。
目前为止还没有好的策略应对这个问题
5.4、并行执行
目前為止,MYSQL不支持
5.5、哈希关联
目前MYSQL唯一支持的是循环嵌套关联,不支持HASH关联
5.6、 松散索引扫描
描述:
所谓的松散索引就是当对表进行扫描时，可以智能的跳过一些记录，以此来减少需要扫描的记录行数。为了更清楚的说明这个问题，举个例子来说明松散索引扫描的好处，例如table1表上有索引(a,b),执行 select * from table1 where b between 2 and 3时,支持/不支持松散扫描的表扫描方式分别如下
由于B列是按照顺序排列的,所以只需要在固定的区间内查找就可以了,其余的记录可以跳过
B不是索引的第一字段,所以只能从第一条找到最后一条
上面两个图可以很明显的说明松散索引的好处,但是Mysql对这个特性的支持不是很好,只针对某些特殊的查询才提供此优化,具体的要看各个版本的手册
5.7、Max()/MIN()
问题描述:
当执行 select max(id) from table1 where name=’sun’ 时,如果name没有建立相应的索引，MYSQL会进行全表扫描解决方案:
将SQL等同的转化为
select id from table1 use index(PRIMARY) wherename=’sun’ limit 1.
这样的语句会尽可能少的扫描表记录
5.8、同一个表的查询以及更新
问题描述:
不能在查询某个表的同时对表进行更新
Update table1t1 set t1.cnt=(select count(*) fromtable1)
否则会抛出异常: ERROR 1093 (HY000): You can'tspecify target table 'ftsexchangerate' for update in FROM clause
解决办法: 转化成关联表的形式
update ftsexchangerate
inner join(
select currency,count(*) as cnt from ftsexchangerate group by (currency) ) as innusing(currency)
set ftsexchangerate.description=inn.cnt ;
