做的网站怎才能被别人访问到,深圳市龙华区属于哪个区,马蹄室内设计官网,经典重庆MySQL中的联合索引(也叫组合索引)遵循最左匹配原则#xff0c;即在创建联合索引时#xff0c;查询条件必须从索引的最左边开始#xff0c;否则索引不会被使用。在联合索引的情况下#xff0c;数据是按照索引第一列排序#xff0c;第一列数据相同时才会按照第二列排序。
例…MySQL中的联合索引(也叫组合索引)遵循最左匹配原则即在创建联合索引时查询条件必须从索引的最左边开始否则索引不会被使用。在联合索引的情况下数据是按照索引第一列排序第一列数据相同时才会按照第二列排序。
例如假设有一个表t_employees它有一个联合索引(first_name, last_name)。
(root192.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(root192.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 |(root192.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(root192.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(root192.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’;
(root192.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)(root192.168.80.85)[superdb] explain SELECT * FROM t_employees WHERE first_name James and LAST_NAMEMarlow;
---------------------------------------------------------------------------------------------------------------------------------------------------
| 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’;
(root192.168.80.85)[superdb] explain SELECT * FROM t_employees WHERE LAST_NAMEMarlow;
-----------------------------------------------------------------------------------------------------------------
| 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)
因为它们没有从索引的最左边开始。在联合索引的情况下数据是按照索引第一列排序第一列数据相同时才会按照第二列排序。
下面的查询会使用联合索引
(root192.168.80.85)[superdb] explain SELECT * FROM t_employees WHERE LAST_NAMEMarlow 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、联合索引不遵循最左匹配原则也是走全扫描二级索引树
我们都知道联合索引要遵循最左匹配才能走索引但是如果数据库表中的字段都是索引的话即使查询过程中没有遵循最左匹配原则也是走全扫描二级索引树(typeindex)
如下的表结构及查询
(root192.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(root192.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(root192.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’;
(root192.168.80.85)[superdb] explain SELECT * FROM t_emplist WHERE LAST_NAMEMarlow;
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 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)
如果数据库表中的字段只有主键二级索引那么即使查询的where条件不满足最左匹配原则也不会走全表扫描typeall而是走全扫描二级索引树(typeindex)。
关键还是看数据表中的字段及索引情况。
