110.三条慢SQL的优化及分析

最新推荐文章于 2024-11-08 14:07:13 发布
最新推荐文章于 2024-11-08 14:07:13 发布
阅读量388 收藏 10
点赞数 6
分类专栏： 数据库运维-MySQL 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43346403/article/details/135775100
版权

1.找出最近50W行慢日志中找出较慢的SQL;

# Query_time: 16.804718  Lock_time: 0.000367 Rows_sent: 10  Rows_examined: 636065
# Query_time: 11.321155  Lock_time: 0.000570 Rows_sent: 1  Rows_examined: 225418
# Query_time: 13.508679  Lock_time: 0.000112 Rows_sent: 1  Rows_examined: 186589

2.SQL1分析

--SQL1:优化:# Query_time: 16.804718
use underwriting;
SET timestamp=1705975491;
SELECT
            m.UUID,
            max( if ( m.RISKCODE like '10%', m.PROPOSALNO, null ) ) as contractNoCi,
            max( if ( mm.RISKCODE not like '10%', mm.PROPOSALNO, null ) ) as contractNoBi,
            t.PLATENO as plateNo,
            t.VEHICLEKINDCODE as vehicleKindCode,
            h.APPLINAME as applicantName,
            h.INSUREDNAME as insuredName,
            h.OPERATORCODE as operatorCode,
            h.OPERATORNAME as operatorName,
        h.SYSTEMCODE as systemCode,
max( if ( m.RISKCODE like '10%', IFNULL(m.TOTALPREMIUM,0)+IFNULL(m.SUMTAX,0), null ) ) as premiumsCi,
            max( if ( mm.RISKCODE not like '10%', IFNULL(mm.TOTALPREMIUM,0), null ) ) as premiumsBi,
            max(IFNULL(m.SUMTAX,0)) as sumTax,
            max( if ( m.RISKCODE like '10%', m.VALIDDATE, null ) ) as validDateCi,
            max( if ( m.RISKCODE like '10%', m.EXPIRYDATE, null ) ) as expiryDateCi,
            max( if ( mm.RISKCODE not like '10%', mm.VALIDDATE, null ) ) as validDateBi,
            max( if ( mm.RISKCODE not like '10%', mm.EXPIRYDATE, null ) ) as expiryDateBi,
            max( if ( m.RISKCODE like '10%', m.BUSINESSSTATUS, null ) )  as businessStatusCi,
            max( if ( mm.RISKCODE not like '10%', mm.BUSINESSSTATUS, null ) ) as currentStatusBi,
            max( if ( m.RISKCODE like '10%', m.RISKCODE, null ) ) as riskCodeCi,
            max( if ( mm.RISKCODE not like '10%', mm.RISKCODE, null ) ) as riskCodeBi,
            if(tt.ATTENTIONID is null,'0','1')as isAttention,
           tt.ATTENTIONID as attentionId,
            h.amlterrorFlag as amlterrorFlag,
            h.AMLTERRORTYPE as amlterrorType,
            x.fraud as fraud,
            h.signStatus as signStatus
    from
            prpthead h LEFT JOIN prpattention tt ON  (h.UUID = tt.UUID and tt.USERCODE='01A00016' and tt.BUSINESSTYPE='T'),
            prptmain m INNER JOIN prptmain mm on m.UUID = mm.UUID,   --m和mm表合并仅访问一次。
            prptitemcar t,
            prptsaleinfo s,
        prptitemcarext x
    where
            h.UUID = m.UUID
            and h.UUID = t.UUID
            and h.UUID = s.UUID
            and s.UUID = x.UUID
            and h.VALIDSTATUS = '1'
    AND s.COMCODE LIKE CONCAT('01','%')
     AND h.ISSUEDATE >='2024-01-17 00:00:00.0'
     AND h.ISSUEDATE < DATE_ADD('2024-01-23 00:00:00.0',INTERVAL 1 DAY)
    AND
     (
            s.COMCODE LIKE CONCAT('01','%')   --这个条件多写了一次。
     )
            group by m.UUID
ORDER BY h.UPDATETIME desc limit 10;


