如果查询的字段即在联合索引，又在单列索引，先走哪个,为什么？

于 2024-03-07 22:31:46 发布
阅读量764 收藏 20
点赞数 24
文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Main123464644/article/details/136547733
版权

首先，回答问题之前，我们先来了解一下索引。

为了提高数据库的查询效率，建索引是最常用的手段。那么问题来了，如果查询条件为2个及以上，我们是创建多个单列索引好呢，还是建一个联合索引比较好？

我们首先来建一张表，

 3.插入a、b、c联合索引
ALTER TABLE `t_demo` ADD INDEX `INDEX_A_B_C` ( `a`, `b`, `c` ) USING BTREE;

接下来我们使用Explain查询来查看接下来的SQL查询语句的执行计划，这次我们只关注key和key_len列。其中key列显示MySQL实际决定使用的索引，如果没有使用到索引则显示NULL。key_len列显示索引中使用的字节数。

1.WHERE条件是a、b、c三个，查询abc所有排列组合情况：

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND b = "Or" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set (0.01 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND c = "tGMvk" AND b = "Or";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set (0.01 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND a = "8166" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set (0.02 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND c = "tGMvk" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set (0.02 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND a = "8166" AND b = "Or";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set (0.03 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND b = "Or" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set (0.03 sec)

2.WHERE条件是a、b、c选两个，查询abc两个中所有排列组合情况：

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND b = "Or";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 96      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
1 row in set (0.01 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 96      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
1 row in set (0.02 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   13 |    10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set (0.02 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   13 |    10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set (0.02 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |     1.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.03 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND b = "Or";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |     1.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.03 sec)

3.WHERE条件是a、b、c其中一个的情况：

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   13 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set (0.02 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.02 sec)
 
mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.02 sec)

4.结果分析


查询条件是a、b、c时，无论是什么顺序，由于优化器优化，都会走INDEX_A_B_C联合索引；
查询条件是a、b时，会走联合索引；
查询条件是a、c时，也会走联合索引，但是Extra信息里面多了一行：Using index condition，意思是先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用WHERE子句中的其他条件去过滤这些数据行，这种情况只有a条件用到联合索引，c条件回表到聚簇索引过滤。
查询条件是b、c时，不走联合索引；
查询条件是a时，会走联合索引；
查询条件是b时，不走联合索引；
查询条件是c时，不走联合索引；

5.总结
联合索引符合最左匹配原则，按照索引建的顺序，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如：以a、b、c为顺序建的联合索引，条件为下列情况都能生效：

WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?
注意：与WHERE后面的条件顺序无关，优化器会将条件顺序优化成上面三种情况后执行。

另外 WHERE a = ? AND c = ? 也会走联合索引，但是只有a条件命中，c条件不走联合索引。

还有，需要避免索引失效的情况，如：LIKE %xxx，或者条件中使用函数等。

所以该题的答案：你明白了吗，如果在查询条件最左侧的走联合索引，查询不在最左侧的会走单列索引。

还未翻身的鱼
关注
  • 24
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL数据库基础知识12，MySQL单列索引和联合索引
学Java，找哪吒
09-02 7974
多个单列索引在多条件查询时优化器会优先选择最优索引策略，可能只用一个索引，也可能将多个索引全用上。但多个单列索引底层会创建多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引。
MySQL进阶-单列索引与联合索引
abc123mma的博客
11-12 1192
建立联合索引还是单列索引呢？
mysql 主键索引，组合索引，单列索引使用场景
java1473727528的博客
06-30 1422
mysql 主键索引，组合索引，单列索引使用场景 CREATE TABLE button ( id bigint(20) NOT NULL AUTO_INCREMENT, --主键索引 button_name varchar(45) NOT NULL COMMENT ‘功能名称’, app_id bigint(20) NOT NULL, permission_id bigint(20) DEFAULT NULL, – permission_id 和 app_id 联合索引。 api_id bigint(
MySQL索引的工作原理
天堂的博客
04-24 455
MySQL中索引是如何工作的？
Mysql面试之索引
Destiny_shine的博客
01-14 5048
一、啥是索引 一种高效获取数据的数据结构。通俗的说，数据库索引像一本书的目录，能加快数据的查询速度。 一般来说索引本身很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。 通常所说的索引，包括聚集索引、组合索引、唯一索引等等，默认都是使用 B+ 树结构组织的。   二、索引有啥优点和缺点 优点： 提高数据检索的效率，降低数据库的 IO 成本。 通过索引列对数据进行排序，降低了数据排序成本，降低 CPU 消耗。 （1）被
深入浅析Mysql联合索引最左匹配原则
09-09
在有1000万条记录的表中，如果有查询`SELECT * FROM table WHERE col1=1 AND col2=2 AND col3=3`，如果每个条件能过滤掉90%的数据，联合索引(col1,col2,col3)可以一次过滤掉99%的数据，而单列索引则需要多次回表操作...
MySQL-数据库-索引详解
08-30
单列索引可以是普通索引也可以是惟一性索引、全文索引，只要保证索引只对应一个字段即可。 #### 多列索引 在表的多个字段创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。只有查询...
MySQL中NULL对索引的影响深入讲解
12-16
但在本地试了下，null列是可以用到索引的，不管是单列索引还是联合索引，但仅限于is null，is not null是不走索引的。 后来在官方文档中找到了说明，如果某列字段中包含null，确实是可以使用索引的，地址：...
Mysql 索引结构直观图解介绍
09-09
当我们为字段a创建单列索引时，一个新的数据结构（通常是B+Tree）会被创建，按照字段a的值进行排序。此时，索引结构的叶子节点指向数据行的实际位置，而不是按照id排序。 接着，创建联合索引(a, b)时，数据排序首先...
MySQL 通过索引优化含ORDER BY的语句
09-11
- 对于`ORDER BY [sort] LIMIT [offset],[LIMIT]`，在`sort`列上建立单列索引可有效加速。 4. **`WHERE + ORDER BY + LIMIT`组合优化**： - 对于`WHERE [columnX] = [value] ORDER BY [sort] LIMIT[offset],...
MySQL 索引
qq_41755453的博客
06-16 501
索引是存储引擎为提高数据表的查询速度而提供的一张目录。索引是保存在文件系统中的。InnoDB通过查询语句中的where子句中的字段以及条件判断是否可以从直接从索引中找到结果。
面试题总结
Errol的分享空间
02-24 465
1、创建组合索引如何决定哪个字段在前，哪个字段在后？ 要根据业务需求，where子句中使用最频繁的一列放在最左边。 选择性高的字段放前面，选择性越高，查询性能越好2、增加索引前，如何知道增加索引对SQL有好处？ 如果增加索引的列，选择性很高，则增加索引对查询的性能是有提高的，反之则不然。 对符合条件数据量少的，使用索引效率会高出很多3、当一个表数据已经占满服务
Oracle 一个字段既有联合索引，又有单独索引
ooooooooooooooxiaosu的博客
07-31 2567
环境 系统：Linux AS3.0+ Oracle 9i RAC Dedicated Server OLTP 系统 概述 优化器采用的是RULE ,现有一个表tab1,有字段 a,b ,c ,d ,e . 都是varchar2 类型 ， 建立index 有c单独索引， a, b ,c 联合索引， 现在有sql 语句 ： selectcount(*) fromtab1 where a=:1 and b=:2 a...
MySQL单列索引和组合索引
xlxxcc的专栏
04-14 4257
单列索引，顾名思义也就是只有一个字段的索引列。 组合索引，又称复合索引，两个或更多个列上的索引被称作复合索引。对于复合索引，他们都遵循左侧原则，也是就是说一个查询可以只使用复合索引最左侧的一部份。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。假如：我们有如下的一个表结构：
mysql联合索引不按顺序_mysql联合索引的使用以及sql执行的过程顺序
weixin_36293915的博客
02-02 1852
例如新增了一个联合索引 abc，我写一条sqlselect* fromtable1 where a=1 andb=2 and c=2;这样3个字段都是可以使用联合索引的。3者调换任意位置，优化器都能匹配到，使用该索引。select * fromtable1where a=1 and b<3 and c=2;此时只要a和b字段能用到索引，因为b使用了范围查询，后面的c就无法使用。...
mysql学习（9）：联合索引和多个单列索引的使用区别详解
欢迎来到Jimmy的博客
08-19 5180
前言 为了提高数据库的查询效率，建索引是最常用的手段。那么问题来了，如果查询条件为2个及以上，我们是创建多个单列索引好呢，还是建一个联合索引比较好？ 正文 我们首先来建一张表，并且指定其中的3个字段（学号、姓名、电话）为联合索引： CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `stu_id` varchar(20) DEFAULT NULL COMMENT '学号', `name` varchar(30) DEFA
已经建了唯一索引的字段再和另一个普通字段组合建唯一索引，查询性能有差别吗？
weixin_42988781的博客
07-28 299
例如cl1对f1建唯一索引，db.cl1.find({“f1”:“xxx”}) cl2对f1建唯一索引，再对(f1,f2)建组合索引，db.cl2.find({“f1”:“xxx”,“f2”:“xxx”}) 理论上，两个查询性能应该是没有区别的，因为f1已经保证唯一了，所以都是n条记录用加了索引的搜索算法找1条，能有什么区别。。 ...
多个单列索引和联合索引的区别详解
feicongcong的博客
12-09 1066
https://blog.csdn.net/Abysscarry/article/details/80792876 一、联合索引测试 注：Mysql版本为 5.7.20 创建测试表(表记录数为63188)： CREATE TABLE `t_mobilesms_11` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `userId` varcha...
多个字段的联合索引有什么影响
最新发布
07-14
多个字段的联合索引可以对查询性能产生积极的影响。通过将多个字段组合成一个联合索引，可以提高查询的效率，并减少查询所需的时间和资源消耗。 以下是一些多字段联合索引的影响： 1. 提高查询效率：当你执行包含联合索引中涉及的字段的查询时，数据库可以更快地定位到满足索引条件的数据。这样可以减少全表扫描的需要，从而提高查询的速度。 2. 减少索引文件大小：相比于单独创建多个单列索引，使用联合索引可以减少索引文件的大小。这对于存储和维护索引数据来说是有益的，特别是当表具有大量数据行时。 3. 约束数据完整性：联合索引可以用于实施数据完整性约束。通过在多个字段上创建联合唯一索引，可以确保这些字段的组合值在表中是唯一的，防止重复或冲突的数据被插入。 需要注意的是，使用联合索引也可能存在一些潜在的影响： 1. 更新和插入操作的性能：当你执行包含联合索引字段的更新和插入操作时，数据库需要维护索引的值。这可能会对更新和插入操作的性能产生一些额外的开销。 2. 查询字段的顺序：联合索引的字段顺序很重要。如果你的查询条件中的字段顺序与联合索引的字段顺序不匹配，那么索引将无法有效地支持查询，从而导致性能下降。 3. 索引选择性：联合索引的选择性是指索引中不同值的数量与表中总行数的比例。如果联合索引的选择性很低，即索引中的不同组合值较少，那么它可能不会带来明显的性能提升。 因此，在创建多字段联合索引时，你需要综合考虑查询模式、数据完整性、索引选择性以及更新和插入操作的性能等因素。根据具体的业务需求和数据访问模式，进行适当的索引设计和优化。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值