MySQL进阶-索引-使用规则-最左前缀法则和范围查询

已于 2024-06-27 00:12:16 修改
阅读量509 收藏 9
点赞数 5
分类专栏： MySQL高级篇 文章标签： mysql 数据库 最左前缀法则 范围查询
于 2024-06-26 22:43:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_65152767/article/details/139998400
版权

文章目录

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 - GPL

Copyright (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
此时索引都生效

在这里插入图片描述

小丁学Java
关注
  • 5
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
博客
史上最快在IDEA中创建类，只需要 ctrl + 鼠标左键双击 就可以调出创建类的窗口（全网首创）
08-02 1169
需要首先鼠标右键一次，点击新建，再点击 Java类，过于麻烦。我们只需要自定义设置创建类的快捷键即可。
博客
写的一致性问题之双删模式
09-13 23
异步双删的改进版，在子线程或者消息的消费中延时一段时间再进行删除。在事务提交之后异步删除redis，性能得到一定的优化。实现思路：多线程、kafka。开启定时任务进行删除。
博客
写的一致性问题之失效模式
09-12 261
写的一致性问题之失效模式。
博客
Java中List集合去重
09-11 190
反问问题：为什么不直接使用 Set 或者 LinkedHashSet 呢实际场景：实际的业务开发中遇到的情况会更复杂。比如，List 集合可能是历史遗留问题，也有可能是调用接口返回的类型限制，只能使用 List 接收，又或者是代码写了一半，在做多个集合合并的时候才发现了这个问题。
博客
写的一致性问题之双写模式
09-04 421
写的一致性问题之双写模式
博客
redis缓存的目的、场景、实现、一致性问题
09-04 560
专辑详情和声音详情属于并发量较高的数据，如果每次访问都实时到数据库获取数据，数据库的访问压力太大。而这些信息一般更新的频率比较低，短时间内不会发生改变。因此，我们可以考虑在前台系统中，增加一层缓存，把这些数据缓存起来，请求到来时，不再调用数据接口，而是直接读取缓存中的数据。
博客
搭建 xxl-job 执行器项目
09-04 515
copy案例工程的配置文件：xxl-job\xxl-job-executor-samples\xxl-job-executor-sample-springboot\src\main\java\com\xxl\job\executor\core\config。复制到 scheduled-demo 这个模块中。到我们的工程中去，不需要作任何修改。
博客
搭建 xxl-job 调度中心
09-03 543
启动调度中心：java -jar xxl-job-admin-xxx.jar 或者 java -jar -Dserver.port=8088 xxl-job-admin-xxx.jar。调度中心配置文件：/xxl-job/xxl-job-admin/src/main/resources/application.properties。在浏览器访问：http://localhost:10010/xxl-job-admin/切换到xxl-job\xxl-job-admin项目目录下。
博客
xxl-job介绍
09-03 425
分布式任务调度框架几乎是每个大型应用必备的工具。XXL-JOB是一个分布式任务调度平台，其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线，开箱即用。目前已有多家公司接入xxl-job，包括比较知名的大众点评，京东，优信二手车，北京尚德，360金融 (360)，联想集团 (联想)，易信 (网易)等等…
博客
直播相关概念
09-03 308
腾讯云地址：https://cloud.tencent.com/login
博客
好用的国内中文大模型
08-29 516
好用的国内中文大模型
博客
使用kafka完成数据的实时同步，同步到es中。(使用kafka实现自动上下架 upper、lower)
08-28 1297
公开的同步公开的同步私有的删除。
博客
添加专辑失败，获取 userId 为空
08-27 456
我们是在切面类 GuiGuAspect 里面获取的 userId ，所以我们需要在指定的方法上面加上 切面注解@GuiGuLogin。点击 getUserId() 进入方法内部。这样问题就可以得到解决。
博客
SpringData-ElasticSearch入门
08-27 734
SpringData-ElasticSearch入门。
博客
es映射配置（_mapping）
08-26 600
已经存在，并且已经有了不同的映射配置，那么直接发送这个 POST 请求可能会导致错误，因为 Elasticsearch 不允许更改已存在字段的类型或某些其他属性。在这种情况下，您可能需要先删除索引（如果数据允许的话），或者创建一个新的索引。的索引（如果该索引尚不存在，则创建它）设置映射（mapping）的。请根据您的实际 Elasticsearch 配置（如主机名、端口和可能的认证要求）调整此命令。您提供的 POST 请求是用于在 Elasticsearch 中为名为。
博客
es相关概念、索引操作（相当于mysql中的数据库操作）
08-26 1222
【代码】es相关概念、索引操作（相当于mysql中的数据库操作）
博客
es、kibana及分词器的安装
08-26 1191
又称反向索引，分为两部分：文档列表和倒排索引区。
博客
使用策略模式改造单点登录
08-26 704
使用策略模式改造单点登录。
博客
SpringBoot整合策略模式之基于 ApplicationContextAware + 枚举类 + 自定义注解实现（推荐）
08-26 472
SpringBoot整合策略模式之基于 ApplicationContextAware + 枚举类 + 自定义注解实现（推荐）
博客
SpringBoot整合策略模式之基于 ApplicationContextAware + 枚举类实现
08-26 468
SpringBoot整合策略模式之基于 ApplicationContextAware + 枚举类实现。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值