2022-02-24-MySQL优化技巧_2022 sql调优-CSDN博客

本文链接：https://blog.csdn.net/qq_33322074/article/details/123102826

文章目录

MySQL优化技巧

创建表

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());

插入10w条数据

-- 插入10w条测试数据
drop procedure if exists insert_employees; 
delimiter $$
create procedure insert_employees()        
begin
  declare i int;                    
  set i = 1;                          
  while(i <= 100000)do                 
    insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
    set i = i + 1;                       
  end while;
end$$
delimiter ;
call insert_employees();

MySQL优化技巧

索引优化

尽量全值匹配：

对索引中所有列都指定具体值，该情况下，索引生效，执行效率高。下面sql中第三条执行效率最高

explain select * from employees where name = 'itwxe';
explain select * from employees where name = 'itwxe' and age = 22;
explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';

最左前缀原则

在使用联合索引的时候要特别注意最左前缀原则，即查询从联合索引的最左前列开始并且不跳过索引中的列。
不过需要注意的是和查询 SQL 书写的顺序无关，最左指的是联合索引创建时列的顺序

explain select * from employees where name = 'itwxe' and age = '18';--该sql会匹配2个索引
explain select * from employees where name = 'itwxe' and position = 'manager';  --该sql只会匹配第一个索引
explain select * from employees where position = 'manager';--该sql不会匹配任何索引

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

需要注意的是这里说的索引列任何操作(计算、函数、(自动/手动)类型转换)不做操作指的是 where 条件之后的，而不是查询结果字段里面的。

explain select * from employees where name = 'weiwei';
explain select * from employees where left(name,6) = 'weiwei';--会导致全表扫描

存储引擎不能使用范围条件右边的列：譬如，由3个列组成的联合索引–name,age,positon

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
explain select * from employees where name = 'itwxe' and age > 22 and position = 'manager';
-- 第二条sql语句，只使用了name,age两个索引列，第三个position没有使用。因为范围条件左边的只有name和age

尽量使用覆盖索引，减少使用 select *

如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。
所谓的覆盖索引就是，索引中就是我们要查询的值

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

<、>、<=、>= 这些，MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

is null和is not null一般情况下也无法使用索引

explain select * from employees where name is null;
explain select * from employees where name is not null;

like 以通配符开头('%xxx')MySQL的索引会失效，变成全表扫描

explain select * from employees where name like 'wei%';
explain select * from employees where name like '%wei';
-- 该sql会造成全表扫描，因为B+树不知道选择那边

解决方案：

1、根据业务建立联合索引，使用覆盖索引查询
2、不能使用覆盖索引的借助ES等搜索引擎

索引下推

可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

explain select * from employees where name like 'weiwei%' and age = 22 and position = 'manager';
explain select * from employees where name like 'itwxe%' and age = 22 and position = 'manager';
-- sql 1 会使用索引下推的方案.第二条 SQL 没有使用索引的原因是因为 MySQL 计算使用索引下推过滤出数据后，查询二级索引+回表的查询成本大于全表扫描，所以 MySQL 选择全表扫描。

类型不匹配MySQL自动转型导致索引失效

字符串不加单引号索引失效，亦或是数值类型加单引号索引失效，也就是第三点中提到的自动类型转换(也叫隐式转换)导致索引失效。

少用or或者in，用它查询时，MySQL不一定使用索引

MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

order by优化

索引本身就是一种排好序的数据结构，所以排序优化最好的办法就是落实到索引上，这样查询出来的数据就已经排好序了，这种排序在 MySQL 中被称之为 Using Index，即覆盖索引。那么如果查询出来后的数据本身没有按所需字段排序，那么就会出现 Using filesort，即文件排序。
文件排序包含单路排序和双路排序

单路排序的详细过程：

