關於MySQL的一些小見解

mchdba發表於2014-03-11
MySql
1.麻雀雖小五臟俱全，MySQL雖然以簡單著稱，但其內部結構並不簡單。相比於之前的版本，您覺得MySQL 5.5引入了哪些重要的新特性？如何做好SQL語句的最佳化？如何合理利用索引？

MySQL 5.5引入了哪些重要的新特性？
(1) 1. 預設儲存引擎更改為InnoDB。
(2) 多核效能提升
Metadata Locking (MDL) Framework替換LOCK_open mutex (lock)，使得MySQL5.1及過去版本在多核心處理器上的效能瓶頸得到解決，官方表示將繼續增強對MySQL多處理器支援，直至MySQL效能 “不受處理器數量的限制”
(3) 複製功能(Replication)加強
MySQL複製特性是網際網路公司應用非常廣泛的特性，作為MySQL最實用最簡單的擴充套件方式，過去的非同步複製方式已經有些不上形勢，對某些使用者 來說“非同步複製”意味著極端情況下的資料風險，MySQL5.5將首次支援半同步(semi-sync replication)在MySQL的高可用方案中將產生更多更加可靠的方案。另外Slave fsync tunning;Relay log corruption recovery和Replication Heartbeat也將實現
(4) 增強表分割槽功能
MySQL 5.5的分割槽對使用者絕對是個好訊息，更易於使用的增強功能，以及TRUNCATE PARTITION命令都可以為DBA節省大量的時間，有時對終端使用者亦如此:
        1) 非整數列分割槽：任何使用過MySQL分割槽的人應該都遇到過不少問題，特別是面對非整數列分割槽時，MySQL 5.1只能處理整數列分割槽，如果你想在日期或字串列上進行分割槽，你不得不使用函式對其進行轉換。很麻煩，而MySQL 5.5中新增了兩類分割槽方法，RANG和LIST分割槽法，同時在新的函式中增加了一個COLUMNS關鍵詞。在MySQL 5.1中使用分割槽另一個讓人頭痛的問題是date型別(即日期列)，你不能直接使用它們，必須使用YEAR或TO_DAYS轉換這些列，但在MySQL 5.5中情況發生了很大的變化，現在在日期列上可以直接分割槽，並且方法也很簡單;

　　2) 多列分割槽：COLUMNS關鍵字現在允許字串和日期列作為分割槽定義列，同時還允許使用多個列定義一個分割槽;

　　3) 可用性增強：truncate分割槽。分割槽最吸引人的一個功能是瞬間移除大量記錄的能力，DBA都喜歡將歷史記錄儲存到按日期分割槽的分割槽表中，這樣可以定期 刪除過時的歷史資料。 但當你需要移除分割槽中的部分資料時，事情就不是那麼簡單了，刪除分割槽沒有問題，但如果是清空分割槽，就很頭痛了，要移除分割槽中的所有 資料，但需要保留分割槽本身，你可以：使用DELETE語句，但我們知道DELETE語句的效能都很差。使用DROP PARTITION語句，緊跟著一個EORGANIZE PARTITIONS語句重新建立分割槽，但這樣做比前一個方法的成本要高出許多。MySQL 5.5引入了TRUNCATE PARTITION，它和DROP PARTITION語句有些類似，但它保留了分割槽本身，也就是說分割槽還可以重複利用。TRUNCATE PARTITION應該是DBA工具箱中的必備工具;

　　4) 更多微調功能：TO_SECONDS：分割槽增強包有一個新的函式處理DATE和DATETIME列，使用TO_SECONDS函式，你可以將日期/時間列轉換成自0年以來的秒數，如果你想使用小於1天的間隔進行分割槽，那麼這個函式就可以幫到你。


(5)Insert Buffering 如果在buffer pool中沒找到資料，那麼直接buffer起來，避免額外的IO;Delete & Purge Buffering 跟插入一樣，如果buffer pool中沒有命中，先buffer起來，避免額外的IO。

(6) Support for Native AIO on Linux


