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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類