一栖秕、索引

1、索引的定義：排好序的用于查詢數(shù)據(jù)庫數(shù)據(jù)的數(shù)據(jù)結構晓避，這些數(shù)據(jù)結構以某種方式指向數(shù)據(jù)簇捍。

影響where和order by后面的操作凛剥。

2曾我、索引的優(yōu)勢：（1）提高數(shù)據(jù)檢索效率，減少磁盤IO（磁盤到內(nèi)存）碑幅。（2）減小數(shù)據(jù)排序的成本彰触，減少CPU資源消耗

索引的劣勢：（1）索引也是以文件的方式保存梯投，也會占用空間；（2）索引提高了查詢速度况毅，同時卻會降低更新表的速度分蓖。更新表的時候不僅要保存數(shù)據(jù)還要保存一下索引文件每次更新添加的索引列。

3尔许、索引的分類：單值索引（單個列）么鹤，唯一索引（唯一，可以為空）味廊，復合索引（多個列）

4蒸甜、mysql索引結構：B樹索引（主要學），哈希索引余佛，全文索引柠新，R樹索引

5、B樹索引：

B+樹：

6辉巡、哪些情況下需要建索引：

（1）主鍵自動建立唯一索引（2）頻繁作為查詢條件的字段（3）查詢中與其他表關聯(lián)的字段恨憎，外鍵關系建立索引（4）頻繁更新的字段不作為索引（5）where條件里用不到的不建索引（6）高并發(fā)下傾向于組合索引。（7）查詢中排序的字段。排序字段若通過索引訪問將大大提高排序速度憔恳。（8）查詢中統(tǒng)計或分組的字段group by

哪些情況不要建索引：

（1）表記錄太少瓤荔；（2）經(jīng)常刪改的表；（3）某個數(shù)據(jù)列包含很多重復的內(nèi)容钥组，為其建索引沒有太大效果输硝。

二、性能分析

Explain

1程梦、Explain關鍵字：模擬優(yōu)化器執(zhí)行SQL語句点把，從而知道m(xù)ysql是怎么執(zhí)行你的SQL語句的。我們可以知道表的讀取順序作烟，數(shù)據(jù)讀取的操作類型愉粤，哪些索引可以用砾医，實際用了哪些索引

2拿撩、使用：explain+SQL語句

3、

執(zhí)行explain語句的查詢結果

各個字段的意義：每一行都表示一個操作

（1）id表示操作執(zhí)行的順序如蚜。id大先執(zhí)行压恒，id相同的從上到下順序執(zhí)行。

（2）select_type :查詢的類型：SIMPLE簡單查詢（不包含子查詢或UNION）错邦、PRIMARY最外層查詢探赫、SUBQUERY子查詢（在select或where后面出現(xiàn)子查詢）、DERIVE衍生（在from后面出現(xiàn)的子查詢撬呢，會產(chǎn)生一個臨時表）伦吠、UNION（若第二個子查詢出現(xiàn)在UNION之后，則被標記為UNION魂拦，若UNION包含在FROM子句的子查詢中毛仪，則外層select被標記為Derived）、UNION RESULT（從UNION表獲取結果的select）

（3）table表示操作的表

（4）type:訪問類型芯勘，顯示查詢使用了何種類型箱靴。有8種類型。性能從高到低是：system>const>eq_ref>ref>range>index>ALL荷愕，一般來說得能保證到range衡怀，爭取ref.

system :如果表只有一行記錄，相當于系統(tǒng)表安疗，這是system類型

const:表示通過一次索引就找到了抛杨，只定位表中一條記錄。如主鍵作為where條件時荐类。select * from stu where id=1;

eq_ref:唯一性索引掃描怖现，對每個索引鍵，表中只有一條記錄與之匹配掉冶，常見于主鍵或唯一索引掃描真竖。select * from t1,t2 where t1.id=t2.id

ref:非唯一性索引掃描脐雪，返回匹配某個單值的所有行select* from stu where class_id=1

range:只檢索給定范圍的行，使用一個索引來選擇行恢共。key列顯示使用了哪個索引战秋。一般就是在where字句中between、<讨韭、>脂信、in等的查詢。

index:全索引掃描透硝。查詢的是索引字段狰闪，select id from stu

（5）possible key 和key ：可能應用在這張表上的索引，實際用到的索引key,如果為NULL,則沒有使用索引濒生，查詢中若使用了覆蓋索引埋泵，則該索引僅出現(xiàn)在key列表中。覆蓋索引罪治，查詢的列正好是建的索引列丽声。select * from su where name ='a',select * from su where name ='a' and id =1

