當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql之索引的工作机制

發布時間：2024/9/20 数据库 23 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql之索引的工作机制小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

mysql之高性能索引

當db的量達到一定數量級之后，每次進行全表掃描效率就會很低，因此一個常見的方案是建立一些必要的索引作為優化手段，那么問題就來了：

那么什么是索引呢？
索引的實現原理是怎樣的？
我們通常說的聚集索引，非聚集索引的區別是什么？
如何創建和使用索引呢？

I. 索引介紹

MySQL官方對索引的定義為：索引是幫助MySQL高效獲取數據的數據結構。簡而言之,索引是數據結構

1. 幾種樹的結構

a. B+樹

單來說就是一種為磁盤或者其他存儲設備而設計的一種平衡二叉樹,在B+tree中所有記錄都按照key的大小存放在葉子結點上，各葉子結點直接用指針連接

b. 二叉樹

二叉樹的規則是父節點大于左孩子節點，小于右孩子節點

c. 平衡二叉樹

首先是一個二叉樹，但是要求任意一個節點的左右孩子節點的高度差不大于1

d. B樹

首先是一個平衡二叉樹，但是又要求每個葉子節點到根節點的距離相等

那么B樹和B+樹的區別是什么呢？

B+樹的葉子節點可以包含一個指針，指向另一個葉子節點
B+樹鍵值的拷貝存在非葉子節點；鍵值+記錄存儲在葉子節點

2. InnoDB引擎之B+樹

mysql的InnnoDB引擎采用的B+樹，只有葉子節點存儲對應的數據列，有以下好處

葉子結點通常包含較多的記錄，具有較高的扇出性(可理解為每個節點對應的下層節點較多)，因此樹的高度較低(3~4)，而樹的高度也決定了磁盤IO的次數，從而影響了數據庫的性能。一般情況下，IO次數與樹的高度是一致的
對于組合索引，B+tree索引是按照索引列名(從左到右的順序)進行順序排序的，因此可以將隨機IO轉換為順序IO提升IO效率;并且可以支持order by \group等排序需求;適合范圍查詢

3. hash索引

hash索引，相比較于B樹而言，不需要從根節點到葉子節點的遍歷，可以一次定位到位置，查詢效率更高，但缺點也很明顯

僅能滿足"=","IN"和"<=>"查詢，不能使用范圍查詢
- 因為是通過hash值進行計算，所以只能精確查詢，hash值是沒什么規律的，不能保證順序和原來一致，所以范圍查詢不行
無法進行排序
- 原因同上
不支持部分索引
- hash值的計算，是根據完整的幾個索引列計算，如果少了其中一個乃至幾個，這個hash值就沒法計算了
hash碰撞

4. 聚集索引與非聚集索引

a. 聚集索引

InnoDB的數據文件本身就是索引文件，B+Tree的葉子節點上的data就是數據本身，key為主鍵，非葉子節點存放<key,address>，address就是下一層的地址

聚簇索引的結構圖:

數據結構

b. 非聚集索引

非聚簇索引，葉子節點上的data是主鍵(即聚簇索引的主鍵，所以聚簇索引的key，不能過長)。為什么存放的主鍵，而不是記錄所在地址呢，理由相當簡單，因為記錄所在地址并不能保證一定不會變，但主鍵可以保證

非聚簇索引結構圖：

數據結構

從非聚集索引的結構上，可以看出這種場景下的定位流程：

先通過非聚集索引，定位到對應的葉子節點，找到對應的主鍵
根據上面找到的主鍵，在聚集索引中，定位到對應的葉子節點（獲取數據）

5. 索引的優點

避免全表掃描（當走不到索引時，就只能一個一個的去匹配；如果走索引，則可以根據B樹來定位）
使用索引可以幫助服務器避免排序或者臨時表（葉子節點上的指針，可以有效的支持范圍查詢；此外葉子節點本身就是根據key進行排序的）
索引將隨機IO變成順序IO

6. 適用范圍

索引并不是適用于任何情況。對于中型、大型表適用。對于小型表全表掃描更高效。而對于特大型表，考慮”分區”技術

II. 索引的使用原則

一般我們在創建表的時候，需要指定primary key, 這樣就可以確定聚集索引了，那么如何添加非聚集索引呢？

1. 索引的幾個語法

創建索引

-- 創建索引 create index `idx_img` on newuser(`img`);-- 查看 show create table newuser\G;

輸出

