MySQL优化笔记其二

order by 优化

order by优化

order by优化2

慢查询

  • 查询配置
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表被阻塞,等待获取锁。
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页