【轉載】高效能MySQL小結

其實只看到了1/3，先記錄一下，等以後有空了再補上。

將查詢結果匯出到檔案

mysql > SQL QUERY INTO OUTFILE `/path/to/sql/file`

將每次操作都匯出到檔案

mysql > T /path/to/file 
mysql > ... //這些操作，及操作的結果都會輸出到對應的檔案 
mysql > 	 

檔案內容大概會是這樣

(root@localhost) [(none)]> use noah; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A Database changed 
(root@localhost) [noah]> show tables; 
+----------------+ 
| Tables_in_noah | 
+----------------+ 
| blog_category  | 
| blog_comment   | 
| blog_post      | 
| blog_res       | 
| blog_role      | 
| blog_role_res  | 
| blog_role_user | 
| blog_tag       | 
| blog_user      | 
| test           | 
+----------------+ 
10 rows in set (0.00 sec) 
(root@localhost) [noah]> select * from test; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | foo  | 
|  2 | bar  | 
+----+------+ 
2 rows in set (0.02 sec) 

顯示MySQL當前狀態及其他資訊

SHOW STATUS;                   //顯示MySQL當前狀態 
SHOW VARIABLES;                //顯示MySQL的變數資訊，如version/data_dir等等 
SHOW VARIABLES LIKE `%home%`;  //獲取包含home的變數 
SHOW TABLE STATUSG            //顯示當前表的狀態，注意後面的G，垂直顯示結果 
DESCRIBE tbl;                  //獲取表結構 
SHOW FULL COLUMNS FROM tbl;    //類似上面

顯示MySQL當前的連線狀況

# 使用mysqladmin mysqladmin processlist 
# 或進入到mysql cli後執行 
mysql > show processlist; # 結果大概是這樣 
+-----+------+-----------+------+---------+------+-------+------------------+ 
| Id  | User | Host      | db   | Command | Time | State | Info             | 
+-----+------+-----------+------+---------+------+-------+------------------+ 
| 409 | root | localhost | noah | Query   |    0 | NULL  | show processlist | 
+-----+------+-----------+------+---------+------+-------+------------------+

關於TIMESTAMP

• 第一個TIMESTAMP欄位會隨著表其他欄位的更新而自動更新，之後的TIMESTAMP欄位則不會。
• TIMESTAMP的範圍是：1970-2037；而DATETIME的範圍是：1000-9999

移除重複的行

# 注意這個IGNORE引數，如果沒加的話會報錯，且執行失敗 
# 假設要去除a,b項重複的行 
ALTER IGNORE TABLE tbl ADD UNIQUE INDEX(a,b);

檢視當前在操作的資料庫

SELECT DATABASE();

也可以在mysql的提示符上動點手腳

# edit /etc/mysql/my.cnf 
[mysql] #no-auto-rehash 
# faster start of mysql but no tab completition 
prompt=(\u@\h) [\d]> \ 

複製一個表

CREATE TABLE tbl1 LIKE tbl; 
INSERT INTO tbl1 SELECT * FROM tbl; # 也可以先用mysqladmin匯出資料，再匯入

定長表與變長表

包含任何varchar、text等變長欄位的資料表，即為變長表，反之則為定長表。所以CHAR和VARCHAR不共存

CHAR，最多可以容納30個字元，但如果字元數不到30個的話，也會佔用這些空間，只不過會在後面補上空格，但我們查詢時又會發現尾部沒有空格，這是因為空格已經被CHAR處理掉了。

VARCHAR，也是最多可以容納30個字元，但如果不足30個的話，有多少字元佔多少空間，不會浪費。

變長表的優勢在於有效利用空間，但由於記錄大小不同，在其上進行許多刪除或更新操作會使表中的碎片增多，需要定期OPTIMIZE TABLE以保持效能。

定長表的查詢，檢索和更新速度都比變長錶快，但佔用的空間也大。

PS:MySQL 5.0.3之後VARCHAR的最大字元數為65535

轉換編碼

SET NAMES utf8 # CHARSET utf8

聚合函式

COUNT [GROUP BY]
COUNT + HAVING + GROUP BY (HAVING可以看作後置WHERE語句)
MIN/MAX [GROUP BY]
SUM/AVG [GROUP BY]
DISTINCT [GROUP BY]
所有的這些聚合函式加上GROUP BY之後，都只對GROUP BY部分有效。(不好理解，忽略)

併發控制

讀鎖(共享鎖)/寫鎖(排他鎖)

當某一使用者修改一部分資料時，MySQL會禁止其他使用者讀取同一資料。大多數時，MySQL都是以透明的方式實現鎖的內部管理

