[精讚] [會員登入]
1692

[Mysql] 使用索引來加速搜尋

只要是常常用到搜索條件的欄位,就應該把它設成索引鍵,有無設定的速度差可能會好幾倍

此文完整連結 http://n.sfs.tw/10376

複製連結 [Mysql] 使用索引來加速搜尋@新精讚
(文章歡迎轉載,務必尊重版權註明連結來源)
2016-12-05 11:40:14 最後編修
2016-12-05 11:40:14 By 瘦河馬
 

資料庫會有主鍵,唯一鍵和索引鍵,前兩個是唯一不可重覆;索引鍵可以重覆。這篇提到索引鍵的用法。

一、把常用索引的值設成「索引鍵」加速搜尋

只要是常常用到搜索條件的欄位,就應該把它設成索引鍵,有無設定的速度差可能會好幾倍。

例如把`type`欄位設成索引鍵

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

爾後搜尋條件有限制type時,搜尋速度會大幅提升。

SELECT * FROM `attribute` WHERE `type`='xxxx';

建議:主鍵索引欄位最好是數字型態,千萬不要用其他的型態,以免傷了效能。

要刪除索引用drop:
ALTER TABLE `attribute` DROP INDEX `type`

查看索引,可以用三種方法:
SHOW {INDEX | INDEXES | KEYS} from `attribute`;

也可以直接查看資料庫的索引
SHOW {INDEX | INDEXES | KEYS} from `dbname`;

二、建立組合索引鍵

上面說的範例是單欄位索引,可以建立多欄位的索引,多欄位的索引使用上比較不一樣。

例如把`type`和`tid`欄位設成索引鍵,給他一個名稱叫combo。
ALTER
TABLE `attribute` ADD INDEX `combo` ( `type` , `tid` )

接下來搜尋時就要強調用combo這個索引。
SELECT * FROM attribute use INDEX (combo)   WHERE type=1 AND tid=2

如果有多個索引重覆欄位不同,有時可能會要指定不使用某個索引,可用IGNORE
SELECT * FROM attribute ignore INDEX (combo)   WHERE type=1 AND tid=2

討論

以上面為例,如果設兩個單鍵索引 type 和 tid,再設一個索組合引combo( `type` , `tid` ) ,單鍵索引和多鍵索引哪個比較快?

當然是多鍵索引快,不然就用單鍵索引代替就好,何必再用組合索引呢?官網有提到[1]

1. Mysql 自動會使用索引,不需要特別指定使用哪一個索引,除非你指定。例如你同時有 col1 和 col2 的單欄索引:
   SELECT * FROM table WHERE col1=1 AND col2=2
  上面這個查詢會自動使用最左邊的(col1)當索引,除非你指定col2當關鍵索引。
   SELECT * FROM table use INDEX( col2 ) WHERE col1=1 AND col2=2

2. 如果你有多欄索引查詢單欄資料,多欄索引中最左邊的欄位一定要出現在條件中,才會使用索引,例如三欄組合索引(col1, col2, col3):

以下兩個查詢會用到索引
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

以下兩個查詢不會用到索引,請注意條件中 col2並非索引的第一個欄位
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

3. 如果同時有col1, col2, (col1,col2) 三個索引,在查詢時會使用哪一個當索引[2]
這是一個好問題,答案是Mysql會採用索引優化演算(Index Merge optimization)[2]大部分時候(col1,col2)會優先使用,端看你的條件而定。

參考資料

[1] 8.5.3 How MySQL Uses Indexes (EN) http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

[2] 8.3.1.4 Index Merge Optimization (EN) http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html


原文 2014-08-25 11:58:44

你可能感興趣的文章

[Mysql/Mariadb] 利用phpMyAdmin 建立關聯式資料表 利用phpMyAdmin 建立關聯式資料表

[Mysql] Mysqldump 的預設編碼 --default-character-set 有時Mysqldump 也得要指定編碼才不會造成匯出的亂碼

[Mysql/MariaDB] 查看資料庫所占空間 查看資料庫在磁碟中所占空間

[MySQL/Mariadb] console 將查詢結果輸出到檔案 在mysql 的console中該如何將查詢結果輸出到檔案?

[Mysql] 建立叢集式資料庫4/4--觀察及測試 觀察及測試haproxy+galera建立Mysql的叢集式資料庫,並進行壓力測試

[Mysql/MariaDB] 修改資料庫、表格或欄位名稱 Mysql/MariaDB修改資料庫、表格或欄位名稱

[Mysql/Mariadb] 建立使用者及資料庫 Mysql/Mariadb建立使用者、刪除使用者、給予權限、建立資料庫

[Mysql/MariaDB] 使用 LOCATE, POSITION, INSTR來取代 like [Mysql/MariaDB中使用LOCATE, POSITION, INSTR來取代 like的方法

[MYSQL] 設定字串欄位的預設值 新增表格的時候,字串欄位給予預設空值;數字欄位給預設數值;日期欄位給空值。

[Mysql/Mariadb] GROUP_CONCAT 函數 MySQL 中和 group by 一起用的函式,如果我們要算數值的總和或平均,可以使用 sum() 或 avg() 。但是我們如果要在 group by 中把字串「黏起來」,不是使用 concat(

我有話要說


限制:留言最高字數1000字,超過部分會被截掉。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

安裝SPHINX支援中文 新版本的 sphinx 和舊版不同,網路上很多範例和教學是不能用的。此文是安裝和設定方法分享

PHP for sphinx 函式庫安裝 PECL/sphinx PHP>= 5.2.2 已經能原生支援 sphinx,可是預設的沒有裝,我們得自己裝才能用

世紀帝國征服者新版本--被遺忘的帝國 世紀帝國征服者新版本--被遺忘的帝國 世紀二代的征服者是精典遊戲中的精典,aofe更好玩...

[AS3] 我做的唯一一個Flash As3遊戲UFO INVADSION [AS3] 我做的唯一一個Flash As3遊戲,是第一個也是最後一個,後來就沒再寫as3,不過as3還滿好玩的。

問問題 問問題其實內涵很深,我悟了很久才懂。 有人問題的目的並不一定是想要得到答案,有時只是純粹想問問題..