MySQL 性能优化及常用命令
MySQL 緩沖機制詳解
mysql緩存機制簡單的說就是緩存sql文本及查詢結果，如果運行相同的sql，服務器直接從緩存中取到結果，而不需要再去解析和執行sql。如果表更改了，那么使用這個表的所有緩沖查詢將不再有效，查詢緩存值的相關條目被清空。更改指的是表中任何數據或是結構的改變，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改變了的表的使用MERGE表的查詢。顯然，這對于頻繁更新的表，查詢緩存是不適合的，而對于一些不常改變數據且有大量相同sql查詢的表，查詢緩存會節約很大的性能。
查詢必須是完全相同的(逐字節相同)才能夠被認為是相同的。另外，同樣的查詢字符串由于其它原因可能認為是不同的。使用不同的數據庫、不同的協議版本或者不同 默認字符集的查詢被認為是不同的查詢并且分別進行緩存。
MYSQL 鎖機制
MYSQL 各存儲引擎使用了三種類型（級別）的鎖定機制：行級鎖定、頁級鎖定和表級鎖定。
1，行級鎖定 (row-level)
行級鎖定的最大特點就是鎖定對象的顆粒度很小，它是目前各大數據庫管理軟件所實現的鎖定顆粒度最小的。由于鎖定顆粒度很小，發生鎖定資源爭用的概率也最小，能夠給予應用程序盡可能大的并發處理能力，從而提高一些需要高并發應用系統的整體性能。弊端是由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要的操作就更多，帶來的消耗自然就更大。此外，行級鎖定也最容易發生死鎖。
2，表級鎖定 (table-level)
和行級鎖相反，表級別的鎖定是MYSQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統處理成本最小，所以獲取鎖和釋放鎖的速度很快，由于表級鎖一次會將整個表鎖定，所以可以很好的避免死鎖問題。但是，表級鎖帶來最大的負面影響就是出現鎖定資源爭用的概率也會最高，致使并發度的性能大打折扣。
3，頁級鎖定 (page-level)
頁級鎖定是MYSQL中比較獨特的一種鎖定級別，在其他數據庫管理軟件中并不常見。頁級鎖定的特點是鎖定顆粒度介于行級鎖定和表級鎖定之間，所以獲取鎖定所需要的資源開銷，以及所能提供的并發處理能力同樣也介于上面兩種。另外，也會發生死鎖。
在數據庫實現資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數據量的數據需要消耗的內存數量越來越多。不過隨著鎖定資源顆粒度的減小，應用程序的訪問請求遇到鎖等待的可能性也會隨之降低，系統整體并發度也隨之提高。
在MYSQL數據庫中，使用表級鎖定的主要是MYISAM，MEMORY,CSV等一些非事務性存儲引擎，而使用行級鎖定的主要是INNODB存儲引擎和NDB CLUSTER存儲引擎，頁級鎖定主要是BERKELEYDB存儲引擎。
MYSQL - MYISAM 的優化
1，縮短鎖定時間
(1) 盡量減少大的復雜Query，將復雜的Query分拆成幾個小的Query分步進行；
(2) 盡可能地建立足夠高效的索引，讓數據檢索更訊速；
(3) 盡量讓MYISAM存儲引擎的表只存放必要的信息， 控制字段類型；
(4) 利用合適機會優化 MYISAM表數據文件；
2，分離能并行的操作
MYISAM的存儲引擎還有一個非常有用的特性，那就是Concurrent Insert(并發插入)的特性。
MYISAM存儲引擎有一個控制是否打開 Concurrent Insert 功能的參數選項：concurrent_insert ，可以設置為0 , 1, 或者2 。說明如下：
(1) concurrent_insert = 2 ，無論MYISAM存儲引擎的表數據文件的中間部分是否存在因為刪除數據而留下的空閑空間，都允許在數據文件尾部進行 Concurrent Insert 操作。
(2) concurrent_insert = 1，當MYISAM存儲引擎表數據文件中間不存在空閑空間的時候，可以從尾部進行Concurrent Insert。
(3) concurrent_insert = 0，無論MYISAM存儲引擎的表數據文件的中間部分是否存在因為刪除數據而留下的空閑空間，都不允許Concurrent Insert。
3，合理利用讀寫優先級
MYISAM 默認情況下是寫優先級要大于讀，所以可以根據系統環境的差異決定讀與寫的優先級。如果系統是一個以讀為主，而且優先保證查詢性能的話，可以通過設置系統參數選項 low_priority_updates = 1 ，將寫的優先級設置為比讀低，及告訴MYSQL盡量先處理讀請求。如果系統需要有限保證數據寫入的性能的話，則不需要設置 low_priority_updates 的參數。建議 concurrent_insert 設置為 1。
MYSQL - INNODB 的優化
INNODB 存儲引擎實現了行級鎖定，雖然在鎖定機制的實現方面帶來的性能損耗可能比表級鎖定要更高一些，但是在整體并發處理能力方面是要遠遠優于MYISAM的表級鎖定的。當系統并發量較高的時候，INNODB的整體性能和MYISAM相比就會有明顯的優勢了。但是當使用不當的時候，可能會讓INNODB的整體性能表現不僅不比MYISAM高，甚至可能會更差。優化MYISAM要做好以下的工作。
(1) 盡可能讓所有的數據檢索都通過索引來完成，從而避免INNODB因為無法通過索引加鎖而升級為表級鎖定。
(2) 合理設計索引，讓INNODB在索引鍵上面加鎖盡可能準確，盡可能地縮小鎖定范圍，避免造成不必要的鎖定而影響其它QUERY的執行。
(3) 盡可能減少基于范圍的數據檢索過濾條件，避免因為間隙帶來的影響而鎖定了不該鎖定的記錄。
(4) 盡量控制事務大小，減少鎖定的資源量和鎖定時間長度。
系統鎖定爭用情況查詢
執行語句 SHOW STATUS LIKE 'table%'; 查詢表級鎖定爭用狀態變量，說明如下：
table_locks_immediate : 產生表級鎖定的次數；
table_locks_waited : 出現表級鎖定爭用而發生等待的次數；
如果 table_locks_waited 狀態值比較高，那么說明系統中表級鎖定爭用的現象比較嚴重了。
執行語句 SHOW STATUS LIKE 'innodb_row_lock%'; 查詢行級鎖定爭用狀態變量，說明如下：
innodb_row_lock_current_waits : 當前正在等待鎖定的數量；
innodb_row_lock_time : 從系統啟動到現在鎖定總時間長度；
innodb_row_lock_time_avg : 每次等待所花平均時間；
innodb_row_lock_time_max: 鎖定最長的一次所花的時間；
innodb_row_lock_waits : 從系統啟動到現在總共等待次數；?
Query 語句優化
MySQL Query Qotimizer ，其主要的功能是通過計算分析系統中收集的各種統計信息，為客戶端請求的Query給出最優的執行計劃，也就是最優化的數據檢索方式。Query語句的優化思路和原則主要體現在下面幾個方向：
（1） 優化更需要優化的 QUERY；
高并發低消耗的QUERY對整個系統的影 響遠比低并發高消耗的大。
（2） 定位優化對象的性能瓶頸；
IO 還是 CPU，可以借用 PROFILING 的功能找出瓶頸。
（3） 明確優化目標；
根據需求分析。
（4） 從EXPLAIN入手；
可以參考 MYSQL QUERY QOTIMIZER 提出的方案。
（5） 多使用PROFILE;
（6） 永遠用小結果集驅動大的結果集；
優化 JOIN QUERY 的時候，最基本的原則就是 小結果集驅動大結果集，通過這個原則來減少嵌套循環中的循環次數，以減少IO總量及CPU運算次數。
（7） 盡可能在索引中完成排序；
（8） 只取自已需要的COLUMNS；
（9） 僅僅使用最有效的過濾條件；
（10） 盡可能避免復雜的 JOIN 和子查詢；
前四點可以理解為QUERY優化的一個基本思路，后面部分則為優化的基本原則。
EXPLAIN 的信息解釋
SELECT_TYPE 所使用的查詢類型
DEPENDENT SUBQUERY ：子查詢內層的第一個SELECT，依賴于外部查詢的結果集
DEPENDENT UNION： 子查詢中的UNION，且為UNION中從第二個SELECT開始的后面所有SELECT，同樣依賴于外部查詢的結果集。
PRIMARY：子查詢中的最外層查詢，注意并不是主鍵查詢。
SIMPLE：除子查詢或UNION之外的其它查詢。
SUBQUERY：子查詢內層查詢的第一個SELECT，結果不依賴于外部查詢結果集。
UNCACHEABLE SUBQUERY：結果集無法緩存的子查詢。
UNION：UNION語句中第二個SELECT開始后面的所有SELECT，第一個SELECT為PRIMARY。
UNION RESULT：UNION中的合并結果。
TABLE 顯示這一步所訪問的數據庫的表的名稱
TYPE 對表使用的訪問方式，主要包含如下集中類型。
ALL：全表掃描。
CONST：讀常量，最多只會有一條記錄匹配，由于是常量，實際上只需要讀一次。
EQ_REF：最多只會有一條匹配結果，一般通過主鍵或唯一鍵索引來訪問。
FULLTEXT：進行全文索引
查看數據庫狀態：
show full processlist;
show table status\G; #查看所有表信息
show create table table_name #查看table_name的建表信息
show columns from table_name #查看table_name的字段信息
show innodb status;
show engines; #顯示存儲引擎的狀態信息
show variables; #顯示MySQL系統變量的值
show index from table_name; #顯示table_name的索引信息
show open tables; #列舉在表緩存中當前被打開的非TEMPORARY表
show databases; #查看有多少數據庫在服務器中
SHOW TABLES; 顯示當前庫的所有表
表操作命令：
檢查表 CHECK TABLE table_name;
修復表 REPAIR TABLE table_name;
優化表 OPTIMIZE TABLE table_name;
分析表 ANALYZE TABLE table_name;
清空表 TRUNCATE table_name;
刪除表 DROP table_name;
重命名表 ALTER TABLE table1 RENAME table2;
改變一個字段的屬性，同時重命名字段名 ALTER TABLE table_name CHANGE old_name new_name VARCHAR(80) NOT NULL;
不改變字段名的字段屬性修改 ALTER TABLE table_name MODIFY field_name VARCHAR(100) NOT NULL;
在表開頭添加一個字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL FIRST;
在表結尾添加一個字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL;
在某個字段之后添加一個字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL AFTER field_x;
刪除表中的一個字段 ALTER TABLE table_name DROP field_name;
創建索引 CREATE INDEX index_name table_name (column_name);
刪除索引 ALTER TABLE table_name DROP INDEX index_name;
創建表分區：
create table kanzher_rss_article_(
aid bigint not null,
rid bigint not null
)
partition by range(aid) (
partition p0 values less than (2000000),
partition p1 values less than (4000000),
partition p2 values less than (6000000),
partition p3 values less than (8000000),
partition p4 values less than (10000000),
partition p5 values less than (12000000),
partition p6 values less than (14000000),
partition p7 values less than (16000000),
partition p8 values less than (18000000),
partition p9 values less than (20000000)
);
數據庫同步設置
設置MASTER表
1，設置從表復制權限：
GRANT ALL PRIVILEGES ON *.* TO?'root'@'%'?IDENTIFIED BY 'xjjgooweb1234' WITH GRANT OPTION;
或者：GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO?slave@192.168.1.2?IDENTIFIED BY 'password';
2，設置my.ini(my.cnf)：
[mysqld]
server-id = 1 #不能和Slave端設置重名
log-bin = proxy-bin #設置日志名，從該處同步到Slave端。
binlog-do-db = proxy #設置同步的數據庫名
3，重啟mysql Master數據庫
4，查看 Master 數據庫狀態 show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| proxy-bin.000001 | 106 | proxy | |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)
5，Master 數據庫已經正常啟動。
show binlog events; 查看復制日志
設置 SLAVE 表
6，設置my.ini(my.cnf)：
[mysqld]
server-id = 2
master-host = Master 服務器IP
master-user = user
master-password = password
master-port = 3306
master-connect-retry = 30 --斷線或者開機后重試間隔時間
replicate-do-db = proxy --slave 同步 master 的數據庫
7，刪除日志目錄下的master.info|relay-log.info文件，重啟 mysql Slave 數據庫
8，查看 slave 數據庫狀態
SHOW SLAVE STATUS\G;
其中 Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，表明 Slave 的 I/O 和 SQL 線程都在正常運行。
到此主從庫搭建成功。
1.主輔庫同步主要是通過二進制日志來實現同步的。?
2.在啟動輔庫的時候必須先把數據同步，并刪除日志目錄下的：master.info文件。因為master.info記錄了上次要連接主庫的信息，如果不刪除，即使my.cnf里進行了修改，也不起作用。因為讀取的還是master.info文件里的信息。
EXPLAIN
SET PROFILING = 1; 打開profiling 功能。
執行語句
查看profiles信息， SHOW PROFILES\G;
SHOW PROFILE CPU,BLOCK IO FOR QUERY 1;
本文轉自 知止內明 51CTO博客，原文鏈接：http://blog.51cto.com/357712148/1961966，如需轉載請自行聯系原作者
總結
以上是生活随笔為你收集整理的MySQL 性能优化及常用命令的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 用Core Temp查看服务器CPU温度
- 下一篇： nmon运行