`
annan211
  • 浏览: 461208 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

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

阅读更多

在优化group by查询的时候,一般的会想到两个名词:松散索引扫描(Loose Index Scan)和紧凑索引扫描(Tight Index Scan),因为通过这两种索引扫描就可以高效快速弟完成group by操作。
  请尊重知识,请尊重原创 更多资料参考请见  http://www.cezuwang.com/listFilm?page=1&areaId=906&filmTypeId=1
在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.cezuwang.com/listFilm?page=1&areaId=906&filmTypeId=1
松散索引扫描个紧凑索引扫描的最大区别是是否需要扫描整个索引或者整个范围扫描。
引自 http://www.tuicool.com/articles/fIF3ey

 

分享到:
评论

相关推荐

    MySQL优化GROUP BY(松散索引扫描与紧凑索引扫描)

    紧凑索引扫描与松散索引扫描类似,但更严格。在这种情况下,MySQL不仅使用索引的最左前缀,而且GROUP BY的列必须完全匹配索引的前缀。这意味着WHERE子句通常需要包含所有GROUP BY的列,且没有额外的常量条件。 例如...

    mysql 松散的索引扫描(Loose index scan)

    在上述查询的EXPLAIN结果中,我们可以看到`Using index for group-by`,这意味着MySQL将使用松散索引扫描来执行此查询。 **松散索引扫描的适用条件:** 1. **单表查询**:查询仅涉及一个表。 2. **GROUP BY与索引...

    mysql-常见问题,索引优化

    17. 松散索引和紧凑索引 松散索引(也称为非聚簇索引)不包含主键数据,只存储索引列,需要回表查找完整记录。而紧凑索引(聚簇索引)将数据和索引存储在一起,查询效率更高,但插入和删除操作相对较慢。选择哪种...

    高性能MySQL(第3版).part2

    6.5.7松散索引扫描229 6.5.8最大值和最小值优化231 6.5.9在同一个表上查询和更新232 6.6查询优化器的提示(hint)232 6.7优化特定类型的查询236 6.7.1优化COUNT()查询236 6.7.2优化关联查询239 6.7.3优化子...

    MySQL DISTINCT 的基本实现原理详解

    在某些情况下,MySQL可以使用松散索引扫描来执行DISTINCT操作。例如,当查询只包含DISTINCT关键字和一个被索引的列时,MySQL会遍历索引,对每个不同值形成一个组,然后返回每个组的第一个记录。在EXPLAIN计划中,`...

    MySQL中distinct语句的基本原理及其与group by的比较

    如果查询涉及到的列有合适的索引,MySQL可以使用松散索引扫描(loose index scan)或紧凑索引扫描(tight index scan)来执行`DISTINCT`。松散索引扫描适用于不涉及其他条件的情况,它会遍历索引中的所有唯一值并...

    MySQL优化GROUP BY方案

    当松散索引扫描不适用时,MySQL可能会使用紧凑索引扫描。这种优化适用于以下场景: 1. **GROUP BY子句不满足最左前缀规则**,但WHERE子句有范围条件,使得MySQL仅需读取部分索引。 2. **WHERE子句有等于常量的等式...

    详解MySQL InnoDB的索引扩展

    实际上,InnoDB使用扩展的二级索引来进行ref、range和index_merge索引访问、松散索引扫描、连接和排序优化以及MIN()和MAX()优化。 从示例中可以更清楚地看到索引扩展的作用。在创建表t1时,我们定义了两列i1和i2...

    MySQL分组查询Group By实现原理详解

    由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数... 何谓松散索引扫描实现 G

    数据库优化综合总结

    6. **松散索引扫描:** - `GROUP BY`条件字段必须在同一索引中最前面的连续位置。 - 使用`MAX`和`MIN`这样的聚合函数。 #### 四、SQL范例 1. **避免循环,善用SQL语句:** - 举例说明如何通过编写高效的SQL查询...

    学生成绩管理系统(SSH+MYSQL)-project

    【学生成绩管理系统(SSH+MYSQL)-project】是一个基于SSH(Spring、Struts2和Hibernate)框架和MySQL数据库的Web应用程序。这个项目旨在提供一个高效、便捷的平台,用于管理和跟踪学生的学术成绩。SSH框架是Java开发...

    基于ssm+mysql的新闻发布及管理系统源码数据库.zip

    9. **优化与扩展**:系统可进一步优化性能,例如缓存策略、数据库索引设计等。同时,系统设计应具有良好的扩展性,便于添加新的功能或对接其他服务。 综上所述,基于SSM+MySQL的新闻发布及管理系统是一个全面实践...

    基于ssm+mysql的新疆旅游管理系统源码数据库.zip

    1. **Spring框架**:Spring是Java应用程序的核心框架,它提供了一个全面的编程和配置模型,用于创建现代、松散耦合的Java应用。在本系统中,Spring可能被用作依赖注入(DI)容器,管理对象的生命周期和依赖关系,...

    mongodb资料,快速上手

    MongoDB 的体系结构与 MySQL 有所不同。 MongoDB 中的记录是一个文档,它是一个由字段和值对(field:value)组成的数据结构。 MongoDB 文档类似于 JSON 对象,即一个文档认为就是一个对象。 数据模型 MongoDB 的...

    mongodb部署配置及应用

    与传统的关系型数据库相比,MongoDB以更为松散的数据结构提供灵活性,并且使用BSON格式存储数据,支持内嵌文档和数组对象等复杂数据类型。 ### 3. 特点和体系结构 MongoDB作为NoSQL数据库,它不同于关系型数据库的...

Global site tag (gtag.js) - Google Analytics