AI 助理
备案控制台
开发者社区 数据库 文章 正文

Mysql索引失效情况

2023-07-25 105
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

Mysql索引概述：

  • 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
  • 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

索引优点：

  1. 提高检索效率，降低数据库的io成本
  2. 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗

索引缺点：

  1. 索引需要占用物理空间
  2. 索引提高了查询效率但是降低了增删改的效率

Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

  1. B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
  2. Hash索引：底层数据结构使用hash表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
  3. R-Tree空间索引：使用较少
  4. 全文索引：是一种通过建立倒排索引，快速匹配文档的方式

创建数据用户表

1. create table tb_user(
2.  id int primary key auto_increment comment '主键',
3.  name varchar(50) not null comment '用户名',
4.  phone varchar(11) not null comment '手机号',
5.  email varchar(100) comment '邮箱',
6.  profession varchar(11) comment '专业',
7.  age tinyint unsigned comment '年龄',
8.  gender char(1) comment '性别 , 1: 男, 2: 女',
9.  status char(1) comment '状态',
10.  createtime datetime comment '创建时间'
11. 
12. ) comment '系统用户表';

添加测试数据 

1. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
2. createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', 
3. 
4. '6', '2001-02-02 00:00:00');
5. 
6. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
7. createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, 
8. 
9. '1', '0', '2001-03-05 00:00:00');
10. 
11. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
12. createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', 
13. 
14. '2', '2002-03-02 00:00:00');
15. 
16. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
17. createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, 
18. 
19. '1', '0', '2001-07-02 00:00:00');
20. 
21. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
22. createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, 
23. 
24. '2', '1', '2001-04-22 00:00:00');
25. 
26. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
27. createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', 
28. 
29. '0', '2001-02-07 00:00:00');
30. 
31. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
32. createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, 
33. 
34. '2', '0', '2001-02-08 00:00:00');
1. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
2. createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 
3. 
4. '1', '5', '2001-05-23 00:00:00');
5. 
6. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
7. createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, 
8. 
9. '1', '0', '2001-09-18 00:00:00');
10. 
11. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
12. createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
13. 化', 27, '1', '2', '2001-08-16 00:00:00');
14. 
15. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
16. createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
17. 程', 27, '1', '0', '2001-06-12 00:00:00');
18. 
19. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
20. createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', 
21. 
22. '0', '2001-05-11 00:00:00');
23. 
24. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
25. createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 
26. 
27. 44, '1', '1', '2001-04-09 00:00:00');
28. 
29. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
30. createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, 
31. 
32. '1', '2', '2001-04-10 00:00:00');
33. 
34. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
35. createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, 
36. 
37. '2', '3', '2001-02-12 00:00:00');
38. 
39. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
40. createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, 
41. 
42. '2', '0', '2001-01-30 00:00:00');
43. 
44. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
45. createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, 
46. 
47. '2', '0', '2000-05-03 00:00:00');
48. 
49. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
50. createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', 
51. 
52. '1', '2001-08-08 00:00:00');
53. 
54. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
55. createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 
56. 
57. 30, '1', '0', '2007-03-12 00:00:00');

索引语法1：创建索引

1. CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( 
2. index_col_name,... ) ;

索引语法2：查看索引

SHOW INDEX FROM table_name ;

索引语法3：删除索引

DROP INDEX index_name ON table_name ;

 

数据准备好了之后，接下来，我们就来完成如下需求：

A. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

CREATE INDEX idx_user_name ON tb_user(name);

B.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

C. 为profession、age、status创建联合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

D. 为email建立合适的索引来提升查询效率。

CREATE INDEX idx_email ON tb_user(email);

Explain执行计划：

Explain或者desc命令获取Mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序

