[TOC]

MySQL索引和SQL調(diào)優(yōu)

本文有參考網(wǎng)上其他相關(guān)文章，本文最后有附參考的鏈接

MySQL索引

MySQL支持諸多存儲引擎，而各種存儲引擎對索引的支持也各不相同复亏，因此MySQL數(shù)據(jù)庫支持多種索引類型类腮，如BTree索引做入，哈希索引冒晰，全文索引等等。為了避免混亂竟块，本文將只關(guān)注于BTree索引壶运，因為這是平常使用MySQL時主要打交道的索引。

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)浪秘。提取句子主干蒋情，就可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。

MySQL索引原理

索引目的

索引的目的在于提高查詢效率耸携，可以類比字典棵癣，如果要查“mysql”這個單詞，我們肯定需要定位到m字母夺衍，然后從下往下找到y(tǒng)字母狈谊，再找到剩下的sql。如果沒有索引沟沙，那么你可能需要把所有單詞看一遍才能找到你想要的河劝，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢尝胆？是不是覺得如果沒有索引丧裁，這個事情根本無法完成？

咱們?nèi)D書館借書也是一樣含衔，如果你要借某一本書，一定是先找到對應(yīng)的分類科目二庵，再找到對應(yīng)的編號贪染，這是生活中活生生的例子，通用索引催享，可以加快查詢速度杭隙，快速定位。

索引原理

所有索引原理都是一樣的因妙，通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果痰憎，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數(shù)據(jù)攀涵。

數(shù)據(jù)庫也是一樣铣耘，但顯然要復(fù)雜許多，因為不僅面臨著等值查詢以故，還有范圍查詢(>蜗细、<、between、in)炉媒、模糊查詢(like)踪区、并集查詢(or)等等。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對所有的問題呢吊骤？我們回想字典的例子缎岗，能不能把數(shù)據(jù)分成段，然后分段查詢呢白粉？最簡單的如果1000條數(shù)據(jù)传泊，1到100分成第一段，101到200分成第二段蜗元，201到300分成第三段……這樣查第250條數(shù)據(jù)或渤，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)奕扣。但如果是1千萬的記錄呢薪鹦，分成幾段比較好？稍有算法基礎(chǔ)的同學會想到搜索樹惯豆，其平均復(fù)雜度是lgN池磁，具有不錯的查詢性能。但這里我們忽略了一個關(guān)鍵的問題楷兽，復(fù)雜度模型是基于每次相同的操作成本來考慮的地熄，數(shù)據(jù)庫實現(xiàn)比較復(fù)雜，數(shù)據(jù)保存在磁盤上芯杀，而為了提高性能端考，每次又可以把部分數(shù)據(jù)讀入內(nèi)存來計算，因為我們知道訪問磁盤的成本大概是訪問內(nèi)存的十萬倍左右揭厚，所以簡單的搜索樹難以滿足復(fù)雜的應(yīng)用場景却特。

索引結(jié)構(gòu)

任何一種數(shù)據(jù)結(jié)構(gòu)都不是憑空產(chǎn)生的，一定會有它的背景和使用場景筛圆，我們現(xiàn)在總結(jié)一下裂明，我們需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么，其實很簡單太援，那就是：每次查找數(shù)據(jù)時把磁盤IO次數(shù)控制在一個很小的數(shù)量級闽晦，最好是常數(shù)數(shù)量級。那么我們就想到如果一個高度可控的多路搜索樹是否能滿足需求呢提岔？就這樣仙蛉，b+樹應(yīng)運而生肮之。

b+樹的索引結(jié)構(gòu)解釋

b+樹.jpg

淺藍色的塊我們稱之為一個磁盤塊解虱，可以看到每個磁盤塊包含幾個數(shù)據(jù)項（深藍色所示）和指針（黃色所示），如磁盤塊1包含數(shù)據(jù)項17和35铐达，包含指針P1、P2巧还、P3鞭莽，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊麸祷，P3表示大于35的磁盤塊澎怒。真實的數(shù)據(jù)存在于葉子節(jié)點即3、5阶牍、9喷面、10、13走孽、15惧辈、28、29磕瓷、36盒齿、60、75困食、79边翁、90、99硕盹。非葉子節(jié)點不存儲真實的數(shù)據(jù)符匾，只存儲指引搜索方向的數(shù)據(jù)項，如17瘩例、35并不真實存在于數(shù)據(jù)表中啊胶。

