如何創建MySQL索引

本文導航

本文介紹了創建mysql 索引，及其優、缺點。針對MySQL索引的特點、應用進行了詳細的描述。分析了如何避免MySQL無法使用，如何使用EXPLAIN分析查詢語句，如何優化MySQL索引的應用。

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表裡所有記錄的引用指針。

注：

[1]索引不是萬能的!

索引可以加快數據檢索操作，但會使數據修改操作變慢。每修改數據記錄，索引就必須刷新一次。為了在某種程序上彌補這一缺陷，許多SQL命令都有一個 DELAY_KEY_WRITE項。這個選項的作用是暫時制止MySQL在該命令每插入一條新記錄和每修改一條現有之後立刻對索引進行刷新，對索引的刷新 將等到全部記錄插入/修改完畢之後再進行。在需要把許多新記錄插入某個數據表的場合，DELAY_KEY_WRITE選項的作用將非常明顯。

[2]另外，索引還會在硬盤上占用相當大的空間。

因此應該只為最經常查詢和最經常排序的數據列建立索引。注意，如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。

從理論上講，完全可以為數據表裡的每個字段分別建一個索引，但MySQL把同一個數據表裡的索引總數限制為16個。

1. InnoDB數據表的索引

與MyISAM數據表相比，索引對InnoDB數據的重要性要大得多。在InnoDB數據表上，索引對InnoDB數據表的重要性要在得多。在 InnoDB數據表上，索引不僅會在搜索數據記錄時發揮作用，還是數據行級鎖定機制的苊、基礎。”數據行級鎖定”的意思是指在事務操作的執行過程中鎖定正 在被處理的個別記錄，不讓其他用戶進行訪問。這種鎖定將影響到(但不限於)SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE命令以及INSERT、UPDATE和DELETE命令。

出於效率方面的考慮，InnoDB數據表的數據行級鎖定實際發生在它們的索引上，而不是數據表自身上。顯然，數據行級鎖定機制只有在有關的數據表有一個合適的索引可供鎖定的時候才能發揮效力。

2. 限制

如果WEHERE子句的查詢條件裡有不等號(WHERE coloum != …)，MySQL將無法使用索引。

類似地，如果WHERE子句的查詢條件裡使用了函數(WHERE DAY(column) = …)，MySQL也將無法使用索引。

在JOIN操作中(需要從多個數據表提取數據時)，MySQL只有在主鍵和外鍵的數據類型相同時才能使用索引。

如果WHERE子句的查詢條件裡使用比較操作符LIKE和REGEXP，MySQL只有在搜索模板的第一個字符不是通配符的情況下才能使用索引。比 如說，如果查詢條件是LIKE ‘abc%’，MySQL將使用索引;如果查詢條件是LIKE ‘�c’，MySQL將不使用索引。

在ORDER BY操作中，MySQL只有在排序條件不是一個查詢條件表達式的情況下才使用索引。(雖然如此，在涉及多個數據表查詢裡，即使有索引可用，那些索引在加快ORDER BY方面也沒什麼作用)

如果某個數據列裡包含許多重復的值，就算為它建立了索引也不會有很好的效果。比如說，如果某個數據列裡包含的淨是些諸如”0/1″或”Y/N”等值，就沒有必要為它創建一個索引。

二，優化mysql：

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

skip-locking

key_buffer = 16K

max_allowed_packet = 1M

table_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 64K

skip-bdb

skip-innodb

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

#safe-updates

[isamchk]

key_buffer = 8M

sort_buffer_size = 8M

[myisamchk]

key_buffer = 8M

sort_buffer_size = 8M

[mysqlhotcopy]

interactive-timeout

一、索引

1、索引分單列索引和組合索引

單列索引：即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。

組合索引：即一個索包含多個列。

2，復合索引

比如有一條語句是這樣的：select * from users where area=’beijing’ and age=22;

如 果我們是在area和age上分別創建單個索引的話，由於mysql查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效率， 但是如果在area、age兩列上創建復合索引的話將帶來更高的效率。如果我們創建了(area, age, salary)的復合索引，那麼其實相當於創建了(area,age,salary)、(area,age)、(area)三個索引，這被稱為最佳左前綴 特性。因此我們在創建復合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。

3，索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那麼這一列對於此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL。

4，使用短索引

對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

5，排序的索引問題

mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作;盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。

6，like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “�a%” 不會使用索引而like “aaa%”可以使用索引。

7，不要在列上進行運算

select * from users where YEAR(adddate)<2007;將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成select * from users where adddate<‘2007-01-01’;

8，不使用NOT IN和<>操作

NOT IN和<>操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可使用id>3 or id<3來代替。

二、全文索引

文本字段上的普通索引只能加快對出現在字段內容最前面的字符串(也就是字段內容開頭的字符)進行檢索操作。如果字段裡存放的是由幾個、甚至是多個單 詞構成的較大段文字，普通索引就沒什麼作用了。這種檢索往往以LIKE %word%的形式出現，這對MySQL來說很復雜，如果需要處理的數據量很大，響應時間就會很長。

這類場合正是全文索引(full-text index)可以大顯身手的地方。在生成這種類型的索引時，MySQL將把在文本中出現的所有單詞創建為一份清單，查詢操作將根據這份清單去檢索有關的數 據記錄。全文索引即可以隨數據表一同創建，也可以等日後有必要時再使用下面這條命令添加：ALTER TABLE tablename ADD FULLTEXT(column1, column2) 有了全文索引，就可以用SELECT查詢命令去檢索那些包含著一個或多個給定單詞的數據記錄了。

下面是這類查詢命令的基本語法：

SELECT * FROM tablename WHERE MATCH(column1, column2) AGAINST(’word1&prime;, ‘word2&prime;, ‘word3&prime;)

上面這條命令將把column1和column2字段裡有word1、word2和word3的數據記錄全部查詢出來。

注解：InnoDB數據表不支持全文索引。