Explain执行计划个字段含义

  1. Id：select查询的序列号，表示查询中执行select字句或者是操作表的顺序（id相同，执行顺序从上到下，id不同，值越大，越先执行）
  2. Select_type：表示select的类型，常见的取值有SIMPLE(简单表，即不使用表连接或子查询，PRIMARY(主查询，即最外层的查询)，UNOIN(UNION中的第二个或者后面的查询语句)，SUBQUERY(where之后包含了子查询))
  3. Type：表示连接类型，性能由好到差的连接类型为：NULL , System , eq_ref , ref , range , index , all
  4. Possible_key:显示可能应用在这张表的索引，一个或多个
  5. Key：实际使用的索引，如果为null，则没有使用索引
  6. Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，再不损失精确性的前提下，长度越短越好
  7. Rows：mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
  8. Filtered：返回结果的行数占需读取行数的百分比，filtered的值越大越好

索引失效情况：

  1. 索引列运算：不要在索引列上进行运算操作，索引将失效
  2. 字符串不加引号：索引将失效，也就是隐式转换
  3. 模糊查询：如果字段头部使用模糊查询，索引失效
  4. Or连接的条件：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么所涉及的索引都不会被使用到
  5. 数据分布影响：如果mysql评估使用索引比全表更慢，则不使用索引
  6. 最左前缀法则：对于联合索引没有遵循最左前缀法则

索引失效情况1：索引列运算

A. 当根据phone字段进行等值匹配查询时, 索引生效。

explain select * from tb_user where phone = '17799990015';

B. 当根据phone字段进行函数运算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

索引失效情况2：字符串不加引号

1，phone不存在隐式转换，索引生效

explain select * from tb_user where phone = '17799990015';

2，phone存在隐式转换，索引失效

explain select * from tb_user where phone = 17799990015;

索引失效情况3：模糊查询

由于下面查询语句中，都是根据profession字段查询，符合最左前缀法则，联合索引是可以生效的， 我们主要看一下，模糊查询时，%加在关键字之前，和加在关键字之后的影响。

explain select * from tb_user where profession like '软件%';

explain select * from tb_user where profession like '%工程';

explain select * from tb_user where profession like '%工%';

经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效

索引失效情况4：Or连接的条件

explain select * from tb_user where id = 10 or age = 23;

explain select * from tb_user where phone = '17799990017' or age = 23;

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

然后，我们可以对age字段建立索引。

create index idx_user_age on tb_user(age);

最终，我们发现，当or连接的条件，左右两侧字段都有索引时，索引才会生效。

索引失效情况5：如果MySQL评估使用索引比全表更慢，则不使用索引。

索引失效情况6：

最左前缀法则：

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。

而且中间不能跳过某一列，否则该列后面的字段索引将失效。 接下来，我们来演示几组案例，看一下 具体的执行计划：

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '软件工程' and age = 31;

explain select * from tb_user where profession = '软件工程';

以上的这三组测试中，我们发现只要联合索引最左边的字段 profession存在，索引就会生效，只不 过索引的长度不同。

explain select * from tb_user where age = 31 and status = '0';

explain select * from tb_user where status = '0';

而通过上面的这两组测试，我们也可以看到索引并未生效，原因是因为不满足最左前缀法则，联合索引 最左边的列profession不存在。

接下来再看一个sql：

explain select * from tb_user where profession = '软件工程' and status = '0';

上述的SQL查询时，存在profession字段，最左边的列是存在的，索引满足最左前缀法则的基本条 件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效。

补充：is null 与 is not null 操作是否走索引。

执行如下两条语句 ：

explain select * from tb_user where profession is null;

explain select * from tb_user where profession is not null;

接下来，我们做一个操作将profession字段值全部更新为null。

update tb_user set profession = null

然后，再次执行上述的两条SQL，查看SQL语句的执行计划。

最终我们看到，一模一样的SQL语句，先后执行了两次，结果查询计划是不一样的，为什么会出现这种 现象，这是和数据库的数据分布有关系。查询时MySQL会评估，走索引快，还是全表扫描快，如果全表 扫描更快，则放弃索引走全表扫描。 因此，is null 、is not null是否走索引，得具体情况具体 分析，并不是固定的。