（6）key_len:表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢時使用的索引的長度觉义，在不失精度的條件下雁社，長度越短越好。這是計算得到的長度晒骇，并非實際使用的長度

（7）ref:顯示索引的哪一列被使用了霉撵，如果可能的話是一個常數(shù)。哪些列或常量被用于查找索引列上的值洪囤。

（8）rows:有多少行被優(yōu)化器查詢徒坡。越少越好

（9）extra:包含不適合在其他列顯示但十分重要的信息。有哪些額外的信息呢箍鼓。比如

Using filesort:說明mysql會對數(shù)據(jù)使用一個外部的索引排序崭参，而不是按照表內(nèi)的索引順序進行讀取，mysql無法利用索引完成的拍序操作稱為文件排序款咖。

Using temporary：產(chǎn)生了臨時表保存中間結果何暮，mysql在對查詢結果排序時使用臨時表。常見于order by 和group by

Using index:表明相應的select操作使用了覆蓋索引铐殃，避免了訪問表的數(shù)據(jù)行海洼，效率不錯。如果同時出現(xiàn)using where 富腊，表明索引被用來執(zhí)行索引鍵值的查找坏逢；如果沒有同時出現(xiàn)using where ，表明索引用來讀取數(shù)據(jù)而非查找操作

覆蓋索引：select的數(shù)據(jù)列只用從索引中就可以獲得了，不必讀取數(shù)據(jù)行是整，Mysql可以利用索引來返回select列表中的字段肖揣，而不必再根據(jù)索引文件讀取數(shù)據(jù)文件，即查詢的列被所建的索引覆蓋（比索引的列少）浮入。

注意：如果要使用覆蓋索引龙优，一定要注意select列表只取出需要的列，不可select*事秀，因為如果將所有的字段一起做索引會導致索引文件過大彤断，查詢性能下降。

Using where表明使用了where過濾

Using join buffer使用了連接緩存

impossible where ：where條件總是false

三易迹、索引優(yōu)化案例

1宰衙、單表

建立一張article表，初始只有主鍵索引

查詢category_id為1且comments大于1的情況下睹欲，views最多的article_id.

sql 為SELECT id,author_id from article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1

性能分析的結果為

explain結果

使用了全表掃描供炼，并且出現(xiàn)了外部的索引排序，需要優(yōu)化句伶。

優(yōu)化方法：嘗試劲蜻。建立查詢相關的列陆淀，category_id,comments,views建立聯(lián)合索引考余。

之后查詢

解決了全表掃描的問題，但依然出現(xiàn)了using filesort轧苫，原因是在三個聯(lián)合索引中楚堤，第二個comments的條件使用的是范圍條件，導致后面的索引失效了含懊。

如果comments條件改為等于1身冬，則

然后繼續(xù)優(yōu)化，如果只建立category_id和views聯(lián)合索引岔乔，再次測試

檢索和排序都用到了索引酥筝。

2、兩表

新建book和class表

測試兩個表的查詢SELECT * FROM class LEFT JOIN book on class.card=book.card.

執(zhí)行了全表掃描

現(xiàn)在應該在哪個字段添加索引雏门？

在class的card字段加索引嘿歌，結果為

對class的查詢變成了index

反過來只在book的card字段添加索引，結果為

對book表的查詢變成了ref

在左連接里面分別設置左表和右表的索引對于查詢的優(yōu)化效果并不相同茁影≈娴郏可以看到對右表的相關字段做索引之后在type和rows上均有優(yōu)化。

這是由左連接的特性決定的募闲，LEFT JOIN條件用于確定如何從右表搜索行步脓，左邊的行一定有。所以在右表建索引收益更大。

如果索引不變靴患，可以修改SQL語句盡量使用到索引仍侥。

總結就是左連接在右表上建索引，右連接在左表建立索引鸳君。

3访圃、三表

新建表phone

三表的連接查詢怎么寫，索引應該怎么建相嵌？

SELECT * FROM class LEFT? JOIN? book on class.card=book.card LEFT JOIN phone ON book.card=phone.card

全表掃描

現(xiàn)在在book和phone表建立索引

ref

4腿时、結論

join語句的優(yōu)化

盡可能減少語句中的NestedLoop循環(huán)總次數(shù)；永遠用小結果集驅(qū)動大結果集饭宾；優(yōu)先保證NestedLoop的內(nèi)層循環(huán)批糟；保證Join語句中被驅(qū)動表上Join的條件字段已經(jīng)被索引。