b+樹的查找過程

如圖所示，如果要查找數(shù)據(jù)項29垛贤，那么首先會把磁盤塊1由磁盤加載到內(nèi)存创淡，此時發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間南吮，鎖定磁盤塊1的P2指針，內(nèi)存時間因為非常短（相比磁盤的IO）可以忽略不計誊酌，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存部凑，發(fā)生第二次IO，29在26和30之間碧浊，鎖定磁盤塊3的P2指針涂邀，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO箱锐，同時內(nèi)存中做二分查找找到29比勉，結(jié)束查詢，總計三次IO。真實的情況是浩聋，3層的b+樹可以表示上百萬的數(shù)據(jù)观蜗，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的衣洁，如果沒有索引墓捻，每個數(shù)據(jù)項都要發(fā)生一次IO，那么總共需要百萬次的IO坊夫，顯然成本非常非常高砖第。

b+樹性質(zhì)

1. 通過上面的分析，我們知道間越小环凿，數(shù)據(jù)項的數(shù)量越多梧兼，樹的高度越低。這就是為什么每個數(shù)據(jù)項智听，即索引字段要盡量的小羽杰，比如int占4字節(jié)，要比bigint8字節(jié)少一半瞭稼。這也是為什么b+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是內(nèi)層節(jié)點忽洛，一旦放到內(nèi)層節(jié)點，磁盤塊的數(shù)據(jù)項會大幅度下降环肘，導致樹增高欲虚。當數(shù)據(jù)項等于1時將會退化成線性表。

2. 當b+樹的數(shù)據(jù)項是復(fù)合的數(shù)據(jù)結(jié)構(gòu)悔雹，比如(name,age,sex)的時候复哆，b+數(shù)是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數(shù)據(jù)來檢索的時候腌零，b+樹會優(yōu)先比較name來確定下一步的所搜方向梯找，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)益涧；但當(20,F)這樣的沒有name的數(shù)據(jù)來的時候锈锤，b+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子闲询，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢久免。比如當(張三,F)這樣的數(shù)據(jù)來檢索時，b+樹可以用name來指定搜索方向扭弧，但下一個字段age的缺失阎姥，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了鸽捻， 這個是非常重要的性質(zhì)呼巴，即索引的最左匹配特性泽腮。

MySQL 索引實現(xiàn)

在MySQL中，索引屬于存儲引擎級別的概念衣赶，不同存儲引擎對索引的實現(xiàn)方式是不同的诊赊，本文主要討論MyISAM和InnoDB兩個存儲引擎的索引實現(xiàn)方式。

MyISAM索引實現(xiàn)

MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)屑埋，葉節(jié)點的data域存放的是數(shù)據(jù)記錄的地址豪筝。
下圖是MyISAM索引的原理圖：

MyISAM_Primarykey.png

這里設(shè)表一共有三列，假設(shè)我們以Col1為主鍵摘能，則上圖便是一個MyISAM表的主索引（Primary key）示意圖续崖。可以看出MyISAM的索引文件僅僅保存數(shù)據(jù)記錄的地址团搞。在MyISAM中严望，主索引和輔助索引（Secondary key）在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的逻恐，而輔助索引的key可以重復(fù)像吻。如果我們在Col2上建立一個輔助索引，則此索引的結(jié)構(gòu)如下圖所示：

MyISAM_Secondarykey.png

同樣也是一顆B+Tree复隆，data域保存數(shù)據(jù)記錄的地址拨匆。因此，MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引挽拂，如果指定的Key存在惭每，則取出其data域的值，然后以data域的值為地址亏栈，讀取相應(yīng)數(shù)據(jù)記錄台腥。

MyISAM的索引方式也叫做“非聚集”的，之所以這么稱呼是為了與InnoDB的聚集索引區(qū)分绒北。

InnoDB索引實現(xiàn)