文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
数据库
存储
关键词：
云数据库 RDS MySQL 版索引
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
张谦谦
目录
相关文章
德国都芳最好
|
4月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中，需要根据具体的业务需求和查询模式，综合运用索引策略和查询性能调优方法，不断地测试和优化，以提高MySQL数据库的查询性能。
德国都芳最好
398 66
互联网课堂
|
3月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN：指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询，帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标，如 `id`、`select_type`、`table`、`type`、`key` 等，并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例，展示了如何通过创建合适索引和调整查询语句来提升查询性能。
互联网课堂
574 9
阿里云开发者
|
2月前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
阿里云开发者
309 80
东阳马生架构
|
1月前
|
缓存 算法 关系型数据库
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念，包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序（Using filesort）的区别及优化方法、以及单表和多表查询的索引优化策略。
东阳马生架构
94 22
MySQL底层概述—8.JOIN排序索引优化
东阳马生架构
|
1月前
|
SQL 存储 关系型数据库
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则，涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例，包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排，以及通过辅助索引来优化特定查询场景。总结了设计索引的原则，如尽量包含where、order by、group by中的字段，选择离散度高的字段作为索引，限制索引数量，并针对频繁查询的低基数字段进行特殊处理等。
东阳马生架构
73 18
MySQL原理简介—9.MySQL索引原理
东阳马生架构
|
1月前
|
存储 关系型数据库 MySQL
MySQL底层概述—6.索引原理
本文详细回顾了：索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
东阳马生架构
82 11
MySQL底层概述—6.索引原理
技术自由圈/原疯狂创客圈
|
5月前
|
存储 关系型数据库 MySQL
阿里面试：为什么要索引？什么是MySQL索引？底层结构是什么？
尼恩是一位资深架构师，他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构，主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型，如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树，它能够有效支持范围查询和顺序遍历，同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点，并提供了多个面试题及其解答，帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
技术自由圈/原疯狂创客圈
180 6
千寻简
|
2月前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引，说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时去指定），比如以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快！
千寻简
127 22
MySQL秘籍之索引与查询优化实战指南
阿里云开发者
|
2月前
|
存储 关系型数据库 MySQL
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构，如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧，以及如何避免索引误用，提高数据库性能。
阿里云开发者
62 11
蓝易云
|
2月前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)？
通过这些内容的详细介绍和实际案例分析，希望能帮助您深入理解索引合并及其在MySQL中的
蓝易云
189 10

热门文章

最新文章

  • 1
    基于 Flink CDC YAML 的 MySQL 到 Kafka 流式数据集成
  • 2
    如何实现 MySQL 的读写分离？
  • 3
    ELK实现nginx、mysql、http的日志可视化实验
  • 4
    如何排查和解决PHP连接数据库MYSQL失败写锁的问题
  • 5
    MySQL底层概述—7.优化原则及慢查询
  • 6
    MySQL底层概述—10.InnoDB锁机制
  • 7
    MySQL 主从复制
  • 8
    如何解决 MySQL 数据库服务器 CPU 飙升的情况
  • 9
    MySQL底层概述—8.JOIN排序索引优化
  • 10
    京东面试：MySQL MVCC是如何实现的？如何通过MVCC实现读已提交、可重复读隔离级别的？
  • 1
    MySQL8官方YUM仓库使用指南
    27
  • 2
    mysql的undo log、redo log、bin log、buffer pool
    15
  • 3
    MySQL生产环境迁移至YashanDB数据库深度体验
    18
  • 4
    使用崖山YMP 迁移 Oracle/MySQL 至YashanDB 23.2 验证测试
    12
  • 5
    mysql的undo log、redo log、bin log、buffer pool
    27
  • 6
    ThinkPHP框架show columns引发mysql性能问题
    30
  • 7
    seatunnel配置mysql2hive
    75
  • 8
    ELK实现nginx、mysql、http的日志可视化实验
    164
  • 9
    MySQL：Prepared Statement 预处理语句
    23
  • 10
    MySQL：CTE 通用表达式
    20

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    获取百炼API-KEY