【转】MySQL插入经验

原文：http://lxh71105321.blog.163.com/blog/static/23988844200901394611940/#

 

好长时间了一直在为一件事情而疑惑：为什么使用MYISAM引擎的时候，mysql的数据插入的速度这么反而变慢了。我很费解

    myisam不支持事务，数据插入的速度应该是很快速的，没有事务的消耗。而且使用的是极快的表级锁，单线程插入应该是相当的快的。

    innodb支持事务，支持行级锁，多线程并发支持的很好，不管线程是更新还是查找，innodb最大程度的发挥着一个数据库该有的性能。

    从上面很明显的可以看出来，单线程操作对于MYISAM应该是相当快速的，但是程序不知为何引擎为innodb的时候，速度可以有7600条/秒，但是换成MYISAM的时候仅剩下2000条/秒。这两天得闲来琢磨一下为什么，于是在mysql的官方看到一篇文章，讲关于myisam的插入优化的。我着手优化了几条，结果让我很兴奋。

    首先是一个ibatis的问题，在原来的程序中，插入数据库的操作是起来一个事务，批量提交的，1w条提交一次。问题就在这里，貌似ibatis的批量提交跟myisam的引擎有某种冲突，导致了性能的严重下降，而且插入数据库的时候超级占用CPU，8核的机器，插入的时候，高峰能能消耗700%的CPU，而且维持在600%+好长一段时间。

    解决了这个问题，插入的效率已经达到了7000+条/秒了，不过下面才是真正的优化。

    insert的语法很简单，insert into tableName(c1,c2,c3) values(value1,value2,value3)。这是普通的，其实还支持一个相当于批量提交的语法insert into tableName(c1,c2,c3) values(value1,valeu2,value3),(value11,value22,value33),(value111,value222,value333).....不过这个默认的有个长度限制1M。好久之前DBA就建议我这么做，我一直没高兴改，毕竟在程序还没能保证能成功运行的时候，也没时间来考虑这些我认为“很小”的优化。等DBA把长度设置成24M之后，我算了下，一次最多可以提交48000条记录。防止线上的某些BT的数据，我每次拼装2w条。这次试试的结果让我大吃一惊3w+条/秒。

    我今天程序的优化到此为止，我觉得这样的入库速度我已经比较满意了。不过还有几个优化手段，有机会或者必要的时候可以试试。

    insert 的语法还支持delayed插入。语法形如:insert delayed into tableName ....如果使用这种语法，数据库在接受到请求的时候会立即返回结果，事实上数据可能还没有完全插入。这个是减少数据库对程序性能的影响，不过这样可以将数据库的插入性能压到极致，如果有足够的数据量。不过这个有个要注意的地方：如果你的sql语句形如insert delayed ....select ....或者insert delayed ...update...，这两种形式的sql，mysql的语法器会直接忽略delayed，即还是等插入完成再返回请求。

    mysql还有一个比较BT的索引，跟oracle不一样，mysql里面想要查询走索引，必须是被已建索引的左边包含。举个例子，建立索引index(k1,k2,k3)，那么走索引的有且仅有三种where k1 = ? 或者where k1 = ? and k2 = ? 或者where k1 = ? and k2 = ? and k3 = ?，其他的都不走索引，例如where k2 = ? and k3 = ?。这下应该清楚了

   以上这些没有经过深入研究，只是平时用到，不得已去学一下