雖然InnoDB也使用B+Tree作為索引結(jié)構(gòu)黎侈，但具體實現(xiàn)方式卻與MyISAM截然不同。

第一個重大區(qū)別是InnoDB的數(shù)據(jù)文件本身就是索引文件闷游。從上文知道峻汉，MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址脐往。而在InnoDB中俱济，表數(shù)據(jù)文件本身就是按B+Tree組織的一個索引結(jié)構(gòu)，這棵樹的葉節(jié)點data域保存了完整的數(shù)據(jù)記錄钙勃。這個索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引聂喇。

InnoDB_Primarykey.png

上圖是InnoDB主索引（同時也是數(shù)據(jù)文件）的示意圖辖源，可以看到葉節(jié)點包含了完整的數(shù)據(jù)記錄蔚携。這種索引叫做聚集索引。因為InnoDB的數(shù)據(jù)文件本身要按主鍵聚集克饶，所以InnoDB要求表必須有主鍵（MyISAM可以沒有）酝蜒，如果沒有顯式指定，則MySQL系統(tǒng)會自動選擇一個可以唯一標識數(shù)據(jù)記錄的列作為主鍵矾湃，如果不存在這種列亡脑，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節(jié)邀跃，類型為長整形霉咨。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應(yīng)記錄主鍵的值而不是地址。換句話說拍屑，InnoDB的所有輔助索引都引用主鍵作為data域途戒。例如，下圖為定義在Col3上的一個輔助索引：

InnoDB_Secondarykey.png

這里以英文字符的ASCII碼作為比較準則僵驰。聚集索引這種實現(xiàn)方式使得按主鍵的搜索十分高效喷斋，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄蒜茴。

了解不同存儲引擎的索引實現(xiàn)方式對于正確使用和優(yōu)化索引都非常有幫助星爪，例如知道了InnoDB的索引實現(xiàn)后，就很容易明白為什么不建議使用過長的字段作為主鍵粉私，因為所有輔助索引都引用主索引顽腾，過長的主索引會令輔助索引變得過大。再例如毡鉴，用非單調(diào)的字段作為主鍵在InnoDB中不是個好主意崔泵，因為InnoDB數(shù)據(jù)文件本身是一顆B+Tree，非單調(diào)的主鍵會造成在插入新記錄時數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整猪瞬，十分低效憎瘸，而使用自增字段作為主鍵則是一個很好的選擇。

如何建立合適的索引

建立索引的原理

一個最重要的原則是最左前綴原理陈瘦，在提這個之前要先說下聯(lián)合索引幌甘，MySQL中的索引可以以一定順序引用多個列，這種索引叫做聯(lián)合索引痊项，一般的锅风，一個聯(lián)合索引是一個有序元組<a1, a2, …, an>，其中各個元素均為數(shù)據(jù)表的一列鞍泉。另外皱埠，單列索引可以看成聯(lián)合索引元素數(shù)為1的特例。

索引匹配的最左原則具體是說咖驮，假如索引列分別為A边器，B训枢，C，順序也是A忘巧，B恒界，C：

- 那么查詢的時候，如果查詢【A】【A砚嘴，B】 【A十酣，B，C】际长，那么可以通過索引查詢
- 如果查詢的時候耸采，采用【A，C】也颤，那么C這個雖然是索引洋幻，但是由于中間缺失了B，因此C這個索引是用不到的翅娶，只能用到A索引
- 如果查詢的時候文留，采用【B】 【B，C】 【C】竭沫，由于沒有用到第一列索引燥翅，不是最左前綴，那么后面的索引也是用不到了
- 如果查詢的時候蜕提，采用范圍查詢森书，并且是最左前綴，也就是第一列索引谎势，那么可以用到索引凛膏，但是范圍后面的列無法用到索引

因為索引雖然加快了查詢速度，但索引也是有代價的：索引文件本身要消耗存儲空間脏榆，同時索引會加重插入猖毫、刪除和修改記錄時的負擔，另外须喂，MySQL在運行時也要消耗資源維護索引吁断，因此索引并不是越多越好

