MySQL数据库性能优化-索引原理和Query cache配置使用（三）

总结之前的内容，MySQL数据库性能优化的途径有以下几个方式

1. 提高硬件方面的配置，比如SSD固态硬盘等。
2. 选择合适的存储引擎，一般使用Innodb（支持事务处理等优势）。
3. 适当的创建索引和进行Query cache配置相结合。

来学一下一下第三点知识mysql索引的原理。

4、影响SQL性能的要素

MySQL数据库的性能不止受到性能参数和底层硬件条件的影响，在这两个条件一定的情况下，开发人员对SQL语句的优化能力更能影响MySQL数据库的性能。由于MySQL中不同数据库引擎对SQL语句的处理过程不尽相同，所以对SQL语句的优化就一定要首先确定使用的数据库引擎的类型。例如MyISAM引擎中统计某一个数据表的总行数时，只需要读取出已保存好的数据总行数就OK了。但InnoDB引擎要完成这个动作，就必须进行table scan/index scan，而是否为被扫描的字段创建了索引又直接影响了扫描速度。本文我们和读者一起来讨论一下InnoDB数据引擎下SQL语句常见的工作方式和优化规则。

4-1、索引

我们都知道无论使用哪种主流的关系型数据库，为SQL查找语句所依据的字段创建索引要比不创建索引时的性能高出几个数量级（当然这也要看SELECT查询语句的具体写法）。那么为什么会出现这样的情况呢？我们又依据什么样的原理来创建数据库的索引呢？本小节将首先为读者进行原理性描述。

4-1-1、B树

同样由于本文的定位，所以我们并不会讨论怎样使用脚本创建索引等基本操作问题。而是直接进行InnoDB数据库引擎中索引原理的讲述。InnoDB中数据库字段的索引采用树结构进行组织，这种树本质上是为了解决数据检索问题的平衡N阶树，又被称为B树。B树及其变体是大学《数据结构》课程中的基础知识，本人虽然工作许多年但始终对《数据结构》这门课程中的主要知识烂熟于心，并认为它和《 离散数学》一样已经成为笔者大学时期学习过的，对笔者实际工作帮助最大的两门课程。

为了帮助读者回忆起B树及其变体的基本结构，也为了后续内容能够正常铺开。我们非常需要使用相当的篇幅对它进行介绍。那么首先使用下图回答树结构的几个基本概念：节点、深度、子树等。

B树是一颗平衡的多叉检索树，它具有以下性质：

• 所谓检索树是指这样的树：树中任意非叶子节点A作为根节点的子树，其左子树上节点中的元素值均小于或等于节点A中元素的值；其右子树上节点中的元素值均大于或等于节点A中元素的值。检索树又称为排序树、有序树，如果将检索树降维成表结构则同样可以使用二分查找法进行节点检索，且时间复杂度基本不变。

• 如果不加任何构造限制，那么在树结构中检索元素的时间复杂度可能为O(n)。这显然失去了检索树的意义，如果一颗检索树能够保证树的高度H限制在节点数N的对数阶范围内（H=O(logn)），这样的检索树就称为平衡树。在编程实践中只要保证树中任意两个子树深度差的绝对值不大于1，就可以保证前述条件成立。另外B树中对深度差绝对值做了更严格的规定，即所有叶子结点都位于同一深度。

• 一颗B树的非叶子节点能够最多关联的子节点数量称为阶数。B树中的阶数至少为3，因为当阶数为2时B树进行节点分裂就可能会出现某叶子节点没有任何元素的情况。

• 树中每个非根节点所包含的元素个数 j 满足：(N/2) - 1 <= j <= N - 1，其中N表示B树的阶数。例如阶数为3的B树每一个非叶子结点能够存储的元素个数可能为 0个、1个和2个（但0个元素没有任何检索意义，还会造成树中任意两个子树深度差的绝对值改变）。

• 树中一个节点可关联的子节点数量比以上文字中提到的元素最大个数多1。也就是说阶数为3的B树每个节点最多可关联3个子节点。

上图展示了一颗3阶B树，它的每个节点最多可以有3个子节点，并且每个子节点中最多有2个元素。可以观察到B树满足检索树的基本规则：凡是比给定元素值大的所有元素，都作为该元素的子元素排列在该元素的左子树上；凡是比给定元素值大的所有元素，都作为该元素的子元素排列在该元素的右子树上。这样一来开发人员就可以使用和二分查找法类似的查找方式定位要查询的元素，或者在插入一个新元素前定位到新元素将要进行插入的位置。

下图演示了在B树中依次添加元素时的分裂和节点间关联过程，这些元素的值依次增大分别是：3、5、7、9、14、13、15、16、18、22、25、31、33。在实际应用开发中，虽然我们并不能保证插入B树的元素值都是增加的，但是对B树的插入操作过程却是相同的（两者的区别只是定位的将要插入新元素的位置不一样）：

