整理:mysql千万级数据库插入速度和读取速度的调整
- 作者: 付出眞芯丶未必能嘚到嗳8191650
- 来源: 51数据库
- 2021-09-03
一
1：innodb_flush_log_at_trx_commit
参数解释：
1：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
2：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
3：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
修改参数：
1: 找到mysql配置文件mysql.ini，修改成合适的值，然后重启mysql。
注意事项:
1：当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
2：当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
3：当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
说明：
1：mysql 手册中，为了确保事务的持久性和复制设置的耐受性、一致性，都是建议将这个参数设置为 1 的。
抄自：http://www.51sjk.com/Upload/Articles/1/0/260/260530_20210701003910696.jpg
2：innodb_autoextend_increment
MySQL Innodb表空间不足的处理方法
官方给出的解决方案：
添加和删除 InnoDB 数据和日志文件
这一节描述在InnoDB表空间耗尽空间之时，或者你想要改变日志文件大小之时，你可以做的一些事情。
最简单的，增加InnoDB表空间大小的方法是从开始配置它为自动扩展的。为表空间定义里的最后一个数据文件指定autoextend属性。然后在文件耗尽空间之时，InnoDB以8MB为 增量自动增加该文件的大小。增加的大小可以通过设置innodb_autoextend_increment值来配置,这个值以MB为单位，默认的是8。
作为替代，你可以通过添加另一个数据文件来增加表空间的尺寸。要这么做的话，你必须停止MySQL服务器，编辑my.cnf文件 ，添加一个新数据文件到innodb_data_file_path的末尾，然后再次启动服务器。
如果最后一个数据文件是用关键字autoextend定义的，编辑my.cnf文件的步骤必须考虑最后一个数据文件已经增长到多大。获取数据文件的尺寸，把它四舍五入到最接近乘积1024 × 1024bytes (= 1MB)，然后在innodb_data_file_path中明确指定大致的尺寸。然后你可以添加另一个数据文件。记得只有innodb_data_file_path里最后一个数据可以被指定为自动扩展。
作为一个例子。假设表空间正好有一个自动扩展文件ibdata1：
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
假设这个数据文件过一段时间已经长到988MB。下面是添加另一个总扩展数据文件之后的配置行：
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
当你添加一个新文件到表空间的之后，请确信它并不存在。当你重启服务器之时，InnoDB创建并初始化这个文件。
当前，你不能从表空间删除一个数据文件。要增加表空间的大小，使用如下步骤：
1. 使用mysqldump 转储所有InnoDB表。
2. 停止服务器。
3. 删除所有已存在的表空间文件。
4. 配置新表空间。
5. 重启服务器。
6. 导入转储文件。
如果你想要改变你的InnoDB日志文件的数量和大小，你必须要停止MySQL服务器，并确信它被无错误地关闭。
随后复制旧日志文件到 一个安全的地方以防万一某样东西在关闭时出错而你需要用它们来恢复表空间。
从日志文件目录删除所有旧日志文件，编辑my.cnf来改变日志文件配置，并再 次启动MySQL服务器。
mysqld 在启动之时发现没有日志文件，然后告诉你它正在创建一个新的日志文件。
实际中的简化版：
mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。
如果不把数据分开存放的话，这个文件的大小很容易就上了G，甚至10+G。对于某些应用来说，并不是太合适。因此要把此文件缩小。
方法：数据文件单独存放。
步骤：
1:备份数据库
从命令行进入MySQL Server 5.0\bin
备份全部数据库，执行命令mysqldump -q -uusername -pyourpassword --add-drop-table -all-databases > /all.sql
做完此步后，停止数据库服务。
2:修改mysql配置文件
修改my.ini文件，增加下面配置
innodb_file_per_table
对每张表使用单独的innoDB文件, 修改/etc/my.cnf文件
3:删除原数据文件
删除原来的ibdata1文件及日志文件ib_logfile*，删除data目录下的应用数据库文件夹(mysql文件夹不要删)
4:还原数据库
启动数据库服务
从命令行进入MySQL Server 5.0\bin
还原全部数据库，执行命令mysql -uusername -pyourpassword < /all.sql
经过以上几步后，可以看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面。
抄自：http://www.51sjk.com/Upload/Articles/1/0/260/260530_2021070100391069601.jpg （这个作者写有不少mysql文章）
懒人方法：
innodb_autoextend_increment由默认的8M调大到128M。(根据数据库服务器情况调整)
此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。
3：innodb_log_buffer_size
日志缓存
将 innodb_log_buffer_size 配置由于默认1M 调整到 16M。(根据数据库服务器情况调整)
此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。
怎样优化调整innodb_log_buffer_size？
官方文档并没有直接告诉如何调整 innodb_log_buffer_size 大小，
根据对mysql 状态信息了解知道 innodb_log_buffer_size 跟Innodb_os_log_written 和 Com_commit 状态有关系。
1：Innodb_os_log_written: innodb 引擎redo log 多少个字节被写入
2：Com_commit : 在autocommit = 0 时候 有多少个innodb 事务提交 ，如果autocommit = 1，innodb 事务不包括在Com_commit 中
在业务压力测试或者高峰时间段进行以下操作：
set global autocommit = 0;
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
经过一段长时间.............
SHOW GLOBAL STATUS LIKE 'Com_commit';
通过以下公式计算平均每个事务要缓冲的平均数据量多少个bytes,前提你需要保留多长时间的redo log buffer
Innodb_os_log_written / Com_commit 在业务高峰运行期间如果 Innodb_log_waits 值为0或接近0， innodb_log_buffer_size 可能太大,可以减少。
set @old_value := 0;
set @new_value := 0;
select VARIABLE_VALUE into @old_value from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_log_waits';
select SLEEP(时间);select VARIABLE_VALUE into @new_value from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Innodb_log_waits';
select @old_value;
select @new_value;
抄自：http://www.51sjk.com/Upload/Articles/1/0/260/260530_20210701003910712.html
4：innodb_log_file_size
innodb_log_file_size的大小设置将会影响mysql数据库的写入性能，若设置的太小，会增加checkpoint写的次数
将 innodb_log_file_size 配置由于默认 8M 调整到 128M。(根据数据库服务器情况调整)
此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。
别人测试情况汇总：
经过以上调整，系统插入速度由于原来10分钟几万条提升至1秒1W左右；
注：以上参数调整，需要根据不同机器来进行实际调整。 特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整；
因为涉及MySQL本身的容灾处理
另外：
批量插入数据量过大，解决办法
修改 my.ini 加上 max_allowed_packet =67108864
67108864=64M
默认大小4194304 也就是4M
修改完成之后要重启mysql服务，如果通过命令行修改就不用重启mysql服务。
命令修改：（即时生效）
正确写法：
设置为500M
mysql> set global max_allowed_packet = 500*1024*1024;
或者
mysql> set global max_allowed_packet = 524288000;
mysql> flush privileges
二
提升数据库读取速度，重数据库层面上读取速度提升主要由于几点：简化SQL、加索引和分区； 经过检查程序SQL已经是最简单，查询条件上已经增加索引。我们只能用武器：表分区。
数据库 MySQL分区前准备：在MySQL中，表空间就是存储数据和索引的数据文件。
将S11数据库由于同享tablespace 修改为支持多个tablespace;
将wb_user_info_sina 和 wb_user_info_tx 两个表修改为各自独立表空间；（Sina：1700W数据，2.6G 大数据文件，Tencent 1400W，2.3G大数据文件）；
分区操作：
1：将现有的主键和索引先删除
2：重现建立id,uid 的联合主键
3：再以 uid 为键值进行分区。这时候到/var/data/mysql 查看数据文件，可以看到两个大表各自独立表空间已经分割成若干个较少独立分区空间。
（这时候若以uid 为检索条件进行查询，并不提升速度；因为键值只是安排数据存储的分区并不会建立分区索引。我非常郁闷这点比Oracle 差得不是一点半点。）
4：再以 uid 字段上进行建立索引。再次到/var/data/mysql 文件夹查看数据文件，非常郁闷地发现各个分区Size竟然大了，
MySQL还是老样子将索引与数据存储在同一个tablespace里面。若能index 与 数据分离能够更加好管理。
经过以上调整，暂时没能体现出系统读取速度提升；基本都是在 2~3秒完成5K数据更新。
抄自：http://www.51sjk.com/Upload/Articles/1/0/260/260530_20210701003910727.jpg
MySQL数据库插入速度调整补充资料：
MySQL 从最开始的时候 1000条/分钟的插入速度调高至 10000条/秒。 相信大家都已经等急了相关介绍，下面我做调优时候的整个过程。提高数据库插入性能中心思想：
1、尽量使数据库一次性写入Data File
2、减少数据库的checkpoint 操作
3、程序上尽量缓冲数据，进行批量式插入与提交
4、减少系统的IO冲突
根据以上四点内容，作为一个业余DBA对MySQL服务进行了下面调整：
修改负责收录记录MySQL服务器配置，提升MySQL整体写速度；具体为下面三个数据库变量值：
innodb_autoextend_increment、innodb_log_buffer_size、innodb_log_file_size；
此三个变量默认值分别为 5M、8M、8M，根据服务器内存大小与具体使用情况，
将此三只分别修改为：128M、16M、128M。同时，也将原来2个 Log File 变更为 8 个Log File。
此次修改主要满足第一和第二点，如：增加innodb_autoextend_increment就是为了避免由于频繁自动扩展Data File而导致 MySQL 的checkpoint 操作；
将大表转变为独立表空并且进行分区，然后将不同分区下挂在多个不同硬盘阵列中。
完成了以上修改操作后，下面幸福结果：
获取测试结果：
Query OK, 2500000 rows affected (4 min 4.85 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (4 min 58.89 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 25.91 sec)份额为
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 22.32 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
最后表的数据量：
+————+
| count(*) |
+————+
| 10000000|
+————+
从上面结果来看，数据量增加会对插入性能有一定影响。不过，整体速度还是非常可以。
一天不到时间，就可以完成4亿数据正常处理。
抄自：http://www.51sjk.com/Upload/Articles/1/0/260/260530_20210701003910727.jpg
MySQL 5.7参考手册：http://www.51sjk.com/Upload/Articles/1/0/260/260530_20210701003910743.jpg
推荐阅读
