107.SELECT语句带有日期的优化

戒掉贪嗔痴(薛双奇)

于 2024-01-06 23:46:06 发布

阅读量401

点赞数 10

分类专栏：数据库运维-MySQL 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/135434115

版权

数据库运维-MySQL 专栏收录该内容

125 篇文章 7 订阅

订阅专栏

1.SELECT语句优化

(1)场景描述

如下SQL返回一行数据,需要36s;
# Query_time: 36.046755  Lock_time: 0.000131 Rows_sent: 1  Rows_examined: 13157416
select count(*) from `underwriting`.`prptmain` where date_format(UPDATETIME,'%Y%m%d') = CURDATE()-INTERVAL 1 DAY;

（2）查看表的结构

show create table `underwriting`.`prptmain`;
CREATE TABLE `prptmain` (
  `CONTRACTNO` varchar(40) NOT NULL COMMENT '合同编号',
  `UUID` varchar(40) NOT NULL COMMENT '系统单据识别标记',
  `PROPOSALNO` varchar(40) DEFAULT NULL COMMENT '投保单档合同号',
  `POLICYNO` varchar(40) DEFAULT NULL COMMENT '保单档合同号（批单档用到)',
  `CURRENCY` varchar(3) DEFAULT NULL COMMENT '币别',
  `CLASSCODE` varchar(3) DEFAULT NULL COMMENT '险类代码',
  `RISKCODE` varchar(4) DEFAULT NULL COMMENT '险种代码',
  `RISKNAME` varchar(80) DEFAULT NULL COMMENT '险种名称',
  `CLAUSETYPE` varchar(6) DEFAULT NULL COMMENT '条款代码',
  `ACCEPTDATE` datetime DEFAULT NULL COMMENT '承保确认时间',
  `VALIDDATE` datetime DEFAULT NULL COMMENT '生效时间',
  `EXPIRYDATE` datetime DEFAULT NULL COMMENT '失效时间',
  `RELATEDNO` varchar(30) DEFAULT NULL COMMENT '关联单号',
  `RENEWALFLAG` varchar(2) DEFAULT NULL COMMENT '续保标志(-1:转保/0:新保/1:续保)',
  `LASTYEARPOLICYNO` varchar(50) DEFAULT NULL COMMENT '上年保单号',
  `TOTALSTANDARDPREMIUM` decimal(14,2) DEFAULT NULL COMMENT '总标准保费',
  `TOTALRISKPREMIUM` decimal(14,2) DEFAULT NULL COMMENT '总风险保费',
  `TOTALPREMIUM` decimal(14,2) DEFAULT NULL COMMENT '总签单保费',
  `NOTAXPREMIUM` decimal(14,2) DEFAULT NULL COMMENT '总签单保费（不含税）',
  `VAT` decimal(14,2) DEFAULT NULL COMMENT '增值税金额',
  `LOWESTDISCOUNT` decimal(8,4) DEFAULT NULL COMMENT '最低折扣（规则引擎整单参考优惠）',
  `SALEDISCOUNT` decimal(8,4) DEFAULT NULL COMMENT '销售折扣（用户填写的折扣，默认最低折扣）',
  `TOTALADJUSTRATE` decimal(8,4) DEFAULT NULL COMMENT '总浮动系数',
  `VEHCRATEWITHANTSCORE` decimal(8,4) DEFAULT NULL COMMENT '不计算车险分最低折扣系数',
  `COMMISSIONRATE` decimal(8,4) DEFAULT NULL COMMENT '手续费比例',
  `COMMISSIONAMOUNT` decimal(14,2) DEFAULT NULL COMMENT '手续费金额（含税）',
  `COMMISSIONNOTAXAMOUNT` decimal(14,2) DEFAULT NULL COMMENT '手续费金额（不含税）',
  `COMMISSIONVAT` decimal(14,2) DEFAULT NULL COMMENT '手续费税金',
  `EXPENSERATE` decimal(8,4) DEFAULT NULL COMMENT '展业费比例',
  `EXPENSEAMOUNT` decimal(14,2) DEFAULT NULL COMMENT '展业费金额（含税）',
  `EXPENSENOTAXAMOUNT` decimal(14,2) DEFAULT NULL COMMENT '展业费金额（不含税）',
  `EXPENSEVAT` decimal(8,4) DEFAULT NULL COMMENT '展业费税金',
  `PERFORMANCERATE` decimal(10,0) DEFAULT NULL,
  `SPECIALCONTRACTFLAG` varchar(2) DEFAULT NULL COMMENT '特殊业务标志',
  `SPECIALCONTRACTSUMMARY` varchar(60) DEFAULT NULL COMMENT '特殊业务说明',
  `REINSNO` varchar(60) DEFAULT NULL COMMENT '临分注记/临分号码',
  `REINSTYPE` varchar(1) DEFAULT NULL COMMENT '再保分保状况',
  `TOTALLOSSFLAG` varchar(1) DEFAULT NULL COMMENT '全损标志 0代表没有全损 1代表车损全损 2代表盗抢全损 ps：不可能出险盗抢和车损全损同时出现的情况',
  `POLICYPRINTTYPE` varchar(2) DEFAULT NULL COMMENT '保单打印类型 1-电子保单，2-监制保单',
  `POLICYPRINTNO` varchar(22) DEFAULT NULL COMMENT '保单打印流水号',
  `APPOINTAREACODE` varchar(12) DEFAULT NULL COMMENT '指定地区查询代码',
  `QUERYSEQUENCENO` varchar(50) DEFAULT NULL COMMENT '平台查询码',
  `CONFIRMSEQUENCENO` varchar(30) DEFAULT NULL COMMENT '平台确认码',
  `AGQUERYCODE` varchar(30) DEFAULT NULL COMMENT '中介校验查询码',
  `BUSINESSSTATUS` varchar(2) DEFAULT NULL COMMENT '业务状态',
  `ADDITIONALCOSTRATE` decimal(8,4) DEFAULT NULL COMMENT '附加费用率',
  `SUMAMOUNT` decimal(14,2) DEFAULT NULL COMMENT '总保额',
  `SUMSUBPREM` decimal(14,2) DEFAULT NULL COMMENT '总附加险保费',
  `SUMDISCOUNT` decimal(14,2) DEFAULT NULL COMMENT '总折扣金额',
  `PROPOSALPRINTTYPE` varchar(1) DEFAULT NULL COMMENT '投保单打印类型 1-电子投保单，2-监制投保单',
  `SUMTAX` decimal(14,2) DEFAULT NULL COMMENT '车船税',
  `VALIDATONCE` varchar(1) DEFAULT NULL COMMENT '即时生效',
  `QUERYSEQEXPIREDATE` datetime DEFAULT NULL COMMENT '平台投保查询码有效期',
  `OLDRISKCODE` varchar(4) DEFAULT NULL COMMENT '老险种代码',
  `HOPEDISCOUNT` decimal(8,4) DEFAULT NULL COMMENT '期望折扣',
  `UWDISCOUNT` decimal(8,4) DEFAULT NULL COMMENT '核保折扣',
  `DISCOUNT` decimal(8,4) DEFAULT NULL COMMENT '整单折扣率',
  `RECOMMENDISCOUNT` decimal(8,4) DEFAULT NULL COMMENT '建议折扣',
  `RULEID` varchar(12) DEFAULT NULL COMMENT '手续费规则id',
  `CREATETIME` datetime DEFAULT NULL COMMENT '创建时间',
  `UPDATETIME` datetime DEFAULT NULL COMMENT '修改时间',
  `REMARK` varchar(1000) DEFAULT NULL COMMENT '备注',
  `HIGHESTDISCOUNT` decimal(8,4) DEFAULT NULL COMMENT '最高折扣',
  `REINSSTATUS` varchar(1) DEFAULT NULL COMMENT '再保状态：1--未提交再保；2--已提交再保;3--再保审核通过；4--再保审核不通过',
  `UWYEARDAYS` int(3) DEFAULT NULL COMMENT '承保年度天数(提车险用)',
  `TRUCKUBISCORE` decimal(8,2) DEFAULT NULL COMMENT '车慧达评分',
  `TRUCKUBITRANCHE` varchar(8) DEFAULT NULL COMMENT '车慧达分档',
  `BURIEDCODE` varchar(40) DEFAULT NULL COMMENT '埋码(慧营销系统出单)',
  `RESPECIALIND` varchar(5) DEFAULT NULL COMMENT '高价车标识',
  PRIMARY KEY (`CONTRACTNO`) USING BTREE,
  KEY `idx_tmain_uuid` (`UUID`) USING BTREE,
  KEY `idx_tmain_proposalNo` (`PROPOSALNO`) USING BTREE,
  KEY `idx_tmain_validDate` (`VALIDDATE`) USING BTREE,
  KEY `idx_tmain_expiryDate` (`EXPIRYDATE`) USING BTREE,
  KEY `idx_tmain_policyNo` (`POLICYNO`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='投保单合同主表';

（3）查看执行计划可以知道,该SQL未使用索引,全表扫描。

explain select count(*) from `underwriting`.`prptmain` where date_format(UPDATETIME,'%Y%m%d') = CURDATE()-INTERVAL 1 DAY;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | prptmain | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8969966 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（4）检查数据分布

--检查非空数据
root@localhost [(none)]>select count(1)  from `underwriting`.`prptmain`where UPDATETIME is not null; 
+----------+
| count(1) |
+----------+
| 10487603 |
+----------+
1 row in set (30.56 sec)

--检查该字段为空的数据。
select count(1)  from `underwriting`.`prptmain`where UPDATETIME is null; 
root@localhost [(none)]>select count(1)  from `underwriting`.`prptmain`where UPDATETIME is null; 
+----------+
| count(1) |
+----------+
|  2771253 |
+----------+
1 row in set (6.28 sec)

--监控空值占比
select 2771253/(10487603+2771253);
+----------------------------+
| 2771253/(10487603+2771253) |
+----------------------------+
|                     0.2090 |
+----------------------------+
1 row in set (0.00 sec)

可以看出,空的数据比例20%,80%的数据非空，且非空间的数据，基本都是时间戳不重复。
因此可以在 UPDATETIME 上创建索引,提示该SQL的性能。

（5）SQL等价改写

select count(*) from `underwriting`.`prptmain` where date_format(UPDATETIME,'%Y%m%d') = CURDATE()-INTERVAL 1 DAY;
更改为:

select count(*) from `underwriting`.`prptmain` where date_format(UPDATETIME,'%Y%m%d') = CURDATE()-INTERVAL 1 DAY
union all
select count(*) from `underwriting`.`prptmain` where UPDATETIME between CURDATE()-INTERVAL 1 DAY and CURDATE();
+----------+
| count(*) |
+----------+
|    25712 |
|    25712 |
+----------+
2 rows in set (15.98 sec)

--SQL可以等价改写为:
select count(*) from `underwriting`.`prptmain` where UPDATETIME between CURDATE()-INTERVAL 1 DAY and CURDATE();

（6）查看SQL执行次数

--查看SQL执行次数,发现只执行一次,且未在缓存中保留SQL;
root@localhost [(none)]>select left(digest,9) as 'sql标识',digest_text as 'sql概况',schema_name '库名',concat(TRUNCATE(SUM_TIMER_WAIT/(select sum(SUM_TIMER_WAIT) 
    -> from performance_schema.events_statements_summary_by_digest)*100,2),'%') as  '时间占比', truncate(avg_timer_wait/1000000000,0) as '平均时间(ms)',count_star '执行次数',
    -> truncate(SUM_ROWS_EXAMINED/count_star,0) '平均扫描行数' ,truncate(SUM_ROWS_SENT/count_star,0) '平均返回行数',SUM_NO_INDEX_USED as '全表扫次数',
    -> sum_created_tmp_tables+sum_created_tmp_disk_tables as '临时表次数' 
    -> from performance_schema.events_statements_summary_by_digest 
    ->  where digest_text like 'select count(*) from `underwriting`.`prptmain` where date_format%' ;
Empty set (0.06 sec)

由于执行次数过少,且SQL文本未在缓冲区缓存,通过查看慢日志发现,虽然该执行次数少,但是每天都会执行，
且该表上其他SQL基于 UPDATETIME 字段的查询都是使用全表扫描,执行效率低,故对其进行优化。
优化方法为创建索引并更改SQL语句:

（7）优化

create index `idx_updatetime` on `underwriting`.`prptmain`   (updatetime) ;
root@localhost [(none)]>select count(*) from `underwriting`.`prptmain` where UPDATETIME between CURDATE()-INTERVAL 1 DAY and CURDATE();
+----------+
| count(*) |
+----------+
|    25710 |
+----------+
1 row in set (0.00 sec)


--如下SQL雷同:建索引后需要重新修改SQL才可以提升查询的效率。
# Query_time: 48.675085  Lock_time: 0.000201 Rows_sent: 17256  Rows_examined: 13157416
SET timestamp=1704139091;
select `CONTRACTNO`,`UUID`,`PROPOSALNO`,`POLICYNO`,`CURRENCY`,`CLASSCODE`,`RISKCODE`,`RISKNAME`,
`CLAUSETYPE`,`ACCEPTDATE`,`VALIDDATE`,`EXPIRYDATE`,`RELATEDNO`,`RENEWALFLAG`,`LASTYEARPOLICYNO`,
`TOTALSTANDARDPREMIUM`,`TOTALRISKPREMIUM`,`TOTALPREMIUM`,`NOTAXPREMIUM`,`VAT`,`LOWESTDISCOUNT`,
`SALEDISCOUNT`,`TOTALADJUSTRATE`,`VEHCRATEWITHANTSCORE`,`COMMISSIONRATE`,`COMMISSIONAMOUNT`,
`COMMISSIONNOTAXAMOUNT`,`COMMISSIONVAT`,`EXPENSERATE`,`EXPENSEAMOUNT`,`EXPENSENOTAXAMOUNT`,
`EXPENSEVAT`,`PERFORMANCERATE`,`SPECIALCONTRACTFLAG`,`SPECIALCONTRACTSUMMARY`,`REINSNO`,`REINSTYPE`,
`TOTALLOSSFLAG`,`POLICYPRINTTYPE`,`POLICYPRINTNO`,`APPOINTAREACODE`,`QUERYSEQUENCENO`,
`CONFIRMSEQUENCENO`,`AGQUERYCODE`,`BUSINESSSTATUS`,`ADDITIONALCOSTRATE`,`SUMAMOUNT`,`SUMSUBPREM`,
`SUMDISCOUNT`,`PROPOSALPRINTTYPE`,`SUMTAX`,`VALIDATONCE`,`QUERYSEQEXPIREDATE`,`OLDRISKCODE`,
`HOPEDISCOUNT`,`UWDISCOUNT`,`DISCOUNT`,`RECOMMENDISCOUNT`,`RULEID`,`CREATETIME`,`UPDATETIME`,
`REMARK`,`HIGHESTDISCOUNT`,`REINSSTATUS`,`UWYEARDAYS`,`TRUCKUBISCORE`,`TRUCKUBITRANCHE`,
`BURIEDCODE`,`RESPECIALIND` from `underwriting`.`prptmain` 
where date_format(UPDATETIME,'%Y%m%d')= CURDATE()-INTERVAL 1 DAY;

优化后等价改写为:
select `CONTRACTNO`,`UUID`,`PROPOSALNO`,`POLICYNO`,`CURRENCY`,`CLASSCODE`,`RISKCODE`,`RISKNAME`,
`CLAUSETYPE`,`ACCEPTDATE`,`VALIDDATE`,`EXPIRYDATE`,`RELATEDNO`,`RENEWALFLAG`,`LASTYEARPOLICYNO`,
`TOTALSTANDARDPREMIUM`,`TOTALRISKPREMIUM`,`TOTALPREMIUM`,`NOTAXPREMIUM`,`VAT`,`LOWESTDISCOUNT`,
`SALEDISCOUNT`,`TOTALADJUSTRATE`,`VEHCRATEWITHANTSCORE`,`COMMISSIONRATE`,`COMMISSIONAMOUNT`,
`COMMISSIONNOTAXAMOUNT`,`COMMISSIONVAT`,`EXPENSERATE`,`EXPENSEAMOUNT`,`EXPENSENOTAXAMOUNT`,
`EXPENSEVAT`,`PERFORMANCERATE`,`SPECIALCONTRACTFLAG`,`SPECIALCONTRACTSUMMARY`,`REINSNO`,`REINSTYPE`,
`TOTALLOSSFLAG`,`POLICYPRINTTYPE`,`POLICYPRINTNO`,`APPOINTAREACODE`,`QUERYSEQUENCENO`,
`CONFIRMSEQUENCENO`,`AGQUERYCODE`,`BUSINESSSTATUS`,`ADDITIONALCOSTRATE`,`SUMAMOUNT`,`SUMSUBPREM`,
`SUMDISCOUNT`,`PROPOSALPRINTTYPE`,`SUMTAX`,`VALIDATONCE`,`QUERYSEQEXPIREDATE`,`OLDRISKCODE`,
`HOPEDISCOUNT`,`UWDISCOUNT`,`DISCOUNT`,`RECOMMENDISCOUNT`,`RULEID`,`CREATETIME`,`UPDATETIME`,
`REMARK`,`HIGHESTDISCOUNT`,`REINSSTATUS`,`UWYEARDAYS`,`TRUCKUBISCORE`,`TRUCKUBITRANCHE`,
`BURIEDCODE`,`RESPECIALIND` from `underwriting`.`prptmain` 
where UPDATETIME between CURDATE()-INTERVAL 1 DAY and CURDATE();


+------------------------+------------------------+------------------------+------------------------+----------+-----------+----------+------------------------------------------------+------------+---------------------+---------------------+---------------------+-----------+-------------+------------------------+----------------------+------------------+--------------+--------------+---------+----------------+--------------+-----------------+----------------------+----------------+------------------+-----------------------+---------------+-------------+---------------+--------------------+------------+-----------------+---------------------+------------------------+---------+-----------+---------------+-----------------+----------------+-----------------+--------------------------------+--------------------------------+-------------+----------------+--------------------+-------------+------------+-------------+-------------------+----------+-------------+---------------------+-------------+--------------+------------+----------+------------------+------------+---------------------+---------------------+--------+-----------------+-------------+------------+---------------+-----------------+--------------------------------------+--------------+
25705 rows in set (0.31 sec)

优化前:48.67 sec
优化后:0.31 sec


查看执行计划:
root@localhost [(none)]>explain select `CONTRACTNO`,`UUID`,`PROPOSALNO`,`POLICYNO`,`CURRENCY`,`CLASSCODE`,`RISKCODE`,`RISKNAME`,
    -> `CLAUSETYPE`,`ACCEPTDATE`,`VALIDDATE`,`EXPIRYDATE`,`RELATEDNO`,`RENEWALFLAG`,`LASTYEARPOLICYNO`,
    -> `TOTALSTANDARDPREMIUM`,`TOTALRISKPREMIUM`,`TOTALPREMIUM`,`NOTAXPREMIUM`,`VAT`,`LOWESTDISCOUNT`,
    -> `SALEDISCOUNT`,`TOTALADJUSTRATE`,`VEHCRATEWITHANTSCORE`,`COMMISSIONRATE`,`COMMISSIONAMOUNT`,
    -> `COMMISSIONNOTAXAMOUNT`,`COMMISSIONVAT`,`EXPENSERATE`,`EXPENSEAMOUNT`,`EXPENSENOTAXAMOUNT`,
    -> `EXPENSEVAT`,`PERFORMANCERATE`,`SPECIALCONTRACTFLAG`,`SPECIALCONTRACTSUMMARY`,`REINSNO`,`REINSTYPE`,
    -> `TOTALLOSSFLAG`,`POLICYPRINTTYPE`,`POLICYPRINTNO`,`APPOINTAREACODE`,`QUERYSEQUENCENO`,
    -> `CONFIRMSEQUENCENO`,`AGQUERYCODE`,`BUSINESSSTATUS`,`ADDITIONALCOSTRATE`,`SUMAMOUNT`,`SUMSUBPREM`,
    -> `SUMDISCOUNT`,`PROPOSALPRINTTYPE`,`SUMTAX`,`VALIDATONCE`,`QUERYSEQEXPIREDATE`,`OLDRISKCODE`,
    -> `HOPEDISCOUNT`,`UWDISCOUNT`,`DISCOUNT`,`RECOMMENDISCOUNT`,`RULEID`,`CREATETIME`,`UPDATETIME`,
    -> `REMARK`,`HIGHESTDISCOUNT`,`REINSSTATUS`,`UWYEARDAYS`,`TRUCKUBISCORE`,`TRUCKUBITRANCHE`,
    -> `BURIEDCODE`,`RESPECIALIND` from `underwriting`.`prptmain` 
    -> where UPDATETIME between CURDATE()-INTERVAL 1 DAY and CURDATE();
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key            | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | prptmain | NULL       | range | idx_updatetime | idx_updatetime | 6       | NULL | 48718 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

由全表扫描,变成全表扫描,因此快。

2.总结

在带有日期时间戳的字段上执行范围查询，最好给该时间字段建立索引。否则容易全表扫描。

戒掉贪嗔痴(薛双奇)

关注

10
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
107.SELECT语句带有日期的优化

在带有日期时间戳的字段上执行范围查询，最好给该时间字段建立索引。（3）查看执行计划可以知道,该SQL未使用索引,全表扫描。1.SELECT语句优化。（6）查看SQL执行次数。（5）SQL等价改写。
复制链接

扫一扫