这篇文章主要讲解了“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 为联合索引
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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联合索引的应用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
网站题目：MySQL联合索引的应用
转载源于：
http://cdkjz.cn/article/podcgj.html