show create table newuser\G *************************** 1. row ***************************Table: newuser Create Table: CREATE TABLE `newuser` (`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用戶id',`username` varchar(30) DEFAULT '' COMMENT '用戶登錄名',`nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用戶昵稱',`password` varchar(50) DEFAULT '' COMMENT '用戶登錄密碼 & 密文根式',`address` text COMMENT '用戶地址',`email` varchar(50) NOT NULL DEFAULT '' COMMENT '用戶郵箱',`phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用戶手機號',`img` varchar(100) DEFAULT '' COMMENT '用戶頭像',`extra` text,`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',`created` int(11) NOT NULL,`updated` int(11) NOT NULL,PRIMARY KEY (`userId`),KEY `idx_username` (`username`),KEY `idx_nickname` (`nickname`),KEY `idx_email` (`email`),KEY `idx_phone` (`phone`),KEY `idx_img` (`img`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

另一種常見的添加索引方式

alter table newuser add index `idx_extra_img`(`isDeleted`, `img`);-- 查看索引 show index from newuser;

輸出結果

+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | newuser | 0 | PRIMARY | 1 | userId | A | 3 | NULL | NULL | | BTREE | | | | newuser | 1 | idx_username | 1 | username | A | 3 | NULL | NULL | YES | BTREE | | | | newuser | 1 | idx_nickname | 1 | nickname | A | 3 | NULL | NULL | | BTREE | | | | newuser | 1 | idx_email | 1 | email | A | 3 | NULL | NULL | | BTREE | | | | newuser | 1 | idx_phone | 1 | phone | A | 3 | NULL | NULL | | BTREE | | | | newuser | 1 | idx_img | 1 | img | A | 3 | NULL | NULL | YES | BTREE | | | | newuser | 1 | idx_extra_img | 1 | isDeleted | A | 3 | NULL | NULL | | BTREE | | | | newuser | 1 | idx_extra_img | 2 | img | A | 3 | NULL | NULL | YES | BTREE | | | +---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

刪除索引

drop index `idx_extra_img` on newuser; drop index `idx_img` on newuser;-- 查看索引 show index from newuser;

輸出

強制走索引的一種方式

語法： select * from table force index(索引) where xxx

2. 索引使用規則

當一個表內有多個索引時，如何判斷自己的sql是否走到了索引，走的是哪個索引呢？

可以通過 explain 關鍵字來進行輔助判斷，當然在實際寫sql時，我們也有必要了解下索引匹配的規則，避免設置了一些冗余的索引，或者寫出一些走不到索引的sql

測試的表結構如下

*************************** 1. row ***************************Table: newuser Create Table: CREATE TABLE `newuser` (`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用戶id',`username` varchar(30) DEFAULT '' COMMENT '用戶登錄名',`nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用戶昵稱',`password` varchar(50) DEFAULT '' COMMENT '用戶登錄密碼 & 密文根式',`address` text COMMENT '用戶地址',`email` varchar(50) NOT NULL DEFAULT '' COMMENT '用戶郵箱',`phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用戶手機號',`img` varchar(100) DEFAULT '' COMMENT '用戶頭像',`extra` text,`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',`created` int(11) NOT NULL,`updated` int(11) NOT NULL,PRIMARY KEY (`userId`),KEY `idx_username` (`username`),KEY `idx_nickname_email_phone` (`nickname`,`email`,`phone`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

a. 最左前綴匹配原則

這個主要是針對多列非聚簇索引而言，比如有下面這個索引idx_nickname_email_phone(nickname, email, phone), nickname 定義在email的前面，那么下面這幾個語句對應的情況是

b. 無法跳過某個列使用后續索引列

即對索引idx_nickname_email_phone(nickname, email, phone), 如果你的sql中，只有 nickname 和 phone, 那么phone走不到索引，因為不能跳過中間的email走索引

c. 范圍查詢后的列無法使用索引

如 >, <, between, like這種就是范圍查詢，下面的sql中，email 和phone都無法走到索引，因為nickname使用了范圍查詢

select * from newuser where nickname like '小灰%' and email='greywolf@xxx.com' and phone=15971112301 limit 10;

d. 列作為函數參數或表達式的一部分

3. 索引缺點

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。
建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快。

4. 注意事項

索引不會包含有NULL值的列
使用短索引
索引列排序
- MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那么order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引
like語句操作
- 一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引
不要在列上進行運算
- select * from users where YEAR(adddate)<2007;
盡量不使用NOT IN和<>操作

5. sql使用策略

a. 使用一個sql代替多個sql

通常建議是使用一個sql來替代多個sql的查詢

當然若sql執行效率很低，或者出現delete等導致鎖表的操作時，也可以采用多個sql，避免阻塞其他sql

b. 分解關聯查詢

將關聯join盡量放在應用中來做，盡量執行小而簡單的的sql

分解后的sql簡單，利于使用mysql緩存
執行分解后的sql，減少鎖競爭
更好的擴展性和維護性（sql簡單）
關聯sql使用的是內嵌循環算法nestloop，而應用中可以使用hashmap等結構處理數據，效率更高

c. count

count(*) 統計的是行數
count(列名) 統計的是列不為null的數量

d. limit

limit offset, size; 分頁查詢，會查詢出 offset + size 條數據，獲取最后的size條數據

如 limit 1000, 20 則會查詢出滿足條件的1020條數據，然后將最后的20個返回，所以盡量避免大翻頁查詢

e. union

需要將where、order by、limit 這些限制放入到每個子查詢，才能重分提升效率。另外如非必須，盡量使用Union all，因為union會給每個子查詢的臨時表加入distinct，對每個臨時表做唯一性檢查，效率較差。

6. mysql使用查詢

a. 查看索引

-- 單位為GB SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';

b. 查看表空間

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size' FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';

c. 查看數據庫中所有表的信息

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', table_rows AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' , CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total' FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';

IV. 其他

參考

深入理解Mysql——高性能索引與高性能SQL

個人博客：一灰灰Blog

基于hexo + github pages搭建的個人博客，記錄所有學習和工作中的博文，歡迎大家前去逛逛

聲明

盡信書則不如，已上內容，純屬一家之言，因本人能力一般，見識有限，如發現bug或者有更好的建議，隨時歡迎批評指正

微博地址: 小灰灰Blog
QQ：一灰灰/3302797840

來源：https://cloud.tencent.com/developer/article/1072135

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的mysql之索引的工作机制的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：先正达（说一说先正达的简介）
下一篇：不属于系统软件的是什么程序（不属于系统软