共計 8411 個字符，預計需要花費 22 分鐘才能閱讀完成。

這篇文章主要講解了“MySQL 數據庫怎么進行優化”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“MySQL 數據庫怎么進行優化”吧！

MySQL 數據庫怎么優化

第一步：

1: 磁盤尋道能力, 以高速硬盤(7200 轉 / 秒), 理論上每秒尋道 7200 次. 這是沒有辦法改變的, 優化的方法是 —- 用多個硬盤, 或者把數據分散存儲.

2: 硬盤的讀寫速度, 這個速度非常的快, 這個更容易解決 – 可以從多個硬盤上并行讀寫.

3:cpu.cpu 處理內存中的數據, 當有相對內存較小的表時, 這是最常見的限制因素.

4: 內存的限制. 當 cpu 需要超出適合 cpu 緩存的數據時, 緩存的帶寬就成了內存的一個瓶頸 — 不過現在內存大的驚人, 一般不會出現這個問題.

第二步:(本人使用的是學校網站的 linux 平臺(LinuxADVX.Mandrakesoft.com2.4.3-19mdk))

1: 調節服務器參數

用 shell mysqld-help 這個命令聲廠一張所有 mysql 選項和可配置變量的表. 輸出以下信息:

possiblevariablesforoption–set-variable(-o)are:

back_logcurrentvalue:5// 要求 mysql 能有的連接數量.back_log 指出在 mysql 暫停接受連接的時間內有多少個連接請求可以被存在堆棧中

connect_timeoutcurrentvalue:5//mysql 服務器在用 badhandshake(不好翻譯)應答前等待一個連接的時間

delayed_insert_timeoutcurrentvalue:200// 一個 insertdelayed 在終止前等待 insert 的時間

delayed_insert_limitcurrentvalue:50//insertdelayed 處理器將檢查是否有任何 select 語句未執行, 如果有, 繼續前執行這些語句

delayed_queue_sizecurrentvalue:1000// 為 insertdelayed 分配多大的隊

flush_timecurrentvalue:0// 如果被設置為非 0, 那么每個 flush_time 時間, 所有表都被關閉

interactive_timeoutcurrentvalue:28800// 服務器在關上它之前在洋交互連接上等待的時間

join_buffer_sizecurrentvalue:131072// 用與全部連接的緩沖區大小

key_buffer_sizecurrentvalue:1048540// 用語索引塊的緩沖區的大小, 增加它可以更好的處理索引

lower_case_table_namescurrentvalue:0//

long_query_timecurrentvalue:10// 如果一個查詢所用時間大于此時間,slow_queried 計數將增加

max_allowed_packetcurrentvalue:1048576// 一個包的大小

max_connectionscurrentvalue:300// 允許同時連接的數量

max_connect_errorscurrentvalue:10// 如果有多于該數量的中斷連接, 將阻止進一步的連接, 可以用 flushhosts 來解決

max_delayed_threadscurrentvalue:15// 可以啟動的處理 insertdelayed 的數量

max_heap_table_sizecurrentvalue:16777216//

max_join_sizecurrentvalue:4294967295// 允許讀取的連接的數量

max_sort_lengthcurrentvalue:1024// 在排序 blob 或者 text 時使用的字節數量

max_tmp_tablescurrentvalue:32// 一個連接同時打開的臨時表的數量

max_write_lock_countcurrentvalue:4294967295// 指定一個值 (通常很小) 來啟動 mysqld, 使得在一定數量的 write 鎖定之后出現 read 鎖定

net_buffer_lengthcurrentvalue:16384// 通信緩沖區的大小 – 在查詢時被重置為該大小

query_buffer_sizecurrentvalue:0// 查詢時緩沖區大小

record_buffercurrentvalue:131072// 每個順序掃描的連接為其掃描的每張表分配的緩沖區的大小

sort_buffercurrentvalue:2097116// 每個進行排序的連接分配的緩沖區的大小

table_cachecurrentvalue:64// 為所有連接打開的表的數量

