mysql索引優化實例說明,mysql索引優化的方案

本文目錄一覽：

1、mysql優化：覆蓋索引（延遲關聯）
2、「Mysql索引原理（七）」覆蓋索引
3、mysql—索引優化
4、輕鬆優化MySQL-之索引優化2 附贈送優化口訣
5、第142節課.mysql優化之什麼是索引
6、mysql–索引優化

mysql優化：覆蓋索引（延遲關聯）

我們都知道InnoDB採用的B+ tree來實現索引的，索引又分為主鍵索引(聚簇索引)和普通索引(二級索引)。

那麼我們就來看下基於主鍵索引和普通索引的查詢有什麼區別？

舉個栗子：

可以看出我們有一個普通索引k，那麼兩顆B+樹的示意圖如下：

[圖片上傳失敗…(image-9b05f7-1597911217600)]

（註：圖來自極客時間專欄）

當我們查詢** select * from T where k=5 其實會先到k那個索引樹上查詢k = 5，然後找到對應的id為500，最後回表到主鍵索引的索引樹找返回所需數據。

如果我們查詢 select id from T where k=5 **則不需要回表就直接返回。

也就是說，基於非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應該盡量使用主鍵查詢。

概念如上，這裡我們還是用例子來說明：

/pre

[圖片上傳失敗…(image-20977-1597911217600)]

（註：圖來自極客時間專欄）

現在，我們一起來看看這條SQL查詢語句的執行流程： select * from T where k between 3 and 5

在這個過程中，回到主鍵索引樹搜索的過程，我們稱為回表。可以看到，這個查詢過程讀了k索引樹的3條記錄（步驟1、3和5），回表了兩次（步驟2和4）。

在這個例子中，由於查詢結果所需要的數據只在主鍵索引上有，所以不得不回表。那麼，有沒有可能經過索引優化，避免回表過程呢？

如果執行的語句是select ID from T where k between 3 and 5，這時只需要查ID的值，而ID的值已經在k索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裡面，索引k已經“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。

由於覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優化手段。

需要注意的是，在引擎內部使用覆蓋索引在索引k上其實讀了三個記錄，R3~R5（對應的索引k上的記錄項），但是對於MySQL的Server層來說，它就是找引擎拿到了兩條記錄，因此MySQL認為掃描行數是2。

上面介紹了那麼多其實是在為延遲關聯做鋪墊，這裡直接續上我們本次慢查詢的sql：

我們都知道在做分頁時會用到Limit關鍵字去篩選所需數據，limit接受1個或者2個參數，接受兩個參數時第一個參數表示偏移量，即從哪一行開始取數據，第二個參數表示要取的行數。如果只有一個參數，相當於偏移量為0。

當偏移量很大時，如limit 100000,10 取第100001-100010條記錄，mysql會取出100010條記錄然後將前100000條記錄丟棄，這無疑是一種巨大的性能浪費。

當有這種寫法時，我們可以採用延遲關聯來進行優化，重點關註： SELECT id FROM qa_question WHERE expert_id = 69 AND STATUS = 30 ORDER BY over_time DESC LIMIT 0, 10 ，這裡其實利用了索引覆蓋，where條件後的expert_id 是有添加索引的，這裡查詢id 可以避免回表，大大提升效率。

工作中會遇到各種各樣的問題，對於一個研發來說最重要的是能夠從這些問題中學到什麼。好久沒有寫博客了，究其原因還是自己變得懶惰了。 (￣ェ￣;)

最後以《高性能Mysql》中的一段話結束：

「Mysql索引原理（七）」覆蓋索引

通常大家都會根據查詢的WHERE條件來創建合適的索引，不過這只是索引優化的一個方面。設計優秀的索引應該考慮到整個查詢，而不單單是WHERE條件部分。索引確實是一種查找數據的高效方式，但是MySQL也可以使用索引來直接獲取列的數據，這樣就不再需要讀取數據行。如果索引的葉子節點中已經包含要查詢的數據，那麼還有什麼必要再回到表中查詢呢？如果一個索引覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

覆蓋索引是非常有用的工具，能夠極大地提高性能：

在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多。

不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引都不存儲索引列的值，所以MySQL只能使用B+Tree索引所覆蓋索引。另外，不同的存儲引擎實現覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。

當發起一個唄索引覆蓋的查詢是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

索引覆蓋查詢還有很多陷阱可能會導致無法實現優化。MySQL查詢優化器會在執行查詢前判斷是否有一個索引能進行覆蓋。假設索引覆蓋了wehre條件中的字段，但不是整個查詢涉及的字段。mysql5.5和更早的版本也總是會回表獲取數據行，儘管並不需要這一行且最終會被過濾掉。

如： EXPLAIN select * from people where last_name=’Allen’ and first_name like ‘%Kim%’

這裡索引無法覆蓋該查詢，有兩個原因：

這條語句只檢索1行，而之前的 like ‘%Kim%’要檢索3行。

也有辦法解決上面所說的兩個問題，需要重寫查詢並巧妙設計索引。

這種方式叫做延遲關聯，因為延遲了對列的訪問。在查詢第一個階段MySQL可以使用覆蓋索引，因為索引包含了主鍵id的值，不需要做二次查找。

在FROM子句的子查詢中找到匹配的id，然後根據這些id值在外層查詢匹配獲取需要的所有列值。雖然無法使用索引覆蓋整個查詢，但總算比完全無法利用索引覆蓋的好吧。

數據量大了怎麼辦？

這樣優化的效果取決於WHERE條件匹配返回的行數。假設這個people表有100萬行，我們看一下上面兩個查詢在三個不同的數據集上的表現，每個數據集都包含100萬行。

實例1中，查詢返回了一個很大的結果集，因此看不到優化的效果。大部分時間都花在讀取和發送數據上了。

實例2中，經過索引過濾，尤其是第二個條件過濾後只返回了很少的結果集，優化的效果非常明顯：在這個數據及上性能提高了很多，優化後的查詢效率主要得益於只需讀取40行完整數據行，而不是原查詢中需要的30000行。

實例3中，子查詢效率反而下降。因為索引過濾時符合第一個條件的結果集已經很小了，所以子查詢帶來的成本反而比從表中直接提取完整行更高。

在大多數存儲引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以更進一步優化InnoDB。回想一下，InnoDB的二級索引的葉子節點都包含了主鍵的值，這意味着InnoDB的二級索引可以有效地利用這些額外的主鍵列來覆蓋查詢。

例如，people表中last_name字段有一個二級索引，雖然該索引的列不包括主鍵id，但也能夠用於對id做覆蓋查詢：

select id,last_name from people where last_name=’hua’

mysql—索引優化

索引就是為特定的mysql字段進行一些特定的算法排序，比如二叉樹的算法和哈希算法，哈希算法是通過建立特徵值，然後根據特徵值來快速查找。

1.普通索引:(index)最基本的索引，沒有任何限制目的:加快數據的查詢速度

2.唯一索引:(unique) 與”普通索引”類似，不同的就是：索引列的值必須唯一，但允許有空值。

3.主鍵索引(primary key) 它是一種特殊的唯一索引，不允許有空值。

4.複合索引:index(a,b,c) 為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。

5.全文索引:fulltext 僅可用於 MyISAM 表，針對較大的數據，生成全文索引很耗時耗空間。

第一類是myisam存儲引擎使用的叫做b-tree結構，

第二類是innodb存儲引擎使用的叫做聚簇結構（也是一種 b-tree）。如下圖：

注意：

1.myisam不需要回行處理

2.innodb不需要回行處理,直接可以獲取數據,因為innodb的儲存引擎是包含了數據和索引文件的,其主鍵索引包含了數據,(唯一索引及普通索是沒有直接包含數據的)

1、索引列不能參與計算

有索引列參與計算的查詢條件對索引不友好（甚至無法使用索引），如from_unixtime(create_time) = ‘2014-05-29’。

原因很簡單，如何在節點中查找到對應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，則會在每個節點依次命中a、b、c，無法命中d。也就是最左前綴匹配原則。

3、冗餘和重複索引

冗餘索引是指在相同的列上按照相同的順序創建的相同類型的索引，應當盡量避免這種索引，發現後立即刪除。比如有一個索引(A,B)，再創建索引(A)就是冗餘索引。冗餘索引經常發生在為表添加新索引時，比如有人新建了索引(A,B)，但這個索引不是擴展已有的索引(A)

4、避免多個範圍條件

select user.* from user where login_time ‘2017-04-01’ and age between 18 and 30;

比如想查詢某個時間段內登錄過的用戶：它有兩個範圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無法同時使用它們 .

5、覆蓋索引 (能擴展就不新建)

如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那麼就沒有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：

1.索引條目遠小於數據行大小，如果只讀取索引，極大減少數據訪問量2.索引是有按照列值順序存儲的，對於I/O密集型的範圍查詢要比隨機從磁盤讀取每一行數據的IO要少的多

6、選擇區分度高的列作索引

如，用性別作索引，那麼索引僅能將1000w行數據劃分為兩部分（如500w男，500w女），索引幾乎無效。

區分度的公式是count(distinct ) / count(*)，表示字段不重複的比例，比例越大區分度越好。唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前的區分度趨近於0。

7、刪除長期未使用的索引

場景一(覆蓋索引 5)

索引應該建在選擇性高的字段上（鍵值唯一的記錄數/總記錄條數），選擇性越高索引的效果越好、價值越大，唯一索引的選擇性最高；

組合索引中字段的順序，選擇性越高的字段排在最前面；

where條件中包含兩個選擇性高的字段時，可以考慮分別創建索引，引擎會同時使用兩個索引（在OR條件下，應該說必須分開建索引）；

不要重複創建彼此有包含關係的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；

組合索引的字段不要過多，如果超過4個字段，一般需要考慮拆分成多個單列索引或更為簡單的組合索引；

不要濫用索引。因為過多的索引不僅僅會增加物理存儲的開銷，對於插入、刪除、更新操作也會增加處理上的開銷，而且會增加優化器在選擇索引時的計算代價。

因此太多的索引與不充分、不正確的索引對性能都是毫無益處的。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應該經過仔細分析，要有建立的依據。

輕鬆優化MySQL-之索引優化2 附贈送優化口訣

索引是在存儲引擎中實現的，也就是說不同的存儲引擎，會使用不同的索引。MyISAM和InnoDB存儲引擎：只支持BTREE索引，也就是說默認使用BTREE，不能夠更換，MySQL5.7中InnoDB可以支持HASH索引；MEMORY/HEAP存儲引擎：支持HASH和BTREE索引。索引可劃分為單列索引（其中包括普通索引、唯一索引、主鍵索引）、組合索引、全文索引、空間索引，其中單列索引是一個索引只包含單個列，但一個表中可以有多個單列索引。

MySQL中基本索引類型，沒有什麼限制，允許在定義索引的列中插入重複值和空值，純粹為了查詢數據更快一點。

索引列中的值必須是唯一的，但是允許為空值，

是一種特殊的唯一索引，不允許有空值。

在表中的多個字段組合上創建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，使用組合索引時遵循最左前綴集合。

由id、name和age3個字段構成的索引，索引行中就按id/name/age的順序存放，索引可以索引下面字段組合(id，name，age)、(id，name)或者(id)。如果要查詢的字段不構成索引最左面的前綴，那麼就不會是用索引，比如，age或者（name，age）組合就不會使用索引查詢

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類型字段上使用全文索引。全文索引就是在一堆文字中，通過其中的某個關鍵字等，就能找到該字段所屬的記錄行，比如有”你是個大牛，神人 …” 通過大牛，可能就可以找到該條記錄。這裡說的是可能，因為全文索引的使用涉及了很多細節，我們只需要知道這個大概意思。

只有在MyISAM引擎上才能使用，空間索引是對空間數據類型的字段建立的索引，MySQL中的空間數據類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。

在創建空間索引時，使用SPATIAL關鍵字。

創建空間索引的列，必須將其聲明為NOT NULL。。

SPATIAL INDEX spatIdx(g)

全值匹配我最愛，最左前綴要遵守；

帶頭大哥不能死，中間兄弟不能斷；

索引列上少計算，範圍之後全失效；

Like百分寫最右，覆蓋索引不寫星；

不等空值還有or，索引失效要少用；

VAR引號不可丟，SQL高級也不難！

參考： u;/u

如果索引包含滿足查詢的所有數據，就稱為覆蓋索引。覆蓋索引是一種非常強大的工具，能大大提高查詢性能。只需要讀取索引而不用讀取數據有以下一些優點：

(1) 索引項通常比記錄要小，所以MySQL訪問更少的數據；

(2) 索引都按值的大小順序存儲，相對於隨機訪問記錄，需要更少的I/O；

(3) 大多數據引擎能更好的緩存索引。比如MyISAM只緩存索引。

(4) 覆蓋索引對於InnoDB表尤其有用，因為InnoDB使用聚集索引組織數據，如果二級索引中包含查詢所需的數據，就不再需要在聚集索引中查找了。

覆蓋索引不能是任何索引，只有B-TREE索引存儲相應的值。而且不同的存儲引擎實現覆蓋索引的方式都不同，並不是所有存儲引擎都支持覆蓋索引(Memory和Falcon就不支持)。

對於索引覆蓋查詢(index-covered query)，使用EXPLAIN時，可以在Extra一列中看到“Using index”。

產品中有一張圖片表，數據量將近100萬條，有一條相關的查詢語句，由於執行頻次較高，想針對此語句進行優化。表結構很簡單，主要字段：

user_id 用戶ID

picname 圖片名稱

smallimg 小圖名稱

一個用戶會有多條圖片記錄，現在有一個根據user_id建立的索引：uid，查詢語句也很簡單。取得某用戶的圖片集合

執行查詢語句（為了查看真實執行時間，強制不使用緩存）

執行了10次，平均耗時在40ms左右。使用explain進行分析

使用了user_id的索引，並且是const常數查找，表示性能已經很好了

因為這個語句太簡單，sql本身沒有什麼優化空間，就考慮了索引。修改索引結構，建立一個(user_id,picname,smallimg)的聯合索引：uid_pic。重新執行10次，平均耗時降到了30ms左右。使用explain進行分析

看到使用的索引變成了剛剛建立的聯合索引，並且Extra部分顯示使用了’Using Index’

‘Using Index’的意思是“覆蓋索引”，它是使上面sql性能提升的關鍵。一個包含查詢所需字段的索引稱為“覆蓋索引”，MySQL只需要通過索引就可以返回查詢所需要的數據，而不必在查到索引之後進行回表操作，減少IO，提高了效率。

例如上面的sql，查詢條件是user_id，可以使用聯合索引，要查詢的字段是picname smallimg，這兩個字段也在聯合索引中，這就實現了“覆蓋索引”，可以根據這個聯合索引一次性完成查詢工作，所以提升了性能

InnoDB存儲引擎由於實現了行級鎖定，雖然在鎖定機制的實現方面帶來的性能損耗可能比表級鎖定要更高一些，但是在整體並發處理能力方面是要遠遠優於MyISAM的表級鎖定的。當系統並發量較高的時候，InnoDB的整體性能和MyISAM相比就會有比較明顯的優勢了。但是當我們使用不當的時候，可能會讓InnoDB的整體性能表現不僅不比MyISAM高，甚至可能會更差。

建議：

（1）儘可能讓所有的數據檢索都通過索引來完成，從而避免InnoDB因為無法通過索引鍵加鎖而升級為表級鎖定

（2）合理設計索引，讓InnoDB在索引鍵上面加鎖的時候儘可能準確，儘可能地縮小鎖定範圍，避免造成不必要的鎖定而影響其他Query的執行

（3）儘可能減少基於範圍的數據檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄

（4）盡量控制事務的大小，減少鎖定的資源量和鎖定時間長度

（5）在業務環境允許的情況下，盡量使用較低級別的事務隔離，以減少MySQL因為實現事務隔離級別所帶來的附加成本。

第142節課.mysql優化之什麼是索引

從 MySQL 5.7 開始，開發人員改變了 InnoDB 構建二級索引的方式，採用自下而上的方法，而不是早期版本中自上而下的方法了。在這篇文章中，我們將通過一個示例來說明如何構建 InnoDB 索引。最後，我將解釋如何通過為 innodb_fill_factor 設置更合適的值。

索引構建過程

在有數據的表上構建索引，InnoDB 中有以下幾個階段：1.讀取階段（從聚簇索引讀取並構建二級索引條目）2.合併排序階段3.插入階段（將排序記錄插入二級索引）在 5.6 版本之前，MySQL 通過一次插入一條記錄來構建二級索引。這是一種“自上而下”的方法。搜索插入位置從樹的根部（頂部）開始並達到葉頁（底部）。該記錄插入光標指向的葉頁上。在查找插入位置和進行業面拆分和合併方面開銷很大。從MySQL 5.7開始，添加索引期間的插入階段使用“排序索引構建”，也稱為“批量索引加載”。在這種方法中，索引是“自下而上”構建的。即葉頁（底部）首先構建，然後非葉級別直到根（頂部）。

示例

在這些情況下使用排序的索引構建：

ALTER TABLE t1 ADD INDEX（or CREATE INDEX）

ALTER TABLE t1 ADD FULLTEXT INDEX

ALTER TABLE t1 ADD COLUMN, ALGORITHM = INPLACE

OPIMIZE t1

對於最後兩個用例，ALTER 會創建一個中間表。中間表索引（主要和次要）使用“排序索引構建”構建。

算法

在 0 級別創建頁，還要為此頁創建一個游標

使用 0 級別處的游標插入頁面，直到填滿

頁面填滿後，創建一個兄弟頁（不要插入到兄弟頁）

為當前的整頁創建節點指針（子頁中的最小鍵，子頁碼），並將節點指針插入上一級（父頁）

在較高級別，檢查游標是否已定位。如果沒有，請為該級別創建父頁和游標

在父頁插入節點指針

如果父頁已填滿，請重複步驟 3, 4, 5, 6

現在插入兄弟頁並使游標指向兄弟頁

在所有插入的末尾，每個級別的游標指向最右邊的頁。提交所有游標（意味着提交修改頁面的迷你事務，釋放所有鎖存器）

為簡單起見，上述算法跳過了有關壓縮頁和 BLOB（外部存儲的 BLOB）處理的細節。

通過自下而上的方式構建索引

為簡單起見，假設子頁和非子頁中允許的最大記錄數為 3

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c BLOB);

INSERT INTO t1 VALUES (1, 11, ‘hello111’);

INSERT INTO t1 VALUES (2, 22, ‘hello222’);

INSERT INTO t1 VALUES (3, 33, ‘hello333’);

INSERT INTO t1 VALUES (4, 44, ‘hello444’);

INSERT INTO t1 VALUES (5, 55, ‘hello555’);

INSERT INTO t1 VALUES (6, 66, ‘hello666’);

INSERT INTO t1 VALUES (7, 77, ‘hello777’);

INSERT INTO t1 VALUES (8, 88, ‘hello888’);

INSERT INTO t1 VALUES (9, 99, ‘hello999’);

INSERT INTO t1 VALUES (10, 1010, ‘hello101010’);

ALTER TABLE t1 ADD INDEX k1(b);

InnoDB 將主鍵字段追加到二級索引。二級索引 k1 的記錄格式為(b, a)。在排序階段完成後，記錄為：

(11,1), (22,2), (33,3), (44,4), (55,5), (66,6), (77,7), (88,8), (99,9), (1010, 10)

