快捷搜索:

MySQL索引的查看创建和删除

1.索引感化

在索引列上,除了上面提到的有序查找之外,数据库使用各类各样的快速定位技巧,能够大年夜大年夜前进查询效率。分外是当数据量异常大年夜,查询涉及多个表时,应用索引每每能使查询速率加快成千上万倍。

例如,有3个未索引的表t1、t2、t3,分手只包孕列c1、c2、c3,每个表分手含有1000行数据组成,指为1~1000的数值,查找对应值相等行的查询如下所示。

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3

此查询结果应该为1000行,每行包孕3个相等的值。在无索引的环境下处置惩罚此查询,必须探求3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿),显然查询将会异常慢。

假如对每个表进行索引,就能极大年夜地加速查询进程。使用索引的查询处置惩罚如下。

(1)从表t1中选择第一行,查看此行所包孕的数据。

(2)应用表t2上的索引,直接定位t2中与t1的值匹配的行。类似,使用表t3上的索引,直接定位t3中与来自t1的值匹配的行。

(3)扫描表t1的下一行并重复前面的历程,直到遍历t1中所有的行。

在此情形下,仍旧对表t1履行了一个完全扫描,但能够在表t2和t3长进行索引查找直接掏出这些表中的行,比未用索引时要快一百万倍。

使用索引,MySQL加速了WHERE子句满意前提行的搜索,而在多表连接查询时,在履行连接时加快了与其他表中的行匹配的速率。

2.创建索引

在履行CREATE TABLE语句时可以创建索引,也可以零丁用CREATE INDEX或ALTER TABLE来为表增添索引。

1.ALTER TABLE

ALTER TABLE用来创建通俗索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

此中table_name是要增添索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。别的,ALTER TABLE容许在单个语句中变动多个表,是以可以在同时创建多个索引。

2.CREATE INDEX

CREATE INDEX可对表增添通俗索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名弗成选。别的,不能用CREATE INDEX语句创建PRIMARY KEY索引。

3.索引类型

在创建索引时,可以规定索引能否包孕重复值。假如不包孕,则索引应该创建为PRIMARY KEY或UNIQUE索引。对付单列惟一性索引,这包管单列不包孕重复的值。对付多列惟一性索引,包管多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引异常类似。事实上,PRIMARY KEY索引仅是一个具着名称PRIMARY的UNIQUE索引。这表示一个表只能包孕一个PRIMARY KEY,由于一个表中弗成能具有两个同名的索引。

下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

4.删除索引

可使用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处置惩罚,语法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

此中,前两条语句是等价的,删除掉落table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时应用,由于一个表只可能有一个PRIMARY KEY索引,是以不必要指定索引名。假如没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

假如从表中删除了某列,则索引会受到影响。对付多列组合的索引,假如删除此中的某列,则该列也会从索引中删除。假如删除组成索引的所有列,则全部索引将被删除。

5.查看索引

mysql> show index from tblname;

mysql> show keys from tblname;

· Table

表的名称。

· Non_unique

假如索引不能包括重复词,则为0。假如可以,则为1。

· Key_name

索引的名称。

· Seq_in_index

索引中的列序列号,从1开始。

· Column_name

列名称。

· Collation

列以什么要领存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

· Cardinality

索引中独一值的数目的预计值。经由过程运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,以是纵然对付小型表,该值也没有需如果正确的。基数越大年夜,当进行联应时,MySQL应用该索引的时机就越大年夜。

· Sub_part

假如列只是被部分地编入索引,则为被编入索引的字符的数目。假如整列被编入索引,则为NULL。

· Packed

唆使关键字若何被压缩。假如没有被压缩,则为NULL。

· Null

假如列含有NULL,则含有YES。假如没有,则该列含有NO。

· Index_type

用过的索引措施(BTREE, FULLTEXT, HASH, RTREE)。

· Comment

更多评注。

您可能还会对下面的文章感兴趣: