1.優化查詢的方法

1.1使用索引

應盡量避免全表掃描，首先應考慮在 where 及 order by 、group by 涉及的列上建立索引。

1.1.1 為出現在where字句的字段建立一個索引

SELECT category_name FROM mytable WHERE category_id=1;

為category_id建立一個索引：

CREATE INDEX mytable_categoryid ON mytable(category_id);

若語句為：

SELECT category_name FROM mytable WHERE category_id=1 AND user_id=2;

若再給user_id建立一個索引，這不是最佳方法。可建立多重索引：

CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

命名方式：表名—字段1名—字段2名

1.1.2 為order by子句中的字段建立一個索引

SELECT category_name FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC;

創建索引：

CREATE INDEX mytable_categoryid_useris_adddate ON mytable(category_id,user_id,adddate);

注意：mytable_categoryid_uerid_adddate將會被截斷為：mytable_categoryid_uerid_addda

1.1.3 索引機制

本質：通過不斷地縮小想要獲取數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們可以總是用同一種查找方式來鎖定數據。

1.2優化SQL語句

1.2.1 通過explain來查看SQL語句執行效果

將explain移到前面，查看執行計劃；

可幫助選擇更好的索引和優化查詢語句，寫出更好的優化語句。

通常我們可以對比較復雜的尤其是涉及到多表的 SELECT 語句， 把關鍵字 EXPLAIN 加到前面， 查看執行計劃。

• 例如： explain select name from news;

1.2.2?任何地方都不要使用 select * from t

用具體的字段列表代替“*” ， 不要返回用不到的任何字段.

1.2.3 不在索引列做運算或者使用函數。

1.2.4?使用 limit

查詢盡可能使用 limit 減少返回的行數， 減少數據傳輸時間和帶寬浪費。

1.3 優化數據庫對象

1.3.1?優化表的數據類型

使用 procedure analyse()函數對表進行分析， 該函數可以對表中列的數據類型提出優化建議。

能小就用小。

表數據類型第一個原則是： 使用能正確的表示和存儲數據的最短類型。 這樣可以減少對磁盤空間、 內存、 cpu 緩存的使用。

使用方法： select * from 表名 procedure analyse();

1.3.2?對表進行拆分

通過拆分表可以提高表的訪問效率。

有 2 種拆分方法：

垂直拆分：把主鍵和一些列放在一個表中， 然后把主鍵和另外的列放在另一個表中。 如果一個表中某些列常用， 而另外一些不常用， 則可以采用垂直拆分。

水平拆分：根據一列或者多列數據的值把數據行放到二個獨立的表中。

1.3.3?使用中間表來提高查詢速度

創建中間表， 表結構和源表結構完全相同， 轉移要統計的數據到中間表， 然后在中間表上進行統計， 得出想要的結果。

1.4 硬件優化

1.4.1 CPU 的優化

選擇多核和主頻高的 CPU。

1.4.2 內存的優化使用更大的內存。

將盡量多的內存分配給 MYSQL 做緩存。

1.4.3 磁盤 I/O 的優化

1.使用磁盤陣列

RAID 0 沒有數據冗余， 沒有數據校驗的磁盤陳列。 實現 RAID 0至少需要兩塊以上的硬盤， 它將兩塊以上的硬盤合并成一塊， 數據
連續地分割在每塊盤上。

RAID1 是將一個兩塊硬盤所構成 RAID 磁盤陣列， 其容量僅等于一塊硬盤的容量， 因為另一塊只是當作數據“鏡像”。

使用 RAID-0+1 磁盤陣列。 RAID 0+1 是 RAID 0 和 RAID 1 的組合形式。 它在提供與 RAID 1 一樣的數據安全保障的同時， 也提供了與 RAID 0 近似的存儲性能。

2.調整磁盤調度算法

選擇合適的磁盤調度算法， 可以減少磁盤的尋道時間。

1.5 MySQL 自身的優化

對 MySQL 自身的優化主要是對其配置文件 my.cnf 中的各項參數進行優化調整。

• 如指定 MySQL 查詢緩沖區的大小，
• 指定 MySQL 允許的最大連接進程數等。

1.6?應用優化

1.6.1 使用數據庫連接池

1.6.2 使用查詢緩存

它的作用是存儲 select 查詢的文本及其相應結果。

如果隨后收到一個相同的查詢， 服務器會從查詢緩存中直接得到查詢結果。

查詢緩存適用的對象是更新不頻繁的表， 當表中數據更改后， 查詢緩存中的相關條目就會被清空。

2.如果有一個特別大的訪問量到數據庫上， 怎么做優化？

2.1.使用優化查詢的方法（見上面）

2.2.主從復制， 讀寫分離， 負載均衡

