常見的索引

（1）PRIMARY:主鍵索引，索引列值唯一且不能為空；一張表只能有一個主鍵索引。

（2）INDEX:普通索引，索引列沒有任何限制。

（3）UNIQUE:唯一索引，索引列的值必須是唯一的，但允許有空值。

（4）FULLTEXT:全文索引，只有MyISAM存儲引擎支持。

（5）SPATIAL：空間索引，對空間數(shù)據(jù)類型的字段建立的索引。

索引的建立與查看

1. 創(chuàng)建數(shù)據(jù)表的同時建立索引

在 CREATE TABLE 語句中添加一下的語句來實現(xiàn)在創(chuàng)建數(shù)據(jù)表的同時建立索引。

create table 表名 (
字段1 數(shù)據(jù)類型 字段屬性，
字段2 數(shù)據(jù)類型 字段屬性，
     ...
索引類型 index 索引名 （字段名）
);

例如在student表中，在字段sname上創(chuàng)建普通索引index_sname；

在字段dept字段和sex字段上創(chuàng)建聯(lián)合索引index_dept_sex；

在remarks字段上創(chuàng)建全文索引ft_remarks。

create table student(
sno char(6) primary key comment '學(xué)號',
sname varchar(20) not null comment '姓名',
dept varchar(20) comment '專業(yè)',
sex char(1) comment '性別',
birthday date comment '出生日期',
totalcredit decimal(4,1) default 0 comment '總學(xué)分',
remarks varchar(100)  comment '備注',
-- 在sname字段上創(chuàng)建普通索引
index index_sname (sname) ,
-- 在dept字段和sex字段上創(chuàng)建聯(lián)合索引
 index index_dept_sex (dept,sex),
-- 在remarks字段上創(chuàng)建全文索引
fulltext index ft_remarks (remarks)
) engine = MyISAM;

2. 創(chuàng)建數(shù)據(jù)表后添加索引

可以使用 CREATE INDEX 語句在一個已有的數(shù)據(jù)表上添加索引

CREATE INDEX 索引名稱 ON 數(shù)據(jù)表名 （列名）

例如在course表cname字段上創(chuàng)建普通索引index_cname。

create index index_cname on course(cname);

在course表cname字段上創(chuàng)建前綴索引索引pf_cname。

create index index_cname on course(cname(4));

3. 使用ALTER TABLE語句建立索引

通過ALTER TABLE 語句直接為已有的數(shù)據(jù)表建立索引

ALTER TABLE 數(shù)據(jù)表名 ADD 索引類型 INDEX 索引名稱（類名）。

例如在score表的sno字段和cno字段上創(chuàng)建聯(lián)合唯一索引。

alter table score add unique index index_sno_cno (sno,cno);

4. 查看數(shù)據(jù)表上的引用

通過SHOW INDEX語句來查看索引.

SHOW INDEX FROM 數(shù)據(jù)表名

例如顯示course表的索引信息。

show index from course;

course 表的索引信息：

參考說明：

• Non_unqiue：索引字段值是否可以重復(fù)，0表示不可以，1表示可以。
• key_name：索引的名字，如果索引是主鍵索引，則它的名字為PRIMARY。
• Seq_in_index：建立索引的字段序號值，聯(lián)合索引第一個字段為1，第二個字段為2。
• Column_name：建立索引的字段。
• Collation：索引字段是否有序，A表示有序，NULL表示沒有排序。
• Cardinality：計算連接時使用索引的可能性，值越大，可能性越高。
• Sub_part：前綴索引的長度，如4，無前綴則為NULL。
• Index_type：索引類型，可選值有BTREE、FULLTEXT、HASH、RTREE。

查看索引的使用情況

使用EXPLAIN關(guān)鍵字查看SQL語句中執(zhí)行索引的使用情況。

例如查看student表中的索引使用信息

explain select * from student where sname like '王%';

查看student表中的全文索引使用信息

explain select * from student where match (remarks) against ('三好學(xué)生');

參考說明：

• id: 查詢標(biāo)識符，默認(rèn)從1開始，若使用了聯(lián)合查詢，則該值依次遞增，聯(lián)合查詢結(jié)果對應(yīng)的該值為NULL。
• select_type: 操作類型，如DELETE、UPDATE等，擔(dān)當(dāng)執(zhí)行SELECT語句時，它的值有多種，例如，SIMPLE表示不需聯(lián)合查詢或簡單的子查詢。
• table: 查詢表的名字。
• partitions: 匹配的分區(qū)。
• type: 取值可為system,const、eq_ref、ref、fulltext等。
• key: 查詢使用到的索引。
• ref: 表示哪些字段或常量與索引進(jìn)行比較，例如，const表示常量與索引進(jìn)行了比較。
• rows: 預(yù)計需要檢索的記錄數(shù)。
• filtered: 按條件過濾的百分比。
• Extral: 附加信息，例如，USing index 表示使用了索引覆蓋。

索引的刪除

1. 使用ALTER TABLE 語句刪除索引

ALTER TABLE 表名 DROP INDEX 索引名;

例如刪除student表中的index_sname索引。

alter table student drop index index_sname ;

2. 使用DROP INDEX 語句刪除索引

DROP INDEX 索引名 ON 表名；

例如刪除score表中的index_sno_cno索引。

drop index index_sno_cno on score;

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論