sql varchar 日期范围_SQL 优化案例一则

本文通过一个实际的SQL优化案例,展示了如何将varchar类型的日期字段改为date类型,以及创建联合索引来提高查询效率。作者强调了正确的数据类型选择和优化索引对SQL性能的影响,并分享了优化后的执行计划对比。
摘要由CSDN通过智能技术生成

导读:

郑松华,知数堂SQL 优化班老师 

现任 CCmediaService DBA,主要负责数据库优化相关工作

擅长SQL优化 ,数据核对

想阅读更多内容请点击订阅专栏

68f2956902f7d92ec2d30ca49bcdd4e4.png


大家好,我是知数堂SQL 优化班老师 网名:骑龟的兔子

背景说明:

今天在刷头条的时候,看到下面的文章

https://www.toutiao.com/a6727944177943839243/

看到他的SQL还有优化的空间,经过他的同意写下这篇文章

3d175802072a8fe655cd01997404ba01.png

为了阅读方便,把需要的资料引用到这里。

表结构如下:

CREATE TABLE `statistic_order` ( `oid` bigint(20) NOT NULL, `o_source` varchar(25) DEFAULT NULL COMMENT '来源编号', `o_actno` varchar(30) DEFAULT NULL COMMENT '活动编号', `o_actname` varchar(100) DEFAULT NULL COMMENT '参与活动名称', `o_n_channel` int(2) DEFAULT NULL COMMENT '商城平台', `o_clue` varchar(25) DEFAULT NULL COMMENT '线索分类', `o_star_level` varchar(25) DEFAULT NULL COMMENT '订单星级', `o_saledep` varchar(30) DEFAULT NULL COMMENT '营销部', `o_style` varchar(30) DEFAULT NULL COMMENT '车型', `o_status` int(2) DEFAULT NULL COMMENT '订单状态', `syctime_day` varchar(15) DEFAULT NULL COMMENT '按天格式化日期', PRIMARY KEY (`oid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

原文作者优化后的SQL 版本如下:

select S.syctime_day, sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE', sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE', sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE', sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE', sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE' from statistic_order S where S.syctime_day > '2015-05-01' and S.syctime_day < '2016-08-01' GROUP BY S.syctime_day order by S.syctime_day asc;

执行计划如下图所示:

eae9cb42b8dc78891aae4097352552dc.png

原文中有如下语言:

在o_source和syctime_day上加上索引之后,效率提高了很多,大概五秒钟就查询出了结果:

原文所示执行计划 :

810b039939f31253d26f40047c7a98d6.png

思路:

从以上条件推测,应该是 o_source 和syctime_day 分别加上单列索引

create index syctimeday_index on statistic_order(syctime_day); create index osource_index on statistic_order(o_source);

重现了类似执行计划:

root@mysql3306.sock>[test3]>desc select S.syctime_day,    ->  sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',    ->  sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',    ->  sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',    ->  sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',    ->  sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'    ->  from statistic_order S where S.syctime_day > '2015-05-01'   ->  and S.syctime_day < '2016-08-01'    ->  GROUP BY S.syctime_day order by S.syctime_day asc\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: S   partitions: NULL         type: rangepossible_keys: syctimeday_index,osource_index          key: syctimeday_index      key_len: 48          ref: NULL         rows: 1     filtered: 100.00        Extra: Using index condition1 row in set, 1 warning (0.00 sec)

优化过程:

下面开始对这个优化开始二次修改:

我们先看下原来的执行计划

eae9cb42b8dc78891aae4097352552dc.png

我们先看下执行计划,这里type index ,还有key_len 为48 

从表结构中有如下字段定义

  `syctime_day` varchar(15) DEFAULT NULL

我们定义表结构的时候,最好是,什么数据类型定义什么类型

这里如果是日期类型就应该选用date 类型

因为这样所占用空间就小

类型 (字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值

现在把数据类型更改为date 类型如下 :

CREATE TABLE `statistic_order2` (  `oid` bigint(20) NOT NULL,  `o_source` varchar(25) DEFAULT NULL,  `o_actno` varchar(30) DEFAULT NULL,  `o_actname` varchar(100) DEFAULT NULL,  `o_n_channel` int(2) DEFAULT NULL,  `o_clue` varchar(25) DEFAULT NULL,  `o_star_level` varchar(25) DEFAULT NULL,  `o_saledep` varchar(30) DEFAULT NULL,  `o_style` varchar(30) DEFAULT NULL,  `o_status` int(2) DEFAULT NULL,  `syctime_day` date DEFAULT NULL,  PRIMARY KEY (`oid`),  KEY `syctimeday_index` (`syctime_day`),  KEY `osource_index` (`syctime_day`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
root@mysql3306.sock>[test3]>desc select S.syctime_day,    ->  sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',    ->  sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',    ->  sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',    ->  sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',    ->  sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'    ->  from statistic_order2 S where S.syctime_day > '2015-05-01' and S.syctime_day < '2016-08-01'    ->  GROUP BY S.syctime_day order by S.syctime_day asc\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: S   partitions: NULL         type: rangepossible_keys: syctimeday_index,osource_index          key: syctimeday_index      key_len: 4          ref: NULL         rows: 1     filtered: 100.00        Extra: Using index condition1 row in set, 1 warning (0.00 sec)

就会发现 key_len 为 4  

还有一个从原来的执行计划的fiter为50可以看出范围太大,就会发生大量的回表操作,也是一个相对负担的操作,那为了优化创建联合索引如下:

create index ix_index on statistic_order2(syctime_day,o_source);

创建之后的执行计划如下:

 root@mysql3306.sock>[test3]>desc select S.syctime_day,    ->  sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',    ->  sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',    ->  sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',    ->  sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',    ->  sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'    ->  from statistic_order2 S where S.syctime_day+0 > '2015-05-01' and S.syctime_day+0 < '2016-08-01'    ->  GROUP BY S.syctime_day order by S.syctime_day asc\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: S   partitions: NULL         type: indexpossible_keys: syctimeday_index,osource_index,ix_index          key: ix_index      key_len: 82          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)

这样就达到了我所要的最终的优化效果!!

结论:

如果在不更改表结构的情况下,创建ix_index 这个索引,这样也可以达到优化效果。

因为本案例的整体的表的数据量不大,改变列的属性达到的效果差不多,但是随着数据量的增加,差距就会更加明显。

谢谢大家~ 欢迎转发

如有关于SQL优化方面疑问需要交流的,请加入QQ群(579036588),并@骑兔子的龟 就可与我联系

END


点击下图小程序订阅 《SQL优化专栏》 get更多优化技能

68f2956902f7d92ec2d30ca49bcdd4e4.png

98755c7174b88eeeaacd6fbbb8ae2136.png

知 数 堂

这里有好课,为职场助攻

免费兑换知数堂的课程和周边,戳此了解

现招聘课程销售助理数枚

不限地区,不限年龄

若您懂“数”又自带流量

欢迎投简历加入我们

扫码了解岗位详情

f5e71788b211ab792549a871c81b6cea.png

e4a6b50cdd7eb07af208c3dcd12dae3e.gif

扫码加入MySQL技术Q群

(群号:579036588)

   5e880e922587f01746bc257822d05f83.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值