MYSQL只是針對索引的優(yōu)化有3種：
成都創(chuàng)新互聯(lián)主營阜南網(wǎng)站建設的網(wǎng)絡公司,主營網(wǎng)站建設方案,App定制開發(fā),阜南h5微信小程序開發(fā)搭建,阜南網(wǎng)站營銷推廣歡迎阜南等地區(qū)企業(yè)咨詢
改成唯一。這樣查詢速度比普通索引要快，不過也得看你那個字段是否唯一了。
根據(jù)查詢條件建多個欄位的索引，這樣比單一索引更快
針對文字建全文索引
在mysql中，索引是一種特殊的數(shù)據(jù)庫結構，由數(shù)據(jù)表中的一列或多列組合而成，可以用來快速查詢數(shù)據(jù)表中有某一特定值的記錄。
通過索引，查詢數(shù)據(jù)時不用讀完記錄的所有信息，而只是查詢索引列即可。
通過索引，查詢數(shù)據(jù)時不用讀完記錄的所有信息，而只是查詢索引列。否則，數(shù)據(jù)庫系統(tǒng)將讀取每條記錄的所有信息進行匹配。
可以把索引比作新華字典的音序表。例如，要查“庫”字，如果不使用音序，就需要從字典的 400 頁中逐頁來找。但是，如果提取拼音出來，構成音序表，就只需要從 10 多頁的音序表中直接查找。這樣就可以大大節(jié)省時間。
因此，使用索引可以很大程度上提高數(shù)據(jù)庫的查詢速度，還有效的提高了數(shù)據(jù)庫系統(tǒng)的性能。
索引的優(yōu)缺點
索引有其明顯的優(yōu)勢，也有其不可避免的缺點。
優(yōu)點
索引的優(yōu)點如下：
1、通過創(chuàng)建唯一索引可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。
2、可以給所有的 MySQL 列類型設置索引。
3、可以大大加快數(shù)據(jù)的查詢速度，這是使用索引最主要的原因。
4、在實現(xiàn)數(shù)據(jù)的參考完整性方面可以加速表與表之間的連接。
5、在使用分組和排序子句進行數(shù)據(jù)查詢時也可以顯著減少查詢中分組和排序的時間
缺點
增加索引也有許多不利的方面，主要如下：
1、創(chuàng)建和維護索引組要耗費時間，并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加。
2、索引需要占磁盤空間，除了數(shù)據(jù)表占數(shù)據(jù)空間以外，每一個索引還要占一定的物理空間。如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達到最大文件尺寸。
3、當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)維護，這樣就降低了數(shù)據(jù)的維護速度。
使用索引時，需要綜合考慮索引的優(yōu)點和缺點。
索引就是為特定的mysql字段進行一些特定的算法排序，比如二叉樹的算法和哈希算法，哈希算法是通過建立特征值，然后根據(jù)特征值來快速查找。
1.普通索引:(index)最基本的索引，沒有任何限制? 目的:加快數(shù)據(jù)的查詢速度
2.唯一索引:(unique)? 與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。
3.主鍵索引(primary key) 它 是一種特殊的唯一索引，不允許有空值。
4.復合索引:index(a,b,c)? 為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。
5.全文索引:fulltext? 僅可用于 MyISAM 表，針對較大的數(shù)據(jù)，生成全文索引很耗時耗空間。
第一類是myisam存儲引擎使用的叫做b-tree結構，
第二類是innodb存儲引擎使用的叫做聚簇結構（也是一種 b-tree）。 如下圖：
注意：
1.myisam不需要回行處理?
2.innodb不需要回行處理,直接可以獲取數(shù)據(jù),因為innodb的儲存引擎是包含了數(shù)據(jù)和索引文件的,其主鍵索引包含了數(shù)據(jù),(唯一索引及普通索是沒有直接包含數(shù)據(jù)的)
1、索引列不能參與計算
有索引列參與計算的查詢條件對索引不友好（甚至無法使用索引），如from_unixtime(create_time) = '2014-05-29'。
原因很簡單，如何在節(jié)點中查找到對應key？如果線性掃描，則每次都需要重新計算，成本太高；如果二分查找，則需要針對from_unixtime方法確定大小關系。
因此，索引列不能參與計算。上述from_unixtime(create_time) = '2014-05-29'語句應該寫成create_time = unix_timestamp('2014-05-29')。
2、最左前綴匹配
如有索引(a, b, c, d)，查詢條件a = 1 and b = 2 and c 3 and d = 4，則會在每個節(jié)點依次命中a、b、c，無法命中d。也就是最左前綴匹配原則。
3、冗余和重復索引
冗余索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引，應當盡量避免這種索引，發(fā)現(xiàn)后立即刪除。比如有一個索引(A,B)，再創(chuàng)建索引(A)就是冗余索引。冗余索引經(jīng)常發(fā)生在為表添加新索引時，比如有人新建了索引(A,B)，但這個索引不是擴展已有的索引(A)
4、避免多個范圍條件
? ? select user.* from user where login_time '2017-04-01' and age between 18 and 30;
比如想查詢某個時間段內(nèi)登錄過的用戶：它有兩個范圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無法同時使用它們 .
5、覆蓋索引 (能擴展就不新建)
如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：
1.索引條目遠小于數(shù)據(jù)行大小，如果只讀取索引，極大減少數(shù)據(jù)訪問量2.索引是有按照列值順序存儲的，對于I/O密集型的范圍查詢要比隨機從磁盤讀取每一行數(shù)據(jù)的IO要少的多
6、選擇區(qū)分度高的列作索引
如，用性別作索引，那么索引僅能將1000w行數(shù)據(jù)劃分為兩部分（如500w男，500w女），索引幾乎無效。
區(qū)分度的公式是count(distinct ) / count(*)，表示字段不重復的比例，比例越大區(qū)分度越好。唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前的區(qū)分度趨近于0。
7、刪除長期未使用的索引
場景一(覆蓋索引 5)
索引應該建在選擇性高的字段上（鍵值唯一的記錄數(shù)/總記錄條數(shù)），選擇性越高索引的效果越好、價值越大，唯一索引的選擇性最高；
組合索引中字段的順序，選擇性越高的字段排在最前面；
where條件中包含兩個選擇性高的字段時，可以考慮分別創(chuàng)建索引，引擎會同時使用兩個索引（在OR條件下，應該說必須分開建索引）；
不要重復創(chuàng)建彼此有包含關系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；
組合索引的字段不要過多，如果超過4個字段，一般需要考慮拆分成多個單列索引或更為簡單的組合索引；
不要濫用索引。因為過多的索引不僅僅會增加物理存儲的開銷，對于插入、刪除、更新操作也會增加處理上的開銷，而且會增加優(yōu)化器在選擇索引時的計算代價。
因此太多的索引與不充分、不正確的索引對性能都是毫無益處的。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應該經(jīng)過仔細分析，要有建立的依據(jù)。
1. 執(zhí)行計劃中明明有使用到索引，為什么執(zhí)行還是這么慢？
2. 執(zhí)行計劃中顯示掃描行數(shù)為 644，為什么 slow log 中顯示 100 多萬行？
a. 我們先看執(zhí)行計劃，選擇的索引 “INDX_BIOM_ELOCK_TASK3(TASK_ID)”。結合 sql 來看，因為有 "ORDER BY TASK_ID DESC" 子句，排序通常很慢，如果使用了文件排序性能會更差，優(yōu)化器選擇這個索引避免了排序。
那為什么不選 possible_keys:INDX_BIOM_ELOCK_TASK 呢？原因也很簡單，TASK_DATE 字段區(qū)分度太低了，走這個索引需要掃描的行數(shù)很大，而且還要進行額外的排序，優(yōu)化器綜合判斷代價更大，所以就不選這個索引了。不過如果我們強制選擇這個索引（用 force index 語法），會看到 SQL 執(zhí)行速度更快少于 10s，那是因為優(yōu)化器基于代價的原則并不等價于執(zhí)行速度的快慢；
b. 再看執(zhí)行計劃中的 type:index，"index" 代表 “全索引掃描”，其實和全表掃描差不多，只是掃描的時候是按照索引次序進行而不是行，主要優(yōu)點就是避免了排序，但是開銷仍然非常大。
Extra:Using where 也意味著掃描完索引后還需要回表進行篩選。一般來說，得保證 type 至少達到 range 級別，最好能達到 ref。
在第 2 點中提到的“慢日志記錄Rows_examined: 1161559，看起來是全表掃描”，這里更正為“全索引掃描”，掃描行數(shù)確實等于表的行數(shù)；
c. 關于執(zhí)行計劃中：“rows：644”，其實這個只是估算值，并不準確，我們分析慢 SQL 時判斷準確的掃描行數(shù)應該以 slow log 中的 Rows_examined 為準。
4. 優(yōu)化建議：添加組合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)
優(yōu)化過程：
TASK_DATE 字段存在索引，但是選擇度很低，優(yōu)化器不會走這個索引，建議后續(xù)可以刪除這個索引：
select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK;+------------+---------------------------+| count(*) | count(distinct TASK_DATE) |+------------+---------------------------+| 1161559 | 223 |+------------+---------------------------+
在這個 sql 中 REL_DEVID 字段從命名上看選擇度較高，通過下面 sql 來檢驗確實如此：
select count(*),count(distinct REL_DEVID) from T_BIOMA_ELOCK_TASK;+----------+---------------------------+| count(*) | count(distinct REL_DEVID) |+----------+---------------------------+| 1161559 | 62235 |+----------+---------------------------+
由于有排序，所以得把 task_id 也加入到新建的索引中，REL_DEVID,task_id 組合選擇度 100%：
select count(*),count(distinct REL_DEVID,task_id) from T_BIOMA_ELOCK_TASK;+----------+-----------------------------------+| count(*) | count(distinct REL_DEVID,task_id) |+----------+-----------------------------------+| 1161559 | 1161559 |+----------+-----------------------------------+
在測試環(huán)境添加 REL_DEVID，TASK_ID 組合索引，測試 sql 性能：alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID);
添加索引后執(zhí)行計劃：
這里還要注意一點“隱式轉換”：REL_DEVID 字段數(shù)據(jù)類型為 varchar，需要在 sql 中加引號：AND T.REL_DEVID = 000000025xxx AND T.REL_DEVID = '000000025xxx'
執(zhí)行時間從 10s+ 降到 毫秒級別：
1 row in set (0.00 sec)
結論
一個典型的 order by 查詢的優(yōu)化，添加更合適的索引可以避免性能問題：執(zhí)行計劃使用索引并不意味著就能執(zhí)行快。
上一篇給小伙伴們講了關于SQL查詢性能優(yōu)化的相關技巧，一個好的查詢SQL離不開合理的索引設計。這篇小二就來嘮一嘮怎么合理的設計一個索引來優(yōu)化我們的查詢速度，要是有不合理的地方...嗯..
當然啦，開個玩笑，歡迎小伙伴們指正！
通常情況下，字段類型的選擇是需要根據(jù)業(yè)務來判斷的，通常需要遵循以下幾點。
下列各種類型表格內(nèi)容來自菜鳥教程，權當備忘。
優(yōu)化建議：
注意： INT(2)設置的為顯示寬度，而不是整數(shù)的長度，需要配合 ZEROFILL 使用 。
例如 id 設置為 TINYINT(2) UNSIGNED ，表示無符號，可以存儲的最大數(shù)值為255，其中 TINYINT(2) 沒有配合 ZEROFILL 實際沒有任何意義，例如插入數(shù)字200，長度雖然超過了兩位，但是這個時候是可以插入成功的，查詢結果同樣為200；插入數(shù)字5時，同樣查詢結果為5。
而 TINYINT(2) 配合 ZEROFILL 后，當插入數(shù)字5時，實際存儲的還是5，不過在查詢是MySQL會在前面補上一個0，即查詢出來的實際為 05 。
優(yōu)化建議：
優(yōu)化建議：
通常來說，考慮好表中每個字段應該使用什么類型和長度，建完表需要做的事情不是馬上建立索引，而是先把相關主體業(yè)務開發(fā)完畢，然后把涉及該表的SQL都拿出來分析之后再建立索引。
盡量少建立單值索引( 唯一索引除外 )，應當設計一個或者兩三個聯(lián)合索引，讓每一個聯(lián)合索引都盡量去包含SQL語句中的 where、order by、group by 的字段，同時確保聯(lián)合索引的字段順序盡量滿足SQL查詢的最左前綴原則。
索引基數(shù)是指這個字段在表里總共有多少個不同的值，比如一張表總共100萬行記錄，其中有個性別字段，性別一共有三個值：男、女、保密，那么該字段的基數(shù)就是3。
如果對這種小基數(shù)字段建立索引的話，因為索引樹中只有男、女、保密三個值，根本沒法進行快速的二分查找，同時還需要回表查詢，還不如全表掃描嘞。
一般建立索引，盡量使用那些基數(shù)比較大的字段，那么才能發(fā)揮出B+樹快速二分查找的優(yōu)勢來。
在 where 和 order by 出現(xiàn)索引設計沖突時，是優(yōu)先針對where去設計索引？還是優(yōu)先針對order by設計索引？
通常情況下都是優(yōu)先針對 where 來設計索引，因為通常情況下都是先 where 條件使用索引快速篩選出來符合條件的數(shù)據(jù)，然后對進行篩選出來的數(shù)據(jù)進行排序和分組，而 where 條件快速篩選出來的的數(shù)據(jù)往往不會很多。
對生產(chǎn)實際運行過程中，或者測試環(huán)境大數(shù)據(jù)量測試過程中發(fā)現(xiàn)的慢查詢SQL進行特定的索引優(yōu)化、代碼優(yōu)化等策略。
終于輪到實戰(zhàn)了，小二最喜歡實戰(zhàn)了。
寫到這里不得不吐槽一下，這個金三銀四的跳槽季節(jié)，年前提離職了，結果離職還沒辦完就封村整整兩個禮拜了，嗚嗚嗚...
上節(jié)小二就提到會有個很有意思的小案例，那么在疫情當下，門都出不去的日子，感覺這個例子更有意思了，咱們來討論一下各種社交平臺怎么做的用戶信息搜索呢。
社交平臺有一個小伙伴們都喜歡的功能，搜索好友信息，比如小二熟練的點開省份...城市..性別..年齡..身高...
咳咳咳...小二怎么可能干這種事情，小二的心里只有代碼，嗯...沒錯，就是這樣。
這個就可以說是對于用戶信息的查詢篩選了，通常這種表都是非常大數(shù)據(jù)量的，在不考慮分庫分表的情況下，怎么通過索引配合SQL來優(yōu)化呢？
通常我們在編寫SQL是會寫出類似如下的SQL來執(zhí)行，有 where、order by、limit 等條件來查詢。
那么接下來小二一個一個慢慢增加字段來分析分析，怎么根據(jù)業(yè)務場景來設計索引。
針對這種情況，很簡單，設計一個聯(lián)合索引 (provice, city, sex) 就完事了。
那么這時候有小伙伴就會說了，很簡單啊，范圍字段放最后咱還是知道的，聯(lián)合索引改成 (provice, city, sex, age) 不就可以了。
嗯，是的，這么干沒毛病，但是小伙伴們有沒有想過有些人萬一既喜歡帥哥又喜歡美女，別想歪了哈...，挺多小姐姐就既喜歡帥哥又喜歡美女的。
那么這個時候小姐姐就不搜索性別了，那么這個時候聯(lián)合索引只能用到前兩個字段了，那么不符合咱們的專業(yè)標準啊，咋辦呢？這時候還是有辦法的，咱們只需要動動小腦袋改改SQL就行了，在沒有選擇性別時判斷一下，改成下面這樣就可以了。
咋辦嘞，同樣往聯(lián)合索引里面塞，例如 (provice, city, sex, hobby, xx, age) 。
針對這種多個范圍查詢的話，為了比較好的利用索引，在業(yè)務允許的情況下可以使用固定范圍，然后數(shù)據(jù)庫字段存儲范圍標識就可以了，這樣就轉化為了等值匹配，就可以很好地利用索引了。
例如最后登錄時間字段不記錄最后登錄時間，而是記錄設置字段 is_login_within_seven_days 在7天內(nèi)有登錄則為1，否則為0，最后索引設計成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。
那么根據(jù)場景最后設計出來的這個索引可能已經(jīng)可以覆蓋大部分的查詢流量了，那么如果還有其他一部分熱度比較高的查詢怎么辦呢，辦法也很簡單啊，再加一兩個索引即可。
例如通常會查詢這個城市比較受歡迎(評分：score)的小姐姐，這時候添加一個聯(lián)合索引 (provice, city, sex, score) 那么就可以了。
可以看出，索引時必須結合場景來設計的，思路就是盡量用不超過3個復雜的聯(lián)合索引來抗住大部分的80%以上的常用查詢流量，然后再用一兩個二級索引來抗下一些非常用查詢流量。
以上就是小二要給大家分享的索引設計，如果能動動你發(fā)財?shù)男∈纸o小二點個免費的贊就更好啦~
下篇小二就來講講MySQL事務和鎖機制。
1、索引并不是越多越好，要根據(jù)查詢有針對性的創(chuàng)建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描
2、應盡量避免在WHERE子句中對字段進行NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描
3、值分布很稀少的字段不適合建索引，例如”性別”這種只有兩三個值的字段
4、字符字段只建前綴索引
5、字符字段最好不要做主鍵
6、不用外鍵，由程序保證約束
7、盡量不用UNIQUE，由程序保證約束
8、使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引
重慶分公司
重慶分公司
