数据插入的顺序

实际应用中，如果InnoDB类型的表没有定义某个特定的列为主键，则最好定义一个和应用无关的字段为主键——通常为Auto Increment的整形字段——这样的好处是能人为的控制数据按序插入。

数据乱序插入时的性能问题是：

1. The destination page might have been flushed to disk and removed from the caches, in which case, InnoDB will have to find it and read if from the disk before it can insert the new row. This causes a lot of random IO.

     目标数据页可能已经被换出到磁盘，这样在插入数据前，InnoDB必须先找到对应数据页并载入内存——这会带来很多随即IO。

2. InnoDB sometimes has to split pages to make room for new rows. This requires moving around a lot of data.

     有可能触发页面分裂，这就需要移动周围的数据。

3. Pages become sparsely and irregularly filled because of splitting, so the final data if fragmented.

     频繁的页面分裂会造成页面的数据稀疏和无序，产生大量的数据碎片。

利用Auto Increment做数据序列化有可能碰到的问题：

For high-concurrency workloads, inserting in primary key order can actually create a single point of contention in InnoDB, as it is currently implemented. This "hot spot" is the upper end of the primary key. Because all inserts take place there, concurrent inserts may fight over next-key locks and/or AUTO_INCREMENT locks (either or both can be a hot spot). If you experience this problem, you may be able to redesign your table or application, or tune InnoDB to perform better for this specific workload.

对于高并发应用，向InnoDB的表中插入数据有可能遇到问题（由InnoDB实现引起的单点竞争）——数据的插入总是发生在主键序列的尾部，并发量太大，就有可能碰到”next-key locks"（存放数据的数据页资源锁定）或者“AUTO_INCREMENT locks"（获取自增长序列值的锁定）。

上面说的这两种高并发情况下的竞争锁定，还没碰到过，不知道这个并发要高到什么情况才会碰到。