高性能MYSQL

來自：http://ffffound.sinaapp.com/2015/04/08/高性能mysql/

結構和歷史

1. 隔離級別有四種：
   READ UNCOMMITTED（未提交讀），同事務中某個語句的修改，即使沒有提交，對其他事務也是可見的。這個也叫臟讀。
   READ COMMITTED（提交讀），另一個事務只能讀到該事務已經提交的修改，是大多數據庫默認的隔離級別。但是有下列問題，一個事務中兩次讀取同一個數據， 由于這個數據可能被另一個事務提交了兩次，所以會出現兩次不同的結果，所以這個級別又叫做不可重復讀。這里的不一樣的數據包括虛讀（兩次結果不同）和幻讀 （出現新的或者缺少了某數據）。
   REPEATABLE READ（可重復讀），這個級別不允許臟讀和不可重復讀，比如MYSQL中通過MVCC來實現解決幻讀問題。
   SERIALIABLE（可串行化），這兒實現了讀鎖，級別最高。
2. 顯示和隱式鎖定：事務執行中，隨時可以執行鎖定，鎖只有在COMMIT或ROLLBACK的時候才釋放，而且所有的鎖是同時釋放的。這些鎖定都是隱式鎖定。也可以通過特定語句顯式鎖定，比如SELECT … LOCK IN SHARE MODE等。
3. MVCC（多版本并發控制）：通過保存數據在某個時間點的快照來實現。在INNODB中通過每行記錄后保存兩個隱藏的列，一個保存行的創建時間，一個保存行的過期（刪除）時間，這兒的保存不是時間而是系統版本號，隨著事務的數量增加而增加版本號。
   SELECT:只找版本號早于當前事務版本的數據，刪除版本要大于當前版本號。
   INSERT：插入時保存當前版本號為行版本號。
   DELETE：為刪除的每行保存當前版本號為行的刪除標示。
   UPDATE：先為插入的行保存版本號，同時保存當前版本號為行刪除標示。
4. INNODB通過MVCC來支持高并發，通過間隙鎖來防止幻讀。
5. MYISAM支持讀取的時候插入（并發插入），支持延遲更新索引鍵（Delayed Key Write），先寫內容最后才更新索引，需要指定DELAY_KEY_WRITE。

SCHEMA與數據類型優化

1. 避免使用NULL。
2. 整數類型中，TINYINT使用8位存儲空間，BIGINT為64位，一般做SIMHASH選擇64位做特征值應該是基于這個，轉成16進制有16位。其中指定的寬度只在命令行中展示時起作用。
3. 實數類型中，DECIMAL用于存儲精確的小數，比如貨幣。
4. VARCHAR比定長CHAR更省空間，因為它只需要使用必要的空間，但是其需要使用1或者2個額外字節用來記錄字符串的長度。但是在update的時候，容易造成碎片。
   CHAR是定長的，MYSQL根據定義字符串的長度分配空間，而且其會刪除所有末尾空格。比如存”STRING     “的時候，末尾的空格會被刪除。
   VARCHAR(5)和VARCHAR(100)存同一個字符雖然空間開銷相同，但是在存的時候會消耗更多內存，還有在使用臨時表的時候也會比較糟糕。
5. BLOB和TEXT是為存儲很大數據而設計的，分別以二進制和字符方式存儲。TEXT是SMALLTEXT的同義詞，BLOB也是。
6. ENUM類型存儲是非常緊湊，其實際存儲為整數。
7. BIT可以在一列中存儲一個或多個0/1值，最大長度為64。問題是存進去是二進制，但是展示出來卻是十進制的。
8. 計數器表的優化，對于單表的a+1操作可能受到鎖的影響，可以通過創建100行數據，然后隨機選取一行寫，取的時候使用SUM(a)進行查詢。
9. 高效ALTER TABLE，修改表結構涉及到不需要改變數據只要改frm文件的時候，可以使用語句ALTER COLUMN來操作。
   還有替換frm的高效方法，首先create table like來建立新表，修改新表結構，對舊表數據執行鎖定”FLUSH TABLES WITH READ LOCK;”
   執行系統命令，mv new.frm a.frm之類，記得備份。
   UNLOCK TABLES;
