MySQL調優之索引優化

PHPer技術棧發表於2021-11-09
MySql索引優化
系列文章：
MySQL調優之查詢優化 MySQL調優之索引優化 MySQL調優之分割槽表 MySQL調優效能監控之show profile MySQL調優使用者監控之show processlist MySQL調優效能監控之performance schema MySQL調優之索引資料結構詳解與索引優化 MySQL中redo log、undo log、binlog關係以及區別

1、索引的優點

1、減少了伺服器需要掃描的資料量

2、幫助伺服器避免排序和臨時表

例子:

select * from emp orde by sal desc;

那麼執行順序:

所以有索引的話就不會建立零時表,臨時表中,磁碟零時表比記憶體臨時表更加的消耗效能。

3、將隨機IO變成順序IO

如何理解隨機IO與順序IO呢?如圖:

這是一個磁碟,B是幾何扇區(僅做標記,無特殊含義),A是磁軌,C是扇區(sector,是物理讀寫的基本單位,通常是512位元組),D由這樣的多個扇區連在一起稱為磁碟塊(IO Block,是檔案系統讀寫資料的最小單位，也叫磁碟簇)。扇區是磁碟最小的物理儲存單元，作業系統將相鄰的扇區組合在一起，形成一個塊，對塊進行管理。值得注意的是這是一個邏輯概念而不是物理概念。

大家都知道資訊是儲存在磁碟的扇區上,假如去吃旋轉小火鍋,上面的菜就是資料,旋轉的旋轉帶就是磁軌,夾在就是尋找菜的過程,如果旋轉一圈需要四分鐘,那麼你吃一個菜,最短用時0min,最長4min,數學期望就是2min,如果你要吃8個菜並且不在一起(這就是隨機IO),那麼數學期望就是16min,如果要吃的8個菜緊挨著(順序IO),那麼數學期望就是2min,可見順序IO能縮短定址時間。

題外話:為什麼存在磁碟塊？（簇）

  1. 讀取方便：由於扇區的容量比較小(512byte)，數目眾多在定址時比較困難，所以作業系統就將相鄰的扇區組合在一起，形成一個塊(通常4k)，再對塊進行整體的操作。
  2. 分離對底層的依賴：作業系統忽略對底層物理儲存結構的設計。通過虛擬出來磁碟塊的概念，在系統中認為塊是最小的單位。

我們平常所說的4K對齊也就是指的塊大小，它表示作業系統讀取磁碟時一次讀取的資料大小。如果作業系統一次讀取4K，但是塊大小隻有2K，就相當於一次IO要做2次磁碟定址。而如果磁碟塊大小剛好也是4K，那麼一次IO就只需一次定址。相對而言，磁碟定址效率是很低的，多一次磁碟定址肯定會更加導致IO效率低，因此對磁碟進行4K對齊也是提高了系統的IO效能。

讀取資料磁碟上大概有三步:

2、索引的用處

1、快速查詢匹配WHERE子句的行

2、從consideration中消除行,如果可以在多個索引之間進行選擇，mysql通常會使用找到最少行的索引

3、如果表具有多列索引，則優化器可以使用索引的任何最左字首來查詢行

4、當有表連線的時候，從其他表檢索行資料

5、查詢特定索引列的min或max值

6、如果排序或分組時在可用索引的最左字首上完成的，則對錶進行排序和分組

7、在某些情況下，可以優化查詢以檢索值而無需查詢資料行

3、索引的分類

3.1、主鍵索引

​ 特殊的唯一索引,不允許有空值,例如表格中自增的id。

3.2、唯一索引

索引列值在表中唯一,在資料庫表結構中對欄位新增唯一索引後進行資料庫進行儲存操作時資料庫會判斷庫中是否已經存在此資料，不存在此資料時才能進行插入操作。

唯一索引經常用在插入資料時,例如高併發下,如何避免插入兩條同樣單號的資料呢?當然是在儲存資料的時候查一遍，那麼怎樣查詢快呢？ 當然是建立索引，所以，在建立唯一約束的時候就建立了唯一索引。

注意的坑

新增唯一索引後有一種特殊情況，那就是如果該欄位沒有限制非空的話，存在插入NULL值的情況，此時，唯一索引並不起作用，也就是你可以插入n條該欄位為null的資料。說白了,mysql中null != null 。

