sql索引使用規則是什么

這篇“sql索引使用規則是什么”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內容，內容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“sql索引使用規則是什么”文章吧。

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索引使用規則是什么”這篇文章的內容，相信大家都有了一定的了解，希望小編分享的內容對大家有幫助，若想了解更多相關的知識內容，請關注億速云行業資訊頻道。