mysql中索引与FROM_UNIXTIME的问题,linux服务器下查看

mysql中索引与FROM_UNIXTIME的问题,mysqlfrom_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.

  1. 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.
  2. 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不能利用上索引是右值与左值的类型不一致导致的。 。

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

零、背景 这周四收到很多告警,找DBA看了看,发现有个慢查询。 简单收集一些信息后,发现...

MySQL之递归小问题,MySQL之递归问题

mysql本身不支持递归语法,但可通过自连接变相实现一些简单的递归

--递归小方法:临时表和普通表的不同方法
--这题使用的是2次临时表查询父节点的递归 

drop table if exists test;
create table test(
id varchar(100),
name varchar(20),
parentid varchar(100)
);
insert test select
'13ed38f1-3c24-dd81-492f-673686dff0f3', '大学教师', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select 
'1ce203ac-ee34-b902-6c10-c806f0f52876','小学教师', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select 
'37e2ea0a-1c31-3412-455a-5e60b8395f7d', '教师' ,      null                union all select 
'c877b7ea-4ed3-f472-9527-53e1618cb1dc', '高数老师', '13ed38f1-3c24-dd81-492f-673686dff0f3' union all select 
'ce50a471-2955-00fa-2fb7-198f6b45b1bd', '中学教师', '37e2ea0a-1c31-3412-455a-5e60b8395f7d';

delimiter $$

create procedure usp_ser(in idd varchar(100))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
drop table if exists tmp2;
CREATE TEMPORARY TABLE tmp1(id varchar(100),name varchar(20),parentid varchar(100),levv int);
CREATE TEMPORARY TABLE tmp2(pid varchar(100));
insert tmp2 select parentid from test where id=idd;
insert tmp1 select t.* , lev from test t join tmp2 a on t.id=a.pid;
    while exists(select 1 from tmp2 )
do
truncate tmp2;
set lev=lev 1;
insert tmp2 select t.id from test t join tmp1 a on t.id=a.parentid and a.levv=lev-1;
insert tmp1 select t.*,lev from test t join tmp2 a on t.id=a.pid;
end while ;
select id,name,parentid from tmp1;
end;
$$

delimiter ;

 call usp_ser('c877b7ea-4ed3-f472-9527-53e1618cb1dc');
 -------------------------------------- ---------- -------------------------------------- 
| id                  | name   | parentid               |
 -------------------------------------- ---------- -------------------------------------- 
| 13ed38f1-3c24-dd81-492f-673686dff0f3 | 大学教师 | 37e2ea0a-1c31-3412-455a-5e60b8395f7d |
| 37e2ea0a-1c31-3412-455a-5e60b8395f7d | 教师   | NULL                 |
 -------------------------------------- ---------- -------------------------------------- 

 call usp_ser('13ed38f1-3c24-dd81-492f-673686dff0f3');
 -------------------------------------- ------ ---------- 
| id                  | name | parentid |
 -------------------------------------- ------ ---------- 
| 37e2ea0a-1c31-3412-455a-5e60b8395f7d | 教师 | NULL   |
 -------------------------------------- ------ ---------- 

 call usp_ser('37e2ea0a-1c31-3412-455a-5e60b8395f7d');

Empty set (0.02 sec)

上面的方法因为由于MySQL中不允许在同一语句中对临时表多次引用,所以用2次临时表
下面给个一次性用普通表完成的 查询子节点的递归查询

核心代码

drop table if exists test;
create table test(
id INT,
parentid INT
);
insert test select
1, 0 UNION ALL SELECT 
2, 1 UNION ALL SELECT 
3, 1 UNION ALL SELECT 
4, 0 UNION ALL SELECT 
5, 2 UNION ALL SELECT 
6, 5 UNION ALL SELECT 
7, 3 ;
Go

delimiter $$

create procedure usp_ser(in idd varchar(100))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1(id INT,parentid INT ,levv INT,ppath VARCHAR(1000));

INSERT tmp1 SELECT *,lev,id FROM test WHERE parentid=idd;

 while row_count()>0
do

set lev=lev 1;
insert tmp1 select t.*,lev,concat(a.ppath,t.id) from test t join tmp1 a on t.parentid=a.id AND levv=LEV-1;

end while ;
SELECT * FROM tmp1;

end;
$$

delimiter ;

 call usp_ser(0);

/*
 ------ ---------- ------ ------- 
| id  | parentid | levv | ppath |
 ------ ---------- ------ ------- 
|  1 |    0 |  1 | 1   |
|  4 |    0 |  1 | 4   |
|  2 |    1 |  2 | 12  |
|  3 |    1 |  2 | 13  |
|  5 |    2 |  3 | 125  |
|  7 |    3 |  3 | 137  |
|  6 |    5 |  4 | 1256 |
 ------ ---------- ------ ------- */

mysql本身不支持递归语法,但可通过自连接变相实现一些简单的递归 --递归小方法:临时表和普通表的不...

linux服务器下查看mysql的安装信息,linuxmysql

查看mysql的安装信息:

#ps -ef | grep mysql

图片 1

usr/bin/mysql 是指:mysql的运行路径
var/lib/mysql 是指:mysql数据库文件的存放路径
usr/lib/mysql 是指:mysql的安装路径 

#whereis mysql

图片 2

图片 3

#mysqladmin -u root -p variables

root是你的数据库帐号
回车后会提示你输入密码,输入上边填写的帐号对应的密码
回车后出来一个大表,找到datadir这一行,后边的值就是数据库所在的路径了。 

图片 4

查看mysql的安装信息: #ps -ef | grep mysql usr/bin/mysql 是指:mysql的运行路径 var/lib/mysql 是指:mysql数...

本文由ca88手机版登录发布于亚洲城ca88手机版官网,转载请注明出处:mysql中索引与FROM_UNIXTIME的问题,linux服务器下查看

TAG标签: ca88手机版登录
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。