MySQL在建立資料表的時候建立索引

標籤：

 轉載：http://www.baike369.com/content/?id=5478MySQL在建立資料表的時候建立索引

在MySQL中建立表的時候，可以直接建立索引。基本的文法格式如下：

CREATE TABLE 表名(欄位名 資料類型 [完整性條件約束條件],                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY                  [索引名](欄位名1 [(長度)] [ASC | DESC]));

• UNIQUE：可選。表示索引為唯一性索引。
• FULLTEXT；可選。表示索引為全文索引。
• SPATIAL：可選。表示索引為空白間索引。
• INDEX和KEY：用於指定欄位為索引，兩者選擇其中之一就可以了，作用是一樣的。
• 索引名：可選。給建立的索引取一個新名稱。
• 欄位名1：指定索引對應的欄位的名稱，該欄位必須是前面定義好的欄位。
• 長度：可選。指索引的長度，必須是字串類型才可以使用。
• ASC：可選。表示升序排列。
• DESC：可選。表示降序排列。

MySQL建立普通索引

建立一個普通索引時，不需要加任何UNIQUE、FULLTEXT或者SPATIAL參數。

執行個體：建立一個名為index1的資料表，在表內的id欄位上建立一個普通索引。

1. 建立普通索引的SQL代碼如下：

CREATE TABLE index1(id INT,                    name VARCHAR(20),                    sex BOOLEAN,                    INDEX(id));

          在DOS提示符視窗中查看MySQL建立普通索引的操作效果。如所示：

           

           從中可以看出，運行結果顯示普通索引建立成功。

2. 使用SHOW CREATE TABLE語句查看錶的結構。如所示：

               

              從中可以看出，在id欄位上已經建立了一個名為id的普通索引。

      語句：

      KEY `id` (`id`)

圓括弧內的id是欄位名稱，圓括弧左側外面的id是索引名稱。

3. 使用EXPLAIN語句查看索引是否被使用。SQL代碼如下：

    EXPLAIN SELECT * FROM index1 where id=1 \G

在DOS提示符視窗中查看使用EXPLAIN語句查看索引是否被使用的操作效果。如所示：

        

      中的結果顯示，possible_keys和key的值都為id。說明id索引已經存在，並且查詢時已經使用了索引。

MySQL建立唯一性索引

如果使用UNIQUE參數進行約束，則可以建立唯一性索引。

執行個體：建立一個名為index2的資料表，在表內的id欄位上建立一個唯一性索引，並且設定id欄位以升序的形式排列。

1. 建立一個唯一性索引的SQL代碼如下：

CREATE TABLE index2(id INT UNIQUE,                    name VARCHAR(20),                    UNIQUE INDEX index2_id(id ASC));

index2_id是為唯一性索引起的一個新名字。

      在DOS提示符視窗中查看MySQL建立唯一性索引的操作效果。如所示：

        

        從中可以看出，運行結果顯示建立成功。

2. 使用SHOW CREATE TABLE語句查看錶的結構。SQL代碼如下：

SHOW CREATE TABLE index2 \G

在DOS提示符視窗中查看使用SHOW CREATE TABLE語句查看錶的結構的效果。如所示：

       

從中可以看出，在id欄位上建立了名為id和index2_id的兩個唯一性索引。這樣做，可以提高資料的查詢速度。

如果在建立index2表時，id欄位沒有進行唯一性結束。如下所示：

CREATE TABLE index2(id INT,                    name VARCHAR(20),                    UNIQUE INDEX index2_id(id ASC));

則也可以在id欄位上成功建立名為index2_id的唯一性索引。但是，這樣可能達不到提高查詢速度的目的。

MySQL建立全文索引

全文索引使用FULLTEXT參數，並且只能在CHAR、VARCHAR或TEXT類型的欄位上建立。

全文索引可以用於全文檢索搜尋。

現在，MyISAM儲存引擎和InnoDB儲存引擎都支援全文索引。

執行個體：建立一個名為index3的資料表，在表中的info欄位上建立名為index3_info的全文索引。

1. 建立全文索引的SQL代碼如下：

CREATE TABLE index3(id INT,                    info VARCHAR(20),                    FULLTEXT INDEX index3_info(info))ENGINE=MyISAM;

如果設定ENGINE=InnoDB，則可以在InnoDB儲存引擎上建立全文索引。

