【MySQL】 MySQL亿级数据、主从架构，Sharding分片_mysql亿万级数据

数据库Mysql

---

本文着重介绍分布式微服务环境下MySQL的高可用部署

---

前面的文章是基础bg，这篇文章将介绍微服务下的MySQL，亿级数据填充，高可用集群，慢查询日志，SQL的高并发测试

java中性能优化捉妖就是考虑GC，优化循环、业务，减少内存的开销OOM（比如20万长度的List）减少从数据源提取的数据量（提取一个上亿数据的大表）、多线程转为线程池，优化GC

微服务下讲究的就是分离解耦，包括动静分离、前后端分离、主动分离（读写分离）

开始之前举一个高性能的例子

场景需要获取User表所有的用户数量， 读出全部数据再List.size肯定没有直接MySQL中进行Count快

这里既然提高了性能，简单介绍性能测试要求微服务的基本要求：

• 模拟生产环境，95%用户的响应时间是否小于N秒， 一般来说，打开APP，初始化不应该超过3s，页面跳转不应该超过1s，跳转效益也等待不应该超过0.5s，搜索数据时间不应该超过0.5s
• 高并发场景，访问一个接口是否调用了过多的接口，比如登录要请求N个接口，高并发下，响应可能很慢
• 用户操作是否有监控功能，是否可以监控微服务的性能和服务端硬件性能
• 高并发下的慢连接、慢读取、慢请求 测试,保证不会因为客户端性能影响服务器性能 【 比如弱网条件下，APP网络差，要和服务端建立websocket长连接，java服务器推送数据后，APP未接收到，java服务器因为OOM而崩溃】
• 长时间大量用户连续登录退出是否会引发OOM、缓存失效、缓存穿透

开发业务时，需要关注的点：

分页处理技术： 单击加载更多，是否返回重复数据…

数据显示是否完整： 关注最后一页的数据

页面展示排序的方式： 后台服务器负责排序，前台JS负责排序，注重注意到底哪里承担排序职责

页面跳转是否正常： 尤其携带cookie等是否正常跳转

异常情况处理： 是否给前台用户返回过多的堆栈信息

程序可逆性： 保证增加数据后可以删除数据，删除数据后可以回滚

日志分割： 日志是否有效，是否按照日期分割方便提取

日志可读性： 日志存储信息是否有效，方便排查

程序灾备处理： 数据库渗透，是否有备份可以迅速恢复

程序高可用处理： 高并发导致服务器崩溃，是否可以继续提供服务

断网弱网处理： 弱网时是否包含超时约定，或者拒绝服务的约定

数据处理： 数据量较大时是否可以压缩，限流削峰处理

脱敏机制： 密码等意思信息是否正常脱敏

数据及时性： web控制台修改数据，APP是否及时有效更新

I/O阻塞会闲置CPU造成浪费，多线程增加锁之后会造成锁等待（SQL偶尔执行慢）、创建销毁维护大量线程，线程切换都很耗时，数据量过大、慢请求控制…

数据库的种类繁多，Cfeng接触的比较多的类型为Key-value数据库Redis、文档数据库MongoDB和ElasticSearch， 关系型数据库MySQL

对于MySQL来说，常用的相关工具包括：

• 性能基准测试工具： sysbench、mysqlslap
• 应用程序Web压测： JMeter
• MySQL服务器CPU监控： Grafana + Prometheus
• 集群分库： MyCat
• 统计工具： percona-toolkit
• 慢SQL查询： mysqldumpslow
• 分布式事务： Seata
• 事务处理测试： hammerDB
• 快速备份和恢复： mysqlhotcopy
• 常规备份和恢复： mysqldump
• 二进制日志（binlog）的解析工具： Maxwell

数据库作为应用性能的一个关键点，在使用时需要进行完备的性能测试：

1. 初始化架构，设计数据库表和接口后，对于数据表的结构进行基准性能测试，得到结构基准信息

2. 数据库主从复制、MyCat集群优化后，需要进行压力测试，保证MySQL的单节点性能

3. 编码结束对可能执行的SQL进行计划解读，进行索引优化

