高性能MySQL第三本筆記總結(jié)（上）

  • SQL語(yǔ)句的執(zhí)行流程

    無(wú)論何時(shí)殉疼，只要有多個(gè)查詢(xún)需要在同一個(gè)時(shí)刻修改數(shù)據(jù)時(shí)才漆，就會(huì)有并發(fā)問(wèn)題牛曹。MySql主要在服務(wù)器層與存儲(chǔ)引擎層進(jìn)行并發(fā)控制。

    假設(shè)數(shù)據(jù)庫(kù)中國(guó)一張郵箱表醇滥，每個(gè)郵件都是一條記錄黎比。如果某個(gè)客戶(hù)正在讀取郵箱，同時(shí)其他客戶(hù)試圖在刪除郵箱表中的某一條數(shù)據(jù)鸳玩。這個(gè)時(shí)候阅虫，讀取的結(jié)構(gòu)就是不確定的了。在MySql中會(huì)通過(guò)鎖定防止其它用戶(hù)讀取同一數(shù)據(jù)不跟。大多數(shù)時(shí)候颓帝，MySQL鎖的內(nèi)部管理都是透明的。

MySQL鎖的粒度

  • 每種MySql引擎都可以實(shí)現(xiàn)自己的鎖策略和鎖粒度窝革，將鎖粒度固定在某個(gè)級(jí)別购城，可以為某些特定的場(chǎng)景提供更好的性能。

表鎖(table lock)

  • 表鎖是mysql中最基本的鎖略虐译，并且是開(kāi)銷(xiāo)最小的策略瘪板。它會(huì)鎖定整個(gè)表，一個(gè)用戶(hù)在對(duì)表進(jìn)行寫(xiě)操作(插入菱蔬、刪除篷帅、更新等)前史侣，需要先獲得寫(xiě)鎖拴泌，這會(huì)阻塞其他用戶(hù)對(duì)該表的所有讀寫(xiě)操作。只有沒(méi)有寫(xiě)鎖時(shí),其他讀取的用戶(hù)才能獲得讀鎖惊橱，讀鎖之間是不相互阻塞的蚪腐。
  • 在特定的場(chǎng)景中，表鎖也可能有良好的性能税朴。例如回季，READ L0CAL 表鎖支持某些類(lèi)型的并發(fā)寫(xiě)操作。另外正林，寫(xiě)鎖也比讀鎖有更高的優(yōu)先級(jí)泡一，因此-一個(gè)寫(xiě)鎖請(qǐng)求可能會(huì)被插入到讀鎖隊(duì)列的前面(寫(xiě)鎖可以插入到鎖隊(duì)列中讀鎖的前面，反之讀鎖則不能插入到寫(xiě)鎖的前面)觅廓。
  • 盡管存儲(chǔ)引擎可以管理自己的鎖鼻忠，MySQL本身還是會(huì)使用各種有效的表鎖來(lái)實(shí)現(xiàn)不同的目的。例如杈绸，服務(wù)器會(huì)為諸如ALTER TABLE之類(lèi)的語(yǔ)句使用表鎖帖蔓，而忽略存儲(chǔ)引擎的鎖機(jī)制矮瘟。

行級(jí)鎖(row lock)

  • 行級(jí)鎖可以最大程度地支持并發(fā)處理(同時(shí)也帶來(lái)了最大的鎖開(kāi)銷(xiāo))。
  • 在InnoDB和XtraDB,以及其他一些存儲(chǔ)引擎中實(shí)現(xiàn)了行級(jí)鎖塑娇。
  • 行級(jí)鎖只在存儲(chǔ)引擎層實(shí)現(xiàn)澈侠，而MySQL服務(wù)器層沒(méi)有實(shí)現(xiàn)。服務(wù)器層完全不了解存儲(chǔ)引擎中的鎖實(shí)現(xiàn)埋酬。

MySQL的事務(wù)

事務(wù)特性

  • A（原子性）事務(wù)的各步操作是不可分的哨啃，保證一系列的操作要么都完成，要么都不完成写妥；
  • C（一致性）事務(wù)完成棘催，數(shù)據(jù)必須處于一致的狀態(tài)；
  • I（隔離性）對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)彼此之間是相互隔離耳标，這表明事務(wù)必須是獨(dú)立的醇坝，不應(yīng)以任何方式依賴(lài)或影響其他事務(wù)；
  • D（持久性）表示事務(wù)對(duì)數(shù)據(jù)處理結(jié)束后次坡，對(duì)數(shù)據(jù)更改必須持久化呼猪，不管是事務(wù)成功還是回滾。事務(wù)日志都能夠保持事務(wù)的永久性砸琅。

事務(wù)的隔離級(jí)別

  • SQL標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別包括：讀未提交（read uncommitted）宋距、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（serializable ）
  • 讀未提交是指症脂，一個(gè)事務(wù)還沒(méi)提交時(shí)谚赎，它做的變更就能被別的事務(wù)看到。
  • 讀提交是指诱篷，一個(gè)事務(wù)提交之后壶唤，它做的變更才會(huì)被其他事務(wù)看到。
  • 可重復(fù)讀是指棕所，一個(gè)事務(wù)執(zhí)行過(guò)程中看到的數(shù)據(jù)闸盔，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的。未提交的更改對(duì)其他事務(wù)是不可見(jiàn)的
  • 串行化:對(duì)應(yīng)一個(gè)記錄會(huì)加讀寫(xiě)鎖琳省，出現(xiàn)沖突的時(shí)候迎吵，后訪(fǎng)問(wèn)的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成才能繼續(xù)執(zhí)行。最高的隔離級(jí)別