在無法保證被驅(qū)動表的Join條件字段被索引且內(nèi)存資源充足的前提下看铆，可以設置更大的JoinBuffer

5徽鼎、避免索引失效

創(chuàng)建表staffs

創(chuàng)建聯(lián)合索引（name,age,pos）

1、全值匹配最好弹惦，就是完全按照建的聯(lián)合索引（順序）進行查詢

SELECT *FROM staffs WHERE `name`='july'

SELECT *FROM staffs WHERE `name`='july' AND age=23

SELECT *FROM staffs WHERE `name`='july' AND age=23 AND pos='dev'

三種查詢均能使用到索引否淤，但使用的長度增加，精度增加

SELECT * FROM staffs WHERE? age=23 AND pos='dev'

索引失效

2棠隐、最佳左前綴法則：查詢從索引的最左邊開始石抡，并且不能跳過列，否則造成后面的索引列失效助泽，導致只使用了部分索引啰扛。（如果最左邊的索引列在，但沒有按順序嗡贺，mysql的優(yōu)化器會幫我們優(yōu)化順序隐解，使用檢索）

3、不在索引列上做任何計算（計算诫睬、函數(shù)煞茫、（自動或手動）類型轉換）會導致索引失效轉向全表掃描。

SELECT *FROM staffs WHERE `name`='July'+'aa'

全表掃描

4摄凡、存儲引擎不能使用索引中查找范圍條件右邊的列续徽，那樣會造成后面的索引列失效

5、盡量使用覆蓋索引架谎，減少select *

6炸宵、mysql在使用不等于（！=或<>）的時候也不能使用索引

SELECT * FROM staffs WHERE `name`='July' AND age<>23 AND pos='dev'

全表掃描

7谷扣、like以通配符開頭like（‘%dev’）會導致當前索引列失效土全。

SELECT * FROM staffs WHERE `name` ='July' AND age=23 AND pos='dev'

ref

SELECT * FROM staffs WHERE `name`? like 'July%' AND age=23 AND pos='dev'（索引都用到了））

range

SELECT * FROM staffs WHERE `name`? like '%July' AND age=23 AND pos='dev'

ALL

解決like‘%字符%’時索引不被使用的方法：

使用覆蓋索引

SELECT `NAME` FROM tb_user WHERE `NAME` LIKE('%a%')

8捎琐、字符串不加單引號索引失效（可以查，但是中間發(fā)生了整數(shù)轉字符串的類型轉換裹匙，這是mysql自動轉換的瑞凑，所以索引失效）

9、少用 or,用or連接時索引會失效

四概页、試題練習

1籽御、聯(lián)合索引（c1,c2,c3,c4）

（1）where c1='a' and c2='b' and c3='c' and c4='d':索引都用到

（2）where c1='a' and c2='b' and c4='c' and c3='d':都用到了，mysql的優(yōu)化器會重新調(diào)整順序

（3）where c1='a' and c2='b' and c4='d'：只用到c1,c2

order by

（4）where c1='a' and c2='b' order by c3:? c1,c2用于查找惰匙，c3用于排序

（5）where c1='a' and c2='b' order by c4:? c1,c2用于查找技掏，出現(xiàn)filesort

（6）where c1='a' and c2='b' order by c4,c3 : c1,c2用于查找，出現(xiàn)filesort项鬼，優(yōu)化器是不會優(yōu)化order by后面排序的前后位置的

（7）where c1='a' and c2='b' and name ='aa'? order by c3,c4? :c1.c2用于查找哑梳，c3,c4用于排序

（8）where c1='a' and c2='b' order by c2,c3: c1，c2用于查找绘盟，c2,c3用于排序

（9）where c1='a' and c2='b' order by c3,c2: 后面c2的排序條件是無效的鸠真，c2是一個常量了，已經(jīng)不用排序了龄毡，不會出現(xiàn)filesort

group by

group? by 使用索引的情況與order by 相同吠卷，因為分組之前必排序

group by基本上都需要排序，會有臨時表產(chǎn)生沦零。

五祭隔、查詢截取分析

1、查詢優(yōu)化

1蠢终、永遠小表驅(qū)動大表

select * from A where id in (select id from B)

B表比A小時序攘，用in優(yōu)于exist

select * from A where exists (select 1 from B where A.id=B.id)

A是小表時，用exists優(yōu)于in

注意：A表與B表的id字段應建立索引寻拂。

exists(子查詢)只返回TRUE或FALSE,因此子查詢中的select* 也可以是select 1或select ‘x’,實際查詢是會忽略select清單