10. 高效載入數據到MyISAM表，可以暫時禁用索引。
    ALTER TABLE tab DISABLE KEYS;
    ALTER TABLE tab ENABLE KEYS;
    但是DISABLE KEYS只對非唯一索引有效。

創建高性能的索引

1. B-Tree索引，其意味著所有的值都是按照順序存儲的，并且每一個葉子頁到根的距離都相等。
   B-Tree對索引列是順序存儲的，所以很適合查找范圍數據。
   缺點是必須按照索引從最左列開始查找，否則無法使用索引。
2. R-Tree（空間數據索引），MyISAM表支持空間索引，可以用作地理數據存儲。
3. 獨立的列無法使用索引，獨立的列是指索引列為表達式的一部分或者函數的參數。
4. 前綴索引，索引很長的字符列會讓索引變大變慢，所以選擇一個合適的長度來索引是很有效率的。
   首先需要找出合適長度的前綴，用語句：
   select count(*) as cnt,LEFT(city,3) as pref from group by pref order by cnt;
   調整其中LEFT函數的值選擇最合適的長度。建索引時如下：
   ALTER TABLE a ADD KEY(city(7))；
   還可以考慮后綴索引，比如查找某個域名的所有電子郵件地址，需要把字符串翻轉后存儲。
5. 多列索引的順序非常重要，要選擇最有效率的列放到最左邊。
6. 聚族索引并不是一種單獨的索引類型，而是一種數據存儲的方式。
   當表有聚簇索引時，它的數據行實際上存放在索引的葉子頁(LEAF PAGE)中，聚簇表示數據行和相鄰的鍵值緊湊地存儲在一起。
7. 當存在OR條件的時候，會看到此時使用了index_merge類型索引，這個說明表上的索引很糟糕，這個是由于在OR左右兩個條件都建立了索引，應該修改索引，或者使用IGNORE INDEX來會略某些索引。
8. 在選擇多列索引的時候，通常把選擇性更大的放到前面(該條件下統計數量更小的)。
9. 在INNODB中最好使用自增作為主鍵，而使用UUID等隨機的聚簇索引會對I/O密集型應用造成很壞性能，它使得聚簇索引的插入變得完全隨機。
10. 當要查詢的字段的值在索引中，就稱該索引為覆蓋索引。在explain的時候extra顯示using index。為了能用到覆蓋索引，可以使用延遲關聯（deferred join）。書上有很巧妙的例子：）。注意的是，INNODB中二級索引的葉子節點都包含了主鍵的值，所以查詢的值包含主鍵id時，主鍵id可以不在所建 的聯合索引中。關于延遲關聯還有個經典例子，大偏移翻頁的時候。
11. 當索引類型為index時，說明MYSQL使用了索引掃描來做排序。
12. 在5.1或更新版本中，INNODB在服務器端過濾掉行后就釋放鎖，而早期版本中則需要在事務提交后才釋放鎖。
13. EXPLAIN中出現Using where表示在存儲引擎返回行后再使用where過濾條件。
14. 一個訣竅，一個符合查詢條件的多列索引中，有時候條件里沒有包含存在的索引列，這時候使用IN來滿足最左前綴。比如多列索引中有sex列，但是用戶查詢時沒有選擇sex，則使用IN(‘M’,’F’)來滿足使用索引的條件。
    某一些條件比如age，一般是范圍查詢，而根據最左前綴碰到范圍查詢后會終止，所以這類一般放在多列索引的最后面。
    而使用開始的IN語句滿足最左前綴也不能濫用，3個IN條件，每 個有N個枚舉值，則會產生N*N*N中組合，降低效率。
15. 按順序訪問范圍數據很快，因為順序I/O不需要多次磁盤尋道，不需要額外排序操作。
16. 聚簇索引(Clustered Index)，一個索引項直接對應實際數據記錄存儲頁。
    索引項和實際數據行的排序完全一樣。
    一個表只能有一個聚簇索引。但是該列能包含多個列，就像電話簿使用姓氏和名字同時進行排序。
