程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> mysql索引使用技巧及注意事項

mysql索引使用技巧及注意事項

編輯：關於MYSQL數據庫

一.索引的作用

一般的應用系統，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現性能問題，遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，所以查詢語句的優化顯然是重中之重。

在數據量和訪問量不大的情況下，mysql訪問是非常快的，是否加索引對訪問影響不大。但是當數據量和訪問量劇增的時候，就會發現mysql變慢，甚至down掉，這就必須要考慮優化sql了，給數據庫建立正確合理的索引，是mysql優化的一個重要手段。

索引的目的在於提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。如果沒有索引，那麼你可能需要把所有單詞看一遍才能找到你想要的。除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數據。

在創建索引時，需要考慮哪些列會用於 SQL 查詢，然後為這些列創建一個或多個索引。事實上，索引也是一種表，保存著主鍵或索引字段，以及一個能將每個記錄指向實際表的指針。數據庫用戶是看不到索引的，它們只是用來加速查詢的。數據庫搜索引擎使用索引來快速定位記錄。

INSERT 與 UPDATE 語句在擁有索引的表中執行會花費更多的時間，而SELECT 語句卻會執行得更快。這是因為，在進行插入或更新時，數據庫也需要插入或更新索引值。

二.索引的創建、刪除

索引的類型：

UNIQUE(唯一索引)：不可以出現相同的值，可以有NULL值
INDEX(普通索引)：允許出現相同的索引內容
PROMARY KEY(主鍵索引)：不允許出現相同的值
fulltext index(全文索引)：可以針對值中的某個單詞，但效率確實不敢恭維
組合索引：實質上是將多個字段建到一個索引裡，列值的組合必須唯一

(1)使用ALTER TABLE語句創建索性

應用於表創建完畢之後再添加。

ALTER TABLE 表名 ADD 索引類型 （unique,primary key,fulltext,index）[索引名]（字段名）

//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主鍵索引
alter table table_name add primary key (column_list) ;

ALTER TABLE可用於創建普通索引、UNIQUE索引和PRIMARY KEY索引3種索引格式，table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。索引名index_name可選，缺省時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以同時創建多個索引。

(2)使用CREATE INDEX語句對表增加索引

CREATE INDEX可用於對表增加普通索引或UNIQUE索引，可用於建表時創建索引。

CREATE INDEX index_name ON table_name(username(length));

如果是CHAR，VARCHAR類型，length可以小於字段實際長度；如果是BLOB和TEXT類型，必須指定 length。

//create只能添加這兩種索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有與ALTER TABLE語句中相同的含義，索引名不可選。另外，不能用CREATE INDEX語句創建PRIMARY KEY索引。

(3)刪除索引

刪除索引可以使用ALTER TABLE或DROP INDEX語句來實現。DROP INDEX可以在ALTER TABLE內部作為一條語句處理，其格式如下：

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

其中，在前面的兩條語句中，都刪除了table_name中的索引index_name。而在最後一條語句中，只在刪除PRIMARY KEY索引中使用，因為一個表只可能有一個PRIMARY KEY索引，因此不需要指定索引名。如果沒有創建PRIMARY KEY索引，但表具有一個或多個UNIQUE索引，則MySQL將刪除第一個UNIQUE索引。

如果從表中刪除某列，則索引會受影響。對於多列組合的索引，如果刪除其中的某列，則該列也會從索引中刪除。如果刪除組成索引的所有列，則整個索引將被刪除。

(4) 組合索引與前綴索引

在這裡要指出，組合索引和前綴索引是對建立索引技巧的一種稱呼，並不是索引的類型。為了更好的表述清楚，建立一個demo表如下。

create table USER_DEMO
(
  ID          int not null auto_increment comment '主鍵',
  LOGIN_NAME      varchar(100) not null comment '登錄名',
  PASSWORD       varchar(100) not null comment '密碼',
  CITY         varchar(30) not null comment '城市',
  AGE         int not null comment '年齡',
  SEX         int not null comment '性別(0:女 1：男)',
  primary key (ID)
);

為了進一步搾取mysql的效率，就可以考慮建立組合索引，即將LOGIN_NAME,CITY,AGE建到一個索引裡：

復制代碼代碼如下:
ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);

建表時，LOGIN_NAME長度為100，這裡用16，是因為一般情況下名字的長度不會超過16，這樣會加快索引查詢速度，還會減少索引文件的大小，提高INSERT，UPDATE的更新速度。

如果分別給LOGIN_NAME,CITY,AGE建立單列索引，讓該表有3個單列索引，查詢時和組合索引的效率是大不一樣的，甚至遠遠低於我們的組合索引。雖然此時有三個索引，但mysql只能用到其中的那個它認為似乎是最有效率的單列索引，另外兩個是用不到的，也就是說還是一個全表掃描的過程。

建立這樣的組合索引，就相當於分別建立如下三種組合索引：

LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

為什麼沒有CITY,AGE等這樣的組合索引呢？這是因為mysql組合索引“最左前綴”的結果。簡單的理解就是只從最左邊的開始組合，並不是只要包含這三列的查詢都會用到該組合索引。也就是說name_city_age(LOGIN_NAME(16),CITY,AGE)從左到右進行索引，如果沒有左前索引，mysql不會執行索引查詢。

如果索引列長度過長,這種列索引時將會產生很大的索引文件,不便於操作,可以使用前綴索引方式進行索引，前綴索引應該控制在一個合適的點,控制在0.31黃金值即可(大於這個值就可以創建)。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 這個值大於0.31就可以創建前綴索引,Distinct去重復

ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前綴索引SQL,將人名的索引建立在10,這樣可以減少索引文件大小,加快索引查詢速度

三.索引的使用及注意事項

EXPLAIN可以幫助開發人員分析SQL問題,explain顯示了mysql如何使用索引來處理select語句以及連接表,可以幫助選擇更好的索引和寫出更優化的查詢語句。

使用方法,在select語句前加上Explain就可以了：

Explain select * from user where id=1;

盡量避免這些不走索引的sql：

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引,因為所有索引列參與了計算

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因為使用了函數運算,原理與上面相同

SELECT * FROM `houdunwang` WHERE `uname` LIKE'後盾%' -- 走索引

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%後盾%" -- 不走索引

-- 正則表達式不使用索引,這應該很好理解,所以為什麼在SQL中很難看到regexp關鍵字的原因

-- 字符串與數字比較不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果條件中有or,即使其中有條件帶索引也不會使用。換言之,就是要求使用的所有字段,都必須建立索引, 我們建議大家盡量避免使用or 關鍵字

-- 如果mysql估計使用全表掃描要比使用索引快,則不使用索引

索引雖然好處很多，但過多的使用索引可能帶來相反的問題，索引也是有缺點的：