MySQL優化之索引

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

SQL為什麼需要優化？ 對於初學者來說，能夠寫出實現功能的SQL語句而不出錯，查詢出所需要的結果，就已經能夠滿足日常使用了。但在某些場景，對性能的要求比較高，因此，要求SQL的執行響應速度快，就需要對SQL進行一定程度的優化。 在實際應用場景中，MySQL經常會存在諸如性能低、執行時間過長、等待時間 ...


SQL為什麼需要優化？

對於初學者來說，能夠寫出實現功能的SQL語句而不出錯，查詢出所需要的結果，就已經能夠滿足日常使用了。但在某些場景，對性能的要求比較高，因此，要求SQL的執行響應速度快，就需要對SQL進行一定程度的優化。
在實際應用場景中，MySQL經常會存在諸如性能低、執行時間過長、等待時間過長、SQL語句欠佳（尤其是連接查詢）、索引失效、伺服器參數設置不合理等問題，這時候就需要對SQL進行優化，從而達到我們所需要的的性能需求。

SQL解析過程

要對SQL進行優化，首先需要知道SQL的解析過程是什麼樣子的。在此之前，我們要明確SQL編寫過程和解析過程的區別。

SQL編寫過程

select [distinct] ... from ... join ... on ... where ... group by ... having ... order by ... limit ...;

SQL解析過程

from ... on ... join ... where ... group by ... having ... select [distinct] ... order by ... limit ...;

以上語法中sql關鍵字的含義，不是本文的重點，網路上有很多教程，此處不再說明，我們只需要知道，SQL的編寫過程和實際解析過程並不是一致的。這點在後續的相關優化中將會進一步說明。

索引

索引相當於字典的目錄，其目的是幫助在MySQL中更快的查詢到所需要的數據。其本質是一種BTREE的數據結構。
所以可以得出一個結論：索引是一種數據結構。 如果您對數據結構有所瞭解，可以更明白的講，索引是一種叫樹的數據結構。樹有很多種，如二叉樹，哈希樹等。索引是B樹（和二叉樹比較類似）。

舉個例子說明：
假設我們有一張表student，其結構及其中的數據如下：

id name score
1 zs 75
2 ls 82
3 ww 62
4 ll 88
5 wq 77
6 wb 53

其中，score列是索引。那麼，該索引的大致結構是如下圖所示的樣子：

因此，如果有這樣一條SQL:

select score from student where score = 77;

如果沒有索引，那麼需要全表掃描，從第一條數據開始，需要到第5次才能查找到我們所需要的數據；而如果有了索引，則只需要3次就能查找到(75->62->77)，由此可見，索引確實能夠提升查詢效率，尤其是當表中的數據量特別大，達到了百萬級別，甚至千萬級別的時候，索引的優勢就更加明顯。

Btree除了常見的二叉樹，還有三叉樹，三叉樹的結構如下所示：

Btree一般指的都是B+樹。實際上，索引的數據全部存儲在葉節點中，這也就意味著，對於Btree中，查詢任意數據的次數都是n次（n為樹的深度）。

由於客戶端和伺服器之間主要是通過IO，所以索引會大大降低IO的使用率，並且能一定程度的降低CPU的使用率。（比如SQL語句中有order by，由於索引的數據結構本身就是排好序的，所以直接省去了這一步，從而降低CPU使用率）。

索引固然有諸多好處，但也有一定的弊端：

  • 索引本身很大，因為它本身也是數據結構，存儲時必然要占空間；
  • 索引不是所有情況均適用
    • 少量數據（數據量少，比如只有一條數據，沒有必要通過索引查詢）
    • 頻繁更新的欄位（因為索引是B樹，頻繁更新的欄位除了要更新該欄位本身，還得更新索引的值，甚至會引起索引結構的變動）
    • 很少使用的列（很少使用，意味著不經常查詢，設置索引意義不大）
  • 索引確實可以提高查詢的效率，但會降低增刪改的效率（因為對數據增刪改，同時會引起索引的變動，需要額外對索引進行增刪改。而實際應用中，因為查詢使用到的場景遠遠多於增刪改，所以索引還是有存在的必要的）。

索引分類：

  • 單值索引：單列的值，一張表可以有多個單值索引。
  • 唯一索引：不能重覆（unique index）
  • 複合索引：多個列構成的索引，相當於書的二級目錄。（不是100%多個索引同時用）
  • 主鍵索引：如果一個欄位設置為主鍵（primary key），則預設是主鍵索引，因此主鍵索引也不能重覆。

主鍵索引和唯一索引的區別是：主鍵索引列的值不能為null，唯一索引列的值可以為null。

索引常見操作

