你对sql优化你有什么方法?

——分析 —— 1.观察,至少跑一天,看看生产的慢sql情况 2.开启慢查询日志,设置阙值(比如超过5秒钟的sql就是慢sql),并将它抓取出来 3.explain + 慢sql分析 —(可以解决80%的问题) 4.show profile —(可以解决99%) 5.运维经理 或者 DBA 进行sql 数据库 参数调优

总结: 1.慢查询开启并捕获 2.explain +慢查询分析 3.show profile查询sql在mysql服务器里面的执行细节和生命周期 4.sql数据库服务器的调优

查询优化

永远小表驱动大表

类似嵌套循环Nested Loop(oracle数据库)
image.pngimage.png

order by关键字优化

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

建表

  1. create table tblA(
  2.     id int not null primary key auto_increment,
  3.     age int,
  4.     birth timestamp
  5. );
  7. insert into tblA values (null,22,now()),
  8. (null,23,now()),
  9. (null,24,now());
  11. -- 创建覆合索引
  12. alter table tblA add index idx_tblA_ageBirth(age,birth);
  14. -- 查看索引
  15. show index from tblA;

Case

order by能不能产生filesort?

  • 不能产生filesort的sql

    1. explain select * from tblA where age>20 order by age;

    image.png

    1. explain select * from tblA where age>20 order by age,birth;

    image.png

    1. explain select * from tblA where birth > '2020-09-23 14:26:15' order by age;

    image.png

  • 能产生filesort

    1. explain select * from tblA where age>20 order by birth;

    image.png

    1. explain select * from tblA where age>20 order by birth,age;

    image.png

    1. explain select * from tblA where age>20 order by birth;

    image.png

    1. explain select * from tblA where birth > '2020-09-23 14:26:15' order by birth;

    image.png

    1. explain select * from tblA order by age ,birth desc;

    image.png

Mysql支持二种方式的排序

FileSort和 Index , Index 效率高,它指 Mysql 扫描索引本身完成排序。 FileSort 方式效率低

Order by 满足俩种情况,会使用Index方式排序

  • order by语句使用最左前缀法则
  • 使用Where 子句与 Order by 子句条件列组合满足索引最最左前缀

结论 : 尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀法则


如果排序的字段不在索引列上,filesort有俩种算法

单路排序

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

双路排序

1.Mysql 4.1 之前是使用的是双路排序,字面意思是俩次扫描磁盘,最终得到数据,读取行指针和 order by列,对它们进行排序,然后扫描已经排好序的列表,按照列表中的值从列表中读取对应的数据输出。 2.从磁盘取排序字段,在buffer进行排序,再从磁盘取其它字段。

结论及引申出来的问题?

由于单路是后出的，总体而言好过双路，但是用单路有问题 1.在 Sort_buffer中,方法B要比A要多占很多空间,因为方法B是把所有字段都取出,所以有可能取出来的总大小超出了 sort_buffer 的容量,导致每次只能取出 sort_buffer 容量大小的数据,进行排序(创建 tmp 文件,多路合并)排完在取出 sort_buffer 容量大小,在排 … 从而多次IO。 2.本来想省一次IO,反而导致大量的I/O操作,反而得不偿失。

优化策略

增大 sort_buffer_size的容量

增大 max_length_for_sort_data参数的设置

why?(提高order by 的速度)

1), order by 时 select * 是一个大忌只 Query 需要字段,这点非常重要。

1.当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法—单路排序,否则用老算法—双路排序。 2.俩种算法的数据都有可能超过 sort_buffer 的容量,超出之后,会创建 tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size的大小。

2),尝试提高 sort_buffer_size

不管用那种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个线程的 1M-8M之间(一个sql在I兆8兆之间)

3),尝试提高 max_length_for_sort_data

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

总结和查询要点(为排序使用索引)

  • mysql 俩种排序方式: 文件排序或扫描有序索引排序
  • mysql能为排序与查询使用相同的索引

image.png

Group by关键字优化

  • group by 使用索引的原则几乎根 order by 一致,唯一区别就是 group by 即使没有过滤条件也能用到索引
  • 当无法使用索引列,增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size参数的设置
  • where 高于 having,能写在 where 限定的条件就不要去 having限定了

最后使用索引的手段: 覆盖索引(简单来说就是 select 和到 from 之间查询的列 <= 使用的索引列 + 主键)

  1. create index idx_emp_ageName on emp(age,name);
  2. explain select * from emp where age <> 30;

image.png
结论: type 类型为 全表扫描 rows 为50w行 

  1. explain select id,name,age from emp where age <> 30;

image.png
结论: type类型为 range 使用到了索引 rows为25w行
总结: 写sql不要写 * ，写具体的字段

慢查询日志

是什么?