MySQL中的事務(wù)

  • MySQL提供了兩種事務(wù)型的存儲(chǔ)引擎: InnoDB和NDB Cluster针贬。另外還有一些第三方存儲(chǔ)引擎也支持事
  • MySQL默認(rèn)采用自動(dòng)提交(AUTOCOMIT) 模式击费。如果不是顯式地開(kāi)始-一個(gè)個(gè)事務(wù)，則每個(gè)查詢(xún)都被當(dāng)作一事務(wù)執(zhí)行提交操作桦他。在當(dāng)前連接中蔫巩，可以通過(guò)設(shè)置AUTOCOMMIT變量來(lái)啟用或者禁用自動(dòng)提交模式:
  • InnoDB采用的是兩階段鎖定協(xié)議(two-phase locking protocol)。在事務(wù)執(zhí)行過(guò)程中，隨時(shí)都可以執(zhí)行鎖定批幌，鎖只有在執(zhí)行COMMIT或者ROLLBACK的時(shí)候才會(huì)釋放础锐，并且所有的鎖是在同一時(shí)刻被釋放。
  • InnoDB也支持通過(guò)特定的語(yǔ)句進(jìn)行顯式鎖定SELECT ... LOCK IN SHARE MODE和SELECT FOR UPDATE 些語(yǔ)句不屬于SQL規(guī)范

多版本并發(fā)控制MVCC

  • MVCC是行級(jí)鎖的一個(gè)變種荧缘，但是它在很多情況下避免了加鎖操作皆警，因此開(kāi)銷(xiāo)更低。雖然實(shí)現(xiàn)機(jī)制有所不同截粗，但大都實(shí)現(xiàn)了非阻塞的讀操作信姓，寫(xiě)操作也只鎖定必要的行。
  • MVCC的實(shí)現(xiàn)绸罗，是通過(guò)保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來(lái)實(shí)現(xiàn)的意推。也就是說(shuō)，不管需要執(zhí)行多長(zhǎng)時(shí)間珊蟀，每個(gè)事務(wù)看到的數(shù)據(jù)都是一致的菊值。根據(jù)事務(wù)開(kāi)始的時(shí)間不同，每個(gè)事務(wù)對(duì)同張表育灸，同一時(shí)刻看到的數(shù)據(jù)可能是不一樣的腻窒。
  • InnoDB的MVCC,是通過(guò)在每行記錄后面保存兩個(gè)隱藏的列來(lái)實(shí)現(xiàn)的。這兩個(gè)列磅崭，一個(gè)保存了行的創(chuàng)建時(shí)間儿子，一個(gè)保存行的過(guò)期時(shí)間(或刪除時(shí)間)。當(dāng)然存儲(chǔ)的并不是實(shí)際的時(shí)間值砸喻，而是系統(tǒng)版本號(hào)柔逼。每開(kāi)始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增割岛。事務(wù)開(kāi)始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)愉适，用來(lái)和查詢(xún)到的每行記錄的版本號(hào)進(jìn)行比較。
  • MVCC只在可重復(fù)讀和讀提交的隔離級(jí)別生效蜂桶。其它兩個(gè)級(jí)別都不兼容

在可重復(fù)讀(REPEATABLE READ)隔離級(jí)別下儡毕，MVCC具體是如何操作的也切。

SELECT查詢(xún)操作時(shí)

InnoDB會(huì)根據(jù)以下兩個(gè)條件檢查每行記錄:

  • InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行(也就是,行的系統(tǒng)版本號(hào)小于或等于事務(wù)的系統(tǒng)版本號(hào)),這樣可以確保事務(wù)讀取的行扑媚，要么是在事務(wù)開(kāi)始前已經(jīng)存在的，要么是事務(wù)自身插入或者修改過(guò)的雷恃。
  • 行的刪除版本要么未定義疆股，要么大于當(dāng)前事務(wù)版本號(hào)。這可以確保事務(wù)讀取到的行倒槐，在事務(wù)開(kāi)始之前未被刪除旬痹。

INSERT

  • InnoDB為新播入的每-一行保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)。

DELETE

  • InnoDB為刪除的每-*行保存當(dāng)前系統(tǒng)版本號(hào)作為行刪除標(biāo)識(shí)。

UPDATE

  • InnoDB為插入-行新記錄两残，保存當(dāng)前系統(tǒng)版本號(hào)作為行版本號(hào)永毅，同時(shí)保存當(dāng)前系統(tǒng)版本號(hào)到原來(lái)的行作為行刪除標(biāo)識(shí)。

數(shù)據(jù)庫(kù)存儲(chǔ)引擎

