我的编程空间,编程开发者的网络收藏夹
学习永远不晚

SQL索引失效的11种情况详析

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

SQL索引失效的11种情况详析

数据库调优的大致方向:

  • 索引失效,没有充分利用到索引——建立索引
  • 关联查询太多join——sql优化
  • 服务器调优及各个参数设置——my.cnf
  • 数据过多——分库分表

sql查询优化技术有很多,大体分为物理查询优化逻辑查询优化:

  • 物理查询优化:通过索引和表连接方式等技术进行优化
  • 逻辑查询优化:通过SQL等价变换提升查询效率,就是换一种sql写法

数据准备:

CREATE DATABASE atguiguDB2;
USE atguigudb2;

#############    class 表    #################
CREATE TABLE `class` (
`id` INT(11) 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;

#############    student 表    #################
CREATE TABLE `student` (
`id` INT(11) 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`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#################################

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

#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 //
DELIMITER ;
#假如要删除
#drop function rand_string;

#用于随机产生多少到多少的编号
DELIMITER //
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 //
DELIMITER ;
#假如要删除
#drop function rand_num;

#创建往stu表中插入数据的存储过程
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 ;

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

#执行存储过程,往stu表添加50万条数据
CALL insert_stu(100000,500000);

SELECT COUNT(*) FROM class;
SELECT COUNT(*) FROM student;

############################### 删除索引的存储过程 ########################
DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;
# 执行存储过程
CALL proc_drop_index("dbname","tablename");

索引失效案例

【1】. 全值匹配

# 【1】. 全值匹配
# student表,主键id,此时无索引,耗时大
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND NAME = 'abcd';

# 注:SQL_NO_CACHE 不使用查询缓存

# 建立索引
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);	
# 此时第三条查询语句默认使用最后一条索引,而不是前两个

【2】. 最佳左前缀法则

# 【2】. 最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.name = 'abcd';	
# 查age&name,用age的索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid = 1 AND student.name = 'abcd';	
# 查classid&name,classid在前,有索引的话先找classid相同的,再找name,
#但现在没有这样的索引,idx_age_classid_name的字段顺序是先找age,所以不符合,所以此时不能用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid = 4 AND student.age = 30 AND student.name = 'abcd';	
#idx_age_classid_name 联合索引中所有字段均出现,可以使用该索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.name = 'abcd';
# 现在,删除idx_age和idx_age_classid,发现用到idx_age_classid_name,而key_len=5,即只用到age字段,int(4)+null(1)
#因为索引完age后没有classid了,不能再查找到name

SQL索引失效的11种情况详析

【3】. 主键插入顺序

在定义表时,让主键auto_increment,否则,插入一条数据时可能会移动大量数据。

如,往 1 5 8 10 15 … 100 中插9,会放在8 10 中间,因为索引默认升序排列。那么10往后的数据都要挪动,页不够时又要放到下一页,每插一条数据都这样挪一次,开销很大

我们自定义的主键列id 拥有AUTO_INCREMENT 属性,在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。

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

# 【4】. 计算、函数、类型转换(自动或手动)导致索引失效
##### 例1:
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';	#更好,能够使用上索引
# type=range 使用了索引中的排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';	# left(text,num_chars):截取左侧n个字符
# type = all 全表的访问
# 该语句的执行过程:针对每一条数据,一个一个取出,先作用一遍函数,再拿函数结果与abc对比,用不上b+树

CREATE INDEX idx_name ON student(NAME);

##### 例2:
CREATE INDEX idx_sno ON student(stuno);
EXPLAIN SELECT SQL_NO_CACHE id,stuno,NAME FROM student WHERE stuno+1 = 900001; 	# type = all 需要做运算,无法直接用索引找值

EXPLAIN SELECT SQL_NO_CACHE id,stuno,NAME FROM student WHERE stuno = 900000; 	# type = ref

【5】. 类型转换导致索引失效

# 【5】. 类型转换导致索引失效
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123;	# 这里使用了隐式转换
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME='123'; 	# name本身就是字符串类型

【6】. 范围条件右边的列索引失效

# 【6】. 范围条件右边的列索引失效 ( > < >= <= between 等)
SHOW INDEX FROM student;
CALL proc_drop_index('atguigudb2','student');

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';	# 这三个and先写谁无所谓,优化器会调优
# key_len = 10, age=5,classId=5,name用不上。classId 是范围,索引右侧的name用不上

# 改写索引:
CREATE INDEX idx_age_name_cid ON student(age,NAME,classId); 	#把需要排序的classid放到最后
# 此时在执行上面的语句,就使用了这个索引,key_len=73

创建的联合索引中,必须把涉及到范围的字段写在最后。

【7】. 不等于(!= 或者<>)索引失效

# 【7】. 不等于(!= 或者<>)索引失效
CREATE INDEX idx_name ON student(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';	# 索引失效 索引查的是等于

【8】. is null可以使用索引,is not null无法使用索引

# 【8】. is null可以使用索引,is not null无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;	# type=ref 相当于等于某个值
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;	# 索引失效 相当于不等于

SQL索引失效的11种情况详析

【9】. like以通配符%开头索引失效

# 【9】. like以通配符%开头索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';	# 可用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab';	# type = all 索引失效

页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

【10】. OR 前后存在非索引的列,索引失效

# 【10】. OR 前后存在非索引的列,索引失效 

CALL proc_drop_index('atguigudb2','student');
SHOW INDEX FROM student;
# 创建一个age的索引
CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;	# 未使用索引,索引+全表扫描->全表扫描
# 再加一个字段的单独索引
CREATE INDEX idx_cid ON student(classid);
# 再执行上条语句,此时 type = index_merge ,key = idx_age,idx_cid。

【11】. 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

总结

SQL索引失效的11种情况详析

到此这篇关于SQL索引失效的11种情况详析的文章就介绍到这了,更多相关SQL索引失效内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

SQL索引失效的11种情况详析

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

SQL索引失效的11种情况详析

目录索引失效案例【1】. 全值匹配【2】. 最佳左前缀法则【3】. 主键插入顺序【4】. 计算、函数、类型转换(自动或手动)导致索引失效【5】. 类型转换导致索引失效【6】. 范围条件右边的列索引失效【7】. 不等于(!= 或者)索引失
2023-03-09

mysql索引失效的几种情况

常见情况:1、使用函数或运算;2、隐式类型转换;3、使用不等于(!=或);4、使用like操作符,并以通配符开头;5、or条件;6、null值;7、索引选择性低;8、复合索引的最左前缀原则;9、优化器决策;10、force index和ig
mysql索引失效的几种情况
2024-02-22

索引失效的情况

正常情况索引使用完全如下所示。 1、当所建索引是联合索引时,查询条件要按照联合索引的顺序进行,否则会出现索引失效的情况。 ①当联合索引的第一个字段不出现在条件中,而且where后面不包括所有索引列,俺么所有的索引都会失效。 ②当联合索引的第一个字段出现在条
索引失效的情况
2016-12-26

SQL索引失效的情况有哪些

这篇文章主要介绍了SQL索引失效的情况有哪些的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇SQL索引失效的情况有哪些文章都会有所收获,下面我们一起来看看吧。数据库调优的大致方向:索引失效,没有充分利用到索引&m
2023-07-05

Mysql索引失效的情况

前提:建立了一个employee表,同时建立了一个组合索引lastName,gender 。 1.最常说的like匹配                            例1 explain select * from employee where las
Mysql索引失效的情况
2016-01-15

oracle索引失效的情况有哪几种

有以下几种情况可能导致Oracle索引失效:1. 索引列上的数据分布不均匀:如果索引列上的数据分布不均匀,即某些值出现的频率非常高,而其他值出现的频率非常低,那么索引的效果可能会下降甚至失效。2. 索引列上的数据被频繁修改:如果索引列上的数
2023-10-08

MySQL导致索引失效的几种情况

目录一、准备工作二、索引失效规则1.优先使用联合索引2.最左匹配原则3.范围条件右边的列索引失效4.计算、函数导致索引失效5.类型转换导致索引失效6.不等于(!= 或者)索引失效7.is null可以使用索引,is not null无法
2022-06-23

MySQL索引失效的几种情况小结

目android录1.最左前缀原则2. 计算、函数使索引失效3. 类型转换导致索引失效4. 不等于(或!=)导致索引失效5. is not null /is null可能不走索引,也可以走索引6. 模糊匹配Like以%开头7. OR前后
2023-03-20

MySQL索引失效的几种情况汇总

一.索引不存储null值 更准确的说,单列索引不存储null值,复合索引不存储全为null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描。 为什么索引列不能存Nul
2022-05-17

Mysql索引查询失效的情况

一:不在索引上使用函数,计算等 在kq_time上增加了一个索引,见图一 我们使用date()函数进行查询,见图二 图二根据kq_time字段查询并没有使用索引,我们可以根据kq_time换一种写法,他就可以走索引了,见图三 查询条件是一样的,图三的写法
Mysql索引查询失效的情况
2021-05-22

MySQL索引优化之不适合构建索引及索引失效的几种情况详解

目录结论不建议建立索引的场景索引失效的场景小结结论具体案例下文有详尽描述不适合建立索引的场景:数据量比较小的表不建议建立索引有大量重复数据的字段上不建议建立索引(类似:性别字段)需要进行频繁更新的表不建议建立索引where、group
2022-07-29

SqlServer索引失效的情况有哪些

表数据量过小或者分布不均匀,导致SQL Server查询优化器认为使用索引扫描的代价大于全表扫描,从而选择全表扫描而不是使用索引。索引列上的数据不均匀,比如索引列上存在大量重复值的情况,会导致查询优化器认为使用索引扫描的代价较大。索引列上存
SqlServer索引失效的情况有哪些
2024-04-16

MySQL索引失效的情况有哪些

这篇文章主要讲解了“MySQL索引失效的情况有哪些”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL索引失效的情况有哪些”吧!1.最左前缀原则在MySQL数据库中,联合索引遵守最左前缀
2023-07-05

innodb索引失效的情况有哪些

数据量过大:当数据表中的数据量非常大时,索引可能会失效,因为MySQL可能会选择不使用索引而进行全表扫描,这样会导致查询性能下降。数据分布不均匀:如果数据分布不均匀,即索引中的数据不平衡地分布在不同的页中,MySQL可能会选择不使用索引而进
innodb索引失效的情况有哪些
2024-03-14

mysql引发索引失效的情况有哪些

这篇文章主要讲解了“mysql引发索引失效的情况有哪些”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“mysql引发索引失效的情况有哪些”吧!1、在查询条件中计算索引列的使用函数或操作。若已建
2023-06-20

MySQL细数发生索引失效的情况

目录索引的存储结构不合理的模糊查询条件对索引使用函数对索引进行表达式计算对索引使用隐式转换联合索引非最左匹配where子句中的or总结索引的存储结构首先了解一下索引的存储结构,知道了索引的存储结构,才方便我们更好地理解索引失效的问题。索
2022-07-13

编程热搜

目录