[MySQL优化案例]系列 -- 用TIMESTAMP类型取代INT和DATETIME 【里面包含存储过程】

Thu, 06 Nov 2008 09:34:33 +0000

1. 准备

创建一个测试表：

mysql> CREATE TABLE `t` (
`d1` int(10) unsigned NOT NULL default '0',
`d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
`d3` datetime NOT NULL,
KEY `d2` (`d2`),
KEY `d1` (`d1`),
KEY `d3` (`d3`)
);

然后创建一个存储过程填充数据：

mysql> DELIMITER //
CREATE PROCEDURE INS_T()
BEGIN
SET @i=1;
WHILE 0<1
DO
SET @i=@i+1;
INSERT INTO t VALUES (1199116800+@i, FROM_UNIXTIME(1199116800+@i), FROM_UNIXTIME(1199116800+@i));
END WHILE;
END;//
DELIMITER ;

run stroe process:

call INS_T();

时间戳 1199116800 表示 2008-01-01 这个时间点。然后运行存储过程，大概填充几十万条记录后，中止执行，因为上面的存储过程是个死循环，所以需要人工中止（ctrl+c）。
删除存储过程：

DROP PROCEDURE test.INS_T;

查看存储过程：

SHOW CREATE PROCEDURE test.INS_T;

来看看到底有多少条记录了，以及索引情况：

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|   924707 |
+----------+
mysql> analyze table t;
+--------+---------+----------+-----------------------------+
| Table  | Op      | Msg_type | Msg_text                    |
+--------+---------+----------+-----------------------------+
| test.t | analyze | status   | Table is already up to date |
+--------+---------+----------+-----------------------------+
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t     |          1 | d2       |            1 | d2          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
| t     |          1 | d1       |            1 | d1          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
| t     |          1 | d3       |            1 | d3          | A         |      924707 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2. 对比

2.1 只检索一条记录

mysql> explain select * from t where d1 = 1199579155;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d1            | d1   | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d2 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d2            | d2   | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
mysql> explain select * from t where d3 = '2008-01-06 08:25:55';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t     | ref  | d3            | d3   | 8       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

2.2 范围检索（我发现当大于时候根本没有用到索引，小于用到了）

mysql> explain select * from t where d1 <= 1199894400;
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d1            | d1   | 4       | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d2 <= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d2            | d2   | 4       | NULL | 121961 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
mysql> explain select * from t where d3 <= from_unixtime(1199894400);
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | range | d3            | d3   | 8       | NULL | 120625 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+

小贴士：
explain select * from t where d2 <= from_unixtime(1199894400); key_len :为4
explain select * from t where d3 <= from_unixtime(1199894400); key_len :为8
效率体现出来了吧？呵呵！

话外音：当大于时候根本没有用到索引，如下:(和下面in和=关于索引的情况类似，但这个> 和< 的情况我还是不太清楚,呵呵)

mysql>  explain select * from t where d1 >= 1199894400;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t     | ALL  | d1            | NULL | NULL    | NULL | 9871898 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

mysql> explain select * from t where d2 >= from_unixtime(1199894400);
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t     | ALL  | d2            | NULL | NULL    | NULL | 9871898 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

mysql> explain select * from t where d3 >= from_unixtime(1199894400);
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t     | ALL  | d3            | NULL | NULL    | NULL | 9871898 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

小结：从上面的2次对比中可以看到，对 d1 或 d2 字段检索时的索引长度都是 4，因为 TIMESTAMP 实际上是 4字节的 INT 值。因此，实际应用中，基本上完全可以采用 TIMESTAMP 来代替另外2种类型了，并且 TIMESTAMP 还能支持自动更新成当前最新时间，何乐而不为呢？

in 和 = 在索引上的区别，建表：

CREATE TABLE `mytest` (
  `id` int(11) NOT NULL auto_increment,
  `uid` int(11) NOT NULL,
  `rstatus` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

EXPLAIN SELECT *
FROM `mytest`
FORCE INDEX ( uid )
WHERE uid =1
AND rstatus =1

发现用上了uid的索引，但这个in语句：

explain SELECT * FROM `mytest` WHERE uid IN ( 1,2,7) and rstatus =1

没有用上uid索引。

为此：认为要建立联合索引？是的，不光是要建立联合索引，而且和顺序有关，如建立：
alter table mytest add index tt(uid,rstatus);
也没有用，这样后运行：

explain SELECT * FROM `mytest` WHERE uid IN ( 1,2,7) and rstatus =1

也没有用到我们想要的tt索引，但是，我们将联合索引换个顺序：

alter table mytest add index tt(rstatus，uid);

执行：

explain SELECT * FROM `mytest` WHERE uid IN ( 1,2,7) and rstatus =1

，就用到了我们的联合索引了!
如下说明：
1.in 和 = 在索引机制上的不同！
2.in 的mysql索引和顺序有关！
最后：我去掉联合索引，加入了强制用uid的索引：
但：

SELECT * FROM `mytest` force index(uid) WHERE uid IN ( 1,2,7) and rstatus =1

也就ok了！

来源：
http://imysql.cn/2008_07_17_timestamp_vs_int_and_datetime
修正了存储过程的代码！

附录：


描述

CREATE PROCEDURE


建立一个存放在MySQL数据库的表格的存储过程。

CREATE FUNCTION


建立一个用户自定义的函数，尤其是返回数据的存储过程。

ALTER PROCEDURE


更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。.

ALTER FUNCTION


更改用CREATE FUNCTION 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。.

DROP PROCEDURE


从MySQL的表格中删除一个或多个存储过程。

DROP FUNCTION


从MySQL的表格中删除一个或多个存储函数。

SHOW CREATE PROCEDURE


返回使用CREATE PROCEDURE 建立的预先指定的存储过程的文本。这一声明是SQL:2003规范的一个MySQL扩展。

SHOW CREATE FUNCTION


返回使用CREATE  FUNCTION建立的预先指定的存储过程的文本。这一声明是SQL:2003规范的一个MySQL扩展。

SHOW PROCEDURE STATUS


返回一个预先指定的存储过程的特性，包括名称、类型、建立者、建立日期、以及更改日期。这一声明是SQL:2003规范的一个MySQL扩展。

SHOW FUNCTION STATUS


返回一个预先指定的存储函数的特性，包括名称、类型、建立者、建立日期、以及更改日期。这一声明是SQL:2003规范的一个MySQL扩展。

CALL


调用一个使用CREATE PROCEDURE建立的预先指定的存储过程。

BEGIN ... END


包含一组执行的多声明。

DECLARE


用于指定当地变量、环境、处理器，以及指针。

SET


用于更改当地和全局服务器变量的值。

SELECT ... INTO


用于存储显示变量的纵列。

OPEN


用于打开一个指针。

FETCH


使用特定指针来获得下一列。

CLOSE


用于关闭和打开指针。

IF


一个An if-then-else-end if 声明。

CASE ... WHEN


一个 case声明的结构

LOOP


一个简单的循环结构；可以使用LEAVE 语句来退出。

LEAVE


用于退出IF，CASE，LOOP，REPEAT以及WHILE 语句。

ITERATE


用于重新开始循环。

REPEAT


在结束时测试的循环。

WHILE


在开始时测试的循环。

RETURNS


返回一个存储过程的值。

[评论] [MySQL优化案例]系列 -- 用TIMESTAMP类型取代INT和DATETIME 【里面包含存储过程】

Thu, 01 Jan 1970 00:00:00 +0000

向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除

[MySQL优化案例]系列 -- 用TIMESTAMP类型取代INT和DATETIME 【里面包含存储过程】

[评论] [MySQL优化案例]系列 -- 用TIMESTAMP类型取代INT和DATETIME 【里面包含存储过程】

向东博客专注WEB应用构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除