本文從如何建立mysql索引以及介紹mysql的索引類型,再講mysql索引的利與弊,以及建立索引時需要注意的地方
首先:先假設(shè)有一張表,表的數(shù)據(jù)有10W條數(shù)據(jù),其中有一條數(shù)據(jù)是nickname='css',如果要拿這條數(shù)據(jù)的話需要些的sql是 SELECT * FROM award WHERE nickname = 'css'
一般情況下,在沒有建立索引的時候,mysql需要掃描全表及掃描10W條數(shù)據(jù)找這條數(shù)據(jù),如果我在nickname上建立索引,那么mysql只需要掃描一行數(shù)據(jù)及為我們找到這條nickname='css'的數(shù)據(jù),是不是感覺性能提升了好多咧....
mysql的索引分為單列索引(主鍵索引,唯索引,普通索引)和組合索引.
單列索引:一個索引只包含一個列,一個表可以有多個單列索引.
組合索引:一個組合索引包含兩個或兩個以上的列,
本文使用的案例的表
CREATE TABLE `award` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶id',
`aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活動場景id',
`nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用戶昵稱',
`is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用戶是否領(lǐng)獎',
`award_time` int(11) NOT NULL DEFAULT 0 COMMENT '領(lǐng)獎時間',
`account` varchar(12) NOT NULL DEFAULT '' COMMENT '帳號',
`password` char(32) NOT NULL DEFAULT '' COMMENT '密碼',
`message` varchar(255) NOT NULL DEFAULT '' COMMENT '獲獎信息',
`created_time` int(11) NOT NULL DEFAULT 0 COMMENT '創(chuàng)建時間',
`updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='獲獎信息表';
(一)索引的創(chuàng)建
1.單列索引
1-1) 普通索引,這個是最基本的索引,
其sql格式是 CREATE INDEX IndexName ON TableName(字段名(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(字段名(length))
第一種方式 :
CREATE INDEX account_Index ON `award`(`account`);
第二種方式:
ALTER TABLE award ADD INDEX account_Index(`account`)
如果是CHAR,VARCHAR,類型,length可以小于字段的實際長度,如果是BLOB和TEXT類型就必須指定長度,
1-2) 唯一索引,與普通索引類似,但是不同的是唯一索引要求所有的類的值是唯一的,這一點和主鍵索引一樣.但是他允許有空值,
其sql格式是 CREATE UNIQUE INDEX IndexName ON TableName(字段名(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)
CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);
1-3) 主鍵索引,不允許有空值,(在B+TREE中的InnoDB引擎中,主鍵索引起到了至關(guān)重要的地位)
主鍵索引建立的規(guī)則是 int優(yōu)于varchar,一般在建表的時候創(chuàng)建,最好是與表的其他字段不相關(guān)的列或者是業(yè)務不相關(guān)的列.一般會設(shè)為 int 而且是 AUTO_INCREMENT自增類型的
2.組合索引
一個表中含有多個單列索引不代表是組合索引,通俗一點講 組合索引是:包含多個字段但是只有索引名稱
其sql格式是 CREATE INDEX IndexName On TableName(字段名(length),字段名(length),...);
CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);
如果你建立了 組合索引(nickname_account_createdTime_Index) 那么他實際包含的是3個索引 (nickname) (nickname,account)(nickname,account,created_time)
在使用查詢的時候遵循mysql組合索引的"最左前綴",下面我們來分析一下 什么是最左前綴:及索引where時的條件要按照建立索引的時候字段的排序方式
1、不按索引最左列開始查詢（多列索引） 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘a(chǎn)aa’ 不使用索引,where c2 = aaa and c3=sss 不能使用索引
2性昭、查詢中某個列有范圍查詢竿开，則其右邊的所有列都無法使用查詢（多列查詢）
Where c1= ‘xxx’ and c2 like = ‘a(chǎn)a%’ and c3=’sss’ 改查詢只會使用索引中的前兩列,因為like是范圍查詢
3捎琐、不能跳過某個字段來進行查詢,這樣利用不到索引,比如我的sql 是
explain select * from award where nickname > 'rSUQFzpkDz3R' and account = 'DYxJoqZq2rd7' and created_time = 1449567822; 那么這時候他使用不到其組合索引.
因為我的索引是 (nickname, account, created_time),如果第一個字段出現(xiàn) 范圍符號的查找,那么將不會用到索引,如果我是第二個或者第三個字段使用范圍符號的查找,那么他會利用索引,利用的索引是(nickname),
因為上面說了建立組合索引(nickname, account, created_time), 會出現(xiàn)三個索引
(3)全文索引
文本字段上(text)如果建立的是普通索引,那么只有對文本的字段內(nèi)容前面的字符進行索引,其字符大小根據(jù)索引建立索引時申明的大小來規(guī)定.
如果文本中出現(xiàn)多個一樣的字符,而且需要查找的話,那么其條件只能是 where column lick '%xxxx%' 這樣做會讓索引失效
.這個時候全文索引就祈禱了作用了
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引，就可以用SELECT查詢命令去檢索那些包含著一個或多個給定單詞的數(shù)據(jù)記錄了叉庐。
ELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
這條命令將把column1和column2字段里有xxx舒帮、sss和ddd的數(shù)據(jù)記錄全部查詢出來。
(二)索引的刪除
刪除索引的mysql格式 :DORP INDEX IndexName ON TableName
(三)使用索引的優(yōu)點
1.可以通過建立唯一索引或者主鍵索引,保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性.
2.建立索引可以大大提高檢索的數(shù)據(jù),以及減少表的檢索行數(shù)
3.在表連接的連接條件 可以加速表與表直接的相連
4.在分組和排序字句進行數(shù)據(jù)檢索,可以減少查詢時間中 分組 和 排序時所消耗的時間(數(shù)據(jù)庫的記錄會重新排序)
5.建立索引,在查詢中使用索引 可以提高性能
(四)使用索引的缺點
1.在創(chuàng)建索引和維護索引 會耗費時間,隨著數(shù)據(jù)量的增加而增加
2.索引文件會占用物理空間,除了數(shù)據(jù)表需要占用物理空間之外,每一個索引還會占用一定的物理空間
3.當對表的數(shù)據(jù)進行 INSERT,UPDATE,DELETE 的時候,索引也要動態(tài)的維護,這樣就會降低數(shù)據(jù)的維護速度,(建立索引會占用磁盤空間的索引文件陡叠。一般情況這個問題不太嚴重玩郊，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快)枉阵。
(五)使用索引需要注意的地方
在建立索引的時候應該考慮索引應該建立在數(shù)據(jù)庫表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.
一般來說,
1.在經(jīng)常需要搜索的列上,可以加快索引的速度
2.主鍵列上可以確保列的唯一性
3.在表與表的而連接條件上加上索引,可以加快連接查詢的速度
4.在經(jīng)常需要排序(order by),分組(group by)和的distinct 列上加索引 可以加快排序查詢的時間, (單獨order by 用不了索引译红，索引考慮加where 或加limit)
5.在一些where 之后的 < <= > >= BETWEEN IN 以及某個情況下的like 建立字段的索引(B-TREE)
6.like語句的 如果你對nickname字段建立了一個索引.當查詢的時候的語句是 nickname lick '%ABC%' 那么這個索引講不會起到作用.而nickname lick 'ABC%' 那么將可以用到索引
7.索引不會包含NULL列,如果列中包含NULL值都將不會被包含在索引中,復合索引中如果有一列含有NULL值那么這個組合索引都將失效,一般需要給默認值0或者 ' '字符串
8.使用短索引,如果你的一個字段是Char(32)或者int(32),在創(chuàng)建索引的時候指定前綴長度 比如前10個字符 (前提是多數(shù)值是唯一的..)那么短索引可以提高查詢速度,并且可以減少磁盤的空間,也可以減少I/0操作.
9.不要在列上進行運算,這樣會使得mysql索引失效,也會進行全表掃描
10.選擇越小的數(shù)據(jù)類型越好,因為通常越小的數(shù)據(jù)類型通常在磁盤,內(nèi)存,cpu,緩存中 占用的空間很少,處理起來更快
(六)什么情況下不創(chuàng)建索引
1.查詢中很少使用到的列 不應該創(chuàng)建索引,如果建立了索引然而還會降低mysql的性能和增大了空間需求.
2.很少數(shù)據(jù)的列也不應該建立索引,比如 一個性別字段 0或者1,在查詢中,結(jié)果集的數(shù)據(jù)占了表中數(shù)據(jù)行的比例比較大,mysql需要掃描的行數(shù)很多,增加索引,并不能提高效率
3.定義為text和image和bit數(shù)據(jù)類型的列不應該增加索引,
4.當表的修改(UPDATE,INSERT,DELETE)操作遠遠大于檢索(SELECT)操作時不應該創(chuàng)建索引,這兩個操作是互斥的關(guān)系
