Mysql 的幾種索引總結
- 1、Mysql 的幾種索引總結
-
- 1.1、普通索引
- 1.2、唯一索引
- 1.3、組合索引
-
- 1.3.1、起作用索引
- 1.3.1、不起作用索引
- 1.4、全文索引
- 1.5、空間索引
- 2、索引分析
-
- 2.1、索引優劣勢
- 2.2、索引語句分析
-
- 2.2.1、删除索引
- 2.2.2、檢視索引
- 2.2.3、SHOW INDEX 語句說明
- 2.2.3、EXPLAIN 語句說明
- 2.3、索引失效
1、Mysql 的幾種索引總結
1.1、普通索引
普通索引是最基本的索引類型，沒有唯一性之類的限制，其作用隻是加快對資料的通路速度，索引值可出現多次。。
- 建立普通索引腳本
/**
* 1、新增時建立索引
*/
CREATE INDEX indexName ON table_name (column_name);
/**
* 2、建立表以後，添加索引，修改表結構(添加索引)
*/
ALTER table tableName ADD INDEX indexName(columnName);
1.2、唯一索引
唯一索引與普通索引類似，兩者不同點在于：唯一索引的索引列的值必須唯一（除了NULL外，NULL可能會出現多次），但允許有空值。如果是組合索引，則列值的組合必須唯一。需要特别說明的是，主鍵也是一種唯一索引，但它必須指定為“PRIMARY KEY”。
建立索引的主要原因是減少查詢索引列操作的執行時間，尤其是對比較龐大的資料表。
- 建立唯一索引腳本
/**
* 1、新增時建立索引.這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。
* 關于 length : 如果是CHAR，VARCHAR類型，length可以小于字段實際長度；如果是BLOB和TEXT類型，必須指定 length。
*/
CREATE UNIQUE INDEX indexName ON mytable(columnName(length)) ;
/**
* 2、建立表以後，添加索引.這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。
*/
ALTER table mytable ADD UNIQUE [indexName] (columnName(length));
/**
* 3、主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。
* 該語句添加一個主鍵，這意味着索引值必須是唯一的，且不能為NULL。
*/
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
1.3、組合索引
組合索引是在多個字段上建立一個索引。
組合索引是針對單列索引的一種劃分。單列索引是在資料表中的某一個字段上建立的索引，一個表中可以建立多個單列索引。
組合索引可起幾個索引的作用，但是使用時并不是随便查詢哪個字段都可以使用索引，而是遵循“最左字首”：利用索引中最左邊的列集來比對行，這樣的列集稱為最左字首。
例如：這裡由id、name 和 age 3 個字段構成的索引，索引行中按 id/name/age 的順序存放，索引可以搜尋下面字段組合：（id、name、age）、（id、name） 或者 id.如果列不構成索引最左字首原則，Mysql不能使用局部索引，如（age）或者（name、age）組合則不能使用索引查詢。
- 建立組合索引腳本
ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);
1.3.1、起作用索引
圖解 ：
1.3.1、不起作用索引
圖解 ：
1.4、全文索引
FULLTEXT 全文索引可以用于全文搜尋。隻有 MyISAM 存儲引擎支援 FULLTEXT 索引，并且隻為 CHAR 、VARCHAR 和 TEXT 列。索引總是對整個列進行，不支援局部（字首）索引。
- 建立全文索引腳本
/*
* 建立全文索引腳本,該語句指定了索引為 FULLTEXT ，用于全文索引。
*/
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
1.5、空間索引
空間索引必須在 MyISAM類型的表中建立，且空間類型的字段必須為非空。
2、索引分析
2.1、索引優劣勢
過多的使用索引将會造成濫用。是以索引也會有它的缺點：
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度
，如對表進行INSERT、UPDATE和DELETE。
因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。
同時，
建立索引會占用磁盤空間的索引檔案。
2.2、索引語句分析
2.2.1、删除索引
- 删除索引腳本
/**
* 删除索引，第一種文法
*/
ALTER TABLE table_name DROP INDEX index_name;
/**
* 删除索引，第二種文法
*/
DROP INDEX index_name ON table_name;
說明：添加 AUTO_INCREMENT 限制字段的唯一索引不能被删除。删除表中的列時，如果要删除的列為索引的組成部分，則該列也會從索引中删除。如果組成索引的所有列都被删除，則整個索引将被删除。
2.2.2、檢視索引
- 檢視索引腳本
/**
* 查詢表索引文法
*/
SHOW INDEX FROM table_name;
/**
* 例：查詢 exp 表的索引
*/
SHOW index from exp;
2.2.3、SHOW INDEX 語句說明
| 關鍵字 | 關鍵字說明 |
|---|---|
| Table | 表示建立索引的表 |
| Non_unique | 表示索引非唯一，1-代表是非唯一索引，0-代表唯一索引 |
| Key_name | 表示索引的名稱 |
| Seq_in_index | 表示該字段在索引中的位置，單列索引該值為1，組合索引為每個字段在索引定義中的順序。 |
| Column_name | 表示定義索引的列字段 |
| Sub_part | 表示索引的長度 |
| Null | 表示該字段是否能為空值 |
| Index_type | 表示索引類型 |
2.2.3、EXPLAIN 語句說明
EXPLAIN 語句用于分析查詢的SQL語句的執行，看一條查詢的SQL語句是怎麼去執行的，有沒有用到索引，需不需要回表查詢，需不需要額外排序的情況，然後我們針對SQL語句執行來進行一些特定語句。
| 關鍵字 | 關鍵字說明 | 備注 |
|---|---|---|
| id | | - |
| select_type | select_type 行指定所使用的 SELECT 查詢類型，這裡值為 SIMPLE 表示簡單的 SELECT，不使用 UNION 或子查詢。其他可能的取值有：PRIMARY、UNION、SUBQUERY 等。 | SIMPLE（ |
| table | table 行指定資料庫讀取的資料表的名字，它們按被讀取的先後順序排列。 | - |
| type | type 行指定了本資料表與其他資料表之間的關聯關系，從好到壞依次是：system,const,eq_ref,ref,range,index,all. | system（ |
| possible_keys | | - |
| key | | - |
| key_len | | - |
| ref | ref 行給出了關聯關系中另一個資料表裡的資料列的名字。 | - |
| rows | | - |
| extra | extra 行提供了與關聯操作有關的資訊。可能的取值有 using filesort 、using temporary、using index、using where、impossible where | using filesort （ |
2.3、索引失效
以下幾種情況會造成索引失效，使用explain指令加在要分析的sql語句前面，在執行結果中檢視key這一列的值，如果為NULL，說明沒有使用索引。
- 1、
like 以%開頭，索引無效；當like字首沒有%，字尾有%時，索引有效。
- 2、
or語句前後沒有同時使用索引。當or左右查詢字段隻有一個是索引，該索引失效，隻有當or左右查詢字段均為索引時，才會生效。
- 3、
組合索引，不是使用第一列索引，索引失效。
- 4、
資料類型出現隐式轉化。如varchar不加單引号的話可能會自動轉換為int型，使索引無效，産生全表掃描。
- 5、
在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，是以這樣的操作不能使用索引，可以用其他的辦法處理，例如：數字類型，判斷大于0，字元串類型設定一個預設值，判斷是否等于預設值即可。
- 6、
在索引字段上使用not，<>，!=。不等于操作符是永遠不會用到索引的，是以對它的處理隻會産生全表掃描。 優化方法： key<>0 改為 key>0 or key<0.
- 7、
對索引字段進行計算操作、字段上使用函數。
- 8、
當全表掃描速度比索引速度快時，mysql會使用全表掃描，此時索引失效。
.