mysql索引和鎖機(jī)制簡介和SQL優(yōu)化專題培訓(xùn)課課件

1Mysql索引簡介4Question3Mysql鎖機(jī)制2SQL語句優(yōu)化目錄2023/1/111Mysql索引簡介4Question3Mysql鎖機(jī)制2S什么是索引select*fromScorewherescore=“77”；id,name,class,…,…,…,score,desc,date,id,name,class,…,…,…,score,desc,date,id,name,class,…,…,…,score,desc,date,讓你實(shí)現(xiàn)在1,000,000行文本文件中查找你會怎么做？for(Stringline:lines){ String[]words=line.split(","); for(Stringword:words){ if(word.equals(77)){ System.out.println(line); } }}一行一行掃描（全表掃描）？太慢，黃花菜都涼了。2023/1/12什么是索引select*fromScorewhere什么是索引二叉查找樹（binarytree）？2023/1/13什么是索引二叉查找樹（binarytree）？2022/1二叉查找樹特點(diǎn)左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快Col2的查找，可以維護(hù)一個右邊所示的二叉查找樹，每個節(jié)點(diǎn)分別包含索引鍵值和一個指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在O(log2n)的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)。雖然這是一個貨真價實(shí)的索引，但是實(shí)際的數(shù)據(jù)庫系統(tǒng)幾乎沒有使用二叉查找樹或其進(jìn)化品種紅黑樹（red-blacktree）實(shí)現(xiàn)的，原因會在下文介紹。2023/1/14二叉查找樹特點(diǎn)左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)Btree特點(diǎn)特點(diǎn)：多路搜索樹，出度大，所有關(guān)鍵字在整顆樹中出現(xiàn)，適合外部排序和查找。BTree漸進(jìn)復(fù)雜度為O(h)=O(logdN)。一般實(shí)際應(yīng)用中，出度d是非常大的數(shù)字，通常超過100，因此h非常?。ㄍǔ２怀^3）。2023/1/15Btree特點(diǎn)特點(diǎn)：多路搜索樹，出度大，所有關(guān)鍵字在整顆樹中B+tree特點(diǎn)特點(diǎn)：一般在數(shù)據(jù)庫系統(tǒng)或文件系統(tǒng)中使用的B+Tree結(jié)構(gòu)都進(jìn)行了優(yōu)化，增加了順序訪問指針，所有關(guān)鍵字都在葉子結(jié)點(diǎn)中出現(xiàn)，非葉子結(jié)點(diǎn)作為葉子結(jié)點(diǎn)的索引；B+樹總是到葉子結(jié)點(diǎn)才命中；2023/1/16B+tree特點(diǎn)特點(diǎn)：一般在數(shù)據(jù)庫系統(tǒng)或文件系統(tǒng)中使用的B+B*tree特點(diǎn)特點(diǎn)：非葉子節(jié)點(diǎn)也有鏈表；2023/1/17B*tree特點(diǎn)特點(diǎn)：非葉子節(jié)點(diǎn)也有鏈表；2022/12/2為什么使用B-Tree（B+Tree）紅黑樹等數(shù)據(jù)結(jié)構(gòu)也可以用來實(shí)現(xiàn)索引，但是文件系統(tǒng)及數(shù)據(jù)庫系統(tǒng)普遍采用B-/+Tree作為索引結(jié)構(gòu)，一般來說，索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗，相對于內(nèi)存存取，I/O存取的消耗要高幾個數(shù)量級，所以評價一個數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是在查找過程中磁盤I/O操作次數(shù)的漸進(jìn)復(fù)雜度。2023/1/18為什么使用B-Tree（B+Tree）紅黑樹等數(shù)據(jù)結(jié)構(gòu)也可以為什么使用B-Tree（B+Tree）根據(jù)B-Tree的定義，可知檢索一次最多需要訪問h個節(jié)點(diǎn)。數(shù)據(jù)庫系統(tǒng)的設(shè)計(jì)者巧妙利用了磁盤預(yù)讀原理，將一個節(jié)點(diǎn)的大小設(shè)為等于一個頁，這樣每個節(jié)點(diǎn)只需要一次I/O就可以完全載入。（Innodb的數(shù)據(jù)頁是16K，1.2.x支持8K,4K壓縮頁）。每次新建節(jié)點(diǎn)時，直接申請一個頁的空間，這樣就保證一個節(jié)點(diǎn)物理上也存儲在一個頁里，加之計(jì)算機(jī)存儲分配都是按頁對齊的，就實(shí)現(xiàn)了一個node只需一次I/O。B-Tree中一次檢索最多需要h-1次I/O（根節(jié)點(diǎn)常駐內(nèi)存），漸進(jìn)復(fù)雜度為O(h)=O(logdN)。2023/1/19為什么使用B-Tree（B+Tree）根據(jù)B-Tree的定義B+Tree頁結(jié)構(gòu)2023/1/110B+Tree頁結(jié)構(gòu)2022/12/2710MyISAM主鍵索引2023/1/111MyISAM主鍵索引2022/12/2711MyISAM非主鍵索引2023/1/112MyISAM非主鍵索引2022/12/2712Innodb主鍵索引第一個重大區(qū)別是InnoDB的數(shù)據(jù)文件本身就是索引文件。從上文知道，MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。而在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個索引結(jié)構(gòu)，這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。第二個與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應(yīng)記錄主鍵的值而不是地址2023/1/113Innodb主鍵索引第一個重大區(qū)別是InnoDB的數(shù)據(jù)文件本Innodb主鍵索引2023/1/114Innodb主鍵索引2022/12/2714Innodb非主鍵索引2023/1/115Innodb非主鍵索引2022/12/2715B+Tree的插入插入282023/1/116B+Tree的插入2022/12/2716B+Tree的插入插入702023/1/117B+Tree的插入插入702022/12/2717B+Tree的插入插入952023/1/118B+Tree的插入插入952022/12/2718建索引策略