--查看SQL的执行计划。
explain SELECT
m.UUID,
max( if ( m.RISKCODE like '10%', m.PROPOSALNO, null ) ) as contractNoCi,
max( if ( mm.RISKCODE not like '10%', mm.PROPOSALNO, null ) ) as contractNoBi,
t.PLATENO as plateNo,
t.VEHICLEKINDCODE as vehicleKindCode,
h.APPLINAME as applicantName,
h.INSUREDNAME as insuredName,
h.OPERATORCODE as operatorCode,
h.OPERATORNAME as operatorName,
h.SYSTEMCODE as systemCode,
max( if ( m.RISKCODE like '10%', IFNULL(m.TOTALPREMIUM,0)+IFNULL(m.SUMTAX,0), null ) ) as premiumsCi,
max( if ( mm.RISKCODE not like '10%', IFNULL(mm.TOTALPREMIUM,0), null ) ) as premiumsBi,
max(IFNULL(m.SUMTAX,0)) as sumTax,
max( if ( m.RISKCODE like '10%', m.VALIDDATE, null ) ) as validDateCi,
max( if ( m.RISKCODE like '10%', m.EXPIRYDATE, null ) ) as expiryDateCi,
max( if ( mm.RISKCODE not like '10%', mm.VALIDDATE, null ) ) as validDateBi,
max( if ( mm.RISKCODE not like '10%', mm.EXPIRYDATE, null ) ) as expiryDateBi,
max( if ( m.RISKCODE like '10%', m.BUSINESSSTATUS, null ) )  as businessStatusCi,
max( if ( mm.RISKCODE not like '10%', mm.BUSINESSSTATUS, null ) ) as currentStatusBi,
max( if ( m.RISKCODE like '10%', m.RISKCODE, null ) ) as riskCodeCi,
max( if ( mm.RISKCODE not like '10%', mm.RISKCODE, null ) ) as riskCodeBi,
if(tt.ATTENTIONID is null,'0','1')as isAttention,
tt.ATTENTIONID as attentionId,
h.amlterrorFlag as amlterrorFlag,
h.AMLTERRORTYPE as amlterrorType,
x.fraud as fraud,
h.signStatus as signStatus
from
	(select * from prpthead h  
	,prptmain m 
	where h.UUID = m.UUID 
	and h.ISSUEDATE >='2024-01-17 00:00:00'
	AND h.ISSUEDATE < DATE_ADD('2024-01-23 00:00:00',INTERVAL 1 DAY)
	) h 
LEFT JOIN prpattention tt ON  (h.UUID = tt.UUID and tt.USERCODE='01A00016' and tt.BUSINESSTYPE='T'),
prptmain m INNER JOIN prptmain mm on m.UUID = mm.UUID,
prptitemcar t,
prptsaleinfo s,
prptitemcarext x
where
h.UUID = m.UUID
and h.UUID = t.UUID
and h.UUID = s.UUID
and s.UUID = x.UUID
and h.VALIDSTATUS = '1'
AND s.COMCODE LIKE CONCAT('01','%')
group by h.UUID
ORDER BY h.UPDATETIME desc limit 10;
--查看执行计划
+----+-------------+-------+------------+--------+-------------------------------+------------------------+---------+---------------------+--------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key    | key_len | ref | rows   | filtered | Extra               |
+----+-------------+-------+------------+--------+-------------------------------+------------------------+---------+---------------------+--------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | h     | NULL       | range  | PRIMARY,idx_head_issueDate    | idx_head_issueDate     | 6       | NULL| 138080 |    10.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | tt    | NULL       | ref    | idx_attention_usercode        | idx_attention_usercode | 43      | const               |      1 |   100.00 | Using where         |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY,idx_tsaleinfo_comCode | PRIMARY| 162     | underwriting.h.UUID |      1 |    50.00 | Using where         |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY       | PRIMARY| 162     | underwriting.h.UUID |      1 |   100.00 | NULL|
|  1 | SIMPLE      | x     | NULL       | eq_ref | PRIMARY       | PRIMARY| 162     | underwriting.h.UUID |      1 |   100.00 | NULL|
|  1 | SIMPLE      | m     | NULL       | ref    | idx_tmain_uuid| idx_tmain_uuid         | 162     | underwriting.h.UUID |      1 |   100.00 | NULL|
|  1 | SIMPLE      | mm    | NULL       | ref    | idx_tmain_uuid| idx_tmain_uuid         | 162     | underwriting.h.UUID |      1 |   100.00 | NULL|
+----+-------------+-------+------------+--------+-------------------------------+------------------------+---------+---------------------+--------+----------+---------------------------------------------------------------------+
7 rows in set, 1 warning (0.01 sec)


