MySQL之创建高性能的索引和查询性能优化(一)

创建高性能的索引

减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。根据设计，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免地。然而，如果叶子页在物理分布上是顺序且紧密地，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多倍；对于索引覆盖扫描这一点更加明显。表的数据存储也可能碎片化。
然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片:

• 1.行碎片(Row fragmentation)
  这种碎片指的是数据行被存储在多个地方的片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降
• 2.行间碎片(Intra-row fragmentation)
  行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能从磁盘上顺序存储的数据中获益
• 3.剩余空间碎片(Free space fragmentation)
  剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
  对于MyISAM表，这三类碎片化都可能发生。但InnoDB不会出现短小的行碎片；InnoDB会移动短小的行并重写到另一个片段中。可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效地。对于一些存储引擎如MyISAM，可以通过排序算法重建索引的方式来消除碎片。老版本的InnoDB没有什么消除碎片化的方法。不过最新版本InnoDB新增了"在线"添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片