表的主鍵、外鍵必須有索引，innodb會自動給外鍵加索引，避免死鎖。；

數(shù)據(jù)行超過1000的表應(yīng)該有索引；經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；索引應(yīng)該建在選擇性高的字段上Cardinality/rows盡可能等于1。Showindex命令查看Cardinality。索引應(yīng)該建在小字段上，整數(shù)字段尤其適合，對于大的文本字段甚至超長字段，不要建索引，或者建立前綴索引，

如createindex

索引名

on

表名(列名1(指定長度)，。。。。)頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引，數(shù)據(jù)的插入，更新和刪除會對索引產(chǎn)生影響，太多的索引會導(dǎo)致插入更新刪除操作緩慢；刪除無用的索引，避免對執(zhí)行計(jì)劃造成負(fù)面影響；2023/1/119建索引策略表的主鍵、外鍵必須有索引，innodb會自動給外建索引策略復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；B、復(fù)合索引的幾個字段是否經(jīng)常同時以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個單字段索引；D、如果復(fù)合索引所包含的字段超過3個，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；E、如果既有單字段索引，又有這幾個字段上的復(fù)合索引，一般可以刪除復(fù)合索引；2023/1/120建索引策略復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索全文索引Mysql5.6innodb1.2.x支持全文索引，不過不支持unicode和中文字符集。2023/1/121全文索引Mysql5.6innodb1.2.x支持全自適應(yīng)Hash索引2023/1/122自適應(yīng)Hash索引2022/12/2722自適應(yīng)Hash索引限制只能用于等值比較，例如=，in,<=>.無法用于排序有沖突可能Mysql自動管理，人為無法干預(yù)。2023/1/123自適應(yīng)Hash索引限制2022/12/27231Mysql索引簡介4Question3Mysql鎖機(jī)制2SQL優(yōu)化目錄2023/1/1241Mysql索引簡介4Question3Mysql鎖機(jī)制2S表結(jié)構(gòu)設(shè)計(jì)原則選擇合適的數(shù)據(jù)類型：如果能夠定長盡量定長，只要能滿足你的需求，應(yīng)盡可能使用更小的數(shù)據(jù)類型：例如使用MEDIUMINT代替INT，但要考慮業(yè)務(wù)擴(kuò)展。不要使用無法加索引的類型作為關(guān)鍵字段，比如text類型為了避免聯(lián)表查詢，有時候可以適當(dāng)?shù)臄?shù)據(jù)冗余，比如郵箱、姓名這些基本不變的數(shù)據(jù)選擇合適的表引擎，有時候MyISAM適合，有時候InnoDB適合為保證查詢性能，最好每個表都建立有auto_increment字段，建立合適的數(shù)據(jù)庫索引最好給每個字段都設(shè)定default值根據(jù)業(yè)務(wù)適當(dāng)分區(qū)（partition）數(shù)據(jù)2023/1/125表結(jié)構(gòu)設(shè)計(jì)原則選擇合適的數(shù)據(jù)類型：如果能夠定長盡量定長，只要表結(jié)構(gòu)設(shè)計(jì)原則盡量把所有的列設(shè)置為NOTNULL，如果你要保存NULL，手動去設(shè)置它，而不是把它設(shè)為默認(rèn)值。盡量少用VARCHAR、TEXT、BLOB類型2023/1/126表結(jié)構(gòu)設(shè)計(jì)原則盡量把所有的列設(shè)置為NOTNULL，如果你要分析SQL效率方法Explain分析SQL的效率，觀察表的執(zhí)行順序，使用了哪列索引，MySQL認(rèn)為在查詢中應(yīng)該檢索的記錄數(shù)，一定要避免Usingfilesort和Usingtemporary使用profile剖析SQL執(zhí)行具體過程使用SHOWFULLPROCESSLIST來查看當(dāng)前MySQL服務(wù)器線程執(zhí)行情況，是否鎖表，和查看相應(yīng)的SQL語句打開慢查詢?nèi)罩?，找出?zhí)行效率慢的SQL語句。SelectSQL_NO_CACHE*from2023/1/127分析SQL效率方法Explain分析SQL的效率，觀察表的最左前綴原理與相關(guān)優(yōu)化SHOWINDEXFROMemployees.titles;+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Null|Index_type|+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+|titles|0|PRIMARY|1|emp_no|A|NULL||BTREE||titles|0|PRIMARY|2|title|A|NULL||BTREE||titles|0|PRIMARY|3|from_date|A|443308||BTREE|+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+2023/1/128最左前綴原理與相關(guān)優(yōu)化SHOWINDEXFROMemp全列匹配EXPLAINSELECT*FROMemployees.titlesWHEREemp_no='1'ANDtitle='SeniorEngineer'ANDfrom_date='1986-06-26';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|1|SIMPLE|titles|const|PRIMARY|PRIMARY|59|const,const,const|1||+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+2023/1/129全列匹配EXPLAINSELECT*FROMempl首列匹配EXPLAINSELECT*FROMtitlesWHEREemp_no='1';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+|1|SIMPLE|titles|ref|PRIMARY|PRIMARY|4|const|1||+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+2023/1/130首列匹配EXPLAINSELECT*FROMtitl第二列未匹配EXPLAINSELECT*FROMtitlesWHEREemp_no='1'ANDfrom_date='1986-06-26';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|1|SIMPLE|titles|ref|PRIMARY|PRIMARY|4|const|1|Usingwhere|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+2023/1/131第二列未匹配EXPLAINSELECT*FROMti未匹配EXPLAINSELECT*FROMtitlesWHEREfrom_date='1986-06-26';+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+|1|SIMPLE|titles|ALL|NULL|NULL|NULL|NULL|443308|Usingwhere|+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+2023/1/132未匹配EXPLAINSELECT*FROMtitleLike匹配EXPLAINSELECT*FROMtitlesWHEREemp_no='1'ANDtitleLIKE'Senior%';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|1|SIMPLE|titles|range|PRIMARY|PRIMARY|56|NULL|1|Usingwhere|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+2023/1/133Like匹配EXPLAINSELECT*FROMtiLike未匹配EXPLAINSELECT*FROMtitlesWHEREemp_no='1'ANDtitleLIKE‘%Senior%';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|1|SIMPLE|titles|ref|PRIMARY|PRIMARY|4|const|1|Usingwhere|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+2023/1/134Like未匹配EXPLAINSELECT*FROMt范圍匹配EXPLAINSELECT*FROMtitlesWHEREemp_no<'12'andtitle='SeniorEngineer';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|1|SIMPLE|titles|range|PRIMARY|PRIMARY|4|NULL|16|Usingwhere|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+范圍列可以用到索引（必須是最左前綴），但是范圍列后面的列無法用到索引。同時，索引最多用于一個范圍列，因此如果查詢條件中有兩個范圍列則無法全用到索引。2023/1/135范圍匹配EXPLAINSELECT*FROMtitlBetween匹配EXPLAINSELECT*FROMtitlesWHEREemp_noBETWEEN‘1’AND‘100’ANDtitle='SeniorEngineer'ANDfrom_dateBETWEEN'1986-01-01'AND'1986-12-31';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|1|SIMPLE|titles|range|PRIMARY|PRIMARY|59|NULL|16|Usingwhere|+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+“BETWEEN”實(shí)際上相當(dāng)于“IN”2023/1/136Between匹配EXPLAINSELECT*FROM函數(shù)無法匹配EXPLAINSELECT*FROMtitlesWHEREemp_no='1'ANDleft(title,6)='Senior';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|1|SIMPLE|titles|ref|PRIMARY|PRIMARY|4|const|1|Usingwhere|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+2023/1/137函數(shù)無法匹配EXPLAINSELECT*FROMti左側(cè)表達(dá)式無法匹配EXPLAINSELECT*FROMtitlesWHEREemp_no-1=10000;+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+|1|SIMPLE|titles|ALL|NULL|NULL|NULL|NULL|443308|Usingwhere|+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+2023/1/138左側(cè)表達(dá)式無法匹配EXPLAINSELECT*FROM索引與排序mysql>EXPLAINSELECT*FROMtitlesWHEREemp_no='1'orderbytitle,from_date;+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|1|SIMPLE|titles|ref|PRIMARY|PRIMARY|4|const|1|Usingwhere|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+Extract里面沒有使用Usingfilesort2023/1/139索引與排序mysql>EXPLAINSELECT*F索引與排序EXPLAINSELECT*FROMtitlesWHEREemp_no>0orderbyemp_no,title;/*可以使用索引*/下面是不能使用索引的例子：EXPLAINSELECT*FROMtitlesWHEREemp_no=1orderbytitleDESC,from_dateASC;/*排序順序不一致*/EXPLAINSELECT*FROMtitlesWHEREemp_no=1orderbytitle,to_date/*使用了一個不在索引中的字段*/EXPLAINSELECT*FROMtitlesWHEREemp_no=1orderbyfrom_date;/*跳過了一個索引字段，無法達(dá)成最左前綴*/2023/1/140索引與排序EXPLAINSELECT*FROMtit索引與排序EXPLAINSELECT*FROMtitlesWHEREemp_no=1andtitlein('SeniorEngineer')orderbyfrom_date;EXPLAINSELECT*FROMtitlesWHEREemp_no=1andtitlein(‘SeniorEngineer’,’Engineer2’)orderbyfrom_date;/*多個等于條件對排序來說是范圍查詢*/EXPLAINSELECT*FROMtitlesWHEREemp_no>0orderbytitle,from_date;/*第一個為范圍查詢，所以無法索引其余列*/盡量使用索引來實(shí)現(xiàn)排序輸出，避免filesort操作。2023/1/141索引與排序EXPLAINSELECT*FROMtit覆蓋索引如果從輔助索引（SecondaryIndex）中就可以得到查詢的記錄，不需要到聚集索引中獲取數(shù)據(jù)，大大減少IO操作，所以性能較好。如：selectid,namefromScore；使用了name列的secondaryIndex。2023/1/142覆蓋索引如果從輔助索引（SecondaryIndex）中就未覆蓋索引select*fromScore；select*fromScorewherename=‘Eric’；selectclass

