Speed up Insert-优快云博客

本文探讨了通过水平分区和将大型字段移至单独表格来提升数据库性能的方法。具体介绍了去除大varchar字段后的查询速度显著提高，并建议减小索引列大小以提升效率。此外，还提到了使用InnoDB表进行批量插入时，通过事务处理可以显著加快插入速度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

It could be a time for horizontal partitioning and moving blob field into a separate table. In this article in 'A Quick Side Note on Vertical Partitioning' author removes a larger varchar field from a table and it increases speed of a query about order of magnitude.

The reason is physical traversal of the data on a disk becomes significantly faster if there is less space to cover, so moving bigger fields elsewhere increases performance.

Also (and you probably do it already) it is beneficial to decrease the size of your index column to its absolute minumum (char(32) in ascii encoding for md5), because size of the key is directly proportional to the speed of its use.

If you do multiple inserts at a time with InnoDB tables you can significantly increase speed of inserts by wrapping them into transaction and doing mupliple inserts in one query:

START TRANSACTION INSERT INTO x (id, md5, field1, field2) values (1, '123dab...', 'data1','data2'),(2,'ab2...','data3','data4'),.....; COMMIT

转载于:https://my.oschina.net/jsyang888/blog/114189