Database class notes 11 (slow query log)

analysis

● 1. Observe and run for at least one day to see the slow production
● 2. Start the slow query log, set the threshold value, for example, slow SQL is more than 5 seconds, and grab it
● 3.explain + slow SQL analysis
● 4.show profile
● 5. Optimize the parameters of SQL database server (O & M or DBA)

summary

● 1. Enable and capture slow query
● 2.explain + slow SQL analysis
● 3.show profile query SQL execution details in MySQL server
● 4. Parameter tuning of SQL database server

Slow query log

● slow query log of MySQL is a kind of log record provided by mysql, which is used to record the statements whose response time exceeds the threshold in mysql, specifically, the running time exceeds long_ query_ If the time is worth SQL, it will be recorded in the slow query log
● specifically, the operation time exceeds long_ query_ If time is worth SQL, it will be recorded in the slow query log. long_ query_ The default value of time is 10, which means to run statements for more than 10 seconds.
● let him check the SQL that exceeds our maximum endurance time. For example, if an SQL is executed for more than 5 seconds, we are even slow SQL. We hope to collect SQL that exceeds 5 seconds and conduct a comprehensive analysis in combination with the previous explain

How do you play?

● by default, MySQL database does not enable slow query log, so we need to set this parameter manually
● of course, if it is not necessary for tuning, it is generally not recommended to start this parameter, because starting the slow query log will have a certain performance impact more or less. Slow query log supports writing log records to files

Slow query log tool

s: Indicates how to sort
c: Number of visits
l: Lock time
r: Return record
t: Query time
al: average locking time
ar: average number of returned records
t: That is, the number of previous data returned

Get up to 10 SQL statements that return recordsets

mysqldumpslow -s r -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

Get the top 10 SQL accessed

mysqldumpslow -s c -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

Batch insert data

Functions and stored procedures

Department table

create table dept(
            id int primary key auto_increment,
            deptno mediumint not null,
            dname varchar(20) not null,
            loc varchar(13) not null
)engine=innodb default charset=gbk;

Employee table

create table emp(
            id int primary key auto_increment,
            empno mediumint not null,   
            ename varchar(20) not null, 
            job varchar(9) not null, 
            mgr mediumint not null,  
            hiredate DATE not null,  
            sal decimal(7,2) not null, 
            comm decimal(7,2) not  null, 
            deptno mediumint not null   
)engine=innodb default charset=gbk;

Create function

Create a function. If an error is reported: this function has none of DETERMINISTIC... View the parameters

set global log_bin_trust_function_creators=1;

Randomly generated string

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 $$

Randomly generated department number

DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$

Create stored procedure

insert data

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(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$