4-1-2、变体B+树

B树的优点很明显：无论进行新元素的插入定位还是进行指定元素的查找，都可以快速完成这些定位/查找动作，其查询性能的时间复杂度相当于二分查找法（O(log(n))）。但是B树的缺点也很明显，首先插入新元素时可能涉及到树深度的改变，当然这个问题可以通过增加B树的阶数来解决。也就是让每一个节点可以拥有更多的子节点，这样就可以在存储元素总量不变的情况下减少树的理论深度，从而减少发生树深度改变的情况，

另一个问题就稍微严重一些了，那就是B树并不适合管理InnoDB引擎中的数据（这个在后文会进行说明）。还好B树结构有一个变体结构，称为B+树。两者最大的区别是，后者在B树的结构基础上扩展出了一个链表存储结构，并且在树的叶子节点对非叶子结点元素进行了冗余存储。如下图所示：

B树和B+树在新元素插入、元素删除、元素查找等基本处理方式上没有太大的区别。但是B+树的两个典型的结构变动刚好可以改进树结构在InnoDB引擎中的应用：

• 由于B+树的叶子节点存储了非叶子节点的冗余元素，所以我们可以在非叶子节点只记录某条数据的索引信息，而在叶子节点记录具体的数据信息。那么MySQL数据库就可以在InnoDB引擎启动时就加载B+树的非叶子节点到内存特定区域，这样做的最大好处是可以在内存空间和查找速度两个维度上找到最好的平衡点。

• 特别注意的是，实际在InnoDB引擎中的B+树结构，其叶子节点并不是存储一行数据（要真是这样，这颗B+树不知道需要有多大。。。），而是和Data Page作为对应。在之前介绍InnoDB引擎的文章中已经说明过数据库中Page的概念。Page是InnoDB引擎中最基本的数据操作单元，无论是InnoDB从磁盘上读取数据还是改变数据，都以Page作为操作单位。同样，InnoDB引擎中的索引结构也以Page为单位在叶子节点关联具体数据。

• 另外B+树在最底层将所有叶子节点串成了一个链表结构（不用担心某个叶子节点没有任何元素，因为B+树遵循所有B树的基本约束），这样一来在进行数据查找时就可以使用表结构进行元素的依次查找，而无需再进行树的遍历操作。实际上在InnoDB引擎中每一个叶子节点都是一个Page信息，构成链表结构后就可以检索每一个Page的上一个Page和下一个Page信息，这恰好也是InnoDB引擎中预读功能的实现基础。

4-1-3、InnoDB中的索引类型

InnoDB数据引擎使用B+树构造索引结构，其中的索引类型依据参与检索的字段不同可以分为主索引和非主索引；依据B+树叶子节点上真实数据的组织情况又可以分为聚族索引和非聚族索引。每一个索引B+树结构都会有一个独立的存储区域来存放，并且在需要进行检索时将这个结构加载到内存区域。真实情况是InnoDB引擎会加载索引B+树结构到内存的Buffer Pool区域。

• 聚簇索引（聚集索引）

  聚簇索引指的是这样的数据组织结构：索引B+树的每个叶子节点直接对应了真实的Data Page。并且B+树所有的叶子节点在最底层共同描述了一个可以直接进行行数据顺序扫描的Data Page结构。如下图所示：

  

  InnoDB引擎在组织索引和数据时，就是通过聚簇索引检索具体Data Page。而聚簇索引B+树的非叶子节点一般由数据表中的主键负责构造（当然也可能不是主键，这个后文会进行说明）。

• 主索引（主键索引/一级索引）

  基于InnoDB引擎工作的每一张数据表都需要有一个主索引，这是因为上一段文字中提到的InnoDB引擎需要使用聚簇索引查找到具体的Data Page，而工作在InnoDB引擎下的数据表有且只有主索引采用聚簇索引的方式组织数据。也就是说主索引B+树的叶子节点都对应了真实的Data Page信息。

  主索引在数据表的索引列表中使用PRIMARY关键字进行标识，一般来说是数据表的主键字段（也有可能是复合主键）。如果开发人员删除了InnoDB引擎中某张数据表的主索引，那么这个数据表将自行寻找一个非空且带有唯一约束的字段作为主索引。如果还是没有找到那样的字段，InnoDB引擎将使用一个隐含字段作为主索引（ROWID）。

  B+树的构造特性在这里就得到了充分利用，因为只需要将主索引B+树的非叶子节点加载到内存中。当检索请求需要读取某一个具体的Data Page时，再从磁盘上进行读取。还记得在之前的文章中提到的预读操作吗？B+树最底层叶子节点组成的链表结构，让InnoDB引擎能够轻松进行临近的Data Page的读取——如果参数设定了需要那样做的话。