除此之外，如果插入空字串的話，例如‘ ’ ‘ ’
不管中間是多少個空字串在插入的時候都算作‘’ ，即空串不論多長，只能插入一條。

3.3、普通索引

其他的就不寫了,提出個問題:普通索引和唯一索引哪個效率高?

後面再開篇詳談。

3.4、全文索引

具體實現是正排索引和倒排索引,主要使用的是倒排索引,如果要在很多資料中搜查關鍵字,例如google搜尋”huawei”,如果底層用like”huawei’”的話,會非常慢,需要掃描整個資料庫,為了提高搜素效率,可以建立關鍵字索引,屬性值為文章id的陣列,然後根據id確定文章位置,加權後將資訊以列表形式返回。

3.5、組合索引

簡單來說就是將多個列聯合設定為索引,使用時遵循最左匹配原則

4、索引有關的技術名詞

4.1、回表

4.2、覆蓋索引

前兩者參考我之前的博文:什麼是聚集索引、非聚集索引、覆蓋索引?

4.3、索引下推

參考我之前的推文:MySQL之謂詞下推

4.4、最左匹配

先說結論:當一個表格有N個屬性時,將(A,B,C)設定為聯合索引,那麼在查詢時,索引是否有效遵循下表:

索引 是否有效
A 有效
A and B 有效
A or B 無效
A and C 有效
B and C 無效
A and B and C 有效

也就是,從左到右,任何一次查詢匹配了最左邊的A才有效,A or B 意味著有一次只匹配了B,那麼無效。

5、索引採用的資料結構

5.1、雜湊表

​ MySQL中,Memory使用的雜湊表作為索引,InnoDB引擎有一個特殊的功能叫做自適應雜湊索引。

5.2、B+樹

參考我之前的文章:為什麼MySQL索引使用B+樹

6、索引匹配方式

下面的討論中,索引為(name,age,pos)

6.1、全值匹配

全值匹配指的是和索引中的所有列進行匹配

explain select * from staffs where name = ‘July’ and age = ‘23’ and pos = ‘dev’;

6.2、匹配最左字首

只匹配前面的幾列

explain select * from staffs where name = ‘July’ and age = ‘23’;

explain select * from staffs where name = ‘July’;

6.3、匹配列字首

可以匹配某一列的值的開頭部分

explain select * from staffs where name like ‘J%’;

explain select * from staffs where name like ‘%y’;

6.4、匹配範圍值

可以查詢某一個範圍的資料

6.5、精確匹配某一列並範圍匹配另外一列

可以查詢第一列的全部和第二列的部分

explain select * from staffs where name = ‘July’ and age > 25;

6.6、只訪問索引的查詢

查詢的時候只需要訪問索引，不需要訪問資料行，本質上就是覆蓋索引

explain select name,age,pos from staffs where name = ‘July’ and age = 25 and pos = ‘dev’;

1、HashMap索引的特點

基於雜湊表的實現，只有精確匹配索引所有列的查詢才有效

雜湊索引自身只需儲存對應的hash值，所以索引的結構十分緊湊，這讓雜湊索引查詢的速度非常快

2、HashMap索引的限制

  1. 雜湊索引只包含雜湊值和行指標，而不儲存欄位值，所以不能使用索引中的值來避免讀取行。
  2. 雜湊索引資料並不是按照索引值順序儲存的，所以也就無法用於排序。
  3. 雜湊索引也不支援部分索引列匹配查詢，因為雜湊索引始終是使用索引列的全部內容來計算雜湊值的。
  4. 雜湊索引只支援等值比較查詢，包括=、IN()、<>（注意<>和<=>是不同的操作）。也不支援任何範圍查詢，例如WHERE price>100。
  5. 訪問雜湊索引的資料非常快，除非有很多雜湊衝突（不同的索引列值卻有相同的雜湊值）。當出現雜湊衝突的時候，儲存引擎必須遍歷連結串列中所有的行指標，逐行進行比較，直到找到所有符合條件的行。
  6. 如果雜湊衝突很多的話，一些索引維護操作的代價也會很高。例如，如果在某個選擇性很低（雜湊衝突很多）的列上建立雜湊索引，那麼當從表中刪除一行時，儲存引擎需要遍歷對應雜湊值的連結串列中的每一行，找到並刪除對應行的引用，衝突越多，代價越大。

