mysql中索引与FROM_UNIXTIME的问题

零、背景

这周四收到很多告警，找DBA看了看，发现有个慢查询。

简单收集一些信息后，发现这个慢查询问题隐藏的很深，问了好多人包括DBA都不知道原因。

一、问题

有一个DB, 有一个字段, 定义如下.

MySQL [d_union_stat]> desc t_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-00 00:00:00 |
+----------------+--------------+------+-----+---------------------+
17 rows in set (0.01 sec)

索引如下:

MySQL [d_union_stat]> show index from t_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
6 rows in set (0.02 sec)

然后我写了一个SQL如下:

SELECT
  count(*)
FROM
  d_union_stat.t_local_cache_log_meta
where
  `c_mtime` < FROM_UNIXTIME(1494485402);

终于有一天DBA过来了, 扔给我一个流水，说这个SQL是慢SQL。

# Time: 170518 11:31:14
# Query_time: 12.312329 Lock_time: 0.000061 Rows_sent: 0 Rows_examined: 5809647
SET timestamp=1495078274;
DELETE FROM `t_local_cache_log_meta` WHERE `c_mtime`< FROM_UNIXTIME(1494473461) limit 1000;

我顿时无语了，我的DB都是加了索引，SQL都是精心优化了的，怎么是慢SQL呢？

问为什么是慢SQL，DBA答不上来， 问了周围的同事也都答不上来。

我心里暗想遇到一个隐藏很深的知识点了。

令人怀疑的地方有两个：1.有6个索引。 2. 右值是 FROM_UNIXTIME 函数。

于是查询MYSQL官方文档，发现6个不是问题。

All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes.  
Most storage engines have higher limits.

于是怀疑问题是 FROM_UNIXTIME 函数了。

然后看看MYSQL的INDEX小节，找到一点蛛丝马迹。

1.To find the rows matching a WHERE clause quickly.
2. To eliminate rows from consideration.
 If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
3.If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
4. MySQL can use indexes on columns more efficiently if they are declared as the same type and size.
 Comparison of dissimilar columns (comparing a string column to a temporal or numeric column, for example) may prevent use of indexes if values cannot be compared directly without conversion.

看到第4条的时候，提到不同类型可能导致不走索引，难道 FROM_UNIXTIME 的返回值不能转化为字符串类型？

于是查询 FROM_UNIXTIME 函数的返回值。

MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.

返回的是一个时间类型，那强制转化为字符串类型呢？

MySQL [d_union_stat]> explain SELECT
  ->   *
  -> 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: Using where
1 row in set (0.01 sec)

这次可以看到, 使用了索引，只扫描了一个数据。

二、结论

这次对 FROM_UNIXTIME 的返回值强制转化一下就可以利用上索引了。

所以这个SQL不能利用上索引是右值与左值的类型不一致导致的。 。

好了，不多说了， 这篇文章算是一个插曲，后面继续介绍算法吧。

赞 (0)

