sql索引的介紹以及使用規則詳析

2023-04-04 06:01:20

1.索引概述

1.1索引介紹

索引（index）是幫助MySQL高效獲取資料的資料結構(有序)。在資料之外，資料庫系統還維護著滿足特定查詢演演算法的資料結構，這些資料結構以某種方式參照（指向）資料，這樣就可以在這些資料結構上實現高階查詢演演算法，這種資料結構就是索引。

1.2索引的優劣勢

2.索引結構

2.1 概述

MySQL的索引是在儲存引擎層實現的，不同的儲存引擎有不同的索引結構，主要包含以下幾種：如下圖所示：

上述是MySQL中所支援的所有的索引結構，不同的儲存引擎對於索引結構的支援情況如下圖所示。平常所說的索引，如果沒有特別指明，都是指B+樹結構組織的索引。

在 MySQL 5.5 之後， InnoDB是預設的 MySQL 儲存引擎，InnoDB引擎的預設索引是B+tree。 MySQL 索引資料結構對經典的 B+Tree 進行了優化。在原 B+Tree的基礎上，增加一個指向相鄰葉子節點的連結串列指標，就形成了帶有順序指標的B+Tree，如下圖所示。這提高區間存取的效能，利於排序。並且InnoDB 中具有自適應 hash 功能， hash 索引是 InnoDB儲存引擎根據 B+Tree 索引在指定條件下自動構建的。

3. 索引分類

3.1 索引分類

在MySQL資料庫，將索引的具體型別主要分為以下幾類：主鍵索引、唯一索引、常規索引、全文索引。如下圖所示。

3.2 聚集索引&二級索引

而在在 InnoDB 儲存引擎中，根據索引的儲存形式，又可以分為以下兩種：

聚集索引選取規則 :

1.如果存在主鍵，主鍵索引就是聚集索引。

2.如果不存在主鍵，將使用第一個唯一（ UNIQUE ）索引作為聚集索引。

3。如果表沒有主鍵，或沒有合適的唯一索引，則 InnoDB 會自動生成一個 rowid 作為隱藏的聚集索引。

聚集索引和二級索引的具體結構如下圖所示。聚集索引的葉子節點下掛的是這一行的資料，二級索引的葉子節點下掛的是該欄位值對應的主鍵值。

執行如下的 SQL語句時，具體的查詢過程如下所示。

具體過程如下:

1. 由於是根據 name 欄位進行查詢，所以先根據 name='Arm' 到 name 欄位的二級索引中進行匹配查找。但是在二級索引中只能查詢到 Arm 對應的主鍵值 10 。

2. 由於查詢返回的資料是 * ，所以此時，還需要根據主鍵值 10 ，到聚集索引中查詢 10 對應的記錄，最終找到10 對應的行 row 。

3 。最終拿到這一行的資料，直接返回即可。其中回表查詢是指，這種先到二級索引中查詢資料，找到主鍵值，然後再到聚集索引中根據主鍵值，獲取資料的方式，就稱之為回表查詢。

索引使用

4.1索引語法

1.建立索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

2.檢視索引

SHOW INDEX FROM table_name ;

3. 刪除索引

DROP INDEX index_name ON table_name ;

4.2建立演示

首先建立一個名為tb_user的表並插入一些資料

create table tb_user(
id int primary key auto_increment comment '主鍵',
name varchar(50) not null comment '使用者名稱',
phone varchar(11) not null comment '手機號',
email varchar(100) comment '郵箱',
profession varchar(11) comment '專業',
age tinyint unsigned comment '年齡',
gender char(1) comment '性別 , 1: 男, 2: 女',
status char(1) comment '狀態',
createtime datetime comment '建立時間'
) comment '系統使用者表';
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('呂布', '17799990000', 'lvbu666@163.com', '軟體工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通訊工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('趙雲', '17799990002', '17799990@139.com', '英語', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('孫悟空', '17799990003', '17799990@sina.com', '工程造價', 54,
'1', '0', '2001-07-02 00:00:00');

插入如下的資料

1.如果需求是，name欄位為姓名欄位，該欄位的值可能會重複，為該欄位建立索引。

則該建立索引的語法為

CREATE INDEX idx_user_name ON tb_user(name);

2.phone手機號欄位的值，是非空，且唯一的，為該欄位建立唯一索引。則該建立索引的語法為

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

3.為profession、age、status建立聯合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

4.為email建立合適的索引來提升查詢效率

CREATE INDEX idx_email ON tb_user(email);

5.檢視tb_user表的所有的索引資料

show index from tb_user;

6.刪除索引如刪除email的索引

DROP INDEX idx_email ON tb_user ;

5.索引法則

1.最左字首法則

如果索引了多列（聯合索引），要遵守最左字首法則。最左字首法則指的是查詢從索引的最左列開始，並且不跳過索引中的列。如果跳躍某一列，索引將會部分失效( 後面的欄位索引失效 ) 。

2.範圍查詢

聯合索引中，出現範圍查詢 (>,<)，範圍查詢右側的列索引失效。所以，在業務允許的情況下，儘可能的使用類似於 >= 或 <= 這類的範圍查詢，而避免使用 > 或 <

3.索引失效情況

1.索引列運算

不要在索引列上進行運算操作，索引將失效，如進行函數運算操作。

2. 字串不加引號

字串型別欄位使用時，不加引號，索引將失效。

3. 模糊查詢

如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

4 .or 連線條件

用 or 分割開的條件，如果 or 前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。

5 . 資料分佈影響

如果 MySQL 評估使用索引比全表更慢，則不使用索引。但可以通過sql提示來進行改變。

6.SQL提示

SQL 提示，是優化資料庫的一個重要手段，簡單來說，就是在 SQL 語句中加入一些人為的提示來達到優化操作的目的。

1. use index ：建議 MySQL 使用哪一個索引完成此次查詢（僅僅是建議， mysql 內部還會再次進行評估）。使用程式碼範例如下。

explain select * from tb_user use index(idx_user_pro) where profession = '軟體工程';

2. ignore index ：忽略指定的索引。使用程式碼範例如下。

explain select * from tb_user ignore index(idx_user_pro) where profession = '軟體工程';

3. force index ：強制使用索引。使用程式碼範例如下。

explain select * from tb_user force index(idx_user_pro) where profession = '軟體工程';

總結

到此這篇關於sql索引的介紹以及使用規則的文章就介紹到這了,更多相關sql索引使用規則內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！