内容纲要
目录
- 优化INSERT语句
- 优化UPDATE语句
- 优化DELETE语句
这部分内容来说明加速数据更改类语句：INSERT、UPDATE和DELETE。
优化INSERT语句
概述
为了优化insert的速度，可以将很多小的操作组合成一个大的操作来优化。理想情况下，你可以与MySQL建立一个连接，然后发送一条多行数据插入语句，并且延迟所有索引更新和一致性检查到最后。
对于插入一行的消耗由以下因素确定，每个因素中的数字表示时间消耗大概的时间占比：
- 建立连接 ：（3） 建立数据库连接池，实现多路复用
- 发送查询到Server：（2）
- 解析查询：（2）
- 插入行：（1 * 行大小）发送数据尽可能小
- 插入索引：（1 * 索引数） 表索引数量尽可能少
- 关闭连接：（1） 数据库连接池与超时关闭连接，减少关闭连接的次数
以上内容没有考虑打开表的初始消耗，因为每个并发运行的查询，打开表的初始开销只有一次。
假设使用B-tree索引的情况下， 表的大小会减慢logn的索引插入速度。
优化方法
你可以使用以下方法来加速插入：
- 如果你可以将相同客户端在同一时间的多条插入语句组合成INSERT with multiple VALUES这样的一个插入语句，那么就会加速插入速度。这种处理在大多数情况下被认为比单条INSERT语句速度要快。如果你添加数据到一个非空表中，你可以调整bulk_insert_buffer_size变量让插入更快。
- 当你从一个文本文件中加载数据到一张表时，你可以使用LOAD DATA语句。这会比使用INSERT语句快20倍。
- 使用列默认值。这样可以减少MySQL的解析开销并提升查询速度。
优化UPDATE语句
概述
更新的优化像LIKE查询的优化，只是需要有数据写入的消耗。写入的速度依赖于更新数据的总量和索引更新的数量。没有更改索引列的数据就不会更新索引。
另一种加速更新的方法是延迟更新然后对一行做多次更新。如果UPDATE语句锁表，那么一次执行多个update比多次执行一个update要快很多。
优化方法
- 更新时尽可能避免索引更新，这样可以避免索引更新带来的开销。
- 合并update对同一行执行多次更新。
优化DELETE语句
概述
在MyISAM表中删除一行的主要时间开销是删除索引的数量。为了更快的删除行，可以增加key_buffer_size系统变量的大小。
如果删除MyISAM表中的所有行，使用TRUNCATE TABLE tbl_name比DELETE FROM tbl_name要快很多。但是TRUNCATE 操作不是事务安全的；当有活动的事务或者表锁是TRUNCATE操作会失败。
优化方法
- 表尽可能少索引。
- 通过增加key_buffer_size来加速删除。
- 清空表尽量用TRUNCATE_TABLE，因为速度快，但是不是事务安全的。
总结
- 优化INSERT可以尽可能单条插入合并为多条插入和使用默认值，合并单条为多条场景可以是消息中间件消费时对业务数据批量处理后合并插入。
- 优化UPDATE也是尽可能合并更新，并且尽可能避免索引更新（这是在业务允许的情况下）。
- 优化DELETE是尽可能减少索引数量，并通过增加缓冲大小来加速。如果是清空表，那么truncate比delete速度快。