fromScorewherename=‘Eric’；需要再查詢一遍PrimaryIndex索引。2023/1/143未覆蓋索引select*fromScore；2022/左連接還是子查詢？5.6之前是建議用左連接代替子查詢。5.6之后通過showprofiles比較后再決定。select

payment

from

salary

where

rank=(

SELECT

rank

from

ranks

where

title=(

SELECT

title

from

jobs

where

employee='張三')

);

select

payment

from

salary

s,ranks

r,jobs

j

where

j.employee='張三'

and

j.title

=

r.title

and

s.rank

=

r.rank;

2023/1/144左連接還是子查詢？5.6之前是建議用左連接代替子查詢。202使用自增字段做主鍵如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁寫滿，就會自動開辟一個新的頁如果使用非自增主鍵（如果身份證號或?qū)W號等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁得中間某個位置。2023/1/145使用自增字段做主鍵如果表使用自增主鍵，那么每次插入新的記錄，使用自增字段做主鍵此時MySQL不得不為了將新記錄插到合適位置而移動數(shù)據(jù)，甚至目標(biāo)頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZETABLE來重建表并優(yōu)化填充頁面。因此，只要可以，請盡量在InnoDB上采用自增字段做主鍵。2023/1/146使用自增字段做主鍵此時MySQL不得不為了將新記錄插到合適位Select指定列來代替select*在某些情況下select*要比select指定列需要浪費(fèi)更多的資源如果某些列中含有text等類型，select指定列可以減少網(wǎng)絡(luò)傳輸緩沖區(qū)的使用如果SQL中含有orderby,并且排序不能利用上已用的索引那么，額外的字段會占用更多的sort_buffer_size.2023/1/147Select指定列來代替select*在某些情況下selSelectcount(*)全表掃描，不到萬不得已盡量不要使用，尤其是上百萬行的表。2023/1/148Selectcount(*)全表掃描，不到萬不得已盡量不要Selectcount(distinct)優(yōu)化selectSQL_NO_CACHEcount(distinctid)fromabc;+--------------------+|count(distinctid)|+--------------------+|415631|+--------------------+1rowinset(0.47sec)selectSQL_NO_CACHEcount(*)from(selectdistinctidfromabc)tmp;+----------+|count(*)|+----------+|415631|+----------+1rowinset(0.24sec)先通過索引把排重的記錄查找出來在count2023/1/149Selectcount(distinct)優(yōu)化selec在mysql端分頁將明顯減少用戶延遲，不管是mysql內(nèi)部處理還是網(wǎng)絡(luò)傳輸，性能都會大大提高。每次20-100行是比較可行的。當(dāng)offset比較大時：selectSQL_NO_CACHE*fromabclimit10000,10

