個(gè)人專題目錄
1. 性能下降SQL慢 執(zhí)行時(shí)間長(zhǎng) 等待時(shí)間長(zhǎng)
1.1 查詢語(yǔ)句寫(xiě)的爛
1.2 索引失效
單值
復(fù)合
1.3 關(guān)聯(lián)查詢太多join(設(shè)計(jì)缺陷或不得已的需求)
1.4 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置(緩沖\線程數(shù)等)
2. 常見(jiàn)通用的join查詢
2.1 SQL執(zhí)行順序
手寫(xiě)
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
機(jī)讀
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
總結(jié)
2.2 Join圖
2.3 建表SQL
2.4 7種Join
3. 索引簡(jiǎn)介
3.1 是什么
MySQL官方對(duì)索引的定義為：索引(Index)是幫助MySQL高校獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)俊性。 可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)
- 索引的目的在于提高查詢效率箍铭，可以類比字典。
- 如果要查‘mysql’這個(gè)單詞，我們肯定需求定位到m字母，然后從上往下找到y(tǒng)字母，再找到sql.
- 如果沒(méi)有索引，順序遍歷
你可以簡(jiǎn)單理解為"排好序的快速查找數(shù)據(jù)結(jié)構(gòu)"驳阎。
詳解(重要)
結(jié)論
數(shù)據(jù)本身之外,數(shù)據(jù)庫(kù)還維護(hù)著一個(gè)滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)馁蒂， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實(shí)現(xiàn)高級(jí)查找算法,這種數(shù)據(jù)結(jié)構(gòu)就是索引呵晚。
一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中沫屡，因此索引往往以文件形式存儲(chǔ)在硬盤(pán)上
我們平時(shí)所說(shuō)的索引饵隙，如果沒(méi)有特別指明，都是指B樹(shù)(多路搜索樹(shù)谁鳍，并不一定是二叉樹(shù))結(jié)構(gòu)組織的索引癞季。其中聚集索引，次要索引倘潜，覆蓋索引绷柒， 復(fù)合索引，前綴索引涮因，唯一索引默認(rèn)都是使用B+樹(shù)索引废睦，統(tǒng)稱索引。當(dāng)然,除了B+樹(shù)這種類型的索引之外养泡，還有哈希索引(hash index)等嗜湃。
3.2 優(yōu)勢(shì)
類似大學(xué)圖書(shū)館建書(shū)目索引，提高數(shù)據(jù)檢索效率澜掩，降低數(shù)據(jù)庫(kù)的IO成本
通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序购披，降低數(shù)據(jù)排序成本，降低了CPU的消耗
為什么要?jiǎng)?chuàng)建索引肩榕？這是因?yàn)楦斩福瑒?chuàng)建索引可以大大提高系統(tǒng)的查詢性能。
第一、通過(guò)創(chuàng)建唯一性索引筐乳，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性歌殃。
第二、可以大大加快 數(shù)據(jù)的檢索速度蝙云，這也是創(chuàng)建索引的最主要的原因氓皱。
第三、可以加速表和表之間的連接勃刨，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義波材。
第四、在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)朵你，同樣可以顯著減少查詢中分組和排序的時(shí)間各聘。
第五揣非、通過(guò)使用索引抡医，可以在查詢的過(guò)程中，使用查詢優(yōu)化器早敬，提高系統(tǒng)的性能忌傻。
3.3 劣勢(shì)
實(shí)際上索引也是一張表，該表保存了主鍵和索引字段搞监，并指向?qū)嶓w表的記錄,所以索引列也是要占用空間的
雖然索引大大提高了查詢速度水孩，同時(shí)卻會(huì)降低更新表的速度,如果對(duì)表INSERT,UPDATE和DELETE。 因?yàn)楦卤頃r(shí)琐驴，MySQL不僅要不存數(shù)據(jù)俘种，還要保存一下索引文件每次更新添加了索引列的字段， 都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息
索引只是提高效率的一個(gè)因素绝淡，如果你的MySQL有大數(shù)據(jù)量的表宙刘，就需要花時(shí)間研究建立優(yōu)秀的索引，或優(yōu)化查詢語(yǔ)句
3.4 mysql索引分類
單值索引
即一個(gè)索引只包含單個(gè)列牢酵，一個(gè)表可以有多個(gè)單列索引
建議一張表索引不要超過(guò)5個(gè)
優(yōu)先考慮復(fù)合索引
唯一索引
索引列的值必須唯一悬包，但允許有空值
復(fù)合索引
即一個(gè)索引包含多個(gè)列
基本語(yǔ)法
創(chuàng)建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
如果是CHAR,VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度馍乙； 如果是BLOB和TEXT類型布近，必須指定length。
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));
刪除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM table_name\G
使用ALTER命令
3.5 mysql索引結(jié)構(gòu)
BTree索引
B-Tree索引丝格，顧名思義撑瞧，就是所有的索引節(jié)點(diǎn)都按照balance tree的數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)。B-tree結(jié)構(gòu)可以顯著減少定位記錄時(shí)所經(jīng)歷的中間過(guò)程显蝌，從而加快存取速度预伺。
B-tree中，每個(gè)結(jié)點(diǎn)包含：
1、本結(jié)點(diǎn)所含關(guān)鍵字的個(gè)數(shù)扭屁；
2算谈、指向父結(jié)點(diǎn)的指針；
3料滥、關(guān)鍵字然眼；
4、指向子結(jié)點(diǎn)的指針葵腹；
對(duì)于一棵m階B-tree高每，每個(gè)結(jié)點(diǎn)至多可以擁有m個(gè)子結(jié)點(diǎn)。各結(jié)點(diǎn)的關(guān)鍵字和可以擁有的子結(jié)點(diǎn)數(shù)都有限制践宴，規(guī)定m階B-tree中鲸匿，根結(jié)點(diǎn)至少有2個(gè)子結(jié)點(diǎn)，除非根結(jié)點(diǎn)為葉子節(jié)點(diǎn)阻肩，相應(yīng)的带欢，根結(jié)點(diǎn)中關(guān)鍵字的個(gè)數(shù)為1m-1；非根結(jié)點(diǎn)至少有[m/2]（[]烤惊，向上取整）個(gè)子結(jié)點(diǎn)乔煞，相應(yīng)的，關(guān)鍵字個(gè)數(shù)為[m/2]-1m-1柒室。
B-tree有以下特性：
1渡贾、關(guān)鍵字集合分布在整棵樹(shù)中；
2雄右、任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)結(jié)點(diǎn)中空骚；
3、搜索有可能在非葉子結(jié)點(diǎn)結(jié)束擂仍；
4囤屹、其搜索性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找；
5防楷、自動(dòng)層次控制牺丙；
由于限制了除根結(jié)點(diǎn)以外的非葉子結(jié)點(diǎn)，至少含有M/2個(gè)兒子复局，確保了結(jié)點(diǎn)的至少利用率冲簿，其最低搜索性能為：
其中，M為設(shè)定的非葉子結(jié)點(diǎn)最多子樹(shù)個(gè)數(shù)亿昏，N為關(guān)鍵字總數(shù)峦剔；
所以B-樹(shù)的性能總是等價(jià)于二分查找（與M值無(wú)關(guān)），也就沒(méi)有B樹(shù)平衡的問(wèn)題角钩；
由于M/2的限制吝沫，在插入結(jié)點(diǎn)時(shí)呻澜，如果結(jié)點(diǎn)已滿，需要將結(jié)點(diǎn)分裂為兩個(gè)各占M/2的結(jié)點(diǎn)惨险；刪除結(jié)點(diǎn)時(shí)羹幸，需將兩個(gè)不足M/2的兄弟結(jié)點(diǎn)合并。
初始化介紹
一顆b+樹(shù)辫愉，淺藍(lán)色的塊我們稱為一個(gè)磁盤(pán)塊栅受，可以看到每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)(深藍(lán)色表示)和指針(黃色表示)，如磁盤(pán)塊1包含數(shù)據(jù)項(xiàng)17和35恭朗，包含指針P1屏镊、P2、P3.
P1表示小于17的磁盤(pán)塊痰腮，P2表示在17到35之間的磁盤(pán)塊而芥，P3表示大于35的磁盤(pán)塊。
真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)膀值，即3棍丐，5，9虫腋，10骄酗，13稀余，15悦冀，28，29睛琳，36...
非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)盒蟆，只存儲(chǔ)引擎搜索方向的數(shù)據(jù)項(xiàng)，如17师骗、35并不是真實(shí)存于數(shù)據(jù)表中历等。
【查找過(guò)程】
如果要查找數(shù)據(jù)項(xiàng)29，那么首先會(huì)把磁盤(pán)塊1由磁盤(pán)加載到內(nèi)存辟癌，此時(shí)發(fā)生一次IO寒屯，在內(nèi)存中用二分查找確實(shí)29在17和35之間，鎖定磁盤(pán)塊1的P2指針黍少，內(nèi)存時(shí)間因?yàn)榉浅６?相比磁盤(pán)的IO)可以忽略不計(jì)寡夹，通過(guò)磁盤(pán)塊1的P2指針的磁盤(pán)地址把磁盤(pán)塊3由磁盤(pán)加載到內(nèi)存，發(fā)生第二次IO厂置，29在26和30之間菩掏，鎖定磁盤(pán)塊3的P2指針，通過(guò)指針加載磁盤(pán)塊8到內(nèi)存昵济，發(fā)生第三次IO智绸，同時(shí)內(nèi)存中做二分查找找到29野揪，結(jié)束查詢，總計(jì)三次IO瞧栗。
Btree索引(或Balanced Tree)斯稳，是一種很普遍的數(shù)據(jù)庫(kù)索引結(jié)構(gòu)，oracle默認(rèn)的索引類型（本文也主要依據(jù)oracle來(lái)講）迹恐。其特點(diǎn)是定位高效平挑、利用率高、自我平衡系草，特別適用于高基數(shù)字段通熄，定位單條或小范圍數(shù)據(jù)非常高效。理論上找都，使用Btree在億條數(shù)據(jù)與100條數(shù)據(jù)中定位記錄的花銷相同唇辨。
數(shù)據(jù)結(jié)構(gòu)利用率高、定位高效
Btree索引的數(shù)據(jù)結(jié)構(gòu)如下：
結(jié)構(gòu)看起來(lái)Btree索引與Binary Tree相似能耻，但在細(xì)節(jié)上有所不同赏枚，上圖中用不同顏色的標(biāo)示出了Btree索引的幾個(gè)主要特點(diǎn)：
樹(shù)形結(jié)構(gòu)：由根節(jié)(root)、分支(branches)晓猛、葉(leaves)三級(jí)節(jié)點(diǎn)組成饿幅，其中分支節(jié)點(diǎn)可以有多層。
多分支結(jié)構(gòu)：與binary tree不相同的是戒职，btree索引中單root/branch可以有多個(gè)子節(jié)點(diǎn)(超過(guò)2個(gè))栗恩。
雙向鏈表：整個(gè)葉子節(jié)點(diǎn)部分是一個(gè)雙向鏈表(后面會(huì)描述這個(gè)設(shè)計(jì)的作用)
單個(gè)數(shù)據(jù)塊中包括多條索引記錄
這里先把幾個(gè)特點(diǎn)羅列出來(lái)，后面會(huì)說(shuō)到各自的作用洪燥。
結(jié)構(gòu)上Btree與Binary Tree的區(qū)別磕秤，在于binary中每節(jié)點(diǎn)代表一個(gè)數(shù)值，而balanced中root和Btree節(jié)點(diǎn)中記錄了多條”值范圍”條目(如:[60-70][70-80])捧韵，這些”值范圍”條目分別指向在其范圍內(nèi)的葉子節(jié)點(diǎn)市咆。既root與branch可以有多個(gè)分支，而不一定是兩個(gè)再来，對(duì)數(shù)據(jù)塊的利用率更高蒙兰。
在Leaf節(jié)點(diǎn)中，同樣也是存放了多條索引記錄芒篷，這些記錄就是具體的索引列值搜变，和與其對(duì)應(yīng)的rowid。另外梭伐，在葉節(jié)點(diǎn)層上痹雅，所有的節(jié)點(diǎn)在組成了一個(gè)雙向鏈表。
了解基本結(jié)構(gòu)后糊识，下圖展示定位數(shù)值82的過(guò)程：
演算如下：
讀取root節(jié)點(diǎn)绩社，判斷82大于在0-120之間摔蓝，走左邊分支。
讀取左邊branch節(jié)點(diǎn)愉耙，判斷82大于80且小于等于120贮尉，走右邊分支。
讀取右邊leaf節(jié)點(diǎn)朴沿，在該節(jié)點(diǎn)中找到數(shù)據(jù)82及對(duì)應(yīng)的rowid
使用rowid去物理表中讀取記錄數(shù)據(jù)塊(如果是count或者只select rowid猜谚，則最后一次讀取不需要)
在整個(gè)索引定位過(guò)程中，數(shù)據(jù)塊的讀取只有3次赌渣。既三次I/O后定位到rowid魏铅。
而由于Btree索引對(duì)結(jié)構(gòu)的利用率很高，定位高效坚芜。當(dāng)1千萬(wàn)條數(shù)據(jù)時(shí)览芳，Btree索引也是三層結(jié)構(gòu)(依稀記得億級(jí)數(shù)據(jù)才是3層與4層的分水嶺)。定位記錄仍只需要三次I/O鸿竖，這便是開(kāi)頭所說(shuō)的沧竟，100條數(shù)據(jù)和1千萬(wàn)條數(shù)據(jù)的定位，在btree索引中的花銷是一樣的缚忧。
平衡擴(kuò)張
除了利用率高悟泵、定位高效外，Btree的另一個(gè)特點(diǎn)是能夠永遠(yuǎn)保持平衡闪水，這與它的擴(kuò)張方式有關(guān)糕非。(unbalanced和hotspot是兩類問(wèn)題，之前我一直混在一起)敦第，先描述下Btree索引的擴(kuò)張方式：
新建一個(gè)索引峰弹，索引上只會(huì)有一個(gè)leaf節(jié)點(diǎn)，取名為Node A芜果，不斷的向這個(gè)leaf節(jié)點(diǎn)中插入數(shù)據(jù)后，直到這個(gè)節(jié)點(diǎn)滿融师，這個(gè)過(guò)程如下圖（綠色表示新建/空閑狀態(tài)右钾，紅色表示節(jié)點(diǎn)沒(méi)有空余空間）：
當(dāng)Node A滿之后，我們?cè)傧虮碇胁迦胍粭l記錄旱爆，此時(shí)索引就需要做拆分處理：會(huì)新分配兩個(gè)數(shù)據(jù)塊NodeB & C舀射，如果新插入的值，大于當(dāng)前最大值怀伦，則將Node A中的值全部插入Node B中脆烟，將新插入的值放到Node C中；否則按照5-5比例房待，將已有數(shù)據(jù)分別插入到NodeB與C中邢羔。
無(wú)論采用哪種分割方式驼抹，之前的leaf節(jié)點(diǎn)A，將變成一個(gè)root節(jié)點(diǎn)拜鹤，保存兩個(gè)范圍條目框冀，指向B與C，結(jié)構(gòu)如下圖（按第一種拆分形式）：
當(dāng)Node C滿之后敏簿，此時(shí) Node A仍有空余空間存放條目明也，所以不需要再拆分，而只是新分配一個(gè)數(shù)據(jù)塊Node D惯裕，將在Node A中創(chuàng)建指定到Node D的條目：
如果當(dāng)根節(jié)點(diǎn)Node A也滿了温数，則需要進(jìn)一步拆分：新建Node E&F&G，將Node A中范圍條目拆分到E&F兩個(gè)節(jié)點(diǎn)中蜻势，并建立E&F到BCD節(jié)點(diǎn)的關(guān)聯(lián)帆吻，向Node G插入索引值。此時(shí)E&F為branch節(jié)點(diǎn)咙边，G為leaf節(jié)點(diǎn)猜煮，A為Root節(jié)點(diǎn)：
在整個(gè)擴(kuò)張過(guò)程中，Btree自身總能保持平衡败许，Leaf節(jié)點(diǎn)的深度能一直保持一致王带。
實(shí)際應(yīng)用中的一些問(wèn)題
前面說(shuō)完了Btree索引的結(jié)構(gòu)與擴(kuò)張邏輯，接下來(lái)講一些Btree索引在應(yīng)用中的一些問(wèn)題：
單一方向擴(kuò)展引起的索引競(jìng)爭(zhēng)(Index Contention)
若索引列使用sequence或者timestamp這類只增不減的數(shù)據(jù)類型市殷。這種情況下Btree索引的增長(zhǎng)方向總是不變的愕撰，不斷的向右邊擴(kuò)展，因?yàn)樾虏迦氲闹涤肋h(yuǎn)是最大的醋寝。
當(dāng)一個(gè)最大值插入到leaf block中后搞挣，leaf block要向上傳播，通知上層節(jié)點(diǎn)更新所對(duì)應(yīng)的“值范圍”條目中的最大值音羞，因此所有靠右邊的block(從leaf 到branch甚至root)都需要做更新操作囱桨，并且可能因?yàn)閴K寫(xiě)滿后執(zhí)行塊拆分。
如果并發(fā)插入多個(gè)最大值嗅绰，則最右邊索引數(shù)據(jù)塊的的更新與拆分都會(huì)存在爭(zhēng)搶舍肠，影響效率。在AWR報(bào)告中可以通過(guò)檢測(cè)enq: TX – index contention事件的時(shí)間來(lái)評(píng)估爭(zhēng)搶的影響窘面。解決此類問(wèn)題可以使用Reverse Index解決翠语，不過(guò)會(huì)帶來(lái)新的問(wèn)題。
Index Browning 索引枯萎(不知道該怎么翻譯這個(gè)名詞财边，就是指leaves節(jié)點(diǎn)”死”了肌括，樹(shù)枯萎了)
其實(shí)oracle針對(duì)這個(gè)問(wèn)題有優(yōu)化機(jī)制，但優(yōu)化的不徹底酣难，所以還是要拿出來(lái)的說(shuō)谍夭。
我們知道當(dāng)表中的數(shù)據(jù)刪除后黑滴，索引上對(duì)應(yīng)的索引值是不會(huì)刪除的，特別是在一性次刪除大批量數(shù)據(jù)后慧库，會(huì)造成大量的dead leaf掛到索引樹(shù)上跷跪。考慮以下示例齐板，如果表100以上的數(shù)據(jù)會(huì)部被刪除了吵瞻，但這些記錄仍在索引中存在，此時(shí)若對(duì)該列取max()：
通過(guò)與之前相同演算甘磨，找到了索引樹(shù)上最大的數(shù)據(jù)塊橡羞，按照記錄最大的值應(yīng)該在這里，但發(fā)現(xiàn)這數(shù)據(jù)塊里的數(shù)據(jù)已經(jīng)被清空了济舆，與是利用Btree索引的另一個(gè)特點(diǎn)：leaves節(jié)點(diǎn)是一個(gè)雙向列表卿泽，若數(shù)據(jù)沒(méi)有找到就去臨近的一個(gè)數(shù)據(jù)塊中看看，在這個(gè)數(shù)據(jù)塊中發(fā)現(xiàn)了最大值99滋觉。
在計(jì)算最大值的過(guò)程中签夭，這次的定位多加載了一個(gè)數(shù)據(jù)塊，再極端的情況下椎侠，大批量的數(shù)據(jù)被刪除第租，就會(huì)造成大量訪問(wèn)這些dead leaves。
針對(duì)這個(gè)問(wèn)題的一般解決辦法是重建索引我纪，但記住! 重建索引并不是最優(yōu)方案慎宾，詳細(xì)原因可以看看這。使用coalesce語(yǔ)句來(lái)整理這些dead leaves到freelist中浅悉，就可以避免這些問(wèn)題趟据。理論上oracle中這步操作是可以自動(dòng)完成的，但在實(shí)際中一次性大量刪除數(shù)據(jù)后术健，oracle在短時(shí)間內(nèi)是反應(yīng)不過(guò)來(lái)的汹碱。
Hash索引，了解
full-text全文索引苛坚，F(xiàn)ull-text索引就是我們常說(shuō)的全文索引比被，他的存儲(chǔ)結(jié)構(gòu)也是b-tree。主要是為了解決在我們需要用like查詢的低效問(wèn)題泼舱。只能解決’xxx%’的like查詢。如：字段數(shù)據(jù)為ABCDE枷莉，索引建立為- A娇昙、AB、ABC笤妙、ABCD冒掌、ABCDE五個(gè)噪裕。
R-Tree索引，了解
3.6 哪些情況需要?jiǎng)?chuàng)建索引
1.主鍵自動(dòng)建立唯一索引
2.頻繁作為查詢的條件的字段應(yīng)該創(chuàng)建索引
3.查詢中與其他表關(guān)聯(lián)的字段股毫，外鍵關(guān)系建立索引
4.頻繁更新的字段不適合創(chuàng)建索引
因?yàn)槊看胃虏粏螁问歉铝擞涗涍€會(huì)更新索引膳音，加重IO負(fù)擔(dān)
5.Where條件里用不到的字段不創(chuàng)建索引
6.單間/組合索引的選擇問(wèn)題，who铃诬？（在高并發(fā)下傾向創(chuàng)建組合索引）
7.查詢中排序的字段祭陷，排序字段若通過(guò)索引去訪問(wèn)將大大提高排序的速度
8.查詢中統(tǒng)計(jì)或者分組字段
建立索引，一般按照select的where條件來(lái)建立趣席，比如： select的條件是where f1 and f2兵志，那么如果我們?cè)谧侄蝔1或字段f2上建立索引是沒(méi)有用的，只有在字段f1和f2上同時(shí)建立索引才有用等宣肚。
3.7 哪些情況不要?jiǎng)?chuàng)建索引
1.表記錄太少
2.經(jīng)常增刪改的表,這是因?yàn)橄牒保薷男阅芎蜋z索性能是互相矛盾的。當(dāng)增加索引時(shí)霉涨，會(huì)提高檢索性能按价，但是會(huì)降低修改性能。當(dāng)減少索引時(shí)笙瑟，會(huì)提高修改性能楼镐，降低檢索性能。因此逮走，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)鸠蚪，不應(yīng)該創(chuàng)建索引。
3.數(shù)據(jù)重復(fù)且分布平均的表字段师溅，因此應(yīng)該只為經(jīng)常查詢和經(jīng)常排序的數(shù)據(jù)列建立索引茅信。 注意，如果某個(gè)數(shù)據(jù)列包含許多重復(fù)的內(nèi)容墓臭，為它建立索引就沒(méi)有太大的實(shí)際效果蘸鲸。
假如一個(gè)表有10萬(wàn)行記錄，有一個(gè)字段A只胡T和F兩種值窿锉，且每個(gè)值的分布概率大約為50%酌摇，那么對(duì)這種表A字段建索引一般不會(huì)提高數(shù)據(jù)庫(kù)的查詢速度。
索引的選擇性是指索引列中不同值的數(shù)據(jù)與不用跟記錄數(shù)的比嗡载。如果一個(gè)表中有2000條記錄采郎，表索引列有1980個(gè)不同的值镐作，那么這個(gè)索引的選擇性是1980/2000=0.99。一個(gè)索引的選擇性越接近于1，這個(gè)索引的效率就越高火俄。
- 對(duì)于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因?yàn)椋@些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少享幽。
3.8 索引管理
普通索引
這是最基本的索引，它沒(méi)有任何限制MyIASM中默認(rèn)的BTREE類型的索引拾弃，也是我們大多數(shù)情況下用到的索引值桩。
創(chuàng)建索引
CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , INDEX index_name (title(5)))
查看索引
SHOW INDEX FROM [table_name]
SHOW KEYS FROM [table_name] # 只在MySQL中可以使用keys關(guān)鍵字。
刪除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
唯一索引
與普通索引類似豪椿，不同的就是：索引列的值必須唯一奔坟，但允許有空值（注意和主鍵不同）。如果是組合索引砂碉，則列值的組合必須唯一蛀蜜，創(chuàng)建方法和普通索引類似
創(chuàng)建索引
CREATE UNIQUE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD UNIQUE index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , UNIQUE index_name (title(length)))
全文索引（FULLTEXT）
MySQL從3.23.23版開(kāi)始支持全文索引和全文檢索，F(xiàn)ULLTEXT索引僅可用于 MyISAM 表增蹭；他們可以從CHAR滴某、VARCHAR或TEXT列中作為CREATE TABLE語(yǔ)句的一部分被創(chuàng)建，或是隨后使用ALTER TABLE 或CREATE INDEX被添加滋迈。
對(duì)于較大的數(shù)據(jù)集霎奢，將你的資料輸入一個(gè)沒(méi)有FULLTEXT索引的表中，然后創(chuàng)建索引饼灿，其速度比把資料輸入現(xiàn)有FULLTEXT索引的速度更為快幕侠。不過(guò)切記對(duì)于大容量的數(shù)據(jù)表，生成全文索引是一個(gè)非常消耗時(shí)間非常消耗硬盤(pán)空間的做法碍彭。
創(chuàng)建索引
CREATE FULLTEXT INDEX index_name ON table_name(column(length))
ALTER TABLE table_name ADD FULLTEXT index_name( column)
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , FULLTEXT index_name (title))
組合索引（最左前綴）
CREATE TABLE article(id int not null, title varchar(255), time date);
平時(shí)用的SQL查詢語(yǔ)句一般都有比較多的限制條件晤硕，所以為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引庇忌。例如上表中針對(duì)title和time建立一個(gè)組合索引：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))舞箍。建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面兩組組合索引：
–title,time
–title
為什么沒(méi)有time這樣的組合索引呢皆疹？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果疏橄。簡(jiǎn)單的理解就是只從最左面的開(kāi)始組合。并不是只要包含這兩列的查詢都會(huì)用到該組合索引略就，如下面的幾個(gè)SQL所示：
1捎迫，使用到上面的索引
SELECT * FROM article WHERE title='測(cè)試' AND time=1234567890;
SELECT * FROM article WHERE title='測(cè)試';
2，不使用上面的索引
SELECT * FROM article WHERE time=1234567890;
創(chuàng)建索引
CREATE INDEX index_name ON table_name (column_list)
4. 性能分析
4.1 MySQL Query Optimizer
- Mysql中有專門(mén)負(fù)責(zé)優(yōu)化的SELECT語(yǔ)句的優(yōu)化器模塊表牢，主要功能：通過(guò)計(jì)算分析系統(tǒng)中收集到的統(tǒng)計(jì)信息窄绒，為客戶端請(qǐng)求的Query提供他認(rèn)為最優(yōu)的執(zhí)行計(jì)劃（他認(rèn)為最優(yōu)的數(shù)據(jù)檢索方式，但不見(jiàn)得是DBA認(rèn)為最優(yōu)的崔兴，這部分最耗費(fèi)時(shí)間）
- 當(dāng)客戶端向MYSQL請(qǐng)求一條Query颗祝，命令解析器模塊完成請(qǐng)求分類浊闪，區(qū)別出是SELECT并轉(zhuǎn)發(fā)給Mysql Query Optimizer恼布，MySQL Query Optimizer 首先會(huì)對(duì)整條Query進(jìn)行優(yōu)化螺戳，處理掉一些常量表達(dá)式的預(yù)算，直接換算成常量值折汞。并對(duì)Query中的查詢條件進(jìn)行簡(jiǎn)化和轉(zhuǎn)換倔幼，如去掉一些無(wú)用或顯而易見(jiàn)的條件、結(jié)構(gòu)調(diào)等爽待。然后分析Query中的Hint信息（如果有）损同，看顯示Hint信息是否可以完全確定該Query的執(zhí)行計(jì)劃。如果沒(méi)有Hint或Hint信息還不足以完全確定執(zhí)行計(jì)劃鸟款，則會(huì)讀取所涉及對(duì)象的統(tǒng)計(jì)信息膏燃，根據(jù)Query進(jìn)行寫(xiě)相應(yīng)的計(jì)算分析，然后再得出最后的執(zhí)行計(jì)劃何什。
4.2 MySQL常見(jiàn)瓶頸
CPU:CPU在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入在內(nèi)存或從磁盤(pán)上讀取數(shù)據(jù)時(shí)候
IO:磁盤(pán)I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量時(shí)
服務(wù)器硬件的性能瓶頸：top,free,iostat和vmstat來(lái)查看系統(tǒng)的性能狀態(tài)
4.3 Explain
是什么（查看執(zhí)行計(jì)劃）
使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語(yǔ)句组哩，從而知道MySQL是 如何處理你的SQL語(yǔ)句的。分析你的查詢語(yǔ)句或是結(jié)構(gòu)的性能瓶頸
官網(wǎng)介紹
能干嘛
表的讀取順序
數(shù)據(jù)讀取操作的操作類型
哪些索引可以使用
哪些索引被實(shí)際使用
表之間的引用
每張表有多少行被優(yōu)化器查詢
怎么玩
Explain+SQL語(yǔ)句
執(zhí)行計(jì)劃包含的信息
+----+-------------+-------+------+---------------+------+---------+------+------+-------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------
各個(gè)字段解釋
id
select查詢的序列號(hào)处渣，包含一組數(shù)字伶贰，表示查詢中執(zhí)行select子句或操作表的順序
三種情況
id相同，執(zhí)行順序由上至下
id不同罐栈，如果是子查詢黍衙，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高荠诬，越先被執(zhí)行
id相同不同琅翻，同時(shí)存在
select_type:查詢的類型，主要用于區(qū)別 普通查詢柑贞、聯(lián)合查詢方椎、子查詢等的復(fù)雜查詢
| 類型名稱 | 描述 |
|---|---|
| SIMPLE | 簡(jiǎn)單的select查詢，查詢中不包含子查詢或者UNION |
| PRIMARY | 查詢中若包含任何復(fù)雜的子部分凌外，最外層查詢則被標(biāo)記為 |
| SUBQUERY | 在SELECT或者WHERE列表中包含了子查詢 |
| DERIVED | 在FROM列表中包含的子查詢被標(biāo)記為DERIVED（衍生） MySQL會(huì)遞歸執(zhí)行這些子查詢辩尊，把結(jié)果放在臨時(shí)表里。 |
| UNION | 若第二個(gè)SELECT出現(xiàn)在UNION之后康辑，則被標(biāo)記為UNION; 若UNION包含在FROM子句的子查詢中摄欲，外層SELECT將被標(biāo)記為：DERIVED |
| UNION RESULT | 從UNION表獲取結(jié)果的SELECT |
table:顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
type
ALL index range ref eq_ref const,system NULL
訪問(wèn)類型排列
type顯示的是訪問(wèn)類型，是較為重要的一個(gè)指標(biāo)疮薇，結(jié)果值從最好到最壞依次是：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
顯示查詢使用了何種類型 從最好到最差依次是:
system>const>eq_ref>ref>range>index>ALL
一般來(lái)說(shuō)胸墙，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref.
system
表只有一行記錄（等于系統(tǒng)表）按咒，這是const類型的特例迟隅，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可以忽略不計(jì)
const
表示通過(guò)索引一次就找到了，const用于比較primary key或者unique索引智袭。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)奔缠，所以很快。如將主鍵至于where列表中吼野，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量
eq_ref
唯一性索引校哎，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配瞳步，常見(jiàn)于主鍵或唯一索引掃描
ref
非唯一索引掃描闷哆，返回匹配某個(gè)單獨(dú)值的所有行。 本質(zhì)上也是一種索引訪問(wèn)单起，它返回所有匹配某個(gè)單獨(dú)值的行抱怔，然而， 它可能會(huì)找到多個(gè)符合條件的行嘀倒，所以他應(yīng)該屬于查找和掃描的混合體
range
只檢索給定范圍的行屈留，使用一個(gè)索引來(lái)選擇行。key列顯示使用了哪個(gè)索引 一般就是在你的where語(yǔ)句中出現(xiàn)了between括儒、<绕沈、>、in等的查詢 這種范圍掃描索引掃描比全表掃描要好帮寻，因?yàn)樗恍枰_(kāi)始索引的某一點(diǎn)乍狐，而結(jié)束語(yǔ)另一點(diǎn)，不用掃描全部索引
index
Full Index Scan,index與ALL區(qū)別為index類型只遍歷索引樹(shù)固逗。這通常比ALL快浅蚪，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。 （也就是說(shuō)雖然all和index都是讀全表烫罩，但index是從索引中讀取的惜傲，而all是從硬盤(pán)中讀的）
all
FullTable Scan,將遍歷全表以找到匹配的行
備注：
一般來(lái)說(shuō)，得保證查詢只是達(dá)到range級(jí)別贝攒，最好達(dá)到ref
possible_keys
顯示可能應(yīng)用在這張表中的索引,一個(gè)或多個(gè)盗誊。 查詢涉及的字段上若存在索引，則該索引將被列出隘弊，但不一定被查詢實(shí)際使用
key
實(shí)際使用的索引哈踱。如果為null則沒(méi)有使用索引
查詢中若使用了覆蓋索引，則索引和查詢的select字段重疊
參閱: 3.USING index
key_len
表示索引中使用的字節(jié)數(shù)梨熙，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度开镣。在不損失精確性的情況下，長(zhǎng)度越短越好
key_len顯示的值為索引最大可能長(zhǎng)度咽扇，并非實(shí)際使用長(zhǎng)度邪财，即key_len是根據(jù)表定義計(jì)算而得陕壹，不是通過(guò)表內(nèi)檢索出的
ref
顯示索引那一列被使用了，如果可能的話树埠，是一個(gè)常數(shù)糠馆。那些列或常量被用于查找索引列上的值
rows
根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)
越少越好
Extra
包含不適合在其他列中顯示但十分重要的額外信息
1.Using filesort
說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序弥奸，而不是按照表內(nèi)的索引順序進(jìn)行讀取榨惠。 MySQL中無(wú)法利用索引完成排序操作成為“文件排序”
2.Using temporary
使用了臨時(shí)表保存中間結(jié)果，MySQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表盛霎。常見(jiàn)于排序order by 和分組查詢 group by
3.USING index
表示相應(yīng)的select操作中使用了覆蓋索引（Coveing Index）,避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)耽装！ 如果同時(shí)出現(xiàn)using where愤炸，表明索引被用來(lái)執(zhí)行索引鍵值的查找； 如果沒(méi)有同時(shí)出現(xiàn)using where掉奄，表面索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作规个。
覆蓋索引（Covering Index）
- 覆蓋索引（Convering Index），一說(shuō)為索引覆蓋姓建。
- 理解方式一：就是select的數(shù)據(jù)列只用從索引中就能夠取得诞仓，不必讀取數(shù)據(jù)行，Mysql可以利用索引返回select列表中的字段速兔，而不必根據(jù)索引再次讀取數(shù)據(jù)文件墅拭，換句話說(shuō)查詢表要被所建的索引覆蓋。
- 理解方式二：索引是高效找到行的一個(gè)方法涣狗，但是一般數(shù)據(jù)庫(kù)也能使用索引找到一個(gè)列的數(shù)據(jù)谍婉，因此它不必讀取整個(gè)行。畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了它的索引的數(shù)據(jù);當(dāng)能通過(guò)讀取索引就可以得到想要的數(shù)據(jù)镀钓，那就不需要讀取行了穗熬。一個(gè)索引包含了(或覆蓋了)滿足查詢結(jié)果的數(shù)據(jù)就叫覆蓋索引。
- 注意：如果要使用覆蓋索引丁溅，一定要注意select列表中只取出需要的列唤蔗，不能select *,因?yàn)槿绻麑⑺凶侄我黄鹱鏊饕龝?huì)導(dǎo)致索引文件過(guò)大，查詢性能下降窟赏。
4.Using where
表面使用了where過(guò)濾
5.using join buffer
使用了連接緩存
6.impossible where
where子句的值總是false妓柜，不能用來(lái)獲取任何元組
7.select tables optimized away
在沒(méi)有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作或者 對(duì)于MyISAM存儲(chǔ)引擎優(yōu)化COUNT(*)操作饰序，不必等到執(zhí)行階段再進(jìn)行計(jì)算领虹， 查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化。
8.distinct
優(yōu)化distinct求豫，在找到第一匹配的元組后即停止找同樣值的工作
熱身Case
5. 索引優(yōu)化
索引不會(huì)包含有NULL值的列
只要列中包含有NULL值都將不會(huì)被包含在索引中塌衰，組合索引中只要有一列含有NULL值诉稍，那么這一列對(duì)于此組合索引就是無(wú)效的。所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL最疆。create table table_name(c1 varchar(32) default ‘0’)
使用短索引
對(duì)串列進(jìn)行索引杯巨，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如努酸，如果有一個(gè)CHAR(255)的列服爷，如果在前10個(gè)或20個(gè)字符內(nèi)，多數(shù)值是惟一的获诈，那么就不要對(duì)整個(gè)列進(jìn)行索引仍源。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤(pán)空間和I/O操作。
CREATE INDEX index_name ON table_name (column(length))
索引列排序
MySQL查詢只使用一個(gè)索引舔涎，因此如果where子句中已經(jīng)使用了索引的話笼踩，那么order by中的列是不會(huì)使用索引的。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求的情況下不要使用排序操作亡嫌；盡量不要包含多個(gè)列的排序嚎于，如果需要最好給這些列創(chuàng)建復(fù)合索引。
like語(yǔ)句操作
一般情況下不鼓勵(lì)使用like操作挟冠，如果非使用不可于购，如何使用也是一個(gè)問(wèn)題。like “%aaa%” 不會(huì)使用索引知染，而like “aaa%”可以使用索引肋僧。
不要在列上進(jìn)行運(yùn)算
例如：select * from users where YEAR(adddate)<2007，將在每個(gè)行上進(jìn)行運(yùn)算持舆，這將導(dǎo)致索引失效而進(jìn)行全表掃描色瘩，因此我們可以改成：select * from users where adddate<’2007-01-01′
最后總結(jié)一下，MySQL只對(duì)以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時(shí)候的like(不以通配符%或_開(kāi)頭的情形)逸寓。而理論上每張表里面最多可創(chuàng)建16個(gè)索引居兆，不過(guò)除非是數(shù)據(jù)量真的很多，否則過(guò)多的使用索引也不是那么好玩的竹伸。
建議：一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)泥栖，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
5.1 索引分析
單表
建表SQL
案例
兩表
建表SQL
案例
三表
建表SQL
案例
5.2 索引失效（應(yīng)該避免）
建表SQL
案例（索引失效）
1.全值匹配我最愛(ài)
2.最佳左前綴法則
如果索引了多例勋篓，要遵守最左前綴法則吧享。指的是查詢從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列。
3.不在索引列上做任何操作（計(jì)算譬嚣、函數(shù)钢颂、（自動(dòng)or手動(dòng)）類型轉(zhuǎn)換），會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
4.存儲(chǔ)引擎不能使用索引中范圍條件右邊的列
5.盡量使用覆蓋索引（只訪問(wèn)索引的查詢（索引列和查詢列一致））拜银，減少select*
6.mysql在使用不等于（殊鞭！=或者<>）的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描
7.is null,is not null 也無(wú)法使用索引
8.like以通配符開(kāi)頭（'$abc...'）mysql索引失效會(huì)變成全表掃描操作
問(wèn)題：解決like'%字符串%'索引不被使用的方法遭垛？？
1操灿、可以使用主鍵索引
2锯仪、使用覆蓋索引，查詢字段必須是建立覆蓋索引字段
3趾盐、當(dāng)覆蓋索引指向的字段是varchar(380)及380以上的字段時(shí)庶喜，覆蓋索引會(huì)失效！
9.字符串不加單引號(hào)索引失效
10.少用or,用它連接時(shí)會(huì)索引失效
11.小總結(jié)
假設(shè)index(a,b,c)
| where語(yǔ)句 | 索引是否被使用 |
|---|---|
| where a=3 | Y,使用到a |
| where a=3 and b=5 | Y,使用到a,b |
| where a=3 and b=5 and c=4 | Y,使用到a,b,c |
| where b=3 或者 where b=3 and c=4 或者 where c=4 | N |
| where a=3 and c=5 | 使用到a救鲤，但是c不可以 |
| where a=3 and b>4 and c=5 | 使用到a和b |
| where a=3 and b like 'kk%' and c=4 | Y,使用到a,b,c |
| where a=3 and b like '%kk' and c=4 | Y,只用到a |
| where a=3 and b like '%kk%' and c=4 | Y,只用到a |
| where a=3 and b like 'k%kk%' and c=4 | Y,使用到a,b,c |
like KK%相當(dāng)于=常量 %KK和%KK% 相當(dāng)于范圍
【優(yōu)化總結(jié)口訣】
會(huì)值匹配我最愛(ài)久窟，最左前綴要遵守；
帶頭大哥不能死蜒简，中間兄弟不能斷瘸羡；
索引列上少計(jì)算，范圍之后會(huì)失效搓茬；
LIKE百分寫(xiě)最右，覆蓋索引不寫(xiě)星队他；
不等空值還有or卷仑，索引失敗要少用；
VAR引號(hào)不可丟麸折，SQL高級(jí)也不難锡凝！
定值、范圍還是排序垢啼，一般order by是給個(gè)范圍
group by 基本上都需要進(jìn)行排序窜锯，會(huì)有臨時(shí)表產(chǎn)生
5.3 一般性建議
對(duì)于單鍵索引，盡量選擇針對(duì)當(dāng)前query過(guò)濾性更好的索引
在選擇組合索引的時(shí)候芭析，當(dāng)前Query中過(guò)濾性最好的字段在索引字段順序中锚扎，位置越靠前越好。
在選擇組合索引的時(shí)候馁启，盡量選擇可以能包含當(dāng)前query中的where子句中更多字段的索引
盡可能通過(guò)分析統(tǒng)計(jì)信息和調(diào)整query的寫(xiě)法來(lái)達(dá)到選擇合適索引的目的
避免全表掃描
對(duì)查詢進(jìn)行優(yōu)化驾孔，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引惯疙。
避免判斷null值
應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷翠勉，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：
select id from t where num is null
可以在num上設(shè)置默認(rèn)值0霉颠，確保表中num列沒(méi)有null值对碌，然后這樣查詢：
select id from t where num=0
避免不等值判斷
應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描蒿偎。
避免使用or邏輯
應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件朽们，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描怀读，如：
select id from t where num=10 or num=20
可以這樣查詢：
select id from t where num=10
union all
select id from t where num=20
慎用in和not in邏輯
in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描华坦，如：
select id from t1 where num in(select id from t2 where id > 10)
此時(shí)外層查詢會(huì)全表掃描愿吹，不使用索引∠Ы悖可以修改為：
select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id
此時(shí)索引被使用犁跪，可以明顯提升查詢效率。
注意模糊查詢
下面的查詢也將導(dǎo)致全表掃描：
select id from t where name like '%abc%'
模糊查詢?nèi)绻潜匾獥l件時(shí)歹袁，可以使用select id from t where name like 'abc%'來(lái)實(shí)現(xiàn)模糊查詢坷衍，此時(shí)索引將被使用。如果頭匹配是必要邏輯条舔，建議使用全文搜索引擎（Elastic search枫耳、Lucene、Solr等）孟抗。
避免查詢條件中字段計(jì)算
應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作迁杨，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：
select id from t where num/2=100
應(yīng)改為:
select id from t where num=100*2
避免查詢條件中對(duì)字段進(jìn)行函數(shù)操作
應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作凄硼，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描铅协。如：
select id from t where substring(name,1,3)='abc'--name以abc開(kāi)頭的id
應(yīng)改為:
select id from t where name like 'abc%'
WHERE子句“=”左邊注意點(diǎn)
不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算摊沉，否則系統(tǒng)將可能無(wú)法正確使用索引狐史。
組合索引使用
在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引说墨，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引骏全，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致尼斧。
不要定義無(wú)異議的查詢
不要寫(xiě)一些沒(méi)有意義的查詢姜贡，如需要生成一個(gè)空表結(jié)構(gòu)：
select col1,col2 into #t from t where 1=0
這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的突颊，應(yīng)改成這樣：
create table #t(...)
exists
很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：
select num from a where num in(select num from b)
用下面的語(yǔ)句替換：
select num from a where exists(select 1 from b where num=a.num)
索引也可能失效
并不是所有索引對(duì)查詢都有效鲁豪，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)律秃，SQL查詢可能不會(huì)去利用索引爬橡，如一表中有字段sex，male棒动、female幾乎各一半糙申，那么即使在sex上建了索引也對(duì)查詢效率起不了作用。
表格字段類型選擇
盡量使用數(shù)字型字段船惨，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型柜裸，這會(huì)降低查詢和連接的性能缕陕，并會(huì)增加存儲(chǔ)開(kāi)銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符疙挺，而對(duì)于數(shù)字型而言只需要比較一次就夠了扛邑。
盡可能的使用 varchar 代替 char ，因?yàn)槭紫瓤勺冮L(zhǎng)度字段存儲(chǔ)空間小铐然，可以節(jié)省存儲(chǔ)空間蔬崩，其次對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些搀暑。
查詢語(yǔ)法中的字段
任何地方都不要使用 select * from t 沥阳，用具體的字段列表代替“*”，不要返回用不到的任何字段自点。
索引無(wú)關(guān)優(yōu)化
不使用*桐罕、盡量不使用union，union all等關(guān)鍵字桂敛、盡量不使用or關(guān)鍵字功炮、盡量使用等值判斷。
表連接建議不超過(guò)5個(gè)术唬。如果超過(guò)5個(gè)死宣，則考慮表格的設(shè)計(jì)。（互聯(lián)網(wǎng)應(yīng)用中）
表連接方式使用外聯(lián)優(yōu)于內(nèi)聯(lián)碴开。
外連接有基礎(chǔ)數(shù)據(jù)存在。如：A left join B,基礎(chǔ)數(shù)據(jù)是A博秫。
A inner join B潦牛，沒(méi)有基礎(chǔ)數(shù)據(jù)的，先使用笛卡爾積完成全連接挡育，在根據(jù)連接條件得到內(nèi)連接結(jié)果集巴碗。
大數(shù)據(jù)量級(jí)的表格做分頁(yè)查詢時(shí)，如果頁(yè)碼數(shù)量過(guò)大即寒，則使用子查詢配合完成分頁(yè)邏輯橡淆。
Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)
