一，什么情況下使用索引

1. 表的主關(guān)鍵字

自動(dòng)建立唯一索引

2. 表的字段唯一約束

ORACLE利用索引來保證數(shù)據(jù)的完整性

3. 直接條件查詢的字段

在SQL中用于條件約束的字段

如zl_yhjbqk（用戶基本情況）中的qc_bh（區(qū)冊(cè)編號(hào)）

select * from zl_yhjbqk where qc_bh=’7001’;

4. 查詢中與其它表關(guān)聯(lián)的字段

字段常常建立了外鍵關(guān)系

如zl_ydcf（用電成份）中的jldb_bh（計(jì)量點(diǎn)表編號(hào)）

select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’;

5. 查詢中排序的字段

排序的字段如果通過索引去訪問那將大大提高排序速度

select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）;select * from zl_yhjbqk where qc_bh=’7001’ order by cb_sx（建立qc_bh+cb_sx索引，注：只是一個(gè)索引，其中包括qc_bh和cb_sx字段）;

6. 查詢中統(tǒng)計(jì)或分組統(tǒng)計(jì)的字段

select max(hbs_bh) from zl_yhjbqk;select qc_bh,count(*) from zl_yhjbqk group by qc_bh;

二，什么情況下應(yīng)不建或少建索引

1. 表記錄太少

如果一個(gè)表只有5條記錄，采用索引去訪問記錄的話，那首先需訪問索引表，再通過索引表訪問數(shù)據(jù)表，一般索引表與數(shù)據(jù)表不在同一個(gè)數(shù)據(jù)塊，這種情況下ORACLE至少要往返讀取數(shù)據(jù)塊兩次。而不用索引的情況下ORACLE會(huì)將所有的數(shù)據(jù)一次讀出，處理速度顯然會(huì)比用索引快。

如表zl_sybm（使用部門）一般只有幾條記錄，除了主關(guān)鍵字外對(duì)任何一個(gè)字段建索引都不會(huì)產(chǎn)生性能優(yōu)化，實(shí)際上如果對(duì)這個(gè)表進(jìn)行了統(tǒng)計(jì)分析后ORACLE也不會(huì)用你建的索引，而是自動(dòng)執(zhí)行全表訪問。如：

select * from zl_sybm where sydw_bh=’5401’（對(duì)sydw_bh建立索引不會(huì)產(chǎn)生性能優(yōu)化）

2. 經(jīng)常插入、刪除、修改的表

對(duì)一些經(jīng)常處理的業(yè)務(wù)表應(yīng)在查詢?cè)试S的情況下盡量減少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等業(yè)務(wù)表。

3. 數(shù)據(jù)重復(fù)且分布平均的表字段

假如一個(gè)表有10萬行記錄，有一個(gè)字段A只有T和F兩種值，且每個(gè)值的分布概率大約為50%，那么對(duì)這種表A字段建索引一般不會(huì)提高數(shù)據(jù)庫(kù)的查詢速度。

4. 經(jīng)常和主字段一塊查詢但主字段索引值比較多的表字段

如gc_dfss（電費(fèi)實(shí)收）表經(jīng)常按收費(fèi)序號(hào)、戶標(biāo)識(shí)編號(hào)、抄表日期、電費(fèi)發(fā)生年月、操作 標(biāo)志來具體查詢某一筆收款的情況，如果將所有的字段都建在一個(gè)索引里那將會(huì)增加數(shù)據(jù)的修改、插入、刪除時(shí)間，從實(shí)際上分析一筆收款如果按收費(fèi)序號(hào)索引就已 經(jīng)將記錄減少到只有幾條，如果再按后面的幾個(gè)字段索引查詢將對(duì)性能不產(chǎn)生太大的影響。

---

三、MySQL索引的概念

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。更通俗的說，數(shù)據(jù)庫(kù)索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫(kù)的查詢速度。上述SQL語句，在沒有索引的情況下，數(shù)據(jù)庫(kù)會(huì)遍歷全部200條數(shù)據(jù)后選擇符合條件的；而有了相應(yīng)的索引之后，數(shù)據(jù)庫(kù)會(huì)直接在索引中查找符合條件的選項(xiàng)。如果我們把SQL語句換成“SELECT * FROM article WHERE id=2000000”，那么你是希望數(shù)據(jù)庫(kù)按照順序讀取完200萬行數(shù)據(jù)以后給你結(jié)果還是直接在索引中定位呢？（注：一般數(shù)據(jù)庫(kù)默認(rèn)都會(huì)為主鍵生成索引）。

索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的，而非聚簇索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對(duì)于單行的檢索很快。

四、索引的類型

在數(shù)據(jù)庫(kù)表中，對(duì)字段建立索引可以大大提高查詢速度。假如我們創(chuàng)建了一個(gè) mytable表

代碼如下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT
NULL );

我們隨機(jī)向里面插入了10000條記錄，其中有一條：5555, admin。

在查找username="admin"的記錄 SELECT * FROM mytable WHERE
username=‘a(chǎn)dmin’;時(shí)，如果在username上已經(jīng)建立了索引，MySQL無須任何掃描，即準(zhǔn)確可找到該記錄。相反，MySQL會(huì)掃描所有記錄，即要查詢10000條記錄。

