MySQL索引优化

·  阅读 474

环境搭建

建表语句


 CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 ceo INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)

复制代码

创建函数

创建函数,假如报错:This function has none of DETERMINISTIC......

由于开启过慢查询日志,因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

  • show variables like 'log_bin_trust_function_creators';
  • set global log_bin_trust_function_creators=1;

随机产生字符串

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 $$
#假如要删除
#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$$ 
#假如要删除
#drop function rand_num;
 
SELECT rand_num(50,100);
复制代码

创建调用存储过程

往emp表中插入数据

DELIMITER $$
CREATE PROCEDURE insert_emp(START INT, max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	#set autocommit =0 把autocommit设置成0  
	SET autocommit = 0;   
		REPEAT  
			SET i = i + 1;  
			INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50),rand_num(1,10000));  
			UNTIL i = max_num  
		END REPEAT; 
	COMMIT;  
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;
 
#执行存储过程,往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000); 
复制代码

往dept表中插入数据

#执行存储过程,往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(max_num INT)
	BEGIN  
	DECLARE i INT DEFAULT 0;   
	SET autocommit = 0;    
		REPEAT  
			SET i = i + 1;  
			INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
			UNTIL i = max_num  
		END REPEAT;  
	COMMIT;  
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;
 
#执行存储过程,往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000); 
复制代码

删除索引

删除指定数据库中表除主键索引之外的所有索引。

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=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$$
 
#CALL proc_drop_index("dbname","tablename");
复制代码

单表使用索引及常见索引失效

案例

全值匹配

image-20201026160742069

image-20201026160818953

添加使用索引CREATE INDEX idx_age ON emp(age);

image-20201026160930866

image-20201026161012521

起到了优化效果。删除相关索引。CALL proc_drop_index("mydb","emp");

image-20201026161140190

image-20201026161158790

添加索引CREATE INDEX idx_age_deptid ON emp(age,deptid);

image-20201026161243195

image-20201026161321021

image-20201026161336661

查询时间大幅度减少到0.001!!删除相关索引。CALL proc_drop_index("mydb","emp");

image-20201026161508829

image-20201026161543116

建立索引CREATE INDEX idx_age_deptid_name ON emp(age,deptid,name);

image-20201026161619566

image-20201026161645337

image-20201026161701444

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age = 30;
CREATE INDEX idx_age ON emp(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age = 30 AND deptid = 4;
CREATE INDEX idx_age_deptid ON emp(age,deptid);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age = 30 AND deptid = 4 AND emp.`name` = 'abcd';
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,name);
复制代码

最佳左前缀法则

image-20201026204615717

将三个条件调换顺序依旧能是复合索引生效!这是因为mysql优化器把我们的sql给自动优化调整顺序了。

age条件放到最前后面接着name条件

image-20201026204713979

却发现此时的key_len=5也就是只有age索引生效啦!再次改变

image-20201026204905702

复合索引全部失效!!

这是为什么呢?这就是因为最佳左前缀法则

image-20201026204005730

第一种情况mysql优化器把三种情况给优化了所以条件依旧为age,deptid,name

第二种情况条件为age,name缺少了deptid,所以不能找到name。也就只有age生效

第三种情况条件为deptid,name因为缺少了age所以根本无法进行查找。全部失效

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

## 最佳左前缀法则
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,name);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid = 4 AND emp.age = 30 AND emp.`name` = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age = 30 AND emp.`name` = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid = 4 AND emp.`name` = 'abcd';
复制代码

使用函数索引失效

image-20201026210633223

image-20201026210649062

两个sql语句的作用是一样的。我们加上索引。CREATE INDEX idx_name ON emp(name);

image-20201026210743554

此时第一条sql语句,索引优化已经生效啦

image-20201026211049438

而第二条sql语句依旧没有被优化,这是为什么呢??

image-20201026211125572

这是因为使用了函数导致索引优化失效!

不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描!

存储引擎不能使用索引中范围条件右边的列

image-20201027135910260

image-20201027140000653

我们照样添加索引CREATE INDEX idx_age_deptId_name ON emp(age,deptId,name);

image-20201027140126014

