MySQL松散索引扫描与紧凑索引扫描

标签: mysql 索引 索引 | 发表时间:2015-08-30 23:35 | 作者:annan211
出处:http://www.iteye.com

在优化group by查询的时候,一般的会想到两个名词:松散索引扫描(Loose Index Scan)和紧凑索引扫描(Tight Index Scan),因为通过这两种索引扫描就可以高效快速弟完成group by操作。

在group by操作在没有合适的索引可用的时候,通常先扫描整个表提取数据并创建一个临时表,然后按照group by指定的列进行排序。在这个临时表里面,对于每一个group的数据行来说是连续在一起的。完成排序之后,就可以发现所有的groups,并可以执行聚集函数(aggregate function)。可以看到,在没有使用索引的时候,需要创建临时表和排序。

MySQL建立的索引(B+Tree)通常是有序的,如果通过读取索引就完成group by操作,那么就可避免创建临时表和排序。因而使用索引进行group by的最重要的前提条件是所有group by的参照列(分组依据的列)来自于同一个索引,且索引按照顺序存储所有的keys(即BTREE index,而HASH index没有顺序的概念)。

MySQ有两种索引扫描方式完成group by操作,就是上面提到的松散索引扫描和紧凑索引扫描。在松散索引扫描方式下,分组操作和范围预测(如果有的话)一起执行完成的。在紧凑索引扫描方式下,先对索引执行范围扫描(range scan),再对结果元组进行分组。

松散索引扫描(Loose Index Scan)

松散索引扫描相当于Oracle中的跳跃索引扫描(skip index scan),就是不需要连续的扫描索引中得每一个元组,扫描时仅考虑索引中得一部分。当查询中没有where条件的时候,松散索引扫描读取的索引元组的个数和groups的数量相同。如果where条件包含范围预测,松散索引扫描查找每个group中第一个满足范围条件,然后再读取最少可能数的keys。松散索引扫描只需要读取很少量的数据就可以完成group by操作,因而执行效率非常高。使用松散索引扫描需要满足以下条件:

1、查询在单一表上。

2、group by指定的所有列是索引的一个最左前缀,并且没有其它的列。比如表t1( c1,c2,c3,c4)上建立了索引(c1,c2,c3)。如果查询包含“group by c1,c2”,那么可以使用松散索引扫描。但是“group by c2,c3”(不是索引最左前缀)和“group by c1,c2,c4”(c4字段不在索引中)。

3、如果在选择列表select list中存在聚集函数,只能使用 min()和max()两个聚集函数,并且指定的是同一列(如果min()和max()同时存在)。这一列必须在索引中,且紧跟着group by指定的列。比如,select t1,t2,min(t3),max(t3) from t1  group by c1,c2。

4、如果查询中存在除了group by指定的列之外的其他部分,那么必须以常量的形式出现(除了min()和max()两个聚集函数)。

比如,select c1,c3 from t1 group by c1,c2不能使用松散索引扫描。而select c1,c3 from t1 where c3 =  3 group by c1,c2可以使用松散索引扫描。

5、索引中的列必须索引整个数据列的值(full column values must be indexed),而不是一个前缀索引。比如,c1 varchar(20), INDEX (c1(10)),这个索引没发用作松散索引扫描。

自从5.5开始,松散索引扫描可以作用于在select list中其它形式的聚集函数,除了min()和max()之外,还支持:

1、AVG(DISTINCT), SUM(DISTINCT)和COUNT(DISTINCT)可以使用松散索引扫描。AVG(DISTINCT), SUM(DISTINCT)只能使用单一列作为参数。而COUNT(DISTINCT)可以使用多列参数。

2、在查询中没有group by和distinct条件。

3、之前声明的松散扫描限制条件同样起作用。

紧凑索引扫描(Tight Index Scan)

紧凑索引扫描可能是全索引扫描或者范围索引扫描,取决于查询条件。当松散索引扫描条件没有满足的时候,group by仍然有可能避免创建临时表。如果在where条件有范围扫描,那么紧凑索引扫描仅读取满足这些条件的keys(索引元组)。否则执行索引扫描。因为这种方式读取所有where条件定义的范围内的keys,或者扫描整个索引当没有where条件,因而称作紧凑索引扫描。对于紧凑索引扫描,只有在所有满足范围条件的keys被找到之后才会执行分组操作。

如果紧凑索引扫描起作用,那么必须满足:在查询中存在常量相等where条件字段(索引中的字段),且该字段在group by指定的字段的前面或者中间。来自于相等条件的常量能够填充搜索keys中的gaps,因而可能构成一个索引的完整前缀。索引前缀能够用于索引查找。如果要求对group by的结果进行排序,并且查找字段有可能组成一个索引前缀,MySQL同样可以避免额外的排序操作,因为对有序的索引进行的查找已经按照顺序提取所有的keys。