--合并:prptmain的两次访问为一次访问。
--改写后SQL如下,查看其执行计划
explain SELECT
m.UUID,
max( if ( m.RISKCODE like '10%', m.PROPOSALNO, null ) ) as contractNoCi,
max( if ( m.RISKCODE not like '10%', m.PROPOSALNO, null ) ) as contractNoBi,
t.PLATENO as plateNo,
t.VEHICLEKINDCODE as vehicleKindCode,
h.APPLINAME as applicantName,
h.INSUREDNAME as insuredName,
h.OPERATORCODE as operatorCode,
h.OPERATORNAME as operatorName,
h.SYSTEMCODE as systemCode,
max( if ( m.RISKCODE like '10%', IFNULL(m.TOTALPREMIUM,0)+IFNULL(m.SUMTAX,0), null ) ) as premiumsCi,
max( if ( m.RISKCODE not like '10%', IFNULL(m.TOTALPREMIUM,0), null ) ) as premiumsBi,
max(IFNULL(m.SUMTAX,0)) as sumTax,
max( if ( m.RISKCODE like '10%', m.VALIDDATE, null ) ) as validDateCi,
max( if ( m.RISKCODE like '10%', m.EXPIRYDATE, null ) ) as expiryDateCi,
max( if ( m.RISKCODE not like '10%', m.VALIDDATE, null ) ) as validDateBi,
max( if ( m.RISKCODE not like '10%', m.EXPIRYDATE, null ) ) as expiryDateBi,
max( if ( m.RISKCODE like '10%', m.BUSINESSSTATUS, null ) )  as businessStatusCi,
max( if ( m.RISKCODE not like '10%', m.BUSINESSSTATUS, null ) ) as currentStatusBi,
max( if ( m.RISKCODE like '10%', m.RISKCODE, null ) ) as riskCodeCi,
max( if ( m.RISKCODE not like '10%', m.RISKCODE, null ) ) as riskCodeBi,
if(tt.ATTENTIONID is null,'0','1')as isAttention,
tt.ATTENTIONID as attentionId,
h.amlterrorFlag as amlterrorFlag,
h.AMLTERRORTYPE as amlterrorType,
x.fraud as fraud,
h.signStatus as signStatus
from
prpthead h 
LEFT JOIN prpattention tt ON  (h.UUID = tt.UUID and tt.USERCODE='01A00016' and tt.BUSINESSTYPE='T'),
prptmain m ,
prptitemcar t,
prptsaleinfo s,
prptitemcarext x
where
h.UUID=m.uuid
and h.UUID = t.UUID
and h.UUID = s.UUID
and s.UUID = x.UUID
and h.VALIDSTATUS = '1'
AND s.COMCODE LIKE CONCAT('01','%')
AND h.ISSUEDATE >='2024-01-17 00:00:00'
AND h.ISSUEDATE < DATE_ADD('2024-01-23 00:00:00',INTERVAL 1 DAY)
group by m.UUID
ORDER BY h.UPDATETIME desc limit 10;