多次運(yùn)行，時間保持在0.0187左右

selectSQL_NO_CACHE*fromabcwhereid>=100000limit10;

多次運(yùn)行，時間保持在0.0061左右，只有前者的1/3。也是帶自增ID的表的一個優(yōu)勢。加limit明顯減少客戶端延遲2023/1/150在mysql端分頁將明顯減少用戶延遲，不管是mysql內(nèi)部處Sql里面含有or不會用到索引。例如name和age都有索引：Select*fromuserwherename=‘hehe’orage=41;全表掃描改成Select*fromuserwherename=‘hehe’unionallselect*fromuserwhereage=41;可以用到索引。Or的優(yōu)化2023/1/151Sql里面含有or不會用到索引。Or的優(yōu)化2022/12/2Select*from{ selecta.id,,a.age,froma,b wherea.id=b.idorderbya.ageDESC}

astmplimit0,20;改成：Selecta.id,,a.age,fromajoinbona.id=b.idorderbya.ageDESClimit0,20;避免了子查詢。不必要的嵌套查詢2023/1/152Select*from不必要的嵌套查詢2022/12/2OnduplicatekeyupdateInsertintouservalues(…..)onduplicatekeyupdatename=‘d’,age=41;強(qiáng)烈建議使用。消除了大量業(yè)務(wù)邏輯處理，一個事務(wù)中完成，效率大大提供。Upsert操作2023/1/153OnduplicatekeyupdateUpsert操盡量少join盡量少排序盡量少or盡量用unionall代替union盡量早過濾（如ICP，條件過濾放在了數(shù)據(jù)引擎層）不必要的表自身連接Where替換Having，having在檢索出所有記錄后再進(jìn)行統(tǒng)計(jì)，避免使用。避免類型轉(zhuǎn)換比如select*fromorderwhereORDER_ID=12345；忘記加引號，而且用不上索引。避免使用MYSQL自帶函數(shù)拿不準(zhǔn)的時候盡量使用Explain和showprofiles來判斷執(zhí)行情況。一些原則2023/1/154盡量少join一些原則2022/12/27541Mysql索引簡介4Question3Mysql鎖機(jī)制2SQL優(yōu)化目錄2023/1/1551Mysql索引簡介4Question3Mysql鎖機(jī)制2SMyISAM：表鎖Innodb：行鎖nnoDB實(shí)現(xiàn)了以下兩種類型的行鎖。l

