MySQL query performance optimization (to be continued 6/04)

Article Directory

MySQL query performance optimization

1. Slow query

1) Concept:

A log record provided by Mysql. SQL that runs longer than the value of long_querry_time will be recorded in the slow query log.

long_querry_time is equal to 10s by default.

2) What is slow query

A log record provided by Mysql. SQL commands whose query time exceeds long_query_time will be recorded in the query log. Long_querry_time is equal to 10s by default.

3) How to play slow query

(1) Description: By default, mysql does not enable the slow query date, and manually enable it when tuning is required. Usually, it is recommended not to enable it.
(2) Check whether
show variables like'%slow_query_log%'
is enabled ; set it to 1, enable the slow query log
set global slow_query_log = 1;
(3) After the slow log is turned on, what kind of SQL will be recorded in the query log?
show variables like'long_query_time%'; View the slow query time threshold
Set the slow query time threshold to 3s, more than 3s is slow SQL, it will take effect only after relinking or opening a new
session (4) View the number of slow SQL records

Insert picture description here

4) Log analysis tool mysqldumpslow

mysqldumpslow --help to view specific information
-s indicates which method is used
-c access times
-l lock time
-r return records
-t query time
-al average lock time
-ar average number of records returned
-at average query time
-t return How many of the first -t top specifies to take the first few days as the result output
-g followed by a regular expression
Syntax:

Insert picture description here


Insert picture description here


In the mysql configuration file mysqld.cnf:

[mysqld]
#指定日志文件存放位置,可以为空,系统会给一个缺省的文件 host_name-slow.log
log-slow-queries=/var/lib/mysql/slowquery.log
#记录超过的时间,默认为 10s
long_query_time=2
#log 下来没有使用索引的 query,可以根据情况决定是否开启
log-queries-not-using-indexes
#如果设置了,所有没有使用索引的查询也将被记录
log-long-format
#设置为 0 后,之后所有的查询操作被会被记录在慢查询日志;
set long_query_time=0;

5) Specific operation code

查看慢查询的时间阈值,默认显示为 10S
SHOW VARIABLES LIKE 'long_query_time%';

查看是否开启
SHOW VARIABLES LIKE '%slow_query_log%';

设置为 1,开启慢查询日志
set global slow_query_log=1;

设置慢查询的时间为 0.001,超过 0.001 就显示为慢查询
set global long_query_time=0.001;

睡眠 1s
select * from sleep(1);

查询慢查询的条数,需要优化的条数
show global status like '%Slow_queries';

路径就是查询是否开启中 show_query_log_file 的文件路径
mysqldumpslow -s r -t /var/lib/mysql/RMT01-slow.log;

2. Execution plan

1) Inquiry process

Insert picture description here

2) Execution plan

{explain | DESC | DESCRIBE} + SQL

(1) explain introduction:

Use the explain keyword to simulate the optimizer's execution of SQL query statements, so as to know how mysql processes your SQL statements, so as to analyze the performance bottleneck of your query statements or table structure:
You can get the following results:

① Table reading order
② Operation type of data reading operation
③ Which indexes can be used
④ Which indexes can be actually used
⑤ Reference between tables
⑥ How many rows in each table are queried by the optimizer

The parameters obtained by explain are as follows:

Insert picture description here


Insert picture description here


Insert picture description here

(2) id

Insert picture description here


The id is the same, the

Insert picture description here


id is executed from top to bottom , the priority from the higher id value is higher, and the higher id is executed first

Insert picture description here

(3) select-type

Insert picture description here


Word query and SUBQUERY

Insert picture description here


DERIVED derived query (the statement appears after from)

Insert picture description here


UNION combined query

Insert picture description here

(4) type (most important)

Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


After the id here is a non-unique index, the const of type is reduced to ref.

Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Summary:

Insert picture description here

(5)possible_keys

CREATE table t1(
	id int(11) NOT NULL auto_increment,
  col1 VARCHAR(20) DEFAULT NULL,
	col2 VARCHAR(20) DEFAULT NULL,
	col3 VARCHAR(20) DEFAULT NULL,
	other_column CHAR(20) DEFAULT NULL,
	PRIMARY KEY (id),
	KEY idx_t1 (other_column),
	KEY col1_col2_col3_idx (col1,col2,col3),
	KEY col1_idx (col1)

);

col1 is the index

Insert picture description here

(6) key (If it is NULL, it means that no index is used)

Insert picture description here

(7) key_len

1) The value displayed by key_len is the maximum possible length of the index field , not the actual length used, that is, key_len is calculated according to the definition, not retrieved from the table.
2) The index usage can be judged according to the number of bytes , especially in When combining indexes

An index is 20 bytes. A null is 1 byte in latin, a total of 21 bytes.

Insert picture description here


An index: utf8mb4 a character 4 bytes, 20*4
2 means varchar is a variable length field, 1 is the default null (the mark can be empty and needs to occupy 1 Bytes )

Insert picture description here


Insert picture description here

(8) ref

Insert picture description here

(9) rows

Insert picture description here

(10) extra

using filesort does not use the index to sort

Insert picture description here

using index used to cover the index

using tempory used a temporary table

Insert picture description here
Insert picture description here

3.SQL optimization strategy