1.Mysql 的慢查询日志是Mysql提供的一种日志记录,它用来记录在 Mysql 中相应时间超过阈值的sql 语句,具体指运行时间超过 long_query_time值的sql,则会被记录在日志文件中。
2.具体指超过 long_query_time 值的sql,则会被记录在慢查询日志中。 long_query_time的默认值为10 ,意思是超过10s的sql语句。 3.由他来查看那些sql语句超过我们最大忍耐时间值,比如一条sql超过5秒钟,我们就算慢sql,希望他能说收集超过5秒的sql,结合 explain 进行优化。

怎么玩?

说明
默认情况下,mysql数据库没有开启慢查询日志,
需要我们手动来设置这个参数。
当然,如果不是调优需要的话,一般不建议启动这个参数
因为开启慢查询日志会或多或少带来一定性能影响,慢查询日志支持将日志记录写入文件。

查看是否开启及如何开启

  • 默认
  • show variables like ‘%slow_query_log%’;
  • 全局开启
  • set global slow_query_log=1;

    永久开启慢查询日志如何做呢?

Linux系统 如果要永久生效,必须修改配置文件 my,cnf文件。 [mysqld]下增加或者修改参数 slow_query_log 和 slow_query_file 然后重启mysql服务器, 如下配置 slow_query_log=1 slow_query_file=/var/lib/mysql/iZuf63o1pfoecqekp92qllZ-slow.log 关于慢查询 slow_query_file 它指定慢查询日志文件的存放路径, 系统默认给一个缺省的文件host_name-slow.log (如果没有指定参数slow_quer_file的话) (不建议设置成长久生效)

如果开启了慢查询日志,什么样的sql将被记录在慢查询日志里呢?

image.png

Case

查看默认阈值

  • 查看当前多少秒算慢

  • SHOW VARIABLES LIKE ‘long_query_time%’;

    查看默认阈值

    show variables like ‘%query_time%’;

  • 修改为阈值大于3的慢sql

  • set long_query_time=3;

    为什么设置后看不出变化？

  • 需要重新连接或新开一一个会话才能看到修改值。

    • SHOW VARIABLES LIKE ‘long_query_time%’;
  • show global variables like ‘long_query_time’;

    记录慢SQL并后续分析

    image.png

    查询当前系统中有多少条慢查询记录

    show global status like ‘%Slow_queries%’; image.png

配置版

  1. [mysqld]如下配置
  3. slowquerylog=1;
  4. slowqueryfile=/var/lib/mysql/xx-slow.log;
  5. longquerytime=3;
  6. log_output=FILE

日志分析工具 mysqldumpslow

在生产环境中,如果要手动分析日志,查找,分析sql,显然是个体力活,mysql提供了日志分析工具 mysqldumpslow

语法 | s | 是表示按照何种方式排序 | | —- | —- | | c | 访问次数 | | l | 锁定时间 | | r | 返回记录 | | t | 查询时间 | | al | 平均锁定时间 | | ar | 平均返回记录数 | | at | 平均查询时间 | | t | 返回前面多少条的数据 | | g | 后面搭配一个正则匹配模式 |

工作常用参考

得到返回记录集最多的10个sql集合

mysqldumpslow -s r -t 10 /var/lib/mysql/host_name-slow.log

得到返回次数最多的10个sql

mysqldumpslow -s c -t 10 /var/lib/mysql/host_name-slow.log

按照时间排序的前10条里面包含有左连接的查询语句

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/host_name-slow.log

建议 : 使用这些命令时结合 | more 使用,否则有可能出现爆屏的情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

批量数据脚本

建表语句

  1. create database bigdata;
  3. use bigdata;
  5. -- 建部门表
  6. create table dept(
  7. id int not null auto_increment primary key,
  8. deptno MEDIUMINT not null default 0,
  9. dname varchar(255) not null,
  10. loc varchar(255) not null
  11. );
  13. -- 建员工表
  14. create table emp(
  15. id int not null primary key auto_increment,
  16. empno MEDIUMINT not null default 0,
  17. ename varchar(20) not null,
  18. job varchar(9) not null,
  19. mgr MEDIUMINT not null,
  20. hiredate DATE not null,
  21. sal DECIMAL(7,2) not null,
  22. comm DECIMAL(7,2) not null,
  23. deptno MEDIUMINT not null DEFAULT 0
  24. );

设置参数 log_bin_trust_function_creators

``image.png

  1. 默认
  2. show variables like '%log_bin_trust_function_creators%';
  3. 开启
  4. set global log_bin_trust_function_creators=1;

创建函数,保证每条数据都不相同

