MySQL5.7慢查询实践

总结

1. 获取慢查询SQL

• 已经执行完的SQL，检查慢查询日志，日志中有执行慢的SQL
• 正在执行中的SQL，show proccesslist;，结果中有执行慢的SQL

慢查询日志关键参数

2. 分析慢查询SQL explain 慢SQL

explain关键参数

type部分值

• 阿里java开发手册-泰山版，要求至少range

Extra部分值

3. 优化慢SQL

准备数据库

库

CREATE DATABASE IF NOT EXISTS test_slow DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
use test_slow;
1
2

表

CREATE TABLE `person_info_large` (  
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
    `code` VARCHAR (36),
    `name` VARCHAR (36),
    `title` VARCHAR (72),
	`location` VARCHAR (108),
    PRIMARY KEY `pk_id` (`id`),
    UNIQUE `uk_code` (`code`),
    KEY `idx_title_location`(`title`,`location`) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
1
2
3
4
5
6
7
8
9
10

行（java生成sql文件》导入sql文件）

package com.xcrj.gen;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.UUID;

public class Main {
    public static void main(String[] args) {
        generate(300 * 10000);
    }

    private static String rand36Str() {
        long time = System.currentTimeMillis();
        int random = (int) (Math.random() * Integer.MAX_VALUE);
        UUID uuid = new UUID(time, random);//随机种子
        return uuid.toString();
    }

    private static String rand36Str(int num) {
        StringBuilder sb = new StringBuilder();
        UUID uuid;
        for (int i = 0; i < num; i++) {
            uuid = UUID.randomUUID();
            sb.append(uuid.toString());
        }
        return sb.toString();
    }

    private static void generate(int size) {
        String row = "INSERT INTO researcher(`code`,`name`,`title`,`location`) VALUE(%s);";
//        System.out.println(String.format(sql,IdWorker.getId()));
        String path = "./test_slow_researcher.sql";
        File file = new File(path);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        try (
                FileOutputStream fos = new FileOutputStream(path);
                BufferedOutputStream bos = new BufferedOutputStream(fos);
        ) {
            for (int i = 0; i < size; i++) {
                StringBuilder sb = new StringBuilder();
                String code = rand36Str(1);
                String name = rand36Str(1);
                String title = rand36Str(2);
                String location = rand36Str(3);
                sb.append("'").append(code).append("'").append(",")
                        .append("'").append(name).append("'").append(",")
                        .append("'").append(title).append("'").append(",")
                        .append("'").append(location).append("'");

                bos.write(String.format(row, sb.toString()).getBytes());

                if (i < size - 1) {
                    bos.write("\n".getBytes());
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

开启慢查询日志

# 检查默认值
show variables like '%quer%';

# 开启慢查询日志
set global slow_query_log=on;
# 设置慢查询阈值为1s
set global long_query_time=1;
# 查看慢查询日志路径
show global variables like 'slow_query_log_file'

# 检查设置值，若发现未生效，关闭当前会话（关闭数据库）重新打开，再检查
show variables like '%quer%';
1
2
3
4
5
6
7
8
9
10
11
12

慢查询测试1，已经执行完的慢查询

# 统计
SELECT count(*) FROM researcher;
# 无索引列
SELECT `name` FROM researcher ORDER BY `name` DESC;
# 有索引列
SELECT `code` FROM researcher ORDER BY `code` DESC;
# 查询慢查询日志文件地址 /var/lib/mysql/333a2bf4a87e-slow.log
show variables like '%quer%';
# 查看慢查询日志
more /var/lib/mysql/333a2bf4a87e-slow.log
# 分析SQL
explain SELECT count(*) FROM researcher;
explain SELECT `name` FROM researcher ORDER BY `name` DESC;
explain SELECT `code` FROM researcher ORDER BY `code` DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT count(*) FROM researcher;

• Query_time: 大于18s

SELECT nameFROM researcher ORDER BYname DESC;

• Query_time: 大于19s

SELECT codeFROM researcher ORDER BYcode DESC;

• Query_time: 大于16s

explain SELECT count(*) FROM researcher;

• key=uk_code，可知 count(*) 会使用索引
• type=index
• extra=Using index

explain SELECT nameFROM researcher ORDER BYname DESC;

• key=NULL
• type=ALL
• extra=Using filesort

explain SELECT codeFROM researcher ORDER BYcode DESC;

• key=uk_code
• type=index
• extra=Using index

explain SELECT idFROM researcher ORDER BYid DESC;

• key=primary
• type=index
• extra=Using index

参数说明

慢查询日志部分参数

explain部分值

type部分值

• 阿里java开发手册-泰山版，要求至少range

Extra部分值

select_type部分值

慢查询测试2，正在执行的慢查询

SELECT `name` FROM researcher ORDER BY `name` DESC;
show processlist;
1
2

• Time=34，已经执行了34s

恢复默认参数

# 检查默认值
show variables like '%quer%';
# 开启慢查询日志
set global slow_query_log=off;
# 设置慢查询阈值为1s
set global long_query_time=10;
# 检查设置值，若发现未生效，关闭当前会话（关闭数据库）重新打开，再检查
show variables like '%quer%';
# 重置表，包括自增ID
TRUNCATE TABLE researcher;
1
2
3
4
5
6
7
8
9
10