MySQL-索引优化分析

一、慢查询日志（默认没有开启）

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句。

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

show variables like '%slow_query_log%'   #查看是否开启慢查询日志，默认禁用
set global slow_query_log=1;      #设置慢查询日志开启，只对当前数据库生效，MySQL重启后会失效

show variables like '%long_query_time%';  #查看慢于多少秒会被记录到日志文件(默认10s)
set global long_query_time=3;  #设置慢查询阈值时间

show global status like '%slow_queries%';  #查询当前系统中有多少条慢查询日志

若要永久生效，必须修改配置文件my.cnf，然后重启服务器

slow_query_log=1
slow_query_log_file=/var/lib/mysql/sql_idx_slow.log   #系统默认会给一个缺省的文件{host_name}-slow.log
long_query_time=3; 
log_output=FILE

二、慢查询日志分析工具mysqldumpslow　

mysqldumpslow --help

mysqldumpslow -s -r -t 10  /var/lib/mysql/hadoop102-slow.log #得到返回记录集最多的10个SQL

mysqldumpslow -s -r -t 10  /var/lib/mysql/hadoop102-slow.log | more #结合|和more使用

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/hadoop102-slow.log #得到按时间排序的前10条里面含有左连接的查询语句　　　

三、批量导入数据

创建函数若报错 This function has none of DETERMINISTIC

由于开启过慢查询日志，开启了binlog，必须为function指定一个参数

show variables like '%log_bin_trust_function_creators%'  
set global log_bin_trust_function_creators = 1 　

永久生效，修改/etc/my/cnf文件添加 log_bin_trust_function_creators = 1 

创建函数：

1、随机生成字符串　

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE char_str VARCHAR(100) DEFAULT 'abcdABCD';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i<n DO
  SET return_str=CONCAT(return_str,SUBSTRING(char_str,FLOOR(1+RAND()*8),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 $$　　

执行存储过程　　

DELIMITER $$
CREATE PROCEDURE insert_tb1_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 tbl_emp(id,name,deptId) VALUES
((START+i),rand_string(10),rand_num());
UNTIL i=max_num
END REPEAT;
COMMIT;
END $$　　　

调用存储过程

DELIMITER ;
CALL insert_tb1_emp(100,10);

四、show profile（默认关闭，并保存最近15次的运行结果）

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

 1、默认关闭，使用前需要开启

show variables like '%profiling%'; 
set profiling=on;  #开启　　

2、运行SQL后，查看结果

show profiles

3、诊断SQL

show profile cpu,block io for query 问题SQL数字号码  

日常开发需要注意的结论

• converting HEAP to MyISAM 查询结果太大，内存不够用往磁盘搬
• Creating tmp table 创建临时表
• Copy to tmp table on disk 把内存中临时表复制到磁盘，非常危险
• locked　

五、全局日志查询(永远不要在生产环境开启这个功能)

1、配置启用

在mysql的my.cnf中，设置如下

general_log=1  #开启
general_log_file=/path/logfile  #记录日志文件的路径
log_output=FILE  #输出格式

2、编码启用

set global general_log=1;
set global log_output='TABLE'; 

3、此后所编写的sql语句，将会记录到mysql库中的general_log表中

select * from mysql.general_log;