Mysql索引失效情况

阳光穿透心脏的1/2处 2024-04-08 13:12 79阅读 0赞

> **目录**
> 
> Mysql索引概述：
> 
> 索引优点：
> 
> 索引缺点：
> 
> 索引语法1：创建索引
> 
> 索引语法2：查看索引
> 
>  索引语法3：删除索引
> 
> Explain执行计划个字段含义
> 
> 索引失效情况：
> 
> 索引失效情况1：索引列运算
> 
> 索引失效情况2：字符串不加引号
> 
> 索引失效情况3：模糊查询
> 
> 索引失效情况4：Or连接的条件
> 
> 索引失效情况6：

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

> ## 索引优点： ##
> 
> 1.  提高检索效率，降低数据库的io成本
> 2.  通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗
> 
> ## 索引缺点： ##
> 
> 1.  索引需要占用物理空间
> 2.  索引提高了查询效率但是降低了增删改的效率
> 
> Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：
> 
> 1.  B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
> 2.  Hash索引：底层数据结构使用hash表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
> 3.  R-Tree空间索引：使用较少
> 4.  全文索引：是一种通过建立倒排索引，快速匹配文档的方式

> 创建数据用户表

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

> 添加测试数据

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', 
    
    '6', '2001-02-02 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, 
    
    '1', '0', '2001-03-05 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', 
    
    '2', '2002-03-02 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, 
    
    '1', '0', '2001-07-02 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, 
    
    '2', '1', '2001-04-22 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', 
    
    '0', '2001-02-07 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, 
    
    '2', '0', '2001-02-08 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 
    
    '1', '5', '2001-05-23 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, 
    
    '1', '0', '2001-09-18 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
    化', 27, '1', '2', '2001-08-16 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
    程', 27, '1', '0', '2001-06-12 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', 
    
    '0', '2001-05-11 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 
    
    44, '1', '1', '2001-04-09 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, 
    
    '1', '2', '2001-04-10 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, 
    
    '2', '3', '2001-02-12 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, 
    
    '2', '0', '2001-01-30 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, 
    
    '2', '0', '2000-05-03 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', 
    
    '1', '2001-08-08 00:00:00');
    
    INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 
    createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 
    
    30, '1', '0', '2007-03-12 00:00:00');

> ## 索引语法1：创建索引 ##

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( 
    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是否走索引，得具体情况具体 分析，并不是固定的。