索引失效情况

最新推荐文章于 2024-07-12 16:35:20 发布

墙上的向日葵qwq

最新推荐文章于 2024-07-12 16:35:20 发布

阅读量149

点赞数

分类专栏：学习文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/Getsunflowers/article/details/127167760

版权

学习专栏收录该内容

9 篇文章 0 订阅

订阅专栏

##索引优化与查询优化

##创建数据库
CREATE DATABASE index_optimize;

##建表
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stuno` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `classId` int DEFAULT NULL,
  PRIMARY KEY (`id`)
	#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES 't_class' (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `class` (
  `id` int NOT NULL AUTO_INCREMENT,
  `className` varchar(30) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  `monitor` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


##创建函数

##生成随机字符串
CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb3
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 DEFINER=`root`@`%` FUNCTION `rand_num`(from_num INT,to_num INT) RETURNS int
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
	RETURN i;
END


##创建过程

##插入student表的过程
CREATE DEFINER=`root`@`%` 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


##插入class表的过程
CREATE DEFINER=`root`@`%` 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


##调用存储过程
CALL insert_class(10000); #班级表插入一万条
CALL insert_stu(100000,500000); #学生表插入五十万条




#1、全值匹配

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';

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、最左前缀

EXPLAIN SELECT SQL_NO_CACHE * from student where age = 30 AND `name` = 'abcd';  #联合索引失效，只有age的单列索引生效

EXPLAIN SELECT SQL_NO_CACHE * from student where classId = 4 AND `name` = 'abcd';  #索引失效

EXPLAIN SELECT SQL_NO_CACHE * from student where classId = 4 AND age = 30 AND    `name` = 'abcd';  #联合索引生效



#3、计算、函数、类型转换导致索引失效

CREATE index idx_name on student(`name`);

EXPLAIN SELECT SQL_NO_CACHE * from student where name like 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * from student where left(name,3) = 'abc';  #索引失效



#4、联合索引中   范围条件右侧的列索引失效  
#例：index(age,classId,name)  此索引中如果classId是范围条件，那么name的索引会失效，与语句中写的顺序无关，优化器会优化
 
EXPLAIN SELECT SQL_NO_CACHE * from student where age = 30 and classId > 20 and `name` = 'abc';


#5、 !=  <> 会导致索引
EXPLAIN SELECT SQL_NO_CACHE * from student where name = 'abc';


#6、 is null可以使用到索引   is not null 会使索引失效
EXPLAIN SELECT SQL_NO_CACHE * from student where name is null;
EXPLAIN SELECT SQL_NO_CACHE * from student where name is not null; #索引失效


#7、like以%开头 索引失效
EXPLAIN SELECT SQL_NO_CACHE * from student where name like '%abc';

#8、or 前后存在非索引的列，索引失效
EXPLAIN SELECT SQL_NO_CACHE * from student where name = 'abc' or classId = 10; # name 和 classId 任意一个列上没有索引则语句不走索引