【MySQL从入门到精通】【高级篇】(二十六)建了索引就能用么?我看未必。来看看几种索引失效的情况吧

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL从入门到精通】【高级篇】(二十五)EXPLAIN中ref、rows、filtered、Extra字段的剖析 通过前面几篇文章的学习,相信小伙伴们对EXPLAIN命令有了一个更加深入理解。这篇文章我们将来学习索引失效的11种情况。有时候并不是说加了索引,就一定能用上索引,还是要具体情况具体分析。

1. 简介

上一篇文章我们介绍了

【MySQL从入门到精通】【高级篇】(二十五)EXPLAIN中ref、rows、filtered、Extra字段的剖析 通过前面几篇文章的学习,相信小伙伴们对EXPLAIN命令有了一个更加深入理解。这篇文章我们将来学习索引失效的11种情况。有时候并不是说加了索引,就一定能用上索引,还是要具体情况具体分析。

都有哪些维度可以进行数据库调优?简而言之:


索引失效,没有充分利用到索引—索引建立

关联查询太多JOIN (设计缺陷或不得已的需求) — SQL优化

服务器调优及各个参数设置(缓冲,线程数等)---- 调整my.cnf

数据过多 ----分库分表

关于数据库调优的知识点非常分散,不同的DBMS,不同的公司,不同的职位,不同的项目遇到的问题都不尽相同。

虽然SQL查询优化的技术有很多,但是大方向上完全可以分成物理查询优化和逻辑查询优化 两大块。


物理查询优化是通过索引和表连接方式等技术来进行优化,这里重点需要掌握索引的使用。

逻辑查询优化就是通过等价变换提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高。

2. 数据准备

学生表(student)插入50万条数据,班级表(class)插入1万条数据。

2.1. 建表

