MySQL 5.6 查询优化器改进-mysql教程-PHP中文网

MySQL 5.6 查询优化器改进

php中文网

发布： 2016-06-07 16:42:15

原创

1112人浏览过

一.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 list 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

HiShop网店代理分销系统

Hishop.5.2.BETA2版主要更新： [修改] 进一步优化了首页打开速度 [修改] 美化了默认模板 [修改] 优化系统架构,程序标签及SQL查询效率,访问系统页面的速度大大提高 [修改] 采用了HTML模板机制,实现了前台模板可视化编辑,降低模板制作与修改的难度. [修改] 全新更换前后台AJAX技术框架,提升了用户操作体验. 店铺管理 [新增] 整合TQ在线客服 [修改] 后台广告位增加