mysql时间字段创建索引和不创建索引查询性能分析

亦凉 2023-09-24 10:02 142阅读 0赞

在数据库设计的时候，我们经常会需要设计时间字段，在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？飘易就这个问题，来一个实践出真知吧。

MYSQL版本号：5.5.19

建立表：

CREATE TABLE IF NOT EXISTS \`datetime\_test\` (

? \`id\` int(11) NOT NULL,

? \`d\_int\` int(11) NOT NULL DEFAULT '0',

? \`d\_timestamp\` timestamp NULL DEFAULT NULL,

? \`d\_datetime\` datetime DEFAULT NULL

) ENGINE=MyISAM AUTO\_INCREMENT=1000001 DEFAULT CHARSET=utf8;

ALTER TABLE \`datetime\_test\`

? MODIFY \`id\` int(11) NOT NULL AUTO\_INCREMENT,AUTO\_INCREMENT=1;

插入100万条测试数据：

set\_time\_limit(300); //最大执行时间这里设置300秒

//连接数据库

$pdo=new PDO("mysql:host=localhost;dbname=test","root","123");

for ($i=1; $i <=1000000; $i++) \{

$d\_int=$i;

$pdo->exec("insert into datetime\_test(d\_int,d\_timestamp,d\_datetime)

values($d\_int,FROM\_UNIXTIME($d\_int),FROM\_UNIXTIME($d\_int))");

取中间的20万条做查询测试：

SELECT FROM\_UNIXTIME(400000), FROM\_UNIXTIME(600000)

1970-01-05 23:06:40, 1970-01-08 06:40:00

第一种情况，MyISAM引擎， d\_int/d\_timestamp/d\_datetime这三个字段都没有索引

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_int >400000 AND d\_int<600000

查询花费 0.0780 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_int>UNIX\_TIMESTAMP('1970-01-05 23:06:40') AND d\_int

查询花费 0.0780 秒

效率不错。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_timestamp>'1970-01-05 23:06:40' AND d\_timestamp

查询花费 0.4368 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE UNIX\_TIMESTAMP(d\_timestamp)>400000 AND UNIX\_TIMESTAMP(d\_timestamp)<600000

查询花费 0.0780 秒

对于timestamp类型，使用UNIX\_TIMESTAMP内置函数查询效率很高，几乎和int相当；直接和日期比较效率低。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_datetime>'1970-01-05 23:06:40' AND d\_datetime

查询花费 0.1370 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE UNIX\_TIMESTAMP(d\_datetime)>400000 AND UNIX\_TIMESTAMP(d\_datetime)<600000

查询花费 0.7498 秒

对于datetime类型，使用UNIX\_TIMESTAMP内置函数查询效率很低，不建议；直接和日期比较，效率还行。

第二种情况，MyISAM引擎， d\_int/d\_timestamp/d\_datetime这三个字段都有索引

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_int >400000 AND d\_int<600000

查询花费 0.3900 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_int>UNIX\_TIMESTAMP('1970-01-05 23:06:40') AND d\_int

查询花费 0.3824 秒

对于int类型，有索引的效率反而低了，飘易的猜测是由于设计的表结构问题，多了索引，反倒多了一个索引查找。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_timestamp>'1970-01-05 23:06:40' AND d\_timestamp

查询花费 0.5696 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE UNIX\_TIMESTAMP(d\_timestamp)>400000 AND UNIX\_TIMESTAMP(d\_timestamp)<600000

查询花费 0.0780 秒

对于timestamp类型，有没有索引貌似区别不大。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE d\_datetime>'1970-01-05 23:06:40' AND d\_datetime

查询花费 0.4508 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test\` WHERE UNIX\_TIMESTAMP(d\_datetime)>400000 AND UNIX\_TIMESTAMP(d\_datetime)<600000

查询花费 0.7614 秒

对于datetime类型，有索引反而效率低了。

第三种情况，InnoDB引擎， d\_int/d\_timestamp/d\_datetime这三个字段都没有索引

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_int >400000 AND d\_int<600000

查询花费 0.3198 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_int>UNIX\_TIMESTAMP('1970-01-05 23:06:40') AND d\_int

查询花费 0.3092 秒

InnoDB引擎的查询效率明细比MyISAM引擎的低，低3倍+。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_timestamp>'1970-01-05 23:06:40' AND d\_timestamp

查询花费 0.7092 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE UNIX\_TIMESTAMP(d\_timestamp)>400000 AND UNIX\_TIMESTAMP(d\_timestamp)<600000

查询花费 0.3160 秒

对于timestamp类型，使用UNIX\_TIMESTAMP内置函数查询效率同样高出直接和日期比较。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_datetime>'1970-01-05 23:06:40' AND d\_datetime

查询花费 0.3834 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE UNIX\_TIMESTAMP(d\_datetime)>400000 AND UNIX\_TIMESTAMP(d\_datetime)<600000

查询花费 0.9794 秒

对于datetime类型，直接和日期比较，效率高于UNIX\_TIMESTAMP内置函数查询。

第四种情况，InnoDB引擎， d\_int/d\_timestamp/d\_datetime这三个字段都有索引

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_int >400000 AND d\_int<600000

查询花费 0.0522 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_int>UNIX\_TIMESTAMP('1970-01-05 23:06:40') AND d\_int

查询花费 0.0624 秒

InnoDB引擎有了索引之后，性能较MyISAM有大幅提高。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_timestamp>'1970-01-05 23:06:40' AND d\_timestamp

查询花费 0.1776 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE UNIX\_TIMESTAMP(d\_timestamp)>400000 AND UNIX\_TIMESTAMP(d\_timestamp)<600000

查询花费 0.2944 秒

对于timestamp类型，有了索引，反倒不建议使用MYSQL内置函数UNIX\_TIMESTAMP查询了。

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE d\_datetime>'1970-01-05 23:06:40' AND d\_datetime

查询花费 0.0820 秒

SELECT SQL\_NO\_CACHE count(id) FROM \`datetime\_test2\` WHERE UNIX\_TIMESTAMP(d\_datetime)>400000 AND UNIX\_TIMESTAMP(d\_datetime)<600000

查询花费 0.9994 秒

对于datetime类型，同样有了索引，反倒不建议使用MYSQL内置函数UNIX\_TIMESTAMP查询了。

【总结】：

对于MyISAM引擎，不建立索引的情况下(推荐)，效率从高到低：int > UNIX\_TIMESTAMP(timestamp) > datetime(直接和时间比较)>timestamp(直接和时间比较)>UNIX\_TIMESTAMP(datetime) 。

对于MyISAM引擎，建立索引的情况下，效率从高到低： UNIX\_TIMESTAMP(timestamp) > int > datetime(直接和时间比较)>timestamp(直接和时间比较)>UNIX\_TIMESTAMP(datetime) 。

对于InnoDB引擎，没有索引的情况下(不建议)，效率从高到低：int > UNIX\_TIMESTAMP(timestamp) > datetime(直接和时间比较) > timestamp(直接和时间比较)> UNIX\_TIMESTAMP(datetime)。

对于InnoDB引擎，建立索引的情况下，效率从高到低：int > datetime(直接和时间比较) > timestamp(直接和时间比较)> UNIX\_TIMESTAMP(timestamp) > UNIX\_TIMESTAMP(datetime)。

一句话，对于MyISAM引擎，采用 UNIX\_TIMESTAMP(timestamp) 比较；对于InnoDB引擎，建立索引，采用 int 或 datetime直接时间比较。

mysql时间字段创建索引和不创建索引查询性能分析

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引优化实践：如何创建、调整和删除索引，以提高查询性能？

相关 MySQL - 对字符串字段创建索引

相关 mysql创建索引和删除索引

相关 mysql创建索引，mysql索引优化，mysql索引创建删除

相关 mysql时间字段创建索引和不创建索引查询性能分析

相关 mysql 如何创建索引、修改索引、删除索引、查询索引

相关 MySQL创建索引和删除索引

相关 mysql 创建索引命令_mysql 索引-【创建索引命令】

相关 mysql 创建索引和删除索引

相关 MySQL创建索引、重建索引、查询索引、删除索引