目前，大部分的主流關系型數據庫都提供了主從復制的功能，通過配置兩臺（或多臺）數據庫的主從關系，可以將一臺數據庫服務器的數據更新同步到另一臺服務器上。

網站可以利用數據庫的這一功能，實現數據庫的讀寫分離，從而改善數據庫的負載壓力。

一個系統的讀操作遠遠多于寫操作，因此寫操作發向 master，讀操作發向 slaves 進行操作（簡單的輪循算法來決定使用哪個 slave）。

利用數據庫的讀寫分離，Web 服務器在寫數據的時候，訪問主數據庫（Master），主數據庫通過主從復制機制將數據更新同步到從數據庫（Slave），這樣當 Web 服務器讀數據的時候，就可以通過從數據庫獲得數據。這一方案使得在大量讀操作的 Web 應用可以輕松地讀取數據，而主數據庫也只會承受少量的寫入操作，還可以實現數據熱備份，可謂是一舉兩得的方案。

2.2.1 主從復制的原理：

影響 MySQL-A 數據庫的操作，在數據庫執行后，都會寫入本地的日志系統 A 中。

假設，實時的將變化了的日志系統中的數據庫事件操作，通過網絡發給 MYSQL-B。

MYSQL-B 收到后，寫入本地日志系統 B，然后一條條的將數據庫事件在數據庫中完成。

那么，MYSQL-A 的變化，MYSQL-B 也會變化，

這樣就是所謂的 MYSQL 的復制。

在上面的模型中，MYSQL-A 就是主服務器，即 master，MYSQL-B 就是從服務器，即slave。

日志系統 A，其實它是 MYSQL 的日志類型中的二進制日志，也就是專門用來保存修改數據庫表的所有動作，即 bin log。

【注意 MYSQL 會在執行語句之后，釋放鎖之前，寫入二進制日志，確保事務安全】

日志系統 B，并不是二進制日志，由于它是從 MYSQL-A 的二進制日志復制過來的，并不是自己的數據庫變化產生的，有點接力的感覺，稱為中繼日志，即 relay log。

可以發現，通過上面的機制，可以保證 MYSQL-A 和 MYSQL-B 的數據庫數據一致，但是時間上肯定有延遲，即 MYSQL-B 的數據是滯后的。

2.2.2 簡化版

mysql 主(稱 master)從(稱 slave)復制的原理：

master 將數據改變記錄到二進制日志(binary log)中，也即是配置文件 log-bin 指定的文件(這些記錄叫做二進制日志事件， binary log events)

從圖中可以看出，Slave 服務器中有一個 I/O線程(I/O Thread)在不停地監聽 Master的二進制日志(Binary Log)是否有更新：

如果沒有，它會睡眠等待 Master 產生新的日志事件；

如果有新的日志事件(Log Events)， 則會將其拷貝至 Slave 服務器中的中繼日志(Relay Log)。

slave 將 master 的二進制日志事件（binary log events）拷貝到它的中繼日志(relay log)

slave 重做中繼日志中的事件,將 Master 上的改變反映到它自己的數據庫中。 ， 所以兩端的數據是完全一樣的。

從圖中可以看出， Slave 服務器中有一個 SQL 線程(SQL Thread)從中繼日志讀取事件， 并重做其中的事件， 從而更新 Slave 的數據， 使其與 Master 中的數據一致。

只要該線程與 I/O 線程保持一致，中繼日志通常會位于 OS 的緩存中，所以中繼日志的開銷很小。

2.2.3 簡要原理圖：

2.2.4 主從復制的方式

1.同步復制

主服務器在將更新的數據寫入它的二進制日志（Binlog）文件中后，必須等待驗證所有的從服務器的更新數據是否已經復制到其中，之后才可以自由處理其它進入的事務處理請求。

2.異步復制

主服務器在將更新的數據寫入它的二進制日志（Binlog）文件中后，無需等待驗證更新數據是否已經復制到從服務器中，就可以自由處理其它進入的事務處理請求。

3.半同步復制

主服務器在將更新的數據寫入它的二進制日志（Binlog）文件中后，只需等待驗證其中一臺從服務器的更新數據是否已經復制到其中，就可以自由處理其它進入的事務處理請求，其他的從服務器不用管。

2.3 數據庫分表， 分區， 分庫

2.3.1 分表

見上面描述。

2.3.2 分區

把一張表的數據分成多個區塊，這些區塊可以在一個磁盤上，也可以在不同的磁盤上。

分區后，表面上還是一張表，但數據散列在多個位置，這樣一來，多塊硬盤同時處理不同的請求，從而提高磁盤 I/O 讀寫性能，實現比較簡單。

包括水平分區和垂直分區。

2.3.3 分庫

根據業務不同把相關的表切分到不同的數據庫中，比如 web、bbs、blog 等庫。

?

?

?

總結

以上是生活随笔為你收集整理的优化查询、访问量大时的优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。