--优化2:
+----+-------------+-------+------------+--------+-------------------------------+------------------------+---------+---------------------+--------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key    | key_len | ref | rows   | filtered | Extra               |
+----+-------------+-------+------------+--------+-------------------------------+------------------------+---------+---------------------+--------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | h     | NULL       | range  | PRIMARY,idx_head_issueDate    | idx_head_issueDate     | 6       | NULL| 140064 |    10.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | tt    | NULL       | ref    | idx_attention_usercode        | idx_attention_usercode | 43      | const               |      1 |   100.00 | Using where         |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY,idx_tsaleinfo_comCode | PRIMARY| 162     | underwriting.h.UUID |      1 |    50.00 | Using where         |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY       				 | PRIMARY| 162     | underwriting.h.UUID |      1 |   100.00 | NULL|
|  1 | SIMPLE      | x     | NULL       | eq_ref | PRIMARY       				 | PRIMARY| 162     | underwriting.h.UUID |      1 |   100.00 | NULL|
|  1 | SIMPLE      | m     | NULL       | ref    | idx_tmain_uuid				 | idx_tmain_uuid         | 162     | underwriting.h.UUID |      1 |   100.00 | NULL|
+----+-------------+-------+------------+--------+-------------------------------+------------------------+---------+---------------------+--------+----------+---------------------------------------------------------------------+

--

Using filesort:不是指使用文件排序,而是指有排序
Using temporary:使用了临时表存储中间结果集
using where:使用索引的情况下，需要回表查询所需的数据
using index condition:使用了索引，但需要回表查询数据

虽然少访问一次prptmain 表,但是所有表都使用了索引,且需要分组并排序的结果集本身很大。
所以该SQL已经是最优的了,执行时间变为5s;

10 rows in set (5.22 sec)
root@localhost [underwriting]>
--再次执行性能有提升,主要原因是执行结果已经缓存在内存中.
--后续多次运行也都是5s左右运行结束。

3.SQL2分析及优化

# Query_time: 11.321155


# Query_time: 11.321155  Lock_time: 0.000570 Rows_sent: 1  Rows_examined: 225418
SET timestamp=1705976828;
select count(1) from (select * from (SELECT
h.UUID,
max( if ( m.RISKCODE like '10%', m.policyNo, null ) ) as contractNoCi,
max( if ( m.RISKCODE not like '10%', m.policyNo, null ) ) as contractNoBi,
t.PLATENO as plateNo,
t.VEHICLEKINDCODE as vehicleKindCode,
h.APPLINAME AS applicantName,
h.INSUREDNAME AS insuredName,
ifnull(t.noLicenseFlag,'0') as noLicenseFlag,
max( if ( m.RISKCODE like '10%', IFNULL(m.TOTALPREMIUM,0)+IFNULL(m.SUMTAX,0), null ) ) as premiumsCi,
max( if ( m.RISKCODE not like '10%', m.TOTALPREMIUM, null ) ) as premiumsBi,
max(IFNULL(m.SUMTAX,0)) as sumTax,
max( if ( m.RISKCODE like '10%', m.VALIDDATE, null ) ) as validDateCi,
max( if ( m.RISKCODE like '10%', m.EXPIRYDATE, null ) ) as expiryDateCi,
max( if ( m.RISKCODE like '10%', m.BUSINESSSTATUS, null ) ) as businessStatusCi,
max( if ( m.RISKCODE like '10%', m.withdrawStatus, null ) ) as withdrawStatusCi,
max( if ( m.RISKCODE not like '10%', m.VALIDDATE, null ) ) as validDateBi,
max( if ( m.RISKCODE not like '10%', m.EXPIRYDATE, null ) ) as expiryDateBi,
max( if ( m.RISKCODE not like '10%', m.BUSINESSSTATUS, null ) ) as currentStatusBi,
max( if ( m.RISKCODE not like '10%', m.withdrawStatus, null ) ) as withdrawStatusBi,
max( if ( m.RISKCODE like '10%', m.RISKCODE, null ) ) as riskCodeCi,
max( if ( m.RISKCODE not like '10%', m.RISKCODE, null ) ) as riskCodeBi,
h.operatorName as operatorName,
h.operatorCode as operatorCode,
"POLICY" as businessType,
s.SALESMANNAME as handlerName,
s.SALESMANCODE as handlerCode,
'COMMON' as correctType
from
prpchead h ,
prpcmain m,
prpcitemcar t,prpcsaleinfo s
where
h.UUID = m.UUID
and h.UUID = t.UUID
and h.uuid=s.uuid
 AND h.APPLINAME  LIKE CONCAT('%','袁斌','%')
 AND s.COMCODE LIKE CONCAT('010103','%')
group by h.UUID) ii where
case when ii.correctType ='LICENSING' THEN
(ii.riskCodeBi !='1234' or ii.riskCodeBi is null ) ELSE 1=1 end
and (case when ii.withdrawStatusBi ='0' and  ii.withdrawStatusCi='0' THEN
	(ii.expiryDateBi >NOW() or ii.expiryDateCi >NOW() ) and ii.currentStatusBi='8' and ii.businessStatusCi='8'
	when ii.withdrawStatusCi ='0' and  (ii.withdrawStatusBi !='0' or ii.withdrawStatusBi is NULL) THEN
		ii.expiryDateCi >NOW() and ii.businessStatusCi='8'
	when ii.withdrawStatusBi ='0' and  (ii.withdrawStatusCi !='0' or ii.withdrawStatusCi is null) THEN
		ii.expiryDateBi >NOW() and ii.currentStatusBi='8'
END)) tmp_count;