InnDB存儲(chǔ)引擎

  • InnDB是Mysql默認(rèn)的事務(wù)型存儲(chǔ)引擎人弓。它被設(shè)計(jì)用來(lái)處理大量的短期(short-lived) 事務(wù)沼死，短期事務(wù)大部分情況是正常提交的，很少會(huì)被回滾崔赌。InnoDB的性能和自動(dòng)崩潰恢復(fù)特性意蛀，使得它在非事務(wù)型存儲(chǔ)的需求中也很流行
  • InnoDB的數(shù)據(jù)存儲(chǔ)在表空間(tablespace) 中，表空間是由InnoDB管理的-個(gè)黑盒子健芭，由一系列的數(shù)據(jù)文件組成县钥。
  • InnoDB采用MVCC來(lái)支持高并發(fā)，并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別慈迈。其默認(rèn)級(jí)別是REPEATABLE READ (可重復(fù)讀) ,并且通過(guò)間隙鎖(next-key locking)策略防止幻讀的出現(xiàn)若贮。間隙鎖使得InnoDB不僅僅鎖定查詢(xún)涉及的行，還會(huì)對(duì)索引中的間隙進(jìn)行鎖定痒留，以防止幻影行的插入兜看。
  • InnoDB表是基于聚簇索引建立的。聚簇素引對(duì)主鍵查詢(xún)有很高的性能狭瞎，不過(guò)它的二級(jí)索引(secondary index细移，非主鍵索引)中必須包含主鍵列，所以如果主鍵列很大的話(huà)熊锭，其他的所有索引都會(huì)很大弧轧。因此，若表上的索引較多的話(huà)碗殷，主鍵應(yīng)當(dāng)盡可能的小精绎。
  • InnoDB內(nèi)部做了很多優(yōu)化，包括從磁盤(pán)讀取數(shù)據(jù)時(shí)采用的可預(yù)測(cè)性預(yù)讀锌妻，能夠自動(dòng)在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應(yīng)哈希索引(adaptive hash index),以及能夠加速插入操作的插入緩沖區(qū)(insert buffer)等

MyISAM引擎

  • MyISAM不支持事務(wù)和行鎖代乃，在MySQL5.1之前的版本是默認(rèn)的存儲(chǔ)引擎，有一個(gè)缺陷是崩潰后無(wú)法恢復(fù)仿粹。
  • 優(yōu)點(diǎn)是對(duì)于只讀的數(shù)據(jù)搁吓，或者表比較小，可以忍受修復(fù)操作吭历，可以繼續(xù)使用
  • MyISAM會(huì)將表存儲(chǔ)在兩個(gè)文件中:數(shù)據(jù)文件和索引文件堕仔，分別以.MYD和.MYI為擴(kuò)展名
  • MyISAM表可以包含動(dòng)態(tài)或者靜態(tài)(長(zhǎng)度固定)行。MySQL會(huì)根據(jù)表的定義來(lái)決定采用何種行格式晌区。MyISAM表可以存儲(chǔ)的行記錄數(shù)摩骨，一般受限于可用的磁盤(pán)空間,或者操作系統(tǒng)中單個(gè)文件的最大尺寸通贞。
  • MyISAM對(duì)整張表加鎖，而不是針對(duì)行恼五。讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖,寫(xiě)入時(shí)則對(duì)表加排他鎖昌罩。但是在表有讀取查詢(xún)的同時(shí)，也可以往表中插入新的記錄(這被稱(chēng)為并發(fā)插入)

Archive引擎

  • Archive引擎會(huì)緩存所有的寫(xiě)并利用zlib對(duì)插人的行進(jìn)行壓縮灾馒，所以比MyISAM表的磁盤(pán)I/O更少客蹋。但是每次SELECT查詢(xún)都需要執(zhí)行全表掃描晶衷。所以Archive表適合日志和數(shù)據(jù)采集類(lèi)應(yīng)用乳蛾，這類(lèi)應(yīng)用做數(shù)據(jù)分析時(shí)往往需要全表掃描遣钳。或者在一- 些需要更快速的INSERT操作的場(chǎng)合下也可以使用傅物。
  • Archive引擎支持行級(jí)鎖和專(zhuān)用的緩沖區(qū)夯辖，所可以實(shí)現(xiàn)高并發(fā)的插人。在一個(gè)查詢(xún)開(kāi)始直到返回表中存在的所有行數(shù)之前董饰，Archive引擎會(huì)阻止其他的SELECT執(zhí)行蒿褂，以實(shí)現(xiàn)一致性讀。另外卒暂，也實(shí)現(xiàn)了批量插入在完成之前對(duì)讀操作是不可見(jiàn)的啄栓。這種機(jī)制模仿了事務(wù)和MVCC的一些特性，但Archive引擎不是一個(gè)事務(wù)型的引擎也祠，而是-一個(gè)針對(duì)高速插人和壓縮做了優(yōu)化的簡(jiǎn)單引擎昙楚。

Blackhole引擎

  • Blackhole引擎沒(méi)有實(shí)現(xiàn)任何的存儲(chǔ)機(jī)制，它會(huì)丟棄所有插入的數(shù)據(jù)诈嘿，不做任何保存堪旧。但是服務(wù)器會(huì)記錄Blackhole表的日志，所以可以用于復(fù)制數(shù)據(jù)到備庫(kù)奖亚，或者只是簡(jiǎn)單地記錄到日志淳梦。這種特殊的存儲(chǔ)引擎可以在--些特殊的復(fù)制架構(gòu)和8志審核時(shí)發(fā)揮作用。但這種應(yīng)用方式我們碰到過(guò)很多問(wèn)題昔字，因此并不推薦爆袍。