17. INNODB支持聚簇索引，其中聚簇索引就是表，必須要像MYISAM那樣的行存儲。聚簇索引的每個葉子節點都包含了主鍵值、事務ID、用于事務和MVCC的回滾指針以及所有的剩余列。
    InnoDB的二級索引和聚簇索引很不相同。InnoDB二級索引的葉子節點中存儲的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。
    在INNODB主鍵中插入UUID，由于主鍵會保持有序，會嚴重影響性能。

查詢性能優化

1. 檢查響應時間，掃描的行和返回的行，掃描的行數和訪問類型（Explain的時候）是三個簡單衡量查詢的指標。
2. 在進行大查詢的時候使用分而治之，比如delete大數據的時候使用limit，使用do while分解操作，避免大語句鎖住過多數據，占滿事務日志，耗盡系統資源，阻塞很多重要查詢。
3. 關聯查詢拆成簡單查詢然后在應用層聚合數據，可以讓緩存效率更高，單個查詢可以減少鎖競爭，本身查詢效率也更高，在數據庫中做關聯查詢還可能導致需要重復地訪問一部分數據。
4. mysql客戶端和服務器之間的通信協議是半雙工，任何一個時刻只能單向發送數據而不能兩邊同時進行，像是拋繡球。所以mysql通常需要等所有數據都已經發送給客戶端后才能釋放這條查詢鎖占用的資源，這時max_allowed_packet很重要。
5. 一個完整查詢包含如下過程包括客戶端/服務器端通信->查詢緩存->語法解析器和預處理->查詢優化器->數據和索引的統計信息->查詢執行引擎->返回結果給客戶端。下面會一次說說每個步驟。
   
6. 查詢狀態，一個連接或者線程，在任何時刻都有一個狀態。
   sleep，線程正在等待客戶端發來新請求。
   query，線程正在執行查詢或者將結果發送給客戶端。
   locked，該線程正在等待表鎖。而存儲引擎級別的鎖比如innodb的行鎖并不會體現在線程狀態。
   copying to tmp table[on disk]，線程正在執行查詢并且將結果集都復制到一張臨時表，一般是group by或者文件排序等操作。on disk表示正在將一個內存臨時表放到磁盤上。
   sorting result，線程正在對結果集進行排序。
   sending data，線程可能在多個狀態間傳送數據，或者正在生成結果集或者正在向客戶端返回數據。
   了解這些狀態可以很快了解誰正在擲球。
7. 在查詢緩存后，先進行語法解析器和預處理，mysql通過關鍵字將SQL語句進行解析并生成一顆對應的解析樹，進行語法規則驗證。當語法樹被認為合法了，則由優化器將其轉化為執行計劃，一條語句可能有很多執行方式并返回相同結果，優化器的作用就是找到這其中最好的執行計劃。優化器是基于成本來預測。
8. 在很多數據庫中IN等同OR，但是在mysql中，會把IN中的數據先進行排序，然后通過二分查找的方式來確定列表中的值是否滿足條件，這是一個O(log n)的操作。當IN中有大量數據的時候效率會更快。
9. 關聯查詢，MySQL認為任何一次查詢都是一次關聯，不僅僅是UNION，子查詢等都可能是。對于UNION，MYSQL現將一系列查詢的單個查詢結果放到一個臨時表中，再重新讀出臨時表的數據來完成UNION查詢。
   MYSQL對任何關聯都執行嵌套循環關聯操作，即先在一個表中循環取出單條數據，然后嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止。
   當在FROM子句中遇到子查詢時，先執行子查詢并將其結果放到一個臨時表中，然后將這個臨時表當做一個普通表對待（派生表）。
10. 執行計劃，MYSQL生成查詢的一顆指令數。可以使用EXPLAIN EXTENDED后再使用WARNINGS。
    任何多表查詢都可以用一棵樹來表示，比如四表查詢：
    
    而事實上MYSQL總是從一個表開始一直嵌套循環，是一顆左側深度優先的樹。
    
