优化概述

数据库性能表现依赖于数据库级别的几个因素，比如表，查询和配置设置。这些软件在硬件级别通过CPU和IO操作构筑结果，你需要尽可能的使用最少的资源达到最大的效果。当你专注于数据库的性能表现时，你应该学习数据库的高级规则和指南，然后以实际执行时间来度量数据库性能。你想成为专家的话，你应该进一步学习在MySQL内部都发生了什么，并且开始以CPU周期和IO操作来度量数据库的性能了。

大多数用户都想方设法的通过现有的软件和硬件配置来或者数据库的最好性能，再高级点的用户则尝试找机会去改进MySQL软件本身，或者开发它们自己的存储引擎和硬件设施以此来扩展MySQL系统。

*）在数据库级别优化

*）在硬件级别优化

*）平衡移植和性能

一、在数据库级别优化

让数据库运行更快的最重要的因素就在它的基本设计之中：

1）表结构是否设计合理？特别是表子段被设计成正确的数据类型，还有每个表是否都有合适的类型的子段？比如，应用程序经常处理频繁的更新，但是却要更新多张子段很少的表。再如应用程序要解析大量的数据，但是这些数据通常只分布在很少的表中，但表的字段却很多。

2）索引设置是否得当，它有没有起到应有的效果？

3）你是否为每个表都设置了合适的存储引擎，并且充分利用了这个存储引擎的优势和特点？特别是，选择事务型存储引擎例如InnoDB或者非事务型存储引擎MyISAM可能对性能和可扩展性非常重要。

注意：

InnoDB是你创建新表时的默认引擎。实际上，改进型的InnoDB的表现特性意味着InnoDB表通常要比与之类似的MyISAM表性能要好，尤其是那种业务量大的数据库。

4）你是否为每个表都使用了合适的行格式？这个选择同样依赖于你为数据表选择的存储引擎。特别是，压缩表以使数据用的磁盘空间更少这样就可以用更少的I/O去读写数据。压缩功能对于任何类型的InnoDB表和MyISAM表都是可用的。

5）应用是否使用了合适的锁机制？例如，通过允许共享访问来实现数据库的并发访问，同时在需要执行排它操作时请求一个独立访问。再次印证，对于存储引擎的选择意义重大。InnoDB存储引擎处理大多数加锁问题而不需要你的参与，实现了更好的并发并且减少了你的代码为了提高性能而进行的大量的试验和尝试。

6）是否所有的内存区域都应用了正确的缓存大小。通俗的讲，是否足够大以应对频繁的数据访问，但又不大到超过物理内存并引起排序。最主要的内存区域配置是InnoDB缓存池，MyISAM的健缓存，还有MySQL的查询缓存。

二、在硬件层面优化

当数据库负载越来越大的时候，任何数据库应用最终都会被硬件所限制。作为一个DBA应该评估一下是否可能通过优化应用或者重新配置服务器去规避这些瓶颈，或者扩展更多的硬件。系统瓶颈通常来源于以下几个原因：

1）硬盘寻址。想找到硬盘上的数据是要花时间的。现代硬盘，这种寻址一般少于10毫秒，因此我们可以以每秒100次寻址来统计。这种耗时在新硬盘上提高缓慢并且对于单表来说比较难优化。优化寻址时间可以通过把数据放到不同硬盘上来进行。

2）硬盘读写。当硬盘在正确的位置时，我们需要读写数据。现代硬盘，一快硬盘读取速率至少在10-20兆每秒。这块很好优化，因为你可以同时从不同的硬盘去读取数据。

3）CPU周期。当数据在主存种，我们必须处理它以得到我们想要的结果。读取大表相比较于大量的内存而言是通常限制的因素。但是小表速度通常不是问题。

4）内存带宽。当CPU超过它能配置在CPU缓存里的数据时，主存带宽就成为了一个瓶颈。这并不是大多数系统所面临的常见瓶颈，但它应该引起我们的注意。

三、平衡移植和性能

在一个可移植的MySQL项目中使用性能目的类型的SQL扩展，你可以用Mysql特有的关键词 /*！ */来作为注释标识。其他的SQL服务器忽略这个关键词。

-------------------------------------英文原文---------------------------------------

Optimization Overview

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.

Optimizing at the Database Level

Optimizing at the Hardware Level

Balancing Portability and Performance

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.

Note

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.

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.

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.

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.

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:

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.

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”.