随机产生字符串

  1. -- 创建随机字符串函数
  2. delimiter $$
  3. create function random_str(n int) returns VARCHAR(255)
  4. begin
  5. DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
  6. DECLARE return_str varchar(255) DEFAULT '';
  7. DECLARE i int DEFAULT 0;
  8. WHILE i<n DO
  9.     set return_str = CONCAT(return_str,substr(chars_str,FLOOR(1+RAND()*52)+1));
  10.     set i=i+1;
  11. END WHILE;
  12. RETURN return_str;
  13. end $$

随机产生部门编号

  1. delimiter $$
  2. create function random_dept_no() returns int(10)
  3. begin
  4. DECLARE i int DEFAULT 0;
  5. set i = FLOOR(100+RAND()*10);
  6. RETURN i;
  7. end $$

创建存储过程

创建emp表中插入数据的存储过程

  1. delimiter $$
  2. create procedure insert_emp(in start int, in max_num int)
  3. begin
  4. DECLARE i int DEFAULT 0;
  5. #关闭数据库自动提交
  6. set autocommit=0;
  7. #REPEAT重复的意思
  8. REPEAT
  9.     set i=i+1;
  10.     insert into emp values (null,(start+i),random_str(6),'SALESMAN',0001,CURDATE(),2000,400,random_dept_no());
  11. UNTIL i = max_num 
  12. END REPEAT;
  13. #提交
  14. COMMIT;
  15. end $$

创建dept表中插入数据的存储过程

  1. delimiter $$
  2. create procedure insert_dept(in start int,in int_max int)
  3. begin
  4. DECLARE i int default 0;
  5. set autocommit =0;
  6. REPEAT
  7.     set i = i+1;
  8.     insert into dept values(null,(start+i),random_str(10),random_str(8));
  9. UNTIL i=int_max END REPEAT;
  10. commit;
  12. end $$

调用存储过程（插入数据）

dept表插入

call insert_dept(100,10);

emp表插入

call insert_emp(10001,500000);

show Profile

是什么?

是 Mysql 提供可以用来分析当前会话语句执行的资源消耗情况,可以用于sql的调优测量
官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

默认情况是关闭的

  • 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
  • show variables like ‘%profiling%’;
  • 保存最近15次的运行结果
  • 开启 profile
  • set profiling=1;

    分析步骤

    1、是否支持，看看当前的mysql版本是否支持

    show variables like ‘%profiling%’; image.png

2、开启功能，默认是关闭，使用前需要开启

set profiling=on;

image.png

3、运行SQL

  1. select * from emp group by id%10 limit 150000;
  2. select * from emp group by id%20 order by 5

4、查看结果，show profiles

5、诊断SQL，

show profile cpu，block io for query 上一步前面的问题SQL数字号码；
参数备注：
image.png

6、日常开发需要注意的结论

converting HEAP to MyISAM查询结果太大，内存都不够用了，往磁盘上搬了

Creating tmp table创建临时表

  • 拷贝数据到临时表
  • 用完再删除

image.png

Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

locked

show processlist(展现进程列表)

可以使用 kill id

全局查询日志

配置启用

  1. 在Mysql 的 my.cnf中,设置如下
  3. 开启
  4. general_log=1
  6. 记录日志文件的路径
  7. generallogfile=/path/logfile
  9. 输出格式
  10. log_output=FILE

编码启用

  1. 1) set global general_log =1;
  2. 2) set global log_output='TABLE';
  4. --查看 
  5. select * from mysql.general_log;

image.png

永远不要在生产环境开启这个功能。

视图 View

什么是视图?

  • 将一段查询的sql封装为一个虚拟的表
  • 虚拟表会不会对sql有优化查询的作用? 不会,因为视图只是进行了封装

  • 这个虚拟表只保存了sql逻辑,不会保存任何查询结果

    作用

  • 封装复杂sql语句,提高复用性

  • 逻辑放在数据库上面,更新不需要发布程序,面对频繁的需求变更更灵活

    适用场景

  • 很多地方可以共用的一组查询结果

  • 报表

    创建/更新语法

    创建

    语法：
    1. create view 视图名 as sql语句
    1. -- create view 视图名 as sql语句
    2. create view view_test as select d.deptName, if(avg(e.age)>50,'老鸟','菜鸟') 老鸟or菜鸟 
    3. from t_dept d 
    4. inner join t_emp e on d.id=e.deptId 
    5. group by d.deptName,d.id

    使用

    1. select * from view_test

    更新

    1. create or replace view view_test as
    2. select d.deptName, if(avg(e.age)>50,'老鸟','菜鸟') 老鸟or菜鸟 from t_dept d
    3. inner join t_emp e
    4. on d.id=e.deptId
    5. group by d.deptName,d.id

    注意事项(使用 5.5)

    mysql 的视图中不允许有form 后面的子查询,但 oracle可以