索引分單列索引和組合索引。單列索引，即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引。組合索引，即一個(gè)索包含多個(gè)列。

1、普通索引
這是最基本的索引，它沒有任何限制，比如上文中為title字段創(chuàng)建的索引就是一個(gè)普通索引，MyIASM中默認(rèn)的BTREE類型的索引，也是我們大多數(shù)情況下用到的索引。
創(chuàng)建方式：

• 直接創(chuàng)建索引

CREATE INDEX index_name ON table(column(length));

• 修改表結(jié)構(gòu)的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length));

• 創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建索引

CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),INDEX index_name (title(length)) );

• 刪除索引

DROP INDEX index_name ON table;

2、唯一索引
與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須是唯一的，創(chuàng)建方法和普通索引類似。

• 創(chuàng)建唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length));

• 修改表結(jié)構(gòu)

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length));

• 創(chuàng)建表時(shí)同時(shí)創(chuàng)建索引

CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),UNIQUE indexName (title(length)) );

3、主鍵索引
它是一種特殊的唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引：

// 代碼如下: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

當(dāng)然也可以用 ALTER 命令。記住：一個(gè)表只能有一個(gè)主鍵。

4、組合索引
平時(shí)用的SQL查詢語句一般都有比較多的限制條件，所以為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引。
例如上表中針對(duì)title和time建立一個(gè)組合索引：

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10));

建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面兩組組合索引：

–title,time–title

為什么沒有time這樣的組合索引呢？這是因?yàn)镸ySQL組合索引“最左前綴”的結(jié)果。
簡(jiǎn)單的理解就是只從最左面的開始組合。并不是只要包含這兩列的查詢都會(huì)用到該組合索引，如下面的幾個(gè)SQL所示：

• 使用到上面的索引

SELECT * FROM article WHREE title='測(cè)試' AND time=1234567890;SELECT * FROM article WHREE utitle='測(cè)試';

• 未使用到上面的索引

SELECT * FROM article WHREE time=1234567890;

五、索引的優(yōu)化

上面說了使用索引的好處，但過多的使用索引將會(huì)造成濫用。因此索引也會(huì)有它的缺點(diǎn)：雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。建立索引會(huì)占用磁盤空間的索引文件。一般情況這個(gè)問題不太嚴(yán)重，但如果你在一個(gè)大表上創(chuàng)建了多種組合索引，索引文件的會(huì)膨脹很快。索引只是提高效率的一個(gè)因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時(shí)間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句。下面是一些總結(jié)以及收藏的MySQL索引的注意事項(xiàng)和優(yōu)化方法。

1. 何時(shí)使用聚集索引或非聚集索引？

事實(shí)上，我們可以通過前面聚集索引和非聚集索引的定義的例子來理解上表。如：返回某范圍內(nèi)的數(shù)據(jù)一項(xiàng)。比如您的某個(gè)表有一個(gè)時(shí)間列，恰好您把聚合索引建立在了該列，這時(shí)您查詢2004年1月1日至2004年10月1日之間的全部數(shù)據(jù)時(shí)，這個(gè)速度就將是很快的，因?yàn)槟倪@本字典正文是按日期進(jìn)行排序的，聚類索引只需要找到要檢索的所有數(shù)據(jù)中的開頭和結(jié)尾數(shù)據(jù)即可；而不像非聚集索引，必須先查到目錄中查到每一項(xiàng)數(shù)據(jù)對(duì)應(yīng)的頁碼，然后再根據(jù)頁碼查到具體內(nèi)容。

2. 索引不會(huì)包含有NULL值的列

只要列中包含有NULL值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL。

3. 使用短索引

對(duì)串列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如，如果有一個(gè)CHAR(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)，多數(shù)值是惟一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

4. 索引列排序

MySQL查詢只使用一個(gè)索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。

5. like語句操作

一般情況下不鼓勵(lì)使用like操作，如果非使用不可，如何使用也是一個(gè)問題。like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引。

6. 不要在列上進(jìn)行運(yùn)算

//例如： select * from users where YEAR(adddate)<2007;

將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描，因此我們可以改成：

select * from users where adddate<’2007-01-01′;

關(guān)于這一點(diǎn)可以圍觀：一個(gè)單引號(hào)引發(fā)的MYSQL性能損失。

最后總結(jié)一下，MySQL只對(duì)以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時(shí)候的like(不以通配符%或_開頭的情形)。
而理論上每張表里面最多可創(chuàng)建16個(gè)索引，不過除非是數(shù)據(jù)量真的很多，否則過多的使用索引會(huì)引起反作用，索引雖好用，可不要太迷戀噢。

---

原文鏈接：

轉(zhuǎn)自：https://www.cnblogs.com/Adalia-Ting/p/9492475.html
https://blog.csdn.net/dengchenrong/article/details/88425762

總結(jié)

以上是生活随笔為你收集整理的关于MySQL什么时候使用索引 什么情况下应不建或少建索引的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。