mysql联合索引的使用规则

联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

从一道有趣的题目开始分析：

假设某个表有一个联合索引（c1,c2,c3,c4）以下选项哪些字段使用了该索引：
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=? and c5=? order by c2,c3
E where c1=? and c2=? and c5=? order by c2,c3

下面我们开始：

首先创建表：

CREATE TABLE t(
c1 CHAR(1) not null,
c2 CHAR(1) not null,
c3 CHAR(1) not null,
c4 CHAR(1) not null,
c5 CHAR(1) not null
)ENGINE myisam CHARSET UTF8;

有c1到c5 5个字段，特别说明一下 字段类型都是定长char（1）类型，并且非空，字符集是utf8（与计算索引使用字节数有关）

创建索引：

alter table t add index c1234(c1,c2,c3,c4);

插入2条数据：insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2')

使用MySql Explain开始分析题目结果：

A选项：

结果可以看出，c1,c2,c3,c4均使用到了该索引，而我们对A结果稍作更改：

将c2条件去掉后：

根据索引最左原则，c2字段没有使用索引，c2之后的字段都不能使用索引。下面2图我们对比下索引最左原则：

上图结果显示直接使用c3是全表查询，无法使用该索引的，所以c3字段使用索引的前提是c1，c2两字段均使用了索引。

即是索引的最左原则（左前缀原则）。

B选项：

key_len长度说明c1，c2字段用到了该索引，Extra显示并没有使用临时表进行排序，说明排序是使用了索引的，但并没有计算在key_len值中，也没有起到连接c4的作用，说明索引到c3这里是断掉的。

排序其实是利用联合索引直接完成了的，即：使用了c1234联合索引，就已经使得c1下c2，c2下c3，c3下c4是有序的了，所以实际是排序利用了索引，c3字段并没有使用该索引。（这段写的时候总感觉有点别扭，不知道我理解的对不对，还有待更深层次的研究）

C选项：

使用group by 一般先生成临时文件，再进行排序，但是字段顺序为c2，c3时，并没有用临时表进行排序，而是利用索引排序好的；当group by字段为c3，c2时，由于与索引字段顺序不一致，所以分组和排序并没有利用到索引。

由key_len长度确定，只有c1一个字段使用了索引。

D选项：

order by 和group by 类似，字段顺序与索引一致时，会使用索引排序；字段顺序与索引不一致时，不使用索引。

由key_len长度确定，只有c1一个字段使用了索引。

E选项：

其实选项E的结果分析在上述ABCD的结果中都分析过了，这里只有c1，c2字段使用了该索引。

综上所述问题答案：

A:四个字段均使用了该索引

B:c1，c2字段使用了该索引

C:c1字段使用该索引

D:c1字段使用该索引

E:c1，c2字段使用了该索引

总结：

索引的最左原则（左前缀原则），如（c1,c2,c3,c4....cN）的联合索引，where 条件按照索引建立的字段顺序来使用（不代表and条件必须按照顺序来写），如果中间某列没有条件，或使用like会导致后面的列不能使用索引。

索引也能用于分组和排序，分组要先排序，在计算平均值等等。所以在分组和排序中，如果字段顺序可以按照索引的字段顺序，即可利用索引的有序特性。

