MySQL篇-MySQL查询优化

简单案例

接口查询列表
image.png
在公司的系统中有一个接口查询耗时非常久大概每次查询十多秒
image.png
看了一下该数据库表的记录只有八十多万行，理论上不应该这么慢，开始查看对应的查询SQL

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
SELECT as_jz_user_info.id 'id', as_jz_user_info.user_name 'userName',
    as_jz_user_info.mobile 'mobile', as_jz_cooperation_company_info.id 'companyId',
    as_jz_cooperation_company_info.coperrate_state 'coperrateState',
    as_jz_cooperation_company_info.estimate_order_num 'estimateOrderNum',
    as_jz_cooperation_company_info.estimate_order_amount 'estimateOrderAmount',
    as_jz_cooperation_company_sales.customer_level_id 'customerLevelId',
    as_jz_cooperation_company_sales.customer_level_name 'customerLevelName',
    as_jz_user_info.company 'company', as_inv_industry_classification
    .level_one 'levelOne', as_inv_industry_classification.level_one_name 'levelOneName',
    as_inv_industry_classification.level_three 'levelThree',
    as_inv_industry_classification.level_three_name 'levelThreeName',
    as_jz_cooperation_company_info.
`customer_logo`
'logo', as_jz_user_info.create_time 'createTime',
    as_jz_cooperation_company_info.contract_date 'contractDate',
    as_jz_cooperation_company_info.contract_deadline 'contractDeadline',
    as_jz_cooperation_company_info.STATUS 'signStatus',
    as_jz_cooperation_company_sales.sign_up_name 'signName',
    as_jz_cooperation_company_sales.follow_up_name 'followName',
    as_tb_users_info.user_name 'followManageName', as_jz_user_info.customer_source 'customerSource',
    q.orderNum 'orderNum', q.orderAmount 'orderAmount', last_q.lastOrderNum 'lastOrderNum',
    last_q.lastOrderAmount 'lastOrderAmount'
FROM jz_user_info as_jz_user_info
LEFT JOIN jz_cooperation_company_info as_jz_cooperation_company_info
ON as_jz_user_info.company = as_jz_cooperation_company_info.company
LEFT JOIN jz_cooperation_company_sales as_jz_cooperation_company_sales
ON as_jz_cooperation_company_sales.company_id =
    as_jz_cooperation_company_info.id
LEFT JOIN tb_users_info as_tb_users_info
ON as_tb_users_info.fbi_user = as_jz_cooperation_company_info.manager_id
LEFT JOIN inv_industry_classification as_inv_industry_classification
ON as_inv_industry_classification.id = as_jz_cooperation_company_info
    .industry_id
LEFT JOIN
    (SELECT as_jz_user_info.company 'company', COUNT(as_jz_order_info
            .id)
        'orderNum', SUM(as_jz_order_info.payment_amount)
        'orderAmount'
        FROM jz_user_info as_jz_user_info LEFT JOIN jz_cooperation_company_info as_jz_cooperation_company_info ON as_jz_user_info
        .company = as_jz_cooperation_company_info.company LEFT JOIN jz_order_info as_jz_order_info ON as_jz_order_info
        .user_id = as_jz_user_info.id WHERE 1 = 1 AND as_jz_order_info
        .payment_status = '1'
        GROUP BY as_jz_user_info.company) q
ON q.company = as_jz_user_info.company
LEFT JOIN
    (SELECT as_jz_user_info.company 'company', COUNT(as_jz_order_info
            .id)
        'lastOrderNum', SUM(as_jz_order_info.payment_amount)
        'lastOrderAmount'
        FROM jz_user_info as_jz_user_info LEFT JOIN jz_cooperation_company_info as_jz_cooperation_company_info ON as_jz_user_info
        .company = as_jz_cooperation_company_info.company LEFT JOIN jz_order_info as_jz_order_info ON as_jz_order_info
        .user_id = as_jz_user_info.id WHERE 1 = 1 AND as_jz_order_info
        .create_time >= ?
        AND as_jz_order_info.create_time <= ?
        AND as_jz_order_info.payment_status = '1'
        GROUP BY as_jz_user_info.company) last_q
ON last_q.company = as_jz_user_info.company
WHERE 1 = 1
AND as_jz_user_info.company_id > 0
GROUP BY as_jz_user_info.id
ORDER BY as_jz_cooperation_company_info.manager_time desc,
    as_jz_user_info.id DESC limit ? , ? sg] == > Preparing: SELECT as_jz_user_info
    .id 'id', as_jz_user_info.user_name 'userName', as_jz_user_info.mobile 'mobile',
    as_jz_cooperation_company_info.id 'companyId',
    as_jz_cooperation_company_info.coperrate_state 'coperrateState',
    as_jz_cooperation_company_info.estimate_order_num 'estimateOrderNum',
    as_jz_cooperation_company_info.estimate_order_amount 'estimateOrderAmount',
    as_jz_cooperation_company_sales.customer_level_id 'customerLevelId',
    as_jz_cooperation_company_sales.customer_level_name 'customerLevelName',
    as_jz_user_info.company 'company', as_inv_industry_classification
    .level_one 'levelOne', as_inv_industry_classification.level_one_name 'levelOneName',
    as_inv_industry_classification.level_three 'levelThree',
    as_inv_industry_classification.level_three_name 'levelThreeName',
    as_jz_cooperation_company_info.