共享鎖（S）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。l

排他鎖（X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。鎖類型2023/1/156MyISAM：表鎖鎖類型2022/12/2756另外，為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（IntentionLocks），這兩種意向鎖都是表鎖。l

意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。l

意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。鎖類型2023/1/157另外，為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB請求鎖模式

是否兼容當(dāng)前鎖模式XIXSISX沖突沖突沖突沖突IX沖突兼容沖突兼容S沖突沖突兼容兼容IS沖突兼容兼容兼容鎖類型2023/1/158XIXSISX沖突沖突沖突沖突IX沖突兼容沖突兼容S沖突沖突鎖類型共享鎖（S）：SELECT*FROMtable_nameWHERE...LOCKINSHAREMODE。排他鎖（X)：SELECT*FROMtable_nameWHERE...FORUPDATE。2023/1/159鎖類型共享鎖（S）：SELECT*FROMtable_鎖類型session_1session_2mysql>setautocommit=0;QueryOK,0rowsaffected(0.00sec)mysql>SELECT*FROMabcWHEREid=2FORUPDATE;+----+-------+------------+|id|name|hehe|+----+-------+------------+|2|fasdf|fdassfasdf|+----+-------+------------+mysql>select*fromabcwhereid=2;+----+-------+------------+|id|name|hehe|+----+-------+------------+|2|fasdf|fdassfasdf|+----+-------+------------+一致性非鎖定讀。

mysql>select*fromabcwhereid=2LOCKINSHAREMODE;阻塞。mysql>commit;

+----+-------+------------+|id|name|hehe|+----+-------+------------+|2|fasdf|fdassfasdf|+----+-------+------------+2023/1/160鎖類型session_1session_2mysql>se鎖實(shí)現(xiàn)InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，這一點(diǎn)與Oracle不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！在實(shí)際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。2023/1/161鎖實(shí)現(xiàn)InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，這鎖實(shí)現(xiàn)session_1session_2mysql>setautocommit=0;QueryOK,0rowsaffected(0.00sec)mysql>select*fromabcwherename='a1'forupdate;+----+------+------------+|id|name|hehe|+----+------+------------+|1|a1|fdassfasdf|+----+------+------------+mysql>select*fromabcwherename='a2'forupdate;阻塞。mysql>commit;

+----+------+------------+|id|name|hehe|+----+------+------------+|2|a2|fdassfasdf|+----+------+------------+2023/1/162鎖實(shí)現(xiàn)session_1session_2mysql>se鎖實(shí)現(xiàn)由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)鎖沖突的。應(yīng)用設(shè)計(jì)的時候要注意這一點(diǎn)。

2023/1/163鎖實(shí)現(xiàn)由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖實(shí)現(xiàn)session_1session_2mysql>setautocommit=0;QueryOK,0rowsaffected(0.00sec)mysql>select*fromtest_indexwherevid=1andname='name1'forupdate;+----+------+-------+|id|vid|name|+----+------+-------+|1|1|name1|+----+------+-------+vid上有輔助索引，name無索引。

