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,走车牌号这个索引才是最优的，而走时间的索引，访问量较大，不是最优的。