一.ICP 注意一下ICP的使用条件： 只能用于二级索引(secondary index)。 explain显示的执行计划中type值（join 类型）为range
一.ICP
注意一下ICP的使用条件：
只能用于二级索引(secondary index)。
explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。
二.order by .. limit …优化
from mysql 5.6.2 开始对order by .. limit …优化：
select col1,col2 from tx order by no_key_col limit offset,rowcount;
From 5.6.2 by treating the sort buffer as a priority queue:
Scan the table, inserting the select 本文来源gaodaima#com搞(代@码$网6list columns from each selected row in sorted order in the queue. If the queue is full, bump out the last row in the sort order.
Return the first N rows from the queue. (If M was specified, skip the first M rows and return the next N rows.)
Before 5.6.2，Previously, the server performed this operation by using a merge file for the sort:
Scan the table, repeating these steps through the end of the table:
Select rows until the sort buffer is filled.
Write the first N rows in the buffer (M+N rows if M was specified) to a merge file.
Sort the merge file and return the first N rows. (If M was specified, skip the first M rows and return the next N rows.)
The cost of the table scan is the same for the queue and merge-file methods, so the optimizer chooses between methods based on other costs:
The queue method involves more CPU for inserting rows into the queue in order
The merge-file method has I/O costs to write and read the file and CPU cost to sort it
算法：
5.6使用queue：
把select 的列放入队列里，当队列满了把队列最后的出队列，，就是把最大的、次大的、…..依次推到队列的前面，表所有行的列全部放完后，把第一个前rowcount 出队列，依次进行，都在sort buffer 里排序。
5.6之前：使用 merge file
merge file 再排序取出结果。
————————————–分割线 ————————————–
用mysqldump和mysqlbinlog的MySQL数据恢复实验
Ubuntu 14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二进制安装
————————————–分割线 ————————————–
本文永久更新链接地址：
转载请注明原文链接：MySQL 5.6 查询优化器改进
