Mysql05-MySQL优化技巧

最新推荐文章于 2021-02-07 16:14:22 发布

小土狗一只

最新推荐文章于 2021-02-07 16:14:22 发布

阅读量195

点赞数

分类专栏： mysql&oracle 文章标签： mysql

本文链接：https://blog.csdn.net/Juwenzhe_HEBUT/article/details/109280957

版权

mysql&oracle 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

Mysql05-MySQL优化技巧

捕获慢查询日志 + explain 慢SQL + show profile + db参数调优

   小表驱动大表
   in和exists
   查询优化
   慢查询日志
   批量插入数据脚本
   用Show Profile进行sql分析
   全局查询日志

一、小表驱动大表
   for(int i=5; ...) { // 小表驱动大表，建立了5次连接
       for(int j=1000; ...){

       }
   }
   ===
   for(int i=1000; ...) { // 不可取
       for(int j=5; ...){

       }
   }
二、in和exists
   exists是in的子查询变种，可以互相转换
   in先执行in里面的子查询（只执行一次），再执行主查询看查出来的内容是否满足子查询条件
   exists先执行主查询，然后将主查询结果与子查询结果比较，返回true，将主查询数据保存到结果集；返回false，不保存。子查询执行A.length次


   总结：数据集小的优先执行，主表是小数据集的，选exists；主表是大数据集的，用in

三、查询优化
   1.order by关键字优化
       1) order by子句，尽量使用索引方式排序，避免filesort产生，也就是说建了idx(name,age)索引，那么要使用 order by name,age 而不是 order by age，后面即使select覆盖了索引也会产生文件排序
       2) 尽可能在索引列上完成排序操作，遵循索引的最佳左前缀
       3) 如果不在索引列上，filesort有两种算法：双路排序和单路排序
           > 双路排序：
               MySQL4.1之前，使用双路排序。就是两次扫描磁盘，最终得到数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出
               从磁盘取排序字段，在sortbuffer缓冲区中进行排序，再从磁盘取其他字段
           > 取一批数据，要对磁盘进行两次扫描，I\O很费资源
           > 单路排序：
               从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快，避免了第二次读取数据。并且把随机IO变成了顺序IO，但会使用更多的空间，因为把每一行内容都保存在内存中了
           > 由于单路是后出的，总体而言好过双路，但是单路需要更大的sortbuffer装载足够多的数据，当数据存不下时，还会产生多次IO，每次只读取了sortbuffer大小的数据量

       4) 增大sort_buffer_size参数
       5) 增大max_length_for_sort_data参数


       order by默认是升序



   2.group by关键字优化
       1) group by实质是先排序后分组，遵循索引的最佳左前缀
       2) 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size
       3) where高于having，能写在where限定的条件就不要取having限定了

四、慢查询日志
   1.超过long_query_time（默认是10秒）的值会被记录到慢查询日志中
   2.默认情况下，mysql不开启慢查询日志，如果非调优需要，一般不需要开启慢查询日志
   3.查看SHOW VARIABLES LIKE '%slow_query_log%';
   4.使用set global slow_query_log=1;只对当前数据库有效，mysql重启后失效；
       想永久生效需要在my.cnf中mysqld下添加配置
       show_query_log = 1
       show_query_log_file = /data/mysql/host_name-slow.log
       long_query_time = 5
   5.查看long_query_time设置的值 SHOW VARIABLES LIKE 'long_query_time%';
   6.设置long_query_time设置的值 set global long_query_time=3; 并重新连接或新开一个会话才能看到设置后的变化
   7.查询当前系统中有多少条慢查询记录 show global status like '%Slow_queries%';
   8.MYSQL日志分析工具mysqldumpslow mysqldumpslow --help



五、批量插入数据脚本
   任务：往表里插入1000w数据
   1.建表

CREATE TABLE big_dept(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	dname VARCHAR(20) NOT NULL DEFAULT "",
	loc VARCHAR(13) NOT NULL DEFAULT ""
)ENGINE=INNODB DEFAULT CHARSET=GBK;
 
CREATE TABLE big_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=GBK;

2.设置log_bin_trust_function_creators

   3.创建函数，保证每条数据都不同
       1) 随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRNG(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i + 1;
	END WHILE;
	RETURN return_str;
END $$

2) 产生随机数

DELIMITER $$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(100+RAND()*10);
	RETURN i;
END $$

4.创建存储过程
1) 创建往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 big_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 $$

2) 创建往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 big_dept(deptno,dname,loc)VALUES((START+i),rand_string(10),rand_string(8));
 UNTIL i = max_num
 END REPEAT;
 COMMIT;
 END $$

   5.调用存储过程
       1)DELIMITER ; 把结束符改回；
       CALL insert_dept(100,10); 调用存储过程用CALL，编号从100开始，插入10个部门
       2)DELIMITER ;
       CALL insert_emp(100001,500000);

六、用Show Profile进行sql分析
   1.Show Profile是MySQL提供用来分析当前会话中语句执行的资源消耗情况，用于SQL调优的测量
   2.默认情况下处于关闭状态，并保存最近15次的运行结果
   3.show profiles分析步骤
       1) 查看当前版本MySQL是否支持 SHOW VARIABLES LIKE 'profiling';
       2) 开启 set profiling = on;
       3) 运行sql
       4) 查看结果 show profiles;
       5) 诊断SQL, show profile cpu,block io for query + Query_ID; 常用的是CPU和BLOCK IO

       6) 注意点：
           converting HEAP to MyISAM 查询结果太大，内存不够用，开始往磁盘上搬
           Creating tmp table 创建临时表
               先创建临时表
               拷贝数据到临时表
               用完了再删除临时表
           Copying to tmp table on disk 把内存中临时表复制到内存，危险！
           locked

七、全局查询日志
   命令查看表结构：desc table_name;
   1.配置启用


   2.永远不要在生产环境开启该功能！