mysql5.7-8.1 Optimization Overview(优化概述)

Database performance depends on several factors at the database level, such as tables, queries, and configuration setting.

数据库性能取决于数据库级别上的几个因素，如表、查询和配置设置。

These software constructs result in CPU and I/O operations at the hardware level,which you must minimize and make as efficient as possible.

这些软件构造导致硬件级别的CPU和I/O操作，您必须尽量减少这些操作并使其尽可能高效。

As you work on database performance, you start by learning the high-level rules and guidelines for the software side, and measuring performance using wall-clock time.

在处理数据库性能时，首先要学习软件方面的高级规则和指导方针，并使用时钟时间度量性能。(wall-clock times：时钟时间又称为墙上时钟时间（ wall clock time）。它是进程运行的时间总量，其值与系统中同时运行的进程数有关。在我们报告时钟时间时，都是在系统中没有其他活动时进行度量的。)

As you become an expert, you learn more about what happens internally, and start measuring things such al CPU cycles and I/O ecosystem.

当您成为这方面的专家时，您将更多地了解内部发生了什么，并开始度量诸如al CPU周期和I/O生态系统之类的东西。

Typical users aim to get the best database performance out of their existing software and hardware configurations.

典型用户的目标是从现有的软件和硬件配置中获得最佳的数据库性能。

Advanced users look for opportunities to improve the MySQL software itself, or develop their owm storage engines and hardware appliances to expand the MySQL system.

高级用户寻找机会来改进MySQL软件本身，或者开发自己的存储引擎和硬件设备来扩展MySQL系统。

Optimizing at the Database Level

 在数据库层面优化

The most important factor in making a database application fast is its basic design:

使数据库应用程序快速运行的最重要因素是它的基本设计:

Are the tables structured properly? 

表的结构是否正确?

In particular, do the columns have the right data types, and does each table have the appropriate columns for the type of work? 

具体来说，列是否具有正确的数据类型，以及每个表是否具有对应工作类型的适当列?

For example, applications that perform frequent updates often have many tables with few columns, while applications that analyze large amounts of data often have few tables with many columns.

例如，执行频繁更新的应用程序通常有很多表，但列很少，而分析大量数据的应用程序通常只有几个表，但列很多。

Are the right indexes in place to make queries efficient

是否有合适的索引来提高查询的效率

Are you using the appropriate storage engine for each table, and taking advantage of the strengths and features of each storage engine you use?

您是否为每个表使用了合适的存储引擎，并充分利用了所使用的每个存储引擎的优点和特性?

In particular, the choice of a transactional storage engine such as InnoDB or a nontransactional one such as MyISAM can be very important for performance and scalability.

特别是，选择事务性存储引擎(如InnoDB)或非事务性存储引擎(如MyISAM)对性能和可扩展性非常重要。

InnoDB is the default storage engine for new tables. In practice, the advanced InnoDB performance features mean that InnoDB tables often outperform the simpler MyISAM tables, especially for a busy database.

InnoDB是新表的默认存储引擎。在实践中，InnoDB的高级性能特性意味着InnoDB表的性能通常优于简单的MyISAM表，特别是对于繁忙的数据库。

Does each table use an appropriate row format? This choice also depends on the storage engine used for the table.

每个表是否使用适当的（row format）?这种选择还取决于表使用的存储引擎。

In particular, compressed tables use less disk space and so require less disk I/O to read and write the data. Compression is available for all kinds of workloads with InnoDB tables, and for read-only MyISAM tables.

特别是，压缩表使用更少的磁盘空间，因此读写数据时需要更少的磁盘I/O。压缩适用于所有InnoDB表和只读MyISAM表的工作负载。

Does the application use an appropriate locking strategy? 

应用程序是否使用适当的锁策略?

For example, by allowing shared access when possible so that database operations can run concurrently, and requesting exclusive access when appropriate so that critical operations get top priority.

例如，在可能的情况下允许共享访问，以便数据库操作可以并发运行，并在适当的情况下请求独占访问，以便关键操作获得最高优先级

Again, the choice of storage engine is significant.

同样，存储引擎的选择也很重要

The InnoDB storage engine handles most locking issues without involvement from you, allowing for better concurrency in the database and reducing the amount of experimentation and tuning for your code.

InnoDB存储引擎可以处理大多数锁定问题，而无需您的参与，从而实现更好的数据库并发性，并减少了代码的试验和调优。

Are all memory areas used for caching sized correctly? 

是否所有的内存使用的区域用于正确的缓存大小呢？

That is, large enough to hold frequently accessed data, but not so large that they overload physical memory and cause paging. The main memory areas to configure are the InnoDB buffer pool, the MyISAM key cache, and the MySQL query cache.

也就是说，要大到足以容纳频繁访问的数据，但又不能大到超载物理内存并导致分页。需要配置的主要内存区域是InnoDB缓冲池、MyISAM密钥缓存和MySQL查询缓存。

Optimizing at the Hardware Level

硬件层面的优化

Any database application eventually hits hardware limits as the database becomes more and more busy.

随着数据库变得越来越忙，任何数据库应用程序最终都会达到硬件限制。

A DBA must evaluate whether it is possible to tune the application or reconfigure the server to avoid these bottlenecks, or whether more hardware resources are required. System bottlenecks typically arise from these sources:

DBA必须评估是否可以调优应用程序或重新配置服务器以避免这些瓶颈，或者是否需要更多的硬件资源。系统瓶颈通常来自以下来源:

Disk seeks. It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk.

磁盘寻址。磁盘找到一段数据需要时间。对于现代磁盘，这一过程的平均时间通常低于10毫秒，所以理论上我们可以每秒进行100次搜索。这一次使用新磁盘改进缓慢，并且很难针对单个表进行优化。优化寻道时间的方法是将数据分布到多个磁盘上。

Disk reading and writing. When the disk is at the correct position, we need to read or write the data. With modern disks, one disk delivers at least 10–20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks.

磁盘读写。当磁盘处于正确位置时，需要对数据进行读写操作。一个磁盘提供至少10-20MB/s的吞吐量。这比寻优更容易，因为可以从多个磁盘并行读取数据。

CPU cycles. When the data is in main memory, we must process it to get our result. Having large tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem

CPU 周期。当数据在主存储器中时，我们必须对它进行处理以得到我们的结果。与内存数量相比，使用大表是最常见的限制因素。但是使用小表, 通常不是问题。

Balancing Portability and Performance

平衡可移植性和性能

To use performance-oriented SQL extensions in a portable MySQL program, you can wrap MySQL-specific keywords in a statement within /*! */ comment delimiters.

要在可移植的MySQL程序中使用面向性能的SQL扩展，可以将MySQL特定的关键字封装在/*!* /注释分隔符。

Other SQL servers ignore the commented keywords. For information about writing comments, see Section 9.6, “Comments”.

其他SQL服务器忽略注释的关键字。有关编写注释的信息，请参见第9.6节“注释”。