mysql中索引与FROM_UNIXTIME的问题
零、背景
这周四收到很多告警,找DBA看了看,发现有个慢查询。
简单收集一些信息后,发现这个慢查询问题隐藏的很深,问了好多人包括DBA都不知道原因。
一、问题
有一个DB,有一个字段,定义如下.
MySQL[d_union_stat]>desct_local_cache_log_meta; +----------------+--------------+------+-----+---------------------+ |Field|Type|Null|Key|Default| +----------------+--------------+------+-----+---------------------+ |c_id|int(11)|NO|PRI|NULL| |c_key|varchar(128)|NO|MUL|| |c_time|int(11)|NO|MUL|0| |c_mtime|varchar(45)|NO|MUL|0000-00-0000:00:00| +----------------+--------------+------+-----+---------------------+ 17rowsinset(0.01sec)
索引如下:
MySQL[d_union_stat]>showindexfromt_local_cache_log_meta\G ***************************1.row*************************** Table:t_local_cache_log_meta Non_unique:0 Key_name:PRIMARY Column_name:c_id Collation:A Cardinality:6517096 Index_type:BTREE ***************************2.row*************************** . . . ***************************6.row*************************** Table:t_local_cache_log_meta Non_unique:1 Key_name:index_mtime Column_name:c_mtime Collation:A Cardinality:592463 Index_type:BTREE 6rowsinset(0.02sec)
然后我写了一个SQL如下:
SELECT count(*) FROM d_union_stat.t_local_cache_log_meta where `c_mtime`终于有一天DBA过来了,扔给我一个流水,说这个SQL是慢SQL。
#Time:17051811:31:14 #Query_time:12.312329Lock_time:0.000061Rows_sent:0Rows_examined:5809647 SETtimestamp=1495078274; DELETEFROM`t_local_cache_log_meta`WHERE`c_mtime`我顿时无语了,我的DB都是加了索引,SQL都是精心优化了的,怎么是慢SQL呢?
问为什么是慢SQL,DBA答不上来,问了周围的同事也都答不上来。
我心里暗想遇到一个隐藏很深的知识点了。
令人怀疑的地方有两个:1.有6个索引。2.右值是FROM_UNIXTIME函数。
于是查询MYSQL官方文档,发现6个不是问题。
Allstorageenginessupportatleast16indexespertableandatotalindexlengthofatleast256bytes.
Moststorageengineshavehigherlimits.于是怀疑问题是FROM_UNIXTIME函数了。
然后看看MYSQL的INDEX小节,找到一点蛛丝马迹。
1.TofindtherowsmatchingaWHEREclausequickly.
2.Toeliminaterowsfromconsideration.
Ifthereisachoicebetweenmultipleindexes,MySQLnormallyusestheindexthatfindsthesmallestnumberofrows.
3.Ifthetablehasamultiple-columnindex,anyleftmostprefixoftheindexcanbeusedbytheoptimizertolookuprows.
4.MySQLcanuseindexesoncolumnsmoreefficientlyiftheyaredeclaredasthesametypeandsize.
Comparisonofdissimilarcolumns(comparingastringcolumntoatemporalornumericcolumn,forexample)maypreventuseofindexesifvaluescannotbecompareddirectlywithoutconversion.看到第4条的时候,提到不同类型可能导致不走索引,难道FROM_UNIXTIME的返回值不能转化为字符串类型?
于是查询FROM_UNIXTIME函数的返回值。
MySQLFROM_UNIXTIME()returnsadate/datetimefromaversionofunix_timestamp.
返回的是一个时间类型,那强制转化为字符串类型呢?
MySQL[d_union_stat]>explainSELECT ->* ->FROM ->t_local_cache_log_meta ->where ->`c_mtime`=CONCAT(FROM_UNIXTIME(1494485402))\G ***************************1.row*************************** id:1 select_type:SIMPLE table:t_local_cache_log_meta type:ref possible_keys:index_mtime key:index_mtime key_len:137 ref:const rows:1 Extra:Usingwhere 1rowinset(0.01sec)这次可以看到,使用了索引,只扫描了一个数据。
二、结论
这次对FROM_UNIXTIME的返回值强制转化一下就可以利用上索引了。
所以这个SQL不能利用上索引是右值与左值的类型不一致导致的。。
好了,不多说了,这篇文章算是一个插曲,后面继续介绍算法吧。