大多数书的目录中会列出“章”和“节”,这便是组合索引。
在实际的数据库访问中,大多数的查询都包含组合条件,比如:
... where a = 1 and b = 2 ... where a = 1 order by b ... where a = 1 group by b
这个时候,即使字段 a 和字段 b 已经分别建立了索引,它们仍然不能同时发挥作用,因为一次查询对于一个数据表只能使用一个索引,它们是无法进行效用叠加的。这样一来,便会存在一定程度的局部行扫描(Range Scan),这在有些特定的场景中将严重影响查询性能,比如上述第一条查询,数据库会先利用字段a的索引快速匹配a=1的记录,然后在这些记录中帅选b=2的记录,而此时b字段的索引将爱莫能助,试想,如果a=1的匹配行非常多的话,查询时间将花在b字段的帅选操作上。
为了应付这样的查询,我们不得不使用组合索引。
我们来创建这样一个数据表:
CREATE TABLE `key_t` ( `id` int(11) NOT NULL, `key1` int(11) NOT NULL DEFAULT '0', `key2` int(11) NOT NULL DEFAULT '0', `key3` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `normal_key` (`key1`,`key2`,`key3`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
这个数据表建立了一个包含了三个字段的组合索引 normal_key,同时它还有一个自增类型的主键。我们为这个表填充了 100 万行的记录,其中 key1、key2、key3的内容均为0到999的随机整数。
SELECT COUNT(*) FROM `key_t`
我们知道以下几个查询都可以直接使用 normal_key 索引,而不需要任何的行扫描。
SELECT * FROM `key_t` WHERE key1=1 SELECT * FROM `key_t` WHERE key1=1 and key2=2 SELECT * FROM `key_t` WHERE key1=1 and key2=2 and key3=3
必要的时候,查询优化器还会帮你调整条件表达式的顺序,以匹配组合索引的要求,比如以下这个查询:
SELECT * FROM `key_t` WHERE key1=1 and key3=3 and key2=2
它会被查询优化器理解为:
SELECT * FROM `key_t` WHERE key1=1 and key2=2 and key3=3
优化器是很智能的。
其次,组合索引对于包含 order by 和 group by 的查询也发挥着重要的作用,它们同样也遵循最左前缀原则,我们看以下这个SQL语句的分析:
其中 type 为 index,表示这个查询只需要在索引中扫描即可,这里的索引即 normal_key 。也就是说,查询语句中 order by 指定的排序规则正好是索引本身的顺序,可以直接拿来派上用场,不需要重新排序。需要注意的是,有些非顺序的索引类型(如 Hash),对 order by 是无效的。
下面这个查询正是符合最左前缀的原则,它也使用了 normal_key 索引。
那么,再看看下面这个SQL语句:
虽然它用到了 normal_key 索引,但只是对 where 子句起作用,而后面的 order by 则需要排序计算,Using filesort 已经证明了这点。
对于包含 group by 的查询,数据库一般需要先将记录分组后放置在新的临时表中,然后分别对它们进行函数计算,比如 count()、sum() 或 max() 等。当有切当的索引存在时,group by 有时也可以使用索引来取代创建临时表,这当然是我们所希望的。以下这个 SQL 语句便利用了 normal_key 索引,避免了创建临时表。
而对于另外一些情况,组合索引就无法帮助 group by 了,比如以下的 SQL 语句:
的确,Using temporary 和 Using filesort 非常不受欢迎,它们越少越好。
组合索引的副作用
在有些情况下,组合索引对于一些查询会产生误导,你需要考虑是否应该阻止组合索引,或者预先设计更加适合的索引。同样针对刚才那个数据表,我们看以下这个查询:
根据最左前缀原则,以上的查询没有可使用的索引,索引要进行全表扫描,必然花费很长的时间。但是,并不是我们想象的那样,看下面的分析:
竟然使用了 normal_key 索引,而且 type 为 index ,表示这里进行了索引扫描,显然优化器认为在索引扫描中扫描要比在全表数据中扫描更加高效,但是,这次它的如意算盘打错了。仔细看,这 10 行记录是按照 key1 字段来顺序排列的,这说明查询是基于 normal_key 索引的扫描,而不是基于数据本身的扫描。Innodb 类型表中数据的存储顺序是按照主键来排列的。
我们在查询的尾部增加了 ORDER BY id ,结果如下所示:
用 EXPLAIN 分析如下:
由于我们指定了 order by id ,查询优化器聪明地放弃了 normal_key 索引,而使用主键进行扫描,这基本上相当于全表扫描。如果你希望结果仍然按照 key1 排序,这不是什么问题,你可以增加一个包含(key2,key1)字段的组合索引,注意它们的顺序,(key1,key2)索引和(key2,key1)索引完全不同,你必须根据需要来进行抉择,而优化器对此无能为力。
相关推荐
什么时候创建组合索引? 当我们的where查询存在多个条件查询的时候,我们需要对查询的列创建组合索引 为什么不对没一列创建索引 减少开销 覆盖索引 效率高 减少开销:假如对col1、col2、col3创建组合索引,相当...
3. 优化组合索引:根据查询条件的频率和业务需求,合理设计组合索引,确保最常使用的查询条件出现在索引的前面。 4. 考虑NULL值:在InnoDB中,NULL值在索引中占的空间比非NULL值少,但过多的NULL值可能导致索引碎片...
本实验主要探讨了如何通过EXPLAIN命令来分析SQL查询时索引的使用情况,特别是关于组合索引的“最左前缀”原则。实验的目的是帮助我们理解和优化MySQL查询。 首先,我们创建了一个名为`qz_users`的用户表,包含`uid`...
首先,复合索引(也称为组合索引)是针对多个列创建的索引,如场景1中的IDX_TEST_01,它同时基于SEGMENT_NAME和EXTENT_ID。当查询条件包含这两个字段时,复合索引可以有效地加速查询。因为数据库可以直接通过索引...
* 索引可以是单列索引,也可以是组合索引。 * 每个表可以有多个单列索引,但这不是组合索引。 二、索引的类型 * 普通索引:最基本的索引,无任何限制。可以使用CREATE INDEX语句、ALTER语句或CREATE TABLE语句创建...
位图索引将每个值映射为一个位,节省存储空间,尤其适合进行多列组合查询。然而,位图索引在处理大量插入、删除操作时效率较低,且不适合大范围的查询。 四、函数索引 函数索引允许对列的函数结果进行索引,比如...
但是,只有当查询条件中包含了组合索引的所有前缀列时,该索引才能被有效利用。例如,对于索引it1c1c2(c1,c2),查询select * from t1 where c1=1 and c2=2可以使用该索引,但查询select * from t1 where c2=2则不能...
主键可以是单个字段,也可以是多个字段的组合。 2. **唯一索引(Unique Index)**:与主键类似,保证索引中的每一项都是唯一的,但不强制成为主键。允许有NULL值。 3. **普通索引(Index)**:最基本的索引类型,...
此外,介绍了单字段索引、组合索引和覆盖索引的创建技巧,以及如何利用Profiler和ReadTrace等工具来识别和优化性能关键SQL语句,这对于任何需要对数据库性能进行调优的专业人士都具有重要的参考价值。
- 组合索引应确保覆盖常用查询的所有列,前导列应为使用频率最高的列。 4. **避免使用NULL**:NULL值不能被索引,因此在WHERE子句中使用`IS NULL`或`IS NOT NULL`会导致索引失效。 5. **IN与EXISTS**:使用`EXISTS`...
`INFORMATION_SCHEMA.STATISTICS`表中包含了关于MySQL中所有表的索引统计信息,包括索引名称、索引类型、索引列等。 #### 三、解读`INFORMATION_SCHEMA.STATISTICS`表 `INFORMATION_SCHEMA.STATISTICS`表包含了多...
根据“索引使用规则.txt”文件提供的信息,我们可以深入探讨一系列关于索引使用的最佳实践与限制条件,以下是对这些规则的详细解析: ### 1. 索引与逻辑运算符的兼容性 当在WHERE子句中使用`NOT`或`LIKE '%XX%'`这...
通常,对逻辑主键使用唯一组合索引,对系统键使用唯一非组合索引,对外键列使用非组合索引。然而,过度索引会导致存储空间浪费,因此需要权衡索引与数据库大小、读写操作的频率。 一些特定的字段不应被索引,例如...
关于数据仓库中编码位图索引的研究 在数据仓库环境中,查询处理是其主要特点之一。由于数据仓库中的大量数据和高读取/更新比率,使得传统的数据库系统中的查询方法和优化技术并不适合于数据仓库环境。因此,提高...
档案索引的标签“管理”表明这是关于信息管理和组织的实践。良好的档案管理能提高工作效率,减少寻找文件的时间,同时也有助于合规性,特别是对于那些需要遵守法规要求保存记录的企业或行业。 创建和维护一个有效的...
以下将详细介绍关于MySQL索引的知识点: 1. **索引类型** - **B-Tree索引**:最常见的索引类型,适用于等值查询和范围查询,包括主键和普通索引。 - **哈希索引**:用于等值查询,速度非常快,但不支持排序和范围...
单列索引不包含NULL,而复合索引则不存储所有元素都为NULL的组合。由于NULL值在计算中具有特殊性,不能参与大多数比较操作,因此在基于索引的查询中,对包含NULL值的列使用`IS NULL`条件会导致全表扫描。 2. **不...
在IT领域,尤其是在数据库管理和数据处理中,"两个表 关于树的组合"的主题涉及到层次结构数据模型的处理。这种模型常用于表示具有层级关系的数据,例如组织结构、目录树或者菜单系统。在这个场景中,我们有两个表,...
然而,Oracle 9i引入了跳跃式扫描,使得在某些情况下,即使不包含首列,也能利用组合索引。 ROWID是Oracle中访问单行数据的唯一标识,类似于行的物理地址。通过ROWID,可以直接定位到数据行,这在处理行级操作或...