11. 關聯查詢優化器，用來決定多個表關聯時的順序。
    由于其第二個是從第一個表的結果嵌套循環的，所以第一個表尤為重要，優化器會選擇掃描很少的行。
    如果有超過N各表的關聯，那么需要檢查N的階乘種關聯順序，這是很糟糕的。
12. 排序優化，如果order by子句中所有列來自第一個表，那么在處理第一個表是就會進行文件排序，會顯示Using filesort
    其余情況，都需要先把關聯結果放到一個臨時表中，最后再進行文件排序。會顯示Using filesort，Using temporary;
13. 關聯子查詢是一個爛查詢。select from IN(select id from where)
    因為優化器會改寫成select from where exists(select * from where AND a.id=b.id)
    所以子句需要主句的id才能查詢，這樣MYSQL會首先對主句進行全表掃描來找出所有id，然后使用id對子句進行逐個執行子查詢。
    所以方法一是：select * from a
    inner join b USING(id)
    where c=1;
    還有個方法，GROUP_CONCAT。
14. MYSQL目前不支持松散索引掃描，就是最左綴的一個原則，聯合索引沒法越過左邊的字段。雖然其聯合索引全部有序。
15. 在同一個表中進行查詢和更新，不能用update set a=(select from),要使用生成表的方式來繞過限制，update tb inner join(select) as b using(id) set tb.a=b.a,這樣會關聯到一個臨時表。
16. HINT查詢優化器的提示，
    HIGH_PRIORITY和LOW_PRIORITY，優先級設置。HIGH一般用于select語句，會將其放到隊列的最前面。LOW會讓語句一直處于等待狀態，只要隊列中還有需要訪問同一個表的語句。這兩個提示只在表鎖的存儲引擎中有效。
    DELAYED，對insert和replace有效，會將提示立即返回給客戶端，并將插入的行數放到緩沖區，然后在空閑時批量寫入。會導致LAST_INSERT_ID()失效。
    FOR UPDATE和LOCK IN SHARE MODE，主要控制SELECT語句的鎖機制，只對實現了行級鎖的存儲引擎有效。
17. count(*)中的*不是指所有的列，而是會忽略所有的列而直接統計行數。
    統計同一個列里不同顏色的商品語句，select sum(if(color=’blue’, 1, 0)) as blue,sum(if(color=’red’, 1, 0)) as red from items;
    其中也可以寫成sum(color=’blue’),sum(color=’red’)
    或者使用如下語句
    select count(color=’blue’ OR NULL) as blue, count(color=’red’ OR NULL) as red from items;
18. 關于大數據偏移的一個常用方法是延遲關聯，
    select from a inner join(select id from limit 100,10) as b using(id);
    或者計算出具體位置使用between，
    OFFSET會導致MYSQL掃描大量不需要的行并拋棄，避免使用OFFSET，比如計算出主鍵的id，使用
    select from a where id<2131231 order by id desc limit 20;
19. MYSQL總是通過創建并填充臨時表的方式來執行UNION查詢。

MySQL高級特性

1. 分區表：是一個獨立的邏輯表，底層是由多個物理子表組成。索引也是按照分區的字表定義的，沒有全局索引。
   在創建的時候使用PARTITION BY子句定義每個分區存放的數據。在執行查詢的時候優化器會根據分區定義過濾那些沒有我們需要數據的分區，這樣查詢就無需掃描所有分區。
   CREATE TABLE sales(date DATETIME NOT NULL,…) ENGINE=InnoDB PARTITION BY RANGE(YEAR(date))(
   PARTITION p_2010 VALUES LESS THAN (2010),
   PARTITION p_2011 VALUES LESS THAN (2011),
   PARTITION p_catchall VALUES LESS THAN MAXVALUE);
