简单谈谈MySQL的loose index scan

2024-07-24 12:45:15

字体：大中小

来源：转载

供稿：网友

众所周知，InnoDB采用IOT（index organization table）即所谓的索引组织表，而叶子节点也就存放了所有的数据，这就意味着，数据总是按照某种顺序存储的。所以问题来了，如果是这样一个语句，执行起来应该是怎么样的呢？语句如下：

select count(distinct a) from table1;

列a上有一个索引，那么按照简单的想法来讲，如何扫描呢？很简单，一条一条的扫描，这样一来，其实做了一次索引全扫描，效率很差。这种扫描方式会扫描到很多很多的重复的索引，这样说的话优化的办法也是很容易想到的：跳过重复的索引就可以了。于是网上能搜到这样的一个优化的办法：

select count(*) from (select distinct a from table1) t;

从已经搜索到的资料看，这样的执行计划中的extra就从using index变成了using index for group-by。

但是，但是，但是，好在我们现在已经没有使用5.1的版本了，大家基本上都是5.5以上了，这些现代版本，已经实现了loose index scan：

很好很好，就不需要再用这种奇技淫巧去优化SQL了。

文档里关于group by这里写的有点意思，说是最大众化的办法就是进行全表扫描并且创建一个临时表，这样执行计划就会难看的要命了，肯定有ALL和using temporary table了。

5.0之后group by在特定条件下可能使用到loose index scan，

CREATE TABLE log_table (id INT NOT NULL PRIMARY KEY,log_machine VARCHAR(20) NOT NULL,log_time DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);

SELECT MAX(log_time) FROM log_table；SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 1')；

这两条sql都只需一次index seek便可返回，源于索引的有序排序，优化器意识到min/max位于最左/右块，从而避免范围扫描；
extra显示Select tables optimized away ；
2

复制代码代码如下:

SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1','Machine 2','Machine 3','Machine 4');

执行计划type 为range(extra显示using where; using index)，即执行索引范围扫描，先读取所有满足log_machine约束的记录，然后对其遍历找出max value；
改进

复制代码代码如下:

SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1','Machine 2','Machine 3','Machine 4') group by log_machine order by 1 desc limit 1;

这满足group by选择loose index scan的要求，执行计划的extra显示using index for group-by，执行效果等值于

上一篇：在MySQL中创建实现自增的序列（Sequence）的教程

下一篇：在阿里云的CentOS环境中安装配置MySQL的教程