文章目录
- 1、最左前缀法则
- 2、启动mysql
- 3、查询tb_user
- 4、查看tb_user的索引
- 5、执行计划 profession ='软件工程' and age=31 and status = '0'
- 6、执行计划 profession ='软件工程' and age=31
- 7、执行计划 profession ='软件工程'
- 8、执行计划 age=31 and status = '0'
- 9、执行计划 status = '0'
- 10、执行计划 profession='软件工程' and status ='0'
- 11、执行计划 age=31 and status='0' and profession='软件工程'
- 12、执行计划 status='0' and profession='软件工程' and age=30
- 13、执行计划 age=30 and profession='软件工程' and status='0'
- 14、执行计划 profession='软件工程' and status='0' and age=30
- 2、范围查询
- 2.1、执行计划 profession='软件工程' and age>30 and status='0'
- 2.2、执行计划 profession='软件工程' and age>=30 and status='0'
1、最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳过某一列，索引将部分失效（后面的字段索引失效）。
2、启动mysql
C:\Users\dgq>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 299
Server version: 8.0.26 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
3、查询tb_user
mysql> use mybatis-example;
Database changed
mysql> select * from tb_user;
+----+--------+-------------+-----------------------+--------------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+--------+-------------+-----------------------+--------------------+------+--------+--------+---------------------+
| 1 | 吕布 | 17799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 17799990001 | caocao666@qq.com | 通讯工程 | 33 | 1 | 0 | 2001-03-05 00:00:00 |
| 3 | 赵云 | 17799990002 | 17799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-02 00:00:00 |
| 4 | 孙悟空 | 17799990003 | 17799990@sina.com | 工程造价 | 54 | 1 | 0 | 2001-07-02 00:00:00 |
| 5 | 花木兰 | 17799990004 | 19980729@sina.com | 软件工程 | 23 | 2 | 1 | 2001-04-22 00:00:00 |
| 6 | 大乔 | 17799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 7 | 露娜 | 17799990006 | luna_love@sina.com | 应用数学 | 24 | 2 | 0 | 2001-02-08 00:00:00 |
| 8 | 程咬金 | 17799990007 | chengyaojin@163.com | 化工 | 38 | 1 | 5 | 2001-05-23 00:00:00 |
| 9 | 项羽 | 17799990008 | xiaoyu666@qq.com | 金属材料 | 43 | 1 | 0 | 2001-09-18 00:00:00 |
| 10 | 白起 | 17799990009 | baiqi666@sina.com | 机械工程及其自动化 | 27 | 1 | 2 | 2001-08-16 00:00:00 |
| 11 | 韩信 | 17799990010 | hanxin520@163.com | 无机非金属材料工程 | 27 | 1 | 0 | 2001-06-12 00:00:00 |
| 12 | 荆轲 | 17799990011 | jingke123@163.com | 会计 | 29 | 1 | 0 | 2001-05-11 00:00:00 |
| 13 | 兰陵王 | 17799990012 | lanlinwang666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-09 00:00:00 |
| 14 | 狂铁 | 17799990013 | kuangtie@sina.com | 应用数学 | 43 | 1 | 2 | 2001-04-10 00:00:00 |
| 15 | 貂蝉 | 17799990014 | 84958948374@qq.com | 软件工程 | 40 | 2 | 3 | 2001-02-12 00:00:00 |
| 16 | 妲己 | 17799990015 | 2783238293@qq.com | 软件工程 | 31 | 2 | 0 | 2001-01-30 00:00:00 |
| 17 | 芈月 | 17799990016 | xiaomin2001@sina.com | 工业经济 | 35 | 2 | 0 | 2000-05-03 00:00:00 |
| 18 | 嬴政 | 17799990017 | 8839434342@qq.com | 化工 | 38 | 1 | 1 | 2001-08-08 00:00:00 |
| 19 | 狄仁杰 | 17799990018 | jujiamlm8166@163.com | 国际贸易 | 30 | 1 | 0 | 2007-03-12 00:00:00 |
| 20 | 安琪拉 | 17799990019 | jdodm1h@126.com | 城市规划 | 51 | 2 | 0 | 2001-08-15 00:00:00 |
| 21 | 典韦 | 17799990020 | ycaunanjian@163.com | 城市规划 | 52 | 1 | 2 | 2000-04-12 00:00:00 |
| 22 | 廉颇 | 17799990021 | lianpo321@126.com | 土木工程 | 19 | 1 | 3 | 2002-07-18 00:00:00 |
| 23 | 后羿 | 17799990022 | altycj2000@139.com | 城市园林 | 20 | 1 | 0 | 2002-03-10 00:00:00 |
| 24 | 姜子牙 | 17799990023 | 37483844@qq.com | 工程造价 | 29 | 1 | 4 | 2003-05-26 00:00:00 |
+----+--------+-------------+-----------------------+--------------------+------+--------+--------+---------------------+
24 rows in set (0.00 sec)mysql>
4、查看tb_user的索引
mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email | 1 | email | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)mysql>
5、执行计划 profession =‘软件工程’ and age=31 and status = ‘0’
mysql> select * from tb_user where profession ='软件工程' and age=31 and status = '0';
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| 16 | 妲己 | 17799990015 | 2783238293@qq.com | 软件工程 | 31 | 2 | 0 | 2001-01-30 00:00:00 |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)mysql> explain select * from tb_user where profession ='软件工程' and age=31 and status = '0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql>
profession的key_len=47
age的key_len=2
status的key_len=5
此时索引都生效
6、执行计划 profession =‘软件工程’ and age=31
mysql> explain select * from tb_user where profession ='软件工程' and age=31;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 49 | const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql>
profession的key_len=47
age的key_len=2
此时索引都生效
7、执行计划 profession =‘软件工程’
mysql> explain select * from tb_user where profession ='软件工程';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 47 | const | 4 | 100.00 | NULL |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql>
profession的key_len=47
此时索引生效
8、执行计划 age=31 and status = ‘0’
mysql> explain select * from tb_user where age=31 and status = '0';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 4.17 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql>
此时索引失效
9、执行计划 status = ‘0’
mysql> explain select * from tb_user where status = '0';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql>
此时索引失效
10、执行计划 profession=‘软件工程’ and status =‘0’
mysql> explain select * from tb_user where profession='软件工程' and status ='0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 47 | const | 4 | 10.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql>
profession的key_len=47
status索引失效
11、执行计划 age=31 and status=‘0’ and profession=‘软件工程’
mysql> explain select * from tb_user where age=31 and status='0' and profession='软件工程';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql>
profession的key_len=47
age的key_len=2
status的key_len=5
此时索引都生效
12、执行计划 status=‘0’ and profession=‘软件工程’ and age=30
mysql> explain select * from tb_user where status='0' and profession='软件工程' and age=30;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
profession的key_len=47
age的key_len=2
status的key_len=5
此时索引都生效
13、执行计划 age=30 and profession=‘软件工程’ and status=‘0’
mysql> explain select * from tb_user where age=30 and profession='软件工程' and status='0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
profession的key_len=47
age的key_len=2
status的key_len=5
此时索引都生效
14、执行计划 profession=‘软件工程’ and status=‘0’ and age=30
mysql> explain select * from tb_user where profession='软件工程' and status='0' and age=30;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
profession的key_len=47
age的key_len=2
status的key_len=5
此时索引都生效
2、范围查询
联合索引中，出现范围查询（>,<）,
范围查询右侧的列索引失效
2.1、执行计划 profession=‘软件工程’ and age>30 and status=‘0’
mysql> explain select * from tb_user where profession='软件工程' and age>30 and status='0';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 49 | NULL | 2 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
profession的key_len=47
age的key_len=2
此时status失效
2.2、执行计划 profession=‘软件工程’ and age>=30 and status=‘0’
mysql> explain select * from tb_user where profession='软件工程' and age>=30 and status='0';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 2 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql>
profession的key_len=47
age的key_len=2
status的key_len=5
此时索引都生效