鎖粒度

只鎖定部分修改的資料，而不是所有的資源，或者只對要修改的資料片精確加鎖。任何時間，在給定的資源上，被加鎖的資料量越小，就可以允許更多的併發修改，只要相互之間互不衝突即可

這麼做的問題是加鎖也會消耗系統資源。如獲得鎖，檢查鎖是否已解除，以及釋放鎖等，都會增加系統開銷。如果系統花費大量時間來管理鎖，而不是讀/寫資料，那麼系統整體效能都可能會受到影響

所謂的鎖策略，就是在鎖開銷和資料安全之間尋求一種平衡。

表鎖(MyISAM)

開銷最小，但不適合頻繁寫操作

行鎖(InnoDb)

可以支援最大的併發處理，但同時也會增加開銷（InnoDb），由儲存引擎實現，而不是MySQL伺服器

事務

一組原子性的SQL語句。要麼全部執行(commit)，要麼全部不執行(rollback)

正像鎖粒度的增加會導致鎖開銷的增加一樣，這種事務處理中的額外安全措施，也會導致資料庫伺服器要完成更多的額外工作

MySQL預設操作是AutoCommit，這意味著除非顯示地開始一個事務，否則將把每個SQL操作視為一個單獨事務自動執行

死鎖

兩個或多個事務在同一資源上相互佔用，並請求加鎖時，導致的惡性迴圈現象

解決辦法：死鎖檢測/死鎖超時機制。InnoDb處理死鎖的方法是，回滾擁有最少排他行級鎖的事務。

隱式和顯式鎖定

InnoDb: 一個事務在執行過程中的任何時候，都可以獲得鎖，但只有在執行COMMIT或ROLLBACK語句後，才可以釋放這些鎖。

InnoDb也支援顯式鎖定，如：

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

多版本併發控制

MySQL事務性儲存引擎，如InnoDb，不是簡單使用行加鎖機制，而是MVCC和行加鎖機制關聯使用。

MVCC不是MySQL獨有的技術，其他如Oracle, Postgresql等都在使用

可以將MVCC設想成一種行級加鎖的變形，它避免了很多情況下的鎖操作，大大降低了系統的開銷

MVCC是通過及時儲存在某些時刻的資料快照，而得以實現的。

所謂”版本號”，其實是InnoDb維護的一個計數器，每啟動一個事務，計數器隨著遞增，並將該號作為事務的版本號

[INSERT]
InnoDb將系統當前的版本號設為新增行的版本號

[DELETE]
InnoDb將系統當前的版本號設為被刪除行的刪除號，該行並未立即被物理刪除

[UPDATE]
INSERT+DELETE

[SELECT]
1. 行版本號不大於事務版本號。這確保了該行在事務開始時已存在，或者由當前事務建立、更新
2. 行刪除號不存在，或者刪除號大於事務版本號。這確保事務開始前行未被刪除

對於被標記為刪除的行，InnoDb有專門的執行緒負責物理刪除，當行滿足如下條件時認為可以將其物理刪除：當前不存在版本號小於該行刪除號的事務，這樣可以確保不會有事務再引用到該行

儲存這些額外記錄的好處，是使大多數讀操作都不必申請加鎖

關於MyISAM

• 表加鎖。併發低/開銷少
• 將每個表儲存成兩個檔案：資料檔案(.MYD)和索引檔案(.MYI)
• 使用CHECK TABLE mytable 和 REPAIR TABLE mytable來修復表，也可以使用myisamchk命令
• 索引長度不能超過1000(注意，如果是utf8的話，長度x3)，InnoDb沒有此限制
• 可以延遲索引。使用表建立選項DELAY_KEY_WRITE建立的MyISAM表，在SQL結束之後，不會將索引的改變資料寫入磁碟，而是在記憶體的鍵緩衝區中快取索引改變資料，只有在清理緩衝區或關閉表時才將索引塊轉到磁碟。對於資料經常改變，並且頻繁使用的表，這種模式大大提高了表的處理效能。不過，如果伺服器或系統崩潰，索引將肯定損壞，並需要修復

關於InnoDb

• 行級鎖。併發高/開銷相對高
• 高效能
• 崩潰後自動恢復
• 主鍵聚簇索引，輔助索引非聚簇索引(單獨索引樹)，輔助索引也會包含主鍵列，所以如果主鍵列較大，則它的輔助索引也會較大
• 任何改變InnoDb表結構的操作會導致整個表的重建，包括重建所有索引
• 外來鍵約束
• 自動提交效能差？
• 可以顯示鎖定
• 不要對InnoDb使用不帶WHERE語句的count(*)，這會導致InnoDb執行全表掃描或索引掃描，而MyISAM只需要從相關記錄中讀取該值即可。

