MySQL Index详解
①M(fèi)ySQL?Index
一、SHOW INDEX會(huì)返回以下字段
1、Table 表的名稱(chēng)。
2、 Non_unique 如果索引不能包括重復(fù)詞,則為0,如果可以則為1。
3、 Key_name 索引的名稱(chēng)
4、 Seq_in_index 索引中的列序列號(hào)，從1開(kāi)始。
5、 Column_name 列名稱(chēng)。
6、 Collation 列以什么方式存儲(chǔ)在索引中。在MySQL中，有值‘A’（升序）或NULL（無(wú)分類(lèi)）。
7、Cardinality 索引中唯一值的數(shù)目的估計(jì)值。通過(guò)運(yùn)行ANALYZE TABLE或myisamchk -a可以更新。基數(shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)來(lái)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒(méi)有必要是精確的。基數(shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL使用該索引的機(jī)會(huì)就越大。
8、Sub_part 如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為NULL。
9、 Packed 指示關(guān)鍵字如何被壓縮。如果沒(méi)有被壓縮，則為NULL。
10、 Null 如果列含有NULL，則含有YES。如果沒(méi)有，則該列含有NO。
11、 Index_type 用過(guò)的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
12、 Comment 多種評(píng)注，您可以使用db_name.tbl_name作為tbl_name FROM db_name語(yǔ)法的另一種形式。這兩個(gè)語(yǔ)句是等價(jià)的：
mysql>SHOW INDEX FROM mytable FROM mydb;
mysql>SHOW INDEX FROM mydb.mytable;
二、使用 CREATE INDEX 創(chuàng)建索引
如果要為已存在的表創(chuàng)建索引，就需要使用 CREATE INDEX 命令或 ALTER TABLE 來(lái)創(chuàng)建索引。
CREATEINDEX idxtitle ON tablename (title)
這條命令將創(chuàng)建與 CREATE TABLE 命令相同的索引。
三、使用 ALTER TABLE 創(chuàng)建索引
ALTER TABLE tablename ADD INDEX idxtitle (title)
四、查看和刪除索引
我們可以使用以下命令來(lái)查看已存在的索引：
SHOW INDEX FROM tablename
DROP INDEX indexname ON tablename
DROP INDEX 將刪除 tablename 表上的 indexname 索引，indexname 就是我們創(chuàng)建表時(shí)指定的索引名。
########################################
createtable c(id char(20) not null primary key,`index` char(10));
問(wèn)題1.如果我忘記把id設(shè)為主鍵現(xiàn)在該怎么辦？輸入什么命令？
ALTER TABLE record?
ADD PRIMARY KEY (id);?
例如：ALTER TABLE `abc` ADD PRIMARY KEY(`a`)；?
問(wèn)題2.如果我想把id設(shè)為主鍵，同時(shí)想把name改為not null，且改為varchar(10),能不能用一句命令完成問(wèn)題2？
ALTER TABLE record
ADD PRIMARY KEY (id),
CHANGEname varchar(10) not null;
########################################
索引 mysql index
? ? ? ? 索引是一種數(shù)據(jù)結(jié)構(gòu)，可以是B-tree, R-tree, 或者 hash 結(jié)構(gòu)。其中R-tree 常用于查詢(xún)比較接近的數(shù)據(jù)；B-trees適合用于查找某范圍內(nèi)的數(shù)據(jù)，可以很快的從當(dāng)前數(shù)據(jù)找到下條數(shù)據(jù)；hash結(jié)構(gòu)則適用于隨機(jī)訪問(wèn)的場(chǎng)合，查找每條數(shù)據(jù)的時(shí)間幾乎相同。顯然，若要查找某個(gè)時(shí)間段的數(shù)據(jù)，用B-tree結(jié)構(gòu)要比hash結(jié)構(gòu)快好多。??
?????? 優(yōu)化查詢(xún)的有效方法是為經(jīng)常查詢(xún)的字段建立索引，如無(wú)索引查詢(xún)數(shù)據(jù)時(shí)，會(huì)遍歷整張表(多么恐怖啊);若有了索引查找會(huì)容易很多。當(dāng)進(jìn)行 UPDATE, DELETE, 以及 INSERT 操作時(shí)，mysql會(huì)自動(dòng)更新索引信息。
1.創(chuàng)建和刪除索引( Creating and dropping indexes )
創(chuàng)建：
PS:當(dāng)用 create index 創(chuàng)建索引時(shí),必須指定索引的名字，否則mysql會(huì)報(bào)錯(cuò)；
? 用 ALTER TABLE 創(chuàng)建索引時(shí)，可以不指定索引名字，若不指定mysql會(huì)自動(dòng)生成索引名字
??
建立索引時(shí)，若不想用存儲(chǔ)引擎的默認(rèn)索引類(lèi)型，可以指定索引的類(lèi)型:
刪除：
[css]?view plaincopy
2.索引類(lèi)型
BTREE ???適合連續(xù)讀取數(shù)據(jù)
RTREE ???適合根據(jù)一條數(shù)據(jù)找附近的數(shù)據(jù)
HASH????? 適合隨機(jī)讀取數(shù)據(jù)
FULLTEXT????
SPATIAL
?查看某個(gè)表中存在的索引類(lèi)型
[css]?view plaincopy3.索引冗余
如果兩個(gè)或者多個(gè)索引包含了相同的索引信息，那么就存在索引冗余。
不同類(lèi)型的索引有不同的索引冗余判斷:
(1) SPATIAL 索引
SPATIAL 只能是一個(gè)簡(jiǎn)單索引，不能說(shuō)復(fù)合索引；存在冗余的情況是同一個(gè)字段有兩個(gè)索引。
(2) FULLTEXT 索引
若一個(gè)FULLTEXT索引是另一個(gè)索引的子集(不考慮字段順序),則存在冗余。
例如: 表中包含如下兩個(gè)索引?
■ (field1)
■ (field1, field2)
(3) HASH 索引
若一個(gè)索引在不考慮字段順序的情況下，索引字段相同，則存在冗余。
例如：表中包含如下兩個(gè)索引?
■ (field1, field2, field3)
■ (field1, field3, field2)
(4) BTREE 索引
若一個(gè)索引是另一個(gè)索引的子集(考慮字段順序),則存在冗余。
例如：表中包含如下兩個(gè)索引?
■ (field2)
■ (field2, field1)
PS：若兩個(gè)或多個(gè)索引有相同的索引字段和字段順序，但是有不同的索引類(lèi)型，這樣的索引是不冗余的。因?yàn)槊糠N索引類(lèi)型有自己的索引值。
######################################
深入MySQL數(shù)據(jù)庫(kù)的索引?
摘要：本文介紹索引的類(lèi)型，已經(jīng)如何創(chuàng)建索引做了介紹，其中涉及三個(gè)比較重要的SQL語(yǔ)句――ALTER TABLE、CREATE/DROP INDEX和CREATE TABLE，注意它們的用法。
索引是加速表內(nèi)容訪問(wèn)的主要手段，特別對(duì)涉及多個(gè)表的連接的查詢(xún)更是如此。這是數(shù)據(jù)庫(kù)優(yōu)化中的一個(gè)重要內(nèi)容，我們要了解為什么需要索引，索引如何工作以及怎樣利用它們來(lái)優(yōu)化查詢(xún)。本節(jié)中，我們將介紹索引的特點(diǎn)，以及創(chuàng)建和刪除索引的語(yǔ)法。?
索引的特點(diǎn)
所有的MySQL列類(lèi)型能被索引。在相關(guān)的列上的使用索引是改進(jìn)SELECT操作性能的最好方法。
一個(gè)表最多可有16個(gè)索引。最大索引長(zhǎng)度是256個(gè)字節(jié)，盡管這可以在編譯MySQL時(shí)被改變。
對(duì)于CHAR和 VARCHAR列，你可以索引列的前綴。這更快并且比索引整個(gè)列需要較少的磁盤(pán)空間。對(duì)于BLOB和TEXT列，你必須索引列的前綴，你不能索引列的全部。
MySQL能在多個(gè)列上創(chuàng)建索引。一個(gè)索引可以由最多15個(gè)列組成。（在CHAR和VARCHAR列上，你也可以使用列的前綴作為一個(gè)索引的部分）。
雖然隨著 MySQL 的進(jìn)一步開(kāi)發(fā)創(chuàng)建索引的約束將會(huì)越來(lái)越少，但現(xiàn)在還是存在一些約束的。下面的表根據(jù)索引的特性，給出了 ISAM 表和 MyISAM 表之間的差別：
表2-1 通道信息特征字對(duì)照表索引的特點(diǎn) ISAM 表 MyISAM 表
NULL 值
BLOB 和 TEXT 列
每個(gè)表中的索引數(shù)
每個(gè)索引中的列數(shù)
最大索引行尺寸
不允許
不能索引
16
16
256 字節(jié) 允許
只能索引列的前綴
32
16
500 字節(jié)
從此表中可以看到，對(duì)于 ISAM 表來(lái)說(shuō)，其索引列必須定義為 NOT NULL，并且不能對(duì) BLOB 和 TEXT 列進(jìn)行索引。MyISAM 表類(lèi)型去掉了這些限制，而且減緩了其他的一些限制。兩種表類(lèi)型的索引特性的差異表明，根據(jù)所使用的 MySQL 版本的不同，有可能對(duì)某些列不能進(jìn)行索引。例如，如果使用3.23 版以前的版本，則不能對(duì)包含 NULL 值的列進(jìn)行索引。
索引有如下的幾種情況：
INDEX索引：通常意義的索引，某些情況下KEY是它的一個(gè)同義詞。索引的列可以包括重復(fù)的值。
UNIQUE索引：唯一索引，保證了列不包含重復(fù)的值，對(duì)于多列唯一索引，它保證值的組合不重復(fù)。
PRIMARY KEY索引：也UNIQUE索引非常類(lèi)似。事實(shí)上，PRIMARYKEY索引僅是一個(gè)具有PRIMARY名稱(chēng)的UNIQUE索引。這表示一個(gè)表只能包含一個(gè)PRIMARY KEY。?
用Alter Table語(yǔ)句創(chuàng)建與刪除索引
為了給現(xiàn)有的表增加一個(gè)索引，可使用 ALTER TABLE 或CREATE INDEX 語(yǔ)句。ALTER TABLE 最常用，因?yàn)榭捎盟鼇?lái)創(chuàng)建普通索引、UNIQUE 索引或 PRIMARY KEY 索引，如：
ALTER TABLE tbl_name ADD INDEX index_name? (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name? (column_list)
ALTER TABLE tbl_name ADD PRIMARY KEY index_name? (column_list)
其中 tbl_name 是要增加索引的表名，而 column_list 指出對(duì)哪些列進(jìn)行索引。一個(gè)(col1,col2,...)形式的列表創(chuàng)造一個(gè)多列索引。索引值有給定列的值串聯(lián)而成。如果索引由不止一列組成，各列名之間用逗號(hào)分隔。索引名 index_name 是可選的，因此可以不寫(xiě)它，MySQL 將根據(jù)第一個(gè)索引列賦給它一個(gè)名稱(chēng)。ALTER TABLE 允許在單個(gè)語(yǔ)句中指定多個(gè)表的更改，因此可以在同時(shí)創(chuàng)建多個(gè)索引。
同樣，也可以用ALTER TABLE語(yǔ)句刪除列的索引：
ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP PRIMARY KEY
注意上面第一條語(yǔ)句可以用來(lái)刪除各種類(lèi)型的索引，而第三條語(yǔ)句只在刪除 PRIMARY KEY 索引時(shí)使用；在此情形中，不需要索引名，因?yàn)橐粋€(gè)表只可能具有一個(gè)這樣的索引。如果沒(méi)有明確地創(chuàng)建作為 PRIMARY KEY 的索引，但該表具有一個(gè)或多個(gè) UNIQUE 索引，則 MySQL 將刪除這些 UNIQUE 索引中的第一個(gè)。
如果從表中刪除了列，則索引可能會(huì)受到影響。如果所刪除的列為索引的組成部分，則該列也會(huì)從索引中刪除。如果組成索引的所有列都被刪除，則整個(gè)索引將被刪除。
例如，對(duì)于上面所使用的student為例，你可能想為之創(chuàng)建這樣的索引，以加速表的檢索速度：
mysql> ALTER TABLE student
-> ADD PRIMARY KEY(id),
-> ADD INDEXmark(english,Chinese,history);
這個(gè)例子，既包括PRIMARY索引，也包括多列索引。記住，使用 PRIMARY索引的列，必須是一個(gè)具有NOT NULL屬性的列，如果你愿意查看創(chuàng)建的索引的情況，可以使用SHOW INDEX語(yǔ)句：
mysql> SHOW INDEX FROM student;
其結(jié)果為：
+---------+------------+----------+--------------+-------------+-
| Table??| Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+-
| student |????????? 0 | PRIMARY ?|???????????1 | id????????? |
| student |????????? 1 | mark???? |??????????? 1 | english???? |
| student |????????? 1 | mark???? |??????????? 2 | chinese???? |
| student |????????? 1 | mark???? |??????????? 3 | history ????|
+---------+------------+----------+--------------+-------------+-
由于列數(shù)太多，上表并沒(méi)有包括所有的輸出，讀者可以試著自己查看。
再使用ALTER TABLE語(yǔ)句刪除索引，刪除索引需要知道索引的名字，你可以通過(guò)SHOW INDEX語(yǔ)句得到：
mysql> ALTER TABLE student DROP PRIMARYKEY,
???-> DROP INDEX mark;
再產(chǎn)看表中的索引，其語(yǔ)句和輸出為：
mysql> SHOW INDEX FROM student;
Empty set (0.01 sec)?
用CREATE\DROP INDEX創(chuàng)建索引
還可以用CREATE INDEX語(yǔ)句來(lái)創(chuàng)建索引.CREATE INDEX 是在 MySQL 3.23版中引入的，但如果使用3.23 版以前的版本，可利用 ALTER TABLE 語(yǔ)句創(chuàng)建索引（MySQL 通常在內(nèi)部將 CREATE INDEX 映射到 ALTER TABLE）。該語(yǔ)句創(chuàng)建索引的語(yǔ)法如下：
CREATE UNIQUE INDEX index_name ON tbl_name(column_list)
CREATE INDEX index_name ON tbl_name(column_list) tbl_name、index_name 和 column_list 具有與 ALTER TABLE 語(yǔ)句中相同的含義。這里索引名不可選。很明顯，CREATE INDEX 可對(duì)表增加普通索引或 UNIQUE 索引，不能用 CREATE INDEX 語(yǔ)句創(chuàng)建 PRIMARY KEY 索引。
可利用 DROP INDEX語(yǔ)句來(lái)刪除索引。類(lèi)似于 CREATE INDEX 語(yǔ)句，DROP INDEX 通常在內(nèi)部作為一條 ALTER TABLE 語(yǔ)句處理，并且DROP INDEX是在 MySQL 3.22 中引入的。
刪除索引語(yǔ)句的語(yǔ)法如下：
DROP INDEX index_name ON tbl_name
還是上一節(jié)的例子，由于CREATE INDEX不能創(chuàng)建PRIMARY索引，所以這里我們只創(chuàng)建一個(gè)多列索引：
mysql> CREATE INDEX mark ONstudent(english,chinese,history);
同樣的檢查student表，可知：
mysql> SHOW INDEX FROM student;
+---------+------------+----------+--------------+-------------+
| Table??| Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+
| student |????????? 1 | mark?? ??|???????????1 | english???? |
| student |????????? 1 | mark???? |??????????? 2 | chinese???? |
| student |????????? 1 | mark???? |??????????? 3 | history???? |
+---------+------------+----------+--------------+-------------+
然后使用下面的語(yǔ)句刪除索引：
mysql> DROP INDEX mark ON student;
在創(chuàng)建表時(shí)指定索引
要想在發(fā)布 CREATE TABLE 語(yǔ)句時(shí)為新表創(chuàng)建索引，所使用的語(yǔ)法類(lèi)似于 ALTER TABLE 語(yǔ)句的語(yǔ)法，但是應(yīng)該在您定義表列的語(yǔ)句部分指定索引創(chuàng)建子句，如下所示：
[sql]?view plaincopy與ALTER TABLE 一樣，索引名對(duì)于 INDEX 和 UNIQUE 都是可選的，如果未給出，MySQL 將為其選一個(gè)。另外，這里KEY時(shí)INDEX的一個(gè)別名，具有相同的意義。
有一種特殊情形：可在列定義之后增加 PRIMARY KEY 創(chuàng)建一個(gè)單列的PRIMARY KEY 索引，如下所示：
[sql]?view plaincopy該語(yǔ)句等價(jià)于以下的語(yǔ)句:
[sql]?view plaincopy前面所有表創(chuàng)建樣例都對(duì)索引列指定了 NOT NULL。如果是 ISAM 表，這是必須的，因?yàn)椴荒軐?duì)可能包含 NULL 值的列進(jìn)行索引。如果是 MyISAM 表，索引列可以為 NULL，只要該索引不是 PRIMARY KEY 索引即可。
在CREATE TBALE語(yǔ)句中可以某個(gè)串列的前綴進(jìn)行索引（列值的最左邊 n 個(gè)字符）。
如果對(duì)某個(gè)串列的前綴進(jìn)行索引，應(yīng)用 column_list 說(shuō)明符表示該列的語(yǔ)法為 col_name(n) 而不用col_name。例如，下面第一條語(yǔ)句創(chuàng)建了一個(gè)具有兩個(gè) CHAR 列的表和一個(gè)由這兩列組成的索引。第二條語(yǔ)句類(lèi)似，但只對(duì)每個(gè)列的前綴進(jìn)行索引：
[sql]?view plaincopy你可以檢查所創(chuàng)建表的索引：
mysql> SHOW INDEX FROM tbl_name;
+----------+------------+----------+--------------+-------------+-
| Table???| Non_unique | Key_name | Seq_in_index | Column_name |
+----------+------------+----------+--------------+-------------+-
| tbl_name |????????? 1 | name???? |??????????? 1 | name??????? |
| tbl_name |????????? 1 | name???? |??????????? 2 | address???? |
+----------+------------+----------+--------------+-------------+-
在某些情況下，可能會(huì)發(fā)現(xiàn)必須對(duì)列的前綴進(jìn)行索引。例如，索引行的長(zhǎng)度有一個(gè)最大上限，因此，如果索引列的長(zhǎng)度超過(guò)了這個(gè)上限，那么就可能需要利用前綴進(jìn)行索引。在 MyISAM 表索引中，對(duì) BLOB 或 TEXT 列也需要前綴索引。
對(duì)一個(gè)列的前綴進(jìn)行索引限制了以后對(duì)該列的更改；不能在不刪除該索引并使用較短前綴的情況下，將該列縮短為一個(gè)長(zhǎng)度小于索引所用前綴的長(zhǎng)度的列。?
總結(jié)
本節(jié)對(duì)索引的類(lèi)型，已經(jīng)如何創(chuàng)建索引做了介紹，其中涉及三個(gè)比較重要的SQL語(yǔ)句――ALTER TABLE、CREATE/DROP INDEX和CREATE TABLE，注意它們的用法。
索引最重要的功能是，通過(guò)使用索引加速表的檢索，有關(guān)這方面的知識(shí)，將在第十章數(shù)據(jù)庫(kù)優(yōu)化中介紹。?
思考題
1、建立一個(gè)如下所述的表：
data：FLOAT列，使用隨機(jī)函數(shù)填充數(shù)據(jù)
birth：DATETIME列，填充當(dāng)前時(shí)間。
然后，請(qǐng)錄入幾條數(shù)據(jù)。最后計(jì)算data列的平均值、總和、極值，并且按照data列降序排序檢索值。
2、分別使用標(biāo)準(zhǔn)SQL模式和擴(kuò)展正規(guī)表達(dá)式模式匹配，匹配上面創(chuàng)建的表，假設(shè)你創(chuàng)建表的當(dāng)前日期為2001-01-01，用模式匹配檢索出birth列包含該日期的值。（實(shí)際上，上面的表中記錄都是同一日期錄入的，因此實(shí)際將返回全部記錄。）
3、為前幾章使用的數(shù)據(jù)表創(chuàng)建索引：
student：為id段創(chuàng)建一個(gè)PRIMARY索引，為english、chinese和history創(chuàng)建一個(gè)多列索引。
pet：為name和owner段創(chuàng)建一個(gè)多類(lèi)索引。
4、刪除為pet表創(chuàng)建的索引。
##################################################
MySQL Index的使用
以下是理論知識(shí)備忘：
一、什么是索引？索引用來(lái)快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹(shù)的形式保存。如果沒(méi)有索引，執(zhí)行查詢(xún)時(shí)MySQL必須從第一個(gè)記錄開(kāi)始掃描整個(gè)表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個(gè)操作的代價(jià)就越高。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無(wú)需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。如果表有1000個(gè)記錄，通過(guò)索引查找記錄至少要比順序掃描記錄快100倍。
假設(shè)我們創(chuàng)建了一個(gè)名為people的表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );
然后，我們完全隨機(jī)把1000個(gè)不同name值插入到people表。下圖顯示了people表所在數(shù)據(jù)文件的一小部分：
可以看到，在數(shù)據(jù)文件中name列沒(méi)有任何明確的次序。如果我們創(chuàng)建了name列的索引，MySQL將在索引中排序name列：
對(duì)于索引中的每一項(xiàng)，MySQL在內(nèi)部為它保存一個(gè)數(shù)據(jù)文件中實(shí)際記錄所在位置的“指針”。因此，如果我們要查找name等于“Mike”記錄的 peopleid（SQL命令為“SELECT peopleid FROM people WHERE name='Mike';”），MySQL能夠在name的索引中查找“Mike”值，然后直接轉(zhuǎn)到數(shù)據(jù)文件中相應(yīng)的行，準(zhǔn)確地返回該行的 peopleid（999）。在這個(gè)過(guò)程中，MySQL只需處理一個(gè)行就可以返回結(jié)果。如果沒(méi)有“name”列的索引，MySQL要掃描數(shù)據(jù)文件中的所有記錄，即1000個(gè)記錄！顯然，需要MySQL處理的記錄數(shù)量越少，則它完成任務(wù)的速度就越快。
二、索引的類(lèi)型
MySQL提供多種索引類(lèi)型供選擇：
普通索引
這是最基本的索引類(lèi)型，而且它沒(méi)有唯一性之類(lèi)的限制。普通索引可以通過(guò)以下幾種方式創(chuàng)建：
創(chuàng)建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
創(chuàng)建表的時(shí)候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
唯一性索引
這種索引和前面的“普通索引”基本相同，但有一個(gè)區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須唯一。唯一性索引可以用以下幾種方式創(chuàng)建：
創(chuàng)建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
創(chuàng)建表的時(shí)候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
主鍵
主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。如果你曾經(jīng)用過(guò)AUTO_INCREMENT類(lèi)型的列，你可能已經(jīng)熟悉主鍵之類(lèi)的概念了。主鍵一般在創(chuàng)建表的時(shí)候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我們也可以通過(guò)修改表的方式加入主鍵，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每個(gè)表只能有一個(gè)主鍵。
全文索引
MySQL從3.23.23版開(kāi)始支持全文索引和全文檢索。在MySQL中，全文索引的索引類(lèi)型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類(lèi)型的列上創(chuàng)建。它可以通過(guò)CREATE TABLE命令創(chuàng)建，也可以通過(guò)ALTER TABLE或CREATE INDEX命令創(chuàng)建。對(duì)于大規(guī)模的數(shù)據(jù)集，通過(guò)ALTER TABLE（或者CREATE INDEX）命令創(chuàng)建全文索引要比把記錄插入帶有全文索引的空表更快。本文下面的討論不再涉及全文索引，要了解更多信息，請(qǐng)參見(jiàn)MySQL documentation。
三、單列索引與多列索引
索引可以是單列索引，也可以是多列索引。下面我們通過(guò)具體的例子來(lái)說(shuō)明這兩種索引的區(qū)別。假設(shè)有這樣一個(gè)people表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );
下面是我們插入到這個(gè)people表的數(shù)據(jù)：
這個(gè)數(shù)據(jù)片段中有四個(gè)名字為“Mikes”的人（其中兩個(gè)姓Sullivans，兩個(gè)姓McConnells），有兩個(gè)年齡為17歲的人，還有一個(gè)名字與眾不同的Joe Smith。
這個(gè)表的主要用途是根據(jù)指定的用戶(hù)姓、名以及年齡返回相應(yīng)的peopleid。例如，我們可能需要查找姓名為Mike Sullivan、年齡17歲用戶(hù)的peopleid（SQL命令為SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;）。由于我們不想讓MySQL每次執(zhí)行查詢(xún)就去掃描整個(gè)表，這里需要考慮運(yùn)用索引。
首先，我們可以考慮在單個(gè)列上創(chuàng)建索引，比如firstname、lastname或者age列。如果我們創(chuàng)建firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname);），MySQL將通過(guò)這個(gè)索引迅速把搜索范圍限制到那些firstname='Mike'的記錄，然后再在這個(gè)“中間結(jié)果集”上進(jìn)行其他條件的搜索：它首先排除那些lastname不等于“Sullivan”的記錄，然后排除那些age不等于17的記錄。當(dāng)記錄滿(mǎn)足所有搜索條件之后，MySQL就返回最終的搜索結(jié)果。
由于建立了firstname列的索引，與執(zhí)行表的完全掃描相比，MySQL的效率提高了很多，但我們要求MySQL掃描的記錄數(shù)量仍舊遠(yuǎn)遠(yuǎn)超過(guò)了實(shí)際所需要的。雖然我們可以刪除firstname列上的索引，再創(chuàng)建lastname或者 age列的索引，但總地看來(lái)，不論在哪個(gè)列上創(chuàng)建索引搜索效率仍舊相似。
為了提高搜索效率，我們需要考慮運(yùn)用多列索引。如果為firstname、lastname和age這三個(gè)列創(chuàng)建一個(gè)多列索引，MySQL只需一次檢索就能夠找出正確的結(jié)果！下面是創(chuàng)建這個(gè)多列索引的SQL命令：
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
由于索引文件以B-樹(shù)格式保存，MySQL能夠立即轉(zhuǎn)到合適的firstname，然后再轉(zhuǎn)到合適的lastname，最后轉(zhuǎn)到合適的age。在沒(méi)有掃描數(shù)據(jù)文件任何一個(gè)記錄的情況下，MySQL就正確地找出了搜索的目標(biāo)記錄！
那么，如果在firstname、lastname、age這三個(gè)列上分別創(chuàng)建單列索引，效果是否和創(chuàng)建一個(gè)firstname、lastname、 age的多列索引一樣呢？答案是否定的，兩者完全不同。當(dāng)我們執(zhí)行查詢(xún)的時(shí)候，MySQL只能使用一個(gè)索引。如果你有三個(gè)單列的索引，MySQL會(huì)試圖選擇一個(gè)限制最嚴(yán)格的索引。但是，即使是限制最嚴(yán)格的單列索引，它的限制能力也肯定遠(yuǎn)遠(yuǎn)低于firstname、lastname、age這三個(gè)列上的多列索引。
四、最左前綴
多列索引還有另外一個(gè)優(yōu)點(diǎn)，它通過(guò)稱(chēng)為最左前綴（Leftmost Prefixing）的概念體現(xiàn)出來(lái)。繼續(xù)考慮前面的例子，現(xiàn)在我們有一個(gè)firstname、lastname、age列上的多列索引，我們稱(chēng)這個(gè)索引為fname_lname_age。當(dāng)搜索條件是以下各種列的組合時(shí)，MySQL將使用fname_lname_age索引：
firstname，lastname，age
firstname，lastname
firstname
從另一方面理解，它相當(dāng)于我們創(chuàng)建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的索引。下面這些查詢(xún)都能夠使用這個(gè)fname_lname_age索引：
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The following queries cannot use the index at all: SELECT peopleid FROM people WHERE lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';
五、選擇索引列
在性能優(yōu)化過(guò)程中，選擇在哪些列上創(chuàng)建索引是最重要的步驟之一。可以考慮使用索引的主要有兩種類(lèi)型的列：在WHERE子句中出現(xiàn)的列，在join子句中出現(xiàn)的列。請(qǐng)看下面這個(gè)查詢(xún)：
SELECT age ## 不使用索引
FROM people WHERE firstname='Mike' ## 考慮使用索引
AND lastname='Sullivan' ## 考慮使用索引
這個(gè)查詢(xún)與前面的查詢(xún)略有不同，但仍屬于簡(jiǎn)單查詢(xún)。由于age是在SELECT部分被引用，MySQL不會(huì)用它來(lái)限制列選擇操作。因此，對(duì)于這個(gè)查詢(xún)來(lái)說(shuō)，創(chuàng)建age列的索引沒(méi)有什么必要。下面是一個(gè)更復(fù)雜的例子：
SELECT people.age, ##不使用索引
town.name ##不使用索引
FROM people LEFT JOIN town ON
people.townid=town.townid ##考慮使用索引
WHERE firstname='Mike' ##考慮使用索引
AND lastname='Sullivan' ##考慮使用索引
與前面的例子一樣，由于firstname和lastname出現(xiàn)在WHERE子句中，因此這兩個(gè)列仍舊有創(chuàng)建索引的必要。除此之外，由于town表的townid列出現(xiàn)在join子句中，因此我們需要考慮創(chuàng)建該列的索引。
那么，我們是否可以簡(jiǎn)單地認(rèn)為應(yīng)該索引WHERE子句和join子句中出現(xiàn)的每一個(gè)列呢？差不多如此，但并不完全。我們還必須考慮到對(duì)列進(jìn)行比較的操作符類(lèi)型。MySQL只有對(duì)以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時(shí)候的LIKE。可以在LIKE操作中使用索引的情形是指另一個(gè)操作數(shù)不是以通配符（%或者_(dá)）開(kāi)頭的情形。例如，“SELECT peopleid FROM peopleWHERE firstname LIKE 'Mich%';”這個(gè)查詢(xún)將使用索引，但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”這個(gè)查詢(xún)不會(huì)使用索引。
六、分析索引效率
現(xiàn)在我們已經(jīng)知道了一些如何選擇索引列的知識(shí)，但還無(wú)法判斷哪一個(gè)最有效。MySQL提供了一個(gè)內(nèi)建的SQL命令幫助
一、 MySQL建表，字段需設(shè)置為非空，需設(shè)置字段默認(rèn)值。
二、 MySQL建表，字段需NULL時(shí)，需設(shè)置字段默認(rèn)值，默認(rèn)值不為NULL。
三、 MySQL建表，如果字段等價(jià)于外鍵，應(yīng)在該字段加索引。
四、 MySQL建表，不同表之間的相同屬性值的字段，列類(lèi)型，類(lèi)型長(zhǎng)度，是否非空，是否默認(rèn)值，需保持一致，否則無(wú)法正確使用索引進(jìn)行關(guān)聯(lián)對(duì)比。
五、 MySQL使用時(shí)，一條SQL語(yǔ)句只能使用一個(gè)表的一個(gè)索引。所有的字段類(lèi)型都可以索引，多列索引的屬性最多15個(gè)。
六、 如果可以在多個(gè)索引中進(jìn)行選擇，MySQL通常使用找到最少行的索引，索引唯一值最高的索引。
七、 建立索引index（part1,part2,part3）,相當(dāng)于建立了 index(part1),index(part1,part2)和index（part1,part2,part3）三個(gè)索引。
八、 MySQL針對(duì)like語(yǔ)法必須如下格式才使用索引：
SELECT * FROM t1 WHERE key_col LIKE 'ab%' ；九、 SELECT COUNT(*) 語(yǔ)法在沒(méi)有where條件的語(yǔ)句中執(zhí)行效率沒(méi)有SELECT COUNT(col_name)快，但是在有where條件的語(yǔ)句中執(zhí)行效率要快。
十、 在where條件中多個(gè)and的條件中，必須都是一個(gè)多列索引的key_part屬性而且必須包含key_part1。各自單一索引的話，只使用遍歷最少行的那個(gè)索引。
十一、 在where條件中多個(gè)or的條件中，每一個(gè)條件，都必須是一個(gè)有效索引。
十二、 ORDER BY 后面的條件必須是同一索引的屬性，排序順序必須一致（比如都是升序或都是降序）。
十三、 所有GROUP BY列引用同一索引的屬性，并且索引必須是按順序保存其關(guān)鍵字的。
十四、 JOIN 索引，所有匹配ON和where的字段應(yīng)建立合適的索引。
十五、 對(duì)智能的掃描全表使用FORCE INDEX告知MySQL，使用索引效率更高。
十六、 定期ANALYZE TABLE tbl_name為掃描的表更新關(guān)鍵字分布 。
十七、 定期使用慢日志檢查語(yǔ)句，執(zhí)行explain，分析可能改進(jìn)的索引。
十八、 條件允許的話，設(shè)置較大的key_buffer_size和query_cache_size的值（全局參數(shù)），和sort_buffer_size的值（session變量，建議不要超過(guò)4M）。
from:?http://blog.csdn.net/tianmohust/article/details/7930482
總結(jié)
以上是生活随笔為你收集整理的MySQL Index详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： IEDA翻译神器
- 下一篇： MYSQL查看执行计划 Explain