文档章节

Mysql5.7在上亿级别的存储性能测试报告 Mysql到底可不可以支持单表过亿?要分区么?分表?

letwang
 letwang
发布于 2016/06/19 17:38
字数 1605
阅读 1740
收藏 16

软硬件环境

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

 

© 著作权归作者所有

共有 人打赏支持
letwang
粉丝 148
博文 63
码字总数 75344
作品 1
南京
技术主管
PostgreSQL 并行vacuum patch - 暨为什么需要并行vacuum或分区表

标签 PostgreSQL , vacuum , freeze , 分区表 , 并行vacuum 背景 我们之前做过一个这样的测试,单表数据从1000万到10亿,对其进行增删改查的压测,性能几乎没有衰减。 数据量 写入吞吐 查询t...

德哥
04/18
0
0
Greenplum 计算能力估算 - 暨多大表需要分区,单个分区多大适宜

标签 PostgreSQL , Greenplum , 分区 , 实践 背景 在数据仓库业务中,单表的数据量通常是非常巨大的。Greenplum在这方面做了很多的优化 1、支持列存储 2、支持向量计算 3、支持分区表 4、支持...

德哥
04/18
0
0
mysql —— 分表分区

面对当今大数据存储,设想当mysql中一个表的总记录超过1000W,会出现性能的大幅度下降吗? 答案是肯定的,一个表的总记录超过1000W,在操作系统层面检索也是效率非常低的 解决方案: 目前针对...

zyt_1978
2016/07/06
19
0
Mysql分表和分区的区别、分库和分表区别

分表和分区的区别: 一,什么是mysql分表,分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看:mysql分表的3种方法。 什么是分区,分区呢就是把一张表的数据分成N多个...

sailikung
05/01
0
0
摆脱垂直&水平拆分的窘境,这一招管用!

作者介绍 张秀云,网名飞鸿无痕,现任职于腾讯,负责腾讯金融数据库的运维和优化工作。2007年开始从事运维方面的工作,经历过网络管理员、Linux运维工程师、DBA、分布式存储运维等多个IT职位...

张秀云
2017/06/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

转:XMLHttpRequest2 新技巧

”XMLHttpRequest 的异步调用网上找的例子运行没问题,但稍微改了一点点就报错”InvalidStateError: XMLHttpRequest has an invalid context“。断断续续 搞了3天终于通了,可以接收二进制文...

SamXIAO
15分钟前
0
0
=====D服务器定时任务=====

Linux定时任务 crontab linux系统是有cron这个系统服务来控制的,Liunx系统上包含很多的计划性工作,使用者自己可以设置计划任务,所以linux系统提供了使用者控制计划任务的命令 crontab的启...

覃光林
24分钟前
0
0
xilinx资源

本系列教学视频由赛灵思高级战略应用工程师带领你:从零开始,一步步深入 掌握 HLS 以及 UltraFAST 设计方法,帮助您成为系统设计和算法加速的大拿! http://www.eetrend.com/topics/2018-0...

whoisliang
35分钟前
2
0
企业级开源四层负载均衡解决方案--LVS

网盘链接 企业级开源四层负载均衡解决方案--LVS 本课程将在Linux环境下,学习配置使用LVS,对Web集群和MySQL集群进行负载均衡,并结合利用Keepalived实现负载均衡器的高可用,实现对后端Rea...

qq__2304636824
44分钟前
3
0
Windows上安装Spacemacs

emacs安装 下载地址emacs 安装比较简单,解压后执行\bin\addpm.exe即可 emacs配置 emacs的默认配置文件路径和.emacs.d文件夹都是在Windows主目录下的 C:\Users\Administrator\AppData\Roami...

yxmsw2007
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部