MySQL優化（3）：索引

来源:https://www.cnblogs.com/xuyiqing/archive/2020/03/11/12459748.html
-Advertisement-
Play Games

MySQL優化中，最重要的優化手段就是索引，也是最常用的優化手段 索引簡介： 索引：關鍵字與數據位置之間的映射關係 關鍵字：從數據中提取，用於標識，檢索數據的特定內容 目的：加快檢索 索引檢索為什麼快： （1）關鍵字相對於數據本身，量較小 （2）關鍵字都是排序好的 MySQL中索引的類型： 普通索引 ...


MySQL優化中，最重要的優化手段就是索引，也是最常用的優化手段

 

索引簡介：

索引：關鍵字與數據位置之間的映射關係

關鍵字：從數據中提取，用於標識，檢索數據的特定內容

目的：加快檢索

 

索引檢索為什麼快：

（1）關鍵字相對於數據本身，量較小

（2）關鍵字都是排序好的

 

MySQL中索引的類型：

普通索引，唯一索引，主鍵索引，全文索引

不同的類型只是對關鍵字的限制不同

普通索引：多索引關鍵字沒限制，有長度限制

唯一索引：要求記錄提供的關鍵字不能重覆

主鍵索引：要求關鍵字不能重覆而且不能為NULL

全文索引：不支持中文，後續細講

 

索引的語法：

查看索引：

SHOW CREATE TABLE [table-name];

 比如查到PRIMARY KEY(‘id’)，就是一個主鍵索引

 

創建索引：需要修改表結構和創建表時候完成，基於不同的類型，方式也不同

同時創建四個索引，由於使用到了全文索引，這裡使用MYISAM引擎

CREATE TABLE USER(
ID INT AUTO_INCREMENT PRIMARY KEY,
FIRST_NAME VARCHAR(16),
LAST_NAME VARCHAR(16),
SN VARCHAR(16),
INFORMATION TEXT,
KEY(FIRST_NAME,LAST_NAME),
UNIQUE KEY(SN),
FULLTEXT KEY(INFORMATION)
)ENGINE=MYISAM;

索引可以命名，比如KEY NAME (FIRST_NAME,LAST_NAME)

這句話創建了一個基於FIRST_NAME和LAST_NAME的複合普通索引

UNIQUE KEY(SN) 創建了基於SN的唯一索引，預設以欄位名命名索引

最後一個全文索引很雞肋，基本不會用

 

在修改表結構的時候創建索引：

ALTER TABLE USER(
ADD KEY(FIRST_NAME,LAST_NAME),
ADD UNIQUE KEY(SN),
ADD FULLTEXT KEY(INFORMATION)
)

 

刪除索引：

ALTER TABLE [table-name] DROP PRIMARY KEY;
ALTER TABLE [table-name] DROP KEY [key-name];

一般不簡易刪除主鍵索引，記錄是按照主鍵來排序的，設計主鍵要註意一定與業務邏輯無關

 

執行計劃：

執行計劃：當MySQL執行SQL語句時，會分析、優化、形成執行計劃後按照執行計劃來執行

在執行計劃中可以清楚的看到當前的查詢是否需要用到索引：

EXPLAIN SELECT * FROM [table-name] WHERE ID<20;

結果中有一樣：KEY:PRIMARY，代表該查詢語句會用到主鍵索引

 

索引使用的場景：

如果兩張表，學生和班級表，多對一的關係，導入較多的記錄來測試

（1）WHERE查詢

EXPLAIN SELECT * FROM STUDENT WHERE ID=123456

和上面的例子一樣，可以看到使用到了主鍵索引

EXPLAIN SELECT * FROM STUDENT WHERE USERNAME="XXX"

這句話執行後會發現：KEY:NULL，沒有索引

我們給它加上索引：（這裡的INDEX和上文的KEY都可以）

ALTER TABLE STUDENT ADD INDEX (USERNAME);

查看執行計劃後可以發現：KEY:USERNAME，說明使用到了新建的索引

 

（2）ORDER BY排序

我們有可能會遇到以下的情況：

SELECT * FROM STUDENT ORDER BY USERNAME;

查看執行計劃後可以發現沒有使用到任何索引，並且看到了Extra:Using Filesort，使用到了外部文件排序，性能更低，需要先將數據讀取到記憶體，分段讀取合併排序

提高效率的方式是增加索引：

ALTER TABLE STUDENT ADD INDEX (USERNAME);

現在查看執行計劃後，可以發現使用到了USERNAME索引，並且沒有使用外部文件排序，性能會有明顯提升

 

（3）JOIN 連接

使用到這條語句：

