詳解MySQL性能優化（一）

2024-07-24 13:07:47

字體：大中小

來源：轉載

供稿：網友

本文對MySQL性能優化進行了詳細的總結與介紹,需要的朋友可以參考下

一、MySQL的主要適用場景
1、Web網站系統

2、日志記錄系統

3、數據倉庫系統

4、嵌入式系統

二、MySQL架構圖：

詳解MySQL性能優化（一）

三、MySQL存儲引擎概述

1）MyISAM存儲引擎

MyISAM存儲引擎的表在數據庫中，每一個表都被存放為三個以表名命名的物理文件。首先肯定會有任何存儲引擎都不可缺少的存放表結構定義信息的.frm文件，另外還有.MYD和.MYI文件，分別存放了表的數據（.MYD）和索引數據（.MYI）。每個表都有且僅有這樣三個文件做為MyISAM存儲類型的表的存儲，也就是說不管這個表有多少個索引，都是存放在同一個.MYI文件中。

MyISAM支持以下三種類型的索引：

1、B-Tree索引

B-Tree索引，顧名思義，就是所有的索引節點都按照balancetree的數據結構來存儲，所有的索引數據節點都在葉節點。

2、R-Tree索引

R-Tree索引的存儲方式和b-tree索引有一些區別，主要設計用于為存儲空間和多維數據的字段做索引，所以目前的MySQL版本來說，也僅支持geometry類型的字段作索引。

3、Full-text索引

Full-text索引就是我們長說的全文索引，他的存儲結構也是b-tree。主要是為了解決在我們需要用like查詢的低效問題。

2）Innodb 存儲引擎

1、支持事務安裝

2、數據多版本讀取

3、鎖定機制的改進

4、實現外鍵

3）NDBCluster存儲引擎

NDB存儲引擎也叫NDBCluster存儲引擎，主要用于MySQLCluster分布式集群環境，Cluster是MySQL從5.0版本才開始提供的新功能。

4）Merge存儲引擎

MERGE存儲引擎，在MySQL用戶手冊中也提到了，也被大家認識為MRG_MyISAM引擎。Why？因為MERGE存儲引擎可以簡單的理解為其功能就是實現了對結構相同的MyISAM表，通過一些特殊的包裝對外提供一個單一的訪問入口，以達到減小應用的復雜度的目的。要創建MERGE表，不僅僅基表的結構要完全一致，包括字段的順序，基表的索引也必須完全一致。

5）Memory存儲引擎

Memory存儲引擎，通過名字就很容易讓人知道，他是一個將數據存儲在內存中的存儲引擎。Memory存儲引擎不會將任何數據存放到磁盤上，僅僅存放了一個表結構相關信息的.frm文件在磁盤上面。所以一旦MySQLCrash或者主機Crash之后，Memory的表就只剩下一個結構了。Memory表支持索引，并且同時支持Hash和B－Tree兩種格式的索引。由于是存放在內存中，所以Memory都是按照定長的空間來存儲數據的，而且不支持BLOB和TEXT類型的字段。Memory存儲引擎實現頁級鎖定。

6）BDB存儲引擎

BDB存儲引擎全稱為BerkeleyDB存儲引擎，和Innodb一樣，也不是MySQL自己開發實現的一個存儲引擎，而是由SleepycatSoftware所提供，當然，也是開源存儲引擎，同樣支持事務安全。

7）FEDERATED存儲引擎

FEDERATED存儲引擎所實現的功能，和Oracle的DBLINK基本相似，主要用來提供對遠程MySQL服務器上面的數據的訪問接口。如果我們使用源碼編譯來安裝MySQL，那么必須手工指定啟用FEDERATED存儲引擎才行，因為MySQL默認是不起用該存儲引擎的。

8）ARCHIVE存儲引擎

ARCHIVE存儲引擎主要用于通過較小的存儲空間來存放過期的很少訪問的歷史數據。ARCHIVE表不支持索引，通過一個.frm的結構定義文件，一個.ARZ的數據壓縮文件還有一個.ARM的meta信息文件。由于其所存放的數據的特殊性，ARCHIVE表不支持刪除，修改操

作，僅支持插入和查詢操作。鎖定機制為行級鎖定。

9）BLACKHOLE存儲引擎

BLACKHOLE存儲引擎是一個非常有意思的存儲引擎，功能恰如其名，就是一個“黑洞”。就像我們unix系統下面的“/dev/null”設備一樣，不管我們寫入任何信息，都是有去無回。

10）CSV存儲引擎

CSV存儲引擎實際上操作的就是一個標準的CSV文件，他不支持索引。起主要用途就是大家有些時候可能會需要通過數據庫中的數據導出成一份報表文件，而CSV文件是很多軟件都支持的一種較為標準的格式，所以我們可以通過先在數據庫中建立一張CVS表，然后將生成的報表信息插入到該表，即可得到一份CSV報表文件了。

四、影響MySQLServer性能的相關因素

1商業需求對性能的影響

典型需求：一個論壇帖子總量的統計，要求：實時更新。

2系統架構及實現對性能的影響

以下幾類數據都是不適合在數據庫中存放的：

二進制多媒體數據

流水隊列數據

超大文本數據

通過Cache技術來提高系統性能：

系統各種配置及規則數據；

活躍用戶的基本信息數據；

活躍用戶的個性化定制信息數據；

準實時的統計信息數據；

其他一些訪問頻繁但變更較少的數據；

3 Query語句對系統性能的影響

需求：取出某個group（假設id為1）下的用戶編號（id），用戶昵稱（nick_name），并按照加入組的時間（user_group.gmt_create）來進行倒序排列，取出前20個。

解決方案一：

復制代碼代碼如下:

		SELECT id,nick_name FROM user,user_group WHERE user_group.group_id=1 and user_group.user_id=user.id ORDER BY user_group.gmt_create desc limit 100,20;

解決方案二：

SELECT user.id,user.nick_name FROM(

SELECT user_id

FROM user_group

WHERE user_group.group_id=1

ORDER BY gmt_create desc

limit 100,20)t,user

WHERE t.user_id=user.id;