thread_concurrencycurrentvalue:10//

tmp_table_sizecurrentvalue:1048576// 臨時表的大小

thread_stackcurrentvalue:131072// 每個線程的大小

wait_timeoutcurrentvalue:28800// 服務器在關閉它 3 之前的一個連接上等待的時間

根據自己的需要配置以上信息會對你幫助.

第三步：

1: 如果你在一個數據庫中創建大量的表, 那么執行打開, 關閉，創建 (表) 的操作就會很慢.2:mysql 使用內存

a: 關鍵字緩存區 (key_buffer_size) 由所有線程共享

b: 每個連接使用一些特定的線程空間. 一個棧 (默認為 64k, 變量 thread_stack), 一個連接緩沖區(變量 net_buffer_length) 和一個結果緩沖區(net_buffer_length). 特定情況下, 連接緩沖區和結果緩沖區被動態擴大到 max_allowed_packet.

c: 所有線程共享一個基存儲器

d: 沒有內存影射

e: 每個做順序掃描的請求分配一個讀緩沖區(record_buffer)

f: 所有聯結均有一遍完成并且大多數聯結甚至可以不用一個臨時表完成. 最臨時的表是基于內存的 (heap) 表

g: 排序請求分配一個排序緩沖區和 2 個臨時表

h: 所有語法分析和計算都在一個本地存儲器完成

i: 每個索引文件只被打開一次, 并且數據文件為每個并發運行的線程打開一次

j: 對每個 blob 列的表，一個緩沖區動態的被擴大以便讀入 blob 值

k: 所有正在使用的表的表處理器被保存在一個緩沖器中并且作為一個 fifo 管理.

l: 一個 mysqladminflush-tables 命令關閉所有不在使用的表并且在當前執行的線程結束時標記所有在使用的表準備關閉

3:mysql 鎖定表

mysql 中所有鎖定不會成為死鎖.wirte 鎖定:mysql 的鎖定原理:a: 如果表沒有鎖定, 那么鎖定;b 否則, 把鎖定請求放入寫鎖定隊列中

read 鎖定:mysql 的鎖定原理:a: 如果表沒有鎖定, 那么鎖定;b 否則, 把鎖定請求放入讀鎖定隊列中

有時候會在一個表中進行很多的 select,insert 操作, 可以在一個臨時表中插入行并且偶爾用臨時表的記錄更新真正的表

a: 用 low_priority 屬性給一個特定的 insert,update 或者 delete 較低的優先級

b:max_write_lock_count 指定一個值 (通常很小) 來啟動 mysqld, 使得在一定數量的 write 鎖定之后出現 read 鎖定

c: 通過使用 setsql_low_priority_updates= 1 可以從一個特定的線程指定所有的更改應該由較低的優先級完成

d: 用 high_priority 指定一個 select

e: 如果使用 insert….select…. 出現問題, 使用 myisam 表 —— 因為它支持因為它支持并發的 select 和 insert

4: 最基本的優化是使數據在硬盤上占據的空間最小. 如果索引做在最小的列上, 那么索引也最小. 實現方法:

a: 使用盡可能小的數據類型

b: 如果可能，聲明表列為 NOTNULL.

c: 如果有可能使用變成的數據類型, 如 varchar(但是速度會受一定的影響)

d: 每個表應該有盡可能短的主索引 e: 創建確實需要的索引

f: 如果一個索引在頭幾個字符上有唯一的前綴, 那么僅僅索引這個前綴 —-mysql 支持在一個字符列的一部分上的索引

g: 如果一個表經常被掃描, 那么試圖拆分它為更多的表

第四步

1: 索引的使用, 索引的重要性就不說了, 功能也不說了, 只說怎么做. 首先要明確所有的 mysql 索引 (primary,unique,index) 在 b 樹中有存儲. 索引主要用語:

a: 快速找到 where 指定條件的記錄 b: 執行聯結時, 從其他表檢索行 c: 對特定的索引列找出 max()和 min()值