3、自適應雜湊

先總結下思路:Innodb 本身是使用的B+Tree索引,自適應hash只是提供的一種特性,當資料庫中某些熱點資料被頻繁訪問的時候,儲存引擎會自己適應地創造一個雜湊索引(只適用於=的情況,範圍查詢不適用),這個雜湊索引的目的是直接對判斷條件取Hash運算,得出資料儲存的地址,而不用一層層的取讀取B+Tree的磁碟塊,從而提高了料率。

圖示:

4、案例

當需要儲存大量的URL，並且根據URL進行搜尋查詢，如果使用B+樹，儲存的內容就會很大

select id from url where url=””

也可以利用將url使用CRC32做雜湊，可以使用以下查詢方式：

select id fom url where url=”” and url_crc=CRC32(“”)

此查詢效能較高原因是使用體積很小的索引來完成查詢

當包含多個列作為索引，需要注意的是正確的順序依賴於該索引的查詢，同時需要考慮如何更好的滿足排序和分組的需要,遵循最左匹配原則,下圖前提是建立了組合索引(3,4,5):

1、聚簇索引

定義:不是單獨的索引型別，而是一種資料儲存方式，指的是資料行跟相鄰的鍵與值緊湊的儲存在一起,在檔案上看會將兩個檔案放在一起。

參考我之前的文章:什麼是聚集索引、非聚集索引、覆蓋索引?

1.1、優點

1、可以把相關資料儲存在一起

2、資料訪問更快，因為索引和資料儲存在同一個樹中

3、使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值

1.2、缺點

1、聚簇資料最大限度地提高了IO密集型應用的效能，如果資料全部在記憶體，那麼聚簇索引就沒有什麼優勢

2、插入速度嚴重依賴於插入順序，按照主鍵的順序插入是最快的方式

3、更新聚簇索引列的代價很高，因為會強制將每個被更新的行移動到新的位置

4、基於聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨頁分裂的問題

5、聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由於頁分裂導致資料儲存不連續的時候

2、非聚簇索引

與聚簇索引最大的區別就是,非聚簇索引資料那兒儲存的不是資料,可能是資料的地址,該索引中索引的邏輯順序與磁碟上行的物理儲存順序不同。我們可以這麼理解聚簇索引：索引的葉節點就是資料節點。而非聚簇索引的葉節點仍然是索引節點(例如儲存資料存放的地址,而非資料)，有一個指標指向對應的資料塊。

1、基本介紹

1、如果一個索引包含所有需要查詢的欄位的值，我們稱之為覆蓋索引

2、不是所有型別的索引都可以稱為覆蓋索引，覆蓋索引必須要儲存索引列的值

3、不同的儲存實現覆蓋索引的方式不同，不是所有的引擎都支援覆蓋索引，memory不支援覆蓋索引

2、優勢

1、索引條目通常遠小於資料行大小，如果只需要讀取索引，那麼mysql就會極大的較少資料訪問量

2、因為索引是按照列值順序儲存的，所以對於IO密集型的範圍查詢會比隨機從磁碟讀取每一行資料的IO要少的多

3、一些儲存引擎如MYISAM在記憶體中只快取索引，資料則依賴於作業系統來快取，因此要訪問資料需要一次系統呼叫，這可能會導致嚴重的效能問題

4、由於INNODB的聚簇索引，覆蓋索引對INNODB表特別有用

3、詳細介紹案例

1、當發起一個被索引覆蓋的查詢時，在explain的extra列可以看到using index的資訊，此時就使用了覆蓋索引

mysql> explain select store_id,film_id from inventory\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: inventory
partitions: NULL
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4581
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.01 sec)

2、在大多數儲存引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以進一步的進行優化，可以使用innodb的二級索引來覆蓋查詢。

例如：actor使用innodb儲存引擎，並在last_name欄位又二級索引，雖然該索引的列不包括主鍵actor_id，但也能夠用於對actor_id做覆蓋查詢

mysql> explain select actor_id,last_name from actor where last_name=’HOPPER’\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 2
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)