--查看执行计划 
explain 
select count(1) from (select * from (SELECT
h.UUID,
max( if ( m.RISKCODE like '10%', m.policyNo, null ) ) as contractNoCi,
max( if ( m.RISKCODE not like '10%', m.policyNo, null ) ) as contractNoBi,
t.PLATENO as plateNo,
t.VEHICLEKINDCODE as vehicleKindCode,
h.APPLINAME AS applicantName,
h.INSUREDNAME AS insuredName,
ifnull(t.noLicenseFlag,'0') as noLicenseFlag,
max( if ( m.RISKCODE like '10%', IFNULL(m.TOTALPREMIUM,0)+IFNULL(m.SUMTAX,0), null ) ) as premiumsCi,
max( if ( m.RISKCODE not like '10%', m.TOTALPREMIUM, null ) ) as premiumsBi,
max(IFNULL(m.SUMTAX,0)) as sumTax,
max( if ( m.RISKCODE like '10%', m.VALIDDATE, null ) ) as validDateCi,
max( if ( m.RISKCODE like '10%', m.EXPIRYDATE, null ) ) as expiryDateCi,
max( if ( m.RISKCODE like '10%', m.BUSINESSSTATUS, null ) ) as businessStatusCi,
max( if ( m.RISKCODE like '10%', m.withdrawStatus, null ) ) as withdrawStatusCi,
max( if ( m.RISKCODE not like '10%', m.VALIDDATE, null ) ) as validDateBi,
max( if ( m.RISKCODE not like '10%', m.EXPIRYDATE, null ) ) as expiryDateBi,
max( if ( m.RISKCODE not like '10%', m.BUSINESSSTATUS, null ) ) as currentStatusBi,
max( if ( m.RISKCODE not like '10%', m.withdrawStatus, null ) ) as withdrawStatusBi,
max( if ( m.RISKCODE like '10%', m.RISKCODE, null ) ) as riskCodeCi,
max( if ( m.RISKCODE not like '10%', m.RISKCODE, null ) ) as riskCodeBi,
h.operatorName as operatorName,
h.operatorCode as operatorCode,
"POLICY" as businessType,
s.SALESMANNAME as handlerName,
s.SALESMANCODE as handlerCode,
'COMMON' as correctType
from
prpchead h ,
prpcmain m,
prpcitemcar t,
prpcsaleinfo s
where
h.UUID = m.UUID
and h.UUID = t.UUID
and h.uuid=s.uuid
 AND h.APPLINAME  LIKE CONCAT('%','袁斌','%')
 AND s.COMCODE LIKE CONCAT('010103','%')
group by h.UUID) ii where
case when ii.correctType ='LICENSING' THEN
(ii.riskCodeBi !='1234' or ii.riskCodeBi is null ) ELSE 1=1 end
and (case when ii.withdrawStatusBi ='0' and  ii.withdrawStatusCi='0' THEN
 (ii.expiryDateBi >NOW() or ii.expiryDateCi >NOW() ) and ii.currentStatusBi='8' and ii.businessStatusCi='8'
 when ii.withdrawStatusCi ='0' and  (ii.withdrawStatusBi !='0' or ii.withdrawStatusBi is NULL) THEN
 ii.expiryDateCi >NOW() and ii.businessStatusCi='8'
 when ii.withdrawStatusBi ='0' and  (ii.withdrawStatusCi !='0' or ii.withdrawStatusCi is null) THEN
 ii.expiryDateBi >NOW() and ii.currentStatusBi='8'
END)) tmp_count;
+----+-------------+------------+------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------+--------+----------+--------------------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                                                                                                                                                                     | key                   | key_len | ref                 | rows   | filtered | Extra                                                  |
+----+-------------+------------+------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------+--------+----------+--------------------------------------------------------+
|  1 | PRIMARY     | <derived3> | NULL       | ALL    | NULL                                                                                                                                                                              | NULL                  | NULL    | NULL                |  41820 |   100.00 | Using where                                            |
|  3 | DERIVED     | s          | NULL       | range  | PRIMARY,idx_csaleinfo_comCode                                                                                                                                                     | idx_csaleinfo_comCode | 83      | NULL                | 273714 |   100.00 | Using index condition; Using temporary; Using filesort |
|  3 | DERIVED     | h          | NULL       | eq_ref | PRIMARY,idx_chead_operatorCode,idx_chead_operateDate,idx_chead_issueDate,idx_chead_UPDATETIME,idx_chead_inputdate,idx_chead_applicantName,idx_chead_insuredName,idx_chead_makeCom | PRIMARY               | 162     | underwriting.s.UUID |      1 |    11.11 | Using where                                            |
|  3 | DERIVED     | t          | NULL       | eq_ref | PRIMARY                                                                                                                                                                           | PRIMARY               | 162     | underwriting.s.UUID |      1 |   100.00 | NULL                                                   |
|  3 | DERIVED     | m          | NULL       | ref    | idx_cmain_uuid                                                                                                                                                                    | idx_cmain_uuid        | 162     | underwriting.s.UUID |      1 |   100.00 | NULL                                                   |
+----+-------------+------------+------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------+--------+----------+--------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

