全部标签
写点什么
MySQLOpenHarmony云计算程序员行业资讯算法Python人工智能区块链Linux前端个人成长面试架构师编程企业动态新基建敏捷安全读书笔记高效工作团队管理创业生涯规划知识管理运维产品经理 查看更多

Day163

作者：爱好编程进阶
  • 2022 年 5 月 10 日

  • 本文字数：9290 字

    阅读完需：约 30 分钟

| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index; Using filesort |


+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+


1 row in set (0.00 sec)



结论


  1. MySQL 支持二种方式的排序，FileSort 和 Index，Index 效率高，它指 MySQL 扫描索引本身完成排序，FileSort 方式效率较低。

  2. ORDER BY 满足两情况（最佳左前缀原则），会使用 Index 方式排序


  • ORDER BY 语句使用索引最左前列

  • 使用 where 子句与 OrderBy 子句条件列组合满足索引最左前列


  1. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀


如果未在索引列上完成排序，mysql 会启动 filesort 的两种算法：双路排序和单路排序


  1. 双路排序


  • MySQL4.1 之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和将要进行 order by 操作的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输

  • 从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。


  1. 单路排序


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

  • 从磁盘读取查询需要的所有列，按照将要进行 order by 的列，在 sort buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成顺序 IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。【保证读取的缓存是够用的，不然性能比双路还拉胯】


  1. 结论及引申出的问题：


  • 由于单路是改进的算法，总体而言好过双路

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

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


  1. 更深层次的优化策略：


  • 增大 sort_buffer_size 参数的设置

  • 增大 max_length_for_sort_data 参数的设置



遵循如下规则，可提高 Order By 的速度


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


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

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


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

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


Order By 排序索引优化的总结



.