在使用InnoDB存儲引擎時，如果沒有特別的需要坞生，請永遠使用一個與業(yè)務(wù)無關(guān)的自增字段作為主鍵仔役。如果從數(shù)據(jù)庫索引優(yōu)化角度看，使用InnoDB引擎而不使用自增主鍵絕對是一個糟糕的主意是己。

InnoDB使用聚集索引又兵，數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點上。這就要求同一個葉子節(jié)點內(nèi)（大小為一個內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放卒废，因此每當有一條新的記錄插入時寒波，MySQL會根據(jù)其主鍵將其插入適當?shù)墓?jié)點和位置乘盼，如果頁面達到裝載因子（InnoDB默認為15/16），則開辟一個新的頁（節(jié)點）俄烁。如果表使用自增主鍵，那么每次插入新的記錄级野，記錄就會順序添加到當前索引節(jié)點的后續(xù)位置页屠，當一頁寫滿，就會自動開辟一個新的頁蓖柔。如下：

auto_Primarykey.png

這樣就會形成一個緊湊的索引結(jié)構(gòu)辰企，近似順序填滿。由于每次插入時也不需要移動已有數(shù)據(jù)况鸣，因此效率很高牢贸，也不會增加很多開銷在維護索引上。

如果使用非自增主鍵（如果身份證號或?qū)W號等）镐捧，由于每次插入主鍵的值近似于隨機潜索，因此每次新紀錄都要被插到現(xiàn)有索引頁得中間某個位置，如下：

random_Primarykey.png

此時MySQL不得不為了將新記錄插到合適位置而移動數(shù)據(jù)懂酱，甚至目標頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉竹习，此時又要從磁盤上讀回來，這增加了很多開銷列牺，同時頻繁的移動整陌、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)瞎领，后續(xù)不得不通過OPTIMIZE TABLE來重建表并優(yōu)化填充頁面泌辫。

因此，只要可以九默，請盡量在InnoDB上采用自增字段做主鍵震放。

建立索引的常用技巧

1. 最左前綴匹配原則，非常重要的原則荤西，mysql會一直向右匹配直到遇到范圍查詢(>澜搅、<、between邪锌、like)就停止匹配勉躺，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的觅丰，如果建立(a,b,d,c)的索引則都可以用到饵溅，a,b,d的順序可以任意調(diào)整。

2. =和in可以亂序妇萄，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序蜕企，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式

3. 盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)咬荷，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少轻掩，唯一鍵的區(qū)分度是1幸乒，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0唇牧，那可能有人會問罕扎，這個比例有什么經(jīng)驗值嗎？使用場景不同丐重，這個值也很難確定腔召，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄

4. 索引列不能參與計算扮惦，保持列“干凈”臀蛛，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單崖蜜，b+樹中存的都是數(shù)據(jù)表中的字段值浊仆，但進行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較纳猪，顯然成本太大氧卧。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);

5. 盡量的擴展索引，不要新建索引氏堤。比如表中已經(jīng)有a的索引沙绝，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可鼠锈，當然要考慮原有數(shù)據(jù)和線上使用情況

MySQL優(yōu)化

配置優(yōu)化

配置優(yōu)化指的MySQL 的 server端的配置闪檬，一般對于業(yè)務(wù)方而言，可以不用關(guān)注购笆，畢竟會有專門的DBA來處理粗悯，但是對于原理的了解，我想同欠，我們開發(fā)样傍，是需要了解的

基本配置

• innodb_buffer_pool_size
  • 這是安裝完InnoDB后第一個應(yīng)該設(shè)置的選項。緩沖池是數(shù)據(jù)和索引緩存的地方：這個值越大越好铺遂，這能保證你在大多數(shù)的讀取操作時使用的是內(nèi)存而不是硬盤衫哥。典型的值是5-6GB(8GB內(nèi)存)，20-25GB(32GB內(nèi)存)襟锐，100-120GB(128GB內(nèi)存)撤逢。