select*fromtest_indexwherevid=1andname='name2'forupdate;阻塞。mysql>commit;

+----+------+-------+|id|vid|name|+----+------+-------+|2|1|name2|+----+------+-------+2023/1/164鎖實(shí)現(xiàn)session_1session_2mysql>se鎖算法RecordLock:單個記錄上的鎖。GapLock：間隙鎖，鎖定一個范圍，但不包含記錄本身。Next-keyLock：鎖定一個范圍和本身RecordLock+GapLock。例如一個索引有10,11,13,20這4個值，那么索引可能被Next-keyLocking的區(qū)間為：（-∞，10）,[10,11),[11,13),[13,20),[20,+∞)Next-keyLock為了解決幻讀問題。

2023/1/165鎖算法RecordLock:單個記錄上的鎖。2022/1鎖算法算法分析：如有一個表：Createtablez(aINT,bINT,Primarykey(a),key(b));Insertintozselect1,1;Insertintozselect3,1;Insertintozselect5,3;Insertintozselect7,6;Insertintozselect10,8;

2023/1/166鎖算法算法分析：2022/12/2766鎖算法select*fromwhere

b=3forupdate;a列數(shù)據(jù):1;3;5;7;10b列數(shù)據(jù)：1;1;

3;

6;

8;對于聚集索引，僅對列a=5的行加上recordLock。對于輔助索引，鎖定范圍是（1,3]，特別需要注意的是，Innodb會對輔助索引下一個鍵值加上GapLock，即還有一個輔助索引范圍（3,6）的鎖。因此，運(yùn)行如下SQL會被阻塞：Select*fromzwherea=5lockinsharemode;Insertintozselect4,2;Insertintozselect6,5;2023/1/167鎖算法select*fromwhereb=3for鎖算法a列數(shù)據(jù):1;3;5;7;10b列數(shù)據(jù)：1;1;

3;

6;

8;運(yùn)行如下SQL不會被阻塞：Insertintozselect8,6;Insertintozselect2,0;Insertintozselect6,7;2023/1/168鎖算法a列數(shù)據(jù):1;3;5;7;102022死鎖事務(wù)A等待事務(wù)B，同時事務(wù)B等待事務(wù)A，會產(chǎn)生死鎖。Innodb有死鎖檢測進(jìn)程，如果檢測到死鎖，會馬上拋出異常并回滾一個事務(wù)（另一個繼續(xù)執(zhí)行）。2023/1/169死鎖事務(wù)A等待事務(wù)B，同時事務(wù)B等待事務(wù)A，會產(chǎn)生死鎖。20死鎖session_1session_2mysql>setautocommit=0;QueryOK,0rowsaffected(0.00sec)mysql>select*fromabcwhereid=1forupdate;|id|name|hehe||1|a1|fdassfasdf|mysql>setautocommit=0;

mysql>select*fromabcwhereid=2forupdate;|id|vid|name||2|1|name2|

