数据库性能取决于数据库级别的多个因素，例如表、查询和配置设置。这些软件结构会导致硬件级别的 CPU 和 I/O 操作，您必须将其最小化并尽可能提高效率。在处理数据库性能时，您首先要学习软件方面的高级规则和指南，并使用挂钟时间测量性能。当您成为专家时，您会更多地了解内部发生的事情，并开始衡量诸如 CPU 周期和 I/O 操作之类的事情。
典型用户的目标是从他们现有的软件和硬件配置中获得最佳的数据库性能。高级用户寻找机会改进 MySQL 软件本身，或开发自己的存储引擎和硬件设备以扩展 MySQL 生态系统。
使数据库应用程序快速运行的最重要因素是其基本设计：
表的结构是否正确？特别是，列是否具有正确的数据类型，每个表是否具有适合工作类型的列？例如，执行频繁更新的应用程序通常有很多表和很少的列，而分析大量数据的应用程序通常有很少的表和很多列。
是否有适当的 索引来提高查询效率？
您是否为每个表使用了适当的存储引擎，并利用了您使用的每个存储引擎的优势和特性？特别是，选择事务存储引擎（例如 ）
InnoDB或非 事务存储引擎（例如 ）MyISAM对于性能和可伸缩性非常重要。笔记InnoDB是新表的默认存储引擎。在实践中，高级InnoDB性能特性意味着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.
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.
内存带宽。当 CPU 需要的数据多于 CPU 高速缓存所能容纳的数据时，主存带宽就成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。
要在可移植的 MySQL 程序中使用面向性能的 SQL 扩展，您可以将特定于 MySQL 的关键字包装在/*! */注释分隔符内的语句中。其他 SQL 服务器忽略注释的关键字。有关编写评论的信息，请参阅第 9.6 节，“评论”。