在mysql中，索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，由數(shù)據(jù)表中的一列或多列組合而成，可以用來快速查詢數(shù)據(jù)表中有某一特定值的記錄。
創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設(shè),漢臺企業(yè)網(wǎng)站建設(shè),漢臺品牌網(wǎng)站建設(shè),網(wǎng)站定制,漢臺網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,漢臺網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。
通過索引，查詢數(shù)據(jù)時不用讀完記錄的所有信息，而只是查詢索引列即可。
通過索引，查詢數(shù)據(jù)時不用讀完記錄的所有信息，而只是查詢索引列。否則，數(shù)據(jù)庫系統(tǒng)將讀取每條記錄的所有信息進行匹配。
可以把索引比作新華字典的音序表。例如，要查“庫”字，如果不使用音序，就需要從字典的 400 頁中逐頁來找。但是，如果提取拼音出來，構(gòu)成音序表，就只需要從 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 列類型設(shè)置索引。
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)點和缺點。
AND (URL_NAME LIKE '%非凡%' OR URL LIKE '%非凡%')
1、這個改成全文檢索
2、經(jīng)常使用的查詢條件建立索引
InnoDB在創(chuàng)建或重建索引時執(zhí)行批量加載，而不是一次插入一個索引記錄。這種索引創(chuàng)建方法也稱為排序索引構(gòu)建?？臻g索引不支持排序索引構(gòu)建。
索引構(gòu)建分為三個階段。在第一階段， 掃描聚集索引，生成索引條目并添加到排序緩沖區(qū)。當排序緩沖區(qū)變滿時，條目將被排序并寫入臨時中間文件。此過程也稱為 “運行”。在第二階段，將一個或多個運行寫入臨時中間文件，對文件中的所有條目執(zhí)行合并排序。在第三個也是最后一個階段，排序后的條目被插入到 B-tree中。
在引入排序索引構(gòu)建之前，使用插入 API 將索引條目一次插入 B 樹中的一條記錄。此方法涉及打開 B 樹 游標以查找插入位置，然后使用 樂觀插入將條目插入 B 樹頁面。如果由于頁面已滿而導(dǎo)致插入失敗， 則將執(zhí)行悲觀插入，這涉及打開 B-tree 游標并根據(jù)需要拆分和合并 B-tree 節(jié)點以找到條目空間。這種“自上而下”的弊端建立索引的方法是搜索插入位置的成本以及 B 樹節(jié)點的不斷拆分和合并。
排序索引構(gòu)建使用“自下而上”建立索引的方法。使用這種方法，對最右側(cè)葉頁的引用保存在 B 樹的所有級別。分配必要 B 樹深度的最右側(cè)葉頁，并根據(jù)其排序順序插入條目。一旦葉頁已滿，就會將節(jié)點指針附加到父頁，并為下一次插入分配一個兄弟葉頁。這個過程一直持續(xù)到所有條目都被插入，這可能導(dǎo)致插入到根級別。分配同級頁時，釋放對先前固定葉頁的引用，新分配的葉頁成為最右邊的葉頁和新的默認插入位置。
要為將來的索引增長留出空間，您可以使用innodb_fill_factor變量來保留一定百分比的 B 樹頁面空間。例如，設(shè)置 innodb_fill_factor為 80 會在排序索引構(gòu)建期間保留 B 樹頁面中 20% 的空間。此設(shè)置適用于 B 樹的葉子頁面和非葉子頁面。它不適用于用于 TEXT或 BLOB條目的外部頁面。保留的空間量可能與配置不完全相同，因為innodb_fill_factor值被解釋為提示而不是硬限制。
全文索引支持排序索引構(gòu)建 。以前，SQL 用于將條目插入全文索引。
對于壓縮表，以前的索引創(chuàng)建方法將條目附加到壓縮頁和未壓縮頁。當修改日志（表示壓縮頁面上的可用空間）變滿時，將重新壓縮壓縮頁面。如果由于空間不足而導(dǎo)致壓縮失敗，則頁面將被拆分。使用排序索引構(gòu)建，條目僅附加到未壓縮的頁面。當一個未壓縮的頁面變滿時，它就會被壓縮。自適應(yīng)填充用于確保在大多數(shù)情況下壓縮成功，但如果壓縮失敗，則會拆分頁面并再次嘗試壓縮。這個過程一直持續(xù)到壓縮成功。
在排序索引構(gòu)建期間禁用重做日志記錄。相反，有一個 檢查點來確保索引構(gòu)建可以承受意外退出或失敗。檢查點強制將所有臟頁寫入磁盤。在排序索引構(gòu)建期間，頁面清理線程會定期收到信號以刷新 臟頁，以確保可以快速處理檢查點操作。通常，當干凈頁面的數(shù)量低于設(shè)置的閾值時，頁面清理線程會刷新臟頁面。對于排序索引構(gòu)建，臟頁會被及時刷新以減少檢查點開銷并行化 I/O 和 CPU 活動。
排序索引構(gòu)建可能會導(dǎo)致 優(yōu)化器統(tǒng)計信息與以前的索引創(chuàng)建方法生成的統(tǒng)計信息不同。統(tǒng)計數(shù)據(jù)差異是由于用于填充索引的算法不同造成的。
索引用于快速找到特定一些值的記錄。如果沒有索引，MySQL就必須從第一行記錄開始讀取整個表來檢索記錄。表越大，資源消耗越大。如果在字段上有索引的話，MySQL就能很快決定該從數(shù)據(jù)文件的哪個位置開始搜索記錄，而無須查找所有的數(shù)據(jù)。如果表中有1000條記錄的話，那么這至少比順序地讀取數(shù)據(jù)快100倍。注意，如果需要存取幾乎全部1000條記錄的話，那么順序讀取就更快了，因為這樣會使磁盤搜索最少。
大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B樹方式存儲。只有空間類型的字段使用R樹存儲，MEMORY (HEAP)表支持哈希索引。
字符串默認都是自動壓縮前綴和后綴中的空格。
通常，如下所述幾種情況下可以使用索引。哈希索引(用于 MEMORY 表)的獨特之處在后面會討論到。
想要盡快找到匹配 WHERE 子句的記錄。
根據(jù)條件排除記錄。如果有多個索引可共選擇的話，MySQL通常選擇能找到最少記錄的那個索引。
做表連接查詢時從其他表中檢索記錄。
想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。優(yōu)化程序會在檢查索引的
key_col 字段前就先檢查其他索引部分是否使用了 WHERE key_part_# = constant 子句。這樣的話，
MySQL會為 MIN() 或 MAX() 表達式分別單獨做一次索引查找，并且將它替換成常數(shù)。當所有的表達式都被替換成常數(shù)后，查詢就立刻返回。如下：
SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
對表作排序或分組，當在一個可用的最左前綴索引上做分組或排序時(如 ORDER
BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。
有些時候，查詢可以優(yōu)化使得無需計算數(shù)據(jù)就能直接取得結(jié)果。當查詢使用表中的一個數(shù)字型字段，且這個字段是索引的最左部分，則可能從索引樹中能很快就取得結(jié)果：
SELECTkey_part3FROMtbl_nameWHEREkey_part1=1
假設(shè)有如下 SELECT 語句：
如果在 col1 和 col2 上有一個多字段索引的話，就能直接取得對應(yīng)的記錄了。
索引覆蓋是指如果查詢的列恰好是索引的一部分,那么查詢只需要在索引文件上進行,不需要回行到磁盤再找數(shù)據(jù)。這種查詢速度非?？?稱為”索引覆蓋”
? ? 1查詢頻繁????2區(qū)分度高????3長度小????4盡量能覆蓋常用查詢字段
索引長度直接影響索引文件的大小,影響增刪改的速度,并間接影響查詢速度(占用內(nèi)存多)。因此對于一些長短不同的字節(jié)，我們會針對列中的值,從左往右截取部分,來建索引。但是：
1:截的越短, 重復(fù)度越高,區(qū)分度越小, 索引效果越不好
2:截的越長, 重復(fù)度越低,區(qū)分度越高, 索引效果越好,但帶來的影響也越大--增刪改變慢,并間影響查詢速度.
所以,我們要在 ?區(qū)分度 + 長度 ?兩者上,取得一個平衡（ distinct?去重 ）
? ? select count (distinct?left (word,6)) / count (*) from tablename;
對于一般的系統(tǒng)應(yīng)用區(qū)別度能達到 0.1 ,索引的性能就可以接受.
? ? alter table tablename add index word(word(4));
給字符串類型的字段建立索引效率不高，但是必須要經(jīng)常查這個字段怎么建索引？
比如說一個字段url，類型是字符串。那么可以建一個字段 crcurl 來存儲url字段crc32后的值，并給 crcurl 建立索引。
???crc32：循環(huán)冗余校驗。根據(jù)網(wǎng)上數(shù)據(jù)包或計算機文件等數(shù)據(jù)產(chǎn)生簡短固定位數(shù)校驗碼的一種散列函數(shù)，主要用來檢測或校驗數(shù)據(jù)傳輸或者保存后可能出現(xiàn)的錯誤。生成的數(shù)字在傳輸或者存儲之前計算出來并且附加到數(shù)據(jù)后面，然后接收方進行檢驗確定數(shù)據(jù)是否發(fā)生變化。一般來說，循環(huán)冗余校驗的值都是32位的整數(shù)。
crc32 是整形，在MySQL中，給整形字段建立索引效率比較高，crc32雖然不能確保唯一性，但是無礙，相同的機率也是極小，關(guān)鍵是可以大大減少查詢的范圍，給crcurl這個字段建立索引，查詢的時候帶上crcurl字段就可以利用到索引。
? ? ? ? 不允許翻過100頁（百度搜索一般到70頁左右）?
首先我們直接大數(shù)據(jù)分頁limit 5000000,10? 發(fā)現(xiàn)耗時4.41秒
接下來我們轉(zhuǎn)換方式使用where條件查詢，只耗時0.02秒
? ? ? ? 2次的查詢結(jié)果不一致，這是因為數(shù)據(jù)被物理刪除過有空洞.，因此我們可以追加軟刪除功能
分析:優(yōu)化思路是 不查,少查,查索引,少取.
我們現(xiàn)在必須要查,則只查索引,不查數(shù)據(jù),得到id.
再用id去查具體條目. ?這種技巧就是延遲索引.
? ? 分析：limit是先查詢再越過，也就是說我們先查詢出所有數(shù)據(jù)再進行跳躍，上圖我們越過500W頁，還使用了inner?join? 內(nèi)存并沒有崩掉，這是因為我們子句tmp臨時表中只查詢了id（索引覆蓋，不需要回行去磁盤找數(shù)據(jù)了）然后拿到這10個id?分別查詢這10條數(shù)據(jù) 。
排序可能發(fā)生2種情況:
1:對于覆蓋索引,直接在索引上查詢時,就是有順序的, using index
2:先取出數(shù)據(jù),形成臨時表做filesort(文件排序,但文件可能在磁盤上,也可能在內(nèi)存中)
我們的爭取目標：取出來的數(shù)據(jù)本身就是有序的! 利用索引來排序,那么什么時候發(fā)生索引排序呢？即查詢索引和order by的字段是同一個字段
???goods表中 cat_id與shop_price組成聯(lián)合索引：
select goods_id,cat_id,shop_price from goods where cat_id=4 order by shop_price;????可以直接利用索引來排序,
using where按照shop_price索引取出的結(jié)果,本身就是有序的
? ? ?????select goods_id,cat_id,shop_price from goods order by click_count;
? ? ? ? ? using filesort用到了文件排序,即取出的結(jié)果再次排序
重復(fù)索引是指 在同1個列(如age), 或者順序相同的幾個列(age,school), 建立了多個索引,稱為重復(fù)索引,重復(fù)索引沒有任何幫助,只會增大索引文件,拖慢更新速度。
冗余索引是指2個索引所覆蓋的列有重疊, 稱為冗余索引。比如x,m,列，加索引 index x(x), ?index xm(x,m) x,xm索引, 兩者的x列重疊了, ?這種情況,稱為冗余索引. （mx, xm 不是重復(fù)的,因為列的順序不一樣）
索引就是為特定的mysql字段進行一些特定的算法排序，比如二叉樹的算法和哈希算法，哈希算法是通過建立特征值，然后根據(jù)特征值來快速查找。
1.普通索引:(index)最基本的索引，沒有任何限制? 目的:加快數(shù)據(jù)的查詢速度
2.唯一索引:(unique)? 與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。
3.主鍵索引(primary key) 它 是一種特殊的唯一索引，不允許有空值。
4.復(fù)合索引:index(a,b,c)? 為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。
5.全文索引:fulltext? 僅可用于 MyISAM 表，針對較大的數(shù)據(jù)，生成全文索引很耗時耗空間。
第一類是myisam存儲引擎使用的叫做b-tree結(jié)構(gòu)，
第二類是innodb存儲引擎使用的叫做聚簇結(jié)構(gòu)（也是一種 b-tree）。 如下圖：
注意：
1.myisam不需要回行處理?
2.innodb不需要回行處理,直接可以獲取數(shù)據(jù),因為innodb的儲存引擎是包含了數(shù)據(jù)和索引文件的,其主鍵索引包含了數(shù)據(jù),(唯一索引及普通索是沒有直接包含數(shù)據(jù)的)
1、索引列不能參與計算
有索引列參與計算的查詢條件對索引不友好（甚至無法使用索引），如from_unixtime(create_time) = '2014-05-29'。
原因很簡單，如何在節(jié)點中查找到對應(yīng)key？如果線性掃描，則每次都需要重新計算，成本太高；如果二分查找，則需要針對from_unixtime方法確定大小關(guān)系。
因此，索引列不能參與計算。上述from_unixtime(create_time) = '2014-05-29'語句應(yīng)該寫成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、冗余和重復(fù)索引
冗余索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引，應(yī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(*)，表示字段不重復(fù)的比例，比例越大區(qū)分度越好。唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前的區(qū)分度趨近于0。
7、刪除長期未使用的索引
場景一(覆蓋索引 5)
索引應(yīng)該建在選擇性高的字段上（鍵值唯一的記錄數(shù)/總記錄條數(shù)），選擇性越高索引的效果越好、價值越大，唯一索引的選擇性最高；
組合索引中字段的順序，選擇性越高的字段排在最前面；
where條件中包含兩個選擇性高的字段時，可以考慮分別創(chuàng)建索引，引擎會同時使用兩個索引（在OR條件下，應(yīng)該說必須分開建索引）；
不要重復(fù)創(chuàng)建彼此有包含關(guān)系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；
組合索引的字段不要過多，如果超過4個字段，一般需要考慮拆分成多個單列索引或更為簡單的組合索引；
不要濫用索引。因為過多的索引不僅僅會增加物理存儲的開銷，對于插入、刪除、更新操作也會增加處理上的開銷，而且會增加優(yōu)化器在選擇索引時的計算代價。
因此太多的索引與不充分、不正確的索引對性能都是毫無益處的。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應(yīng)該經(jīng)過仔細分析，要有建立的依據(jù)。
重慶分公司
重慶分公司