• 非聚簇索引（非聚集索引）

  非聚族索引首先也是一颗B+树，只是非聚簇索引的叶子节点不再关联具体的Data Page信息，而是关联另一个索引值。InnoDB引擎下工作的每一个数据表虽然都只有一个聚簇索引，那就是它的主索引。但是每一张数据表可以有多个非聚簇索引，而后者的叶子节点全部存储着对应的数据主键信息（或者其它可以在聚簇索引中进行检索的关键值）。

  

  注意上图所示的B+树的叶子节点不再关联具体的Data Page信息，而只是关联了构成聚簇索引非叶子结点的主键信息。

• 非主索引（辅助索引/二级索引）

  数据表索引列表中除去主索引以外的其它索引都称为非主索引。非主索引都是使用非聚簇索引方式组织数据，也就是说它们实际上是对聚簇索引进行检索的数据结构依据。

  例如当开发人员创建了一个以字段A作为索引的非聚簇索引结构，并且在SQL中使用字段A作为查询条件执行检索时。InnoDB会首先使用非聚簇索引检索出对应的主键信息，然后再通过主索引检索这个主键对应的数据。

  

关于索引和执行计划调整的介绍，将在下一篇文章中提到。

4-2、Query Cache

为了加快查询语句的执行性能，从MySQL早期的版本开始就提供了一种名叫Query Cache的缓存技术。这个缓存技术和技术人员使用哪种数据库引擎无关，它完全独立工作于各种数据库引擎的上层，并使用独立的内存区域。

Query Cache的工作原理描述起来也比较简单，当某一个客户端连接（session）进行SQL查询并得到返回信息时，MySQL数据库除了将查询结果返回给客户端外，还在特定的内存区域缓存这条SQL查询语句的结果，以便包括这个客户端在内的所有客户的再次执行相同查询请求时，MySQL能够直接从缓存区返回结果。这里有两个关键点需要明确：

• 什么是“相同的查询语句”？Query Cache使用K-V结构对查询结果进行记录，其中的K就是查询语句本身（当然还要附加上诸如database name这样的关键信息）。所以“select * from A”和“select * from a”这样的语句将被看成是两条不同的查询语句。“select * from A”和“select * from A”也将被视为两条不同的查询语句（空格数量不一样）。

• 怎样避免“缓存数据不一致”的问题？ 
  一旦被缓存的查询结果所涉及的数据表发生了“写”操作，那么无论“写”操作本身是否影响到被缓存的数据，涉及到数据表的所有缓存数据都将被清除。这种简单暴力的处理方式，不仅绕过了数据一致性问题，还节约了宝贵的时间——因为在大多数数据库应用中，读请求是远远多余写请求的。如果您所在团队开发的应用会使MySQL数据库读写请求比例达到或查过1：1，那么使用Query Cache就没有什么意义，建议直接关闭。

4-2-1、Query Cache基本设置

您可以通过“show variables like ‘query_cache%’”语句查询当前为MySQL服务设定的和Query Cache相关的参数值。

# show variables like 'query_cache%';query_cache_limit                   1048576query_cache_min_res_unit            4096query_cache_size                    0query_cache_type                    OFFquery_cache_wlock_invalidate        OFF
1
2
3
4
5
6
7

这些设置参数的定义可以简单描述如下：

• query_cache_size：这是参数设置了MySQL服务中Query Cache的全局大小，单位为byte。该参数在MySQL version 5.5及以后版本中的默认值都为0，也就是说如果在这些版本中要使用Query Cache则需要自己设置Query Cache的大小。query_cache_size不应该这是太大（最大支持256M），这是因为当某张数据表进行写操作时，MySQL服务需要从Query Cache抹去的相关数据也就越多，反而会增加耗时。query_cache_size设置为33554432（32M）是比较好的。

• query_cache_limit：该参数设置单条查询语句允许缓存到Query Cache中的最大结果容量值，1048576（1M）是它的默认值。也就是说如果查询语句返回的查询结果集合大于1M，则这个查询结果集合不会缓存到Query Cache区域。

• query_cache_min_res_unit：该参数设置Query Cache每次分配内存的最小大小，默认值为4096（4KB）。

• query_cache_type：注意，既是单独设置query_cache_size为0，也不会使MySQL服务关闭Query Cache功能。一定要设置query_cache_type参数为0（OFF）才行。另外当该参数值为1（ON）时，代表开启Query Cache功能，此时必须在SQL查询语句中明确使用SQL_NO_CACHE，才能关闭这条查询语句的Query Cache功能；该参数的值还可以为2（DEMAND），此时只有当SQL查询语句明确使用SQL_CACHE关键字，才能让这条查询语句使用Query Cache功能。

