mysql数据库插入速度和读取速度的调整记录[MySQL防范]

（1）提高数据库插入性能中央机惟:尽大概将数据一次性写入到Data File和削减数据库的checkpoint 操作.这次改正了下面四个配置项:
1）将 innodb_flush_log_at_trx_commit 配置设定为0;按过往经验设定为0,插入速度会有很大提高.

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.
1:the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file
2:the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it
2）将 innodb_autoextend_increment 配置由于默许8M 调整到 128M

此配置项作用主如果当tablespace 空间已经满了后,需求MySQL系统需求自动扩大多少空间,每次tablespace 扩大城市让各个SQL 处于等候状况.增添自动扩大Size可以削减tablespace自动扩大次数.

3）将 innodb_log_buffer_size 配置由于默许1M 调整到 16M

此配置项作用设定innodb 数据库引擎写日记缓存区;将此缓存段增大可以削减数据库写数据文件次数.

4）将 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本身的容灾处理.

（2）晋升数据库读取速度,重数据库层面上读取速度晋升主要由于几点:简化SQL、加索引和分区; 经过查抄程序SQL已经是最简单,查询条件上已经增添索引.我们只能用武器:表分区.

数据库 MySQL分区前预备:在MySQL中,表空间就是存储数据和索引的数据文件.
将S11数据库由于同享tablespace 改正成支持多个tablespace;

将wb_user_info_sina 和 wb_user_info_tx 两个表改正成各自独立表空间;（Sina:1700W数据,2.6G 大数据文件,Tencent 1400W,2.3G大数据文件）;
分区操作:
将现有的主键和索引先删除
重现成立id,uid 的结合主键
再以 uid 为键值举行分区.这时刻到/var/data/mysql 查看数据文件,可以看到两个大表各自独立表空间已经分割成若干个较少独立分区空间.（这时刻若以uid 为检索条件举行查询,并不晋升速度;因为键值只是安置数据存储的分区并不会成立分区索引.我非常忧郁这点比Oracle 差得不是一点半点.）
再以 uid 字段上举行成立索引.再次到/var/data/mysql 文件夹查看数据文件,非常忧郁地发现各个分区Size竟然大了.MySQL还是老模样将索引与数据存储在同一个tablespace里面.若能index 与 数据别离可以越发好管理.

经过以上调整,暂时没能表现出系统读取速度晋升;基本都是在 2~3秒完成5K数据更新.

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亿数据正常处理.预计数据库瓶颈已经被巧妙办理,后果变成程序"猿"苦逼地向我抱怨,大哥不用这么狠啊.