MySQL优化器

大家好，我是Bingo，一枚IT从业者，通过翻译MySQL8.0文档来学习MySQL。今天是我的第一篇文稿，请大家验收

8.1 MySQL优化器概述

数据库的性能取决于数据库本身的一些因素，例如表、查询和配置设置。这些软件结构会导致硬件级别的 CPU 和 I/O 操作，您必须尽可能减少这些操作来提高效率。提升数据库性能，首先要学习软件方面的高级规则和指南，并使用CPU挂钟时间（运行时间）来衡量性能。当您成为专家时，您会更多地关注内部发生的事情，并开始衡量诸如 CPU 时钟周期和 I/O 操作之类的东西。

大部分用户都想从现有的软件和硬件配置中获取最好的数据库性能。高级一些的工程师则寻找机会改进MySQL软件本身或者自研一套存储引擎和硬件设备来扩展MySQL生态系统。以下从三个方面来优化MySQL，为大家提供一些思路：

数据库层面优化

众所周知，数据库运行速度最重要的是其本身的基础设计：

1、表结构是否合理，字段是否符合标准规范，是否满足程序运行的基础单元。例如频繁更新的应哟个程序通常有很多表，较少的列，而分析大量数据的应用程序则有很少的表，很多的列。

2、是否正确使用了索引，来提高查询效率？

3、您是否为每个表的建立，考虑使用合适的存储引擎，充分考虑到每个存储引擎的特点和优势。忒特别是，事务存储引擎或非事务存储引擎，这对于性能和可扩展性非常重要。

4、您是否为每个表使用了适当的行格式，当然这取决于您所选择的存储引擎。比如，压缩表使用较少的磁盘空间，需要更少的磁盘I/O来读写数据。压缩表适用于所有InnoDB表的所有类型的工作负载，以及只读MyISAM表

5、应用程序是否使用了合适的锁策略。比如，尽可能的允许共享访问，以便可以提升读的性能，当然对请求的独占也是非常关键的。这一点同样取决于存储引擎的选择，InnoDB存储引擎自身可以处理大多数锁定问题，从而实现很好的并发性能。

6、用于缓存的所有内存区域的大小设置是否合理？也就是说，大到足以容纳经常访问的数据，但又不会大到使物理内存过载并导致分页。要配置的主要内存区域是 InnoDB 缓冲池和 MyISAM 密钥缓存。

硬件层面的优化

随着数据库的使用场景越来越丰富，任何数据库都最终会遇到硬件性能瓶颈。DBA必须评估是否可以调整应用程序和重新配置服务器已避免这谢瓶颈，或者增加足够的硬件资源。主要的硬件瓶颈来源于以下几点：

1、磁盘寻道。也就是磁盘找到一条数据所需要的时间。现在的磁盘，平均时间通常低于 10 毫秒，因此理论上我们每秒可以进行大约 100 次寻道。这个时间随着新磁盘的增加而缓慢改善，并且很难针对单个表进行优化。优化寻道时间的方法是将数据分布到多个磁盘上。

2、磁盘读写。当磁盘在正确的位置时，我们需要读取或写入数据。现在的磁盘，一个磁盘可提供至少 10–20MB/s 的吞吐量。这比查找更容易优化，因为您可以从多个磁盘并行读取。

3、CPU 时钟周期。当数据在主存中时，我们必须对其进行处理以获得我们的结果。与内存相比，拥有一张大表是最常见的限制因素。但是对于小表，速度通常不是问题。

4、内存带宽。当 CPU 需要的数据超出 CPU 高速缓存的容量时，主内存带宽就会成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但还是需要注意。

平衡可移植性和性能

1、要在可移植的 MySQL 程序中使用面向性能的 SQL 扩展，您可以将 MySQL 特定的关键字包装在 /*! */ 中。其他 SQL 服务器忽略注释的关键字。

翻译：MySQL :: MySQL 8.0 Reference Manual :: 8.1 Optimization Overviewhttps://dev.mysql.com/doc/refman/8.0/en/optimize-overview.html