Mysql5.7在上亿级别的存储性能测试报告 Mysql到底可不可以支持单表过亿?要分区么?分表?
博客专区 > let 的博客 > 博客详情
Mysql5.7在上亿级别的存储性能测试报告 Mysql到底可不可以支持单表过亿?要分区么?分表?
let 发表于2年前
Mysql5.7在上亿级别的存储性能测试报告 Mysql到底可不可以支持单表过亿?要分区么?分表?
  • 发表于 2年前
  • 阅读 1657
  • 收藏 14
  • 点赞 1
  • 评论 0

标题:腾讯云 新注册用户域名抢购1元起>>>   

摘要: 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事务占位符批量导入上亿大数据测试代码演示案例

<?php
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性能

https://yunpan.cn/cRIszrtnuuUjf  访问密码 91ff

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性能

https://yunpan.cn/cBsw5KibNW6Cz  访问密码 621f

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性能

https://yunpan.cn/cR476XS6dDf2S  访问密码 eaac

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

3.Mysql按照主键ID随机查询真的很快,存储10亿都没有问题!

如果首先已经通过某个方式获得了某些主键ID记录,最后通过Mysql直接 ID IN(...)岂不很快?

这时候选择Sphinx搜索引擎进行筛选查找操作,最终获得上述ID数据是个明智的选择,如果你安装了Mysql Sphinx 扩展,那么还可以把 Sphinx 表当作常规表与真实的Mysql表进行LEFT Join,利用 Sphinx 排查复杂业务逻辑,Mysql吐数据,妙哉!

4.建立字段时候谨慎的选择字段类型、表中存储哪些字段直接关系到你的系统负载!

设计数据库时我们一般会把常用、短类型字段放在主表(尽量打造成静态表),把常用、类型字段放在附表,最终2个或者多个表进行JOIN,附表中适当的维护使用冗余字段,也是不错的选择!

 

共有 人打赏支持
粉丝 137
博文 66
码字总数 75584
×
let
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: