Mysql—索引原理與詳解

索引的原理

索引的優點和缺點和使用原則

索引優點：

• 可以加快數據的檢索速度，提高查詢速度。
• 所有的MySql列類型(欄位類型)都可以被索引，也就是可以給任意欄位建立索引。
• 全文檢索欄位進行搜索優化。

索引缺點：

•  創建索引和維護索引要耗費時間，並且隨著數據量的增加所耗費的時間也會增加。
• 當對錶中的數據進行增加、刪除、修改的時候，索引也需要動態維護，降低了數據的維護速度。
• 索引也需要占用物理存儲空間（資料庫目錄：/var/lib/mysql）。
• 我們知道數據表中的數據也會有最大上線設置的，如果有大量的索引，索引文件可能會比數據文件更快達到上線值。

使用原則：

索引的分類

• 普通索引：加速查找
• 唯一索引：加速查找+約束 （唯一）
• 主鍵索引：加速查找+約束（不為空且唯一）
• 外鍵索引：  
• 組合索引：primary key(id,name):聯合主鍵索引，unique(id,name):聯合唯一索引，index(id,name):聯合普通索引
• 全文索引：用於搜索很長一篇文章的時候，效果最好。（full text）
• 空間索引：瞭解就好，幾乎不用。（spatial）

普通索引（index）

使用規則
1.一個表中可以有多個index欄位
2.欄位的值可以有重覆，也可以為null值。欄位值無約束
3.經常把做查詢條件的欄位設置為index欄位
4.index欄位的key標誌為：mul

創建普通索引（有三種方法）
方法1：創建表時創建索引index
create table student(
    id int,
    name varchar(25),
    score float(5,2),
    index(name),              # 創建name索引
    index(score)              # 創建score索引
);
方法2：創建表後創建索引index：索引名一般和欄位名一樣，只要自己能認出就行，可以隨便起名。
mysql> create index 索引名 on 表名(欄位名)
mysql> create index name on student(name); 
方法3：創建表後創建索引index
mysql> alter table 表名 add index 索引名(欄位名);
mysql> alter table student add index(name);
mysql> alter table student add index name6(name);

查看普通索引：key一列就是索引的列，我們會發現在name和score有值MUL。
mysql> desc 表名;  
mysql> desc student;                          # Key標誌為：MUL
mysql> show index from 表名;                   # Key_name值為索引名
mysql> show index from student\G;             # 如果欄位名過多，就添加一個\G。

刪除普通索引：刪除普通索引只能一個一個刪除
mysql> drop index 索引名 on 表名;
mysql> drop index name6 on student;

唯一索引（unique）

使用規則
1.一個表中可以有多個unique欄位
2.unique欄位的值不允許重覆，可以為空值null
3.unique的key標誌是UNI

創建唯一索引(基本等同index創建)
方法1：創建表時創建索引
create table student(
    id int,
    name varchar(25),
    score float(5,2), 
    unique(name)，
    unique(score)
);   
方法2：創建表後創建索引：索引名一般和欄位名一樣，只要自己能認出就行，可以隨便起名。
mysql> create unique index 索引名 on 表名(欄位名);
方法3：創建表後創建索引
mysql> alter table student add unique(name);
mysql> alter table student add unique name6(name);
mysql> alter table student add unique index name6(name);

查看唯一索引
mysql> desc 表名; 
mysql> show index from 表名;

刪除唯一索引
mysql> drop index 索引名 on 表名;

主鍵索引（primary key）

外鍵索引（foreign key）