SELECT C.* COUNT(S.ID) FROM CLASS C JOIN STUDENT S ON C.ID=S.CLASS_ID GROUP BY C.ID;

當數據量巨大的時候，這句話要執行5S以上

查看執行計劃後，發現其中一張表沒有索引，且使用到了外部文件排序

解決：

ALTER TABLE STUDENT ADD INDEX (CLASS_ID);

執行後發現速度明顯提升，並且兩張表都使用到了索引，沒有外部文件排序

 

（4）索引覆蓋

前三條很重要，是必須做的優化，這條只是一個現象

比如我們使用這條語句：建立一個複合索引

ALTER TABLE STUDENT ADD INDEX (FIRSTNAME,LASTNAME);

然後再執行：這句話沒有使用到以上三種情況

SELECT FIRSTNAME,LASTNAME FROM STUDENT;

但是查看計劃後，發現還是使用到了索引，並且Extra:Using index，說明這句話只使用了索引來完成

如果執行這句話

SELECT FIRSTNAME,LASTNAME,USER FROM STUDENT;

再查看計劃後發現沒有使用到了索引，並且進行了全表掃描

兩次的差異只是多了一個USER欄位，而複合索引沒有包含該欄位

 

總結：MySQL的查詢優先使用了索引，由於索引覆蓋，建議SELECT後面只寫有必要的欄位，被覆蓋的可能性就會提升，儘可能地優化

 

語法註意細節：

（1）欄位需要獨立出現

SELECT * FROM STUDENT WHERE ID+1=20;

這句話是能執行成功的，ID是主鍵，查看計劃後卻沒有使用到主鍵索引

欄位沒有獨立出現，不能觸發該欄位上的索引，避免這種情況

（2）LIKE查詢不能以通配符開頭

SELECT * FROM STUDENT WHERE USERNAME LIKE '%A%';

這句話無法使用到索引，如果是以下的情況，那麼會用到索引

SELECT * FROM STUDENT WHERE USERNAME LIKE 'A%';

字元串比較中，不能使用包含的邏輯，比如查詢包含Java的字元串，不能寫'%Java%'，效率過低

解決辦法：全文索引，但是MySQL全文索引很雞肋，應該使用第三方的比如ES，Solr

（3）複合索引的右側欄位不能獨立使用索引

已有INDEX NAME(FIRSTNAME,LASTNAME);

使用語句，這句話使用到了索引

SELECT * FROM STUDENT WHERE FIRSTNAME='XXX';

而下麵這句話沒有使用到索引

SELECT * FROM STUDENT WHERE LASTNAME='XXX';

原因：複合索引是按照左側欄位排序的，如果左側欄位相同再用右側欄位排序，總體上來看，右側欄位是未排序的

既然這樣為什麼還要建複合索引呢？以下這種情況

SELECT * FROM STUDENT WHERE FIRSTNAME LIKE 'XX%' AND LASTNAME LIKE 'XX%';

這句話如果建立兩個索引，那麼計算兩個索引的交集會更慢，所以需要複合索引

如果遇到上面的情況，再給LASTNAME建立一個索引即可

（4）早期版本NULL值無法使用索引

SELECT * FROM STUDENT WHERE FIRSTNAME=NULL;

新版本無需關心這一條

（5）OR語法保證兩邊的條件都有索引可用

SELECT * FROM STUDENT WHERE FIRSTNAME LIKE 'XX%' OR USER LIKE 'XX%';

如果USER沒有索引，那麼還是會全表查詢

（6）狀態值不容易使用到索引

GENDER 0,1,2表示男，女，未知

即使在欄位上增加了索引，通常也不會起作用

SELECT * FROM STUDENT WHERE GENDER IN (0,1);

 原因：狀態值往往導致一個狀態值匹配大量記錄，查詢大量記錄的時候，MYSQL認為使用索引開銷比全表掃描都要大

 

如何創建索引：

（1）WHERE,ORDER BY,JOIN欄位上建立索引

（2）組合索引的建立：基於業務邏輯

（3）如果條件經常出現在一起，多欄位索引可以升級為複合索引

（4）如果通過增加個別欄位，就可以出現索引覆蓋，那麼增加個別欄位

（5）不會用到的索引應該刪掉

（6）常規情況下我們建立的資料庫系統本身性能就不差了

（7）有些欄位是否只使用首碼就能完成，使用首碼索引

 

首碼索引：INDEX(FIELD(10))

使用欄位field的前10個字元建立索引，預設是使用欄位全部內容建立索引

使用：GIT的COMMIT_ID；密碼欄位

 

索引的存儲結構：

BTREE索引，HASH索引，聚簇索引

以上概念指的是索引的存儲結構，數據結構上的概念，實際使用無需關心，瞭解即可

 