• innodb_log_file_size
  • 這是redo日志的大小。redo日志被用于確保寫操作快速而可靠并且在崩潰時恢復(fù)。一直到MySQL 5.1蚊荣，它都難于調(diào)整初狰，因為一方面你想讓它更大來提高性能，另一方面你想讓它更小來使得崩潰后更快恢復(fù)互例。幸運的是從MySQL 5.5之后奢入，崩潰恢復(fù)的性能的到了很大提升，這樣你就可以同時擁有較高的寫入性能和崩潰恢復(fù)性能了俊马。一直到MySQL 5.5肩杈，redo日志的總尺寸被限定在4GB(默認可以有2個log文件)解寝。這在MySQL 5.6里被提高了。如果你知道你的應(yīng)用程序需要頻繁的寫入數(shù)據(jù)并且你使用的時MySQL 5.6夫偶，你可以一開始就把它這是成4G。
• max_connections
  • 如果你經(jīng)潮＃看到‘Too many connections'錯誤逾礁，是因為max_connections的值太低了说铃。這非常常見因為應(yīng)用程序沒有正確的關(guān)閉數(shù)據(jù)庫連接，你需要比默認的151連接數(shù)更大的值嘹履。max_connection值被設(shè)高了(例如1000或更高)之后一個主要缺陷是當服務(wù)器運行1000個或更高的活動事務(wù)時會變的沒有響應(yīng)腻扇。在應(yīng)用程序里使用連接池或者在MySQL里使用進程池有助于解決這一問題。

InnoDB配置

• innodb_file_per_table
  • 這項設(shè)置告知InnoDB是否需要將所有表的數(shù)據(jù)和索引存放在共享表空間里（innodb_file_per_table = OFF） 或者為每張表的數(shù)據(jù)單獨放在一個.ibd文件（innodb_file_per_table = ON）砾嫉。每張表一個文件允許你在drop幼苛、truncate或者rebuild表時回收磁盤空間。這對于一些高級特性也是有必要的焕刮，比如數(shù)據(jù)壓縮舶沿。但是它不會帶來任何性能收益。你不想讓每張表一個文件的主要場景是：有非常多的表（比如10k+）配并。MySQL 5.6中括荡，這個屬性默認值是ON，因此大部分情況下你什么都不需要做荐绝。對于之前的版本你必需在加載數(shù)據(jù)之前將這個屬性設(shè)置為ON一汽，因為它只對新創(chuàng)建的表有影響。
• innodb_flush_log_at_trx_commit
  • 默認值為1，表示InnoDB完全支持ACID特性召夹。當你的主要關(guān)注點是數(shù)據(jù)安全的時候這個值是最合適的岩喷，比如在一個主節(jié)點上。但是對于磁盤（讀寫）速度較慢的系統(tǒng)监憎，它會帶來很巨大的開銷纱意，因為每次將改變flush到redo日志都需要額外的fsyncs。將它的值設(shè)置為2會導致不太可靠（reliable）因為提交的事務(wù)僅僅每秒才flush一次到redo日志鲸阔，但對于一些場景是可以接受的偷霉，比如對于主節(jié)點的備份節(jié)點這個值是可以接受的。如果值為0速度就更快了褐筛，但在系統(tǒng)崩潰時可能丟失一些數(shù)據(jù)：只適用于備份節(jié)點类少。
• innodb_flush_method
  • 這項配置決定了數(shù)據(jù)和日志寫入硬盤的方式。一般來說渔扎，如果你有硬件RAID控制器，并且其獨立緩存采用write-back機制残吩，并有著電池斷電保護泣侮，那么應(yīng)該設(shè)置配置為O_DIRECT；否則酬凳，大多數(shù)情況下應(yīng)將其設(shè)為fdatasync（默認值）宁仔。sysbench是一個可以幫助你決定這個選項的好工具翎苫。
• innodb_log_buffer_size
  • 這項配置決定了為尚未執(zhí)行的事務(wù)分配的緩存煎谍。其默認值（1MB）一般來說已經(jīng)夠用了呐粘，但是如果你的事務(wù)中包含有二進制大對象或者大文本字段的話唆垃，這點緩存很快就會被填滿并觸發(fā)額外的I/O操作辕万〗ツ颍看看Innodb_log_waits狀態(tài)變量砖茸，如果它不是0渔彰，增加innodb_log_buffer_size。