4. 对数据库作业务存储量测试， 存储不同的数据量的SQL响应时间

5. 数据库疲劳测试，数据库是否会内存泄露

6. 灾备测试： 主从结构如果机器挂了，是否可以正常提供服务

7.安全测试： 防火墙、脱敏.....
1
2
3
4
5
6
7
8
9
10
11
12
13

MySQL填充亿级数据

要正确模拟线上环境，需要给数据库填充亿级数据，传统插入Insert插入数据过慢

使用Java等语言连接数据库操作MySQL，除了语言本身损耗，还包括语言和数据库连接的损耗；所以要给数据库增大数据量，不推荐使用语言连接的方式

除了第三方工具，这里给出3种解决方案来填充亿级数据

Insert into select

该方式不涉及IO，所以速度最快，但是因为大多重复数据，自由度不高，【同时该方式不能用于数据库表迁移，因为SELECT 全表扫描，InnoDB行级锁会锁住大量数据，表的使用就崩溃】

Insert Into Select 语句可以先从一个表中复制数据，再将数据插入目标表，目标表已经存在的行不受影响

INSERT INTO target_table SELECT 字段1，2..... FROM origin_table
1

连续执行多次，因为是指数级增长，可以快速填充

但是数据会出现大量重复，并且执行该语句多次会越来越慢，因为一次性插入庞大数据【从最开始0.000Xms ----> 几分钟】

比如向一个s_id,s_name, s_birth, s_sex的student表填充上亿数据，就可以多执行几次

use cfengtest;
insert into student select null,s_name,s_birth,s_sex from student;
1
2

插入上百万数据时，执行很慢

16:26:50	insert into student select null,s_name,s_birth,s_sex from student	1572864 row(s) affected Records: 1572864  Duplicates: 0  Warnings: 0	28.844 sec 
# 这里插入157万数据，耗时28s， 但是还是很快了，直接将查询结果插入，不涉及IO
cfeng迅速就将数据扩充到628万

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|  6285728 |
+----------+
1 row in set (1.35 sec)

select * from studnet limit 6000000，10；
10 rows in set (4.91 sec)  <----- Limit数值过大成为慢SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14

这里需要注意，Mysql的数据执行需要缓冲区，需要在InnoDB的buffer pool种处理缓存：

包括数据缓存、索引缓存、缓存数据、内部结构

当MySQL大批量执行INSERT INTO SELECT ，要求InnoDB的buffer pool足够大

解决缓存区异常的方案：

1. INSET INTO SELECT 语句加上Limit 限制一次性插入的数量
2. 增加innodb_buffer_size的值

查看默认的数据库引擎的参数：

show variables like ‘%innodb%’

innodb_buffer_pool_size | 8388608 默认大小8MB

修改大小为64MB， 在LINUX系统修改my.cnf， windows系统为my.ini，修改重新运行即可

存储过程loop insert

虽然存储过程不具有一致性，修改麻烦，不推荐使用，但是还是具有优势

存储过程就是数据库中可以完成某种特定功能的SQL语句集合

delimiter $$
CREATE PROCEDURE demo_in_parameter(in i int) 
BEGIN
	WHILE i < 10000000 DO
		insert into student values ('','cfeng','2001-07-01','男');
		SET i=i+1;
	END WHILE
end$$
1
2
3
4
5
6
7
8

可以使用存储方案的随机函数创建数据，如果要使用事务，提交事务不要太频繁，避免磁盘IO异常

调用该存储过程 call demo_in_parameter(0)

Loadfile 导入CVS文件

Loadfile就是利用java语言或者python先创建CVS、txt，再将数据存放在文件中，通过MySQL的loadfile命令，将文件数据导入

• 准备文件，利用java编写相应的CVS文件，内容

\N cfeng   2001/1/1 男
\N cLEI    1999/12/1 男
.....
1
2
3

• 将文件导入Mysql

  通过Load data infile xxx into table 命令导入数据

  load data local infile '/xxxx/xxx' into table student
  1

第三方的解决方案还包括DataFactory、DataFaker， 专业服务，当然数据自由度更高