mysql>select*fromabcwhereid=2forupdate;|id|name|hehe||2|a2|fdassfasdf|mysql>select*fromabcwhereid=1forupdate;ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction2023/1/170死鎖session_1session_2mysql>set死鎖Wait-forgraph事務(wù)T1需要等待事務(wù)T2，畫一條T1到T2的線。以此類推。最后整個圖如果有回路就表示有死鎖。深度優(yōu)先算法實(shí)現(xiàn)。注意：OLTP事務(wù)要短。不要不commit也不rollback。2023/1/171死鎖Wait-forgraph2022/12/2771Q&A歡迎大家提出疑問，或者新的需求。Q&A2023/1/172Q&AQ&A2022/12/27722023/1/1732022/12/27731Mysql索引簡介4Question3Mysql鎖機(jī)制2SQL語句優(yōu)化目錄2023/1/1741Mysql索引簡介4Question3Mysql鎖機(jī)制2S什么是索引select*fromScorewherescore=“77”；id,name,class,…,…,…,score,desc,date,id,name,class,…,…,…,score,desc,date,id,name,class,…,…,…,score,desc,date,讓你實(shí)現(xiàn)在1,000,000行文本文件中查找你會怎么做？for(Stringline:lines){ String[]words=line.split(","); for(Stringword:words){ if(word.equals(77)){ System.out.println(line); } }}一行一行掃描（全表掃描）？太慢，黃花菜都涼了。2023/1/175什么是索引select*fromScorewhere什么是索引二叉查找樹（binarytree）？2023/1/176什么是索引二叉查找樹（binarytree）？2022/1二叉查找樹特點(diǎn)左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快Col2的查找，可以維護(hù)一個右邊所示的二叉查找樹，每個節(jié)點(diǎn)分別包含索引鍵值和一個指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在O(log2n)的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)。雖然這是一個貨真價實(shí)的索引，但是實(shí)際的數(shù)據(jù)庫系統(tǒng)幾乎沒有使用二叉查找樹或其進(jìn)化品種紅黑樹（red-blacktree）實(shí)現(xiàn)的，原因會在下文介紹。2023/1/177二叉查找樹特點(diǎn)左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)Btree特點(diǎn)特點(diǎn)：多路搜索樹，出度大，所有關(guān)鍵字在整顆樹中出現(xiàn)，適合外部排序和查找。BTree漸進(jìn)復(fù)雜度為O(h)=O(logdN)。一般實(shí)際應(yīng)用中，出度d是非常大的數(shù)字，通常超過100，因此h非常?。ㄍǔ２怀^3）。2023/1/178Btree特點(diǎn)特點(diǎn)：多路搜索樹，出度大，所有關(guān)鍵字在整顆樹中B+tree特點(diǎn)特點(diǎn)：一般在數(shù)據(jù)庫系統(tǒng)或文件系統(tǒng)中使用的B+Tree結(jié)構(gòu)都進(jìn)行了優(yōu)化，增加了順序訪問指針，所有關(guān)鍵字都在葉子結(jié)點(diǎn)中出現(xiàn)，非葉子結(jié)點(diǎn)作為葉子結(jié)點(diǎn)的索引；B+樹總是到葉子結(jié)點(diǎn)才命中；2023/1/179B+tree特點(diǎn)特點(diǎn)：一般在數(shù)據(jù)庫系統(tǒng)或文件系統(tǒng)中使用的B+B*tree特點(diǎn)特點(diǎn)：非葉子節(jié)點(diǎn)也有鏈表；2023/1/180B*tree特點(diǎn)特點(diǎn)：非葉子節(jié)點(diǎn)也有鏈表；2022/12/2為什么使用B-Tree（B+Tree）紅黑樹等數(shù)據(jù)結(jié)構(gòu)也可以用來實(shí)現(xiàn)索引，但是文件系統(tǒng)及數(shù)據(jù)庫系統(tǒng)普遍采用B-/+Tree作為索引結(jié)構(gòu)，一般來說，索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上。這樣的話，索引查找過程中就要產(chǎn)生磁盤I/O消耗，相對于內(nèi)存存取，I/O存取的消耗要高幾個數(shù)量級，所以評價一個數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是在查找過程中磁盤I/O操作次數(shù)的漸進(jìn)復(fù)雜度。2023/1/181為什么使用B-Tree（B+Tree）紅黑樹等數(shù)據(jù)結(jié)構(gòu)也可以為什么使用B-Tree（B+Tree）根據(jù)B-Tree的定義，可知檢索一次最多需要訪問h個節(jié)點(diǎn)。數(shù)據(jù)庫系統(tǒng)的設(shè)計(jì)者巧妙利用了磁盤預(yù)讀原理，將一個節(jié)點(diǎn)的大小設(shè)為等于一個頁，這樣每個節(jié)點(diǎn)只需要一次I/O就可以完全載入。（Innodb的數(shù)據(jù)頁是16K，1.2.x支持8K,4K壓縮頁）。每次新建節(jié)點(diǎn)時，直接申請一個頁的空間，這樣就保證一個節(jié)點(diǎn)物理上也存儲在一個頁里，加之計(jì)算機(jī)存儲分配都是按頁對齊的，就實(shí)現(xiàn)了一個node只需一次I/O。B-Tree中一次檢索最多需要h-1次I/O（根節(jié)點(diǎn)常駐內(nèi)存），漸進(jìn)復(fù)雜度為O(h)=O(logdN)。2023/1/182為什么使用B-Tree（B+Tree）根據(jù)B-Tree的定義B+Tree頁結(jié)構(gòu)2023/1/183B+Tree頁結(jié)構(gòu)2022/12/2710MyISAM主鍵索引2023/1/184MyISAM主鍵索引2022/12/2711MyISAM非主鍵索引2023/1/185MyISAM非主鍵索引2022/12/2712Innodb主鍵索引第一個重大區(qū)別是InnoDB的數(shù)據(jù)文件本身就是索引文件。從上文知道，MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址。而在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個索引結(jié)構(gòu)，這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。第二個與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應(yīng)記錄主鍵的值而不是地址2023/1/186Innodb主鍵索引第一個重大區(qū)別是InnoDB的數(shù)據(jù)文件本Innodb主鍵索引2023/1/187Innodb主鍵索引2022/12/2714Innodb非主鍵索引2023/1/188Innodb非主鍵索引2022/12/2715B+Tree的插入插入282023/1/189B+Tree的插入2022/12/2716B+Tree的插入插入702023/1/190B+Tree的插入插入702022/12/2717B+Tree的插入插入952023/1/191B+Tree的插入插入952022/12/2718建索引策略

表的主鍵、外鍵必須有索引，innodb會自動給外鍵加索引，避免死鎖。；

