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

Mysql索引失效情况

2023-07-25 55
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 官方介绍索引是帮助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 版索引
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
张谦谦
目录
相关文章
请回答1024
|
6天前
|
存储 关系型数据库 MySQL
Mysql索引总结（1）
Mysql索引总结（1）
请回答1024
25 0
请回答1024
|
6天前
|
存储 关系型数据库 MySQL
MySQL 索引的10 个核心要点
MySQL 索引的10 个核心要点
请回答1024
21 0
toKeep
|
6天前
|
SQL 存储 关系型数据库
MySQL索引及事务
MySQL索引及事务
toKeep
25 2
飞川撸码
|
6天前
|
SQL 存储 关系型数据库
必知的 MySQL 索引失效场景【包括实践验证】，别再踩坑了！（下）
必知的 MySQL 索引失效场景【包括实践验证】，别再踩坑了！
飞川撸码
24 2
飞川撸码
|
6天前
|
SQL 关系型数据库 MySQL
必知的 MySQL 索引失效场景【包括实践验证】，别再踩坑了！（上）
必知的 MySQL 索引失效场景【包括实践验证】，别再踩坑了！
飞川撸码
21 2
飞川撸码
|
6天前
|
NoSQL 关系型数据库 MySQL
B+树 和 跳表 的结构及区别，不同的用途【mysql的索引为什么使用B+树而不使用跳表？】
B+树 和 跳表 的结构及区别，不同的用途【mysql的索引为什么使用B+树而不使用跳表？】
飞川撸码
22 2
yusheng_xyb
|
6天前
|
存储 算法 关系型数据库
MySQL索引详解
MySQL索引详解
yusheng_xyb
15 0
菜菜的后端私房菜
|
6天前
|
存储 SQL 关系型数据库
完蛋！😱 我被MySQL索引失效包围了！
完蛋！😱 我被MySQL索引失效包围了！
菜菜的后端私房菜
9 0
菜菜的后端私房菜
|
6天前
|
SQL 存储 关系型数据库
MySQL的3种索引合并优化⭐️or到底能不能用索引?
MySQL的3种索引合并优化⭐️or到底能不能用索引?
菜菜的后端私房菜
9 0
菜菜的后端私房菜
|
6天前
|
存储 SQL 关系型数据库
MySQL索引，看这一篇就够了！
MySQL索引，看这一篇就够了！
菜菜的后端私房菜
19 0

热门文章

最新文章

  • 1
    《MySQL 简易速速上手小册》第6章：MySQL 复制和分布式数据库（2024 最新版）
  • 2
    [AIGC] 对比MySQL全文索引，RedisSearch，和Elasticsearch的详细区别
  • 3
    MySQL学习手册（第一部分）
  • 4
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 5
    centos7安装mysql-带网盘安装包
  • 6
    下次老板问你MySQL如何优化时，你可以这样说，老板默默给你加工资
  • 7
    【MySQL】SQL优化
  • 8
    MySQL环境搭建——“MySQL数据库”
  • 9
    Linux联网安装MySQL Server
  • 10
    高性能 MySQL 第四版（GPT 重译）（四）(2)
  • 1
    [MySQL]事务原理之redo log,undo log
    132
  • 2
    [MySQL]SQL优化之索引的使用规则
    44
  • 3
    [MySQL] SQL优化之性能分析
    39
  • 4
    【mysql】—— 用户管理
    40
  • 5
    【mysql】—— 视图
    35
  • 6
    【mysql】—— 表的增删改查
    45
  • 7
    【mysql】—— 表的约束
    43
  • 8
    【MYSQL】—— MySQL 在 Centos 7环境安装
    76
  • 9
    MySQL模糊查询二三事
    29
  • 10
    MySQL的JOIN到底是怎么玩的
    110

    • 相关课程

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

    • 相关电子书

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

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考