MySQL索引优化

2020-12-02 本文已影响0人西界__

环境搭建

建表语句


 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索引失效会变成全表扫描的操作

image

添加索引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);

总结

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

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y，使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
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 null	is null 支持索引但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引
where a = 3 and b like 'kk%' and c = 4	Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4	Y,只用到a
where a = 3 and b like '%kk%' and c = 4	Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y,使用到a,b,c

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

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
书写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 TABLEbookADD INDEX Y (card);

image-20201027151124361

索引优化生效，再给class表建立索引ALTER TABLEclassADD INDEX Y (card);

image-20201027151228196

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

image-20201027151413449

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

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

image-20201027151746222

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

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

子查询优化

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 exists用left 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的速度

Order by时select * 是一个大忌只Query需要的字段，这点非常重要。在这里的影响是：
- 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
- 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
尝试提高 sort_buffer_size 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整
尝试提高 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 *

MySQL索引优化

环境搭建

建表语句

创建函数

创建调用存储过程

删除索引

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

案例

总结

关联查询

子查询优化

排序分组优化

order by

索引选择

Using filesort

group by

覆盖索引

猜你喜欢

热点阅读