-- 班级表
CREATE TABLE class(
  id INT NOT NULL AUTO_INCREMENT,
  classname VARCHAR(30) DEFAULT NULL,
  address VARCHAR(40) DEFAULT NULL,
  monitor INT NULL,
 PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 学生表
CREATE TABLE student(
 id INT NOT NULL AUTO_INCREMENT,
 stuno INT NOT NULL,
 name VARCHAR(20) DEFAULT NULL,
 age INT(3) DEFAULT NULL,
 classId INT(11) DEFAULT NULL,
 PRIMARY KEY(id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2.2. 设置参数

命令开启,允许创建函数设置:

set global log_bin_trust_function_creators=1;  #不加global只是当前窗口有效

2.3. 创建函数

保证每条数据都不同

CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i<n DO 
   SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
  SET i=i+1;
  END WHILE;
  RETURN return_str;
END

随机生成班级编号

CREATE FUNCTION `rand_num`(from_num INT,to_num INT) RETURNS int(11)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET i=FLOOR(from_num+RAND()*(to_num-from_num+1));
  RETURN i;
END

2.4. 创建存储过程

创建往student表中插入数据的存储过程

-- 创建往student表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(START INT,max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit=0; #设置手动提交事务
  REPEAT #循环
  SET i=i+1; #赋值
  INSERT INTO student(stuno,name,age,classId) VALUES
  ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
  UNTIL i=max_num
  END REPEAT;
  COMMIT; #提交事务
END //
DELIMITER;

创建往class表中插入数据的存储过程

DELIMITER //
CREATE PROCEDURE insert_class(max_num INT) 
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit=0; #设置手动提交事务
  REPEAT #循环
  SET i=i+1; #赋值
  INSERT INTO class(classname,address,monitor) VALUES
  (rand_string(8),rand_string(10),rand_num(1,100000));
  UNTIL i=max_num
  END REPEAT;
COMMIT;
END //
DELIMITER;

2.5. 调用存储过程

class

-- 执行存储过程,往class表添加1万条数据
CALL insert_class(10000);
CALL insert_stu(100000, 500000);

3. 索引失效的情况

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快了查询的速度,因此索引对查询的速度有着至关重要的影响。


使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。

如果查询时没有使用索引,查询语句就会扫描表中的全部记录。在数据量大的情况下,这样查询的速度会很慢。

大多数情况下都默认采用B+树来构建索引,只是空间列类型的索引使用R树,并且MEMORY表还支持hash索引。


其实用不用索引最终都是优化器说了算,优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer), 它不是基于规则(Rule-BaseOptimizer),也不是基于语义。怎么样开销小就怎么样来。另外,SQL语句是否使用索引,跟数据库版本,

3.1. 索引使用的简单说明

全值匹配的话,如果在字段上添加索引,那么可以使用到索引

最佳左前缀法则:即在MySQL建立联合索引的时会遵循最佳左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

举例:

在student表中建立一个联合索引

CREATE INDEX idx_age_classid_name ON student(age,classId,name);

直接使用 name 字段进行查询的话则不能使用索引,不明白原理的话可以看下【MySQL从入门到精通】【高级篇】(七)设计一个索引&InnoDB中的索引方案,简单的说,就是在构建索引是是从最左边的字段开始构建的。

-- 不能使用到索引
EXPLAIN SELECT * FROM  student WHERE `name`='张三';

能使用索引的情况:

-- 能使用到索引(其中一个字段)
EXPLAIN SELECT * FROM student WHERE age=19 AND `name`='张三';
-- 能使用到索引(其中两个字段)
EXPLAIN SELECT * FROM student WHERE age=19 AND `classId`=111;

从key_len 字段的值就能够验证我们的结论。

结论:MySQL可以为多个字段创建索引,一个索引可以包含16个字段,对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。 如果查询条件中没有使用这些字段中第一个个字段时,多列(或联合)索引不会被使用。

Aibaba《Java开发手册》中指出:索引文件具有B+树的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。


主键插入顺序

对于一个使用InnoDB存储引擎的表来说,在我们没有显式的创建索引时,表中的数据实际上都是存储在聚簇索引的叶子节点的。 而记录又是存储在数据页中的,数据页和记录又是按照主键值从小到大的顺序进行排序,所以我们插入的记录的主键值依次增大的。那我们每插入满一个数据页就换到下一个数据页继续插入,如果我们插入的主键值忽大忽小的话,就比较麻烦了,假设某个数据页存储的记录已经满了,它存储的主键值在1~100之间:

可这个数据页已经满了,在插进来咋办呢?我们需要把当前页面分裂成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着:性能损耗!所以如果我们想尽量避免这样无谓的性能损耗,最好让插入的记录的主键值一次递增,这样就不会发生这样的性能损耗了,所以我们建议:让主键具有AUTO_INCREMENT。

3.2. 计算、函数、类型转换(自动或手动)导致索引失效

在MySQL中对查询字段使用函数,或者对字段进行统计,又或者发生隐式转换的情况下会导致索引失效。

3.2.1. 在字段上使用计算

在查询字段上使用计算时,即使该字段上创建了索引,MySQL也不能使用该索引。举例如下:

-- 创建索引
CREATE INDEX idx_stuno ON student(stuno);
-- 不使用计算,能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE stuno=100007;
-- 使用计算,不能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE stuno+1=100007;

3.2.2. 使用函数

在student表的name字段上创建索引idx_name,查询name字段的前缀是abc开头的字段,第一个查询没有使用函数,第二个查询在查询字段上使用了字段。举例如下:

-- 创建索引
CREATE INDEX idx_name ON student(NAME);
-- 不使用函数,能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` LIKE 'abc%';
-- 使用函数,不能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.`name`,3)='abc';

3.2.3. 类型转换导致索引失效

student 表的name字段的数据类型是varchar类型,正常的话需要传入一个字符串,如果我们传入一个数字给该字段会发生什么呢?

-- 没有类型转换,索引有效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='VcByPC';
-- 有类型转换的情况,索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=111111;

3.3. 范围条件右边的列索引失效

让我们先来看一个例子:

-- 删除其他索引
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_stuno;
-- 创建一个联合索引
CREATE INDEX idx_classid_age_name ON student(classId,age,name);
-- 正常使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId='1211' AND age=10 and NAME='VcByPC';
-- 范围条件右边的列索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId='1211' AND age>10 and NAME='VcByPC';

首先,创建一个名为idx_classid_age_name的联合索引,列的顺序是classId,age,name。

接着两个查询都是用到了这三个列作为查询条件,两者唯一的区别第一个查询是全值匹配, 而第二个查询中 age的查询条件是age>10 是一个范围条件,让我们来看看最终的效果:

从效果图可以看出,两个查询都使用到了联合索引idx_classid_age_name,两者的区别在于key_len字段差别很大,第一个查询的key_len有73=20*3+1+2+5+5,因为 name字段的类型varchar(20),长度为20, 在utf-8中一个字符占用3个字节,那么就是20*3,并且name有可能为空就需要加1,而且name字段是变长的,那么需要1个字节存储长度,存储索引1个字节。而classId字段和age字段都是int类型,utf8中int类型占用4个字节,存储索引信息1个字节,而第二个查询的key_len则只有10。


应用开发中范围查询,例如:金额查询,日期查询往往都是范围查询。应将查询条件放置where语句最后。

3.4. 不等于索引失效

字段的条件是不等于的情况下,在字段上建立的索引会失效。

-- 创建索引
CREATE INDEX idx_stuno ON student(stuno);
-- 等于,能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE stuno=100007;
-- 不等于,不能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE stuno!=100007;

等于查询是精确的等值查询,能直接用上在该字段上建的索引,而不等于查询在是范围查询,需要全表遍历。

3.5. is not null 不能使用索引(is null可以使用索引)

如下在name字段上建立索引之后,一个查询的条件是:name IS NULL,另一个查询的条件是: NAME IS NOT NULL 。

CREATE INDEX idx_name ON student(NAME);
-- IS NULL可以触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE `name` IS NULL;
-- IS NOT NULL不能触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME IS NOT NULL;

结果如上图所示:IS NULL可以触发索引,而IS NOT NULL 则无法触发索引。由此我们可以得出如下结论:最好在设计数据表就将字段设置为 NOT NULL 约束,比如你可以将INT类型的字段,默认值设置为0,将字符串类型的默认值设置为空字符串(’ ')。

同理,在查询中使用 not like 也无法使用索引,导致全表查询。

3.6. like (左匹配能使用索引,右匹配或者全模糊查询不能使用索引)

在使用 LIKE 关键字进行查询的查询语句中,如果匹配字符串的第一个字符为**‘%’,索引就不会起作用,只有’%'** 不在第一个位置,索引才会起作用。

-- 左匹配,能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE `name` LIKE 'abc%';
-- 右匹配,不能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE `name` LIKE '%abc';

在用like进行模糊查询时,建议使用左匹配,禁用全模糊查询,遇到全模糊查询时应该考虑走索引。

3.7. OR 前后存在非索引的列

在WHERE子句中,如果在OR前的条件进行索引,而在OR后的条件列没有进行索引,那么索引会失效,也就是说,OR前后的两个条件中的列都是索引时,查询中才使用索引。


因为OR的含义就是两个只要满足一个即可,因此只有一个条件里进行了索引是没有意义的。只要有条件列没有进行索引,就会进行全表扫描。因此索引也会失效。

-- 未使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE `name`='VcByPC' OR age=10;

总结

本文详细介绍了索引失效的六种情况,其实大家也不用去背诵这几种情况,只需要深入的理解索引的结构就能够想明白。MySQL的InnoDB的索引本质上是一个平衡多叉树,按照索引字段的大小从小到大构建。尤其适合唯一性高的字段的全值匹配查询。

综上所述,建议如下:


对于单列索引,尽量选择针对当前query过滤性更好的索引。

在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。

在选择组合索引的时候,尽量能够包含当前query中的where子句中更多字段的索引。

在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面。

总之,书写SQL语句时,尽量避免造成索引失效的情况。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
存储 SQL 关系型数据库
初识mysql索引 - 小白篇
初识mysql索引 - 小白篇
|
8天前
|
监控 关系型数据库 MySQL
MySQL索引的创建步骤是什么?
【6月更文挑战第15天】MySQL索引的创建步骤是什么?
16 5
|
5天前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
7天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
12 2
|
8天前
|
存储 关系型数据库 MySQL
MySQL索引的限制
【6月更文挑战第15天】MySQL索引的限制
9 3
|
6天前
|
存储 关系型数据库 MySQL
MySQL索引18连问,谁能顶住
MySQL索引18问概览: 1. 索引是提升查询速度的数据结构,如书的目录。 2. 索引类型包括B+tree、Hash、Full-text、R-Tree等,B+tree擅长范围查询,Hash擅长等值比较。 3. 主键索引唯一且不可为空,每表只能一个;唯一索引允许唯一值,可为空。 4. 聚簇索引数据与索引顺序一致,非聚簇索引存储指针。
180 0
MySQL索引18连问,谁能顶住
|
8天前
|
SQL 存储 监控
精通MySQL:从入门到精通的全方位指南
第一章:MySQL入门 1.1 MySQL概述 简要介绍MySQL的历史、特点和应用场景
|
2天前
|
SQL 关系型数据库 MySQL
MySQL数据库数据模型概念入门及基础的SQL语句2024
MySQL数据库数据模型概念入门及基础的SQL语句2024
7 0
|
3天前
|
存储 关系型数据库 MySQL
索引的威力--记一次MySQL存储过程优化
在MySQL存储过程中,一个`INSERT INTO SELECT`语句起初执行超过130秒,优化后,执行时间降低到1秒内,实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询,最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法,并讨论了单列索引与组合索引的优缺点。作者强调,随着数据量增加,索引对于查询性能的重要性,计划未来采用读写分离来进一步优化处理大量插入和查询的场景。
|
4天前
|
SQL 关系型数据库 MySQL
Mysql从入门到精通——Mysql知识点总结(基础篇)
Mysql从入门到精通——Mysql知识点总结(基础篇)