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%' ;
slow_query_log=1
slow_query_log_file=xxxxx
long_query_time=3
log_output=FILE
数据库脚本
set global log_bin_trust_function_creators= 1 ;
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 ;
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
SHOW VARIABLES LIKE '%profiling%' ;
SET profiling= on ;
show profiles;
SHOW PROFILE cpu, block io FOR QUERY 58 ;
全局查询日志
#开启
general_log=1
#记录日志文件的路径
general_log_file=*****
#输出格式
log_output=FILE
set global general_log= 1 ;
set global log_output= 'TABLE' ;
锁机制 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表被阻塞,等待获取锁。