MySQL優化之避免索引失效的方法

来源:https://www.cnblogs.com/chenyc2020/archive/2020/03/30/12579381.html
-Advertisement-
Play Games

在上一篇文章中，通過分析執行計劃的欄位說明，大體說了一下索引優化過程中的一些註意點，那麼如何才能避免索引失效呢？本篇文章將來討論這個問題。 避免索引失效的常見方法 1.對於複合索引的使用，應按照索引建立的順序使用，儘量不要跨列（最佳左首碼原則） 為了說明問題，我們仍然使用上一篇文章中的test01表 ...


在上一篇文章中，通過分析執行計劃的欄位說明，大體說了一下索引優化過程中的一些註意點，那麼如何才能避免索引失效呢？本篇文章將來討論這個問題。

避免索引失效的常見方法

1.對於複合索引的使用，應按照索引建立的順序使用，儘量不要跨列（最佳左首碼原則）

為了說明問題，我們仍然使用上一篇文章中的test01表，其表結構如下所示：

mysql> desc test01;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(4)      | YES  | MUL | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| passwd | char(20)    | YES  |     | NULL    |       |
| inf    | char(50)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show index from test01;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
 Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
---------+---------------+
| test01 |          1 | t_idx1   |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |
         |               |
| test01 |          1 | t_idx1   |            2 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |
         |               |
| test01 |          1 | t_idx1   |            3 | passwd      | A         |           0 |     NULL | NULL   | YES  | BTREE      |
         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
---------+---------------+
3 rows in set (0.01 sec)

如果常規的SQL寫法，三個索引全覆蓋，沒有任何問題：

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 129     | const,const,const |    1 |   100.00 | NULL
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
1 row in set, 1 warning (0.00 sec)

但是如果跨列使用，如下所示：

mysql> explain select * from test01 where id = 1 and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

通過觀察，發現key_len已經從129變成5了，說明只有id使用到了索引，而passwd並沒有用到索引。
接下來我們看一種更糟糕的情況：

mysql> explain select * from test01 where id = 1 order by passwd;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
--------------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
                    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
--------------------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion; Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
--------------------+
1 row in set, 1 warning (0.00 sec)

上述語句中，Extra欄位中出現了Using filesort，之前說過，這是非常差的一種寫法，如果我們做一下改動：

mysql> explain select * from test01 where id = 1 order by name,passwd;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

與上面的SQL相比較，只是在order by 里，加上了name，Using filesort就去掉了，所以這裡的不能跨列，指的是where 和order by之間不能跨列，否則會出現很糟糕的情況。

2.不要在索引上進行任何函數操作

包括但不限於sum、trim、甚至對欄位進行加減乘除計算。

mysql> explain select id from test01 where id = 1 and trim(name) ='zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

因為在name欄位上，進行了trim函數操作，所以name失效，連帶著後面的passwd也失效了，因為key_len = 5。
所以此處透露出兩個信息點：

  • 在索引欄位上進行函數操作，會導致索引失效；
  • 複合索引如果前面的欄位失效，其後面的所有欄位索引都會失效。

3.複合索引不要使用is null或is not null，否則其自身和其後面的索引全部失效。

仍然是看一個例子：

mysql> explain select id from test01 where id = 1 and name is not null and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

因為name使用了is not null，所以導致name和passwd都失效了，從key_len = 5可以看出。

4.like儘量不要在前面加%

這一點之前在說明range級別的時候有提到過，此處再次說明一下。

mysql> explain select * from test01 where id = 1 and name like '%a%' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

在上例中，name欄位使用了like '%a%'，所以導致name和passwd都失效，只有id使用到了索引。
為了對比，如果把前面的%去掉，看看什麼結果：

mysql> explain select * from test01 where id = 1 and name like 'a%' and passwd = '123';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
    |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | range | t_idx1        | t_idx1 | 129     | NULL |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

可以看到，此時key_len = 129，說明三個欄位都用到了。

5.儘量不要使用類型轉換，包括顯式的和隱式的

正常的索引應該是這樣：

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 129     | const,const,const |    1 |   100.00 | NULL
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
1 row in set, 1 warning (0.00 sec)

但是如果改一下，把passwd = '123'改成passwd = 123，讓MySQL自己去做類型轉換，將123轉換成'123'，那結果是怎樣的呢？

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = 123;
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
----------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra
          |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
----------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 68      | const,const |    1 |   100.00 | Using index
condition |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
----------+
1 row in set, 2 warnings (0.00 sec)

發現key_len = 68，最後一個passwd失效了。

6.儘量不要使用or

or會使or前面的和後面的索引同時失效，這點比較變態，所以要特別註意：

mysql> explain select * from test01 where id = 1 or name = 'zz';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test01 | NULL       | ALL  | t_idx1        | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到因為使用了or，導致索引為NULL，type級別為ALL。