大数据量表的查询要么优化索引，要么优化代码和网络

MySQL基准测试： sysbench、mysqlslap

填充大数据量之后，可能会存在问题：

• MySQL单表数据过亿，返回数据速度极慢是正确的吗？
• 单台MySQL数据库最大承载访问量？
• 主从复制如何选择策略减少对单台数据库的性能影响？
• 如何为MySQL数据库配置参数达到最优？

要想了解主从复制对于数据库性能的影响，就可以分别测试主从复制集群和单节点访问，得到两种响应结果

从代码上说：MySQL单表数据量过大确实更消耗性能，但是类似HashMap（超4000慢），但还是可以满足应用需求，速度也不一定是极慢（如果只是返回10条数据，还是ms级别 ---- cfeng验证过）

sysbench

模块化、跨平台、开源的多线程基准测试锅具，可以执行CPU、内存、线程、IO、数据库等方面的测试

CPU --- 处理器性能      threads--- 线程调度性能  mutex --- 互斥锁性能
memory --- 内存分配和传输速度性能    fileio --- 文件IO性能  oltp -- 数据库性能（OLTP基准测试）
1
2

对于数据库，主要测试不同系统参数下数据库的负载情况，支持MySQL等少量数据库

使用方式 ：

1. prepare： 造数据
2. run ： 执行脚本进行测试
3. cleanup： 删除测试数据

sysbench需要从mysql官网下载https://github.com/akopytov/sysbench

wget https://downloads.mysql.com/source/dbt2-0.37.50.16.tar.gz

tar -zvxf sysbench-0.4.12.14.tar.gz

cd sysbench-0.4.12.14

./configure

yum install mysql-devel

make 

make install
1
2
3
4
5
6
7
8
9
10
11
12
13

通过sysbench --version 查看是否按照成功

Sysbench的命令参数

sysbench [options] ...[testname] [command]
1

options是sysbench的基本参数，指定sysbench的并发度，压测时长，线程数、总等待数…

testname是sysbench的基准测试名称，可选项包括fileio、memory、cpu，捆绑的Lua脚本名称或者定制的Lua脚本

command指定sysbench执行哪些测试命令，包括prepare、run、cleanup

• 压测CPU

sysbench --test=cpu  run

压测过程使用top发现CPU使用率飙升
1
2
3

• 压测内存

sysbench --test=memory run
1

• 压测磁盘IO， 需要prepare、run、cleanup

sysbench --test=fileio --file-total-size=1G prepare

sysbench --test=fileio --file-total-size=1G --file-test-mode=rndrw run

sysbench --test=fileio --file-total-size=1G cleanup
1
2
3
4
5

• 压测MySQL

sysbench \
--test=oltp \
--db-dirver=mysql \
--mysql-table-engine=myisam \
--mysql-db=mytest \
--oltp-table-size=100 \
--mysql-socket=/var/lib/mysql/myslq.sock \
--mysql-host=192.168.204.100 \
--mysql-user=cfeng \
--mysql-password=cfeng \
prepare


sysbench \
--test=oltp \
--db-dirver=mysql \
--mysql-table-engine=myisam \
--mysql-db=mytest \
--oltp-table-size=100 \
--mysql-socket=/var/lib/mysql/myslq.sock \
--mysql-host=192.168.204.100 \
--mysql-user=cfeng \
--mysql-password=cfeng \
run


sysbench \
--test=oltp \
--db-dirver=mysql \
--mysql-table-engine=myisam \
--mysql-db=mytest \
--oltp-table-size=100 \
--mysql-socket=/var/lib/mysql/myslq.sock \
--mysql-host=192.168.204.100 \
--mysql-user=cfeng \
--mysql-password=cfeng \
cleanup


压测数据库TPS性能
sysbench \
--db-dirver=mysql \
--time=180 \
--thread=4 \
--report-interval=1 
--mysql-host=192.168.204.100 \
--mysql-port=3306 \
--mysql-user=cfeng \
--mysql-password=cfeng \
--oltp_read_write \
--db-ps-mode=disable\
run
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

这里只是简单介绍一下，开拓一下，如果详细使用后会出文章