FavoriteLoading
0

在使用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估計使用全表掃描要比使用索引快,則不使用索引
索引雖然好處很多,但過多的使用索引可能帶來相反的問題,索引也是有缺點的:
雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對表進行INSERT,UPDATE和DELETE。因為更新表時,mysql不僅要保存數據,還要保存一下索引文件
建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重,但如果你在要給大表上建了多種組合索引,索引文件會膨脹很寬
索引只是提高效率的一個方式,如果mysql有大數據量的表,就要花時間研究建立最優的索引,或優化查詢語句。
使用索引時,有一些技巧:
1.索引不會包含有NULL的列
只要列中包含有NULL值,都將不會被包含在索引中,復合索引中只要有一列含有NULL值,那么這一列對于此符合索引就是無效的。
2.使用短索引
對串列進行索引,如果可以就應該指定一個前綴長度。例如,如果有一個char(255)的列,如果在前10個或20個字符內,多數值是唯一的,那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。
3.索引列排序
mysql查詢只使用一個索引,因此如果where子句中已經使用了索引的話,那么order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作,盡量不要包含多個列的排序,如果需要最好給這些列建復合索引。
4.like語句操作
一般情況下不鼓勵使用like操作,如果非使用不可,注意正確的使用方式。like ‘%aaa%'不會使用索引,而like ‘aaa%'可以使用索引。
5.不要在列上進行運算
6.不使用NOT IN 、<>、!=操作,但<,<=,=,>,>=,BETWEEN,IN是可以用到索引的
7.索引要建立在經常進行select操作的字段上。
這是因為,如果這些列很少用到,那么有無索引并不能明顯改變查詢速度。相反,由于增加了索引,反而降低了系統的維護速度和增大了空間需求。
8.索引要建立在值比較唯一的字段上。
9.對于那些定義為text、image和bit數據類型的列不應該增加索引。因為這些列的數據量要么相當大,要么取值很少。
10.在where和join中出現的列需要建立索引。
11.where的查詢條件里有不等號(where column != …),mysql將無法使用索引。
12.如果where字句的查詢條件里使用了函數(如:where DAY(column)=…),mysql將無法使用索引。
13.在join操作中(需要從多個數據表提取數據時),mysql只有在主鍵和外鍵的數據類型相同時才能使用索引,否則及時建立了索引也不會使用。

【聲明】:8090安全小組門戶(http://www.jvwkvg.tw)登載此文出于傳遞更多信息之目的,并不代表本站贊同其觀點和對其真實性負責,僅適于網絡安全技術愛好者學習研究使用,學習中請遵循國家相關法律法規。如有問題請聯系我們,聯系郵箱[email protected],我們會在最短的時間內進行處理。