欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL Execution Plan--合理利用隐式的业务逻辑

程序员文章站 2022-06-09 12:05:41
问题描述 优化过程中遇到一个SQL: 其执行计划为: 从执行计划来看,使用Using index(覆盖索引)已经是最优的执行计划,但每次查询扫描数据较多,影响整体查询性能。 优化方案 查询需要使用SUM计算user_value的总和,借用1+1+0+0+0+0+0=1+1=2的例子,进行如下测试: ......

问题描述

优化过程中遇到一个sql:

select
sum(user_value)
from user_log
where del_flag = 0
and product_id = 2324
and user_type = 1;

其执行计划为:

*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: user_log
   partitions: null
         type: ref
possible_keys: index_sid_ty_vl_ct,idx_product_id_oth1
          key: idx_product_id_oth1
      key_len: 12
          ref: const,const,const
         rows: 14884
     filtered: 100.00
        extra: using index
1 row in set, 1 warning (0.00 sec

从执行计划来看,使用using index(覆盖索引)已经是最优的执行计划,但每次查询扫描数据较多,影响整体查询性能。

 

优化方案

查询需要使用sum计算user_value的总和,借用1+1+0+0+0+0+0=1+1=2的例子,进行如下测试:

select
sum(case when user_value>0 then 1 else 0 end) as count1,
count(1) as count2
from user_log
where del_flag = 0
and product_id = 2324
and user_type = 1;

+--------+--------+
| count1 | count2 |
+--------+--------+
|    680 |   8067 |
+--------+--------+

在假设user_value没有负值的情况下,下面两条sql的结果相同:

##测试sql1
select
sum(user_value),
count(1) as count2
from user_log
where del_flag = 0
and product_id = 2324
and user_type = 1;

##测试sql2
select
sum(user_value)
from user_log
where del_flag = 0
and product_id = 2324
and user_type = 1
and user_value>0;

测试sql1的执行时间为0.00327250,其资源消耗为:

+----------------------+----------+----------+------------+--------------+---------------+-------+
| status               | duration | cpu_user | cpu_system | block_ops_in | block_ops_out | swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting             | 0.000066 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| checking permissions | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| opening tables       | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| init                 | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| system lock          | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| optimizing           | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| statistics           | 0.000127 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| preparing            | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| sending data         | 0.002867 | 0.002999 |   0.000000 |            0 |             0 |     0 |
| end                  | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| query end            | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| closing tables       | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| freeing items        | 0.000054 | 0.000000 |   0.000000 |            0 |             8 |     0 |
| cleaning up          | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
+----------------------+----------+----------+------------+--------------+---------------+-------+

而测试sql2的执行时间为0.00072325,其资源消耗为:

+----------------------+----------+----------+------------+--------------+---------------+-------+
| status               | duration | cpu_user | cpu_system | block_ops_in | block_ops_out | swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting             | 0.000072 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| checking permissions | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| opening tables       | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| init                 | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| system lock          | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| optimizing           | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| statistics           | 0.000089 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| preparing            | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| sending data         | 0.000365 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| end                  | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| query end            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| closing tables       | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| freeing items        | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |     0 |
| cleaning up          | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
+----------------------+----------+----------+------------+--------------+---------------+-------+

在sending data部分,两者在durion部分差距约10倍,而测试sql2在cpu_user部分差距更明显。

 

总结:

dba在优化sql时,除了从数据分布/索引结构等方面入手外,还需要从业务逻辑方面入手。

 

ps:上面的优化是假设user_value没有负值,而实际业务逻辑中user_value可能存在负值,因此以上优化纯属于瞎编。