CSV引擎

  • CSV引擎可以將普通的CSV文件(逗號(hào)分割值的文件)作為MySQL的表來(lái)處理，但這種表不支持索引作郭。CSV引擎可以在數(shù)據(jù)庫(kù)運(yùn)行時(shí)拷入或者拷出文件陨囊。可以將Excel等電子表格軟件中的數(shù)據(jù)存儲(chǔ)為CSV文件所坯，然后復(fù)制到MySQL數(shù)據(jù)目錄下谆扎，就能在MySQL中打開(kāi)使用。同樣芹助，如果將數(shù)據(jù)寫(xiě)人到一個(gè)CSV引擎表堂湖，其他的外部程序也能立即從表的數(shù)據(jù)文件中讀取CSV格式的數(shù)據(jù)。因此CSV引擎可以作為- -種數(shù)據(jù)交換的機(jī)制状土，非常有用无蜂。

Federated引擎

  • Federated引擎是訪(fǎng)問(wèn)其他MySQL服務(wù)器的-一個(gè)代理，它會(huì)創(chuàng)建-一個(gè)到遠(yuǎn)程MySQL服務(wù)器的客戶(hù)端連接蒙谓，并將查詢(xún)傳輸?shù)竭h(yuǎn)程服務(wù)器執(zhí)行斥季，然后提取或者發(fā)送需要的數(shù)據(jù)。最初設(shè)計(jì)該存儲(chǔ)引擎是為了和企業(yè)級(jí)數(shù)據(jù)庫(kù)如Microsoft SQL Server和Oracle的類(lèi)似特性競(jìng)爭(zhēng)的累驮，可以說(shuō)更多的是一種市場(chǎng)行為酣倾。盡管該引擎看起來(lái)提供了一種很好的跨服務(wù)器的靈活性，但也經(jīng)常帶來(lái)問(wèn)題谤专，因此默認(rèn)是禁用的躁锡。

Memory引擎

  • 如果需要快速地訪(fǎng)問(wèn)數(shù)據(jù)，并且這些數(shù)據(jù)不會(huì)被修改置侍，重啟以后丟失也沒(méi)有關(guān)系映之，那么使用Memory表(以前也叫做HEAP表)是非常有用的。Memory 表至少比MyISAM表要快一個(gè)數(shù)量級(jí)蜡坊，因?yàn)樗械臄?shù)據(jù)都保存在內(nèi)存中,不需要進(jìn)行磁盤(pán)I/O杠输。Memory 表的結(jié)構(gòu)在重啟以后還會(huì)保留,但數(shù)據(jù)會(huì)丟失。

Schema(數(shù)據(jù)庫(kù)的組織和結(jié)構(gòu))與數(shù)據(jù)類(lèi)型優(yōu)化

更小的通常好

  • 盡量使用可以正確存儲(chǔ)數(shù)據(jù)的最下數(shù)據(jù)類(lèi)型秕衙，更小的數(shù)據(jù)類(lèi)型通常更快蠢甲，占用更小的磁盤(pán),內(nèi)存和cpu緩存,并且處理時(shí)需要的cpu周期更少
  • 但是要確保沒(méi)有低估需要存儲(chǔ)的值的范圍，因?yàn)樵趕chema中的多個(gè)地方增加數(shù)據(jù)類(lèi)型的范圍是一個(gè)非常耗時(shí)和痛苦的操作据忘。如果無(wú)法確定哪個(gè)數(shù)據(jù)類(lèi)型是最好的峡钓，就選擇你認(rèn)為不會(huì)超過(guò)范圍的最小類(lèi)型

簡(jiǎn)單就好

  • 簡(jiǎn)單數(shù)據(jù)類(lèi)型的操作通常需要更少的CPU周期。例如若河，整型比字符操作代價(jià)更低能岩，
  • 整型比字符操作代價(jià)更低，因?yàn)樽址托?duì)規(guī)則(排序規(guī)則)使字符比較比整型比較更復(fù)雜
  • 使用MySQL內(nèi)建的類(lèi)型而不是字符串來(lái)存儲(chǔ)日期和時(shí)間
  • 用整型存儲(chǔ)IP地址萧福。int類(lèi)型占4個(gè)字節(jié)拉鹃，tinyint占1個(gè)字節(jié)。

盡量避免NUll

  • 如果查詢(xún)中包含null的列鲫忍，會(huì)使得索引膏燕，索引統(tǒng)計(jì)和值比較更復(fù)雜
  • 當(dāng)可以NULL的列被索引時(shí),每個(gè)索引記錄需要一個(gè)額外的字節(jié)

datetime和timesamp

  • datetime和timesamp都可以存儲(chǔ)相同的數(shù)據(jù)類(lèi)型,時(shí)間和日期，精確到秒悟民。然而timesamp只使用datetime一半的存儲(chǔ)空間
  • timesamp 會(huì)根據(jù)時(shí)區(qū)變化坝辫，具有特殊的自動(dòng)更新能力，允許的時(shí)間范圍要小很多射亏，有時(shí)它的特許能力會(huì)成為障礙