其他設(shè)置

• query_cache_size
  • query cache（查詢緩存）是一個眾所周知的瓶頸植榕，甚至在并發(fā)并不多的時候也是如此。 最佳選項是將其從一開始就停用淤堵，設(shè)置query_cache_size = 0（現(xiàn)在MySQL 5.6的默認值）并利用其他方法加速查詢：優(yōu)化索引慰毅、增加拷貝分散負載或者啟用額外的緩存（比如memcache或redis）汹胃。如果你已經(jīng)為你的應(yīng)用啟用了query cache并且還沒有發(fā)現(xiàn)任何問題着饥，query cache可能對你有用宰掉。這是如果你想停用它孟害，那就得小心了纹坐。
• log_bin
  • 如果你想讓數(shù)據(jù)庫服務(wù)器充當主節(jié)點的備份節(jié)點耘子，那么開啟二進制日志是必須的谷誓。如果這么做了之后，還別忘了設(shè)置server_id為一個唯一的值鸵钝。就算只有一個服務(wù)器变逃，如果你想做基于時間點的數(shù)據(jù)恢復(fù)揽乱，這（開啟二進制日志）也是很有用的：從你最近的備份中恢復(fù)（全量備份）凰棉，并應(yīng)用二進制日志中的修改（增量備份）撒犀。二進制日志一旦創(chuàng)建就將永久保存绘证。所以如果你不想讓磁盤空間耗盡，你可以用 PURGE BINARY LOGS 來清除舊文件杆煞，或者設(shè)置 expire_logs_days 來指定過多少天日志將被自動清除。記錄二進制日志不是沒有開銷的派桩，所以如果你在一個非主節(jié)點的復(fù)制節(jié)點上不需要它的話铆惑，那么建議關(guān)閉這個選項员魏。
• skip_name_resolve
  • 當客戶端連接數(shù)據(jù)庫服務(wù)器時，服務(wù)器會進行主機名解析虏束，并且當DNS很慢時镇匀，建立連接也會很慢坑律。因此建議在啟動服務(wù)器時關(guān)閉skip_name_resolve選項而不進行DNS查找。唯一的局限是之后GRANT語句中只能使用IP地址了也物，因此在添加這項設(shè)置到一個已有系統(tǒng)中必須格外小心滑蚯。

SQL 調(diào)優(yōu)

一般要進行SQL調(diào)優(yōu)告材，那么就說有慢查詢的SQL，系統(tǒng)或者server可以開啟慢查詢?nèi)罩静绕涫蔷€上系統(tǒng)，一般都會開啟慢查詢?nèi)罩疽晒剩绻新樵冏菔疲梢酝ㄟ^日志來過濾钦铁。但是知道了有需要優(yōu)化的SQL后育瓜，下面要做的就是如何進行調(diào)優(yōu)

慢查詢優(yōu)化基本步驟

1. 先運行看看是否真的很慢栽烂，注意設(shè)置SQL_NO_CACHE
2. where條件單表查焰手，鎖定最小返回記錄表书妻。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起躲履，單表每個字段分別查詢工猜，看哪個字段的區(qū)分度最高
3. explain查看執(zhí)行計劃篷帅，是否與1預(yù)期一致（從鎖定記錄較少的表開始查詢）
4. order by limit 形式的sql語句讓排序的表優(yōu)先查
5. 了解業(yè)務(wù)方使用場景
6. 加索引時參照建索引的幾大原則
7. 觀察結(jié)果魏身，不符合預(yù)期繼續(xù)從0分析

常用調(diào)優(yōu)手段

執(zhí)行計劃explain

在日常工作中，我們有時會開慢查詢?nèi)ビ涗浺恍﹫?zhí)行時間比較久的SQL語句宙枷，找出這些SQL語句并不意味著完事了，我們常常用到explain這個命令來查看一個這些SQL語句的執(zhí)行計劃卓囚，查看該SQL語句有沒有使用上了索引哪亿，有沒有做全表掃描蝇棉，這都可以通過explain命令來查看篡殷。所以我們深入了解MySQL的基于開銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細節(jié)劲弦，以及當運行SQL語句時哪種策略預(yù)計會被優(yōu)化器采用邑跪。

