SQL索引引起低级错误造成的生产问题回顾

最近我们部门业务扩张需要大批量招人，我也参与面试环节（在此打个广告，若想跳槽的 请关注 加V ），发现IT大佬都是"理论强，实践弱"。为什么这么讲，前几天是大版本发布日，连续遇到几个 SQL 索引引起的低级错误，也是开发人员轻视的一点。所以我在面试结束后会有这样的面试评语“无生产排查经验”，”无定位生产问题经验”~~

那为什么我会写这样的批语，分析下前几天的生产问题 。

开发人员都知道查询数据库若不带索引查询速度会很慢，要合理利用索引加快查询效率，那么怎么使用索引是对的呢？

举个例子以下sql是 mysql 数据库， 若其它数据库建表sql请对应做改造

CREATE TABLE `sale_day` (

`statis_day` varchar(10) DEFAULT NULL,

`store_code` varchar(10) DEFAULT NULL,

`sale_amount` varchar(10) DEFAULT NULL,

PRIMARY key (`statis_day`,`store_code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

如上表是两个字段组合主键，那么这么组合主键主要是注意哪些坑呢？

请回答出以下几个sql哪个 效率会差

#1

select sale_amount from sale_day where store_code='001';

#2

select sale_amount from sale_day where statis_day='20190429' and store_code='001';

#3

select sale_amount from sale_day where store_code='001' and statis_day='20190429';

如果不知道是哪个效率差，那么我们应该如何验证呢？

我使用的是Navicat for mysql 工具，右击有个Explain Selected 分析sql，可以看到执行计划。

把这几个sql都比对一下可以答案是否是与你想像的一样呢？

#2 和#3 都使用了primary 主建索引，而#1未走索引 type =ALL 属于全表扫描。那么像#1这种sql你是否写过 ？ 

很抱歉 ， 大家都会讲没写过，那么我会讲，我遇到了。当然不是我写的，我在发布时候发现大量的TimeoutException异常，我直接跟踪定位到sql发现sql中条件查询未使用到索引，当然会慢 。修改原来索引已经不现实，修改业务代码更不现实。无奈赶快找dba添加索引，只能通过添加索引解决问题～～～

第二种场景

我把上面的表做了扩展，因为一天可以生成多条数据，那么原来的主建要做变更，那么有的开发人员就动了脑子了，把主键这么定义了！！

CREATE TABLE `sale_day` (

`statis_day` varchar(10) DEFAULT NULL,

`static_date` date DEFAULT '0000-00-00',

`store_code` varchar(10) DEFAULT NULL,

`sale_amount` varchar(10) DEFAULT NULL,

PRIMARY key (`static_date`,`statis_day`,`store_code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

于是我们引发一个问题，

1.索引中添加date/timestamp类型是否走索引？

2.条件查询根据时间类型使用函数是否走索引？

3.通过条件查询时间类型转化后是否走索引？

以下sql，我在windows中和mac中两台机器 进行验证发现是与机器有关，但也不完全是！

select * from sale_day where static_date=FORMAT(now(),'yyyy-mm-dd')

mac执行结果

windows执行的结果

同样的表结构

CREATE TABLE `n_cmmdty_purchase_sale_price_d` (

`statis_date` date NOT NULL DEFAULT '0000-00-00',

`store_code` varchar(32) NOT NULL DEFAULT '' ,

PRIMARY KEY (`statis_date`,`store_code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

mac执行结果

windows执行结果

有这场景我们就要约定 时间类型的字段不可以作为索引字段 表示，若需要请添加冗余字段 转化成varchar类型 。

通过以上两种场景分析条件查询非常重要，一不小心就会犯了低级错误。

如果你不小心或不经义这样的粗心，你会让面试管批上这样的批语~~~~

以上是根据个人理解做了分析，如有不正确请留言讨论。

----------

再次感谢，欢迎关注微信公众号“零售云技术”，文章持续更新，或留言讨论