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;