優(yōu)化概述

數(shù)據(jù)庫性能表現(xiàn)依賴于數(shù)據(jù)庫級別的幾個因素，比如表，查詢和配置設(shè)置。這些軟件在硬件級別通過CPU和IO操作構(gòu)筑結(jié)果，你需要盡可能的使用最少的資源達(dá)到最大的效果。當(dāng)你專注于數(shù)據(jù)庫的性能表現(xiàn)時，你應(yīng)該學(xué)習(xí)數(shù)據(jù)庫的高級規(guī)則和指南，然后以實際執(zhí)行時間來度量數(shù)據(jù)庫性能。你想成為專家的話，你應(yīng)該進(jìn)一步學(xué)習(xí)在MySQL內(nèi)部都發(fā)生了什么，并且開始以CPU周期和IO操作來度量數(shù)據(jù)庫的性能了。

大多數(shù)用戶都想方設(shè)法的通過現(xiàn)有的軟件和硬件配置來或者數(shù)據(jù)庫的最好性能，再高級點的用戶則嘗試找機(jī)會去改進(jìn)MySQL軟件本身，或者開發(fā)它們自己的存儲引擎和硬件設(shè)施以此來擴(kuò)展MySQL系統(tǒng)。

*)在數(shù)據(jù)庫級別優(yōu)化

*)在硬件級別優(yōu)化

*)平衡移植和性能

一、在數(shù)據(jù)庫級別優(yōu)化

讓數(shù)據(jù)庫運行更快的最重要的因素就在它的基本設(shè)計之中：

1)表結(jié)構(gòu)是否設(shè)計合理？特別是表子段被設(shè)計成正確的數(shù)據(jù)類型，還有每個表是否都有合適的類型的子段？比如，應(yīng)用程序經(jīng)常處理頻繁的更新，但是卻要更新多張子段很少的表。再如應(yīng)用程序要解析大量的數(shù)據(jù)，但是這些數(shù)據(jù)通常只分布在很少的表中，但表的字段卻很多。

2)索引設(shè)置是否得當(dāng)，它有沒有起到應(yīng)有的效果？

3)你是否為每個表都設(shè)置了合適的存儲引擎，并且充分利用了這個存儲引擎的優(yōu)勢和特點？特別是，選擇事務(wù)型存儲引擎例如InnoDB或者非事務(wù)型存儲引擎MyISAM可能對性能和可擴(kuò)展性非常重要。

注意：

InnoDB是你創(chuàng)建新表時的默認(rèn)引擎。實際上，改進(jìn)型的InnoDB的表現(xiàn)特性意味著InnoDB表通常要比與之類似的MyISAM表性能要好，尤其是那種業(yè)務(wù)量大的數(shù)據(jù)庫。

4)你是否為每個表都使用了合適的行格式？這個選擇同樣依賴于你為數(shù)據(jù)表選擇的存儲引擎。特別是，壓縮表以使數(shù)據(jù)用的磁盤空間更少這樣就可以用更少的I/O去讀寫數(shù)據(jù)。壓縮功能對于任何類型的InnoDB表和MyISAM表都是可用的。

5)應(yīng)用是否使用了合適的鎖機(jī)制？例如，通過允許共享訪問來實現(xiàn)數(shù)據(jù)庫的并發(fā)訪問，同時在需要執(zhí)行排它操作時請求一個獨立訪問。再次印證，對于存儲引擎的選擇意義重大。InnoDB存儲引擎處理大多數(shù)加鎖問題而不需要你的參與，實現(xiàn)了更好的并發(fā)并且減少了你的代碼為了提高性能而進(jìn)行的大量的試驗和嘗試。

6)是否所有的內(nèi)存區(qū)域都應(yīng)用了正確的緩存大小。通俗的講，是否足夠大以應(yīng)對頻繁的數(shù)據(jù)訪問，但又不大到超過物理內(nèi)存并引起排序。最主要的內(nèi)存區(qū)域配置是InnoDB緩存池，MyISAM的健緩存，還有MySQL的查詢緩存。

二、在硬件層面優(yōu)化

當(dāng)數(shù)據(jù)庫負(fù)載越來越大的時候，任何數(shù)據(jù)庫應(yīng)用最終都會被硬件所限制。作為一個DBA應(yīng)該評估一下是否可能通過優(yōu)化應(yīng)用或者重新配置服務(wù)器去規(guī)避這些瓶頸，或者擴(kuò)展更多的硬件。系統(tǒng)瓶頸通常來源于以下幾個原因：

1)硬盤尋址。想找到硬盤上的數(shù)據(jù)是要花時間的。現(xiàn)代硬盤，這種尋址一般少于10毫秒，因此我們可以以每秒100次尋址來統(tǒng)計。這種耗時在新硬盤上提高緩慢并且對于單表來說比較難優(yōu)化。優(yōu)化尋址時間可以通過把數(shù)據(jù)放到不同硬盤上來進(jìn)行。

2)硬盤讀寫。當(dāng)硬盤在正確的位置時，我們需要讀寫數(shù)據(jù)。現(xiàn)代硬盤，一快硬盤讀取速率至少在10-20兆每秒。這塊很好優(yōu)化，因為你可以同時從不同的硬盤去讀取數(shù)據(jù)。

3)CPU周期。當(dāng)數(shù)據(jù)在主存種，我們必須處理它以得到我們想要的結(jié)果。讀取大表相比較于大量的內(nèi)存而言是通常限制的因素。但是小表速度通常不是問題。

4)內(nèi)存帶寬。當(dāng)CPU超過它能配置在CPU緩存里的數(shù)據(jù)時，主存帶寬就成為了一個瓶頸。這并不是大多數(shù)系統(tǒng)所面臨的常見瓶頸，但它應(yīng)該引起我們的注意。

三、平衡移植和性能

在一個可移植的MySQL項目中使用性能目的類型的SQL擴(kuò)展，你可以用Mysql特有的關(guān)鍵詞 /*！ */來作為注釋標(biāo)識。其他的SQL服務(wù)器忽略這個關(guān)鍵詞。

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

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

總結(jié)

以上是生活随笔為你收集整理的mysql数据库英文句子翻译_MySQL文档翻译（八）附英文原文---性能优化概览的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。