MySQL聯合索引的應用

這篇文章主要講解了“MySQL聯合索引的應用”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL聯合索引的應用”吧！

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聯合索引的應用這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！