MySQL优化笔记其二

order by 优化

慢查询

• 查询配置

show variables like '%slow_query_log%'

• 开启

set global slow_query_log=1

• 阀值配置

show variables like '%long_query_time%'

set global long_query_time=3

• 睡眠语句

select sleep(seconds);

• 慢查询条数

show global variables like '%Slow_queries%';

• mysqld配置文件 设置启动参数

slow_query_log=1
slow_query_log_file=xxxxx
long_query_time=3
log_output=FILE

• 日志分析工具mysqldumpslow

数据库脚本

• 批量插入脚本

set global log_bin_trust_function_creators=1;

• 创建一个mysql 函数function

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

• 创建存储过程

--SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- ----------------------


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 emp(emp_no,emp_name,job,mgr,hiredate,sal,comm,dept_no) VALUES((start+i),rand_string(6),'SALESMAN',0001,CURDATE(),200,400,rand_num());
UNTIL i=max_num
END REPEAT;
COMMIT;
END $$
delimiter ;

CALL insert_emp(1000,1008);
-- -------------------


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 dept(dept_no,dept_name,loc) VALUES ((start + i),rand_string(10),rand_string(8));
UNTIL i=max_num
END REPEAT;
COMMIT;
END $$
delimiter ;

CALL insert_dept(100,100);

show profile

• 开启profiling

SHOW VARIABLES LIKE '%profiling%';
SET profiling=on;

• 运行SQL
• 查看profile

show profiles;
SHOW PROFILE cpu,block io FOR QUERY 58;

全局查询日志

• my.cnf配置启用

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

• 编码启用

set global general_log=1;
set global log_output='TABLE';
-- 记录会保存到mysql库里的general_log表中

锁机制 MyISAM引擎

• 查看加锁情况

show open tables;

• 手动增加表锁

lock table 表名1 read(write),表名2 read(write),其他;

• 手动释放锁

unlock tables;

• 举例: session_1对dept表加read锁,seesion_1和session_2(泛指其他session)均可读dept，但是session_1无法对dept表写数据，也无法读取其他未锁定的表。session_2可以读取其他表，但是写数据进dept表被阻塞，等待获取锁。