一、 Mysql 5.6 对索引的优化之索引下推
“索引条件下推”，称为 Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。
“目的”，一是说明减少完整记录（一条完整元组）读取的个数；二是说明对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚集索引有效。
MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';可以将其关闭。
官方文档中给的例子和解释如下：people表中（zipcode，lastname，firstname）构成一个索引SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。
如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。
使用
（1）ICP只能用于辅助索引，不能用于聚集索引。
（2）ICP只用于单表，不是多表连接是的连接条件部分（如开篇强调）
（3）EQ_REF/REF_OR_NULL/REF/SYSTEM/CONST: 可以使用ICP
（4）range：如果不是“index tree only（只读索引）”，则有机会使用ICP
（5）ALL/FT/INDEX_MERGE/INDEX_SCAN: 不可以使用ICP
先来看一下mysql的查询过程。
二、查询优化器
Mysql查询优化器 一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下：1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个。
种类
（1）RBO :RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划（比如在RBO里面，有这么一条规则：有索引使用索引。那么所有带有索引的表在任何情况下都会走索引）所以，RBO现在被很多数据库抛弃（oracle默认是CBO，但是仍然保留RBO代码，MySQL只有CBO）
（2）CBO :CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO，CPU等消耗。也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息计算出来的。(计算过程比较复杂)查询生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。
（1）语法解析器和预处理首先MySQL通过关键字将SQL语句进行解析，并生成一棵解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。例如是否使用错误的关键字，或者使用关键字的顺序是否正确，引号是否能前后正确匹配等。
（2）预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名看它们是否有歧义。
（3）一下步预处理会验证权限。一条语句 可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。成本的最小单位是随机读取一个4K的数据页的成本，并加入一些因子来估算某引动操作的代价。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。
这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。
当然很多原因会导致MySQL优化器选择错误的执行计划：例如统计信息不准确或执行计划中的成本估算不等同于实际执行的成本。
可通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本，如下：
select count(*) from test;
-----------------
|count(*) | 4 |
-----------------
show status like 'Last_query_cost';
-------------------------------
| variable_name | Value |
-------------------------------
| Last_query_cost | 1.79900 |
-------------------------------上述语句预测了此count（*）操作大概需要做1.8个数据页的随机查找才能完成。
处理的优化类型
① 重新定义关联表的顺序；② 将外连接转化为内连接；
③ 使用等价变化规则；可以合并和减少一些比较，还可以移除一些恒成立和恒不成立的判断。
④ 优化count()、min()和max()；索引和列是否可为空通常可以帮助MySQL优化这类的表达式，如查找最小值，只需找到索引树最左边的第一条记录。
⑤ 预估并转化为常数表达式；当MySQL检测到一个表达式可以转化为常数时，就会一直把该表达式作为常数进行优化处理。
⑥ 覆盖索引扫描；当扫描的索引列包含所有查询中需要的使用的列时，MySQL就可以直接使用索引返回需要的数据。
⑦ 子查询优化；
⑧ 提前终止查询；如使用limit子句查找限制数量的数据。
⑨ 等值传播；如果两个列的值通过等式关联，那么MySQL能够将其中一个列的where条件传递到另一个列上。
⑩ 列表in()的比较；MySQL对in()列表进行优化，先对列表中的值进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。如何执行关联查询
MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到一个表中寻找匹配的行，依次下去直到找到的有匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。（嵌套循环关联）执行计划
MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED，再执行SHOW WARNINGS，就可以看到重构出的查询。
不过，如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”。实际上，当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式。排序优化
无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果需要排序的数据量小于排序缓冲区，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序的块进行合并，最手返回排序结果。
MySQL有两种排序方法：
（1）两次传输排序（旧版），读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。显然是两次传输，特别是读取排序后的数据时（第二次）大量随机I/O，所以两次传输成本高。
（2）单次传输排序（新版），一次读取出所有需要的或SQL查询指定的列，然后根据排序列，排序，直接返回排序后的结果。顺序I/O，缺点：如果列多，额外占用空间。
MySQL在进行文件排序时需要使用的临时存储空间可能会比想象的要大得多，因为MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够以容纳其中最长的字符串。
在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表时就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有Using filesort。除此之外的所有情况，MySQL都会将关联的结果存放在一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下Extra字段可以看到Using temporary;Using filesort。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。查询执行引擎
相对于查询优化，查询执行简单些了，MySQL只根据执行计划输出的指令逐步执行。指令都是调用存储引擎的API来完成，一般称为 handler API，实际上，MySQL优化阶段为每个表都创建了一个 handler 实例，用 handler 实例获取表的相关信息（列名、索引统计信息等）。
存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像搭积木一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条件的下一条目的功能，有了这两个功能就可以完成全索引扫描操作。返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，例如该查询影响到的行数。MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。这样处理有两个好处：服务端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。
优化特定类型的查询
①优化COUNT（）查询 count（*）：统计行数，比统计一般的列值个数要快很多。
②简单的优化：通过修改条件语句，减少扫描的次数。（始终记住，计算count(*)是很快的，比计算所有带条件的统计都要快）
③使用近似值：即count()结果可以用一个优化器估算出来的值代替。
④优化关联查询
1.确保ON或者USING子句中的列上有索引，一般索引建立在最后个关联表上的相应列上。
2.确保任何时候的GROUP BY 和 ORDER BY 中的表达式只涉及到一个表上的列，这样MySQL才有可能使用索引来优化这个过程。⑤优化子查询 尽可能使用关联查询代替子查询
三、查询缓存
mysql Query Cache和Oracle Query Cache 是不同的，oracle Query Cache 是缓存执行计划的，而MySql Query Cache 不缓存执行计划而是整个结果集。缓存整个结果集的好处不言而喻，但由于缓存的是结果集因此Query必须是完全一样的，这样带来的后果就是平均 Hit Rate 命中率一般不会太高。Query Cache 对于一些小型应用程序或者数据表的数据量不大的情况下效果是最为明显的
mysql Query Cache管理
① query cache开关 可以通过query_cache_type来控制缓存的开关，query_cache_type的状态值有如下几种：
0(OFF)：代表不使用缓冲；
1(ON)：代表使用缓冲；
2(DEMAND)：代表根据需要使用；② query_cache_size
1）默认情况下query_cache_size为0，表示为查询缓存预留的内存为0，则无法使用查询缓存。所以我们需要设置query_cache_size的值：SET GLOBAL query_cache_size = 134217728;
2）注意上面的值如果设得太小不会生效。比如我用下面的SQL设置query_cache_size大小： SET GLOBAL query_cache_size = 4000;
3）默认情况下query_cache_size为0，表示为查询缓存预留的内存为0，则无法使用查询缓存。这个值必须是1024的整数倍。否则，mysql实际分配的数据会和你指定的不同。所以我们需要设置query_cache_size的值： SET GLOBAL query_cache_size = 134217728;
4）注意上面的值如果设得太小不会生效。比如我用下面的SQL设置query_cache_size大小： SET GLOBAL query_cache_size = 4000;
5）query_cache_limitmysql能够缓存的最大查询结果。如果查询结果大于这个值，则不会被缓存。缺省为1M。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以只有当结果全部返回后，mysql才知道查询结果是否超出限制。
如果超出，mysql则增加状态值Qcache_not_cached，并将结果从查询缓存中删除。
如果你事先知道有很多这样的情况发生，那么建议在查询语句中加入SQL_NO_CACHE来避免查询缓存带来的额外消耗。
6）query_cache_wlock_invalidate
如果某个数据表被其他的连接锁住，是否还要从查询缓存中返回结果。这个参数默认是OFF，这可能在一定程度上回改变服务器的行为，因为这使得数据库可能返回其他线程锁住的数据。
如果设置为NO，则不会从缓存中读数据，但是这可能会增加锁等待。
7）query_cache_min_res_unit
是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。
③ SHOW WARNINGS;mysql query cache规则
① 缓存条件（规则）需要注意的是mysql query cache 是对大小写敏感的，因为Query Cache 在内存中是以 HASH 结构来进行映射，HASH 算法基础就是组成 SQL 语句的字符，所以 任何sql语句的改变重新cache,这也是项目开发中要建立sql语句书写规范的原因吧。
a) mysql query cache内容为 select 的结果集, cache 使用完整的 sql 字符串做 key, 并区分大小写，空格等。即两个sql必须完全一致才会导致cache命中b) prepared statement永远不会cache到结果，即使参数完全一样。据说在 5.1 之后会得到改善。
c) where条件中如包含了某些函数永远不会被cache, 比如current_date, now等。
d) date 之类的函数如果返回是以小时或天级别的，最好先算出来再传进去。
select * from foo where date1=current_date -- 不会被 cache
select * from foo where date1='2008-12-30' -- 被cache, 正确的做法e) 太大的result set不会被cache (< query_cache_limit)
② 缓存数据何时失效（invalidate）
在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。
a) 一旦表数据进行任何一行的修改，基于该表相关cache立即全部失效。
b) 为什么不做聪明一点判断修改的是否cache的内容？因为分析cache内容太复杂，服务器需要追求最大的性能。
可以使用下面三个SQL来清理查询缓存：
1、FLUSH QUERY CACHE; // 清理查询缓存内存碎片。
2、RESET QUERY CACHE; // 从查询缓存中移出所有查询。
3、FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容。③ 性能
a) cache 未必所有场合总是会改善性能
当有大量的查询和大量的修改时，cache机制可能会造成性能下降。因为每次修改会导致系统去做cache失效操作，造成不小开销。另外系统cache的访问由一个单一的全局锁来控制，这时候大量>的查询将被阻塞，直至锁释放。所以不要简单认为设置cache必定会带来性能提升。
b) 大result set不会被cache的开销
太大的result set不会被cache, 但mysql预先不知道result set的长度，所以只能等到reset set在cache添加到临界值 query_cache_limit 之后才会简单的把这个cache 丢弃。这并不是一个高效的操作。如果mysql status中Qcache_not_cached太大的话, 则可对潜在的大结果集的sql显式添加 SQL_NO_CACHE 的控制。
query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
④ 内存池使用
mysql query cache 使用内存池技术，自己管理内存释放和分配，而不是通过操作系统。内存池使用的基本单位是变长的block, 一个result set的cache通过链表把这些block串起来。因为存放result set的时候并不知道这个resultset最终有多大。block最短长度为 query_cache_min_res_unit, resultset 的最后一个block会执行trim操作。
Query Cache 在提高数据库性能方面具有非常重要的作用。
其设定也非常简单，仅需要在配置文件写入两行：query_cache_type 和 query_cache _size，而且 MySQL 的 query cache 非常快！而且一旦命中，就直接发送给客户端，节约大量的 CPU 时间。
当然，非 SELECT 语句对缓冲是有影响的，它们可能使缓冲中的数据过期。一个 UPDATE 语句引起的部分表修改，将导致对该表所有的缓冲数据失效，这是 MySQL 为了平衡性能而没有采取的措施。因为，如果每次 UPDATE 需要检查修改的数据，然后撤出部分缓冲将导致代码的复杂度增加。示例说明
① 如果query_cache_type为1而又不想利用查询缓存中的数据
SELECT SQL_NO_CACHE * FROM my_table WHERE condition;
② 如果值为2，但想要使用缓存
SELECT SQL_CACHE * FROM my_table WHERE condition;
感谢阅读！
欢迎大家关注公众号“isevena”
