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联合索引的应用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
新闻名称：MySQL联合索引的应用
标题网址：http://pwwzsj.com/article/podcgj.html