其他參考我之前的文章:什麼是聚集索引、非聚集索引、覆蓋索引?

  1. 當使用索引列進行查詢的時候儘量不要使用表示式，把計算放到業務層而不是資料庫層

    select actor_id from actor where actor_id=4;

    select actor_id from actor where actor_id+1=5;

  2. 儘量使用主鍵查詢，而不是其他索引，因此主鍵查詢不會觸發回表查詢

  3. 使用字首索引

    ​ 有時候需要索引很長的字串，這會讓索引變的大且慢，通常情況下可以使用某個列開始的部分字串，這樣大大的節約索引空間，從而提高索引效率，但這會降低索引的選擇性，索引的選擇性是指不重複的索引值和資料表記錄總數的比值，範圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性更高的索引可以讓mysql在查詢的時候過濾掉更多的行。

    ​ 一般情況下某個列字首的選擇性也是足夠高的，足以滿足查詢的效能，但是對應BLOB,TEXT,VARCHAR型別的列，必須要使用字首索引，因為mysql不允許索引這些列的完整長度，使用該方法的訣竅在於要選擇足夠長的字首以保證較高的選擇性，通過又不能太長。

    案例演示：

    –建立資料表
    create table citydemo(city varchar(50) not null);
    insert into citydemo(city) select city from city;

    –重複執行5次下面的sql語句
    insert into citydemo(city) select city from citydemo;

    –更新城市表的名稱
    update citydemo set city=(select city from city order by rand() limit 1);

    –查詢最常見的城市列表，發現每個值都出現45-65次，
    select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

    –查詢最頻繁出現的城市字首，先從3個字首字母開始，發現比原來出現的次數更多，可以分別擷取多個字元檢視城市出現的次數
    select count() as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
    select count(    ) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
    –此時字首的選擇性接近於完整列的選擇性

    –還可以通過另外一種方式來計算完整列的選擇性，可以看到當字首長度到達7之後，再增加字首長度，選擇性提升的幅度已經很小了
    select count(distinct left(city,3))/count() as sel3,
    count(distinct left(city,4))/count(    ) as sel4,
    count(distinct left(city,5))/count() as sel5,
    count(distinct left(city,6))/count(    ) as sel6,
    count(distinct left(city,7))/count() as sel7,
    count(distinct left(city,8))/count(    ) as sel8
    from citydemo;

    –計算完成之後可以建立字首索引
    alter table citydemo add key(city(7));

    –注意：字首索引是一種能使索引更小更快的有效方法，但是也包含缺點：mysql無法使用字首索引做order by 和 group by。

  4. 使用索引掃描來排序

    mysql有兩種方式可以生成有序的結果：通過排序操作或者按索引順序掃描，如果explain出來的type列的值為index,則說明mysql使用了索引掃描來做排序

    ​ 掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那麼就不得不每掃描一條索引記錄就得回表查詢一次對應的行，這基本都是隨機IO，因此按索引順序讀取資料的速度通常要比順序地全表掃描慢

    ​ mysql可以使用同一個索引即滿足排序，又用於查詢行，如果可能的話，設計索引時應該儘可能地同時滿足這兩種任務。

    ​ 只有當索引的列順序和order by子句的順序完全一致，並且所有列的排序方式都一樣時，mysql才能夠使用索引來對結果進行排序，如果查詢需要關聯多張表，則只有當orderby子句引用的欄位全部為第一張表時，才能使用索引做排序。order by子句和查詢型查詢的限制是一樣的，需要滿足索引的最左字首的要求，否則，mysql都需要執行順序操作，而無法利用索引排序

    –sakila資料庫中rental表在rental_date,inventory_id,customer_id上有rental_date的索引
    –使用rental_date索引為下面的查詢做排序
    explain select rental_id,staff_id from rental where rental_date=’2005-05-25’ order by inventory_id,customer_id\G
    *** 1. row ***

           id: 1

    select_type: SIMPLE

        table: rental

    partitions: NULL

         type: ref

    possible_keys: rental_date

          key: rental_date
  key_len: 5
      ref: const
     rows: 1
 filtered: 100.00
    Extra: Using index condition

    1 row in set, 1 warning (0.00 sec)
    –order by子句不滿足索引的最左字首的要求，也可以用於查詢排序，這是因為所以你的第一列被指定為一個常數

    –該查詢為索引的第一列提供了常量條件，而使用第二列進行排序，將兩個列組合在一起，就形成了索引的最左字首
    explain select rental_id,staff_id from rental where rental_date=’2005-05-25’ order by inventory_id desc\G
    *** 1. row ***

           id: 1

    select_type: SIMPLE

        table: rental

    partitions: NULL

         type: ref

    possible_keys: rental_date

          key: rental_date
  key_len: 5
      ref: const
     rows: 1
 filtered: 100.00
    Extra: Using where

    1 row in set, 1 warning (0.00 sec)

    –下面的查詢不會利用索引
    explain select rental_id,staff_id from rental where rental_date>’2005-05-25’ order by rental_date,inventory_id\G
    *** 1. row ***

           id: 1

    select_type: SIMPLE

        table: rental

    partitions: NULL

         type: ALL

    possible_keys: rental_date

          key: NULL
  key_len: NULL
      ref: NULL
     rows: 16005
 filtered: 50.00
    Extra: Using where; Using filesort

    –該查詢使用了兩中不同的排序方向，但是索引列都是正序排序的
    explain select rental_id,staff_id from rental where rental_date>’2005-05-25’ order by inventory_id desc,customer_id asc\G
    *** 1. row ***

           id: 1

    select_type: SIMPLE

        table: rental

    partitions: NULL

         type: ALL

    possible_keys: rental_date

          key: NULL
  key_len: NULL
      ref: NULL
     rows: 16005
 filtered: 50.00
    Extra: Using where; Using filesort

    1 row in set, 1 warning (0.00 sec)

    –該查詢中引用了一個不再索引中的列
    explain select rental_id,staff_id from rental where rental_date>’2005-05-25’ order by inventory_id,staff_id\G
    *** 1. row ***

           id: 1

    select_type: SIMPLE

        table: rental

    partitions: NULL

         type: ALL

    possible_keys: rental_date

          key: NULL
  key_len: NULL
      ref: NULL
     rows: 16005
 filtered: 50.00
    Extra: Using where; Using filesort

    1 row in set, 1 warning (0.00 sec)

  5. union all,in,or都能夠使用索引，但是推薦使用in

    explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2; explain select * from actor where actor_id in (1,2); explain select * from actor where actor_id = 1 or actor_id =2;

  6. 範圍列可以用到索引

    範圍條件是：<、>

    範圍列可以用到索引，但是範圍列後面的列無法用到索引，索引最多用於一個範圍列

  7. 強制型別轉換會全表掃描

    create table user(id int,name varchar(10),phone varchar(11)); alter table user add index idx_1(phone); explain select * from user where phone=13800001234; explain select * from user where phone='13800001234';

  8. 更新十分頻繁，資料區分度不高的欄位上不宜建立索引

    更新會變更B+樹，更新頻繁的欄位建議索引會大大降低資料庫效能

    類似於性別這類區分不大的屬性，建立索引是沒有意義的，不能有效的過濾資料，

    一般區分度在80%以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(*) 來計算

  9. 建立索引的列，不允許為null，可能會得到不符合預期的結果

  10. 當需要進行表連線的時候，最好不要超過三張表，因為需要join的欄位，資料型別必須一致

  11. 能使用limit的時候儘量使用limit

  12. 單表索引建議控制在5個以內

  13. 單索引欄位數不允許超過5個（組合索引）

  14. 建立索引的時候應該避免以下錯誤概念

    索引越多越好
    過早優化，在不瞭解系統的情況下進行優化(寫時儘量rang級別,上線之後再優化)