效能檢測

mysql > SET PROFILING = 1;
mysql > ...
mysql > SHOW PROFILES;

會把執行的語句和執行時間都列印出來，如下

+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00011700 | select count(*) from user         |
|        2 | 0.00033500 | select count(*) from user_copy    |
|        3 | 0.59868300 | select count(created) from user   |
|        4 | 0.51746400 | select count(name) from user_copy |
|        5 | 0.00846700 | show table status like `user`     |
+----------+------------+-----------------------------------+

還可以針對某個query進行更細緻的分析

也可以使用FLUSH STATUS + SHOW SESSION STATUS

mysql > SHOW PROFILE FOR QUERY 1;

查詢快取

MySQL在第二次執行相同的SQL查詢語句時，預設會使用查詢快取。加上”SQL_NO_CACHE”不使用查詢快取

SELECT SQL_NO_CACHE username, ...

關於NULL

• 儘量避免NULL
• MySQL難以優化引用了可空列的查詢，它會使索引，索引統計和值更加複雜
• 即使要在表中儲存”沒有值”的欄位，還是有可能不使用NULL的，考慮使用0或空字元來代替它。

索引

索引是效能問題的首要原因，先搞定索引，再去搞查詢優化

B-Tree索引

• 根節點儲存了指向子節點的指標，儲存引擎根據指標尋找資料
• 當一個資料塊不能放下所有索引欄位資料時，就會形成樹形的根節點或分支節點，所以樹的深度和廣度是由資料量決定的
• 每個節點包含了下層節點的連結，(沒有相鄰節點連結，上層連結可有可沒有)

假設建立了一個(last_name, first_name, birth)的索引，此索引對於以下型別可用

匹配全名 (例如可以找到一個叫Cuba Allen，並且出生於1960-01-01的人)
匹配最左字首 (例如可以找到姓為Allen的人，僅適用於索引中的第一列)

由於樹的節點是排好序的，它們可以用於查詢和ORDER BY查詢

B-Tree的侷限在於如果查詢不是從索引列的最左邊開始，就無法使用索引。所以索引列的順序至關重要。

高效能索引策略

隔離列

如果在查詢中沒有隔離索引的列，MySQL通常不會是使用索引。”隔離”列意味著它不是表示式的一部分，也沒有位於函式中。

字首索引

找到合適的字首長度(計算全列的選擇性，並使字首的選擇性接近於它)

SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo
SELECT COUNT(DISTINCE LEFT(city, 4))/COUNT(*) FROM city_demo

也要注意如果資料分佈非常不均勻，可能就會有問題

字首索引能很好的減少索引的大小及提高速度，但MySQL不能在ORDER BY和GROUP BY中使用索引

聚集索引 (InnoDb)

當表有聚集索引時，它的資料行實際儲存在索引的葉子頁(而不是指標)，所謂”聚集”就是指實際的資料行和相關的鍵值都儲存在一起，每個表只能由一個聚集索引(主索引)，因為不能以此把行儲存在兩個地方

優點：

• 可以把相關資料儲存在一起。如果沒有使用聚集，讀取每個郵件都會訪問磁碟
• 資料訪問快。聚集索引把索引和資料都儲存到了同一棵B-Tree中，因此從聚集索引中取得的資料通常比在非聚集索引進行查詢要快
• 聚集索引能最大限度地提升I/O密集負載的效能。

缺點：

• 更新索引列代價是龐大的，因為它強制InnoDb把每個更新的行移到新位置
• 輔助索引會比較大，因為它們的葉子包含了被引用行的主鍵列
• 輔助索引訪問需要兩次索引查詢

覆蓋索引

所有滿足查詢需要的資料的索引(只需要讀取索引，不需要再讀取行資料)，比如這條SQL語句

SELECT state_id, city, address FROM userinfo WHERE state_id = 5

如果只在state_id上建索引，則city,address都要從表裡讀取行資料

如果建立index (state_id, city, address)，既能使用state_id索引，同時又可以使用覆蓋索引，速度就快多了

多餘和重複索引

• MySQL允許你在統一列上建立多個索引，所以MySQL不得不單獨維護每一個索引
• 如果列(A,B)上有索引，那麼另外一個列(A)上的索引就是多餘的(B-Tree)
• 大多數情況下，多餘索引都是不好的，為了避免它，應該擴充套件已有索引，而不是新增新索引
• 索引越多，更新索引的開銷越大，尤其是在資料很多的情況下

–EOF–

若無特別說明，本站文章均為原創，轉載請保留連結，謝謝