ID=3的四个表都使用了索引,已经是最优的了。
prpcsaleinfo s,s表的访问数据较大,但他查询的是所需的所有数据了。
最外层是派生表,不能使用索引。

explain select * from prpcsaleinfo s where s.COMCODE LIKE CONCAT('010103','%');
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys         | key                   | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | s     | NULL       | range | idx_csaleinfo_comCode | idx_csaleinfo_comCode | 83      | NULL | 273714 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

ID=3的四个表都使用了索引,已经是最优的了。

4.SQL3优化 

# Query_time: 13.508679

# Query_time: 13.508679  Lock_time: 0.000112 Rows_sent: 1  Rows_examined: 186589
use underwriting;
SET timestamp=1705977205;

select count(1) from (
 SELECT DISTINCT g.UUID FROM PRPJPAYFEEINFO g WHERE 1=1
 AND g.DEDUCTIONFLAG= '0'
 AND g.COMPANYCODE LIKE CONCAT('0104020107','%')
 AND(g.COMPANYCODE LIKE CONCAT('0104020106','%')OR g.COMPANYCODE LIKE CONCAT('0104020107','%'))
 and g.BUSINESSTYPE = 'T'
 and g.PLATENO = '湘A8D900'
 and g.ISSUEDATE >= '2024-01-03'
 and g.ISSUEDATE < DATE_ADD('2024-01-23',interval 1 day)
) tmp_count;

--查看执行计划:
explain 
select count(1) from (
 SELECT DISTINCT g.UUID FROM PRPJPAYFEEINFO g WHERE 1=1
 AND g.DEDUCTIONFLAG= '0'
 AND g.COMPANYCODE LIKE CONCAT('0104020107','%')
 AND(g.COMPANYCODE LIKE CONCAT('0104020106','%')OR g.COMPANYCODE LIKE CONCAT('0104020107','%'))
 and g.BUSINESSTYPE = 'T'
 and g.PLATENO = '湘A8D900'
 and g.ISSUEDATE >= '2024-01-03'
 and g.ISSUEDATE < DATE_ADD('2024-01-23',interval 1 day)
) tmp_count;
+----+-------------+------------+------------+-------+-------------------------------+-----------+---------+------+--------+----------+-----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys                 | key       | key_len | ref  | rows   | filtered | Extra                                               |
+----+-------------+------------+------------+-------+-------------------------------+-----------+---------+------+--------+----------+-----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL                          | NULL      | NULL    | NULL |      9 |   100.00 | NULL                                                |
|  2 | DERIVED     | g          | NULL       | range | idx_payfeeinfo_uuid,ISSUEDATE | ISSUEDATE | 6       | NULL | 399486 |     0.00 | Using index condition; Using where; Using temporary |
+----+-------------+------------+------------+-------+-------------------------------+-----------+---------+------+--------+----------+-----------------------------------------------------+


