最近看了一本MYSQL优化的书,书上说where条件前后顺序会导致索引无法使用?MMP 啥意思呢?
意思说 比如说 建了个组合索引 CREATE INDEX IX_A_B ON t(a,b);
SELECT * FROM T WHERE B=1 AND A=2;
这样的语句使用不上IX_A_B,因为条件WHERE顺序不匹配!
记得以前面试被问过ORACLE WHERE 条件顺序如何? 我很蒙蔽啊!什么鬼问题?
现在才明白当初的面试官是从MYSQL过来的!!
那我们做下测试,只从MYSQL被O收走了后,出了8.0 还添加了跳跃索引扫描。这些ORACLE数据库老早使用的技术了!
看看这个不友好的WHERE条件顺序的限制是否还存在! 拿5.7来看看!
mysql> desc T_Dire_Info;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| DirectoryNumber | varchar(36) | NO | PRI | NULL | |
| Name | varchar(50) | YES | | NULL | |
| IdNo | varchar(30) | YES | | NULL | |
| FileDirectory | varchar(200) | YES | | NULL | |
| PageCount | int(11) | YES | | NULL | |
| PosPage | varchar(50) | YES | | NULL | |
| PosX | varchar(500) | YES | | NULL | |
| PosY | varchar(500) | YES | | NULL | |
| CreateDate | datetime | YES | | NULL | |
| UpdateDate | datetime | YES | | NULL | |
| ImagePath | varchar(500) | YES | | NULL | |
| FileDirectoryDst | varchar(200) | YES | | NULL | |
| FileDirectoryFinal | varchar(200) | YES | | NULL | |
| SuccessUrl | varchar(500) | YES | | NULL | |
| SignStatus | varchar(5) | YES | | NULL | |
| Mobile | varchar(20) | YES | | NULL | |
| RatioX | varchar(500) | YES | | NULL | |
| RatioY | varchar(500) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
拿个测试库的表玩玩!
mysql>show index from T_Dire_Info;
+-------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| T_Dire_Info | 0 | PRIMARY | 1 | DirectoryNumber | A | 280 | NULL | NULL | | BTREE | | |
+-------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
只有一个主键索引!
我看拿NAME和MOBILE 做个组合索引,这两个字段应该比较常用吧!
mysql> select Name,Mobile from T_Dire_Info;
+-----------+-------------+
| Name | Mobile |
+-----------+-------------+
| 陈婵 | 13128931806 |
| 魏小伟 | 18898733036 |
| 龙杰勇 | 13128761786 |
| 龙杰勇 | 13128761786 |
| 黄林 | 13418670345 |
| 龙杰勇 | 13128761786 |
| 龙杰勇 | 13128761786 |
| 王振国 | 13410766451 |
| 魏小伟 | 18898733036 |
| 魏小伟 | 18898733036 |
| 龙杰勇 | 13128761786 |
| 李路 | 13798258167 |
| 黄林 | 13418670345 |
| 陈飞 | 18665916371 |
都是重复数据而已。创建组合索引
mysql>create index ix_Dire_Info_Name_Mobile on T_Dire_Info(Name,Mobile);
show index from T_Dire_Info;
+-------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| T_Dire_Info | 0 | PRIMARY | 1 | DirectoryNumber | A | 277 | NULL | NULL | | BTREE | | |
| T_Dire_Info | 1 |ix_Dire_Info_Name_Mobile| 1 |Name| A | 17 | NULL | NULL | YES | BTREE | | |
| T_Dire_Info | 1 |ix_Dire_Info_Name_Mobile| 2 |Mobile| A | 28 | NULL | NULL | YES | BTREE | | |
+-------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
索引有了
explain select * from T_Dire_Info where name='ZFK';
+----+-------------+-------------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | T_Dire_Info | NULL | ref |ix_Dire_Info_Name_Mobile| ix_Dire_Info_Name_Mobile | 153 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
用上了索引
mysql> explain select * from T_Dire_Info where Mobile=1355;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | T_Dire_Info | NULL | ALL | NULL | NULL | NULL | NULL | 289 | 10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表扫描,
mysql> explain select * from T_Dire_Info where Mobile=1355 and Name='zfk';
+----+-------------+-------------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | T_Dire_Info | NULL | ref |ix_Dire_Info_Name_Mobile| ix_Dire_Info_Name_Mobile | 153 | const | 1 | 10.00 | Using index condition |
+----+-------------+-------------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
前后顺序不一样 还是用上了索引