使用explain 只需要在原有select 基礎(chǔ)上加上explain關(guān)鍵字就可以了画畅，如下：

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

簡要解釋下explain各個字段的含義

1. id : 表示SQL執(zhí)行的順序的標識,SQL從大到小的執(zhí)行
2. select_type：表示查詢中每個select子句的類型
3. table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的轴踱，有時不是真實的表名字
4. type：表示MySQL在表中找到所需行的方式，又稱“訪問類型”沸版。常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右视粮，性能從差到好）
5. possible_keys：指出MySQL能使用哪個索引在表中找到記錄蕾殴，查詢涉及到的字段上若存在索引钓觉，則該索引將被列出荡灾，但不一定被查詢使用
6. Key：key列顯示MySQL實際決定使用的鍵（索引）础锐，如果沒有選擇索引皆警，鍵是NULL信姓。
7. key_len：表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度从诲，并非實際使用長度俊性，即key_len是根據(jù)表定義計算而得定页，不是通過表內(nèi)檢索出的）
8. ref：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
9. rows： 表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況卒落，估算的找到所需的記錄所需要讀取的行數(shù)，理論上行數(shù)越少腰湾，查詢性能越好
10. Extra：該列包含MySQL解決查詢的詳細信息

EXPLAIN的特性

• EXPLAIN不會告訴你關(guān)于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
• 部分統(tǒng)計信息是估算的葵萎，并非精確值
• EXPALIN只能解釋SELECT操作卷雕，其他操作要重寫為SELECT后查看執(zhí)行計劃漫雕。

實戰(zhàn)演練

表結(jié)構(gòu)和查詢語句

假如有如下表結(jié)構(gòu)