select count(1) from PRPJPAYFEEINFO g where g.PLATENO = '湘A8D900';
+----------+
| count(1) |
+----------+
|        8 |
+----------+

--相关的表只有8条数据,PLATENO 是车牌号,应该走这个字段上的索引。
--但是 该字段上没有索引,所以需要在该字段上创建索引。
--优化方案:
use underwriting;
create index idx_plateno on PRPJPAYFEEINFO using(plateno);

5.总结

虽然有时候SQL访问的表可以走索引，但是还要看走哪个索引最优。例如上面的SQL,走车牌号这个索引才是最优的，而走时间的索引，访问量较大，不是最优的。

MySql基础知识总结（SQL优化篇）
学Java，找哪吒
07-12 4万+
1、建表语句 + 联合索引 CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp .
MySQL慢查询日志释疑总结
weixin_34029949的博客
11-15 185
  之前写了一篇“MySQL慢查询日志总结“，总结了一些MySQL慢查询日志常用的相关知识，这里总结一下在工作当中遇到关于MySQL慢查询日志的相关细节问题，有些是释疑或自己有疑惑，自己测试验证、解惑方面的知识。此篇只是总结个人的一些经验，不足之处，敬请指正！       1： 为什么在慢查询日志里面出现Query_time小于long_query_time阀值的SQL语句呢？     例如，如下...