1、show status like ‘Handler_read%’;

mysql> show status like 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 45 | +-----------------------+-------+ 7 rows in set (0.02 sec)

2、引數解釋

Handler_read_first：讀取索引第一個條目的次數

Handler_read_key：通過index獲取資料的次數

Handler_read_last：讀取索引最後一個條目的次數

Handler_read_next：通過索引讀取下一條資料的次數

Handler_read_prev：通過索引讀取上一條資料的次數

Handler_read_rnd：從固定位置讀取資料的次數

Handler_read_rnd_next：從資料節點讀取下一條資料的次數

1、預先準備好資料

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS itdragon_order_list;
CREATE TABLE itdragon_order_list (
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵id，預設自增長’,
transaction_id varchar(150) DEFAULT NULL COMMENT ‘交易號’,
gross double DEFAULT NULL COMMENT ‘毛收入(RMB)’,
net double DEFAULT NULL COMMENT ‘淨收入(RMB)’,
stock_id int(11) DEFAULT NULL COMMENT ‘發貨倉庫’,
order_status int(11) DEFAULT NULL COMMENT ‘訂單狀態’,
descript varchar(255) DEFAULT NULL COMMENT ‘客服備註’,
finance_descript varchar(255) DEFAULT NULL COMMENT ‘財務備註’,
create_type varchar(100) DEFAULT NULL COMMENT ‘建立型別’,
order_level int(11) DEFAULT NULL COMMENT ‘訂單級別’,
input_user varchar(20) DEFAULT NULL COMMENT ‘錄入人’,
input_date varchar(20) DEFAULT NULL COMMENT ‘錄入時間’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

INSERT INTO itdragon_order_list VALUES (‘10000’, ‘81X97310V32236260E’, ‘6.6’, ‘6.13’, ‘1’, ‘10’, ‘ok’, ‘ok’, ‘auto’, ‘1’, ‘itdragon’, ‘2017-08-28 17:01:49’);
INSERT INTO itdragon_order_list VALUES (‘10001’, ‘61525478BB371361Q’, ‘18.88’, ‘18.79’, ‘1’, ‘10’, ‘ok’, ‘ok’, ‘auto’, ‘1’, ‘itdragon’, ‘2017-08-18 17:01:50’);
INSERT INTO itdragon_order_list VALUES (‘10002’, ‘5RT64180WE555861V’, ‘20.18’, ‘20.17’, ‘1’, ‘10’, ‘ok’, ‘ok’, ‘auto’, ‘1’, ‘itdragon’, ‘2017-09-08 17:01:49’);

逐步開始進行優化：

2、第一個案例：

select * from itdragon_order_list where transaction_id = “81X97310V32236260E”;
–通過檢視執行計劃發現type=all,需要進行全表掃描
explain select * from itdragon_order_list where transaction_id = “81X97310V32236260E”;

–優化一、為transaction_id建立唯一索引
create unique index idx_order_transaID on itdragon_order_list (transaction_id);
–當建立索引之後，唯一索引對應的type是const，通過索引一次就可以找到結果，普通索引對應的type是ref，表示非唯一性索引賽秒，找到值還要進行掃描，直到將索引檔案掃描完為止，顯而易見，const的效能要高於ref
explain select * from itdragon_order_list where transaction_id = “81X97310V32236260E”;

–優化二、使用覆蓋索引，查詢的結果變成 transaction_id,當extra出現using index,表示使用了覆蓋索引
explain select transaction_id from itdragon_order_list where transaction_id = “81X97310V32236260E”;

select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

通過檢視執行計劃發現type=all,需要進行全表掃描:

explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

優化一、為transaction_id建立唯一索引

create unique index idx_order_transaID on itdragon_order_list (transaction_id);

​ 當建立索引之後，唯一索引對應的type是const，通過索引一次就可以找到結果，普通索引對應的type是ref，表示非唯一性索引賽秒，找到值還要進行掃描，直到將索引檔案掃描完為止，顯而易見，const的效能要高於ref

優化二、使用覆蓋索引，查詢的結果變成 transaction_id,當extra出現using index,表示使用了覆蓋索引

explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

3、第二個案例

–建立複合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

--建立索引之後發現跟沒有建立索引一樣，都是全表掃描，都是檔案排序 explain select * from itdragon_order_list order by order_level,input_date;

--可以使用force index強制指定索引 explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;

--其實給訂單排序意義不大，給訂單級別新增索引意義也不大，因此可以先確定order_level的值，然後再給input_date排序 explain select * from itdragon_order_list where order_level=3 order by input_date;

來源：www.cnblogs.com/Courage129/p/14194...

本作品採用《CC 協議》，轉載必須註明作者和本文連結
PHPer技術棧

相關文章