初始插入階段

讓我們從記錄 (11,1) 開始。

在 0 級別（葉級別）創建頁

創建一個到頁的游標

所有插入都將轉到此頁面，直到它填滿了

箭頭顯示游標當前指向的位置。它目前位於第 5 頁，下一個插入將轉到此頁面。

還有兩個空閑插槽，因此插入記錄 (22,2) 和 (33,3) 非常簡單

對於下一條記錄 (44,4)，頁碼 5 已滿（前面提到的假設最大記錄數為 3）。這就是步驟。

頁填充時的索引構建

創建一個兄弟頁，頁碼 6

不要插入兄弟頁

在游標處提交頁面，即迷你事務提交，釋放鎖存器等

作為提交的一部分，創建節點指針並將其插入到【當前級別 + 1】的父頁面中（即在 1 級別）

節點指針的格式 (子頁面中的最小鍵,子頁碼) 。第 5 頁的最小鍵是 (11,1) 。在父級別插入記錄 ((11,1)，5)。

1 級別的父頁尚不存在，MySQL 創建頁碼 7 和指向頁碼 7 的游標。

將 ((11,1)，5) 插入第 7 頁

現在，返回到 0 級並創建從第 5 頁到第 6 頁的鏈接，反之亦然

0 級別的游標現在指向兄弟頁，頁碼為 6

將 (44,4) 插入第 6 頁

下一個插入 – (55,5) 和 (66,6) – 很簡單，它們轉到第 6 頁。

插入記錄 (77,7) 類似於 (44,4)，除了父頁面（頁面編號 7）已經存在並且它有兩個以上記錄的空間。首先將節點指針 ((44,4),8) 插入第 7 頁，然後將 (77,7) 記錄到同級 8 頁中。

插入記錄 (88,8) 和 (99,9) 很簡單，因為第 8 頁有兩個空閑插槽。

下一個插入 (1010,10) 。將節點指針 ((77,7),8) 插入 1級別的父頁（頁碼 7）。

MySQL 在 0 級創建同級頁碼 9。將記錄 (1010,10) 插入第 9 頁並將光標更改為此頁面。

以此類推。在上面的示例中，數據庫在 0 級別提交到第 9 頁，在 1 級別提交到第 7 頁。

我們現在有了一個完整的 B+-tree 索引，它是自下至上構建的！

索引填充因子

全局變量 innodb_fill_factor 用於設置插入 B-tree 頁中的空間量。默認值為 100，表示使用整個業面（不包括頁眉）。聚簇索引具有 innodb_fill_factor=100 的免除項。在這種情況下，聚簇索引也空間的 1 /16 保持空閑。即 6.25% 的空間用於未來的 DML。

值 80 意味着 MySQL 使用了 80% 的頁空間填充，預留 20% 於未來的更新。如果 innodb_fill_factor=100 則沒有剩餘空間供未來插入二級索引。如果在添加索引後，期望表上有更多的 DML，則可能導致業面拆分並再次合併。在這種情況下，建議使用 80-90 之間的值。此變量還會影響使用 OPTIMIZE TABLE 和 ALTER TABLE DROP COLUMN, ALGOITHM=INPLACE 重新創建的索引。也不應該設置太低的值，例如低於 50。因為索引會佔用浪費更多的磁盤空間，值較低時，索引中的頁數較多，索引統計信息的採樣可能不是最佳的。優化器可以選擇具有次優統計信息的錯誤查詢計劃。

排序索引構建的優點

沒有頁面拆分（不包括壓縮表）和合併

沒有重複搜索插入位置

插入不會被重做記錄（頁分配除外），因此重做日誌子系統的壓力較小

缺點

ALTER 正在進行時，插入性能降低 Bug＃82940，但在後續版本中計劃修復。

請點擊輸入圖片描述

mysql–索引優化

索引覆蓋是指如果查詢的列恰好是索引的一部分,那麼查詢只需要在索引文件上進行,不需要回行到磁盤再找數據。這種查詢速度非常快,稱為”索引覆蓋”