2、order by關鍵字排序優(yōu)化

建表tblA,只有兩個字段丈牢，age和birth祭钉，建立復合索引（age,birth）

Mysql支持兩種方式排序，F(xiàn)ilesort和index,index 效率高己沛，它值mysql掃描索引本身完成排序慌核，filesort方式效率低。如果排序的部分沒有完全使用索引就會產(chǎn)生filesort

（1）SELECT * FROM tbla WHERE age>20 ORDER BY age,birth? ? 可以用到索引

（2）SELECT * FROM tblaWHERE age>20 ORDER BY birth? ? 排序沒有用到birth索引申尼。產(chǎn)生filesort

（3）SELECT * FROM tbla? WHERE age>20 ORDER BY birth,age 產(chǎn)生filesort

（4）select * from tblA order by age asc,birth desc

同時升序或降序是可以用到索引排序的垮卓。

總結:order by 子句盡量使用index方式排序，避免使用filesort排序

盡可能在索引列上完成排序师幕。如果不在索引列上粟按，filesort有兩種算法：雙路排序和單路排序诬滩。

雙路排序：在mysql4.1之前使用雙路排序，兩次掃描磁盤灭将，最終得到數(shù)據(jù)疼鸟。讀取行指針累和order by 列，對他們排序庙曙，然后掃描已經(jīng)排好序的列表空镜，按照列表中的值重新從列表中讀取對應的數(shù)據(jù)輸出。從磁盤讀取排序字段捌朴，在buffer進行排序吴攒，再從磁盤讀取其他字段。取一次數(shù)據(jù)要經(jīng)歷兩次磁盤掃描砂蔽，IO耗時舶斧。

單路排序：改進的算法。從磁盤讀取查詢需要的列察皇，按照order by 列在buffer進行排序茴厉，然后掃描排序后的列表輸出，避免第二次IO,但是會使用更多的空間什荣，因為排序后的結果是保存在內(nèi)存里矾缓。

單路排序的問題：

如果單路排序時在buffer里面，如果結果數(shù)據(jù)過多稻爬，導致buffer空間不足嗜闻，每次只能對buffer大小的數(shù)據(jù)進行排序，反而導致多次IO.

如何優(yōu)化桅锄？增大sort_buffer的size琉雳，增大max_lenth_for_sort_data參數(shù)

提高order by 的速度：

（1）最好不要用select * ,出于buffer空間和max_length_for_sort_data的考慮。

（2）提高sort_buffer_size：根據(jù)系統(tǒng)的情況調(diào)整

（3）提高max_length_for_sort_data：

排序使用索引

2友瘤、慢查詢?nèi)罩?/h2>

運行時間超過確定閾值的SQL記錄到慢查詢?nèi)罩敬渲狻ＤJ閾值是10秒(大于)，long_query_time.

默認慢查詢?nèi)罩娟P閉辫秧。

set global show_query_log=1開啟慢查詢?nèi)罩?/p>

SELECT SLEEP(4) 模擬超時SQL束倍，可以把超時時間設為3秒

慢查詢?nèi)罩疚募?/div>

查看超時sql

Mysql日志分析工具：mysqldumpslow

3、批量數(shù)據(jù)腳本

使用存儲過程

插入一千萬數(shù)據(jù)

4盟戏、show profile

是mysql提供可以用來分析當前會話中語句執(zhí)行的資源消耗情況绪妹，用于SQL調(diào)優(yōu)的測量

開啟

執(zhí)行sql

查看結果show profiles

診斷sql ，show profile cpu,block io for query 3(查詢編號)

顯示cpu,io的情況柿究，還可以查memory內(nèi)存的使用情況

一條SQL的生命周期

5邮旷、全局查詢?nèi)罩?/h2>

測試環(huán)境

六、Mysql鎖機制

按照對數(shù)據(jù)的操作分為讀鎖和寫鎖蝇摸。按照鎖的粒度來分可分為表鎖和行鎖婶肩。

讀鎖（共享鎖）：針對同一份數(shù)據(jù)办陷，多個讀操作可以同時進行而不會互相影響

寫鎖（排他鎖）：當前寫操作沒有完成之前，會阻斷其他寫鎖和讀鎖狡孔。

1懂诗、表鎖

偏向MyISAM存儲引擎，開銷小苗膝，加鎖快殃恒；無死鎖，發(fā)生鎖沖突的概率最高辱揭，并發(fā)度低

show OPEN TABLES查看表的鎖情況离唐。