c2在c1,c3之前,c2=‘a’填充这个坑,组成一个索引前缀,因而能够使用紧凑索引扫描。

select c1,c2,c3 from t1 where c2 = ‘a’ group by c1,c3

c1在索引的最前面,c1=a和group by c2,c3组成一个索引前缀,因而能够使用紧凑索引扫描。

select c1,c2,c3 from t1 where c1 = ‘a’ group by c2,c3

松散索引扫描个紧凑索引扫描的最大区别是是否需要扫描整个索引或者整个范围扫描。
引自 http://www.tuicool.com/articles/fIF3ey


已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql 索引 索引] 推荐:

ElasticSearch 索引 VS MySQL 索引

- - crossoverJie's Blog
这段时间在维护产品的搜索功能,每次在管理台看到 elasticsearch 这么高效的查询效率我都很好奇他是如何做到的. 这甚至比在我本地使用 MySQL 通过主键的查询速度还快. 这类问题网上很多答案,大概意思呢如下:. Lucene 的全文检索引擎,它会对数据进行分词后保存索引,擅长管理大量的索引数据,相对于.

[MySQL] B+树索引

- - CSDN博客推荐文章
B+树是一种经典的数据结构,由平衡树和二叉查找树结合产生,它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树,在B+树中,所有的记录节点都是按键值大小顺序存放在同一层的叶节点中,叶节点间用指针相连,构成双向循环链表,非叶节点(根节点、枝节点)只存放键值,不存放实际数据. 保持树平衡主要是为了提高查询性能,但为了维护树的平衡,成本也是巨大的,当有数据插入或删除时,需采用拆分节点、左旋、右旋等方法.

mysql 索引技巧

- - 小彰
MySQL索引的建立对于MySQL的高效运行是很重要的. 下面介绍几种常见的MySQL索引类型. 在数据库表中,对字段建立索引可以大大提高查询速度. 假如我们创建了一个 mytable表:. CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );   我们随机向里面插入了10000条记录,其中有一条:5555, admin.

mysql选择索引

- - CSDN博客数据库推荐文章
1、尽量为用来搜索、分类或分组的数据列编制索引,不要为作为输出显示的数据列编制索引. 最适合有索引的数据列是那些在where子句中数据列,在联结子句中出现的数据列,或者是在Group by 、Order by子句中出现的数据列. select 后的数据列最好不要用索引. 2、综合考虑各数据列的维度.

mysql 索引详解

- - 行业应用 - ITeye博客
本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题. 特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等. 为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论.

mysql索引认识

- - 数据库 - ITeye博客
数据在磁盘中是以 “块”的形式存储的,所以一张表涉及的数据可能会存在多个块中,而在磁盘中查询数据则会根据字段是否为有序与无序来区分,. 无序情况:1.数值具有唯一性则需要查找 总块数/2.                   2.无序+无唯一性则需要查找  总块数. 有序情况:1.数值唯一性:log2(总块数/2)   (log2是二分查找算法).

MySQL 索引方式

- - zzm
本文配图来自《高性能MySQL(第二版)》. 在数据库中,对性能影响最大的几个策略包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略. 索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率. MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略,本文将分析它们的异同和性能.

MySql索引总结

- - 掘金后端
MySQL 索引底层数据结构.   Mysql索引使用的数据结构主要有 BTree索引 和 Hash索引. 对于Hash索引来说,底层数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,使用Hash索引查询性能最快. 其余大多数场景建议使用BTree索引. 为什么索引能够提高查询速度.

Mysql-innodb-B+索引

- - 掘金后端
这是读书笔记,Mysql,innodb系列一共3篇. Mysql-innodb-B+索引(本篇). Mysql-innodb-锁(预计20200523). Mysql-innodb-事务预计20200530). CREATE TABLE `aid_***_detail` ( //省略所有字段 PRIMARY KEY (`id`), KEY `range_idx` (`range_id`,`is_delete`,`range_detail_num`,`goods_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4复制代码.

MySQL B+树索引及索引优化

- - 数据库 - ITeye博客
    MySQL的索引实现由很多种实现,包括hash索引,B+索引,全文索引等,本文只讨论B+树索引. 1.评价一个索引好坏主要看IO的访问次数,B+树红黑树来说,树高很小(出度很大)即可以有效降低IO的访问次数. B+数的高度h=logd(n),d越大,h越小,查询效率越高. 相对B树,B+树d可以很大,因为非叶子节点不存储数据,只存储key,在一个存储页上可以存储更多的key值.