sec 8.1 mysql优化

主要翻译记录https://dev.mysql.com/doc/refman/8.0/en/optimization.html

This chapter explains how to optimize MySQL performance and provides examples. Optimization involves configuring, tuning, and measuring performance, at several levels. Depending on your job role (developer, DBA, or a combination of both), you might optimize at the level of individual SQL statements, entire applications, a single database server, or multiple networked database servers. Sometimes you can be proactive and plan in advance for performance, while other times you might troubleshoot a configuration or code issue after a problem occurs. Optimizing CPU and memory usage can also improve scalability, allowing the database to handle more load without slowing down.

 本章解释如何优化mysql的性能，并提供了例子。优化涉及了在各个层次配置、调试、和测试性能。对于不同的工作角色（开发者、数据库管理员），你可能需要从单独的sql声明语句、整个应用、单个数据库服务器或多个联网服务器等层次进行优化。有时可能是积极主动地提前计划以提升性能，也可能是在当问题发生后检查配置或代码中的故障。优化CPU和内存使用也可以提升可扩展性，使得数据库能够处理更大的负载并且不会降低处理速度。

 

 

8.1优化概览

Database performance depends on several factors at the database level, such as tables, queries, and configuration settings. These software constructs result in CPU and I/O operations at the hardware level, which you must minimize and make as efficient as possible. 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. As you become an expert, you learn more about what happens internally, and start measuring things such as CPU cycles and I/O operations.

 

从数据库的层次来讲，优化依赖多个因素，比如表、查询语句、配置设置信息。这些软件构成会造成硬件层面上的CPU和I/O操作，而CPU和I/O操作是你必须要最小化以尽可能提升性能。面对数据库性能问题时，新手会从软件层面的高层次的规则和指南入手，并且通过墙上时钟时间来衡量性能（https://blog.csdn.net/xingchenxuanfeng/article/details/73549506）。而成为专家需要知道其内部发生了什么，并开始使用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 own storage engines and hardware appliances to expand the MySQL ecosystem.

 

典型的使用者目标是在已有的软硬件配置上实现数据库的最佳性能。高级使用者寻找机会来提升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可能对于性能和可扩展性非常重要。
• Does each table use an appropriate row format? This choice also depends on the storage engine used for the table. 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.
• 每张表是否采用了合适的行格式？这个选择也依赖于表采用的存储引擎。特别是，compressed表使用的硬盘空间较少，所以需要的磁盘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 and the MyISAM key cache.
• 用于缓存的所有内存区域大小是否正确？也就是说，足够大以容纳经常访问的数据，但又不能太大以至于它们会使物理内存过载并导致分页。要配置的主要内存区域是InnoDB缓冲池和MyISAM key cache。

　　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毫秒，因此理论上我们可以在1秒内执行约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周期。当数据位于主存储器中时，我们必须对其进行处理以获得结果。与内存量相比，在拥有表规模较大时，内存是更常见的限制因素。但是对于小规模的表，速度通常不是问题。
• Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one to be aware of.
• 内存带宽。当CPU需要的数据超出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. Other SQL servers ignore the commented keywords. For information about writing comments, see Section 9.6, “Comment Syntax”.

　　要在可移植的MySQL程序中使用面向性能的SQL扩展，可以在/ *！中的语句中包装特定于MySQL的关键字。 * /注释定界符。其他SQL Server忽略注释的关键字。有关编写注释的信息，请参见第9.6节“注释语法”。