Mysql中复合索引使用规则有哪些

• 攻略

技术Mysql中复合索引使用规则有哪些这篇文章主要介绍了Mysql中复合索引使用规则有哪些，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。联合索引验证:从左

这篇文章主要介绍了关系型数据库中复合索引使用规则有哪些，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

联合索引验证：从左向右发挥作用

索引：(c1，c2，c3，c4):找到c1的基础上，可以找到c2，找到c3的基础上，可以找到补体第四成份缺乏

a:select *来自t，其中c1=x，c2=x，c3=x，C4=x；

b:select *来自t，其中c1=x，c2=x，c4x，C3=x；用到了c1 c2 c3 c4

c:select * from t其中c1=x和c2=x和c4=x按c3排序；C1C2用到了索引查找，C3只发挥了排序的作用，C3不用(c3:订购发挥作用了，排序不用作了)，C4的索引就不用，4块木板，中间断了，后面也就用不上了

d:select * from t其中c1=x和c4=x被c3、c2分组；

e:select * from t其中c1=x和c5=x按c2、c3排序；

来自t的f:select *其中c1=x和c2=x和c5=？按c2、c3排序；

创建表t (c1炭(10)、c2炭(10)、c3炭(10)、C4炭(10)、C5炭(10))；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

在t(c1，c2，c3，c4)上创建索引idx _ t _ c1234

在t(c1)上创建索引idx _ t _ c1

在t(c2)上创建索引idx _ t _ c2

在t(c3)上创建索引idx _ t _ c3

在t(c4)上创建索引idx _ t _ c4

更改表t删除索引idx _ t _ c1234

a:

解释从t中选择*其中c1='a1 '和c2='b2 '和c3='a3 '和c4=' a

- - - - - - -

-----+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 44      | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+

key_len: 44 // CHAR(10)*4 + 4 * NULL:说明全用到了3个索引，且都是等值查询的索引：c1,c2,c3,c4

删除了复合索引后：发现只用到c1索引，c2,c3,c4索引全没用上
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4='a';
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys                       | key      | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c1 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+

删除了复合索引后：发现只使用了一个索引c4，没有用c1索引，这是因为优化器发现c4='a1'一条也没找到，用这个索引查询是最快的
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4='a1';
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys                       | key      | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c4 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+

删除了复合索引后：发现只使用了一个索引c1，没有用其它索引，这是因为优化器没有发现哪个条件取值记录最少（c2,c3,c4='等值连接也是匹配多条）就选第最左列索引
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4='a4';
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys                       | key      | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c1 | 11      | const |   18 | Using index condition; Using where |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+

a:
explain select * from t where c4='a1' and c2='b2' and c3='a3'  and c1='a1';
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 44      | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
where条件后面的顺序无关 

b:
explain select * from t where c1='a1' and c2='b2' and c4>'a' and c3='a3';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | idx_t_c1234   | idx_t_c1234 | 44      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+

 key_len: 44 // CHAR(10)*4 + 4 * NULL:说明全用到了4个索引，且都是等值查询的索引：c1,c2,c3,c4,全通过
 Using index condition：5.6新特性，Where条件过滤是在innodb引擎层就可做掉了，这样innodb发送给server层的会少很多，如果不启用该功能，则数据通过索引访问后，数据要发送到server层进行where过滤

b:
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4>'a';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | idx_t_c1234   | idx_t_c1234 | 44      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+

range：代表c4采用索引了，且使用到范围查找

c:
explain select * from t where c1='a1' and c2='b2' and c4='b4' order by c3; 
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 22      | const,const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+

 key_len: 22 // CHAR(10)*2 + 2 * NULL:说明全用到了c1,c2索引,且都是等值查询的索引：c1,c2
 Using where:说明c4在server层进行where过滤操作
 c3：用到了索引排序
 
ref 需要与索引比较的列 列名或者const（常数，where id = 1的时候就是const了）

删除了复合索引后：只用到了c1索引，也就是只用一个索引，其它索引也没用上，排序也没用上
explain select * from t where c1='a1' and c2='b2' and c4='b4' order by c3; 
+----+-------------+-------+------+----------------------------+----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys              | key      | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+----------------------------+----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c4 | idx_t_c1 | 11      | const |    2 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+----------------------------+----------+---------+-------+------+----------------------------------------------------+

