时间存储用int和datetime哪个字段更合适,建立下面的两个表
测试环境是内存2个G,一核的虚拟机
CREATE TABLE `test_inttime` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `int_time` int(11) NOT NULL DEFAULT '0' COMMENT '插入时间', PRIMARY KEY (`id`), KEY `int_time` (`int_time`) USING BTREE COMMENT 'int索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `test_datetime` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `date_time` datetime DEFAULT NULL COMMENT '插入时间', PRIMARY KEY (`id`), KEY `date_time` (`date_time`) USING BTREE COMMENT 'date_time' ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
分别对两个表进行两千万数据进行测试,这里小弟采用swoole多进程进行处理
这是插入test_inttime
<?php require 'pdo.php'; echo 'begin ' . date('Y-m-d H:i:s', time()); /** * 开启10个进程 * 每个进程插入数据库的表二百万条数据 */ for ($i = 0; $i < 10; $i++) { $process = new swoole_process('insertData', true); $start = $process->start(); $process->write($i); $workers[$start] = $process; } //防止产生僵尸进程. while (true) { $res = swoole_process::wait(); if ($res) { $pid = $res['pid']; write_logs('exit ' . $pid); } else { break; } } /** * 插入数据的主函数 * @param swoole_process $worker 每个进程 * 每个进程分二十次塞入数据库, * 对插入的数值进行拼接成大的字符串,进行插库操作, * 需要对数据库的进行配置文件的修改 max_allowed_packet */ function insertData(swoole_process $worker) { $num = $worker->read(); $pdo = MyPDO::getInstance('127.0.0.1', 'root', 'root', 'test', 'utf-8'); for ($j = 0; $j < 20; $j++) { $sql = "insert into test_inttime(int_time) VALUES "; for ($i = 0; $i < 100000; $i++) { $testTime = (time() - rand(1, 100)); $sql .= "($testTime),"; } $sql = substr($sql, 0, strlen($sql) - 1); $pdo->query($sql); } } /** * 日志错误记录 * @param $word string 传入错误信息拼接的字符串 */ function write_logs($word) { $time = date('Y-m-d H:i:s', time()); $date = date('Y-m-d', time()); $filename = './' . $date . '.log'; $fh = fopen($filename, "a+"); fwrite($fh, $time . " " . $word . " "); fclose($fh); } echo PHP_EOL; echo 'end ' . date('Y-m-d H:i:s', time());
这是插入test_datetime表
<?php require 'pdo.php'; echo 'begin ' . date('Y-m-d H:i:s', time()); /** * 开启10个进程 * 每个进程插入数据库的表二百万条数据 */ for ($i = 0; $i < 10; $i++) { $process = new swoole_process('insertData', true); $start = $process->start(); $process->write($i); $workers[$start] = $process; } //防止产生僵尸进程. while (true) { $res = swoole_process::wait(); if ($res) { $pid = $res['pid']; write_logs('exit ' . $pid); } else { break; } } echo PHP_EOL; echo 'end ' . date('Y-m-d H:i:s', time()); /** * 插入数据的主函数 * @param swoole_process $worker 每个进程 * 每个进程分二十次塞入数据库, * 对插入的数值进行拼接成大的字符串,进行插库操作, * 需要对数据库的进行配置文件的修改 max_allowed_packet */ function insertData(swoole_process $worker) {$num = $worker->read(); $pdo = MyPDO::getInstance('127.0.0.1', 'root', 'root', 'test', 'utf-8'); for ($j = 0; $j < 20; $j++) { $sql = "insert into test_datetime(date_time) VALUES "; for ($i = 0; $i < 100000; $i++) {
$testTime = time() - rand(1, 100);
$testTime = date('Y-m-d H:i:s', $testTime); $sql .= "('" . $testTime . "'),"; } $sql = substr($sql, 0, strlen($sql) - 1); $pdo->query($sql); } } /** * 日志错误记录 * @param $word string 传入错误信息拼接的字符串 */ function write_logs($word) { $time = date('Y-m-d H:i:s', time()); $date = date('Y-m-d', time()); $filename = './' . $date . '.log'; $fh = fopen($filename, "a+"); fwrite($fh, $time . " " . $word . " "); fclose($fh); }
分别运行上面两个方法
第一个test_inttime执行完用时如下,用时141s
成功插入两千万条数据
第二个test_datetime用时如下,用时176s
成功插入两千万条数据
进行存储空间的测试
第一个test_inttime
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB -> from tables where -> table_schema='test' -> and table_name = 'test_inttime'; +----------------+-----------------+ | data_length_MB | index_length_MB | +----------------+-----------------+ | 624.00MB | 264.89MB | +----------------+-----------------+
第二个test_datetime
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB -> from tables where -> table_schema='test' -> and table_name = 'test_datetime'; +----------------+-----------------+ | data_length_MB | index_length_MB | +----------------+-----------------+ | 744.00MB | 410.00MB | +----------------+-----------------+
进行查询测试
每隔20000进行1000次查询,取平均值和最大值和最小值
int_time
<?php require 'pdo.php'; $pdo = MyPDO::getInstance('127.0.0.1', 'root', '', 'test', 'utf-8'); for ($i = 0; $i < 1000; $i++) { $offset = $i * (20000); $start_time = time(); $res = $pdo->query("SELECT * from test_inttime ORDER BY int_time desc limit " . $offset . " ,10 "); if (!$res) { write_logs('int_time ' . $i . ' fail ', 'error_int_time'); continue; } $end_time = time(); $offset_time = $end_time - $start_time; write_logs($offset_time, 'int_time'); } /** * 日志记录 * @param $log_name string 传入的log名称,默认为date日期 * @param $word string 传入错误信息拼接的字符串 */ function write_logs($word, $log_name = '') { $time = date('Y-m-d H:i:s', time()); $date = date('Y-m-d', time()); if (!$log_name) { $log_name = $date; } $filename = './' . $log_name . '.log'; $fh = fopen($filename, "a+"); fwrite($fh, $time . " " . $word . " "); fclose($fh); }
date_time
<?php require 'pdo.php'; $pdo = MyPDO::getInstance('127.0.0.1', 'root', '', 'test', 'utf-8'); for ($i = 0; $i < 1000; $i++) { $offset = $i * (20000); $start_time = time(); $res = $pdo->query("SELECT * from test_datetime ORDER BY date_time desc limit " . $offset . " ,10 "); if (!$res) { write_logs('date_time ' . $i . ' fail ', 'error_date_time'); continue; } $end_time = time(); $offset_time = $end_time - $start_time; write_logs($offset_time, 'date_time'); } /** * 日志记录 * @param $log_name string 传入的log名称,默认为date日期 * @param $word string 传入错误信息拼接的字符串 */ function write_logs($word, $log_name = '') { $time = date('Y-m-d H:i:s', time()); $date = date('Y-m-d', time()); if (!$log_name) { $log_name = $date; } $filename = './' . $log_name . '.log'; $fh = fopen($filename, "a+"); fwrite($fh, $time . " " . $word . " "); fclose($fh); }
用时datetime大于inttime
经过比较,得到以下结果
| int_time | date_time | |
| 插入时间 | 141s | 176s |
| 存储空间 |
624.00MB(数据) +264.89MB(索引) |
744.00MB(数据)+410.00MB(索引) |
| 查询时间(1000次)(单位:最小值/最大值/平均值) | 0s /16s / 6.2315s | 0s /17s/ 6.5251s |
int_time 好于 date_time