`customer_logo`
'logo', as_jz_user_info.create_time 'createTime',
    as_jz_cooperation_company_info.contract_date 'contractDate',
    as_jz_cooperation_company_info.contract_deadline 'contractDeadline',
    as_jz_cooperation_company_info.STATUS 'signStatus',
    as_jz_cooperation_company_sales.sign_up_name 'signName',
    as_jz_cooperation_company_sales.follow_up_name 'followName',
    as_tb_users_info.user_name 'followManageName', as_jz_user_info.customer_source 'customerSource',
    q.orderNum 'orderNum', q.orderAmount 'orderAmount', last_q.lastOrderNum 'lastOrderNum',
    last_q.lastOrderAmount 'lastOrderAmount'
FROM jz_user_info as_jz_user_info
LEFT JOIN jz_cooperation_company_info as_jz_cooperation_company_info
ON as_jz_user_info.company = as_jz_cooperation_company_info.company
LEFT JOIN jz_cooperation_company_sales as_jz_cooperation_company_sales
ON as_jz_cooperation_company_sales.company_id =
    as_jz_cooperation_company_info.id
LEFT JOIN tb_users_info as_tb_users_info
ON as_tb_users_info.fbi_user = as_jz_cooperation_company_info.manager_id
LEFT JOIN inv_industry_classification as_inv_industry_classification
ON as_inv_industry_classification.id = as_jz_cooperation_company_info
    .industry_id
LEFT JOIN
    (SELECT as_jz_user_info.company 'company', COUNT(as_jz_order_info
            .id)
        'orderNum', SUM(as_jz_order_info.payment_amount)
        'orderAmount'
        FROM jz_user_info as_jz_user_info LEFT JOIN jz_cooperation_company_info as_jz_cooperation_company_info ON as_jz_user_info
        .company = as_jz_cooperation_company_info.company LEFT JOIN jz_order_info as_jz_order_info ON as_jz_order_info
        .user_id = as_jz_user_info.id WHERE 1 = 1 AND as_jz_order_info
        .payment_status = '1'
        GROUP BY as_jz_user_info.company) q
ON q.company = as_jz_user_info.company
LEFT JOIN
    (SELECT as_jz_user_info.company 'company', COUNT(as_jz_order_info
            .id)
        'lastOrderNum', SUM(as_jz_order_info.payment_amount)
        'lastOrderAmount'
        FROM jz_user_info as_jz_user_info LEFT JOIN jz_cooperation_company_info as_jz_cooperation_company_info ON as_jz_user_info
        .company = as_jz_cooperation_company_info.company LEFT JOIN jz_order_info as_jz_order_info ON as_jz_order_info
        .user_id = as_jz_user_info.id WHERE 1 = 1 AND as_jz_order_info
        .create_time >= ?
        AND as_jz_order_info.create_time <= ?
        AND as_jz_order_info.payment_status = '1'
        GROUP BY as_jz_user_info.company) last_q
ON last_q.company = as_jz_user_info.company
WHERE 1 = 1
AND as_jz_user_info.company_id > 0
GROUP BY as_jz_user_info.id
ORDER BY as_jz_cooperation_company_info.manager_time desc,
    as_jz_user_info.id DESC limit ? , ?

该需要还需要有条件进行查询
image.png
通过分析发现耗时主要几种在订单金额、数量的统计中，和与订单表进行连接查询，其造成笛卡尔积较大导致查询效率低下

业务上的优化

经过和产品经理沟通，其上一年订单量、金额都是固定的，不以查询时间改动，所以对表进行添加两个字段进行记录，使用定时任务进行每一年统计一次，无需实时统计

使用子查询代替连接查询

以先查小表再连接大表

1
2
3
4
5
6
7
8
9
10
11
12
select jcci.company,
jcci.id,
industry_id,
contract_date,
contract_deadline,
estimate_order_num,
estimate_order_amount,
prev_year_order_num,
prev_year_order_amount
from jz_cooperation_company_info jcci
where 1 = 1
and manager_id = 76) jcci

建立索引

为连接条件，查询条件的字段建立索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 添加上一年统计字段
alter table jz_cooperation_company_info add prev_year_order_num bigint default 0 null comment '上一年订单数量';
alter table jz_cooperation_company_info add prev_year_order_amount double default 0 null comment '上一年订单金额';
# 添加索引
create index jz_user_info_company_id_index on jz_user_info (company_id)
create index jz_order_info_create_time_index	on jz_order_info (create_time)
# 添加订单状态、时间索引
create index jz_order_info_payment_status_index on jz_order_info (payment_status);
create index jz_cooperation_company_info_manager_id_index on jz_cooperation_company_info (manager_id);
create index jz_user_info_company_index on jz_user_info (company);
# 创建查询条件索引
create index jz_user_info_mobile_index on jz_user_info (mobile);
create index jz_user_info_user_name_index on jz_user_info (user_name);