MySQL中的慢查询
weixin_37841366的博客
10-22 488
实际开发中，最直接性能指标就是SQL执行速度 慢查询 顾名思义，就是执行很慢的查询 如果定义慢?超过long_query_time参数设定的时间阈值（默认10s），就被认定是慢查询的， 是需要优化的。 慢查询日志 慢查询日志默认是不开启的 如果需要优化SQL语句，可以开启这个功能，很容易知道哪些语句是需要优化的 Query_time:实际执行的时间，Lock_time:如果加了锁的话，等到锁所消耗的时间，Row_sent：拿到了多少条数据， Rows_examined:为了得到想要的结果操作
mysql 慢查询日志的操作
rainrun的博客
10-16 764
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。 慢查询相关
Mysql 慢查询日志
小楼一夜听春雨，深巷明朝卖杏花
03-28 433
MySQL 慢查询日志 MySQL 有一种日志，叫做慢查询日志，主要就是用来记录一些耗时的查询操 作。通过这个日志我们就可以分析出哪些的操作是影响性能的，我们需要对其 进行一些优化措施。查看开启状态 一、慢查询日志相关参数 1、slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。 2、log-slow-queries ：旧版（5.6以下版本）MySQ...
使用工具分析慢查询日志
Data & Analysis
06-24 3108
Query_time、Rows_examined、Rows_sent这3个信息让我们看到了查询需要优化什么。查询时间最长的SQL往往是最需要优化的，如果检查了大量记录（Rows_examined），而只返回很小的结果集（Rows_sent），往往也意味着存在不良SQL。但在一个高并发的数据库服务上，或者在做压力测试时，如果发现慢查询日志增长得非常快，很难筛选和查找里面的信息，那么在这种情况下，有如...
慢sql查询,慢sql优化,常用的Oracle函数--日常更新中
qq_45591389的博客
05-25 1771
dual简单来说是一个空表，是oracle提供的最小的工作表，只有一行一列，具有某些特殊功用，所有用户都可以使用dual名称访问。
SQL性能优化神器：explain 执行计划
犬小哈
07-16 174
???? 欢迎加入小哈的星球，你将获得:专属的项目实战 /1v1 提问/Java 学习路线 /学习打卡/每月赠书/社群讨论 新项目:《从零手撸：仿小红书（微服务架构）》正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...,点击查看项目介绍；《从零手撸：前后端分离博客项目（全栈开发）》2期已完结,演示链接：htt...
做 SQL 性能优化真是让人干瞪眼
默默不代表沉默
11-08 7104
SQL性能优化，逐步分析，强大的SPL玩转SQL性能
基于金仓数据库KingbaseES的SQL优化
Kingbase_的博客
03-01 1186
一、前言 分组查询是常见的SQL查询语句。SQL分组功能主要通过GROUP BY关键字来实现，并且GROUP BY通常需要配合聚合函数来使用。比如说，分组之后可以计数（COUNT），求和（SUM），求平均数（AVG）等。 本文探讨的不是GROUP BY关键字的学习和使用，而是一种另类的“分组”查询。 有这样一个功能需求：系统中存在资讯信息模块，用于发布一些和业务相关的活动动态，其中每条资讯信息都有一个所属类型（如科技类的资讯、娱乐类、军事类•••）和浏览量字段。官网上需要滚动展示一些热门资讯信息列表（浏览量
mysql 分组查询慢_记一次MySQL分组查询慢优化过程
weixin_39559486的博客
01-19 1067
一：问题时间范围查询所有数据的同数据存在超过4次的数据，检索查询时已经没有速度了，直接不响应。优化方向：①给md5_code、nuclear_time字段加索引。②给sql语句后面加order by null。③调整where条件里字段的查询顺序，有索引的放前面。④给所有where条件的字段加组合索引。⑤用子查询的方式，先查where条件里的内容，再去重。 SQL分析后的结果是：可能用到索引：nu...
MySQL之SQL语句性能差异分析
qq_40892728的博客
10-28 375
在 MySQL 中，有很多看上去逻辑相同，但性能却差异巨大的 SQL 语句。对这些语句使用不当的话，就会不经意间导致整个数据库的压力变大。 案例一：条件字段函数操作 假设你现在维护了一个交易系统，其中交易记录表 tradelog 包含交易流水号（tradeid）、交易员 id（operator）、交易时间（t_modified）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下： mysql> CREATE TABLE `tradelog` ( `id` int(11) NOT NU
SQL查询优化技巧与案例分析
SQL查询优化的重要性 ## 1.1 SQL查询优化的定义 SQL查询优化是指对SQL查询语句在执行过程中的性能进行提升的一系列技巧和方法。它的目标是减少查询的执行时间和资源消耗，提高数据库系统的响应速度和处理能力。 #...
如何产看SQL 查询的执行时间
技术广场
11-05 374
对于查看毫秒级执行时间，推荐使用 SET STATISTICS TIME或 GETDATE()计时方法。这两种方法可以直接在 SQL Server Management Studio 中快速应用。
如何在Linux中使用Cron定时执行SQL任务
最新发布
Mr_Wanter
11-08 278
linux crontab 定时执行sql脚本。
Flink SQL
qq_41081716的博客
11-05 266
以上步骤展示了如何使用 Flink SQL 从 Kafka 读取数据、进行聚合计算，并将结果分别写入 MySQL 和 HDFS。这是一个基本的流程，根据实际需求，可以调整表结构、连接器配置以及 SQL 查询以适应不同的应用场景。
PostgreSQL (八) 创建分区
fracong的博客
11-04 323
分区的新建和查询
【SQL50】day 1
m0_73629042的博客
11-05 320
使用不等于符号无法筛选null情况。
sql之count()函数解析
JustinMars的博客
11-07 201
COUNT(*)：统计所有行。 COUNT(column_name)：统计某列的非 NULL 值。 COUNT(DISTINCT column_name)：统计某列中不重复的非 NULL 值。
提升MySQL性能：慢查询日志与SQL优化实战指南
慢查询日志记录了SQL语句、执行时间、查询计划等信息，便于分析性能瓶颈。 3. **定期审计**：定期检查数据库活动，手动审查长时间运行的SQL，找出潜在的问题。 接下来，文章详细讲解了**慢查询日志**的使用，包括...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值