2. 查詢緩存可以更快得返回數據，但是高并發下可能成為整個服務器的資源競爭點，多核服務器上還可能導致服務器僵死。所以很多時候我們認為應該默認關閉查詢緩存。
   在判斷緩存是否命中的時候，MySQL不會解析格式化查詢語句，而是直接使用SQL語句和一些客戶端的信息，比如空格，注釋等任何的不同都會導致緩存的不命中。
   當語句中存在不確定數據比如NOW()等函數時都不會被緩存。
   當打開查詢緩存時，每個讀查詢都會先檢查是否命中緩存，寫入緩存的時候會帶來系統消耗，當某個表有變動，則對應表的所有緩存都將設置失效，當查詢緩存太大或者碎片太多，都會帶來很大系統消耗。
   所以使用查詢緩存時要小心，不要設置太大內存，而且要在確定有明確收益的時候才使用查詢緩存。

優化服務器設置

操作系統和硬件優化

1. 存儲引擎通常把數據和索引都保存在一個大文件中，這意味著RAID（磁盤冗余陣列）存儲大量數據通常是最可行的方法。
   RAID 0
   這是成本最低和性能最高的RAID配置，因為其沒有冗余，建議只在不擔心數據丟失的情況下使用。而且其的損壞概率比單塊磁盤還要高。
   RAID 1
   提供不錯的讀性能，而且在不同磁盤間冗余數據，所以有很好的冗余性。
   RAID 5
   通過分布奇偶校驗把數據分布到多個磁盤，這樣任何一個盤的數據失效，都可以從奇偶校驗中重建。但是如果有兩塊磁盤失效了，則整個卷數據都無法恢復。就每個存儲單元的成本而言，這是最經濟的，因為只額外消耗了一塊磁盤的存儲空間。
   RAID 10
   他由分片的鏡像組成，所以對讀和寫都有良好的擴展性。
   RAID 15
   由條帶化的RAID5組成。用于存放非常龐大的數據集。
2. vmstat和iostat

復制

1. 復制解決的基本問題是讓一臺服務器的數據與其他服務器保持同步。
2. MYSQL支持兩種復制方式：基于行的復制和基于語句的復制。基于語句的復制也叫邏輯復制，很早就存在。基于行的復制5.1版才加進來。這兩種方 式都是通過在主庫記錄二進制日志，在備庫重放日志的方式來實現異步的數據復制。這意味著同一時間點備庫上的數據可能和主庫存在不一致。
3. 復制通常不會增加主庫的開銷，主要是啟用二進制日志帶來的開銷。

可擴展的MySQL

1. 向上擴展：購買更多性能強悍的硬件。
2. 向外擴展，分三部分：復制，拆分以及數據分片。
3. 復制可分為按功能拆分，比如分為論壇、新聞等。
   數據分片，全局數據存儲在單點上，而對同功能下的數據增長過多的時候進行分片。比如對用戶表進行分片。
4. 生成全局唯一的ID
   比如分片后，使用AUTO_INCREMENT來獲取唯一ID會有問題。
   首先可以使用auto_increment_increment和auto_increment_offset兩個變量來讓MYSQL以期望的值和偏移量 增加自增列的值。比如兩臺服務器，可以配置兩臺服務器的自增幅度為2，其中一臺偏移為1一臺為2.所以其中一臺總是奇數一臺總是偶數。
   還可以在一個全局數據庫中創建自增來生成唯一數字。
   還能從全局節點中請求一批數字，用完再申請。
   還可以使用分片號和自增的組合來作為唯一ID。
   可以使用UUID()來生成全局唯一值。因為其值很大且不連續，因此不適合做innodb的主鍵。這時可以考慮UUID_SHORT()，能生成連續的值，并使用64位代替128位。
5. 通過多實例擴展。
6. 通過集群擴展。
7. 向內擴展，比如對不需要的數據進行歸檔和清理。

高可用性

云端的MySQL

應用層優化

備份與恢復

1. 熱備份指備份不需要任何的停機服務。
2. 邏輯備份指導出，物理備份指復制原始文件。
3. 差異備份是對自上次全備份后所有改變的部分而做的備份，增量備份則是字從任何類型的上次備份后所有的修改做的備份。
4. 生成邏輯備份:
   mysqldump test t1
   select into outfile以符號分割文件格式創建數據的邏輯備份。
5. 文件系統快照。
   快照會在/dev目錄下創建一個新的邏輯卷，可以像掛載其他設備一樣掛載它。
6. 備份腳本化