mysql - 按主键顺序插入行更快吗？

Question

我有一张这样的 table :

create table stream_last
(
        symbol_id       integer,
        high            decimal(8,2),
        low             decimal(8,2),
        last            decimal(8,2),
        volume          integer,
        last_updated    datetime,
        INDEX USING BTREE(symbol_id)
) ENGINE=MEMORY;

我需要每 5 秒执行大约 4,000 次插入(由 C 程序读取流并在 symbol_id 上聚合)。但是，我可以先对数据进行排序，然后按 symbol_id 升序插入。这会让这个批量插入更快吗？

第二个问题是，BTREE 是索引的最佳选择吗？从文档中，我可以使用 HASH 但不确定哪个最好...

唐

Answer 1

不要认为你可能会从你提出的优化中获得任何显着差异，但这里列出了建议

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

当然可以帮助您更好地了解您的需求。

这句话可能有道理

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

You can use the following methods to speed up inserts:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.3, “Server System Variables”.