- 数据库
- Mysql5.7在上亿级别的存储性能测试报告 Mysql到底可不可以支持单表过亿？要分区么？分表？
### 软硬件环境
Intel 酷睿i5 480M，2.66GHz（笔记本）
5400转硬盘
6G内存
Win10 64 位操作系统
PHP version: 7.0.6
Server version: 5.7.10 - MySQL Community Server (GPL)
### PDO事务占位符批量导入上亿大数据测试代码演示案例
function transaction(&$sql, &$paramArray){
static $connect = null;
if (!isset($connect)) {
$connect = new PDO("mysql:dbname=test;host=127.0.0.1;port=3306;charset=utf8", 'root', '123456');
}
$connect->beginTransaction();
$sth = $connect->prepare($sql);
foreach ($paramArray as $param) {
$sth->execute($param);
}
$connect->commit();
}
$t = microtime(true);
$paramArray = [];
$sql = 'INSERT INTO `b` VALUES (NULL,?,?,?,?,?,?,?,?,?)';
for ($i = 1; $i <= 1000000000; $i ++) {//10亿
$paramArray[] = [$i, $i, $i, $i, $i, $i, $i, $i, $i];
if ($i % 1000000 === 0) {//100万
transaction($sql, $paramArray);
$paramArray = [];
}
}
if ($paramArray) {
transaction($sql, $paramArray);
$paramArray = [];
}
var_dump((microtime(true)-$t),'ok');
### 3亿条理想字段类型记录下的InnoDB COUNT性能
CREATE TABLE `a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a1` int(10) unsigned NOT NULL,
`a2` int(10) unsigned NOT NULL,
`a3` int(10) unsigned NOT NULL,
`a4` int(10) unsigned NOT NULL,
`a5` int(10) unsigned NOT NULL,
`a6` int(10) unsigned NOT NULL,
`a7` int(10) unsigned NOT NULL,
`a8` int(10) unsigned NOT NULL,
`a9` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=300000001 DEFAULT CHARSET=utf8
mysql> select count(*) from a;
+-----------+
| count(*) |
+-----------+
| 300000000 |
+-----------+
1 row in set (5 min 54.45 sec)
Information
Table comments:
Space usage
Data 16.2 GiB
Index 0 B
Total 16.2 GiB
Row statistics
Format dynamic
Collation utf8_general_ci
Next autoindex 300,000,001
Creation Jun 03, 2016 at 09:26 PM
### 10亿条理想字段类型记录下的InnoDB COUNT性能
CREATE TABLE `b` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b1` int(10) unsigned NOT NULL,
`b2` int(10) unsigned NOT NULL,
`b3` int(10) unsigned NOT NULL,
`b4` int(10) unsigned NOT NULL,
`b5` int(10) unsigned NOT NULL,
`b6` int(10) unsigned NOT NULL,
`b7` int(10) unsigned NOT NULL,
`b8` int(10) unsigned NOT NULL,
`b9` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000001 DEFAULT CHARSET=utf8
mysql> select count(*) from b;
+------------+
| count(*) |
+------------+
| 1000000000 |
+------------+
1 row in set (22 min 42.38 sec)
Information
Table comments:
Space usage
Data 61.1 GiB
Index 0 B
Total 61.1 GiB
Row statistics
Format dynamic
Collation utf8_general_ci
Next autoindex 1,000,000,001
Creation Jun 19, 2016 at 10:43 PM
Last update Jun 19, 2016 at 10:25 PM
#### 10万条常规字段类型记录下的InnoDB COUNT性能
CREATE TABLE `c` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c9` tinyint(3) unsigned NOT NULL,
`c8` datetime NOT NULL,
`c7` datetime NOT NULL,
`c1` varchar(32) NOT NULL,
`c2` varchar(64) NOT NULL,
`c3` varchar(128) NOT NULL,
`c4` varchar(255) NOT NULL,
`c5` text NOT NULL,
`c6` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
mysql> select count(*) from c;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (1 min 24.18 sec)
Information
Table comments:
Space usage
Data 8.3 GiB
Index 0 B
Total 8.3 GiB
Row statistics
Format dynamic
Collation utf8_general_ci
Next autoindex 100,001
Creation Jun 08, 2016 at 09:13 PM
Last update Jun 19, 2016 at 08:10 PM
### 按照主键随机SELECT *
mysql> select * from a where id in (10,3431,56432,543523,65,43432,43546565,32234343,123456789);
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| id | a1 | a2 | a3 | a4 | a5 | a6 | a7 | a8 | a9 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| 65 | 65 | 65 | 65 | 65 | 65 | 65 | 65 | 65 | 65 |
| 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 |
| 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 |
| 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 |
| 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 |
| 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 |
| 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 |
| 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
9 rows in set (0.45 sec)
mysql> select * from b where id in (10,3431,56432,543523,65,43432,43546565,32234343,123456789);
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| id | b1 | b2 | b3 | b4 | b5 | b6 | b7 | b8 | b9 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| 65 | 65 | 65 | 65 | 65 | 65 | 65 | 65 | 65 | 65 |
| 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 | 3431 |
| 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 | 43432 |
| 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 | 56432 |
| 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 | 543523 |
| 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 | 32234343 |
| 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 | 43546565 |
| 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 | 123456789 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
9 rows in set (0.20 sec)
mysql> select id from c where id in (10,3431,56432,543523,65,43432,43546565,32234343,123456789);
+-------+
| id |
+-------+
| 10 |
| 65 |
| 3431 |
| 43432 |
| 56432 |
+-------+
5 rows in set (0.00 sec)
#### MAX函数性能
mysql> select max(id) from a;
+-----------+
| max(id) |
+-----------+
| 300000000 |
+-----------+
1 row in set (0.34 sec)
mysql> select max(id) from b;
+------------+
| max(id) |
+------------+
| 1000000000 |
+------------+
1 row in set (0.35 sec)
mysql> select max(id) from c;
+---------+
| max(id) |
+---------+
| 100000 |
+---------+
1 row in set (0.00 sec)
### 其他性能指标
mysql> ALTER TABLE `test` ENGINE = INNODB;
Query OK, 100000000 rows affected (6 hours 8 min 6.96 sec)
Records: 100000000 Duplicates: 0 Warnings: 0
ARCHIVE
Index 0B，Data 2 G
mysql> mysql> select max(id) from test;
+-----------+
| max(id) |
+-----------+
| 100000000 |
+-----------+
1 row in set (5 min 5.13 sec)
mysql> select id from test order by id desc limit 1;
+-----------+
| id |
+-----------+
| 100000000 |
+-----------+
1 row in set (5 min 2.89 sec)
InnoDB
Index 0B，Data 93.6 GiB
mysql> select max(id) from test;
+-----------+
| max(id) |
+-----------+
| 100000000 |
+-----------+
1 row in set (0.00 sec)
mysql> select id from test order by id desc limit 1;
+-----------+
| id |
+-----------+
| 100000000 |
+-----------+
1 row in set (0.00 sec)
### 启发
##### 1.上面一切的一切悲观测试结果，增加硬件配置，会带来立竿见影的性能提升！
也许有的同学会提出分库、分表、分区、拆字段、上缓存、上搜索引擎、上大数据分析....但是这些执行完毕后，项目代码也要配套更新，紧接着是测试、安全、并发等等问题亟待解决（目前团队稳定么？有这些真实力么？技术成本提高后紧接着带来的是招聘成本和维护成本）.....
一个高级工程师的月薪应该差不多够改善硬件了吧？不够？那么整个研发部门的月薪呢？项目稳定性带来的业务市场直接价值和潜在价值呢？
##### 2.当数据量很庞大的时候，尽量避免COUNT等操作！
一定要的话也可以选择计算粗略值
select TABLE_ROWS from information_schema.`TABLES` WHERE TABLE_NAME = '表名';
explain select count(*) from c where id > 0;
或者获取是否含有下一页、上一页
http://php.net/manual/zh/pdostatement.fetch.php
.Mysql按照主键ID随机查询真的很快，存储10亿都没有问题！
如果首先已经通过某个方式获得了某些主键ID记录，最后通过Mysql直接 ID IN（...）岂不很快？
这时候选择Sphinx搜索引擎进行筛选查找操作，最终获得上述ID数据是个明智的选择，如果你安装了Mysql Sphinx 扩展，那么还可以把 Sphinx 表当作常规表与真实的Mysql表进行LEFT Join，利用 Sphinx 排查复杂业务逻辑，Mysql吐数据，妙哉！
4.建立字段时候谨慎的选择字段类型、表中存储哪些字段直接关系到你的系统负载！
设计数据库时我们一般会把常用、短类型字段放在主表（尽量打造成静态表），把不常用、长类型字段放在附表，最终2个或者多个表进行JOIN，附表中适当的维护使用冗余字段，也是不错的选择！
