建站
咨詢
售后
建站咨詢
新聞中心
Mysql建立索引經(jīng)驗(yàn)
在實(shí)際開(kāi)發(fā)中使用數(shù)據(jù)庫(kù)時(shí)，難免會(huì)遇到一些大表數(shù)據(jù)，對(duì)這些數(shù)據(jù)進(jìn)行查詢時(shí)，有時(shí)候SQL會(huì)查詢得特別慢，這時(shí)候，有經(jīng)驗(yàn)的老師傅會(huì)告訴你，你看一下哪幾個(gè)字段查的多，加一個(gè)索引就好了。
成都創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站與策劃設(shè)計(jì),蘇尼特右網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:蘇尼特右等地區(qū)。蘇尼特右做網(wǎng)站價(jià)格咨詢:18982081108
那么，怎么合理地建立索引呢？這里分享一下我的一些經(jīng)驗(yàn)，如有不妥之處，歡迎批評(píng)指正。
1、不要盲目建立索引 ， 先分析再創(chuàng)建
索引雖然能大幅度提升我們的查詢性能，但也要知道，在你進(jìn)行增刪改時(shí)，索引樹(shù)也要同樣地進(jìn)行維護(hù)。所以，索引不是越多越好，而是按需建立。最好是在一整塊模塊開(kāi)發(fā)完成后，分析一下，去針對(duì)大多數(shù)的查詢，建立聯(lián)合索引。
2、使用聯(lián)合索引盡量覆蓋多的條件
這是說(shuō)在一個(gè)慢sql里假如有五個(gè)where ，一個(gè) order by ，那么我們的聯(lián)合索引盡量覆蓋到這五個(gè)查詢條件，如果有必要，order by 也覆蓋上 。
3、小基數(shù)字段不需要索引
這個(gè)意思是，如果一張表里某個(gè)字段的值只有那么幾個(gè)，那么你針對(duì)這個(gè)字段建立的索引其實(shí)沒(méi)什么意義，比如說(shuō)，一個(gè)性別字段就兩種結(jié)果，你建了索引，排序也沒(méi)什么意思（也就是索引里把男女給分開(kāi)了）
所以說(shuō)，索引盡量選擇基數(shù)大的數(shù)據(jù)去建立，能最大化地利用索引
4、長(zhǎng)字符串可以使用前綴索引
我們建立索引的字段盡量選擇字段類型較小的，比如一個(gè)varchar(20)和varchar(256)的，我們?cè)?0的上面建立的索引和在256上就有明顯的差距（字符串那么長(zhǎng)排序也不好排呀，唉）。
當(dāng)然，如果一定是要對(duì)varchar(256)建立索引,我們可以選擇里面的前20個(gè)字符放在索引樹(shù)里（這里的20不絕對(duì)，選擇能盡量分辨數(shù)據(jù)的最小字符字段設(shè)計(jì)），類似這樣KEY index(name(20),age,job) ，索引只會(huì)對(duì)name的前20個(gè)字符進(jìn)行搜索，但前綴索引無(wú)法適用于order by 和 group by。
5、對(duì)排序字段設(shè)計(jì)索引的優(yōu)先級(jí)低
如果一個(gè)SQL里我們出現(xiàn)了范圍查找，后邊又跟著一個(gè)排序字段，那么我們優(yōu)先給范圍查找的字段設(shè)置索引，而不是優(yōu)先排序。
6、如果出現(xiàn)慢SQL，可以設(shè)計(jì)一個(gè)只針對(duì)該條SQL的聯(lián)合索引。
不過(guò)慢SQL的優(yōu)化，需要一步步去進(jìn)行分析，可以先用explain查看SQL語(yǔ)句的分析結(jié)果，再針對(duì)結(jié)果去做相應(yīng)的改進(jìn)。explain的東西我們下次再講。
PS：在 select 語(yǔ)句之前增加 explain 關(guān)鍵字，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，執(zhí)行查詢會(huì)返回執(zhí)行計(jì)劃的信息，而不是 執(zhí)行這條SQL。
mysql如何建立索引
我們可以通過(guò)查看索引的屬性來(lái)判斷創(chuàng)建索引的方法。
查看索引的語(yǔ)法格式如下：
SHOW INDEX FROM 表名 [ FROM 數(shù)據(jù)庫(kù)名]
語(yǔ)法說(shuō)明如下：
表名：指定需要查看索引的數(shù)據(jù)表名。
數(shù)據(jù)庫(kù)名：指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫(kù)，可省略。比如，SHOW INDEX FROM student FROM test; 語(yǔ)句表示查看 test 數(shù)據(jù)庫(kù)中 student 數(shù)據(jù)表的索引。
示例
使用 SHOW INDEX 語(yǔ)句查看《MySQL創(chuàng)建索引》一節(jié)中 tb_stu_info2 數(shù)據(jù)表的索引信息，SQL 語(yǔ)句和運(yùn)行結(jié)果如下所示。
mysql SHOW INDEX FROM tb_stu_info2\G
1. row
Table: tb_stu_info2
Non_unique: 0
Key_name: height
Seq_in_index: 1
Column_name: height
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.03 sec)
其中各主要參數(shù)說(shuō)明如下：
參數(shù) 說(shuō)明
Table 表示創(chuàng)建索引的數(shù)據(jù)表名，這里是 tb_stu_info2 數(shù)據(jù)表。
Non_unique 表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。
Key_name 表示索引的名稱。
Seq_in_index 表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。
Column_name 表示定義索引的列字段。
Collation 表示列以何種順序存儲(chǔ)在索引中。在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無(wú)分類。
Cardinality 索引中唯一值數(shù)目的估計(jì)值?；鶖?shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒(méi)有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL 使用該索引的機(jī)會(huì)就越大。
Sub_part 表示列中被編入索引的字符的數(shù)量。若列只是部分被編入索引，則該列的值為被編入索引的字符的數(shù)目；若整列被編入索引，則該列的值為 NULL。
Packed 指示關(guān)鍵字如何被壓縮。若沒(méi)有被壓縮，值為 NULL。
Null 用于顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒(méi)有，則該列的值為 NO。
Index_type 顯示索引使用的類型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment 顯示評(píng)注。
MySQL性能優(yōu)化之索引設(shè)計(jì)
上一篇給小伙伴們講了關(guān)于SQL查詢性能優(yōu)化的相關(guān)技巧，一個(gè)好的查詢SQL離不開(kāi)合理的索引設(shè)計(jì)。這篇小二就來(lái)嘮一嘮怎么合理的設(shè)計(jì)一個(gè)索引來(lái)優(yōu)化我們的查詢速度，要是有不合理的地方...嗯..
當(dāng)然啦，開(kāi)個(gè)玩笑，歡迎小伙伴們指正！
通常情況下，字段類型的選擇是需要根據(jù)業(yè)務(wù)來(lái)判斷的，通常需要遵循以下幾點(diǎn)。
下列各種類型表格內(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í)候是可以插入成功的，查詢結(jié)果同樣為200；插入數(shù)字5時(shí)，同樣查詢結(jié)果為5。
而 TINYINT(2) 配合 ZEROFILL 后，當(dāng)插入數(shù)字5時(shí)，實(shí)際存儲(chǔ)的還是5，不過(guò)在查詢是MySQL會(huì)在前面補(bǔ)上一個(gè)0，即查詢出來(lái)的實(shí)際為 05 。
優(yōu)化建議：
優(yōu)化建議：
通常來(lái)說(shuō)，考慮好表中每個(gè)字段應(yīng)該使用什么類型和長(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)合索引的字段順序盡量滿足SQL查詢的最左前綴原則。
索引基數(shù)是指這個(gè)字段在表里總共有多少個(gè)不同的值，比如一張表總共100萬(wàn)行記錄，其中有個(gè)性別字段，性別一共有三個(gè)值：男、女、保密，那么該字段的基數(shù)就是3。
如果對(duì)這種小基數(shù)字段建立索引的話，因?yàn)樗饕龢?shù)中只有男、女、保密三個(gè)值，根本沒(méi)法進(jìn)行快速的二分查找，同時(shí)還需要回表查詢，還不如全表掃描嘞。
一般建立索引，盡量使用那些基數(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)的慢查詢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)下，門都出不去的日子，感覺(jué)這個(gè)例子更有意思了，咱們來(lái)討論一下各種社交平臺(tái)怎么做的用戶信息搜索呢。
社交平臺(tái)有一個(gè)小伙伴們都喜歡的功能，搜索好友信息，比如小二熟練的點(diǎn)開(kāi)省份...城市..性別..年齡..身高...
咳咳咳...小二怎么可能干這種事情，小二的心里只有代碼，嗯...沒(méi)錯(cuò)，就是這樣。
這個(gè)就可以說(shuō)是對(duì)于用戶信息的查詢篩選了，通常這種表都是非常大數(shù)據(jù)量的，在不考慮分庫(kù)分表的情況下，怎么通過(guò)索引配合SQL來(lái)優(yōu)化呢？
通常我們?cè)诰帉?xiě)SQL是會(huì)寫(xiě)出類似如下的SQL來(lái)執(zhí)行，有 where、order by、limit 等條件來(lái)查詢。
那么接下來(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è)字段了，那么不符合咱們的專業(yè)標(biāo)準(zhǔn)啊，咋辦呢？這時(shí)候還是有辦法的，咱們只需要?jiǎng)觿?dòng)小腦袋改改SQL就行了，在沒(méi)有選擇性別時(shí)判斷一下，改成下面這樣就可以了。
咋辦嘞，同樣往聯(lián)合索引里面塞，例如 (provice, city, sex, hobby, xx, age) 。
針對(duì)這種多個(gè)范圍查詢的話，為了比較好的利用索引，在業(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)可以覆蓋大部分的查詢流量了，那么如果還有其他一部分熱度比較高的查詢?cè)趺崔k呢，辦法也很簡(jiǎn)單啊，再加一兩個(gè)索引即可。
例如通常會(huì)查詢這個(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%以上的常用查詢流量，然后再用一兩個(gè)二級(jí)索引來(lái)抗下一些非常用查詢流量。
以上就是小二要給大家分享的索引設(shè)計(jì)，如果能動(dòng)動(dòng)你發(fā)財(cái)?shù)男∈纸o小二點(diǎn)個(gè)免費(fèi)的贊就更好啦~
下篇小二就來(lái)講講MySQL事務(wù)和鎖機(jī)制。
文章標(biāo)題：mysql怎么設(shè)計(jì)索引 mysql設(shè)計(jì)索引的原則
鏈接分享：http://fisionsoft.com.cn/article/doceops.html