d:
explain select * from t where c1='a1' and c4='c4' group by c3,c2;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                                                               |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition; Using where; Using temporary; Using filesort |

key_len: 11 // CHAR(10)*1 + 1 * NULL:说明全用到了c1索引,且都是等值查询的索引：c1
Using temporary：DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列,且没用到索引，才会用临时表来排序，该临时表是内存临时表，还不是最糟糕的，最怕的是Using disk temporary
Using filesort：当我们试图对一个没有索引的字段进行排序时，就是filesoft
c3,c2由于与(c1,c2,c3,c4)索引不连续，无法用到索引排序

删除了复合索引后：只用到了c1索引，也就是只用一个索引，其它索引也没用上，group by 也没用上
explain select * from t where c1='a1' and c4='c4' group by c3,c2;
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys     | key      | key_len | ref   | rows | Extra                                                               |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c4 | idx_t_c4 | 11      | const |    1 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------------------------------------------+

d:
explain select * from t where c1='a1' and c4='c4' group by c2,c3;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+

c2,c3用到了(c1,c2,c3,c4)索引排序，与c1相连

e:
explain select * from t where c1='a3' and c5='a5' order by c2,c3;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+

 key_len: 11 // CHAR(10)*1 + 1 * NULL:说明全用到了c1索引,且都是等值查询的索引：c1

f:
explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c2,c3; 
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 22      | const,const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+

 key_len: 11 // CHAR(10)*2 + 2 * NULL:说明全用到了c1索引,且都是等值查询的索引：c1,c2

group by 中能通过索引避免排序的原理：
explain select * from t where c1='a1' and c4='c4' group by c3,c2;
explain select * from t where c1='a1' and c2='b2' and c4='b4' order by c3; 
where条件只是过虑数据，在过滤的过程中，如果c3,c2有索引，就可直接使用
在查找的过程中，己可得到c3在一起的数据，此时可以sum,avg等，不用排序了

删除了复合索引后：只用到了c1索引，也就是只用一个索引，其它索引也没用上， order by  也没用上
explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c2,c3; 
+----+-------------+-------+------+-------------------+----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys     | key      | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2 | idx_t_c1 | 11      | const |    2 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+----------------------------------------------------+

g:
explain select * from t where c3='a%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |   36 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
全表扫，没用到了复合索引idx_t_c1234，除非Where条件后面有c1,c2

explain select * from t where c1='a%';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

用到了复合索引idx_t_c1234

感谢你能够认真阅读完这篇文章，希望小编分享的“Mysql中复合索引使用规则有哪些”这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!

内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/66416.html

赞 (0)

相关推荐

  • 优化查询语句的建议具体是什么

    技术优化查询语句的建议具体是什么本篇文章为大家展示了优化查询语句的建议具体是什么，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。我要做两件事情，第一，指出为什么这个清单很糟糕，第二

    攻略 2021年12月3日

  • BASE64处理文件

    技术BASE64处理文件 BASE64处理文件Base64:
    Base64是一种编码方式，基于64个ASCII字符来表示二进制数据，Base64将8个bit为一个单位的字节数据拆分为以6个bit为一个单

    礼包 2021年12月24日

  • SpringBoot自动配置Quartz的实现步骤是什么

    技术SpringBoot自动配置Quartz的实现步骤是什么本篇内容介绍了“SpringBoot自动配置Quartz的实现步骤是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学

    攻略 2021年11月1日

  • 数据库的信息安全管理有哪些

    技术数据库的信息安全管理有哪些本篇内容主要讲解“数据库的信息安全管理有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“数据库的信息安全管理有哪些”吧!一、操作系统账号的安全

    攻略 2021年11月15日

  • memcached与redis性能测试的示例分析

    技术memcached与redis性能测试的示例分析这篇文章主要介绍memcached与redis性能测试的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！memcached与redis性能

    攻略 2021年11月15日

  • 如何搭建动态IP池

    技术如何搭建动态IP池本篇文章给大家分享的是有关如何搭建动态IP池，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。DynamicIP是指在需要时分配IP地址的

    攻略 2021年11月4日