創建索引：

create 索引類型 索引名 on 表（欄位）
alter table 表明 add 索引類型 索引名(欄位)

刪除索引：

drop index 索引名 on 表名

查詢索引：

show index from 表名

以本文中的student表為例，加以說明：

mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
| score | double   | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

在student表上對name欄位創建單值索引stu_idx1：

mysql> create index stu_idx1 on student(name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

在student表上對id欄位創建唯一索引stu_idx2：

mysql> create unique index stu_idx2 on student(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

在student表上對name，score欄位創建複合索引stu_idx3:

mysql> create index stu_idx3 on student(name,score);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用alter的方式對student表的score欄位創建單值索引stu_idx4:

mysql> alter table student add index stu_idx4(score);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看創建的索引：

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
     |
| student |          0 | stu_idx2 |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
     |
| student |          1 | stu_idx1 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            2 | score       | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx4 |            1 | score       | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

從以上表格，可以讀出以下信息：

  • 第一條數據，索引名是PRIMARY,這條索引不是手動創建的，而是建表時因為指定了id為primary key，因此自動創建的主鍵索引；
  • 第11列Index_type可以看出索引的數據結構均為BTREE；
  • 第4、5條數據索引名都為stu_idx3,索引序號Seq_in_index分別為1和2，因此，這兩個是一對，代表是一個複合索引。
  • Non_unique值為0，代表是唯一索引（或主鍵索引），為1代表不是唯一索引，也就是說該索引列的值可以重覆。

假如要刪除索引stu_idx2和stu_idx4，則執行如下語句：

mysql> drop index stu_idx2 on student;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index stu_idx4 on student;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次查詢：

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
     |
| student |          1 | stu_idx1 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            1 | name        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
| student |          1 | stu_idx3 |            2 | score       | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
     |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

索引的一些註意事項

創建索引需要註意的事項：

  • 選擇表關聯（JOIN）的關聯條件列；
  • 數據查詢（WHERE）過濾條件列；
  • 重覆鍵值數少的列；
  • 複合索引應該將重覆鍵值數少的列放在首欄位；
  • 如果表的數據比較少（如少於1000行），應根據實際情況評估是否需要創建索引；
  • 複合索引欄位的個數不建議超過3個；
  • 不建議在大欄位（如char(100)等欄位類型）上創建索引；
  • 對於頻繁訪問的業務表，索引數量不建議超過5個；
  • 對於數據很少變化的靜態表、歷史表，索引數量不建議超過8個；

使用索引需要註意的事項：

  • 避免在索引上進行運算；
  • 避免使用 in 和 not in；
  • 儘量不適用like；
  • 避免在索引列上使用函數；
  • 避免在索引上使用 is null或 is not null；
  • 避免使用 !=, >, < 等符號；
  • 避免改變索引列的類型；
  • 避免使用having子句；
  • 對於複合索引，應按照索引中欄位的順序編製查詢條件 ；
    以上這些已經涉及到後面的索引優化範疇，這裡大概有個印象，在下一篇文章中會詳細講述。

唯一索引和主鍵的區別：

  • 一張表裡可以有多個唯一索引，但只能有一個主鍵；
  • 主鍵保證記錄唯一且非空(null)，唯一索引只能保證記錄唯一，可以為空(null)；
  • 主鍵一定是唯一索引，但唯一索引可以不是主鍵；
  • 主鍵可以被其他表引為外鍵，唯一索引不可以；
  • 主鍵是約束，不占空間，唯一索引是數據結構，是表的冗餘結構，占存儲空間，這是二者的本質區別。

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

-Advertisement-
Play Games
更多相關文章
  • 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. 如果上面都沒有 , 那麼就是有一個隱藏的 ...
  • MySQL優化之避免索引失效的方法
    在上一篇文章中，通過分析執行計劃的欄位說明，大體說了一下索引優化過程中的一些註意點，那麼如何才能避免索引失效呢？本篇文章將來討論這個問題。 避免索引失效的常見方法 1.對於複合索引的使用，應按照索引建立的順序使用，儘量不要跨列（最佳左首碼原則） 為了說明問題，我們仍然使用上一篇文章中的test01表 ...
  • MySQL優化之執行計劃
    前言 研究SQL性能問題，其實本質就是優化索引，而優化索引，一個非常重要的工具就是執行計劃(explain)，它可以模擬SQL優化器執行SQL語句，從而讓開發人員知道自己編寫的SQL的運行情況。 執行計劃語法 執行計劃的語法非常簡單，就是在要執行的SQL語句前加上 即可。 以我們在上一篇文章中創建的 ...
一周排行
    -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 ...
所有分類