如果一定要使用or，應該怎樣補救呢？
補救的辦法是儘量用到索引覆蓋。比如我們把原來SQL中的select * 中的 * 號替換成具體的欄位，這些欄位能夠覆蓋索引，那麼對索引優化也有一定的提升：

mysql> explain select id, name, passwd from test01 where id = 1 or name = 'zz';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | index | t_idx1        | t_idx1 | 129     | NULL |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

從上例中可以看到，where條件沒做任何改變，但是type級別已經提升到了index，也是用到了索引。

7.in經常會使索引失效，應該慎用

mysql> explain select id, name, passwd from test01 where id = 1 and name in ('zz', 'aa');
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    2 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

從上例中，不難看出，key_len = 5，所以name索引失效了，原因就是name使用了in。
為什麼說經常會使索引失效呢？因為in也不一定總使索引失效，如下麵的例子：

mysql> explain select id, name, passwd from test01 where id in (1,2,3) and name = 'zz';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | index | t_idx1        | t_idx1 | 129     | NULL |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

可以看到此時key_len = 129，說明用到了全部索引。以上情況之所以出現，其實還是索引失效了，但是雖然id索引失效，但是name索引並沒有失效，所以上面的句子等價於：select id, name, passwd from test01 where name = 'zz';。這與之前說的複合索引只要前面的失效，後面都失效並不太一致，所以對於in，應該謹慎使用。

對於關聯表查詢的情況，應該遵循“小表驅動大表”的原則

總而言之，就是左連接給左表建索引，右連接給右表建索引，內連接的話給數據量小的表建索引。

還需要說明一點的是，索引並不是越多越好

因為索引的數據結構是B樹，畢竟要占記憶體空間，所以如果索引越多，索引越大，對硬碟空間的消耗其實是巨大的，而且如果表結構需要調整，意味著索引也要同步做調整，否則會導致不可預計的問題出現。
因此，在實際開發中，對於創建索引，應充分考慮到具體的業務情況，根據業務實現來創建索引，對於有些比較特殊的複雜SQL，建議在代碼里進行一定的邏輯處理後再進行常規的索引查詢。
舉個例子，比如test01表中，需要判斷inf欄位是否包含“上海”欄位，如果在SQL里實現，則必然是如下的邏輯：

select id,name,passwd,inf from test01 where id = 1 and name = 'zz' and passwd = '123' and inf like '%上海%'

這條sql就比較恐怖了，且不說inf本來不是索引，而且有like '%上海%'這種糟糕的寫法，所以我們完全可以使用下麵的方法代替。
先使用下麵的SQL查出所有欄位：

select id,name,passwd,inf from test01 where id = 1 and name = 'zz' and passwd = '123'

然後在代碼里判斷inf欄位是否包含上海欄位，如C語言實現如下：

if (strstr(inf, "上海") != NULL)
{
	//do something
}

這樣一來，雖然只是多了一步簡單的邏輯判斷，但是對於SQL優化的幫助其實是巨大的。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • mysql學習總結（一）
    作為一名小白，今天開始上傳自己的學習總結。 ...
  • Windows 下安裝多個Redis 實例
    1.在Redis 目錄下複製redis.windows-service.conf文件，建議命名規則redis.windows-service-port.conf，我們以6380埠為例。 2.打開redis.windows-service-6380配置文件。 修改埠port 修改資料庫名稱dbfi ...
  • SQL多條件模糊查詢、IN 、自定義函數筆記（一）
    在工作中遇到的一些關於Sql查詢的問題整理記錄，實現環境 SQLService 2014 一、對同一個欄位，多個關鍵詞的查詢幾種實現方式 基本語法： SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern 案例表（Tb ...
  • Mysql性能優化：什麼是索引下推？
    導讀 本文章始發於本人公眾號：碼猿技術專欄，原創不易，謝謝關註推薦。 索引下推（index condition pushdown ）簡稱ICP，在Mysql5.6的版本上推出，用於優化查詢。 在不使用ICP的情況下，在使用非主鍵索引（又叫普通索引或者二級索引）進行查詢時，存儲引擎通過索引檢索到數據， ...
  • java結合email實現自動推送
    1、編寫方法獲取最新標題的最新的標題 2、使用獲取額標題進行模糊查詢，查詢出郵箱地址，標題名稱 3、使用email發送郵件 ...
  • Cassandra簡單介紹和二進位安裝
    Cassandra簡介： Apache Cassandra最初由Facebook開發，用於儲存收件箱等簡單格式數據，集GoogleBigTable的數據模型與Amazon Dynamo的完全分散式的架構於一身Facebook於2008將 Cassandra 開源， Apache Cassandra是 ...
  • [MySQL] 有沒有解決幻讀問題
    預設隔離級別下 , mysql沒有解決幻讀問題 , 需要應用代碼裡加一個鎖來解決 幻讀問題是啥? 預設的隔離級別是可重覆讀 REPEATABLE-READ , 在這個模式下出現幻讀的例子一般是這兩種情況: 事務1和事務2同時 , 事務1讀數據 , 事務2插入數據提交 , 事務1插入同樣的數據時報錯說 ...
  • [MySQL] innoDB引擎的主鍵與聚簇索引
    mysql的innodb引擎本身存儲的形式就必須是聚簇索引的形式 , 在磁碟上樹狀存儲的 , 但是不一定是根據主鍵聚簇的 , 有三種情形: 1. 有主鍵的情況下 , 主鍵就是聚簇索引 2. 沒有主鍵的情況下 , 第一個非空null的唯一索引就是聚簇索引 3. 如果上面都沒有 , 那麼就是有一個隱藏的 ...
