High-performance MySQL (2) Slow query

I. Analysis of MySQL Query

1, first from the slow query start, slow query is the lowest cost, the most accurate measurement of query Time tool.

A, start MySQL slow query, modify my.conf

[Mysqld]
#开启慢查询
log_slow_queries = on
#指定日志文件存放位置, can be null, the system will give a default file Host_name-slow.log
log-slow-queries =/usr/ Local/mysq/var/slowquery.log
#记录超过的时间, the default is 10s
long_query_time = 1
#log下来没有使用索引的query, you can decide whether to open
log-queries-not-using-indexes
#执行速度较慢的管理命令 (such as Optimeze TABLE) will also be recorded
log-slow-admin-statements

b, view current information

c, write stored procedures, generate a table of 500W records

#生成随机字符串函数, calls
delimiter $$
create function rand_string (n int) returns varchar (255)
begin
in stored procedures DECLARE chars_str varchar () default ' abcdefghigklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz ';
DECLARE return_str varchar (255) default ";
declare i int default 0;
While I<n do
set return_str = Concat (return_str,substring chars_str,floor (1+rand () *52), 1);
Set i = i + 1;
End While;
return return_str;
End
$$
delimiter;
#生成随机数字, call
delimiter $$
create function Rand_num () returns INT (5)
begin
declare i int in stored procedures Default 0;
Set i = Floor (10+rand () *500);
return i;
End
$$
delimiter;
#定义存储过程
delimiter $$
CREATE PROCEDURE insert_emp (start int (ten), Max_num Int ())
begin
Declare i int default 0;
Set autocommit = 0;
Repeat
set i = i+1;
INSERT into EMP values ((start+i), rand_string (6), ' salesman ', 0001,2000,400,rand_num (), curdate ());
Until I = Max_num end
repeat;
End
$$

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/