虽然查询时间大幅度减少了,但是还是没有先前使用复合索引那么快!这是为什么呢??

image-20201027140227619

可以看出key_len=10命中的是age,depId。而name是失效的!

这就是存储引擎不能使用索引中范围条件右边的列(age,deptId,name),emp.deptId > 20导致name失效。

所以我们要把使用范围条件的列在创建索引时放在最后面CREATE INDEX idx_age_name_deptId ON emp(age,name,deptId);

image-20201027140710990

image-20201027140749751

此时就全部使用上了。

## 存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age = 30 AND emp.deptId > 20 AND emp.name = 'abc';
CREATE INDEX idx_age_deptId_name ON emp(age,deptId,name);

CREATE INDEX idx_age_name_deptId ON emp(age,name,deptId);
复制代码

mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

image-20201027141150374

image-20201027141206979

添加索引CREATE INDEX idx_name ON emp(name);

image-20201027141300403

可以看到type依旧为all,并没有起到优化效果。

mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

## mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name` <> 'abc';
CREATE INDEX idx_name ON emp(name);
复制代码

is null是可以使用索引的,is not null 无法使用索引。

image-20201027141740024

image-20201027141755145

添加索引CREATE INDEX idx_age ON emp(age);

image-20201027141852342

image-20201027141919814

is null是可以使用索引的,is not null 无法使用索引。

## `is null`是可以使用索引的,`is not null `无法使用索引。
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;
CREATE INDEX idx_age ON emp(age);
复制代码

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

添加索引CREATE INDEX idx_name ON emp(name);

image-20201027142306879

可以看到依旧没有起到优化效果!这是因为导致查询条件不确定,必须要全部查找,所以导致失效。

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

## like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name` LIKE '%abc%';
CREATE INDEX idx_name ON emp(name);
复制代码

字符串不加单引号索引失效

image-20201027142735291

添加索引CREATE INDEX idx_name ON emp(name);

image-20201027142815606

依旧没有生效,这是因为emp.name=123;name为varchar类型,发生了类型自动转换而导致失效的。所以我们JavaBean对象要和数据库保存一致,防止出现类型自动转换。

## 字符串不加单引号索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name`=123;
CREATE INDEX idx_name ON emp(name);
复制代码

总结

  1. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
  2. 存储引擎不能使用索引中范围条件右边的列
  3. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
  4. is not null 也无法使用索引,但是is null是可以使用索引的
  5. like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
  6. 字符串不加单引号索引失效(出现自动类型转换)

假设index(a,b,c)

Where语句索引是否被使用
where a = 3Y,使用到a
where a = 3 and b = 5Y,使用到a,b
where a = 3 and b = 5 and c = 4Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4N
where a = 3 and c = 5使用到a, 但是c不可以,b中间断了
where a = 3 and b > 4 and c = 5使用到a和b, c不能用在范围之后,b断了
where a is null and b is not nullis null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3不能使用索引
where abs(a) =3不能使用 索引
where a = 3 and b like 'kk%' and c = 4Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4Y,只用到a
where a = 3 and b like '%kk%' and c = 4Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4Y,使用到a,b,c

建议(query过滤性就是类似身份证号这些唯一的,能够快速过滤其他的性质)

  1. 对于单键索引,尽量选择针对当前query过滤性更好的索引
  2. 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
  3. 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
  4. 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
  5. 书写sql语句时,尽量避免造成索引失效的情况

关联查询

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
复制代码

image-20201027151044101

首先给book表建立索引ALTER TABLE bookADD INDEX Y (card);

image-20201027151124361

索引优化生效,再给class表建立索引ALTER TABLE classADD INDEX Y (card);

image-20201027151228196

虽然索引建立成功了,但是还是没有达到对class对优化效果。

image-20201027151413449

这是因为此时的class表是驱动表,book表是被驱动表,给驱动表建立索引是避免不了全表扫描的。所以我们只能给被驱动表建立索引优化。

我们改成使用inner Join连接两表查询。

image-20201027151746222

