不是说,一条sql语句只能用一个索引么?但如下这样一条sql语句
SELECT * FROM `comment` WHERE `toconuid` = '10' or `tocomuid` = '10'
其中toconuid列和tocomuid列分别为单列索引,explain后显示两个索引都用了,extra为 Using union(toconuid,tocomuid); Using where
在这里,可以理解MYSQL把这个语句拆成了两条语句
SELECT * FROM `comment` WHERE `toconuid` = '10' union SELECT * FROM `comment` WHERE `tocomuid` = '10'
一.索引合并优化
索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。
在EXPLAIN输出中,该方法表现为type列内的index_merge。在这种情况下,key列包含一列使用的索引,key_len包含这些索引的最长的关键元素。
例如:
SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20; SELECT * FROM tbl_name WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30; SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
二.索引合并方法有几种访问算法
参见EXPLAIN输出的Extra字段,有交集、联合、排序并集三种索引合并访问算法。
注意:索引合并优化算法具有以下几个已知缺陷:
1).如果可以对某些关键字进行范围扫描,则不考虑索引合并。例如,下面的查询:
SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
对于该查询,可以有两个方案:
a.使用(goodkey1 < 10 OR goodkey2 < 20)条件进行索引合并扫描。
b.使用badkey < 30条件进行范围扫描。
然而,优化器只考虑第2个方案。如果这不是你想要的,你可以通过使用IGNORE INDEX或FORCE INDEX让优化器考虑index_merge。下面的查询使用索引合并执行:
SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
SELECT * FROM t1 IGNORE INDEX(badkey) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
2).如果查询有一个复杂的WHERE子句,有较深的AND/OR嵌套关系,MySQL不选择该优选方案,通过下面的识别法则尝试分布各条件:
a.(x AND y) OR z = (x OR z) AND (y OR z)
b.(x OR y) AND z = (x AND z) OR (y AND z)
index_merge访问方法的不同变量之间的选择和其它访问方法基于各适用选项的成本估计。
1.索引合并交集访问算法
该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件,每个条件为下面之一:
以这种形式,即索引有确切的N部分(即包括了所有索引部分):
a.key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
b.任何InnoDB或BDB表的主键的范围条件。
下面是一些例子:
SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20; SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;
索引合并交集算法同时对所有使用的索引进行扫描,并产生从合并的索引扫描接收的行序列的交集。
如果使用的索引包括查询中使用的所有列,所有表记录均不搜索,并且在这种情况下EXPLAIN的输出包含Extra字段中的Using index。下面是一个此类查询的例子:
SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;
如果使用的索引未包括查询中使用的所有列,只有满足所有使用的关键字的范围条件才搜索所有记录。
如果某个合并条件是InnoDB或BDB表的主键的一个条件,不用于记录查询,但用于过滤使用其它条件搜索的记录。
2.索引合并并集访问算法
该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候,每个条件为下面之一:
以这种形式,即索引有确切的N部分(即包括了所有索引部分):
1).key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
2).任何InnoDB或BDB表的主键的范围条件。
3).索引合并方法交集算法适用的一个条件。
下面是一些例子:
SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3; SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR (key3='foo' AND key4='bar') AND key5=5;
3.索引合并排序并集访问算法
该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件,但索引合并方法联合算法并不适用的时候。
下面是一些例子:
SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;
相关推荐
一:union all 肯定是能够命中索引的 二:简单的in能够命中索引 三:对于or,新版的MySQL能够命中索引 四、对于!=,负向查询肯定不能命中索引 五、其他方案
如果查询语句使用索引(通常是where条件匹配索引)就会利用树的结构加快查找,索引会按值查找到要查找的行在表中位置,不需回表查询数据的就是聚簇索引(索引和数据存放在一起)。通常是需要回表再查数据,需要消耗...
MySQL Innodb 索引原理详解
mysql查询,通过explain 分析,没有利用到索引,查询效率不高等出现的问题。
MySQL查询优化技术_索引
介绍mysql不同的存储引擎 以及 索引技术在mysql中的应用
MySQL索引 聚集索引 如果你想了解MySQL索引查询优化,你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引,次要索引有一定的了解,才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引。
数据索引 索引概述 课程目标 了解 ——索引的分类。 理解 ——索引的概念及作用; 索引概述 索引的概念 索引是一个单独的、物理的数据库结构,是某个表中一列或者...如果作为搜索条件的列上已经创建了索引,MySQL无需
Mysql中创建各种索引的语句整理 代码 添加PRIMARY KEY(主键索引) 添加UNIQUE(唯一索引) 添加INDEX(普通索引) 添加FULLTEXT(全文索引) 添加多列索引 ) mysql>ALTER TABLE `...
在面试过程中,常常会涉及到MySQL索引的相关问题,包括索引的原理、优化技巧以及索引失效的原因等。 首先,MySQL索引是一种数据结构,用于快速定位和访问数据库中的数据。它通过创建索引列和索引对象来实现,可以...
包含mysql简介、索引介绍、索引引用策略、索引失效场景等
MySQL 索引最佳实践很不错,虽然是英文版
MySQL之高效覆盖索引
自己整理有关MySQL的索引失效相关的信息,重点在组合索引的失效,有兴趣可以看看,看看是否对你有些许帮助
mysql索引导出删除
《MySQL索引原理及如何建立高效索引.pptx》主要讲述mysql数据库索引底层原理、作用、 索引使用、索引失效等核心技术点。非常实用!!!
MySQl索引优化课件,详细得介绍了MySQl索引优化数据库
oR-Tree索引: 用来对GIS数据类型创建SPATIAL索引 物理存储维度 o聚集索引:聚集索引就是以主键创建的索引,在叶子节点存储的是表中的数据。 o非聚集索引:非聚集索引就是以非主键创建的索引,在叶子节点存储的是主键...