[](()1.3、GROUP BY 优化

group by 关键字优化


  1. group by 实质是先排序后进行分组，遵照索引的最佳左前缀

  2. 当无法使用索引列，增大 max_length_for_sort_data 参数的设置+增大 sort_buffer_size 参数的设置

  3. where 高于 having，能写在 where 限定的条件就不要去 having 限定了，where 能搞定就尽量不要用 having 了

  4. 其余的规则均和 order by 一致


[](()2、慢查询日志


[](()2.1、慢查询日志介绍

慢查询日志是什么？


  1. MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。

  2. long_query_time 的默认值为 10，意思是运行 10 秒以上的 SQL 语句会被记录下来

  3. 由他来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过 5 秒钟，我们就算慢 SQL，希望能收集超过 5 秒的 sql，结合之前 explain 进行全面分析。

[](()2.2、慢查询日志开启

怎么玩？


说明：


  1. 默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。

  2. 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件



查看是否开启及如何开启


  • 查看慢查询日志是否开启：

  • 默认情况下 slow_query_log 的值为 OFF，表示慢查询日志是禁用的

  • 可以通过设置 slow_query_log 的值来开启

  • 通过SHOW VARIABLES LIKE '%slow_query_log%';查看 mysql 的慢查询日志是否开启


mysql> SHOW VARIABLES LIKE '%slow_query_log%';


+---------------------+-------------------------------+


| Variable_name | Value |


+---------------------+-------------------------------+


| slow_query_log | OFF |


| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |


+---------------------+-------------------------------+


2 rows in set (0.00 sec)


  • 如何开启开启慢查询日志：

  • set global slow_query_log = 1;开启慢查询日志

  • 使用set global slow_query_log = 1开启了慢查询日志只对当前数据库生效，如果 MySQL 重启后则会失效。


mysql> set global slow_query_log = 1;


Query OK, 0 rows affected (0.07 sec)


mysql> SHOW VARIABLES LIKE '%slow_query_log%';


+---------------------+---------------------------------+


| Variable_name | Value |


+---------------------+---------------------------------+


| slow_query_log | ON |


| slow_query_log_file | /www/server/data/mysql-slow.log | //宝塔面板的对应目录


+---------------------+---------------------------------+


2 rows in set (0.01 sec)


  • 如果要 **《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源 威信搜索公众号【编程进阶路】** 永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

  • 修改 my.cnf 文件，[mysqld]下增加或修改参数：slow_query_log和slow_query_log_file后，然后重启 MySQL 服务器。

  • 也即将如下两行配置进 my.cnf 文件


[mysqld]


slow_query_log =1


slow_query_log_file=/var/lib/mysql/Heygo-slow.log


  • 关于慢查询的参数 slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件 host_name-slow.log（如果没有指定参数 slow_query_log_file 的话）



那么开启慢查询日志后，什么样的 SQL 参会记录到慢查询里面？


  • 这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为 10 秒，命令：SHOW VARIABLES LIKE 'long_query_time%';查看慢 SQL 的阈值


mysql> SHOW VARIABLES LIKE 'long_query_time%';


+-----------------+-----------+


| Variable_name | Value |


+-----------------+-----------+


| long_query_time | 10.000000 |


+-----------------+-----------+


1 row in set (0.01 sec)


  • 可以使用命令修改，也可以在 my.cnf 参数里面修改。

  • 假如运行时间正好等于 long_query_time 的情况，并不会被记录下来。也就是说，在 mysql 源码里是判断大于 long_query_time，而非大于等于。

[](()2.3、慢查询日志示例

案例讲解


  • 查看慢 SQL 的阈值时间，默认阈值时间为 10s


mysql> SHOW VARIABLES LIKE 'long_query_time%';


+-----------------+-----------+


| Variable_name | Value |


+-----------------+-----------+


| long_query_time | 10.000000 |


+-----------------+-----------+


1 row in set (0.00 sec)


  • 设置慢 SQL 的阈值时间，我们将其设置为 3s


mysql> set global long_query_time=3;


Query OK, 0 rows affected (0.00 sec)


  • 为什么设置后阈值时间没变？

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

  • 查看全局的 long_query_time 值：show global variables like 'long_query_time';发现已经生效


mysql> set global long_query_time=3;


Query OK, 0 rows affected (0.00 sec)


mysql> SHOW VARIABLES LIKE 'long_query_time%';


+-----------------+-----------+


| Variable_name | Value |


+-----------------+-----------+


| long_query_time | 10.000000 |


+-----------------+-----------+


1 row in set (0.00 sec)


mysql> show global variables like 'long_query_time';


+-----------------+----------+


| Variable_name | Value |


+-----------------+----------+


| long_query_time | 3.000000 |


+-----------------+----------+


1 row in set (0.00 sec)


  • 记录慢 SQL 以供后续分析

  • 怼个 select sleep(4); 超过 3s ，肯定会被记录到日志中


mysql> select sleep(4);


+----------+


| sleep(4) |


+----------+


| 0 |


+----------+


1 row in set (4.00 sec)


  • 慢查询日志文件在 /var/lib/mysql/ 下，后缀为 -slow.log


[root@Heygo mysql]# cd /var/lib/mysql/


[root@Heygo mysql]# ls -l


总用量 176156


-rw-rw----. 1 mysql mysql 56 8 月 3 19:08 auto.cnf


drwx------. 2 mysql mysql 4096 8 月 5 10:36 db01


-rw-rw----. 1 mysql mysql 7289 8 月 3 22:38 Heygo.err


-rw-rw----. 1 mysql mysql 371 8 月 5 12:58 Heygo-slow.log


-rw-rw----. 1 mysql mysql 79691776 8 月 5 10:36 ibdata1


-rw-rw----. 1 mysql mysql 50331648 8 月 5 10:36 ib_logfile0


-rw-rw----. 1 mysql mysql 50331648 8 月 3 19:08 ib_logfile1


drwx------. 2 mysql mysql 4096 8 月 3 19:08 mysql


srwxrwxrwx. 1 mysql mysql 0 8 月 3 22:38 mysql.sock


drwx------. 2 mysql mysql 4096 8 月 3 19:08 performance_schema


  • 查看慢查询日志中的内容


[root@Heygo mysql]# cat Heygo-slow.log


/usr/sbin/mysqld, Version: 5.6.49 (MySQL Community Server (GPL)). started with:


Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock


Time Id Command Argument

Time: 200805 12:58:01

User@Host: root[root] @ localhost [] Id: 11

Query_time: 4.000424 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1596603481;


select sleep(4);


  • 查询当前系统中有多少条慢查询记录：show global status like '%Slow_queries%';


mysql> show global status like '%Slow_queries%';


+---------------+-------+


| Variable_name | Value |


+---------------+-------+


| Slow_queries | 1 |


+---------------+-------+


1 row in set (0.00 sec)



配置版的慢查询日志


在 /etc/my.cnf 文件的 [mysqld] 节点下配置


slow_query_log=1；


slow_query_log_file=/var/lib/mysql/Heygo-slow.log


long_query_time=3；


log_output=FILE


.


日志分析命令 mysqldumpslow


mysqldumpslow 是什么？


在生产环境中，如果要手工分析日志，查找、分析 SQL，显然是个体力活，MySQL 提供了日志分析工具 mysqldumpslow。



查看 mysqldumpslow 的帮助信息


[root@Heygo mysql]# mysqldumpslow --help


Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]


Parse and summarize the MySQL slow query log. Options are


--verbose verbose


--debug debug


--help write this text to standard output


-v verbose


-d debug


-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default


al: average lock time


ar: average rows sent


at: average query time


c: count


l: lock time


r: rows sent


t: query time


-r reverse the sort order (largest last instead of first)


-t NUM just show the top n queries


-a don't abstract all numbers to N and strings to 'S'


-n NUM abstract numbers with at least n digits within names


-g PATTERN grep: only consider stmts that include this string


-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),


default is '*', i.e. match all


-i NAME name of server instance (if using mysql.server startup script)


-l don't subtract lock time from total time



mysqldumpshow 参数解释


  1. s：是表示按何种方式排序

  2. c：访问次数

  3. l：锁定时间

  4. r：返回记录

  5. t：查询时间

  6. al：平均锁定时间

  7. ar：平均返回记录数

  8. at：平均查询时间

  9. t：即为返回前面多少条的数据

  10. g：后边搭配一个正则匹配模式，大小写不敏感的



常用参数手册


  1. 得到返回记录集最多的 10 个 SQL


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


  1. 得到访问次数最多的 10 个 SQL


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


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


mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log


  1. 另外建议在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况


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


.


[](()3、批量数据脚本



MySql 函数与存储过程的区别：


存储过程可以有返回值，也可以没有，函数有且仅有一个返回值


创建表


  • 建表 SQL


CREATE TABLE dept


(


deptno int unsigned primary key auto_increment,


dname varchar(20) not null default "",


loc varchar(8) not null default ""


)ENGINE=INNODB DEFAULT CHARSET=utf8;


CREATE TABLE emp


(


id int unsigned primary key auto_increment,


empno mediumint unsigned not null default 0,


ename varchar(20) not null default "",


job varchar(9) not null default "",


mgr mediumint unsigned not null default 0,


hiredate date not null,


sal decimal(7,2) not null,


comm decimal(7,2) not null,


deptno mediumint unsigned not null default 0


)ENGINE=INNODB DEFAULT CHARSET=utf8;


设置参数


  • 创建函数，假如报错：This function has none of DETERMINISTIC………

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

  • log_bin_trust_function_creators = OFF ，默认必须为 function 传递一个参数


mysql> show variables like 'log_bin_trust_function_creators';


+---------------------------------+-------+


| Variable_name | Value |


+---------------------------------+-------+


| log_bin_trust_function_creators | OFF |


+---------------------------------+-------+


1 row in set (0.00 sec)


  • 通过 set global log_bin_trust_function_creators=1;我们可以不用为 function 传参


mysql> set global log_bin_trust_function_creators=1;


Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'log_bin_trust_function_creators';


+---------------------------------+-------+


| Variable_name | Value |


+---------------------------------+-------+


| log_bin_trust_function_creators | ON |


+---------------------------------+-------+


1 row in set (0.00 sec)


  • 这样添加了参数以后，如果 mysqld 重启，上述参数又会消失，永久方法在配置文件中修改‘

  • windows 下：my.ini --> [mysqld] 节点下加上 log_bin_trust_function_creators=1

  • linux 下：/etc/my.cnf --> [mysqld] 节点下加上 log_bin_trust_function_creators=1


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


  • 随机产生字符串的函数


delimiter


# 两个


表示结束


create function rand_string(n int) returns varchar(255)


begin


declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';


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 $$


  • 随机产生部门编号的函数


delimiter $$


create function rand_num() returns int(5)


begin


declare i int default 0;


set i=floor(100+rand()*10);


return i;


end $$


创建存储过程


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


delimiter $$


create procedure insert_emp(in start int(10),in max_num int(10))


begin


declare i int default 0;


set autocommit = 0;


repeat


set i = i+1;


insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());


until i=max_num


end repeat;


commit;


end $$


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


delimiter $$


create procedure insert_dept(in start int(10),in max_num int(10))


begin


declare i int default 0;


set autocommit = 0;


repeat


set i = i+1;


insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));


until i=max_num


end repeat;


commit;


end $$


调用存储过程


  • 向 dept 表中插入 10 条记录


DELIMITER ;


CALL insert_dept(100, 10);


12


mysql> select * from dept;


+--------+---------+--------+


| deptno | dname | loc |


+--------+---------+--------+


| 101 | aowswej | syrlhb |


| 102 | uvneag | pup |


| 103 | lps | iudgy |


| 104 | jipvsk | ihytx |


| 105 | hrpzhiv | vjb |


| 106 | phngy | yf |


| 107 | uhgd | lgst |


| 108 | ynyl | iio |


| 109 | daqbgsh | mp |


| 110 | yfbrju | vuhsf |


+--------+---------+--------+


10 rows in set (0.00 sec)


  • 向 emp 表中插入 50w 条记录


DELIMITER ;


CALL insert_emp(100001, 500000);


12


mysql> select * from emp limit 20;


+----+--------+-------+----------+-----+------------+---------+--------+--------+


| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |


+----+--------+-------+----------+-----+------------+---------+--------+--------+


| 1 | 100002 | ipbmd | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |


| 2 | 100003 | bfvt | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 107 |


| 3 | 100004 | | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 109 |


| 4 | 100005 | cptas | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |


| 5 | 100006 | ftn | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 108 |


| 6 | 100007 | gzh | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 102 |


| 7 | 100008 | rji | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 100 |


| 8 | 100009 | | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 106 |


| 9 | 100010 | tms | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 100 |


| 10 | 100011 | utxe | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |


| 11 | 100012 | vbis | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 104 |


| 12 | 100013 | qgfv | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 104 |


| 13 | 100014 | wrvb | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 105 |


| 14 | 100015 | dyks | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 109 |


| 15 | 100016 | hpcs | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |


| 16 | 100017 | fxb | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 108 |


| 17 | 100018 | vqxq | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 102 |


| 18 | 100019 | rq | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 102 |


| 19 | 100020 | l | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 106 |


| 20 | 100021 | lk | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 100 |


......省略....


| 499984 | 599985 | l | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 104 |


| 499985 | 599986 | fvt | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 103 |


| 499986 | 599987 | xnal | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 101 |


| 499987 | 599988 | vj | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 100 |


| 499988 | 599989 | sch | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 105 |


| 499989 | 599990 | g | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 107 |


| 499990 | 599991 | dcfrs | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 109 |


| 499991 | 599992 | hxsu | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 103 |


| 499992 | 599993 | o | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 107 |


| 499993 | 599994 | r | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 102 |


| 499994 | 599995 | gtdk | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 104 |


| 499995 | 599996 | njfb | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 100 |


| 499996 | 599997 | k | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 103 |


| 499997 | 599998 | abesz | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 108 |


| 499998 | 599999 | owpmo | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 106 |


| 499999 | 600000 | u | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 105 |


| 500000 | 600001 | btf | salesman | 1 | 2021-01-13 | 2000.00 | 400.00 | 104 |


+----+--------+-------+----------+-----+------------+---------+--------+--------+


500000 rows in set (0.71 sec)


.


[](()4、Show Profile



Show Profile 是什么？


  1. 是 mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量

  2. 官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

  3. 默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果


分析步骤


查看是当前的 SQL 版本是否支持 Show Profile


  • show variables like ‘profiling%’; 查看 Show Profile 是否开启


mysql> show variables like 'profiling%';


+------------------------+-------+


| Variable_name | Value |


+------------------------+-------+


| profiling | OFF |


| profiling_history_size | 15 |


+------------------------+-------+


2 rows in set (0.01 sec)



开启功能 Show Profile ，默认是关闭，使用前需要开启


  • set profiling=on; 开启 Show Profile


mysql> set profiling=on;


Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> show variables like 'profiling%';


+------------------------+-------+


| Variable_name | Value |

划线
评论
复制
发布于: 刚刚阅读数: 3
Java程序员后端开发
用户头像

爱好编程进阶

关注

还未添加个人签名 2022.04.13 加入

还未添加个人简介

评论

发布
暂无评论
Copyright © 2022, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号
Day163_Java_爱好编程进阶_InfoQ写作社区