相关推荐

  • mysql 添加索引 mysql 如何创建索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • FROM_UNIXTIME 格式化MYSQL时间戳函数

    函数:FROM_UNIXTIME作用:将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示.语法:FROM_UNIXTIME(unix_timestamp,format) 返回表示 Unix 时间标记的一个字符串,根据format字符串格式化.format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符. 根据format字符串格式化date值.下列修饰符可以被用在format字符串中: %M 月名字(January--December)%W 星期

  • MySQL查看、创建和删除索引的方法

    本文实例讲述了MySQL查看.创建和删除索引的方法.分享给大家供大家参考.具体如下: 1．索引作用 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率.特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍. 例如,有3个未索引的表t1.t2.t3,分别只包含列c1.c2.c3,每个表分别含有1000行数据组成,指为1-1000的数值,查找对应值相等行的查询如下所示. SELECT c1,c2,c3 FROM t1,t2,t3

  • mysql From_unixtime及UNIX_TIMESTAMP及DATE_FORMAT日期函数

    from_unixtime()是MySQL里的时间函数 date为需要处理的参数(该参数是Unix 时间戳),可以是字段名,也可以直接是Unix 时间戳字符串 后面的 '%Y%m%d' 主要是将返回值格式化 例如: mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d' ) ->20071120 mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y年%m月%d' ) ->2007年11月20 UNIX_TI

  • MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

  • MySQL 创建索引(Create Index)的方法和语法结构及例子

    CREATE INDEX Syntax CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type] index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH | RTREE} 复制代码 代码如下: -- 创建无索引的表格 create t

  • MySQL 主键与索引的联系与区别分析

    关系数据库依赖于主键,它是数据库物理模式的基石.主键在物理层面上只有两个用途: 惟一地标识一行. 作为一个可以被外键有效引用的对象. 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针.下面是主键和索引的一些区别与联系. 1. 主键一定是唯一性索引,唯一性索引并不一定就是主键. 所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引.因为主键可以唯一标识某一行记录,所以可以确保执行数据更新.删除的

  • 基于mysql全文索引的深入理解

    基于mysql全文索引的深入理解

    前言:本文简单讲述全文索引的应用实例,MYSQL演示版本5.5.24. Q:全文索引适用于什么场合? A:全文索引是目前实现大数据搜索的关键技术. 至于更详细的介绍请自行百度,本文不再阐述. -------------------------------------------------------------------------------- 一.如何设置? 如图点击结尾处的{全文搜索}即可设置全文索引,不同MYSQL版本名字可能不同. 二.设置条件 1.表的存储引擎是MyISAM,默认

  • mysql中索引与FROM_UNIXTIME的问题

    零.背景 这周四收到很多告警,找DBA看了看,发现有个慢查询. 简单收集一些信息后,发现这个慢查询问题隐藏的很深,问了好多人包括DBA都不知道原因. 一.问题 有一个DB, 有一个字段, 定义如下. MySQL [d_union_stat]> desc t_local_cache_log_meta; +----------------+--------------+------+-----+---------------------+ | Field | Type | Null | Key |

  • MySQL中索引与视图的用法与区别详解

    前言 本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 索引 一.概述 所有的Mysql列类型都可以被索引. mysql支持BTREE索引.HASH索引.前缀索引.全文本索引(FULLTEXT)[只有MyISAM引擎支持,且仅限于char,varchar,text列].空间列索引[只有MyISAM引擎支持,且索引的字段必须非空],但不支持函数索引. MyISAM和InnoDB存储引擎的表默认创建BTREE索引,

  • MySQL中索引失效的常见场景与规避方法

    前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时

  • Mysql中索引和约束的示例语句

    外键 查询一个表的主键是哪些表的外键 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'mydbname' AND REFERENCED_TABLE_NAME = '表名'; 导出所有外键语句 SELECT CONCAT('ALTER

  • MySQL中索引的定义以及操作新手教程

    目录 索引的定义 索引的类型 普通.唯一.主键和全文索引 普通索引(INDEX) 唯一索引(UNIQUE INDEX) 主键索引(PRIMARY KEY) 全文索引(FULLTEXT) 单列索引和组合索引 聚集索引和非聚集索引 索引的创建原则 索引操作 创建索引 查看索引 删除索引 总结 索引的定义 数据库中的索引就像一本书的目录,可以据此快速定位数据库中相关数据的所在位置. 在数据库中,索引被定义为一种特殊的数据结构,由数据库中的一列或多列组合而成,可以用来快速查询数据表中某一特定值的记录.

  • 深入了解MySQL中索引优化器的工作原理

    深入了解MySQL中索引优化器的工作原理

    目录 本文导读 一.MySQL 优化器是如何选择索引的 1.MySQL数据库组成 2.MySQL数据库成本计算 二.MySQL查询成本 三.SELECT 执行过程 总结 本文导读 本文将解读MySQL数据库查询优化器(CBO)的工作原理.简单介绍了MySQL Server的组成,MySQL优化器选择索引额原理以及SQL成本分析,最后通过 select 查询总结整个查询过程. 一.MySQL 优化器是如何选择索引的 下面我们来看这张表,SUB_ODR_ID字段创建了相关的 2 个索引,根据我们前面

  • MySQL中索引优化distinct语句及distinct的多字段操作

    MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB;

  • mysql中索引使用不当速度比没加索引还慢的测试

    下面是我们插入到这个tuangou表的数据: id web city type 1 拉手网 北京 餐饮美食 2 拉手网 上海 休闲娱乐 3 百分团 天津 餐饮美食 4 拉手网 深圳 网上购物 5 百分团 石家庄 优惠卷票 6 百分团 邯郸 美容保健 .. 4999 百分团 重庆 旅游酒店 5000 拉手网 西安 优惠卷票 执行mysql语句: $sql = "select from tuangou where web='拉手网' and city='上海'"; (1)如果没有加索引,执

  • 简单介绍MySQL中索引的使用方法

    数据库索引是一个数据结构,提高操作的速度,在一个表中可以使用一个或多个列,提供两个快速随机查找和高效的顺序访问记录的基础创建索引. 在创建索引时,它应该被认为是将SQL查询的那些列上创建一个或多个索引的列. 实际上,指数也保持主键或索引字段和指针的实际表中每条记录的表型. 用户无法看到索引,它们只是用来加快查询速度,将用于数据库搜索引擎找到的记录速度非常快. INSERT和UPDATE语句表上的索引需要更多的时间,成为快速对这些表的SELECT语句.究其原因是,当进行插入或更新,数据库以及需要惰

  • 分析MySQL中索引引引发的CPU负载飙升的问题

    收到一个mysql服务器负载告警,上去一看,load average都飙到280多了,用top一看,CPU跑到了336%,不过IO和内存的负载并不高,根据经验,应该又是一起索引引起的惨案了. 看下processlist以及slow query情况,发现有一个SQL经常出现,执行计划中的扫描记录数看着还可以,单次执行耗时为0.07s,还不算太大.乍一看,可能不是它引发的,但出现频率实在太高,而且执行计划看起来也不够完美: mysql> explain SELECT count(1) FROM a

随机推荐