[TOC]
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.
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.
决定数据库性能的关键因素，包括表结构，查询和配置。 这些软件层面的结构和配置，又会在硬件层面导致CPU开销和 I/O 操作，我们必须让这些操作最小化并使其尽可能地高效。 在研究数据库性能时，首先要学习软件方面的高级规则和基本准则，并使用时钟时间来衡量性能。 成为专家后，则需要了解更多的内部实现原理，并度量诸如CPU周期和I/O操作之类的东西。
普通用户调优的目的，是希望通过现有的软件和硬件设施，获得最佳的数据库性能。 高级用户则有能力改进MySQL软件本身的性能，甚至开发自己的存储引擎和硬件设备，完善MySQL生态系统。
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
InnoDBor a nontransactional one such asMyISAMcan be very important for performance and scalability.
基于数据库的应用系统，影响性能的主要因素包括以下部分：
-
表结构设计合理：
- 各个列的数据类型是否合理
- 每个表中的列设计是否合理
- 例如，数据频繁变更的系统, 应该设计为许多表，每个表的列数量应该限制在一定范围内
- 而需要进行大量数据分析的系统，通常使用宽表： 即表数量很少，而每个表有很多列。
-
设置必要的索引: 提高查询效率
-
数据表选择合适的存储引擎:
- 是否充分利用了存储引擎的优势和特性？
- 有些情况下，对于性能和可伸缩性而言，选择事务存储引擎（如InnoDB）或非事务存储引擎（如MyISAM）的区别非常明显。
Note
InnoDBis the default storage engine for new tables. In practice, the advancedInnoDBperformance features mean thatInnoDBtables often outperform the simplerMyISAMtables, especially for a busy database.
提示:
InnoDB目前是MySQL默认的存储引擎。 实际上，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
InnoDBtables, and for read-onlyMyISAMtables. -
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
InnoDBstorage 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. -
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
InnoDBbuffer pool, theMyISAMkey cache, and the MySQL query cache. -
表结构设置了适当的行格式(row format). 该选择还取决于使用的存储引擎。 特别是，压缩表使用较少的磁盘空间，因此读取和写入数据时需要的磁盘 I/O 相对较少。压缩表支持所有类型的 InnoDB 表, 以及只读的MyISAM表。
-
应用程序需要指定适当的 锁定策略。 例如， 在可能的情况下允许共享访问，以便数据库操作可以并发进行，并在必要的时候请求资源独占访问， 以使关键操作获得最高优先级。 同样，存储引擎的选择也很重要。
InnoDB存储引擎可以处理大部分的锁定问题，而无需用户特别指定，从而更好地支持并发，降低需要的代码重试和调优。 -
正确设置相关 内存缓存区的大小。 也就是说，缓冲区的大小要足以容纳经常访问的数据，但又不能太大，以免发生虚拟内存交换。 最主要的内存区域配置是:
InnoDB缓冲池（buffer pool），MyISAMkey cache 以及 MySQL 查询缓存。
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:
- 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.
- 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.
- 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.
- 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.
随着数据库越来越繁忙，任何数据库软件最终都会达到硬件的性能极限。 DBA必须评估能否调优应用程序，或者重新配置服务器，以避免 性能瓶颈，是否需要更多的硬件资源。 系统瓶颈通常包括：
- 磁盘检索。 从磁盘中查找数据通常需要花费不少时间。 对于现在的磁盘，此操作的平均时间通常小于10毫秒，因此理论上1秒钟可以执行100次搜索。 随着新型磁盘的使用，这个时间有了缓慢的改善，并且很难为单个表进行优化。 优化寻道时间的方法是将数据存储到多个磁盘上。
- 磁盘读写。当磁头位于正确的位置时，则需要读取或写入数据。 现在单个磁盘可提供至少 10–20MB/s 的数据吞吐量。 与查找相比，读写优化起来更容易，因为我们可以从多个磁盘来并行读取。
- CPU周期。当数据位于主内存中时，必须先对其进行处理才能获得结果。 与内存容量相比，大表中的数据量是最常见的限制因素。但对于小表来说，速度一般就不是问题瓶颈。
- 内存带宽。当需要处理超出 CPU cache 容量的数据时，主内存的带宽就可能会成为瓶颈。 对于大多数系统来说，这是一个不常见的瓶颈，但也要意识到这一点。
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关键字。
其他的数据库服务器会忽略注释的这些关键字。 有关注释信息的编写，请参见 第9.6节“注释语法”。