如何做好SQL語句的最佳化？
(1)欄位型別儘量用int或者tinyint型別，另外varchar欄位儘量用''取代null。
(2)select * 儘量少用，你想要什麼欄位 就select 什麼欄位出來 不要老是用* 號！同理，只要一行資料時儘量使用 LIMIT 1
(3)儘量避免使用order by rand()，因為這個可能會導致mysql的災難。
(4)每個表應該設定一個ID主鍵，最好是int型別
(5)對於寫，儘量用簡單的sql，嚴禁批次insert，update，以及delete操作。
(6)分頁的時候，先查詢第一條的主鍵id比如是78654，然後再where之後用id>78654。
(7)不要用永久連結 mysql_pconnect()；除非你真的非常肯定你的程式不會發生意外，不然很可能也會導致你的mysql死掉。
(8)儘量用 union all 代替 union。
(9)儘量用exists取代in，用join取代子查詢。
(10)儘量優先最佳化高併發的 SQL，而不是執行頻率低某些“大”SQL。
(11)還可以透過慢查詢日誌分析，sql執行監控等手段去進一步篩選可最佳化的sql。


如何合理利用索引？
(1)不設定外來鍵，在經常進行join的欄位上建立索引
(2)在頻繁進行排序或分組(即進行group by或order by操作)的列上建立索引。
(3)在值唯一性越高的列上建立索引，比如唯一性最高的主鍵。
(4)如果group以及sort的列有多個，那麼就建立組合索引。
(5)使用percona的工具來check索引的有效性，如果失效就可以rebiuld它
(6)在欄位建立索引最後都可以自動加上主鍵id欄位(如果id是自增欄位)


2.淘寶為什麼要放棄Oracle，而選擇MySQL呢？大家平常都用MySQL來幹些什麼事情？
根據taobao內部DBA的訊息，主要是阿里巴巴想建立一套屬於自己的系統，不再依賴於外部服務提供商，mysql免費只是其中一部分原因並不是主要原因。
我們公司的Mysql用來做使用者帳號交易資料儲存還有cache和token快取。


3.在資料庫表丟失或損壞的情況下，備份你的資料庫是很重要的。如果發生系統崩潰，你肯定想能夠將你的表儘可能丟失最少的資料恢復到崩潰發生時的狀態。MySQL常見的備份有四種，備份策略一：直接複製資料庫檔案；備份策略二：使用mysqlhotcopy備份資料庫；備份策略三：使用mysqldump備份資料庫；備份策略四：使用主從複製機制(replication)，請從資料庫規模以及應用的角度，談談您對這四種備份的認識。
首先解釋一個誤區，主從機制不適備份，這是資料冗餘的一種。

策略一和策略二適用於myisam儲存引擎。
策略三適用於所有儲存引擎，不過mysqldump會全鎖表，而且增量備份只能結合binlog來做。
策略四其實不是備份的一種，因為前3種都可以給予策略四上的從庫來操作。

個人覺得策略四應該說實時線上備份機制比如mydumper以及xtrabackup等





4.InnoDB給MySQL提供了具有提交，回滾和崩潰恢復能力的事務安全（ACID相容）儲存引擎。InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多使用者部署和效能。而MyISAM 是MySQL中預設的儲存引擎，它基於更老的ISAM程式碼，但有很多有用的擴充套件。您覺得InnoDB和MyISAM有哪些區別？在選擇儲存引擎上需要考慮哪些問題？
他們的主要區別在於，
(1)innodb支援事務而myisam不支援事務
(2)還有一個行鎖一個表鎖
(3)InnoDB 中不儲存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行, select count(*) from table,MyISAM只要簡單的讀出儲存好的行數，注意的是，當count(*)語句包含   where條件時，兩種表的操作是一樣的
(4) 對AUTO_INCREMENT的操作， MyISAM為INSERT和UPDATE操作自動更新這一列。對於AUTO_INCREMENT型別的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。
(5) 構成上區別，每個MyISAM在磁碟上儲存成三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案型別，基於磁碟的資源是InnoDB表空間資料檔案和它的日誌檔案，InnoDB 表的大小隻受限於作業系統檔案的大小，一般為 2GB(據說在64位上不一樣)。

選擇上：根據業務來，一般對資料一致性有要求的電子商務以及遊戲行業都用innodb，而移動網際網路，小規模金融業務等對一致性要求不高的可以採用myisam。



5.談談試讀《MySQL管理之道：效能調優、高可用與監控》章節後您的感想。

innodb_io_capacity，這個引數可以對IO產生效果，回去嘗試一下。
200    單盤SAS/SATA
2000   SAS*12 RAID 10
5000   SSD
500000 FUSION-IO

這一章，可關閉自適應雜湊索引，這個寫的比較好，還有半同步這個比較不錯，裡面有幾個效能圖，根據效能圖來分析，詳細生動。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26230597/viewspace-1107312/，如需轉載，請註明出處，否則將追究法律責任。

相關文章