在DOS提示符視窗中查看MySQL建立全文索引的操作效果。如所示：

         

從中可以看出，代碼的執行結果顯示建立成功。

2. 使用SHOW CREATE TABLE語句查看index3資料表的結構。如所示：

從中可以看出，在info欄位上已經建立了一個名為index3_info的全文索引。

注意

我使用的是MySQL 5.6.19版本，已經可以在InnoDB儲存引擎中建立全文索引了。

全文索引非常適合於大型資料集，對於小的資料集，它的用處可能比較小。

MySQL建立單列索引

單列索引是在資料表的單個欄位上建立的索引。一個表中可以建立多個單列索引。唯一性索引和普通索引等都為單列索引。

執行個體：建立一個名為index4的資料表，在表中的subject欄位上建立名為index4_st的單列索引。

1. 建立單列索引的SQL代碼如下：

CREATE TABLE index4(id INT,                    subject VARCHAR(30),                    INDEX index4_st(subject(10)));

在DOS提示符視窗中查看MySQL建立單列索引的操作效果。如所示：

       

從中可以看出，代碼執行的結果顯示建立成功。

2. 使用SHOW CREATE TABLE語句查看index4資料表的結構。如所示：

          

從中可以看出，在subject欄位上已經建立了一個名為index4_st的單列索引。

注意：subject欄位長度為30，而index4_st設定的索引長度只有10，這樣做是為了提高查詢速度。對於字元型的資料，可以不用查詢全部資訊，而只查詢它前面的若干字元資訊。

MySQL建立多列索引

建立多列索引是在表的多個欄位上建立一個索引。

執行個體：建立一個名為index5的資料表，在表中的name和sex欄位上建立名為index5_ns的多列索引。

1. 建立多列索引的SQL代碼如下：

CREATE TABLE index5(id INT,                    name VARCHAR(20),                    sex CHAR(4),                    INDEX index5_ns(name,sex));

在DOS提示符視窗中查看MySQL建立多列索引的操作效果。如所示：

        

從中可以看出，代碼的執行結果顯示index5_ns索引建立成功。

2. 使用SHOW CREATE TABLE語句查看index5資料表的結構。如所示：

         

從中可以看出，name和sex欄位上已經建立了一個名為index5_ns的多列索引。

3. 多列索引中，只有查詢條件中使用了這些欄位中第一個欄位時，索引才會被使用。

先在index5資料表中添加一些資料記錄，然後使用EXPLAIN語句可以查看索引的使用方式。如果只是使用name欄位作為查詢條件進行查詢。如所示：

          

從中可以看出，possible_keys和key的值都是index5_ns。Extra（額外資訊）顯示正在使用索引。這說明使用name欄位進行索引時，索引index5_ns已經被使用。

4. 如果只使用sex欄位作為查詢條件進行查詢。如所示：

           

從中可以看出，possible_keys和key的值都是NULL。Extra（額外資訊）顯示正在使用where條件查詢，而未使用索引。

提示

使用多列索引時一定要特別注意，只有使用了索引中的第一個欄位時才會觸發索引。如果沒有使用索引中的第一個欄位，那麼這個多列索引就不會起作用。因此，在最佳化查詢速度時，可以考慮最佳化多列索引。

MySQL建立空間索引

使用SPATIAL參數能夠建立空間索引。建立空間索引時，表的儲存引擎必須是MyISAM類型。而且，索引欄位必須有非空約束。

執行個體：建立一個名為index6的資料表，在表中的space欄位上建立名為index6_sp的空間索引。

1. 建立空間索引的SQL代碼如下：

CREATE TABLE index6(id INT,                    space GEOMETRY NOT NULL,                    SPATIAL INDEX index6_sp(space))ENGINE=MyISAM;

在DOS提示符視窗中查看MySQL建立空間索引的操作效果。如所示：

       

從可以看出，代碼執行的結果顯示空間索引建立成功。

2. 使用SHOW CREATE TABLE語句可看index6資料表的結構。如所示：

        

從中可以看出，在space欄位上已經建立了一個名為index6_sp的空間索引。

注意，space欄位是非空的，而且資料類型是GEOMETRY類型。這個類型是空間資料類型。

空間資料類型包括GEOMETRY、POINT、LINESTRING和POLYGON類型等。這些空間資料類型平時很少用到

MySQL在建立資料表的時候建立索引