1.索引概述

1.1索引介紹

索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)。在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足 特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu) 上實現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。

1.2索引的優(yōu)劣勢

2.索引結(jié)構(gòu)

2.1 概述

MySQL的索引是在存儲引擎層實現(xiàn)的，不同的存儲引擎有不同的索引結(jié)構(gòu)，主要包含以下幾種：如下圖所示：

上述是MySQL中所支持的所有的索引結(jié)構(gòu)，不同的存儲引擎對于索引結(jié)構(gòu)的支持情況如下圖所示。平常所說的索引，如果沒有特別指明，都是指B+樹結(jié)構(gòu)組織的索引。

在 MySQL 5.5 之后， InnoDB是默認(rèn)的 MySQL 存儲引擎，InnoDB引擎的默認(rèn)索引是B+tree。 MySQL 索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的 B+Tree 進(jìn)行了優(yōu)化。在原 B+Tree的基礎(chǔ)上，增加一個指向相鄰葉子節(jié)點 的鏈表指針，就形成了帶有順序指針的B+Tree，如下圖所示。這提高區(qū)間訪問的性能，利于排序。并且InnoDB 中具有自適應(yīng) hash 功能， hash 索引是 InnoDB存儲引擎根據(jù) B+Tree 索引在指定條件下自動構(gòu)建的。

3. 索引分類

3.1 索引分類

在MySQL數(shù)據(jù)庫，將索引的具體類型主要分為以下幾類：主鍵索引、唯一索引、常規(guī)索引、全文索引。如下圖所示。

3.2 聚集索引&二級索引

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

聚集索引選取規(guī)則 :

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

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

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

聚集索引和二級索引的具體結(jié)構(gòu)如下圖所示。 聚集索引的葉子節(jié)點下掛的是這一行的數(shù)據(jù) ， 二級索引的葉子節(jié)點下掛的是該字段值對應(yīng)的主鍵值。

執(zhí) 行如下的 SQL語句時，具體的查找過程如下所示。

具體過程如下:

1. 由于是根據(jù) name 字段進(jìn)行查詢，所以先根據(jù) name='Arm' 到 name 字段的二級索引中進(jìn)行匹配查 找。但是在二級索引中只能查找到 Arm 對應(yīng)的主鍵值 10 。

2. 由于查詢返回的數(shù)據(jù)是 * ，所以此時，還需要根據(jù)主鍵值 10 ，到聚集索引中查找 10 對應(yīng)的記錄，最 終找到10 對應(yīng)的行 row 。

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

索引使用

4.1索引語法

1.創(chuàng)建索引

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創(chuàng)建演示

首先創(chuàng)建一個名為tb_user的表并插入一些數(shù)據(jù)

create table tb_user(
id int primary key auto_increment comment '主鍵',
name varchar(50) not null comment '用戶名',
phone varchar(11) not null comment '手機(jī)號',
email varchar(100) comment '郵箱',
profession varchar(11) comment '專業(yè)',
age tinyint unsigned comment '年齡',
gender char(1) comment '性別 , 1: 男, 2: 女',
status char(1) comment '狀態(tài)',
createtime datetime comment '創(chuàng)建時間'
) comment '系統(tǒng)用戶表';
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');

插入如下的數(shù)據(jù)

1.如果需求是 ，name字段為姓名字段，該字段的值可能會重復(fù)，為該字段創(chuàng)建索引。

則該創(chuàng)建索引的語法為

CREATE INDEX idx_user_name ON tb_user(name);

2.phone手機(jī)號字段的值，是非空，且唯一的，為該字段創(chuàng)建唯一索引。 則該創(chuàng)建索引的語法為

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

3.為profession、age、status創(chuàng)建聯(lián)合索引。

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表的所有的索引數(shù)據(jù)

show index from tb_user;

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

DROP INDEX idx_email ON tb_user ;

5.索引法則

1.最左前綴法則

如果索引了多列（聯(lián)合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始， 并且不跳過索引中的列。如果跳躍某一列，索引將會部分失效( 后面的字段索引失效 ) 。

2.范圍查詢

聯(lián)合索引中，出現(xiàn)范圍查詢 (>,<)，范圍查詢右側(cè)的列索引失效。所以，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或 <= 這類的范圍查詢，而避免使用 > 或 <

3.索引失效情況

1.索引列運算

不要在索引列上進(jìn)行運算操作， 索引將失效，如進(jìn)行函數(shù)運算操作。

2. 字符串不加引號

字符串類型字段使用時，不加引號，索引將失效。

3.  模糊查詢

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

4 .or 連接條件

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

5 . 數(shù)據(jù)分布影響

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

6.SQL提示

SQL 提示，是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說，就是在 SQL 語句中加入一些人為的提示來達(dá)到優(yōu) 化操作的目的。

1. use index ： 建議 MySQL 使用哪一個索引完成此次查詢（僅僅是建議， mysql 內(nèi)部還會再次進(jìn) 行評估）。使用代碼示例如下。

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 ： 強(qiáng)制使用索引。使用代碼示例如下。

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

總結(jié)

到此這篇關(guān)于sql索引的介紹以及使用規(guī)則的文章就介紹到這了,更多相關(guān)sql索引使用規(guī)則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論