id相同,按照由上至下的顺序执行,所以book 变成了驱动表,class 表变成了被驱动表。class的索引优化生效。

  1. 保证被驱动表的join字段已经被索引
  2. left join 时,选择小表作为驱动表,大表作为被驱动表。
  3. inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
  4. 虚拟表无法建立索引
  5. 子查询尽量不要放在被驱动表,有可能使用不到索引。
  6. 能够直接多表关联的尽量直接关联,不用子查询。

子查询优化

SELECT * FROM t_emp a WHERE a.id NOT IN
(SELECT b.ceo FROM t_dept b where b.ceo IS NO NULL);

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.id = b.ceo 
WHERE b.id IS NULL;
复制代码

尽量不要使用not in或者 not existsleft outer join on xxx is null 替代

排序分组优化

order by

无过滤 不索引

image-20201027160226496

我们加上CREATE INDEX idx_age_deptId_name on emp(age,deptId,name);

image-20201027160302656

还是无法进行优化,此时我们在后面加上limit进行过滤

image-20201027160415692

发现此时的优化效果已经出来了。

所以我们使用order by语句时要对其进行过滤。

#无过滤 不索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptId;

EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptId LIMIT 10;

CREATE INDEX idx_age_deptId_name on emp(age,deptId,`name`);
复制代码

image-20201027162322718

CREATE INDEX idx_age_deptId_name on emp(age,deptId,`name`);

EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId;##用上索引

EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId,`name`;##用上索引

EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId,empno;## empno没有索引  Using filesort

EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY `name`,deptId;##索引顺序错 Using filesort

EXPLAIN SELECT * FROM emp WHERE deptId = 45 ORDER BY age;##索引顺序错 Using filesort
复制代码

where条件进行过滤,索引顺序要保证,不能错,不能断。

image-20201027162559466

image-20201027162624823

排序顺序要保持一致,不能一升一反。

索引选择

image-20201027164910892

我们首先创建CREATE INDEX idx_age_empno_name on emp(age,empno,name);创建这个索引由于empno采用了范围查询,所以导致name失效

image-20201027165115673

删除索引,再次创建CREATE INDEX idx_age_name on emp(age,name);

image-20201027165212353

消除了Using filesort,再把刚才删除的索引给创建。运行查看mysql选择使用那个索引

image-20201027165334141

可以看见mysql选择了idx_age_empno_name索引,虽然导致了Using filesort,但是rows却少了很多!!从而查询效率更高!

image-20201027165530696

因为empno < 101000这个条件使用了索引,大幅度减少了扫描数量。从而mysql选择了这个索引!!

结论: 当范围条件和group by 或者 order by 的字段出现二选一时 , 优先观察条件字段的过滤数量 ,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。同时要相信mysql选择索引的能力!

Using filesort

当我们没有办法避免Using filesort时,filesort有两种算法:mysql就要启动双路排序和单路排序。

双路排序:MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。

取一批数据,要对磁盘进行了两次扫描,众所周知,I\O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。

单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。

由于单路是后出的,总体而言好过双路。但是用单路有问题

在sort_buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取取sort_buffer容量大小,再排……从而多次I/O。

本来想省一次I/O操作,反而导致了大量的I/O操作,反而得不偿失。

优化策略

  • 增大sort_buffer_size参数的设置
  • 增大max_length_for_sort_data参数的设置
  • 减少select 后面的查询的字段。

提高Order By的速度

  1. Order by时select * 是一个大忌只Query需要的字段, 这点非常重要。在这里的影响是:

    • 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序, 否则用老算法——多路排序。

    • 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

  2. 尝试提高 sort_buffer_size 不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的 1M-8M之间调整

  3. 尝试提高 max_length_for_sort_data 提高这个参数, 会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整

group by

group by 使用索引的原则几乎跟order by一致 ,唯一区别是groupby 即使没有过滤条件用到索引,也可以直接使用索引。

覆盖索引

什么是覆盖索引? 简单说就是,select 到 from 之间查询的列 <=使用的索引列+主键

image-20201027171236242

explain select * from emp where name like '%abc';

image-20201027171313348

image-20201027171404868

使用覆盖索引后

image-20201027171425615

也就是不要使用select *

分类:
后端
标签:
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改