改进后完整的SQL

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
explain
SELECT jcci.company                    company,
jcci.id                                companyId,
jcci.contract_date                     contractDate,
jcci.contract_deadline                 contractDeadline,
ifnull(jcci.estimate_order_num, 0)     estimateOrderNum,
ifnull(jcci.estimate_order_amount, 0)  estimateOrderAmount,
ifnull(jcci.prev_year_order_num, 0)    lastOrderNum,
ifnull(jcci.prev_year_order_amount, 0) lastOrderAmount,
jui.user_name                          userName,
jui.mobile                             mobile,
jui.customer_source                    customerSource,
iic.level_one                          levelOne,
iic.level_one_name                     levelOneName,
iic.level_three                        levelThree,
iic.level_three_name                   levelThreeName,
ifnull(COUNT(joi.id), 0)               'orderNum',
ifnull(SUM(joi.payment_amount), 0)     'orderAmount'
FROM (select jcci.company,
      jcci.id,
      industry_id,
      contract_date,
      contract_deadline,
      estimate_order_num,
      estimate_order_amount,
      prev_year_order_num,
      prev_year_order_amount
      from jz_cooperation_company_info jcci
      where 1 = 1
      and manager_id = 76) jcci
left join jz_user_info jui on jui.company = jcci.company
left join jz_order_info joi
ON joi.payment_status = '1' and joi.create_time between '2022-01-01' and '2022-08-30' and joi.user_id = jui.id
left join inv_industry_classification iic on iic.id = jcci.industry_id
where 1 = 1
GROUP BY jui.company
order by null
LIMIT 10

经优化后查询稳定在1秒左右

分析工具

explain分析工具

1
2
3
4
5
6
7
8
9
10
11
12
13
14
explain select jcci.id company_id, jcci.company
from jz_cooperation_company_info jcci
left join jz_user_info jui on jui.company = jcci.company
where jui.id in (
          select distinct(user_id)
          from jz_order_info joi
          where joi.payment_status = 1
          and joi.user_id in (
              select jui.id from jz_user_info jui
              where company in (
                  select jci.company from jz_cooperation_company_info jci where sales_id = 38
                )
            )
          and year(joi.payment_time) = 2022)

分析结果
image.png

参数详解

id

选择标识符

select_type

select_type:表示查询的类型。

  • SIMPLE：简单的select查询，查询中不包含子查询或者UNION
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为
  • SUBQUERY：在SELECT或WHERE列表中包含了子查询
  • DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表里
  • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT：从UNION表获取结果的SELECT

table

查询对应的表名或别名

type

type:表示表的连接类型

（从上到下，性能从差到好）

  • all 全表查询
  • index 索引全扫描
  • range 索引范围扫描
  • ref 使用非唯一或唯一索引的前缀扫描,返回相同值的记录
  • eq_ref 使用唯一索引，只返回一条记录
  • const,system 单表中最多只有一行匹配,根据唯一索引或主键进行查询
  • null 不访问表或索引就可以直接得到结果

possible_keys

表示查询时，可能使用的索引

key

表示实际使用的索引

key_len

索引字段的长度

ref

列与索引的比较

rows

扫描出的行数(估算的行数)

filtered

按表条件过滤的行百分比

Extra

执行情况的描述和说明

1
2
3
Using temporary（临时表,表示由于排序没有走索引）
Using filesort（没有使用索引的排序）
Using where（使用了索引）

耗时分析

查看MySQL对应的版本

1
2
select version();
show variables like '%version%';

开启profiling记录SQL耗时

1
2
3
4
5
6
7
8
9
10
11
12
# 查看是否已经开启
show variables like '%profiling%';
# 开启
set profiling  = 1;
# 设置记录条数
set profiling_history_size  = 50;
# 查看所有记录
show profiles;
# 查看指定记录的所有属性
show profile all for query 367;
# 关闭记录
set profiling  = 0;

总结建议

  1. 有时候在对一些时间字段建立索引但是查询并没有走索引，很大可能是事件量过多，导致全表扫描

  • 如果相同的查询比较频繁，尽量开启查询缓存

    1
    2
    3
    4
    # 查看是否开启查询缓存
    show variables like '%cache%';
    # 开启缓存 修改 /usr/my.cnf 重启MySQL
    query_cache_type = 1

  • 连接时条件尽量走索引

  • 尽量使用group by 的分组利用到联合索引

  • 尽量添加order by null避免filesort

相关资料

数据库组 > MySQL
#SQL优化
MySQL篇-MySQL查询优化
https://mikeygithub.github.io/2022/03/09/yuque/MySQL篇-MySQL查询优化/
作者
Mikey
发布于
2022年3月9日
许可协议