文档章节

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

letwang
 letwang
发布于 2016/06/19 17:38
字数 1605
阅读 1684
收藏 14
点赞 1
评论 0

软硬件环境

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
粉丝 140
博文 67
码字总数 76298
作品 0
南京
技术主管
Mysql5.7—mysql优化分区、分表(必备)

小生博客:http://xsboke.blog.51cto.com 小生 Q Q:1770058260 -------谢谢您的参考,如有疑问,欢迎交流 一、 分表 1. 分表简介 分表是将一个大表按照一定的规则分解成多张具有独立存储空间...

小生博客 ⋅ 2017/05/28 ⋅ 0

PostgreSQL 并行vacuum patch - 暨为什么需要并行vacuum或分区表

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

德哥 ⋅ 04/18 ⋅ 0

Greenplum 计算能力估算 - 暨多大表需要分区,单个分区多大适宜

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

德哥 ⋅ 04/18 ⋅ 0

mysql —— 分表分区

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

zyt_1978 ⋅ 2016/07/06 ⋅ 0

mysql分区技术

背景介绍 当 MySQL中一个表的总记录数超过了1000万后,会出现性能的大幅度下降吗?答案是肯定的,但是性能下降的比率不一而同,要看系统的架构、应用程序,甚至还要根据索引、服务器硬件等多...

五大三粗 ⋅ 2015/06/10 ⋅ 1

MySQL数据库如何解决大数据量存储问题

利用MySQL数据库如何解决大数据量存储问题? 各位高手您们好,我最近接手公司里一个比较棘手的问题,关于如何利用MySQL存储大数据量的问题,主要是数据库中的两张历史数据表,一张模拟量历史...

zyt_1978 ⋅ 2016/07/09 ⋅ 0

Mysql分表和分区的区别、分库和分表区别

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

sailikung ⋅ 05/01 ⋅ 0

摆脱垂直&水平拆分的窘境,这一招管用!

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

张秀云 ⋅ 2017/06/22 ⋅ 0

mysql的分区和分表

1,什么是mysql分表,分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看mysql分表的3种方法 什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同一...

stone_ ⋅ 2016/08/16 ⋅ 0

关于MySQL的知识点与面试常见问题都在这里

摘要: Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_Guide 书籍推荐 《高性能MySQL : 第3版》 文字教程推荐 MySQL ...

传授知识的天使 ⋅ 06/13 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

来自一个优秀Java工程师的简历

写在前面: 鉴于前几天的一份前端简历,虽然带着很多不看好的声音,但却帮助了很多正在求职路上的人,不管评论怎么说,我还是决定要贴出一份后端的简历。 XXX ID:357912485 目前正在找工作 ...

颖伙虫 ⋅ 24分钟前 ⋅ 0

Confluence 6 恢复一个站点有关使用站点导出为备份的说明

推荐使用生产备份策略。我们推荐你针对你的生产环境中使用的 Confluence 参考 Production Backup Strategy 页面中的内容进行备份和恢复(这个需要你备份你的数据库和 home 目录)。XML 导出备...

honeymose ⋅ 今天 ⋅ 0

JavaScript零基础入门——(九)JavaScript的函数

JavaScript零基础入门——(九)JavaScript的函数 欢迎回到我们的JavaScript零基础入门,上一节课我们了解了有关JS中数组的相关知识点,不知道大家有没有自己去敲一敲,消化一下?这一节课,...

JandenMa ⋅ 今天 ⋅ 0

火狐浏览器各版本下载及插件httprequest

各版本下载地址:http://ftp.mozilla.org/pub/mozilla.org//firefox/releases/ httprequest插件截至57版本可用

xiaoge2016 ⋅ 今天 ⋅ 0

Docker系列教程28-实战:使用Docker Compose运行ELK

原文:http://www.itmuch.com/docker/28-docker-compose-in-action-elk/,转载请说明出处。 ElasticSearch【存储】 Logtash【日志聚合器】 Kibana【界面】 答案: version: '2'services: ...

周立_ITMuch ⋅ 今天 ⋅ 0

使用快嘉sdkg极速搭建接口模拟系统

在具体项目研发过程中,一旦前后端双方约定好接口,前端和app同事就会希望后台同事可以尽快提供可供对接的接口方便调试,而对后台同事来说定好接口还仅是个开始、设计流程,实现业务逻辑,编...

fastjrun ⋅ 今天 ⋅ 0

PXE/KickStart 无人值守安装

导言 作为中小公司的运维,经常会遇到一些机械式的重复工作,例如:有时公司同时上线几十甚至上百台服务器,而且需要我们在短时间内完成系统安装。 常规的办法有什么? 光盘安装系统 ===> 一...

kangvcar ⋅ 昨天 ⋅ 0

使用Puppeteer撸一个爬虫

Puppeteer是什么 puppeteer是谷歌chrome团队官方开发的一个无界面(Headless)chrome工具。Chrome Headless将成为web应用自动化测试的行业标杆。所以我们很有必要来了解一下它。所谓的无头浏...

小草先森 ⋅ 昨天 ⋅ 0

Java Done Right

* 表示难度较大或理论性较强。 ** 表示难度更大或理论性更强。 【Java语言本身】 基础语法,面向对象,顺序编程,并发编程,网络编程,泛型,注解,lambda(Java8),module(Java9),var(...

风华神使 ⋅ 昨天 ⋅ 0

Linux系统日志

linux 系统日志 /var/log/messages /etc/logrotate.conf 日志切割配置文件 https://my.oschina.net/u/2000675/blog/908189 logrotate 使用详解 dmesg 命令 /var/log/dmesg 日志 last命令,调......

Linux学习笔记 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部