這個觀念我很久之前就知道了,只是一直沒有時間寫出來。

 

索引的概念就像是書本上的目錄。

你再找一本書的資料要快,就是先看前面的目錄。

這樣才能快速找到你要的資訊。

不然就是一頁一頁慢慢翻或是靠記憶去翻。

 

一般MYSQL在建立table的時候,如果你有設定主鍵,他預設的時候就會先用主鍵建立一個索引。

 

索引的類型

UNIQUE=>唯一索引

不可以出現相同的值,可以有NULL值。

INDEX=>普通索引

允許出現相同的索引内容。

PRIMARY KEY=>主键索引

不允許出現相同的值,且不能為NULL值,一個表只能有一個primary_key索引。

fulltext index=>全文索引

上述三种索引都是針對列的值發揮作用,但全文索引,可以針對值中的某個單詞,比如一篇文章中的某個詞,然而並没有什么鳥用,因為只有myisam以及英文支持,且效率讓人不敢恭维,但是可以用coreseek和xunsearch等第三方應用來完成這個需求。

 

其實看完我上面的描述,你大概可以了解到。

基本上我只用UNIQUE INDEX PRIMARY 這三個。

我個人經驗全文撿索基本上他很吃資源,而且效能會吃掉很多,所以我沒在用。

 

剛剛上文有提到Mysql 建立table的時候,就會建立一個 PRIMARY 的索引。

 

建立索引的公式

ALTER TABLE `資料庫table` ADD INDEX `索引名稱` (`col_1`, `col_2`.......);

 

 

 

你有一個table 叫 attribute

當中有這個幾欄位 sys_id,type,name,uid,phone

你有一段SQL查詢語法

SELECT * FROM `attribute` WHERE `type`='xxxx' AND `name`='yyyyy' AND  `uid`='yyyyy'

正常來說你要加速他的查詢,你應該要建立一個索引使用到 type,name,uid,索引的名稱叫 test_index

ALTER TABLE `attribute` ADD INDEX `test_index` (`type`, `name`,`uid`);

基本上這個就能加速你的資料庫查詢速度。

 

但是這邊有幾個討論的點。

1 如果我把

ALTER TABLE `attribute` ADD INDEX `test_index` (`type`, `name`,`uid`);

改成 ALTER TABLE `attribute` ADD INDEX `test_index` (`name`,`uid` , `type`);

其實在理論上市有差別的,我不敢很肯定,因為我自己感受不出來。但是就先前的文章看來的資訊。

如果你查詢條件先後是 type,name,uid。

你建立 (`type`, `name`,`uid`) 查詢的效能會比 (`name`,`uid` , `type`) 好,但是因為我感受不太出來。

但是我還是參照之前看的文件資料來建立。

 

2.有人提出一個想法,我把每個欄位都建立一個索引呢?這樣會比較快嗎?

也就是說你建立三個索引

ALTER TABLE `attribute` ADD INDEX `test_index_1` (`name`);

ALTER TABLE `attribute` ADD INDEX `test_index_2` (`uid`);

ALTER TABLE `attribute` ADD INDEX `test_index_3` ( `type`);

這樣的效能會比

ALTER TABLE `attribute` ADD INDEX `test_index` (`type`, `name`,`uid`);

還要好嗎?

就我個人實測的部分,會稍微好一點點,但是不會比較好。

因為mysql 會依照內部的程式去判斷使用哪一個索引。

而不會取把三個索引都叫來使用。

 

3.按照上面的說法,那是不是我把全部的欄位建立一個索引所有的搜尋都會變很快。

ALTER TABLE `attribute` ADD INDEX `test_index` (`sys_id`,`type`, `name`,`uid`,`phone`);

不完全錯,但是因為你每建立一個索引當你資料庫有異動的時候,他索引資料也跟著異動,會減低你的insert效能。

另外建立多餘的索引反而容易吃掉你很多的硬碟空間。

 

 

我個人的習慣,系統資料庫剛建立好,通常我不會急著去建立索引,我都會先讓系統上去一段時間。

因為我程式有完整的log記錄,我會去看狀況去調整,去建立。

 

另外網路上看到的技巧,他寫的也不錯,作為參考

建立索引的技巧

1.維度高的列建立索引

     資料列中 不重複值 出現的個數,這個數量越高,維度就越高

     如資料表中存在8行資料a ,b ,c,d,a,b,c,d這個表的維度為4

     要為維度高的列建立索引,如性別和年齡,那年齡的維度就高於性別

     性別這樣的列不適合建立索引,因為維度過低。

     =>撿單翻譯一下,就是這個欄位的值差異度越大的越適合去建立索引。

2.對 where,on,group by,order by 中出現的列使用索引 =>這個我就不解釋了,你知道的。

3.對較小的資料列使用索引,這樣會使索引檔案更小,同時記憶體中也可以裝載更多的索引鍵

     =>這點我就覺得沒什麼感覺,我就是要位系統加速,我這個不太在乎他說的這點。

4.為較長的字串使用字首索引

5.不要過多建立索引,除了增加額外的磁碟空間外,對於DML操作的速度影響很大,因為其每增刪改一次就得從新建立索引

6.使用組合索引,可以減少檔案索引大小,在使用時速度要優於多個單列索引=>恩這個上面我有解釋了。

參考來源:http://www.runoob.com/w3cnote/mysql-index.html

文章標籤

全站熱搜

阿基 發表在 痞客邦 留言(0) 人氣()