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年10月26日

  • PostgreSQL中make_rel_from_joinlist函数分析

    技术PostgreSQL中make_rel_from_joinlist函数分析这篇文章主要介绍“PostgreSQL中make_rel_from_joinlist函数分析”，在日常操作中，相信很多人在PostgreSQL

    攻略 2021年11月10日

  • 代理ip服务器要遵循的关键步骤有哪几个

    技术代理ip服务器要遵循的关键步骤有哪几个代理ip服务器要遵循的关键步骤有哪几个，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。代理ip服务器要遵循几个关键步

    攻略 2021年10月28日

  • mysql如何查询数据表是否存在

    技术mysql如何查询数据表是否存在这篇文章主要为大家展示了“mysql如何查询数据表是否存在”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“mysql如何查询数据表是否存在”

    攻略 2021年12月2日

  • 逻辑回归吴恩达

    技术逻辑回归吴恩达 逻辑回归_吴恩达线性回归做分类不太理想新增最右边额外的红色点，会改变原来的线性回归的拟合直线从洋红改变到蓝色直线，运用原来的数据标准，分类出现了错误，使得新的拟合直线更糟糕而且分类问

    礼包 2021年11月27日

  • Django组件-cookie与session

    技术Django组件-cookie与session Django组件-cookie与sessionDjango组件-cookie与session
    会话跟踪技术
    1 什么是会话跟踪技术
    我们需要先了解

    礼包 2021年12月13日