circlemessage_idx_0 | CREATE TABLE `circlemessage_idx_0` (
  `circle_id` bigint(20) unsigned NOT NULL COMMENT '群組id',
  `from_id` bigint(20) unsigned NOT NULL COMMENT '發(fā)送用戶id',
  `to_id` bigint(20) unsigned NOT NULL COMMENT '指定接收用戶id',
  `msg_id` bigint(20) unsigned NOT NULL COMMENT '消息ID',
  `type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '消息類型',
  PRIMARY KEY (`msg_id`,`to_id`),
  KEY `idx_from_circle` (`from_id`,`circle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

通過執(zhí)行計劃explain分析如下查詢語句

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063  and msg_id>=6273803462253938690  and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| id | select_type | table               | type  | possible_keys           | key     | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16      | NULL | 349780 | Using where |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063   and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16      | NULL |   30 | Using where |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

問題分析

通過上面兩個執(zhí)行計劃可以發(fā)現(xiàn)當沒有msg_id >= xxx這個查詢條件的時候，檢索的rows要少很多锥咸，并且兩者查詢的時候都用到了索引搏予，而且用到的還只是主鍵索引弧轧。那說明索引應(yīng)該是不合理的雪侥，沒有發(fā)揮最大作用。

分析這個執(zhí)行計劃可以看到精绎，當包含msg_id >= xxx 查詢條件的時候速缨，rows有34w多行，這種情況捺典，說明檢索太多鸟廓，要么就是表里面確實有這么大从祝，要么就是索引不合理沒有用到索引襟己，大都情況是沒用合理用到索引引谜。列中所用到的索引也是PRIMARY贝室，那就可能是(msg_id,to_id)的其中一個峡迷，注意我們建立表的時候msg_id索引的順序是在to_id前面的夯辖，因此MySQL查詢一定會優(yōu)先用msg_id索引贮缅，在使用了msg_id索引后，就已經(jīng)檢索出了34w行，并且由于msg_id的查詢條件是大于等于，因此，再這個查詢條件后堂湖，就不能再用到to_id的索引斥季。

然后再看key_len長度為16灶挟，結(jié)合 key為PRIMARY惕医，那么可以分析得知妓笙，只有一個主鍵索引被用到鲫忍。

最后看看 type 值阀溶，是range鼎姐，那么就說明這個查詢要么是范圍查詢，要么就是多值匹配知态。

請注意，from_id != xxx 這樣的語句，是無法用到索引的梭域。 只有from_id = xxx就可以用到所以囊颅，因此from id 的索引其實可以不用伸但，建立索引的時候就要考慮清楚

如何優(yōu)化

既然知道索引不合理捞烟，那么就要分析并調(diào)整索引。一般而言惦界，我們既然要從單表里面查詢泉哈，那么就需要能夠知道大體煤率，單表里面大致會有哪些數(shù)據(jù)乍钻，現(xiàn)在的量級大概是多少闷畸。

然后開始下一步的分析谭确，既然msgid是被設(shè)置為了主鍵肠骆，那一定是全局唯一的狞贱，所有质况，有多少數(shù)據(jù)量就至少會有多少條msgid；那么檢索msg_id基本就是檢索整個表了。我們要做的優(yōu)化就是要盡量減少索引，減少查詢的行數(shù)涤姊；那么就需要思考纲辽，通過查詢哪些字段才能夠減少行數(shù)拖吼？比如吊档，一個張表里面怠硼，所屬某個用戶的數(shù)據(jù)香璃，會不會比查詢msgid的行數(shù)要少增显？ 查詢某個用戶并且是屬于某個圈子的同云，那會不會就更少了炸站？ 等等旱易。阀坏。。

然后根據(jù)實際情況分析盒至，單表里面命中to_id 的行數(shù)應(yīng)該是會小于命中msg_id的枷遂，因此要首先保證能夠使用到to_id的索引酒唉，為此痪伦，可以設(shè)置主鍵的時候把msg_id和to_id的順序交互一下流妻；但是，由于已經(jīng)是線上的表涣达，已經(jīng)有了大量數(shù)據(jù)度苔，并且業(yè)務(wù)開始運行寇窑，這種情況下甩骏，修改主鍵會引發(fā)很多問題（當然修改索引是OK的），因此咨察，不建議直接修改主鍵摄狱。那么媒役，為了保證有效使用to_id的索引酣衷，就要新建一個聯(lián)合索引鸥诽；那么新建的聯(lián)合索引的第一索引字段必然是to_id，針對此業(yè)務(wù)場景拳昌，最好能夠再加上circle_id索引炬藤，這樣可以快速索引碴里；這樣就得到了新的聯(lián)合索引(to_id,circle_id)的索引咬腋，然后根竿，因為要找msg_id寇壳，為此，在此基礎(chǔ)上泞歉，再加上msg_id偿洁。最終得到的聯(lián)合索引為(to_id,circle_id,msg_id)沟优；這樣的話挠阁，就能夠快速檢索這樣的查詢語句了：where to_id = xxx and circle_id = xxx and msgId >= xxx

當然锨用，索引的建立隘谣，也不是說某個sql 語句需要啥索引寻歧，就建立某個聯(lián)合索引码泛，這樣的話，索引太多的話晌缘，寫的性能受影響（插入磷箕、刪除岳枷、修改）嫩舟，然后存儲空間也會相應(yīng)增大；另外mysql在運行時也會消耗資源維護索引播玖，所以，索引并不是越多越好掰吕，需要結(jié)合查詢最頻繁殖熟、最影響性能的sql來建立合適的索引斑响。需要再說明的是舰罚，一個聯(lián)合索引或者一組主鍵就是一個btree，多個索引就是多個btree

總結(jié)

首先我們需要深入理解索引的原理和實現(xiàn)赏陵，當理解了原理后，才能夠更有助于我們建立合適的索引考传。然后我們建立索引的時候伙菊，不要想當然镜硕，要先想清楚業(yè)務(wù)邏輯兴枯，再建立對應(yīng)的表結(jié)構(gòu)和索引财剖。 需要再次強調(diào)如下幾點:

1. 索引不是越多越好
2. 區(qū)分主鍵和索引
3. 理解索引結(jié)構(gòu)原理
4. 理解查詢索引規(guī)則

參考

美團-MySQL索引原理及慢查詢優(yōu)化

MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理

感謝參考文章的原作者