關於mysql的索引

標籤：mysql索引

我們先來瞭解MYSQL索引的概念：

       索引是一種特殊的檔案，他們包含著對資料表裡所有記錄的引用指標。更通俗地說，資料庫索引好比是是一本書前面的目錄，能加快資料庫的查詢速度，我們有了相應的索引後，資料庫會直接在索引中尋找合格選項。索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照資料存放的物理位置為順序的，而非聚簇索引就不一樣了，聚簇索引能提高多行檢索的速度，而非聚簇索引對單行的檢索很快。

mysql主要有兩種結構：B+樹和hash

        hash索引在mysql中較少用，它以把資料的索引以hash形式組織起來，因此當尋找某一條記錄的時候，速度非常快。因為是hash結構，每個鍵只對應一個值，而且是散列的方式分布，所以它並不支援範圍尋找和排序等功能。

        B+樹：B+樹是mysql使用最頻繁的一個索引資料結構，資料結構以平衡樹的形式來組織，因為是樹形結構，所以更適合用來處理排序，範圍尋找等功能。相對hash索引，B+樹在尋找單條索引的速度上雖然比不上hash索引，但是因為更適合排序等操作，所以更受使用者的歡迎。

MYSQL索引的類型：

1. 普通索引：
   

             這是最基本的索引，它沒有任何限制，它有以下幾種建立方式：

       （1）建立索引：

                       CREATE INDEX indexName ON mytable(username(length));

        （2）修改表結果：

                       ALTER mytable ADD INDEX [indexName] ON (username(length))

                         CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   ); 

                       刪除索引：

                        DROP INDEX index_name ON table

2.唯一索引：

          與前面的普通索引類似，不同的是：索引列的值必須唯一，但允許有空值，如果是複合式索引，則列值的組合必須唯一。它有以下幾種建立方式：

          （1）建立索引：

                          CREATE UNIQUE INDEX indexname ON mytable（username（length））

           （2）修改表結構：

                           ALTER mytable ADD UNIQUE[indexName] ON(username（length）)

            （3）建立表的時候直接指定：

                            CREATE TABLE mytable（ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName]（username（length）））

3.主鍵索引：

          這是一種特殊的唯一索引，不允許有空值，一般是在建表的時候，建立主鍵索引：

                 CREATE TABLE mytable （ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID)）;

                  這裡要注意，一個表只能有一個主鍵。

4.複合式索引：

          為了形象對比單列索引和複合式索引，為表添加多個欄位：

                CREATE TABLE mytable（ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,age INT NOT NULL）;

          為了進一步榨取mysql的效率，就要考慮建立複合式索引，就是將上面的name，city，age建到一個索引裡：

                ALTER TABLE mytable ADD INDEX name_ciry_age（name（10），city，age）

          我們建表時username長度為16，這裡用10，這是因為一般情況下名字的長度不會超過10，這樣會加速索引尋找速度，還會減少索引檔案的大小，提高insert的更新速度。如果分別在username，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的複合式索引效率也會不打一樣，遠遠低於我們的複合式索引。

 使用索引的注意事項：

        1.索引不會包含有NULL值的列：只要列中包含有NULL值，都將不會被包含在mysql索引中，符合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的，所以我們在資料庫設計時不要讓欄位的預設值為NULL。

        2.使用短索引：對串列進行索引，如果可能應該指定一個前置長度。短索引不僅可以提高查詢速度，而且可以節省磁碟空間和I/O操作。

        3.索引列排序：mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下，不要使用排序操作，盡量不要包含多個列的排序操作，如果需要最好給這些列建立複合索引。

        4.不要在列上進行運算，這將導致索引失效而進行全表掃描。

mysql各種索引區別：

       普通索引：最基本的索引，沒有任何限制；

       唯一索引：與普通索引類似，不同的是，索引列的值必須唯一，但允許有空值；

       主鍵索引：它是一種特殊的唯一索引，不允許有空值；

       全文索引：僅可用於MYISAM表，針對較大的資料，產生全文索引，很耗時耗空間；

       複合式索引：為了更高的提高mysql效率可建立複合式索引，遵循“最左首碼”原則。

      

             

關於mysql的索引