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，附表中适当的维护使用冗余字段，也是不错的选择！

数据库