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

创建高性能的索引

减少索引和数据的碎片

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

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

mysql>ALTER TABLE <table> ENGINE=<engine>;

对于开启了expand_fast_index_creation参数的Percona Server，按这种方式重建表，则会同时消除表和索引的碎片化。但对于标准版本的MySQL则只会消除表(实际上是聚簇索引)的碎片化。可用先删除所有索引，然后重建表，最后重新创建索引的方式模拟Percona Server的这个功能。应该通过一些实际测量而不是随意假设来确定是否需要消除索引和表的碎片化。Percona的XtraBackup有个–stats参数以非备份的方式运行，而只是打印索引和表的统计情况。包括页中的数据量和空余空间。这可以用来确定数据的碎片化程度。另外也要考虑数据是否已经达到稳定状态，如果你进行碎片整理将数据压缩到一起，可能反而会导致后续的更新操作触发一系列的页分裂和重组，这会对性能造成不良的影响(直到数据再次达到新的稳定状态)

总结。

索引是一个非常复杂的话题！MySQL和存储引擎访问数据的方式，加上索引的特性，使得索引成为一个影响数据访问的有力而灵活的工作(无论数据是在磁盘中还是在内存中)。在MySQL中，大多数情况下都会使用B-Tree索引。其他类型的索引大多只适用于特殊的目的。如果在合适的场景中使用索引，将大大提高查询的响应时间。在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

• 1.单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机IO要快很多，不过这一点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多需要的行。使用索引可以创建位置引用以提升效率
• 2.按顺序访问范围数据是很快的,这有两个原因。第一，顺序IO不需要多次磁盘寻道，所以比随机IO要快很多(特别是对机械硬盘)。第二，如果服务器能够按需要顺序读取数据，那么久不再需要额外的排序操作，并且GROUP BY 查询也无序再做排序和将行按组进行聚合计算了。
• 3.索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎久不需要再回表查找行。这避免了大量的单行访问。

总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。这也与Lahdenmaki和Leach的书中的"三星"评价系统是一致的。如果表上的每一个查询都有一个完美的索引来满足当然是最好的。但不幸的是，要这么做有时可能需要创建大量的索引。还有一些时候对某些查询是不可能创建一个达到"三星"的索引(例如查询要按照两个列排序，其中一个列是正需，另一个列倒序)。这时必须有所取舍以创建最合适的索引，或者寻求替代策略(例如反范式化，或者提前计算汇总表等)
理解索引是如何工作的非常重要，应该根据这些理解来创建最合适的索引，而不是根据一些诸如"在多列索引中将选择性最高的列放在第一列(例如约会网站按性别查询)"或"应该为WHERE子句中出现的所有列创建索引"之类的经验法则及其推论。那么如何判断一个系统创建的索引是合理的呢？一般来说，建议按照响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否适用了随机IO访问数据，或者是有太多回表查询那些不再索引中的列的操作。如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提升性能。如果不行，也可以看看是否可以重写该查询，将其转化成一个能够高效利用现有索引或者新创建索引的查询。

如果基于响应时间的分析不能找出有问题的查询呢？是否可能我们没有注意道"很糟糕"的查询，需要一个更好地索引来获取更高的性能？一般来说，不可能。对于诊断时抓不到的查询，那就不是问题。但是这个查询未来有可能会成为问题，因为应用程序、数据和负载都在变化。如果仍然想找到那些索引不是很合适的查询，并在它们成为问题前进行优化，则可以适用pt-query-digest的查询审查"review"功能，分析其EXPLAIN出来的执行计划

查询性能优化

概述

前面介绍了如何设计最优的库表结构、如何建立最好的索引，这些对于高性能来说是必不可少的。但这些还不够——还需要合理的设计查询。如果查询写的很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。查询优化、索引优化、库表结构优化需要齐头并进，一个不落。在获得编写MySQL查询的经验的同时，也将学习到如何为高效的查询设计表和索引。同样的，也可以学习到在优化库表结构时会影响到那些类型的查询。这个过程需要时间。接下来激昂从查询设计的一些基本原则开始——这也是在发现查询效率不高的时候首先需要考虑的因素。然后会介绍一些更深的查询优化技巧，并会介绍一些MySQL优化器内部的机制。将展示MySQL时如何执行查询的，如何改变一个查询的执行计划

为什么查询速度会慢

在尝试编写快速的查询之前，需要清楚一点，真正重要的是响应时间。如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快（有时候你可能还需要修改一些查询，减少这些查询对系统中运行的其他查询的影响，这种情况下，你是在减少一个查询的资源消耗）。MySQL在执行查询的时候有那些子任务，哪些子任务运行的速度很慢？这里很难给出完整的列表。通常来说，查询的生命周期大致可以按照顺序来看:从客户端，到服务器，然后再服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中"执行"可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎以及调用后的数据处理，包括排序、分组等。在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络、CPU计算，生成统计信息和执行计划、锁等待(互斥等待)等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗时间，根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。在每一个消耗大量时间的查询案例中，都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作上所花费的时间