到此这篇关于mysql联合索引的使用规则的文章就介绍到这了,更多相关mysql联合索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL 独立索引和联合索引的选择

    MySQL 独立索引和联合索引的选择

    通常会对多列索引缺乏理解,常见的错误是将很多列设置独立索引,或者是索引列使用错误的次序.我们在下一篇讨论索引列次序的问题,首先看一下多列独立索引的情况,以下面的表结构为例: CREATE TABLE test ( c1 INT, c2 INT, c3 INT, KEY(c1), KEY(c2), KEY(c3), ); 使用这种索引策略通常是一些权威的建议(例如在WHERE条件中用到的条件列增加索引)的结果.事实上,这是大错特错的,要评分的话顶多给1颗星.这种方式的索引与真正优化的索引相比,要慢

  • MySQL中的联合索引学习教程

    联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引.复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏

  • MySQL联合索引功能与用法实例分析

    MySQL联合索引功能与用法实例分析

    本文实例讲述了MySQL联合索引功能与用法.分享给大家供大家参考,具体如下: 联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用

  • MySQL联合索引用法示例

    MySQL联合索引用法示例

    本文实例讲述了MySQL联合索引.分享给大家供大家参考,具体如下: 员工表 userid 部门表 deptid 员工部门表 条件:一个员工可以对应多个部门 问题:怎么样设置数据库,让其不能重复添加 userid 和deptid? uuid userid deptid 111 212 311(这个就不能让其添加) DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dep

  • mysql的联合索引(复合索引)的实现

    mysql的联合索引(复合索引)的实现

    联合索引 本文中联合索引的定义为(MySQL): ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`); 联合索引的优点 若多个一条SQL,需要多个用到两个条件 SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX'; 当索引在检索 password字段的时候,数据量大大缩小,索引的命中率减小,增大了索引的效率. 符合索引的索引体积比单独索引的体积要小,而且只是一

  • 深入浅析Mysql联合索引最左匹配原则

    前言 之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,最近面试时和面试官交流,发现遗漏了些东西,这里自己整理一下这方面的内容. 最左前缀匹配原则 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例: 对列col1.列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1,col2,col3); 联合索引 test_col1_col2_col3 实际建

  • mysql联合索引的使用规则

    mysql联合索引的使用规则

    联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 从一道有趣的题目开始分析: 假设某个表有一个联合索引(c1,c2,c3,c4)以下选项哪些字段使用了该索引: A where c1=x and c2=x and c4>x and c3=x B w

  • mysql 联合索引生效的条件及索引失效的条件

    mysql 联合索引生效的条件及索引失效的条件

    目录 1.联合索引失效的条件 2.索引失效的条件 1.联合索引失效的条件 联合索引又叫复合索引.两个或更多个列上的索引被称作复合索引. 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单

  • 总结三道MySQL联合索引面试题

    总结三道MySQL联合索引面试题

    目录 前言: 众所周知MySQL联合索引遵循最左前缀匹配原则,在少数情况下也会不遵循(有兴趣,可以翻一下上篇文章) 创建联合索引的时候,建议优先把区分度高的字段放在第一列. 至于怎么统计区分度,可以按照下面这种方式. 创建一张测试表,用来测试: CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `a` int NOT NULL, `b` int NOT NULL, `c` int NOT NULL, PRIM

  • MySQL联合索引遵循最左前缀匹配原则

    MySQL联合索引遵循最左前缀匹配原则

    面试官: 我看你的简历上写着精通MySQL,问你个简单的问题,MySQL联合索引有什么特性? 心想,这还不简单,这不是问到我手心里了吗? 听我给你背一遍八股文! 我: MySQL联合索引遵循最左前缀匹配原则,即最左优先,查询的时候会优先匹配最左边的索引. 例如当我们在 (a,b,c) 三个字段上创建联合索引时,实际上是创建了三个索引,分别是(a).(a,b).(a,b,c). 查询条件中包含这些索引的时候,查询就会用到索引.例如下面的查询条件,就可以用到索引: select * from tab

  • MySQL 联合索引与Where子句的优化 提高数据库运行效率

    网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了.涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火~~然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我确定了不是机器配置的问题,"深圳-枪手"热心人他的机器512内存过百W的数据里也跑得飞快,甚至跟那些几W块的机器一样牛(吹过头了),呵呵~~~ 在群友的分析指点下,尝试把排序.条件等一个一个去除来做测试,结果发现问题就出在排序部分,去除排序的时候,执行时间由原来的48秒变成0.3x秒,这是

  • MySQL中索引与视图的用法与区别详解

    前言 本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 索引 一.概述 所有的Mysql列类型都可以被索引. mysql支持BTREE索引.HASH索引.前缀索引.全文本索引(FULLTEXT)[只有MyISAM引擎支持,且仅限于char,varchar,text列].空间列索引[只有MyISAM引擎支持,且索引的字段必须非空],但不支持函数索引. MyISAM和InnoDB存储引擎的表默认创建BTREE索引,

随机推荐