一周排行
    -Advertisement-
    Play Games
  • C#TMS系統學習（BaseCity頁面）
    C#TMS系統代碼-基礎頁面BaseCity學習 本人純新手，剛進公司跟領導報道，我說我是java全棧，他問我會不會C#，我說大學學過，他說這個TMS系統就給你來管了。外包已經把代碼給我了，這幾天先把增刪改查的代碼背一下，說不定後面就要趕鴨子上架了 Service頁面 //using => impo ...
  • C# - 委托與事件
    委托與事件 委托 委托的定義 委托是C#中的一種類型，用於存儲對方法的引用。它允許將方法作為參數傳遞給其他方法，實現回調、事件處理和動態調用等功能。通俗來講，就是委托包含方法的記憶體地址，方法匹配與委托相同的簽名，因此通過使用正確的參數類型來調用方法。 委托的特性 引用方法：委托允許存儲對方法的引用， ...
  • 解析 ABP vNext 依賴註入實現【屬性註入】的原理
    前言 這幾天閑來沒事看看ABP vNext的文檔和源碼，關於關於依賴註入(屬性註入)這塊兒產生了興趣。 我們都知道。Volo.ABP 依賴註入容器使用了第三方組件Autofac實現的。有三種註入方式，構造函數註入和方法註入和屬性註入。 ABP的屬性註入原則參考如下： 這時候我就開始疑惑了，因為我知道 ...
  • C#TMS系統學習（ShippingNotice頁面）
    C#TMS系統代碼-業務頁面ShippingNotice學習 學一個業務頁面，ok，領導開完會就被裁掉了，很突然啊，他收拾東西的時候我還以為他要旅游提前請假了，還在尋思為什麼回家連自己買的幾箱飲料都要叫跑腿帶走，怕被偷嗎？還好我在他開會之前拿了兩瓶芬達 感覺感覺前面的BaseCity差不太多，這邊的 ...
  • C#動態查詢：巧用Expression組合多條件表達式
    概述：在C#中，通過`Expression`類、`AndAlso`和`OrElse`方法可組合兩個`Expression<Func<T, bool>>`，實現多條件動態查詢。通過創建表達式樹，可輕鬆構建複雜的查詢條件。 在C#中，可以使用AndAlso和OrElse方法組合兩個Expression< ...
  • 在Biwen.QuickApi中整合一個極簡的發佈訂閱(事件匯流排)
    閑來無聊在我的Biwen.QuickApi中實現一下極簡的事件匯流排,其實代碼還是蠻簡單的,對於初學者可能有些幫助 就貼出來,有什麼不足的地方也歡迎板磚交流~ 首先定義一個事件約定的空介面 public interface IEvent{} 然後定義事件訂閱者介面 public interface I ...
  • WPF 穩定的全屏化視窗方法
    1. 案例 成某三甲醫預約系統， 該項目在2024年初進行上線測試，在正常運行了兩天後，業務系統報錯：The connection pool has been exhausted, either raise MaxPoolSize (currently 800) or Timeout (curren ...
  • 在 WPF 中集成 ASP.NET Core 和 WebView2 用於集成 SPA 應用
    背景 我們有些工具在 Web 版中已經有了很好的實踐，而在 WPF 中重新開發也是一種費時費力的操作，那麼直接集成則是最省事省力的方法了。 思路解釋 為什麼要使用 WPF？莫問為什麼，老 C# 開發的堅持，另外因為 Windows 上已經裝了 Webview2/edge 整體打包比 electron ...
  • C#事件
    EDP是一套集組織架構，許可權框架【功能許可權，操作許可權，數據訪問許可權，WebApi許可權】，自動化日誌，動態Interface，WebApi管理等基礎功能於一體的，基於.net的企業應用開發框架。通過友好的編碼方式實現數據行、列許可權的管控。 ...
  • .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 發佈到 Win7+
    .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
所有分類