字段列的類(lèi)型選擇

  • 在滿(mǎn)足值的范圍情況下近忙，盡量選擇最小的數(shù)據(jù)類(lèi)型竭业。列如tinyint比int少三個(gè)字節(jié)。tinyint 1字節(jié) (-128及舍，127) (0未辆，255) 小整數(shù)值
  • 字段固定長(zhǎng)度如手機(jī)號(hào)，身份證號(hào)用char,可變長(zhǎng)度使用varchar因?yàn)槠溟L(zhǎng)度固定锯玛，方便程序的存儲(chǔ)與查找,付出的是空間的代價(jià)咐柜。varchar是以空間效率為首位的
  • 整數(shù)類(lèi)型通常是標(biāo)識(shí)列最好的數(shù)據(jù)類(lèi)型，因?yàn)樗麄兒芸觳⑶铱梢允?code>AUTO_INCREMENT
  • 如果可能攘残，盡量批量使用字符串類(lèi)型拙友，因?yàn)樗鼈兿母嗟目臻g，比數(shù)字類(lèi)型慢
  • 如果存儲(chǔ)UUID值歼郭，可以移除-符號(hào)遗契。使用UNHEX()函數(shù)轉(zhuǎn)換為UUID的16字節(jié)的的數(shù)字，并存儲(chǔ)在一個(gè)binary(16)列中实撒。取值時(shí)可以使用HEX()格式化為16進(jìn)制格式

使用inet_aton和inet_ntoa處理ip地址數(shù)據(jù)

  • 插入數(shù)據(jù)前姊途，先用inet_aton把ip地址轉(zhuǎn)為整型，可以節(jié)省空間知态，因?yàn)?code>char(15) 占16字節(jié)捷兰。
  • 顯示數(shù)據(jù)時(shí)，使用inet_ntoa把整型的ip地址轉(zhuǎn)為電地址顯示即可负敏。

總結(jié)

  • 盡量避免過(guò)度設(shè)計(jì)贡茅，例如會(huì)導(dǎo)致極其復(fù)雜査詢(xún)的schema設(shè)計(jì)，或者有很多列的表設(shè)計(jì)
  • 使用小而簡(jiǎn)單的合適數(shù)據(jù)類(lèi)型其做，除非真實(shí)數(shù)據(jù)模型中有確切的需要顶考，否則應(yīng)該盡可能地避免使用NULL值。
  • 盡量使用相同的數(shù)據(jù)類(lèi)型存儲(chǔ)相似或相關(guān)的值妖泄，尤其是要在關(guān)聯(lián)條件中使用的列驹沿。
  • 注意可變長(zhǎng)字符串，其在臨時(shí)表和排序時(shí)可能導(dǎo)致悲觀的按最大長(zhǎng)度分配內(nèi)存蹈胡。
  • 盡量使用整型定義標(biāo)識(shí)列渊季。
  • 避免使用MySQL已經(jīng)遺棄的特性，例如指定浮點(diǎn)數(shù)的精度罚渐，或者整數(shù)的顯示寬度却汉。
  • 小心使用ENUM和SET。雖然它們用起來(lái)很方便荷并，但是不要濫用合砂，否則有時(shí)候會(huì)變成 陷阱。最好避免使用BIT源织。
  • ALTER TABLE是讓人痛苦的操作翩伪，因?yàn)樵诖蟛糠智闆r下微猖，它都會(huì)鎖表并且重建整張表。例如在備機(jī)執(zhí)行ALTER并在完成后把它切換為主庫(kù)幻工。

創(chuàng)建高性能的索引

索引基礎(chǔ)

  • 索引有很多類(lèi)型,Mysql是使用B+tree樹(shù)索引,索引是在引擎層實(shí)現(xiàn)而不是服務(wù)層励两。不同引擎的索引工作方式不一樣黎茎。
  • MyISAM使用前綴壓縮技術(shù)使得索引更小囊颅，InnoDB按照原數(shù)據(jù)根式進(jìn)行存儲(chǔ)。MyISAM索引通過(guò)數(shù)據(jù)到物理位置引用被索引的行傅瞻，InnoDB根據(jù)主鍵引用被索引的行
  • 全值匹配：指的是和索引中所有列進(jìn)行匹配踢代。比如EXPLAIN 中的type=index
  • 最左匹配原則: mysql索引規(guī)則中要求復(fù)合索引要想使用第二個(gè)索引，必須先使用第一個(gè)索引的原因嗅骄。（而且第一個(gè)索引必須是等值匹配）胳挎。也就是如果使用復(fù)合索引查詢(xún)時(shí)優(yōu)先按照索引創(chuàng)建的順序進(jìn)行條件查詢(xún)

索引的選擇策略

  • 索引字段必須是獨(dú)立的列，不嗯呢該是表達(dá)式和函數(shù)運(yùn)算
  • 計(jì)算合適的前綴索引長(zhǎng)度
  • 善用復(fù)合索引溺森，而不是為每個(gè)字段都建立索引慕爬，選擇合適的索引列順序
    *當(dāng)不需要排序和分組時(shí)使用頻率較高的放在復(fù)合索引前列,這時(shí)候的索引優(yōu)化用于where條件
  • 盡可能將左范圍查詢(xún)的列放在索引的后面,以便優(yōu)化器使用盡可能多的索引列。
  • 對(duì)于范圍條件查詢(xún),mysql范圍列后面的其它的索引列,對(duì)于多個(gè)等值條件查詢(xún)則沒(méi)有這種限制