BTREE索引：

索引存儲在磁碟上所用的基礎的通用的存儲結構

特征：磁碟上的數據結構，不是二叉樹，一定要一個中文，那就是多路平衡查找樹

特點：一個BTREE節點，存儲多個索引關鍵字，多少由節點大小和關鍵字來確定的，

節點大小是固定的，由電腦文件系統來確定，一次性磁碟讀取記憶體量，就是一個節點大小

由於一個節點的大小是固定的，一個節點無法容納大量關鍵字，所以分散在多個節點來存儲關鍵字

這時候如何進行排序呢？通過上層節點的子節點指針指向下層節點，用來關聯所有的節點，子節點指針位於關鍵字之間

 

例如每個節點存儲1000個關鍵字，深度為2的兩層BTREE大概可以存儲1000000（1000*1000）個關鍵字

查找一個關鍵字，需要讀取幾個節點的內容呢？從根開始，確定下級節點，僅僅兩次的磁碟讀取就可以做到

Btree的意義在於可以遍歷大量關鍵字，減少磁碟讀取量的開銷

 

聚簇索引：

關鍵字和記錄在一起進行存儲

是升級後的Btree，數據結構上的B+Tree

MySQL中只有Innodb的主鍵索引是聚簇結構

 

HASH索引：

當索引被載入到記憶體後採用的存儲結構，採用哈希結構存儲了，類似Java的Map，Key-Value


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

-Advertisement-
Play Games
更多相關文章
  • plsql項目案例
    第六章 項目案例1 需求： 1. 建立一個銀行賬戶的表bank_account，具備以下欄位和約束 2. 完成該表基本CRUD 3. 模擬轉賬流程 4. 模擬異常之後的業務回滾 開發文檔： 1. 按需求建表 2. 遵循編碼規範，創建項目，包結構 3. 書寫該表的CRUD JDBCDao 4. 模擬轉 ...
  • plsql欄位約束
    第五章 欄位約束 初識約束 約束是資料庫用來確保數據滿足業務規則的手段，對數據做的條件限制。 約束的類型 1. 主鍵約束（PRIMARY KEY） 2. 唯一性約束（UNIQUE) 3. 非空約束（NOT NULL) 4. 檢查約束（CHECK) 5. 外鍵約束（FOREIGN KEY) 主鍵約束（ ...
  • Oracle索引和事務
    第四章索引和事務 1. 什麼是索引？有什麼用？ 1）索引是資料庫對象之一，用於加快數據的檢索，類似於書籍的目錄。在資料庫中索引可以減少資料庫程式查詢結果時需要讀取的數據量，類似於在書籍中我們利用索引可以不用翻閱整本書即可找到想要的信息。 2）索引是建立在表上的可選對象；索引的關鍵在於通過一組排序後的 ...
  • 組函數和表關係
    第三章組函數和表關係 字元串函數 -- concat 連接兩個字元串 select concat('abc','ABC') from dual; select 'abc'||'ABC' from dual; select concat(first_name,'_')||last_name con,t ...
  • Oracle體系架構和導入導出
    第二章 Oracle體系架構和導入/導出 Oracle體繫結構 服務名，實例名，orcl n Oracle通過資料庫實例來載入和管理資料庫，每個運行的Oracle資料庫都對應一個Oracle實例(Instance)，也可以稱為常式。 n 當資料庫伺服器上的一個資料庫啟動時，Oracle將為其分配一塊 ...
  • 初步體驗Oracle
    Oracle 與Mysql 對比： MySQL: 免費；小型企業；僅是資料庫；輕 Oracle：收費， 中大型企業；資料庫服務（許可權，併發，事務，一致性）；更適合集群；重 共同點：都屬於關係型資料庫 RDBMS 非關係型資料庫NoSQL（Not Only SQL )： Redis，Mongodb，S ...
  • MySQL優化（4）：查詢緩存
    查詢緩存： MySQL提供的數據緩存QueryCache，用於緩存SELECT查詢的結果 預設不開啟，需要在配置文件中開啟緩存（my.ini/my.cnf） 在[mysqld]段中，修改query_cache_type完成配置： 0：關閉 1：開啟，但是預設緩存，需要增加sql-no-cache提示 ...
  • （2）SQL Server導出功能
    1.前言 有時候，我們需要把A庫A1表某一部分或全部數據導出到B庫B1表中，如果系統運維工程師沒打通兩個庫鏈接，我們執行T-SQL是處理數據導入時會發生如下錯誤： 這時候SQL Server導出功能很好彌補這一點，而該章節重點介紹該功能。 2.操作 資料庫版本：Microsoft SQL Serve ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類