1、慢查询日志

• 慢查询日志：mysql提供的一种日志记录，用于记录mysql响应时间超过阀值的sql语句（long_query_time，默认10秒）

  • 默认关闭状态：建议开发调优时打开该日志，正式环境关闭该日志

    # 查询慢查询日志是否开启
    SHOW VARIABLES LIKE '%slow_query_log%'
    # 在内存中临时开启，重启mysql失效（常用）
    SET GLOBAL slow_query_log =1;
    # 永久修改
    # mysql配置文件
    # [mysqld]中新增
    # 开启慢查询日志记录
    slow_query_log =1
    # 慢查询日志目录地址
    slow_query_log_file=/var/lib/mysql/2d994c01327f-slow.log
    

  • 阀值修改：

    # 查看慢查询阀值
    SHOW VARIABLES LIKE '%long_query_time%'
    # 临时设置 单位秒 ，注意设置完成后不会立刻生效需要重新登陆后才生效
    SET GLOBAL long_query_time =5; 
    # 永久设置
    # mysql配置文件
    # [mysqld]中新增
    long_query_time =5
    

• 查询超过慢查询的sql数量

  # 测试 线程睡眠6秒
  SELECT SLEEP(6);
  # 查询慢查询数量
  SHOW GLOBAL STATUS LIKE '%Slow_queries%';
  

• 可以直接到慢查询的日志中查看sql

  SHOW VARIABLES LIKE '%slow_query_log%'
  

  
  

• mysqldumpslow工具可以根据一些过滤条件查找出指定的，慢查询sql

  • s：排序方式
  • r：逆序
  • l：锁定时间
  • g：正则匹配，
    

• 示例：

  • 返回记录最多的三个sql

    mysqldumpslow -s r -t 3 var/lib/mysql/2d994c01327f-slow.log

  • 获取访问次数最多的3个慢sql

    mysqldumpslow -s c -t 3 var/lib/mysql/2d994c01327f-slow.log

  • 按照时间排序，前10个包含left join查询语句的sql

    mysqldumpslow -s t -t 10 -g “left join” var/lib/mysql/2d994c01327f-slow.log

2、分析海量数据

2.1、创建存储函数

获取随机字符串

CREATE FUNCTION randString ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
	DECLARE
		all_str VARCHAR ( 100 ) DEFAULT 'abcdefghi jklmnopgrstuvwxyzABCDEFGHI JKLMNOPGRSTUVWXYZ';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < n DO
			
			SET return_str = CONCAT(
				return_str,
			SUBSTRING( all_str, FLOOR( RAND()* 52+1 ), 1 ));
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;
	
END
	

mysql服务中执行的话可以修改结束符为“&”，执行后可以在修改回“;”

delimiter &

在开启慢日志时，创建存储过程/存储函数时可能会报错
可以查看“log_bin_trust_function_creators”是否开启

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

获取随机整数

CREATE FUNCTION randNum () RETURNS INT ( 5 ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET i = FLOOR( RAND()* 10000 );
	RETURN i;
END;

2.2、通过存储过程插入海量数据

CREATE TABLE `emp` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '名称',
  `num` int(20) NOT NULL COMMENT '编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

CREATE TABLE `depd` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `loc` varchar(255) NOT NULL COMMENT '编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门';

创建员工存储过程

CREATE PROCEDURE inserter_emp (
	IN startNum INT ( 10 ),
	IN endNum INT ( 10 )) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;
	REPEAT
			INSERT INTO emp
		VALUES
			(
				startNum + i,
				randString ( 5 ),
			randNum ());
		
		SET i = i + 1;
		UNTIL i = endNum 
	END REPEAT;
	COMMIT;
	
SET autocommit = 1;
END;

创建部门存储过程

CREATE PROCEDURE inserter_depd (
	IN startNum INT ( 10 ),
	IN endNum INT ( 10 )) BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;
	REPEAT
			INSERT INTO depd
		VALUES
			(
				startNum + i,
				randString ( 5 ),
			randString ( 15 ));
		
		SET i = i + 1;
		UNTIL i = endNum 
	END REPEAT;
	COMMIT;
	
SET autocommit = 1;
END;

2.3、分析海量数据 PROFILES

记录profiles打开状态后的所有sql语句，查看执行消费的时间，只能查看总消费时间，不能查看各个硬件消费时间

# 查看profiles
SHOW PROFILES;

# 查看profiles状态
SHOW VARIABLES LIKE '%profiling'

# 临时打开PROFILES
set profiling =on；

2.4、全局查询sql日志

记录开启之后记录全部sql语句（所有的全局开启动作都建议仅在测试环境调优时开启，正式环境关闭，因为比较消耗性能）

查看是否开启全局sql记录
SHOW VARIABLES LIKE ‘%general_log%’

• 将所有执行的sql记录在表中

  1开启全局日志
  set global general_log =1;
  将全部sql记录在表中
  set global log_output =‘table’;

• 将所有执行的sql记录在文件中

  1开启全局日志
  set global general_log =1;
  将全部sql记录在文件中
  set global log_output =‘file’;
  设置
  set global general_log_file =‘自定义路径’;

0关闭全局日志
SHOW global general_log =0;

查看全局日志
SELECT * from mysql.general_log