从索引 name 找到第一个满足 name = ‘itwxe’ 条件的主键 id
根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
从索引 name 找到下一个满足 name = ‘itwxe’ 条件的主键 id。
重复步骤 2、3 直到不满足 name = ‘itwxe’
对 sort_buffer 中的数据按照字段 position 进行排序
返回结果给客户端

双路排序的详细过程：

从索引 name 找到第一个满足 name = ‘itwxe’ 的主键id
根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
从索引 name 取下一个满足 name = ‘itwxe’ 记录的主键 id
重复 3、4 直到不满足 name = ‘itwxe’
对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端

单路排序和双路排序优缺点

单路排序：需要足够大的内存空间，但是排序快。
多路排序：需要的内存空间小，但是多了一次回查

MySQL根据max_length_for_sort_data的大小和需要查询的字段大小的比较来判断是单路排序还是双路排序。默认大小是1024字节。因此通过扩大max_length_for_sort_data大小来提升order by效率
MySQL的排序主要是受到两个变量来控制：

sort_buffer_size -----默认1Mb—用于存储排序的字段或者记录
max_length_for_sort_data—默认1024字节，用于判断是走双路排序还是单路排序
调大两个变量的值可以提升MySQL的性能

不过需要注意，如果全部使用 sort_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

Group by优化

group by 与 order by 很类似，其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。对于group by的优化，如果不需要排序的可以加上 order by null禁止排序。

分页查询优化

常见的分页查询的sql

select * from employees limit 90000,10;

看似 MySQL 是取90001行开始的10条记录，但是实际上 MySQL 在处理这个分页的时候是先读取前 90010 条记录，然后把前90000条记录舍弃，取出90001-90010的数据返回给客户端。因此如果要查询一张大表比较靠后的数据，执行效率是很低的。

如果主键是自增且连续的数值

select * from employees where id > 90000 limit 10;

根据非主键字段排序的分页查询

先根据主键id查询出对应数据，然后通过inner join查询出具体数据

select * from employees order by name limit 90000,10;
select * from employees ed_all inner join (select id from employees order by name limit 90000,10) ed_id on ed_all.id = ed_id.id;

利用延迟关联或者子查询优化超多分页场景

MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率就非常低下。
要么控制返回的总页数。
要么对超过特定阀值的页数进行SQL改写
正确写法：先快速定位需要获取的id段，然后再关联

select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20) b where a.id=b.id

join关联查询优化

表关联常见的两种算法

NLJ(Nested-Loop Join)：NLJ 算法一次一行循环地从第一张表(驱动表)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(被驱动表)里取出满足条件的行，然后取出两张表的结果合集。

explain select * from t1 inner join t2 on t1.a = t2.a;

inner join查询

执行步骤

从表 t2 中读取一行数据(如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据)。
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找。
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端。
重复上面 3 步。

先执行的就是驱动表(执行计划结果的 id如果一样则按从上到下顺序执行 SQL)，优化器通常会优先选择小表做驱动表，用 where 条件过滤完驱动表，然后再跟被驱动表做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用 left join 时，左表是驱动表，右表是被驱动表。
当使用 right join 时，右表时驱动表，左表是被驱动表。
当使用 inner join 时，MySQL 通常会选择数据量比较小表作为驱动表，大表作为被驱动表。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了200 行。
如果连接查询的列上没有索引，NLJ 算法性能会比较低，那么 MySQL 则会选择 BNL 算法。

基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法

BNL 算法把驱动表的数据读入到 join_buffer(连接查询缓存) 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
Block Nested-Loop join算法执行流程：

把 t2 的所有数据放入到 join_buffer 中。
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比。
返回满足 join 条件的数据。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段。通过调大join_buffer_size可以提升没有索引字段的join查询。
分段后会进一步增加判断次数和扫描行数。

总结NLJ和BNL算法