• query_cache_wlock_invalidate：该参数设置Query Cache中数据的失效时刻（非常重要）。如果该值为1（ON），则在数据表被写锁定的同时该表中数据涉及的所有Query Cache都将失效；如果该值为0（OFF），则表示在数据表写锁定的同时，Query Cache中该数据表的相关数据都还继续有效。

您还可以通过“show status like ‘Qcache%’”语句查询当前MySQL服务中Query Cache的工作状态

# show status like 'Qcache%'Qcache_free_blocks          0Qcache_free_memory          0Qcache_hits                 0Qcache_inserts              0Qcache_lowmem_prunes        0Qcache_not_cached           0Qcache_queries_in_cache     0Qcache_total_blocks         0
1
2
3
4
5
6
7
8
9
10

各位读者看到以上示例中所有和Query Cache相关的状态值都为0，这是因为在演示的MySQL服务中默认关闭了Query Cache功能（主要是设置了query_cache_type的值为0）。不过以上展示的Query Cache状态信息中一些状态项还是要进行说明：

• Qcache_free_memory：该指标说明了当前Query Cache专用内存区域还有多少剩余内存。

• Qcache_hits：该指标说明当前Query Cache从MySQL服务启动到现在的命中次数。

• Qcache_lowmem_prunes：该指标说明因为Query Cache内存不足而被清除的查询结果数量。

其它的状态项可参见MySQL的官方文档《The MySQL Query Cache》

4-2-2、Query Cache的局限性和使用建议

为什么MySQL Version 5.5及以后的版本会默认关闭Query Cache功能呢？这至少说明官方并不建议在任何场景下都是用Query Cache功能，甚至是大多数场景下。首先，Query Cache存在功能局限性：

• 早期版本（Version 5.1）的Query Cache功能并不支持变量绑定，也就是说类似“select * from A where field = ?”这样的SQL查询结果不会被放入Query Cache中。

• 存储过程、触发器等基于数据库引擎类型工作的特定功能，如果其中使用了查询语句，这些查询语句的结果也不会放入Query Cache中。

• 复杂的SQL查询中，往往包含多个子句。这些子句的查询结果能够被放入Query Cache中。但是用于包含这些子句的外部查询结果却不能放入Query Cache。

以上提到的Query Cache功能局限性在每次MySQL版本升级的过程中，MySQL开发团队都逐渐进行了调整，所以这写功能性限制并不是什么太大的问题。例如以上说到的在存储过程中的SQL查询不会加入到Query Cache中，这个实际上就不是什么大问题，目前来看业务系统中业务逻辑处理部分还都是放在上层业务代码中来解决，使用复杂存储过来处理业务逻辑的情况不多见。MySQL官方默认关闭Query Cache主要还是因为Query Cache的性能局限性：

• “Waiting on query cache mutex”这种错误是典型的使用Query Cache不当所引起的错误。由于Query Cache设计的暴力清除策略，导致只要有数据表进行写操作，Query Cache中和这个数据表相关的所有结果都要失效的现象。所有需要从Query Cache中读取相关数据的客户端session就要等待数据清除完毕，所以就会出现以上错误提示。

• 如果这时query_cache_size设置得过大，反而会加剧这个问题的严重程度。因为过大的Query Cache区域意味着可能存储了和这个被写操作关联的数据表的更多查询结果集，也就需要更多时间去清除数据。

• 如果这张数据表又是写密集度非常高的数据表，那么这个问题会更加严重。因为Query Cache中相关数据会被频繁的擦除、重写。客户端session也会不停的进入锁定等待状态。

在实际业务应用中，笔者并不建议直接关闭Query Cache。而是建议在将query_cache_type参数设置为2（DEMAND）并分配不大的内存总空间（query_cache_size 设置为16MB足够了）的前提下，由业务层代码显式控制Query Cache的使用。

只有满足以下所有特点的SQL查询操作才建议显示开启Query Cache功能：写操作并不密集的数据表、读写操作比最好大于10：1（或者根据读者自己的业务特性规定的更大比值）。毕竟只有业务层才清楚哪些数据表的读写操作比大于10：1，并且写操作并不时常进行。而满足以上操作特性的数据表通常都是基础性码表：例如行政区域表、电话分区表、身份证分区表、车辆号牌表。

对于复杂的SQL查询、读写比不大的数据表、写操作频繁或者写操作并发特别大的数据表并不建议开启Query Cache功能。例如订单表、库存物品表、车辆承运表、评论信息表等业务写操作频繁的数据表。

来源：http://blog.csdn.net/yinwenjie（未经允许严禁用于商业用途！）