MySQL联合索引最左匹配原则

zxrhhm

已于 2024-07-07 09:26:36 修改

阅读量350

点赞数 10

文章标签： mysql 数据库服务器

于 2024-07-07 09:25:08 首次发布

本文链接：https://blog.csdn.net/zxrhhm/article/details/140240993

版权

MySQL中的联合索引(也叫组合索引)遵循最左匹配原则，即在创建联合索引时，查询条件必须从索引的最左边开始，否则索引不会被使用。在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

例如，假设有一个表t_employees，它有一个联合索引(first_name, last_name)。

(root@192.168.80.85)[superdb]> create table t_employees as select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from employees;
Query OK, 91 rows affected (0.06 sec)
Records: 91  Duplicates: 0  Warnings: 0

(root@192.168.80.85)[superdb]> select * from t_employees;
+-------------+-------------+-------------+----------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | SALARY   |
+-------------+-------------+-------------+----------+
|         116 | Shelli      | Baida       |  2900.00 |
|         117 | Sigal       | Tobias      |  2800.00 |
|         118 | Guy         | Himuro      |  2600.00 |
|         119 | Karen       | Colmenares  |  2500.00 |
|         120 | Matthew     | Weiss       |  8000.00 |
|         121 | Adam        | Fripp       |  8200.00 |
|         122 | Payam       | Kaufling    |  7900.00 |
|         123 | Shanta      | Vollman     |  6500.00 |
|         124 | Kevin       | Mourgos     |  5800.00 |
|         125 | Julia       | Nayer       |  3200.00 |
|         126 | Irene       | Mikkilineni |  2700.00 |
|         127 | James       | Landry      |  2400.00 |
|         128 | Steven      | Markle      |  2200.00 |
|         129 | Laura       | Bissot      |  3300.00 |
|         130 | Mozhe       | Atkinson    |  2800.00 |
|         131 | James       | Marlow      |  2500.00 |
|         132 | TJ          | Olson       |  2100.00 |
|         133 | Jason       | Mallin      |  3300.00 |
|         134 | Michael     | Rogers      |  2900.00 |
|         135 | Ki          | Gee         |  2400.00 |
|         136 | Hazel       | Philtanker  |  2200.00 |
|         137 | Renske      | Ladwig      |  3600.00 |
|         138 | Stephen     | Stiles      |  3200.00 |
|         139 | John        | Seo         |  2700.00 |
|         140 | Joshua      | Patel       |  2500.00 |
|         141 | Trenna      | Rajs        |  3500.00 |
|         142 | Curtis      | Davies      |  3100.00 |
|         143 | Randall     | Matos       |  2600.00 |
|         144 | Peter       | Vargas      |  2500.00 |

(root@192.168.80.85)[superdb]> alter
 table t_employees add constraint pk_t_employees_id primary key(EMPLOYEE_ID);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@192.168.80.85)[superdb]> create index indx_t_employees_nameinfo on t_employees(FIRST_NAME,LAST_NAME);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@192.168.80.85)[superdb]> show index from t_employees;
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_employees |          0 | PRIMARY                   |            1 | EMPLOYEE_ID | A         |          91 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_employees |          1 | indx_t_employees_nameinfo |            1 | FIRST_NAME  | A         |          79 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_employees |          1 | indx_t_employees_nameinfo |            2 | LAST_NAME   | A         |          91 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

1、满足联合索引最左匹配原则

以下查询会使用这个联合索引：
SELECT * FROM t_employees WHERE first_name = ‘James’;
SELECT * FROM t_employees WHERE first_name = ‘James’ AND last_name = ‘Marlow’;

(root@192.168.80.85)[superdb]> explain SELECT * FROM t_employees WHERE first_name = 'James';
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_employees | NULL       | ref  | indx_t_employees_nameinfo | indx_t_employees_nameinfo | 83      | const |    2 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(root@192.168.80.85)[superdb]> explain SELECT * FROM t_employees WHERE first_name = 'James' and LAST_NAME='Marlow';
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_employees | NULL       | ref  | indx_t_employees_nameinfo | indx_t_employees_nameinfo | 185     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

但是，下面的查询不会使用联合索引：
SELECT * FROM t_employees WHERE LAST_NAME=‘Marlow’;

(root@192.168.80.85)[superdb]>  explain SELECT * FROM t_employees WHERE LAST_NAME='Marlow';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   91 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

因为它们没有从索引的最左边开始。在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

下面的查询会使用联合索引

(root@192.168.80.85)[superdb]> explain SELECT * FROM t_employees WHERE LAST_NAME='Marlow' and first_name = 'James';
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_employees | NULL       | ref  | indx_t_employees_nameinfo | indx_t_employees_nameinfo | 185     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。需要注意的是，因为有查询优化器，所以 first_name,last_name 字段在 where 子句的顺序并不重要

2、联合索引不遵循最左匹配原则，也是走全扫描二级索引树

我们都知道联合索引要遵循最左匹配才能走索引，但是如果数据库表中的字段都是索引的话，即使查询过程中，没有遵循最左匹配原则，也是走全扫描二级索引树(type=index)

如下的表结构及查询

(root@192.168.80.85)[superdb]> create table t_emplist as select EMPLOYEE_ID,FIRST_NAME,LAST_NAME from employees;
Query OK, 91 rows affected (0.10 sec)
Records: 91  Duplicates: 0  Warnings: 0

(root@192.168.80.85)[superdb]> alter table t_emplist add constraint pk_t_emplist_id primary key(EMPLOYEE_ID);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@192.168.80.85)[superdb]> create index indx_t_emplist_nameinfo on t_emplist(FIRST_NAME,LAST_NAME);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

如下面的查询会使用联合索引，但不是最左匹配原则
SELECT * FROM t_emplist WHERE LAST_NAME=‘Marlow’;

(root@192.168.80.85)[superdb]> explain SELECT * FROM t_emplist WHERE LAST_NAME='Marlow';
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_emplist | NULL       | index | indx_t_emplist_nameinfo | indx_t_emplist_nameinfo | 185     | NULL |   91 |    10.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)