d：如果排序或者分組在一個可用鍵的最前面加前綴，排序或分組一個表

e：一個查詢可能被用來優化檢索值，而不用訪問數據文件. 如果某些表的列是數字型并且正好是某個列的前綴，為了更快，值可以從索引樹中取出

2：存儲或者更新數據的查詢速度 grant 的執行會稍稍的減低效率.

mysql 的函數應該被高度的優化. 可以用 benchmark(loop_count,expression)來找出是否查詢有問題

select 的查詢速度：如果想要讓一個 select…where… 更快，我能想到的只有建立索引. 可以在一個表上運行 myisamchk–analyze 來更好的優化查詢. 可以用 myisamchk–sort-index–sort-records= 1 來設置用一個索引排序一個索引和數據.

3：mysql 優化 where 子句

3.1：刪除不必要的括號：

((aANDb)ANDcOR(((aANDb)AND(aANDd)))) (aANDbANDc)OR(aANDbANDcANDd)

3.2: 使用常數

(ab 5ANDb=cANDa=5

3.3: 刪除常數條件

(b =5ANDb=5)OR(b=6AND5=5)OR(b=100AND2=3) b=5ORb=6

3.4: 索引使用的常數表達式僅計算一次

3.5：在一個表中，沒有一個 where 的 count(*)直接從表中檢索信息

3.6: 所有常數的表在查詢中在任何其他表之前讀出

3.7: 對外聯結表最好聯結組合是嘗試了所有可能性找到的

3.8：如果有一個 order by 字句和一個不同的 group by 子句或者 order by 或者 group by 包含不是來自聯結的第一個表的列，那么創建一個臨時表

3.9: 如果使用了 sql_small_result，那么 msyql 使用在內存中的一個表

3.10: 每個表的索引給查詢并且使用跨越少于 30% 的行的索引.

3.11 在每個記錄輸出前，跳過不匹配 having 子句的行

4：優化 left join

在 mysql 中 aleftjoinb 按以下方式實現

a：表 b 依賴于表 a

b：表 a 依賴于所有用在 left join 條件的表(除了 b)

c：所有 left join 條件被移到 where 子句中

d：進行所有的聯結優化，除了一個表總是在所有他依賴的表后讀取. 如果有一個循環依賴，那么將發生錯誤

e：進行所有的標準的 where 優化 f：如果在 a 中有一行匹配 where 子句，但是在 b 中沒有任何匹配 left join 條件，那么，在 b 中生成的所有設置為 NULL 的一行

g：如果使用 left join 來找出某些表中不存在的行并且在 where 部分有 column_nameISNULL 測試(column_name 為 NOTNULL 列). 那么，mysql 在它已經找到了匹配 left join 條件的一行后，將停止在更多的行后尋找

5：優化 limit

a：如果用 limit 只選擇一行，當 mysql 需要掃描整個表時，它的作用相當于索引

b：如果使用 limit# 與 order by，mysql 如果找到了第 #行，將結束排序，而不會排序正個表

c：當結合 limit# 和 distinct 時，mysql 如果找到了第 #行，將停止

d：只要 mysql 已經發送了第一個 #行到客戶，mysql 將放棄查詢

e：limit0 一直會很快的返回一個空集合.

f：臨時表的大小使用 limit# 計算需要多少空間來解決查詢

6：優化 insert

MySQL 數據庫怎么優化

插入一條記錄的是由以下構成：

a: 連接(3)

b: 發送查詢給服務器(2)

c: 分析查詢(2)

d: 插入記錄(1* 記錄大小)

e：插入索引(1* 索引)

f：關閉(1)

以上數字可以看成和總時間成比例

改善插入速度的一些方法：

6.1：如果同時從一個連接插入許多行，使用多個值的 insert，這比用多個語句要快

6.2：如果從不同連接插入很多行，使用 insert delayed 語句速度更快

6.3: 用 myisam，如果在表中沒有刪除的行，能在 select：s 正在運行的同時插入行

6.4: 當從一個文該篇文章件裝載一個表時，用 load data infile. 這個通常比 insert 快 20 倍

6.5: 可以鎖定表然后插入 – 主要的速度差別是在所有 insert 語句完成后，索引緩沖區僅被存入到硬盤一次. 一般與有不同的 insert 語句那樣多次存入要快. 如果能用一個單個語句插入所有的行，鎖定就不需要. 鎖定也降低連接的整體時間. 但是對某些線程最大等待時間將上升. 例如：

thread1does1000inserts

thread2,3and4does1insert

thread5does1000inserts

如果不使用鎖定，2，3，4 將在 1 和 5 之前完成. 如果使用鎖定，2，3，4，將可能在 1 和 5 之后完成. 但是整體時間應該快 40%. 因為 insert，update，delete 操作在 mysql 中是很快的，通過為多于大約 5 次連續不斷的插入或更新一行的東西加鎖，將獲得更好的整體性能. 如果做很多一行的插入，可以做一個 lock tables，偶爾隨后做一個 unlock tables(大約每 1000 行)以允許另外的線程存取表. 這仍然將導致獲得好的性能.load data infile 對裝載數據仍然是很快的.

為了對 load data infile 和 insert 得到一些更快的速度，擴大關鍵字緩沖區.

7 優化 update 的速度

它的速度依賴于被更新數據的大小和被更新索引的數量

使 update 更快的另一個方法是推遲修改，然后一行一行的做很多修改. 如果鎖定表，做一行一行的很多修改比一次做一個快

8 優化 delete 速度

刪除一個記錄的時間與索引數量成正比. 為了更快的刪除記錄，可以增加索引緩存的大小從一個表刪除所有行比刪除這個表的大部分要快的多

第五步

1：選擇一種表類型 1.1 靜態 myisam

這種格式是最簡單且最安全的格式，它是磁盤格式中最快的. 速度來自于數據能在磁盤上被找到的難易程度. 當鎖定有一個索引和靜態格式的東西是，它很簡單，只是行長度乘以數量. 而且在掃描一張表時，每次用磁盤讀取來讀入常數個記錄是很容易的. 安全性來源于如果當寫入一個靜態 myisam 文件時導致計算機 down 掉，myisamchk 很容易指出每行在哪里開始和結束，因此，它通常能收回所有記錄，除了部分被寫入的記錄. 在 mysql 中所有索引總能被重建

1.2 動態 myisam

這種格式每一行必須有一個頭說明它有多長. 當一個記錄在更改期間變長時，它可以在多于一個位置上結束. 能使用 optimize tablename 或 myisamchk 整理一張表. 如果在同一個表中有像某些 varchar 或者 blob 列那樣存取 / 改變的靜態數據，將動態列移入另外一個表以避免碎片.

1.2.1 壓縮 myisam，用可選的 myisampack 工具生成

1.2.2 內存

這種格式對小型 / 中型表很有用. 對拷貝 / 創建一個常用的查找表到洋 heap 表有可能加快多個表聯結，用同樣數據可能要快好幾倍時間.

selecttablename.a,tablename2.afromtablename,tablanem2,tablename3where

tablaneme.a=tablename2.aandtablename2.a=tablename3.aandtablename2.c!=0;

為了加速它，可以用 tablename2 和 tablename3 的聯結創建一個臨時表，因為用相同列 (tablename1.a) 查找.

CREATE TEMPORARY TABLE testTYPE=HEAP

SELECT

tablename2.aasa2,tablename3.aasa3

FROM

tablenam2,tablename3

WHERE

tablename2.a=tablename3.aandc=0;

SELECTtablename.a,test.a3fromtablename,testwheretablename.a=test.a1;

SELECTtablename.a,test,a3,fromtablename,testwheretablename.a=test.a1and….;

1.3 靜態表的特點

1.3.1 默認格式. 用在表不包含 varchar，blob，text 列的時候

1.3.2 所有的 char，numeric 和 decimal 列填充到列寬度

1.3.3 非常快

1.3.4 容易緩沖

1.3.5 容易在 down 后重建，因為記錄位于固定的位置

1.3.6 不必被重新組織(用 myisamchk)，除非是一個巨量的記錄被刪除并且優化存儲大小

1.3.7 通常比動態表需要更多的存儲空間

1.4 動態表的特點

1.4.1 如果表包含任何 varchar，blob，text 列，使用該格式

1.4.2 所有字符串列是動態的

1.4.3 每個記錄前置一個位.

1.4.4 通常比定長表需要更多的磁盤空間

1.4.5 每個記錄僅僅使用所需要的空間，如果一個記錄變的很大，它按需要被分成很多段，這導致了記錄碎片

1.4.6 如果用超過行長度的信息更新行，行被分段.

1.4.7 在系統 down 掉以后不好重建表，因為一個記錄可以是多段

1.4.8 對動態尺寸記錄的期望行長度是 3 +(number of columns+7)/8+(numberofcharcolumns)+packedsizeofnumericcolumns+lengthofstrings+(numberofNULLcolumns+7)/8

對每個連接有 6 個字節的懲罰. 無論何時更改引起記錄的變大，都有一個動態記錄被連接. 每個新連接至少有 20 個字節，因此下一個變大將可能在同一個連接中. 如果不是，將有另外一個連接. 可以用 myisamchk - 惡毒檢查有多少連接. 所有連接可以用 myisamchk- r 刪除.

1.5 壓縮表的特點

1.5.1 一張用 myisampack 實用程序制作的只讀表.

1.5.2 解壓縮代碼存在于所有 mysql 分發中，以便使沒有 myisampack 的連接也能讀取用 myisampack 壓縮的表

1.5.3 占據很小的磁盤空間

1.5.4 每個記錄被單獨壓縮. 一個記錄的頭是一個定長的 (1～～3 個字節) 這取決于表的最大記錄. 每列以不同的方式被壓縮. 一些常用的壓縮類型是：

a: 通常對每列有一張不同的哈夫曼表 b: 后綴空白壓縮 c: 前綴空白壓縮 d: 用值 0 的數字使用 1 位存儲

e: 如果整數列的值有一個小范圍，列使用最小的可能類型來存儲. 例如：如果所有的值在 0 到 255 之間，一個 bigint 可以作為一個 tinyint 存儲

g: 如果列僅有可能值的一個小集合，列類型被轉換到 enum h: 列可以使用上面的壓縮方法的組合

1.5.5 能處理定長或動態長度的記錄，去不能處理 blob 或者 text 列 1.5.6 能用 myisamchk 解壓縮

mysql 能支持不同的索引類型，但一般的類型是 isam，這是一個 B 樹索引并且能粗略的為索引文件計算大小為(key_length+4)*0.67，在所有的鍵上的總和.

字符串索引是空白壓縮的。如果第一個索引是一個字符串，它可將壓縮前綴如果字符串列有很多尾部空白或是一個總部能甬道全長的 varchar 列，空白壓縮使索引文件更小. 如果很多字符串有相同的前綴.

1.6 內存表的特點

mysql 內部的 heap 表使用每偶溢出去的 100% 動態哈希并且沒有與刪除有關的問題. 只能通過使用在堆表中的一個索引來用等式存取東西(通常用 = 操作符)

堆表的缺點是：

1.6.1 想要同時使用的所有堆表需要足夠的額外內存

1.6.2 不能在索引的一個部分搜索

1.6.3 不能按順序搜索下一個條目(即，使用這個索引做一個 order by)

1.6.4mysql 不能算出在 2 個值之間大概有多少行. 這被優化器使用是用來決定使用哪個索引的，但是在另一個方面甚至不需要磁盤尋道。

感謝各位的閱讀，以上就是“MySQL 數據庫怎么進行優化”的內容了，經過本文的學習后，相信大家對 MySQL 數據庫怎么進行優化這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！