Inndb主鍵索引和非主鍵索引的區(qū)別

  • 主鍵索引即存儲(chǔ)了索引值屏积，又在葉子中存儲(chǔ)了行的數(shù)據(jù)医窿。所以通過(guò)主鍵查詢(xún)時(shí)效率高，一次查詢(xún)即可炊林，不需要回表操作姥卢。這種即存儲(chǔ)索引值又存儲(chǔ)行的所有數(shù)據(jù)的結(jié)構(gòu)叫做(聚簇索引）
  • 非主鍵索引存儲(chǔ)索引值，但葉子中存儲(chǔ)的是主鍵ID渣聚，所以查詢(xún)時(shí)需要進(jìn)行一次回表操作才可以取到所有的行數(shù)據(jù)

索引覆蓋

  • 索引覆蓋是指如果查詢(xún)的列恰好是索引的一部分,那么查詢(xún)只需要在索引文件上進(jìn)行,不需要回行到磁盤(pán)再找數(shù)據(jù).這種查詢(xún)速度非扯懒瘢快,稱(chēng)為”索引覆蓋”

理想的索引

  • 理想的索引。1:查詢(xún)頻繁 2:區(qū)分度高 3:長(zhǎng)度小 4:盡量能覆蓋常用查詢(xún)字段.
  • 索引長(zhǎng)度直接影響索引文件的大小,影響增刪改的速度,并間接影響查詢(xún)速度(占用內(nèi)存多).
  • 針對(duì)列中的值,從左往右截取部分,來(lái)建索引
  • 截的越短,重復(fù)度越高,區(qū)分度越小, 索引效果越不好
    -截的越長(zhǎng),重復(fù)度越低,區(qū)分度越高,索引效果越好,但帶來(lái)的影響也越大--增刪改變慢,并間影響查詢(xún)速度.所以,我們要在 區(qū)分度+長(zhǎng)度奕枝，兩者上,取得一個(gè)平衡.
  • 慣用手法:截取不同長(zhǎng)度,并測(cè)試其區(qū)分度

select count(distinct left(word,6))/count(*) from dict;

使用索引掃描來(lái)做排序

  • mysql有兩種方式可以生成有序的結(jié)果：通過(guò)排序操作或者按索引順序掃描棺榔。如果explan出來(lái)的type值為index則說(shuō)明使用了索引掃描來(lái)做排序。
  • 只有索引列的順序和order by字段的順序完全一致隘道，并且所有列的排序方向都一樣時(shí),mysql才能使用索引對(duì)結(jié)果進(jìn)行排序症歇。
  • 如果查詢(xún)需要關(guān)聯(lián)多表，則只有當(dāng)order by中使用的字段完全是第一個(gè)表時(shí)薄声，才能使用索引做排序当船。order by和查找型的限制一樣，都要滿(mǎn)足最左前綴要求默辨。
  • 如果索引的第一個(gè)字段為常量時(shí)德频，where或join中指對(duì)這個(gè)列制定了固定的常量值，就可以不滿(mǎn)足最左前綴要求缩幸。比如有個(gè)復(fù)合索引(c1,c2,c3), 執(zhí)行 select * from where c1=1 order c2,c3 就可以使用到索引排序

重復(fù)索引與冗余索引

  • 重復(fù)索引: 是指 在同1個(gè)列(如age), 或者 順序相同的幾個(gè)列(age,school), 建立了多個(gè)索引,稱(chēng)為重復(fù)索引,重復(fù)索引沒(méi)有任何幫助,只會(huì)增大索引文件,拖慢更新速度, 去掉.
  • 冗余索引:是指2個(gè)索引所覆蓋的列有重疊, 稱(chēng)為冗余索引比如x,m,列,加索引 index x(x),index xm(x,m)x,xm索引,兩者的x列重疊了,這種情況,稱(chēng)為冗余索引.
  • 甚至可以把index mx(m,x)索引也建立,mx,xm也不是重復(fù)的,因?yàn)榱械捻樞虿灰粯?

索引和鎖

  • 索引可以讓查詢(xún)鎖定更少的行,Innodb在訪(fǎng)問(wèn)行的時(shí)候?qū)ζ浼渔i壹置，而索引可以減少I(mǎi)nnodb訪(fǎng)問(wèn)的行數(shù),從而減少鎖的數(shù)量

查詢(xún)性能優(yōu)化

優(yōu)化數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)

  • 確認(rèn)程序是否在檢索大量超過(guò)需要的數(shù)據(jù)
  • 確認(rèn)MySQL服務(wù)器層是否在分析大量超過(guò)需要的數(shù)據(jù)行
  • 是否查詢(xún)不需要的記錄竞思，常見(jiàn)的錯(cuò)誤是誤認(rèn)為MySQL只會(huì)返回需要的數(shù)據(jù)，實(shí)際上mysql先是返回全部數(shù)據(jù)钞护，在進(jìn)行計(jì)算盖喷。一般使用limit區(qū)分
  • 多表關(guān)聯(lián)的時(shí)候返回了全部的列，只需要取出所用的列即可
  • 優(yōu)化select *操作,以及重復(fù)查詢(xún)同一條數(shù)據(jù)行做好緩存
  • 執(zhí)行語(yǔ)句分析难咕，檢查MySQL是否掃描了額外的記錄

