索引覆蓋是指如果查詢(xún)的列恰好是索引的一部分,那么查詢(xún)只需要在索引文件上進(jìn)行,不需要回行到磁盤(pán)再找數(shù)據(jù)。這種查詢(xún)速度非常快,稱(chēng)為”索引覆蓋”
站在用戶(hù)的角度思考問(wèn)題，與客戶(hù)深入溝通，找到古塔網(wǎng)站設(shè)計(jì)與古塔網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶(hù)體驗(yàn)好的作品，建站類(lèi)型包括：做網(wǎng)站、成都網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名注冊(cè)、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋古塔地區(qū)。
? ? 1查詢(xún)頻繁????2區(qū)分度高????3長(zhǎng)度小????4盡量能覆蓋常用查詢(xún)字段
索引長(zhǎng)度直接影響索引文件的大小,影響增刪改的速度,并間接影響查詢(xún)速度(占用內(nèi)存多)。因此對(duì)于一些長(zhǎng)短不同的字節(jié)，我們會(huì)針對(duì)列中的值,從左往右截取部分,來(lái)建索引。但是：
1:截的越短, 重復(fù)度越高,區(qū)分度越小, 索引效果越不好
2:截的越長(zhǎng), 重復(fù)度越低,區(qū)分度越高, 索引效果越好,但帶來(lái)的影響也越大--增刪改變慢,并間影響查詢(xún)速度.
所以,我們要在 ?區(qū)分度 + 長(zhǎng)度 ?兩者上,取得一個(gè)平衡（ distinct?去重 ）
? ? select count (distinct?left (word,6)) / count (*) from tablename;
對(duì)于一般的系統(tǒng)應(yīng)用區(qū)別度能達(dá)到 0.1 ,索引的性能就可以接受.
? ? alter table tablename add index word(word(4));
給字符串類(lèi)型的字段建立索引效率不高，但是必須要經(jīng)常查這個(gè)字段怎么建索引？
比如說(shuō)一個(gè)字段url，類(lèi)型是字符串。那么可以建一個(gè)字段 crcurl 來(lái)存儲(chǔ)url字段crc32后的值，并給 crcurl 建立索引。
???crc32：循環(huán)冗余校驗(yàn)。根據(jù)網(wǎng)上數(shù)據(jù)包或計(jì)算機(jī)文件等數(shù)據(jù)產(chǎn)生簡(jiǎn)短固定位數(shù)校驗(yàn)碼的一種散列函數(shù)，主要用來(lái)檢測(cè)或校驗(yàn)數(shù)據(jù)傳輸或者保存后可能出現(xiàn)的錯(cuò)誤。生成的數(shù)字在傳輸或者存儲(chǔ)之前計(jì)算出來(lái)并且附加到數(shù)據(jù)后面，然后接收方進(jìn)行檢驗(yàn)確定數(shù)據(jù)是否發(fā)生變化。一般來(lái)說(shuō)，循環(huán)冗余校驗(yàn)的值都是32位的整數(shù)。
crc32 是整形，在MySQL中，給整形字段建立索引效率比較高，crc32雖然不能確保唯一性，但是無(wú)礙，相同的機(jī)率也是極小，關(guān)鍵是可以大大減少查詢(xún)的范圍，給crcurl這個(gè)字段建立索引，查詢(xún)的時(shí)候帶上crcurl字段就可以利用到索引。
? ? ? ? 不允許翻過(guò)100頁(yè)（百度搜索一般到70頁(yè)左右）?
首先我們直接大數(shù)據(jù)分頁(yè)limit 5000000,10? 發(fā)現(xiàn)耗時(shí)4.41秒
接下來(lái)我們轉(zhuǎn)換方式使用where條件查詢(xún)，只耗時(shí)0.02秒
? ? ? ? 2次的查詢(xún)結(jié)果不一致，這是因?yàn)閿?shù)據(jù)被物理刪除過(guò)有空洞.，因此我們可以追加軟刪除功能
分析:優(yōu)化思路是 不查,少查,查索引,少取.
我們現(xiàn)在必須要查,則只查索引,不查數(shù)據(jù),得到id.
再用id去查具體條目. ?這種技巧就是延遲索引.
? ? 分析：limit是先查詢(xún)?cè)僭竭^(guò)，也就是說(shuō)我們先查詢(xún)出所有數(shù)據(jù)再進(jìn)行跳躍，上圖我們?cè)竭^(guò)500W頁(yè)，還使用了inner?join? 內(nèi)存并沒(méi)有崩掉，這是因?yàn)槲覀冏泳鋞mp臨時(shí)表中只查詢(xún)了id（索引覆蓋，不需要回行去磁盤(pán)找數(shù)據(jù)了）然后拿到這10個(gè)id?分別查詢(xún)這10條數(shù)據(jù) 。
排序可能發(fā)生2種情況:
1:對(duì)于覆蓋索引,直接在索引上查詢(xún)時(shí),就是有順序的, using index
2:先取出數(shù)據(jù),形成臨時(shí)表做filesort(文件排序,但文件可能在磁盤(pán)上,也可能在內(nèi)存中)
我們的爭(zhēng)取目標(biāo)：取出來(lái)的數(shù)據(jù)本身就是有序的! 利用索引來(lái)排序,那么什么時(shí)候發(fā)生索引排序呢？即查詢(xún)索引和order by的字段是同一個(gè)字段
???goods表中 cat_id與shop_price組成聯(lián)合索引：
select goods_id,cat_id,shop_price from goods where cat_id=4 order by shop_price;????可以直接利用索引來(lái)排序,
using where按照shop_price索引取出的結(jié)果,本身就是有序的
? ? ?????select goods_id,cat_id,shop_price from goods order by click_count;
? ? ? ? ? using filesort用到了文件排序,即取出的結(jié)果再次排序
重復(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)為冗余索引. （mx, xm 不是重復(fù)的,因?yàn)榱械捻樞虿灰粯樱?/p>
索引合并檢索方法可以檢索多個(gè)范圍掃描并將結(jié)果合并。這種訪(fǎng)問(wèn)方法只能合并同一個(gè)表的索引掃描，不能合并跨表掃描。
合并可能生成基礎(chǔ)掃描結(jié)果的"并集"，"交集"，或者"交集的并集"
示例：
這種方法適用于 WHERE 子句中的條件是通過(guò) AND 結(jié)合的不同索引的范圍條件時(shí)，其中的每個(gè)條件都需要滿(mǎn)足下列條件之一：
示例：
索引合并交集算法在所有使用的索引上同時(shí)進(jìn)行掃描，并從掃描結(jié)果中生成行的交集
如果查詢(xún)中的所有列都被使用的索引覆蓋，不需要檢索所有表行（ EXPLAIN 輸出中的 Extra 列中包括 Using index ）。例如這個(gè)語(yǔ)句：
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
如果使用的索引沒(méi)有覆蓋查詢(xún)中所有的行，只有當(dāng)所有使用的索引的范圍條件滿(mǎn)足時(shí)才檢索整個(gè)行。
如果合并條件中包括 Innodb 表主鍵索引條件，主鍵并不用來(lái)檢索數(shù)據(jù)，而是用來(lái)篩選使用其他條件檢索出的行。 # 就是先通過(guò)其他的范圍條件篩選出一部分?jǐn)?shù)據(jù)，在從這部分?jǐn)?shù)據(jù)中，通過(guò)主鍵來(lái)篩選出最終的結(jié)果
這種方法適用于 WHERE 子句中的條件是通過(guò) OR 結(jié)合的不同索引的范圍條件時(shí)，其中的每個(gè)條件都需要滿(mǎn)足下列條件之一：
示例：
這種方法適用于 WHERE 子句中的條件是通過(guò) OR 結(jié)合的不同索引的范圍條件，但是不能使用 Index Merge Union 算法的情景
示例：
sort_union 和 union 算法的區(qū)別是， sort_union 必須在返回行數(shù)據(jù)前先獲取行ID并對(duì)行ID進(jìn)行排序。
在 optimizer_swith 中有4個(gè)關(guān)于 Index Merge 的變量：
index_merge,index_merge_intersection,index_merge_union,index_merge_sort_union
默認(rèn)情況下都是啟用的。要單獨(dú)啟用某個(gè)算法，設(shè)置 index_merge=off ，并將相應(yīng)的標(biāo)志設(shè)置為 on
索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的，也就是說(shuō)不同的存儲(chǔ)引擎，會(huì)使用不同的索引。MyISAM和InnoDB存儲(chǔ)引擎：只支持BTREE索引，也就是說(shuō)默認(rèn)使用BTREE，不能夠更換，MySQL5.7中InnoDB可以支持HASH索引；MEMORY/HEAP存儲(chǔ)引擎：支持HASH和BTREE索引。索引可劃分為單列索引（其中包括普通索引、唯一索引、主鍵索引）、組合索引、全文索引、空間索引，其中單列索引是一個(gè)索引只包含單個(gè)列，但一個(gè)表中可以有多個(gè)單列索引。
MySQL中基本索引類(lèi)型，沒(méi)有什么限制，允許在定義索引的列中插入重復(fù)值和空值，純粹為了查詢(xún)數(shù)據(jù)更快一點(diǎn)。
索引列中的值必須是唯一的，但是允許為空值，
是一種特殊的唯一索引，不允許有空值。
在表中的多個(gè)字段組合上創(chuàng)建的索引，只有在查詢(xún)條件中使用了這些字段的左邊字段時(shí)，索引才會(huì)被使用，使用組合索引時(shí)遵循最左前綴集合。
由id、name和age3個(gè)字段構(gòu)成的索引，索引行中就按id/name/age的順序存放，索引可以索引下面字段組合(id，name，age)、(id，name)或者(id)。如果要查詢(xún)的字段不構(gòu)成索引最左面的前綴，那么就不會(huì)是用索引，比如，age或者（name，age）組合就不會(huì)使用索引查詢(xún)
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類(lèi)型字段上使用全文索引。全文索引就是在一堆文字中，通過(guò)其中的某個(gè)關(guān)鍵字等，就能找到該字段所屬的記錄行，比如有"你是個(gè)大牛，神人 ..." 通過(guò)大牛，可能就可以找到該條記錄。這里說(shuō)的是可能，因?yàn)槿乃饕氖褂蒙婕傲撕芏嗉?xì)節(jié)，我們只需要知道這個(gè)大概意思。
只有在MyISAM引擎上才能使用，空間索引是對(duì)空間數(shù)據(jù)類(lèi)型的字段建立的索引，MySQL中的空間數(shù)據(jù)類(lèi)型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。
在創(chuàng)建空間索引時(shí)，使用SPATIAL關(guān)鍵字。
創(chuàng)建空間索引的列，必須將其聲明為NOT NULL。。
SPATIAL INDEX spatIdx(g)
全值匹配我最?lèi)?ài)，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計(jì)算，范圍之后全失效；
Like百分寫(xiě)最右，覆蓋索引不寫(xiě)星；
不等空值還有or，索引失效要少用；
VAR引號(hào)不可丟，SQL高級(jí)也不難！
參考： u;/u
參考： u;/u
如果索引包含滿(mǎn)足查詢(xún)的所有數(shù)據(jù)，就稱(chēng)為覆蓋索引。覆蓋索引是一種非常強(qiáng)大的工具，能大大提高查詢(xún)性能。只需要讀取索引而不用讀取數(shù)據(jù)有以下一些優(yōu)點(diǎn)：
(1) 索引項(xiàng)通常比記錄要小，所以MySQL訪(fǎng)問(wèn)更少的數(shù)據(jù)；
(2) 索引都按值的大小順序存儲(chǔ)，相對(duì)于隨機(jī)訪(fǎng)問(wèn)記錄，需要更少的I/O；
(3) 大多數(shù)據(jù)引擎能更好的緩存索引。比如MyISAM只緩存索引。
(4) 覆蓋索引對(duì)于InnoDB表尤其有用，因?yàn)镮nnoDB使用聚集索引組織數(shù)據(jù)，如果二級(jí)索引中包含查詢(xún)所需的數(shù)據(jù)，就不再需要在聚集索引中查找了。
覆蓋索引不能是任何索引，只有B-TREE索引存儲(chǔ)相應(yīng)的值。而且不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式都不同，并不是所有存儲(chǔ)引擎都支持覆蓋索引(Memory和Falcon就不支持)。
對(duì)于索引覆蓋查詢(xún)(index-covered query)，使用EXPLAIN時(shí)，可以在Extra一列中看到“Using index”。
產(chǎn)品中有一張圖片表，數(shù)據(jù)量將近100萬(wàn)條，有一條相關(guān)的查詢(xún)語(yǔ)句，由于執(zhí)行頻次較高，想針對(duì)此語(yǔ)句進(jìn)行優(yōu)化。表結(jié)構(gòu)很簡(jiǎn)單，主要字段：
user_id 用戶(hù)ID
picname 圖片名稱(chēng)
smallimg 小圖名稱(chēng)
一個(gè)用戶(hù)會(huì)有多條圖片記錄，現(xiàn)在有一個(gè)根據(jù)user_id建立的索引：uid，查詢(xún)語(yǔ)句也很簡(jiǎn)單。取得某用戶(hù)的圖片集合
執(zhí)行查詢(xún)語(yǔ)句（為了查看真實(shí)執(zhí)行時(shí)間，強(qiáng)制不使用緩存）
執(zhí)行了10次，平均耗時(shí)在40ms左右。使用explain進(jìn)行分析
使用了user_id的索引，并且是const常數(shù)查找，表示性能已經(jīng)很好了
因?yàn)檫@個(gè)語(yǔ)句太簡(jiǎn)單，sql本身沒(méi)有什么優(yōu)化空間，就考慮了索引。修改索引結(jié)構(gòu)，建立一個(gè)(user_id,picname,smallimg)的聯(lián)合索引：uid_pic。重新執(zhí)行10次，平均耗時(shí)降到了30ms左右。使用explain進(jìn)行分析
看到使用的索引變成了剛剛建立的聯(lián)合索引，并且Extra部分顯示使用了'Using Index'
'Using Index'的意思是“覆蓋索引”，它是使上面sql性能提升的關(guān)鍵。一個(gè)包含查詢(xún)所需字段的索引稱(chēng)為“覆蓋索引”，MySQL只需要通過(guò)索引就可以返回查詢(xún)所需要的數(shù)據(jù)，而不必在查到索引之后進(jìn)行回表操作，減少I(mǎi)O，提高了效率。
例如上面的sql，查詢(xún)條件是user_id，可以使用聯(lián)合索引，要查詢(xún)的字段是picname smallimg，這兩個(gè)字段也在聯(lián)合索引中，這就實(shí)現(xiàn)了“覆蓋索引”，可以根據(jù)這個(gè)聯(lián)合索引一次性完成查詢(xún)工作，所以提升了性能
InnoDB存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面帶來(lái)的性能損耗可能比表級(jí)鎖定要更高一些，但是在整體并發(fā)處理能力方面是要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表級(jí)鎖定的。當(dāng)系統(tǒng)并發(fā)量較高的時(shí)候，InnoDB的整體性能和MyISAM相比就會(huì)有比較明顯的優(yōu)勢(shì)了。但是當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會(huì)讓InnoDB的整體性能表現(xiàn)不僅不比MyISAM高，甚至可能會(huì)更差。
建議：
（1）盡可能讓所有的數(shù)據(jù)檢索都通過(guò)索引來(lái)完成，從而避免InnoDB因?yàn)闊o(wú)法通過(guò)索引鍵加鎖而升級(jí)為表級(jí)鎖定
（2）合理設(shè)計(jì)索引，讓InnoDB在索引鍵上面加鎖的時(shí)候盡可能準(zhǔn)確，盡可能地縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query的執(zhí)行
（3）盡可能減少基于范圍的數(shù)據(jù)檢索過(guò)濾條件，避免因?yàn)殚g隙鎖帶來(lái)的負(fù)面影響而鎖定了不該鎖定的記錄
（4）盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時(shí)間長(zhǎng)度
（5）在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級(jí)別的事務(wù)隔離，以減少M(fèi)ySQL因?yàn)閷?shí)現(xiàn)事務(wù)隔離級(jí)別所帶來(lái)的附加成本。
索引就是為特定的mysql字段進(jìn)行一些特定的算法排序，比如二叉樹(shù)的算法和哈希算法，哈希算法是通過(guò)建立特征值，然后根據(jù)特征值來(lái)快速查找。
1.普通索引:(index)最基本的索引，沒(méi)有任何限制? 目的:加快數(shù)據(jù)的查詢(xún)速度
2.唯一索引:(unique)? 與"普通索引"類(lèi)似，不同的就是：索引列的值必須唯一，但允許有空值。
3.主鍵索引(primary key) 它 是一種特殊的唯一索引，不允許有空值。
4.復(fù)合索引:index(a,b,c)? 為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。
5.全文索引:fulltext? 僅可用于 MyISAM 表，針對(duì)較大的數(shù)據(jù)，生成全文索引很耗時(shí)耗空間。
第一類(lèi)是myisam存儲(chǔ)引擎使用的叫做b-tree結(jié)構(gòu)，
第二類(lèi)是innodb存儲(chǔ)引擎使用的叫做聚簇結(jié)構(gòu)（也是一種 b-tree）。 如下圖：
注意：
1.myisam不需要回行處理?
2.innodb不需要回行處理,直接可以獲取數(shù)據(jù),因?yàn)閕nnodb的儲(chǔ)存引擎是包含了數(shù)據(jù)和索引文件的,其主鍵索引包含了數(shù)據(jù),(唯一索引及普通索是沒(méi)有直接包含數(shù)據(jù)的)
1、索引列不能參與計(jì)算
有索引列參與計(jì)算的查詢(xún)條件對(duì)索引不友好（甚至無(wú)法使用索引），如from_unixtime(create_time) = '2014-05-29'。
原因很簡(jiǎn)單，如何在節(jié)點(diǎn)中查找到對(duì)應(yīng)key？如果線(xiàn)性?huà)呙?，則每次都需要重新計(jì)算，成本太高；如果二分查找，則需要針對(duì)from_unixtime方法確定大小關(guān)系。
因此，索引列不能參與計(jì)算。上述from_unixtime(create_time) = '2014-05-29'語(yǔ)句應(yīng)該寫(xiě)成create_time = unix_timestamp('2014-05-29')。
2、最左前綴匹配
如有索引(a, b, c, d)，查詢(xún)條件a = 1 and b = 2 and c 3 and d = 4，則會(huì)在每個(gè)節(jié)點(diǎn)依次命中a、b、c，無(wú)法命中d。也就是最左前綴匹配原則。
3、冗余和重復(fù)索引
冗余索引是指在相同的列上按照相同的順序創(chuàng)建的相同類(lèi)型的索引，應(yīng)當(dāng)盡量避免這種索引，發(fā)現(xiàn)后立即刪除。比如有一個(gè)索引(A,B)，再創(chuàng)建索引(A)就是冗余索引。冗余索引經(jīng)常發(fā)生在為表添加新索引時(shí)，比如有人新建了索引(A,B)，但這個(gè)索引不是擴(kuò)展已有的索引(A)
4、避免多個(gè)范圍條件
? ? select user.* from user where login_time '2017-04-01' and age between 18 and 30;
比如想查詢(xún)某個(gè)時(shí)間段內(nèi)登錄過(guò)的用戶(hù)：它有兩個(gè)范圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無(wú)法同時(shí)使用它們 .
5、覆蓋索引 (能擴(kuò)展就不新建)
如果一個(gè)索引包含或者說(shuō)覆蓋所有需要查詢(xún)的字段的值，那么就沒(méi)有必要再回表查詢(xún)，這就稱(chēng)為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因?yàn)椴樵?xún)只需要掃描索引會(huì)帶來(lái)許多好處：
1.索引條目遠(yuǎn)小于數(shù)據(jù)行大小，如果只讀取索引，極大減少數(shù)據(jù)訪(fǎng)問(wèn)量2.索引是有按照列值順序存儲(chǔ)的，對(duì)于I/O密集型的范圍查詢(xún)要比隨機(jī)從磁盤(pán)讀取每一行數(shù)據(jù)的IO要少的多
6、選擇區(qū)分度高的列作索引
如，用性別作索引，那么索引僅能將1000w行數(shù)據(jù)劃分為兩部分（如500w男，500w女），索引幾乎無(wú)效。
區(qū)分度的公式是count(distinct ) / count(*)，表示字段不重復(fù)的比例，比例越大區(qū)分度越好。唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前的區(qū)分度趨近于0。
7、刪除長(zhǎng)期未使用的索引
場(chǎng)景一(覆蓋索引 5)
索引應(yīng)該建在選擇性高的字段上（鍵值唯一的記錄數(shù)/總記錄條數(shù)），選擇性越高索引的效果越好、價(jià)值越大，唯一索引的選擇性最高；
組合索引中字段的順序，選擇性越高的字段排在最前面；
where條件中包含兩個(gè)選擇性高的字段時(shí)，可以考慮分別創(chuàng)建索引，引擎會(huì)同時(shí)使用兩個(gè)索引（在OR條件下，應(yīng)該說(shuō)必須分開(kāi)建索引）；
不要重復(fù)創(chuàng)建彼此有包含關(guān)系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；
組合索引的字段不要過(guò)多，如果超過(guò)4個(gè)字段，一般需要考慮拆分成多個(gè)單列索引或更為簡(jiǎn)單的組合索引；
不要濫用索引。因?yàn)檫^(guò)多的索引不僅僅會(huì)增加物理存儲(chǔ)的開(kāi)銷(xiāo)，對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開(kāi)銷(xiāo)，而且會(huì)增加優(yōu)化器在選擇索引時(shí)的計(jì)算代價(jià)。
因此太多的索引與不充分、不正確的索引對(duì)性能都是毫無(wú)益處的。一言以蔽之，索引的建立必須慎重，對(duì)每個(gè)索引的必要性都應(yīng)該經(jīng)過(guò)仔細(xì)分析，要有建立的依據(jù)。
上一篇給小伙伴們講了關(guān)于SQL查詢(xún)性能優(yōu)化的相關(guān)技巧，一個(gè)好的查詢(xún)SQL離不開(kāi)合理的索引設(shè)計(jì)。這篇小二就來(lái)嘮一嘮怎么合理的設(shè)計(jì)一個(gè)索引來(lái)優(yōu)化我們的查詢(xún)速度，要是有不合理的地方...嗯..
當(dāng)然啦，開(kāi)個(gè)玩笑，歡迎小伙伴們指正！
通常情況下，字段類(lèi)型的選擇是需要根據(jù)業(yè)務(wù)來(lái)判斷的，通常需要遵循以下幾點(diǎn)。
下列各種類(lèi)型表格內(nèi)容來(lái)自菜鳥(niǎo)教程，權(quán)當(dāng)備忘。
優(yōu)化建議：
注意： INT(2)設(shè)置的為顯示寬度，而不是整數(shù)的長(zhǎng)度，需要配合 ZEROFILL 使用 。
例如 id 設(shè)置為 TINYINT(2) UNSIGNED ，表示無(wú)符號(hào)，可以存儲(chǔ)的最大數(shù)值為255，其中 TINYINT(2) 沒(méi)有配合 ZEROFILL 實(shí)際沒(méi)有任何意義，例如插入數(shù)字200，長(zhǎng)度雖然超過(guò)了兩位，但是這個(gè)時(shí)候是可以插入成功的，查詢(xún)結(jié)果同樣為200；插入數(shù)字5時(shí)，同樣查詢(xún)結(jié)果為5。
而 TINYINT(2) 配合 ZEROFILL 后，當(dāng)插入數(shù)字5時(shí)，實(shí)際存儲(chǔ)的還是5，不過(guò)在查詢(xún)是MySQL會(huì)在前面補(bǔ)上一個(gè)0，即查詢(xún)出來(lái)的實(shí)際為 05 。
優(yōu)化建議：
優(yōu)化建議：
通常來(lái)說(shuō)，考慮好表中每個(gè)字段應(yīng)該使用什么類(lèi)型和長(zhǎng)度，建完表需要做的事情不是馬上建立索引，而是先把相關(guān)主體業(yè)務(wù)開(kāi)發(fā)完畢，然后把涉及該表的SQL都拿出來(lái)分析之后再建立索引。
盡量少建立單值索引( 唯一索引除外 )，應(yīng)當(dāng)設(shè)計(jì)一個(gè)或者兩三個(gè)聯(lián)合索引，讓每一個(gè)聯(lián)合索引都盡量去包含SQL語(yǔ)句中的 where、order by、group by 的字段，同時(shí)確保聯(lián)合索引的字段順序盡量滿(mǎn)足SQL查詢(xún)的最左前綴原則。
索引基數(shù)是指這個(gè)字段在表里總共有多少個(gè)不同的值，比如一張表總共100萬(wàn)行記錄，其中有個(gè)性別字段，性別一共有三個(gè)值：男、女、保密，那么該字段的基數(shù)就是3。
如果對(duì)這種小基數(shù)字段建立索引的話(huà)，因?yàn)樗饕龢?shù)中只有男、女、保密三個(gè)值，根本沒(méi)法進(jìn)行快速的二分查找，同時(shí)還需要回表查詢(xún)，還不如全表掃描嘞。
一般建立索引，盡量使用那些基數(shù)比較大的字段，那么才能發(fā)揮出B+樹(shù)快速二分查找的優(yōu)勢(shì)來(lái)。
在 where 和 order by 出現(xiàn)索引設(shè)計(jì)沖突時(shí)，是優(yōu)先針對(duì)where去設(shè)計(jì)索引？還是優(yōu)先針對(duì)order by設(shè)計(jì)索引？
通常情況下都是優(yōu)先針對(duì) where 來(lái)設(shè)計(jì)索引，因?yàn)橥ǔＧ闆r下都是先 where 條件使用索引快速篩選出來(lái)符合條件的數(shù)據(jù)，然后對(duì)進(jìn)行篩選出來(lái)的數(shù)據(jù)進(jìn)行排序和分組，而 where 條件快速篩選出來(lái)的的數(shù)據(jù)往往不會(huì)很多。
對(duì)生產(chǎn)實(shí)際運(yùn)行過(guò)程中，或者測(cè)試環(huán)境大數(shù)據(jù)量測(cè)試過(guò)程中發(fā)現(xiàn)的慢查詢(xún)SQL進(jìn)行特定的索引優(yōu)化、代碼優(yōu)化等策略。
終于輪到實(shí)戰(zhàn)了，小二最喜歡實(shí)戰(zhàn)了。
寫(xiě)到這里不得不吐槽一下，這個(gè)金三銀四的跳槽季節(jié)，年前提離職了，結(jié)果離職還沒(méi)辦完就封村整整兩個(gè)禮拜了，嗚嗚嗚...
上節(jié)小二就提到會(huì)有個(gè)很有意思的小案例，那么在疫情當(dāng)下，門(mén)都出不去的日子，感覺(jué)這個(gè)例子更有意思了，咱們來(lái)討論一下各種社交平臺(tái)怎么做的用戶(hù)信息搜索呢。
社交平臺(tái)有一個(gè)小伙伴們都喜歡的功能，搜索好友信息，比如小二熟練的點(diǎn)開(kāi)省份...城市..性別..年齡..身高...
咳咳咳...小二怎么可能干這種事情，小二的心里只有代碼，嗯...沒(méi)錯(cuò)，就是這樣。
這個(gè)就可以說(shuō)是對(duì)于用戶(hù)信息的查詢(xún)篩選了，通常這種表都是非常大數(shù)據(jù)量的，在不考慮分庫(kù)分表的情況下，怎么通過(guò)索引配合SQL來(lái)優(yōu)化呢？
通常我們?cè)诰帉?xiě)SQL是會(huì)寫(xiě)出類(lèi)似如下的SQL來(lái)執(zhí)行，有 where、order by、limit 等條件來(lái)查詢(xún)。
那么接下來(lái)小二一個(gè)一個(gè)慢慢增加字段來(lái)分析分析，怎么根據(jù)業(yè)務(wù)場(chǎng)景來(lái)設(shè)計(jì)索引。
針對(duì)這種情況，很簡(jiǎn)單，設(shè)計(jì)一個(gè)聯(lián)合索引 (provice, city, sex) 就完事了。
那么這時(shí)候有小伙伴就會(huì)說(shuō)了，很簡(jiǎn)單啊，范圍字段放最后咱還是知道的，聯(lián)合索引改成 (provice, city, sex, age) 不就可以了。
嗯，是的，這么干沒(méi)毛病，但是小伙伴們有沒(méi)有想過(guò)有些人萬(wàn)一既喜歡帥哥又喜歡美女，別想歪了哈...，挺多小姐姐就既喜歡帥哥又喜歡美女的。
那么這個(gè)時(shí)候小姐姐就不搜索性別了，那么這個(gè)時(shí)候聯(lián)合索引只能用到前兩個(gè)字段了，那么不符合咱們的專(zhuān)業(yè)標(biāo)準(zhǔn)啊，咋辦呢？這時(shí)候還是有辦法的，咱們只需要?jiǎng)觿?dòng)小腦袋改改SQL就行了，在沒(méi)有選擇性別時(shí)判斷一下，改成下面這樣就可以了。
咋辦嘞，同樣往聯(lián)合索引里面塞，例如 (provice, city, sex, hobby, xx, age) 。
針對(duì)這種多個(gè)范圍查詢(xún)的話(huà)，為了比較好的利用索引，在業(yè)務(wù)允許的情況下可以使用固定范圍，然后數(shù)據(jù)庫(kù)字段存儲(chǔ)范圍標(biāo)識(shí)就可以了，這樣就轉(zhuǎn)化為了等值匹配，就可以很好地利用索引了。
例如最后登錄時(shí)間字段不記錄最后登錄時(shí)間，而是記錄設(shè)置字段 is_login_within_seven_days 在7天內(nèi)有登錄則為1，否則為0，最后索引設(shè)計(jì)成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。
那么根據(jù)場(chǎng)景最后設(shè)計(jì)出來(lái)的這個(gè)索引可能已經(jīng)可以覆蓋大部分的查詢(xún)流量了，那么如果還有其他一部分熱度比較高的查詢(xún)?cè)趺崔k呢，辦法也很簡(jiǎn)單啊，再加一兩個(gè)索引即可。
例如通常會(huì)查詢(xún)這個(gè)城市比較受歡迎(評(píng)分：score)的小姐姐，這時(shí)候添加一個(gè)聯(lián)合索引 (provice, city, sex, score) 那么就可以了。
可以看出，索引時(shí)必須結(jié)合場(chǎng)景來(lái)設(shè)計(jì)的，思路就是盡量用不超過(guò)3個(gè)復(fù)雜的聯(lián)合索引來(lái)抗住大部分的80%以上的常用查詢(xún)流量，然后再用一兩個(gè)二級(jí)索引來(lái)抗下一些非常用查詢(xún)流量。
以上就是小二要給大家分享的索引設(shè)計(jì)，如果能動(dòng)動(dòng)你發(fā)財(cái)?shù)男∈纸o小二點(diǎn)個(gè)免費(fèi)的贊就更好啦~
下篇小二就來(lái)講講MySQL事務(wù)和鎖機(jī)制。
重慶分公司
重慶分公司
