从业务应用开发者的角度优化MySQL性能

MySQL的性能优化包括操作系统和硬件优化、MySQL服务配置优化、合理的表结构设计和索引设计、查询优化等等，其中业务应用开发者能做的主要在表结构、索引、查询三个方面，以下对这三个方面的优化思路做简单介绍。内容主要参考《高性能MySQL（第四版）》第六、七、八章，如无特别说明，以下优化内容主要针对InnoDB引擎和B-Tree索引。

表定义

选择优化的数据类型

简单够用，尽量避免NULL值。

• 更小的通常更好：更小的数据类型占用的磁盘、内存和CPU缓存的空间更少，处理时需要的CPU周期也更少。但也要确保没有低估要存储的值的范围。

• 简单为好：简单数据类型的操作通常需要的CPU周期更少。

• 尽量避免存储NULL：可为NULL的列使得索引、索引统计和值比较都更复杂，对MySQL来说更难优化。可为NULL的列会使用更多的存储空间。

表结构设计的陷阱

• 太多的列：非常宽的表CPU消耗非常严重

MySQL的存储引擎API通过在服务器和存储引擎之间以行缓存区格式复制行来工作；服务器将缓存区解码为列。将行缓存区转换为有解码列的行数据结构的操作代价非常高。InnoDB的行格式总是需要转换。这种转换的成本取决于列数。

• 太多的联接：如果需要以高并发性快速执行查询，每个查询最好少于十几张表。

• 谨慎但不要惧怕使用NULL：可以使用0、特殊值或空字符串作为空值代替NULL；但当使用NULL比其他替代方案更简单时，使用NULL也许会更好。

索引设计

三星索引

索引将相关的记录放到一起则获得“一星”；索引中的数据顺序和查找中的排序顺序一致则获得“二星”；索引中的列包含了查询中需要的全部列则获得“三星”。

高性能的索引策略

索引的选择性

索引的选择性是指，不重复的索引值/数据表的记录总数（＃T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高。唯一索引的选择性是1。

前缀索引和索引的选择性

有时为了提升索引的性能同时节省索引空间，可以只对字段的前一部分字符进行索引，这样做的缺点是，会降低索引的选择性，并且无法使用覆盖索引的特性。

多列索引设计

设计索引的列顺序有一个重要的经验法则：将选择性最高的列放到索引最前。

为了借助索引完成排序和分组，就需要根据排序和分组的需求设计索引。

多列索引也需要兼顾使用其中的某一列筛选数据的情况，要注意最左匹配的要求。

覆盖索引

如果可以使用索引直接获取列的数据而不再需要读取数据行，那么这个索引就是覆盖索引。

利用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。

ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL需要执行排序操作，而无法利用索引排序。

删除重复索引和冗余索引

重复索引：指在相同的列上按照相同顺序创建的相同类型的索引。

冗余索引：冗余索引和重复索引有一些不同。如果创建了索引(A，B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引，因此，索引(A，B)也可以当作索引(A)来使用（这种冗余只是对B-tree索引来说的）。

重复索引和冗余索引的危害：MySQL要单独维护每个索引，包括重复和冗余索引，并且优化器在优化查询的时候也需要逐个地进行评估，这会影响性能，同时也浪费磁盘空间。

删除未使用的索引

在sys数据库中，在table_io_waits_summary_by_index_usage视图中可以查询到哪些索引从来没有被使用过。

更新索引统计信息

如果存储引擎向优化器提供的扫描行数信息不准确，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL的优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息，以解决这个问题。

查询设计

性能优化思路：如果把查询看作一个任务，那么这个任务由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，要么消除其中的一些子任务，要么减少子任务的执行次数，要么让子任务执行的更快。

查询的生命周期

从客户端到服务器，然后在服务器上进行语法解析，生成执行计划，执行，并返回给客户端。其中“执行”是整个生命周期中最重要的阶段，这其中包括大量为了检索数据对存储引擎的调用以及调用后的数据处理，包括排序、分组等。

优化数据访问

是否向数据库请求了不需要的数据

• 查询了不需要的记录：比如分页查询中仅需要10条，却请求了100条数据

• 多表联接时返回全部列，正确的做法是用到多少列返回多少列

• 总是取出全部列，这种做法有好有坏：

  • 好处是SQL可能会复用

  • 坏处是无法使用覆盖索引，为服务器带来额外的I/O、内存、CPU消耗

• 重复查询相同的数据。解决方式是把查询结果缓存起来

MySQL是否在扫描额外的记录

扫描行数和返回行数

分析MySQL在执行查询时扫描的行数和返回的记录数，在一定程度上能够评估该查询的效率高不高。理想情况下，扫描的行数和返回的记录数应该是相同的。

扫描的行数和访问类型

EXPLAIN语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。以上类型速度从慢到快，扫描的行数从多到少。

重构查询方式

复杂查询拆分

考虑将一个复杂的查询分成多个简单的查询

分解联接查询

将联表查询拆分为单表查询

查询优化器的优化类型

了解查询优化器的优化类型，有助于写出对优化器更友好的语句，以下是常见的优化类型：

• 重新定义联接表的顺序

• 将外联接转化成内联接：有些外联接可以被转化为内联接，然后再通过调整内联接上各个表的联接顺序

• 使用代数等价变换规则：MySQL使用一些代数等价变换规则来简化并规范表达式。例如，(5=5 AND a>5)将被改写为a>5。

• 优化COUNT()、MIN()、MAX()：如果某一列在B-tree索引上，那么在计算该列的最小值时，可以直接取索引最左端的记录。MAX()类似。

• 索引覆盖扫描：直接使用索引返回需要查询的列数据。

• 提前终止查询：在发现已经满足查询需求的时候，MySQL总是能立刻终止查询

• 列表IN()的比较：MySQL中IN()查询比OR查询效率更高。MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式确定列表中的值是否满足条件，这是一个O(logn)复杂度的查询；而OR查询则是O(n)的复杂度。当IN()列表中有大量取值时，处理速度的提升会更明显

特定类型的优化

优化联接查询

• 确保ON或者USING子句中的列上有索引。在创建索引的时候需要考虑联接的顺序。当表A和表B用列c联接的时候，如果优化器的联接顺序是B、A，那么就不需要在B表的对应列上建索引。

• 确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

优化分页查询

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列。在偏移量很大的时候，这样做的效率会有非常大的提升。

优化UNION查询

• 子句下推：MySQL通过创建并填充临时表的方式执行UNION查询，因此很多在UNION结果的基础上执行的WHERE、LIMIT、ORDER BY子句无法做优化。可以将UNION结果后的WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器利用这些条件进行优化。

• 使用UNION ALL代替UNION：除非明确需要在MySQL服务器上消除重复的行，否则一定要使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。