MySQL插入10万数据时间(结论:最快14.967s,每秒插入6681条)

记录我的一次MySQL操作Demo:


 

存储过程:


 

DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
   DECLARE n int DEFAULT 1;
        loopname:LOOP
            INSERT INTO user_info(id,name,age,gender,address,tel)VALUES(n,'lilis',16,2,'杭州下沙',18758);
            SET n=n+1;
        IF n=100000 THEN
            LEAVE loopname;
        END IF;
        END LOOP loopname;
END;
CALL my_insert();

 

表结构:


 


完全插入花费时间:时间: 228.370s(3分多钟)平均每秒插入:438.6条记录。


电脑配置信息:内存8g,i3 3217u,固态硬盘(浦科特m6s 128g)。


我觉得这个插入速度太慢了,后来百度的时候注意到MySQL的配置文件中innodb_flush_log_at_trx_commit=2这个配置非常影响写入性能,默认为1,改成2之后同样的数据量写入就快多了,降到了

时间: 14.967s

关于:innodb_flush_log_at_trx_commit这个参数注释原话是这样的:

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.

翻译过来就是说设为1时:会在每个事务提交后会执行往磁盘写日志的操作。设为0或2可减少日志对磁盘IO的负担。0表示每秒往磁盘写一次日志,2表示每次事务都往内存提交日志,但每秒往磁盘仅写入一次。现在想想为什么之前插入这么慢,就是因为每个inset完成后都往磁盘写日志,导致占满了磁盘IO(我观察了磁盘IO,参数设为1的时候磁盘占用时间保持100%)。


 

数据库库优化有4层次:

1.sql语句及索引优化(性价比最高,最容易操作)

2.表结构优化(数据库设计层面)

3.数据库配置优化

4.硬件的升级

我遇到的这个问题就属于第3个层次。

 

posted @ 2017-06-15 13:10  蓝色星辰  阅读(19045)  评论(3编辑  收藏  举报