數(shù)據(jù)行超過1000的表應(yīng)該有索引；經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引；索引應(yīng)該建在選擇性高的字段上Cardinality/rows盡可能等于1。Showindex命令查看Cardinality。索引應(yīng)該建在小字段上，整數(shù)字段尤其適合，對于大的文本字段甚至超長字段，不要建索引，或者建立前綴索引，

如createindex

索引名

on

表名(列名1(指定長度)，。。。。)頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引，數(shù)據(jù)的插入，更新和刪除會對索引產(chǎn)生影響，太多的索引會導(dǎo)致插入更新刪除操作緩慢；刪除無用的索引，避免對執(zhí)行計(jì)劃造成負(fù)面影響；2023/1/192建索引策略表的主鍵、外鍵必須有索引，innodb會自動給外建索引策略復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；B、復(fù)合索引的幾個字段是否經(jīng)常同時以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個單字段索引；D、如果復(fù)合索引所包含的字段超過3個，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；E、如果既有單字段索引，又有這幾個字段上的復(fù)合索引，一般可以刪除復(fù)合索引；2023/1/193建索引策略復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索全文索引Mysql5.6innodb1.2.x支持全文索引，不過不支持unicode和中文字符集。2023/1/194全文索引Mysql5.6innodb1.2.x支持全自適應(yīng)Hash索引2023/1/195自適應(yīng)Hash索引2022/12/2722自適應(yīng)Hash索引限制只能用于等值比較，例如=，in,<=>.無法用于排序有沖突可能Mysql自動管理，人為無法干預(yù)。2023/1/196自適應(yīng)Hash索引限制2022/12/27231Mysql索引簡介4Question3Mysql鎖機(jī)制2SQL優(yōu)化目錄2023/1/1971Mysql索引簡介4Question3Mysql鎖機(jī)制2S表結(jié)構(gòu)設(shè)計(jì)原則選擇合適的數(shù)據(jù)類型：如果能夠定長盡量定長，只要能滿足你的需求，應(yīng)盡可能使用更小的數(shù)據(jù)類型：例如使用MEDIUMINT代替INT，但要考慮業(yè)務(wù)擴(kuò)展。不要使用無法加索引的類型作為關(guān)鍵字段，比如text類型為了避免聯(lián)表查詢，有時候可以適當(dāng)?shù)臄?shù)據(jù)冗余，比如郵箱、姓名這些基本不變的數(shù)據(jù)選擇合適的表引擎，有時候MyISAM適合，有時候InnoDB適合為保證查詢性能，最好每個表都建立有auto_increment字段，建立合適的數(shù)據(jù)庫索引最好給每個字段都設(shè)定default值根據(jù)業(yè)務(wù)適當(dāng)分區(qū)（partition）數(shù)據(jù)2023/1/198表結(jié)構(gòu)設(shè)計(jì)原則選擇合適的數(shù)據(jù)類型：如果能夠定長盡量定長，只要表結(jié)構(gòu)設(shè)計(jì)原則盡量把所有的列設(shè)置為NOTNULL，如果你要保存NULL，手動去設(shè)置它，而不是把它設(shè)為默認(rèn)值。盡量少用VARCHAR、TEXT、BLOB類型2023/1/199表結(jié)構(gòu)設(shè)計(jì)原則盡量把所有的列設(shè)置為NOTNULL，如果你要分析SQL效率方法Explain分析SQL的效率，觀察表的執(zhí)行順序，使用了哪列索引，MySQL認(rèn)為在查詢中應(yīng)該檢索的記錄數(shù)，一定要避免Usingfilesort和Usingtemporary使用profile剖析SQL執(zhí)行具體過程使用SHOWFULLPROCESSLIST來查看當(dāng)前MySQL服務(wù)器線程執(zhí)行情況，是否鎖表，和查看相應(yīng)的SQL語句打開慢查詢?nèi)罩?，找出?zhí)行效率慢的SQL語句。SelectSQL_NO_CACHE*from2023/1/1100分析SQL效率方法Explain分析SQL的效率，觀察表的最左前綴原理與相關(guān)優(yōu)化SHOWINDEXFROMemployees.titles;+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Null|Index_type|+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+|titles|0|PRIMARY|1|emp_no|A|NULL||BTREE||titles|0|PRIMARY|2|title|A|NULL||BTREE||titles|0|PRIMARY|3|from_date|A|443308||BTREE|+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+2023/1/1101最左前綴原理與相關(guān)優(yōu)化SHOWINDEXFROMemp全列匹配EXPLAINSELECT*FROMemployees.titlesWHEREemp_no='1'ANDtitle='SeniorEngineer'ANDfrom_date='1986-06-26';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|1|SIMPLE|titles|const|PRIMARY|PRIMARY|59|const,const,const|1||+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+2023/1/1102全列匹配EXPLAINSELECT*FROMempl首列匹配EXPLAINSELECT*FROMtitlesWHERE