在没有使用索引的情况下使用NLJ算法会增加磁盘扫描次数，而BNL是计算次数。计算更加迅速，因此在没有索引情况下使用的是BNL算法。
通过增加join_buffer_size大小可以减少BNL算法的分段次数进而提升join性能
超过三个表禁止 join，多表关联查询时，保证被关联的字段需要有索引，尽量选择 NLJ 算法，同时需要 join 的字段，数据类型必须绝对一致。
小表驱动大表，写多表连接 SQL 时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去mysql优化器自己判断的时间。

straight_join：straight_join 功能同 inner join 类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定 MySQL 选择 t2 表作为驱动表。
straight_join 只适用于 inner join，并不适用于 left join、right join。(因为 left join、right join 已经代表指定了表的执行顺序)
尽可能让优化器去判断，使用 straight_join 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

In和exists优化

原则：小表驱动大表
in优化：当A表数据集小于B表时，用

select * from A where id in (select id from B);
# 等价于：
for(select id from B) {
    select * from A where A.id = B.id;
}

exsits优化：当A表的数据集小于B表的数据集时，exists 优于 in。

select * from A where exists (select 1 from B where B.id = A.id);
# 等价于：
for(select * from A) {
    select 1 from B where B.id = A.id;
}

个人理解：本质是尽量减少磁盘的扫描。一个查询就是一次磁盘扫描。所以for循环越少，磁盘查询就越少

count(*)优化

常见的count()查询

-- 临时关闭MySQL查询缓存
set global query_cache_size=0;
set global query_cache_type=0;

-- count
explain select count(*) from employees;
explain select count(1) from employees;
explain select count(id) from employees;
explain select count(name) from employees;

结论：
1、字段有索引：count() ≈ count(1) > count(字段) > count(主键id) // 字段有索引，count(字段) 统计走二级索引，二级索引存储数据比主键索引少，所以 count(字段) > count(主键id)。
2、字段无索引：count() ≈ count(1)> count(主键id) > count(字段) // 字段没有索引，count(字段) 统计走不了索引，count(主键id) 还可以走主键索引，所以 count(主键id) > count(字段)
3、直接选择count()就ok了。MySQL5.7对count()做了优化

优化方案：

MyISAM有一个变量存储表的数量，但是只能使用select count(*) from table不能使用任何where条件。
通过show table status可以获取总数的估计值。
将总数维护到Redis里面，但是要保证数据一致性。
增加数据库计数表，维护一个数据库计数表

总结

索引优化

1、尽量全值匹配索引
2、最左前缀原则
3、不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描
4、存储引擎不能使用范围条件右边的列：
5、MySQL在使用不等于(!=或者<>)，not in,not exists的时候无法使用索引会导致全表扫描
6、尽量使用覆盖索引，能不回表查询就不回表查询
7、like 以通配符开头('%xxx')MySQL的索引会失效，变成全表扫描

order by优化

1、能走索引字段走索引
2、不能走索引字段通过调整MySQL的sort_buffer_size和max_length_for_sort_data来提升MySQL性能

Group by优化

1、如果group by 不追求顺序，后面添加 order by null

分页查询优化

1、对于分页量大的查询，最好先找到分页的id，然后直接跳到对应id处查询
2、对于id自增的1亿条记录数据，还可以采用倒排序算法查询

join关联查询优化

1、小表驱动大表，减少磁盘扫描
2、有索引字段采用NLJ算法，没有索引字段通过调整MySQL的join_buffer_size来提升BNL算法性能

in和exists查询

1、小表驱动大表，减少磁盘扫描

换掉MySQL吧

知识点

知识点1：MySQL的delimiter用法

delimiter其实就是高数MySQL解释器，该命令是否已经结束了，MySQL是否可以执行了，默认情况下，delimiter是分号。在命令行客户端中，如果有一行命令以分号结束，然后回车，MySQL将会执行该命令。

-- 插入10w条测试数据
drop procedure if exists insert_employees; 
delimiter $$
create procedure insert_employees()        
begin
  declare i int;                    
  set i = 1;                          
  while(i <= 100000)do                 
    insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
    set i = i + 1;                       
  end while;
end$$
delimiter ;
call insert_employees();