如何做推广麦当劳的网站,wordpress ssl部署,dedecms搭建网站,医院网站html模板提示#xff1a;mysql索引最左前缀使用的规则#xff0c;以及索引失效和部分字段索引失效 文章目录 索引使用法则最左前缀法则 索引执行explain分析遵守联合索引最左前缀法则#xff08;索引有效#xff09;未遵守联合索引最左前缀法则#xff08;索引失效或部分索引失效mysql索引最左前缀使用的规则以及索引失效和部分字段索引失效 文章目录 索引使用法则最左前缀法则 索引执行explain分析遵守联合索引最左前缀法则索引有效未遵守联合索引最左前缀法则索引失效或部分索引失效 思考 索引使用法则
最左前缀法则
联合索引多列索引要遵守最左前缀法则(最左边的字段必须存在,跳过某一字段后面字段索引失效)造成索引失效或者部分索引失效 1、创建表
city | CREATE TABLE city (ID int NOT NULL AUTO_INCREMENT,Name char(35) NOT NULL DEFAULT ,CountryCode char(3) NOT NULL DEFAULT ,District char(20) NOT NULL DEFAULT ,Info json DEFAULT NULL,PRIMARY KEY (ID)
) ENGINEInnoDB AUTO_INCREMENT4080 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci 2、创建联合索引多列索引
mysql create index idx_Name_CountryCode_District on city (Name,CountryCode,District);Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
create index idx_字段1_字段2......字段n
3、查看索引 show index city;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| city | 0 | PRIMARY | 1 | ID | A | 4079 | NULL | NULL | | BTREE | | | YES | NULL |
| city | 1 | idx_Name_CountryCode_District | 1 | Name | A | 3998 | NULL | NULL | | BTREE | | | YES | NULL |
| city | 1 | idx_Name_CountryCode_District | 2 | CountryCode | A | 4056 | NULL | NULL | | BTREE | | | YES | NULL |
| city | 1 | idx_Name_CountryCode_District | 3 | District | A | 4078 | NULL | NULL | | BTREE | | | YES | NULL |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NameCountryCodeDistrict三个字段创建的联合索引正常显示
上述步骤创建联合索引以及查看联合索引等工作完成
索引执行explain分析
遵守联合索引最左前缀法则索引有效
1、全表执行计划查询
mysql explain select * from city;
-----------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
type:全表扫描 key未使用到索引
2、第一个索引字段Name执行查询
mysql explain select * from city where NameJabaliya- ;
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 140 | const | 1 | 100.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
key使用的所以有Name_CountryCode_District key_len;索引长度140也是Name索引长度140
3、前两个字段Name、CountryCode索引查询执行
mysql explain select * from city where NameJabaliyaand CountryCodeVIR;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 152 | const,const | 1 | 100.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
key_len152上述Name索引字段为140CountryCode字段索引长度等于152-14012
4、三个字段Name、CountryCode、District索引查询执行
mysql explain select * from city where NameJabaliyaand CountryCodeVIR and DistrictManicaland;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 232 | const,const,const | 1 | 100.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
key_len:District索引长度等于232-152
上述三种索引查询情况下索引都正常履行了自身的职责遵守了联合索引多列索引的最做前缀法则所有索引都正常未造成索引失效
未遵守联合索引最左前缀法则索引失效或部分索引失效
1.第一个索引字段、第三个索引字段联合查询执行
mysql explain select * from city where NameJabaliyaand DistrictManicaland;
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 140 | const | 1 | 10.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
当执行第一个字段索引和第三个字段索引时跳过第二个字段索引进行查询的时key_len显示为140和使用explain select * from city where NameJabaliya’查询的索引长度一样name的索引字段等于140而District索引长度未0表示District字段索引失效了当联合索引跳过中间索引时会造成部分索引失效
2、第二个索引字段和第三个索引字段查询执行
mysql explain select * from city where CountryCodeVIR and DistrictManicaland;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 1.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
keynull 未使用到索引 key_lennull 跳过第一个字段索引未遵守联合索引最左前缀法则造成CountryCode、District索引已失效
思考 explain select * from city where DistrictManicaland and NameNablus and CountryCodePSE;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 232 | const,const,const | 1 | 100.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)总结执行查询时只要遵守联合索引最左前缀法则和索引查询时的前后位置无任何关系联合索引遵守idx_Name_CountryCode_District顺序和 explain select * from city where District‘Manicaland’ and Name‘Nablus’ and CountryCode‘PSE’;无关系只要查询中遵守最左前缀法则即可
