MySQL联合索引的应用

这篇文章主要讲解了“MySQL联合索引的应用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL联合索引的应用”吧！

创新互联基于分布式IDC数据中心构建的平台为众多户提供四川乐山服务器托管 四川大带宽租用 成都机柜租用 成都服务器租用。

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int NOT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

字段 a , b , c 为联合索引

• abc(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a= 8 and b = 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_abc     | index_abc | 9       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• ab(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a= 8 and b = 8;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_abc     | index_abc | 9       | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• ac(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a= 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_abc     | index_abc | 5       | const |    1 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

• bc(非索引)

mysql> EXPLAIN SELECT * FROM `t1` where b = 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10107 |    10.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• a(范围)bc(非索引)

mysql> EXPLAIN SELECT * FROM `t1` where a > 8 and b = 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | index_abc     | NULL | NULL    | NULL | 10107 |     5.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

-ab(范围)c(索引,Filesort)

mysql> EXPLAIN SELECT * FROM `t1` where a = 8 and b > 8 ORDER BY c;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | index_abc     | index_abc | 9       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(范围)(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a = 8 and b = 8 and c > 8;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | index_abc     | index_abc | 14      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• abc(or)(非索引)

mysql> EXPLAIN SELECT * FROM `t1` where a= 8 or b = 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | index_abc     | NULL | NULL    | NULL | 10107 |    10.01 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(非索引)

mysql> EXPLAIN SELECT * FROM `t1` where a != 8 and b = 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | index_abc     | NULL | NULL    | NULL | 10107 |     5.01 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(索引,filesort)

mysql> EXPLAIN SELECT * FROM `t1` where a = 8 and b != 8 ORDER BY c;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | index_abc     | index_abc | 9       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a = 8 and b = 8 and c != c;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_abc     | index_abc | 9       | const,const |    1 |    90.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• abc(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a = 8 and c != 8 ORDER BY b;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_abc     | index_abc | 5       | const |    1 |    90.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• abc(非索引)

mysql> EXPLAIN SELECT * FROM `t1` where a like '%8' and b = 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10107 |     1.11 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(索引,filesort)

mysql> EXPLAIN SELECT * FROM `t1` where a = 8 and b like '%8' ORDER BY c;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_abc     | index_abc | 5       | const |    1 |    11.11 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a = 8 and b = 8 and c like '%8';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_abc     | index_abc | 9       | const,const |    1 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• abc(非索引)

mysql> EXPLAIN SELECT * FROM `t1` where a not in (8) and b = 8 ORDER BY c;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | index_abc     | NULL | NULL    | NULL | 10107 |     5.01 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(索引,filesore)

mysql> EXPLAIN SELECT * FROM `t1` where a= 8 and b not in (8) ORDER BY c;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | index_abc     | index_abc | 9       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

• abc(索引)

mysql> EXPLAIN SELECT * FROM `t1` where a= 8 and b = 8 and c not in (8);
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | index_abc     | index_abc | 14      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

感谢各位的阅读，以上就是“MySQL联合索引的应用”的内容了，经过本文的学习后，相信大家对MySQL联合索引的应用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！