重構(gòu)查詢(xún)的方式

  • 設(shè)計(jì)查詢(xún)語(yǔ)句時(shí)课梳，是否要將一個(gè)復(fù)雜的查詢(xún)拆分成多個(gè)簡(jiǎn)單的查詢(xún)
  • 切分查詢(xún)：將大查詢(xún)切分成小查詢(xún)，每個(gè)查詢(xún)功能完全一樣余佃，但是只完成一部分?jǐn)?shù)據(jù)的操作暮刃，每次返回一小部分的結(jié)果。比如在做定時(shí)清除線(xiàn)日志表大表數(shù)據(jù)刪除時(shí)爆土，分批次刪除比較高效椭懊，可以大大減少刪除時(shí)鎖的持有時(shí)間
  • 分解關(guān)聯(lián)查詢(xún):可以對(duì)每一個(gè)表進(jìn)行一次單表查詢(xún)，然后將結(jié)果在程序中就行關(guān)聯(lián)匯總步势。查詢(xún)拆分后,執(zhí)行單個(gè)查詢(xún)可以減少鎖的競(jìng)爭(zhēng)氧猬。可以讓緩存的效率更高坏瘩，許多應(yīng)用可以方便緩存單表查詢(xún)對(duì)應(yīng)的結(jié)果對(duì)象盅抚。

Mysql中驅(qū)動(dòng)表的概念

  • mysql中指定了連接條件時(shí)，滿(mǎn)足查詢(xún)條件的記錄行數(shù)少的表為驅(qū)動(dòng)表桑腮；如未指定查詢(xún)條件泉哈，則掃描行數(shù)少的為驅(qū)動(dòng)表。mysql優(yōu)化器就是這么粗暴以小表驅(qū)動(dòng)大表的方式來(lái)決定執(zhí)行順序的破讨。
  • 有時(shí)這種優(yōu)化方式反而使表的查詢(xún)效率變低丛晦，這個(gè)時(shí)候STRAIGHT_JOIN就排上用場(chǎng)了

比如如下測(cè)試的sql,Table1表的FilterID字段建了索引


select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

STRAIGHT_JOIN 方式改寫(xiě)驅(qū)動(dòng)表

select t1.*
from Table1 t1
STRAIGHT_JOIN  Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

排序優(yōu)化

  • 當(dāng)MySQL不能通過(guò)索引生成排序結(jié)果時(shí)，MySQL就需要自己排序提陶，如果數(shù)據(jù)量少則在內(nèi)存中進(jìn)行烫沙，如果數(shù)據(jù)量大則使用磁盤(pán)，這個(gè)過(guò)程統(tǒng)一稱(chēng)為文件排序
  • 排序的數(shù)據(jù)量小于排序緩沖區(qū)隙笆，則使用內(nèi)存進(jìn)行快速排序操作锌蓄。如果內(nèi)存不夠，會(huì)先將數(shù)據(jù)分塊撑柔，對(duì)每個(gè)獨(dú)立的塊進(jìn)行排序,并將各個(gè)塊的排序結(jié)果放在磁盤(pán)上瘸爽，最后將各個(gè)排序好的塊進(jìn)行合并返回結(jié)果
  • mysql在新版本中使用單次排序算法，先讀取查詢(xún)所需要的所有列铅忿，然后在根據(jù)給定的列進(jìn)行排序剪决，最后直接返回排序結(jié)果。

優(yōu)化COUNT查詢(xún)

# 改查詢(xún)需要掃描474條行數(shù)
EXPLAIN select count(*)  from  system_log20190401000001 where id >5;

# 將條件反轉(zhuǎn)一下，先查詢(xún)id<5的數(shù)據(jù)柑潦，然后利用總數(shù)去做減法也能得到結(jié)果 
EXPLAIN select (select count(*)  from  system_log20190401000001)-count(*) from system_log20190401000001  where id <=5;

優(yōu)化limit分頁(yè)

  • 在做limit分頁(yè)時(shí)通常使用偏移量加排序的方式實(shí)現(xiàn)享言，但是當(dāng)偏移量非常大的時(shí)候性能非常低下，比如取10020條只返回最后20條渗鬼，前10000條都拋棄了览露，一般這種方面優(yōu)化，要嗎在頁(yè)面上做大分頁(yè)數(shù)限制譬胎，要嗎優(yōu)化大偏移量的性能差牛。
  • 優(yōu)化此類(lèi)分頁(yè)查詢(xún)的最簡(jiǎn)單辦法就是利用覆蓋索返回需要的列，防止回表操作银择。然后利用返回的數(shù)據(jù)做一次關(guān)聯(lián)返回所需要的其余列多糠。
  • 有時(shí)也可以將limit查詢(xún)轉(zhuǎn)為為已知的位置進(jìn)行查詢(xún)累舷，比如betwen 10000 and 10020
  • 另一種做法就是緩存1000條數(shù)據(jù)浩考，每次分頁(yè)從緩存中取，大于1000就在頁(yè)面額外設(shè)計(jì)按鈕找到更多數(shù)據(jù)被盈。