1查詢頻繁 2區分度高 3長度小 4盡量能覆蓋常用查詢字段

索引長度直接影響索引文件的大小,影響增刪改的速度,並間接影響查詢速度(佔用內存多)。因此對於一些長短不同的字節，我們會針對列中的值,從左往右截取部分,來建索引。但是：

1:截的越短, 重複度越高,區分度越小, 索引效果越不好

2:截的越長, 重複度越低,區分度越高, 索引效果越好,但帶來的影響也越大–增刪改變慢,並間影響查詢速度.

所以,我們要在區分度 + 長度兩者上,取得一個平衡（ distinct 去重）

select count (distinct left (word,6)) / count (*) from tablename;

對於一般的系統應用區別度能達到 0.1 ,索引的性能就可以接受.

alter table tablename add index word(word(4));

給字符串類型的字段建立索引效率不高，但是必須要經常查這個字段怎麼建索引？

比如說一個字段url，類型是字符串。那麼可以建一個字段 crcurl 來存儲url字段crc32後的值，並給 crcurl 建立索引。

crc32：循環冗餘校驗。根據網上數據包或計算機文件等數據產生簡短固定位數校驗碼的一種散列函數，主要用來檢測或校驗數據傳輸或者保存後可能出現的錯誤。生成的數字在傳輸或者存儲之前計算出來並且附加到數據後面，然後接收方進行檢驗確定數據是否發生變化。一般來說，循環冗餘校驗的值都是32位的整數。

crc32 是整形，在MySQL中，給整形字段建立索引效率比較高，crc32雖然不能確保唯一性，但是無礙，相同的機率也是極小，關鍵是可以大大減少查詢的範圍，給crcurl這個字段建立索引，查詢的時候帶上crcurl字段就可以利用到索引。

不允許翻過100頁（百度搜索一般到70頁左右）

首先我們直接大數據分頁limit 5000000,10 發現耗時4.41秒

接下來我們轉換方式使用where條件查詢，只耗時0.02秒

2次的查詢結果不一致，這是因為數據被物理刪除過有空洞.，因此我們可以追加軟刪除功能

分析:優化思路是不查,少查,查索引,少取.

我們現在必須要查,則只查索引,不查數據,得到id.

再用id去查具體條目. 這種技巧就是延遲索引.

分析：limit是先查詢再越過，也就是說我們先查詢出所有數據再進行跳躍，上圖我們越過500W頁，還使用了inner join 內存並沒有崩掉，這是因為我們子句tmp臨時表中只查詢了id（索引覆蓋，不需要回行去磁盤找數據了）然後拿到這10個id 分別查詢這10條數據。

排序可能發生2種情況:

1:對於覆蓋索引,直接在索引上查詢時,就是有順序的, using index

2:先取出數據,形成臨時表做filesort(文件排序,但文件可能在磁盤上,也可能在內存中)

我們的爭取目標：取出來的數據本身就是有序的! 利用索引來排序,那麼什麼時候發生索引排序呢？即查詢索引和order by的字段是同一個字段

goods表中 cat_id與shop_price組成聯合索引：

select goods_id,cat_id,shop_price from goods where cat_id=4 order by shop_price; 可以直接利用索引來排序,

using where按照shop_price索引取出的結果,本身就是有序的

select goods_id,cat_id,shop_price from goods order by click_count;

using filesort用到了文件排序,即取出的結果再次排序

重複索引是指在同1個列(如age), 或者順序相同的幾個列(age,school), 建立了多個索引,稱為重複索引,重複索引沒有任何幫助,只會增大索引文件,拖慢更新速度。

冗餘索引是指2個索引所覆蓋的列有重疊, 稱為冗餘索引。比如x,m,列，加索引 index x(x), index xm(x,m) x,xm索引, 兩者的x列重疊了, 這種情況,稱為冗餘索引. （mx, xm 不是重複的,因為列的順序不一樣）

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/282681.html