以下是最近学习MySQL的一些笔记,推荐一起阅读:
查询截取分析
分析过程:
- 观察,查看生产慢SQL情况
- 开启慢查询日志,设置阈值
- explain+慢SQL分析
- show profile查询SQL在MySQL服务器里面的执行细节和执行情况
- SQL数据库服务参数调优
查询优化
小表驱动大表原则
for (int i=10;)
for (int i=1000;)
...
for (int i=1000;)
for (int i=10;)
...
上面的嵌套for循环,第一个更优,因为如果作为数据集来说,连接数据库只需要10次
-
小表驱动大表,小的数据集驱动大的数据集,用小表A驱动大表B
-
A>B,用in优于exists
SELECT * FROM A WHERE A.id IN (SELECT id FROM B)
-
A<B,用exists优于in
SELECT * FROM A a WHERE EXISTS (SELECT id FROM B b WHERE a.id=b.id)
理解:
in是包含,exists是被包含;
使用exists的时候,是将主查询结果放到子查询里面做验证,根据验证结果返回TRUE或者FALSE,然后根据结果筛选数据
order by关键词优化
关键:避免产生filesort
尽量使用Index方式排序(尽可能匹配最佳左前缀)
如果使用了filesort
MySQL有两种排序算法:双路排序和单路排序
-
双路排序(老版本,MySQL4.1之后淘汰):读取指针和order by列,对他们进行排序;然后扫描已经排序好的列表,再按照要求读取的字段扫描得到最终结果
- 从磁盘取排序字段,在buffer进行排序,再从磁盘读取其他数据
- 两次磁盘扫描
-
单路排序:
- 一次磁盘扫描,I/O次数少速度快
- 如果缓冲区不够大会出现问题
单路排序出现的问题:
单路排序优化策略
因为单路排序会出现缓冲区不够的问题,所以可以进行参数设置my.cnf文件
- 调整sort_buffer_size参数
- 调整max_length_for_sort_data参数
当然这个参数最好不要动,知道有这个参数就行了
https://www.cnblogs.com/wy123/p/7744171.html
如何提升order by的速度(总结)
- 不要用select *
- 调参数(参考)
group by关键词优化
和order by相似
慢查询日志
慢查询是查询时间超过一定阈值的SQL语句,MySQL提供了慢查询日志功能来对慢查询语句进行日志记录
这个功能默认是关闭的么?
有关慢查询日志的操作:
-
查看慢查询日志记录情况
show variables like '%slow_query_log%';
-
开启慢查询日志功能(不是永久开启,重启会失效)
set global slow_query_log=1;
-
查看慢查询时间阈值
show variables like '%long_query_time%';
默认10秒
-
修改慢查询时间阈值
set global long_query_time=5;
-
查看慢查询条数
show global status like '%slow_queries%';
-
mysqldumpslow
mysql提供的一个方便查看慢查询日志的工具:
mysqldumpslow --help 返回记录集最多的10个SQL mysqldumpslow -s r -t 10 $LOG_PATH | more 访问次数最多的10个SQL mysqldumpslow -s c -t 10 $LOG_PATH | more 按照时间排序前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" $LOG_PATH | more
各种参数大致如下:
批量数据脚本
存储过程和函数的区别:
存储过程和函数的区别在于 函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN、OUT、INOUT类型,而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL,可能需要将函数改造成存储过程。
存储过程和函数允许包含DDL语句,也允许使用事务,还可以调用其他的存储过程和函数,但不允许执行 Load Data Infile 语句;
https://www.cnblogs.com/cshaptx4869/p/10476692.html
向数据库中插入1000W条数据(直接执行):
由于我懒得解释了,就直接执行下面的sql即可创建一个名为sql_learn的库,里面有2张表,其中user表有1000W条数据
注意:1000W实在是太多了,不怕死的可以跑一跑,我就跑了10W测试了一下,服务器已经要炸了
-- 建库sql_learn
CREATE DATABASE IF NOT EXISTS `sql_learn`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
USE `sql_learn`;
-- 建表
CREATE TABLE `dept`(
`dept_id` int(8) PRIMARY KEY,
`dept_name` VARCHAR(8) NOT NULL
)ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `user`(
`id` int(8) PRIMARY KEY,
`username` VARCHAR(8) NOT NULL,
`dept` int(8) DEFAULT NULL,
CONSTRAINT fk_user_dept FOREIGN KEY(`dept`) REFERENCES dept(`dept_id`)
)ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci;
-- 随机生成函数
DELIMITER $$
CREATE FUNCTION `func_genString`(length INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
DECLARE ans VARCHAR(255) DEFAULT "";
DECLARE i INT DEFAULT 0;
WHILE i < length DO
SET ans = CONCAT(ans, SUBSTRING(chars_str, FLOOR(RAND()*52+1), 1));
SET i=i+1;
END WHILE;
RETURN ans;
END $$
DELIMITER $$
CREATE FUNCTION `func_randDeptId`(start_id INT, max_number INT) RETURNS INT
BEGIN
DECLARE ans INT;
SET ans = FLOOR(RAND() * (max_number-start_id+1)) + start_id;
RETURN ans;
END $$
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE `insert_dept`(
IN START INT(10),
IN max_number INT(10)
)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit=0;
REPEAT
INSERT INTO `dept`(`dept_id`, `dept_name`) VALUES (START+i, func_genString(8));
SET i=i+1;
UNTIL i=max_number END REPEAT;
COMMIT;
END $$
DELIMITER $$
CREATE PROCEDURE `insert_user`(
IN START INT(10),
IN max_number INT(10),
IN dept_start INT(10),
IN dept_max_number INT(10)
)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit=0;
REPEAT
INSERT INTO `user`(`id`, `username`, `dept`)
VALUES (START+i, func_genString(8), func_randDeptId(dept_start, dept_max_number));
SET i=i+1;
UNTIL i=max_number END REPEAT;
COMMIT;
END $$
DELIMITER ;
-- 创建数据
-- 50个部门
CALL insert_dept(1, 50);
-- 10000000个员工
-- CALL insert_user(1, 2000000, 1, 50);
-- CALL insert_user(2000001, 2000000, 1, 50);
-- CALL insert_user(4000001, 2000000, 1, 50);
-- CALL insert_user(6000001, 2000000, 1, 50);
-- CALL insert_user(8000001, 2000000, 1, 50);
CALL insert_user(1, 100000, 1, 50);
使用show profile进行SQL分析
show profile是MySQL提供的用来分析当前会话中语句执行的资源消耗情况,可以用于SQL调优的测量
默认情况下这个功能处于关闭状态,并且保存最近15次的运行结果
-
查看show profile功能
show variables like '%profiling%';
-
开启show profile功能
set profiling=on;
-
简单使用show profiles;
show profiles;
-
使用show profiles进行分析
show profile cpu, block io for query <number>;
其他参数:
show profile的结果其实就是navicat等数据库操作软件里面的”剖析“功能:
show profile结果中需要注意的几个参数:
全局查询日志
注意不要在生产环境使用查询日志功能
-
开启全局查询日志
set global general_log=1;
-
配置日志输出方式
set global log_output='TABLE'; 编写的sql语句会记录到mysql库里面的general_log表 select * from mysql.general_log;