優(yōu)化UNION查詢(xún)

  • UNION 操作符用于合并兩個(gè)或多個(gè)SELECT語(yǔ)句的結(jié)果集
  • 默認(rèn)地析孽，UNION操作符選取不同的值。如果允許重復(fù)的值只怎，請(qǐng)使用 UNION ALL
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

# UNION ALL
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末袜瞬，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子身堡，更是在濱河造成了極大的恐慌邓尤，老刑警劉巖，帶你破解...
    沈念sama閱讀 211,817評(píng)論 6贊 492
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件贴谎，死亡現(xiàn)場(chǎng)離奇詭異汞扎，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)擅这，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,329評(píng)論 3贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門(mén)澈魄，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人仲翎，你說(shuō)我怎么就攤上這事痹扇。” “怎么了溯香？”我有些...
    開(kāi)封第一講書(shū)人閱讀 157,354評(píng)論 0贊 348
  • 道士緝兇錄：失蹤的賣(mài)姜人
    文/不壞的土叔 我叫張陵鲫构，是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我玫坛，道長(zhǎng)结笨，這世上最難降的妖魔是什么？ 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 56,498評(píng)論 1贊 284
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮禀梳，結(jié)果婚禮上杜窄，老公的妹妹穿的比我還像新娘。我一直安慰自己算途，他們只是感情好塞耕，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,600評(píng)論 6贊 386
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著嘴瓤，像睡著了一般扫外。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上廓脆，一...
    開(kāi)封第一講書(shū)人閱讀 49,829評(píng)論 1贊 290
  • 城市分裂傳說(shuō)
    那天筛谚，我揣著相機(jī)與錄音，去河邊找鬼停忿。 笑死驾讲，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的席赂。 我是一名探鬼主播吮铭，決...
    沈念sama閱讀 38,979評(píng)論 3贊 408
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開(kāi)眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼颅停！你這毒婦竟也來(lái)了谓晌？” 一聲冷哼從身側(cè)響起，我...
    開(kāi)封第一講書(shū)人閱讀 37,722評(píng)論 0贊 266
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤癞揉，失蹤者是張志新（化名）和其女友劉穎纸肉，沒(méi)想到半個(gè)月后，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體喊熟，經(jīng)...
    沈念sama閱讀 44,189評(píng)論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡柏肪，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,519評(píng)論 2贊 327
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了逊移。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片预吆。...
    茶點(diǎn)故事閱讀 38,654評(píng)論 1贊 340
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖胳泉，靈堂內(nèi)的尸體忽然破棺而出拐叉，到底是詐尸還是另有隱情，我是刑警寧澤扇商，帶...
    沈念sama閱讀 34,329評(píng)論 4贊 330
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布凤瘦，位于F島的核電站，受9級(jí)特大地震影響案铺，放射性物質(zhì)發(fā)生泄漏蔬芥。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,940評(píng)論 3贊 313
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望笔诵。 院中可真熱鬧返吻，春花似錦、人聲如沸乎婿。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 30,762評(píng)論 0贊 21
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)谢翎。三九已至捍靠，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間森逮，已是汗流浹背榨婆。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 31,993評(píng)論 1贊 266
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工， 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留褒侧，地道東北人良风。 一個(gè)月前我還...
    沈念sama閱讀 46,382評(píng)論 2贊 360
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長(zhǎng)得像璃搜，于是被迫代替她去往敵國(guó)和親拖吼。 傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,543評(píng)論 2贊 349

推薦閱讀更多精彩內(nèi)容

  • 今天看到一位朋友寫(xiě)的mysql筆記總結(jié)这吻，覺(jué)得寫(xiě)的很詳細(xì)很用心，這里轉(zhuǎn)載一下篙议，供大家參考下唾糯，也希望大家能關(guān)注他原文地...
    信仰與初衷閱讀 4,725評(píng)論 0贊 30
  • 轉(zhuǎn) # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    呂品?閱讀 9,709評(píng)論 0贊 44
  • 為了充分發(fā)揮MySQL的性能并順利地使用，就必須理解其設(shè)計(jì)鬼贱。MySQL的靈活性體現(xiàn)在很多方面移怯。例如，你可以通過(guò)配置...
    李文文丶閱讀 1,163評(píng)論 0贊 4
  • 一这难、MySQL優(yōu)化 MySQL優(yōu)化從哪些方面入手： （1）存儲(chǔ)層（數(shù)據(jù)） 構(gòu)建良好的數(shù)據(jù)結(jié)構(gòu)舟误。可以大大的提升我們S...
    寵辱不驚丶?xì)q月靜好閱讀 2,422評(píng)論 1贊 8
  • 驚蟄是一年中的第三個(gè)節(jié)氣姻乓。驚蟄時(shí)節(jié)嵌溢，早晚溫差大，陰雨天氣多蹋岩，乍暖還寒很容易導(dǎo)致感冒赖草！所以要在飲食上進(jìn)行調(diào)理和經(jīng)絡(luò)的...
    冬陽(yáng)桑梓閱讀 270評(píng)論 0贊 1