mysql最佳化之索引

標籤：prim   避免   函數   fulltext   文本   沒有   文法   需要   table   

Mysql最佳化之使用索引

1,索引簡介

索引是單獨一種資料結構，單獨存在的一個空間。可以把資料表裡的建立了索引的欄位，進和物理地址，存在在一塊，這塊空間就是‘索引’。

查詢資料先從索引中查詢，查詢到之後，可以直接定位到物理地址，通過物理地址，直接找到真實資料。查詢會更快速。

索引是一種 以空間換時間的一種方式，犧牲了空間和寫的速度，提高了查詢速度

2，準備示範資料表

這裡以myisam引擎的資料庫為例，我準備了一張1800000條資料的表，這張表格儲存體時包含了三個檔案，.Frm是表結構檔案，.MYD是表資料檔案，.MYI是表索引檔案,Mac下使用管理員模式將這三個檔案複製到mysql相應目錄下

提示：Mac使用su命令切換到root模式

退出root模式

 

查看資料庫student裡是否有這個表

 

 

查看一下結果，這裡沒有加主鍵，因為主鍵預設具有索引

 

 

3，添加使用索引

先看下不加索引時的查詢速度

 

把empno設為主鍵

 

再看一下查詢速度

會發現查詢速度有很大提升

那麼非主鍵索引怎麼加呢，例如來查一下ename= jKYMOL的記錄

 

 查詢耗時0.37秒，下面給ename添加索引

 

再來查詢

 

現在來總結一下添加索引的文法

主鍵索引：alter table 表名 add primary key (主鍵欄位)

唯一索引：alter table 表名 add unique key (唯一鍵欄位)

普通索引：alter table 表名 add key (普通欄位)

全文索引：alter table 表名 add fulltext key (文本類型欄位，mysql5.6以下myisam支援)

符合索引：alter table 表名 add 索引類型 [索引名稱]  (欄位)

5，索引可以添加那麼就可刪除，怎麼刪除呢

帶自增屬性主鍵索引刪除，需要先刪除自增屬性

刪除(修改)自增屬性文法：alter table 表名 modify 欄位 欄位屬性

刪除主鍵索引文法：alter table 表名 drop primary key

刪除非主鍵索引文法:alter table 表名 drop 索引名

例如刪除索引ename

 

現在再來查一下資料

 

可以看到查詢時間又變長了

補充：查看某條查詢語句是否會用到索引

文法：explain sql語句\G

說明：後面的\G是格式化輸出

例如：

 

6，索引原則

很多時候，增加了索引，但是沒有被使用到。這種情況，其實索引是沒有起到作用的。要避免出現這種情況。

  列獨立，即sql語句的欄位不允許有運算操作，包含函數方法

 

左固定，使用模糊查詢只有左邊固定時才能用到索引

   先給ename欄位添加索引alter table emp add key (ename);

 

使用show create table emp;命令查看添加結果

 

 使用模糊查詢，查詢ename欄位

發現只有左固定時查詢速度才會很快

複合索引，多個欄位組合成為索引，欄位同時出現，可以使用到索引，單獨出現使用不到索引

給enamel和deptno建立複合索引

 

 

查看索引被使用方式

 

 

or原則，or條件兩端都有索引，則索引可用，有一端沒有索引，則索引都不可用

 

mysql最佳化之索引