一般可分為四類：
- 普通索引：最普通的索引
- 唯一索引：索引列的值必須唯一，但允許有空值
- 主鍵索引：一種特殊的唯一索引，不允許有空值
- 聯合索引：索引列有多個字段，使用時需要滿足最左前綴原則
普通索引
這是最基本的索引，它沒有任何限制。它有以下幾種創建方式：
1.創建索引
代碼如下:
|
1
|
CREATE INDEX indexName ON mytable(username(length)); |
如果是 CHAR，VARCHAR 類型，length 可以小于字段實際長度；如果是 BLOB 和 TEXT 類型，必須指定 length，下同。
2.修改表結構
代碼如下:
|
1
|
ALTER mytable ADD INDEX [indexName] ON (username(length)) |
創建表的時候直接指定
|
1
2
3
4
5
|
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length))); |
刪除索引的語法：
|
1
|
DROP INDEX [indexName] ON mytable; |
唯一索引
它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式：
|
1
|
CREATE UNIQUE INDEX indexName ON mytable(username(length)) |
修改表結構：
|
1
|
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) |
創建表的時候直接指定：
|
1
2
3
4
5
|
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) ); |
主鍵索引
它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引：
代碼如下:
|
1
2
3
4
5
|
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); |
當然也可以用 ALTER 命令。記住：一個表只能有一個主鍵。
聯合索引
為了形象地對比單列索引和組合索引，為表添加多個字段：
|
1
2
3
4
5
6
|
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 建到一個索引里：
代碼如下:
|
1
|
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); |
索引用不上的情況
索引不是每次都可以生效的，如果我們操作不對，那么很有可能是走全表掃描而不是走的索引，Explain 中的 possible_key、key_len 以及 key 參數都可以分析我們的 SQL 語句是否用到了索引。
以下情況將會導致索引失效
- 查詢的列中使用了 != 比如 select id,name,age from student where id != 2;
- 查詢的列中使用了函數操作，比如 pow(id,2) 對 id 做平方這種函數表達式，也不會用到索引
- 如果條件中有 or，即使其中有部分條件帶索引也不會使用（這也是為什么盡量少用or的原因）
- 聯合索引中即使滿足最左前綴原則，但是第一個條件帶了范圍查詢，那么也不會用到索引
- 存在索引列的數據類型隱形轉換，則用不上索引，比如列類型是字符串，那一定要在條件中將數據使用引號引用起來,否則不使用索引
- 如果 MySQL 估計使用全表掃描要比使用索引快,則不使用索引
以上都可以使用 Explain 進行測試是否用到了索引。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對服務器之家的支持。
原文鏈接：https://bestzuo.cn/posts/4061268615.html
