索引的作用

在索引列上,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。

例如:3个未建索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有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子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。

 

创建和删除索引

索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。

删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。

1)ALTER TABLE 创建索引

语法如下:

  1. alter table table_name add index index_name (column_list) ; 
  2. alter table table_name add unique (column_list) ; 
  3. alter table table_name add primary key (column_list) ; 

其中包括普通索引、unique唯一索引、primary key主键索引3种创建索引的格式

table_name,是要增加索引的表名,

column_list,指出对哪些列进行索引,多列时各列之间用逗号分隔。

index_name,索引名(可选),缺省时MySQL将根据第一个索引列赋一个名称。

另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引

创建索引的示例如下:

  1. mysql> use tpsc 
  2. Database changed 
  3. mysql> alter table tpsc add index shili (tpmc ) ; 
  4. Query OK, 2 rows affected (0.08 sec) 
  5. Records: 2 Duplicates: 0 Warnings: 0 

 

2)CREATE INDEX 增加索引

能够增加普通索引和UNIQUE索引两种。其格式如下:

  1. create index index_name on table_name (column_list) ; 
  2. create unique index index_name on table_name (column_list) ; 

说明:table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。

另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

 

3)删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。

DROP INDEX可以在ALTER TABLE内部作为一条语句处理,其格式如下:

  1. drop index index_name on table_name ; 
  2. alter table table_name drop index index_name ; 
  3. alter table table_name drop primary key ; 

其中,在前面的两条语句中,都删除了table_name中的索引名index_name。

而在最后一条语句中,只在删除primary key索引中使用,因为一个表只可能有一个primary key索引,因此不需要指定索引名。

如果没有创建primary key索引,但表具有一个或多个UNIQUE唯一索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除某列,则索引会受影响:

a)对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除

b)如果删除组成索引的所有列,则整个索引将被删除。

删除索引的操作,如下面的代码:

  1. mysql> drop index shili on tpsc ; 
  2. Query OK, 2 rows affected (0.08 sec) 
  3. Records: 2 Duplicates: 0 Warnings: 0 

该语句删除了前面创建的名称为“shili”的索引。

 

4)索引类型

在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE唯一索引。

对于单列UNIQUE惟一性索引,这保证单列不包含重复的值;对于多列UNIQUE惟一性索引,保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有索引名为PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。

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

ALTER TABLE students ADD PRIMARY KEY (sid)

 

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

 

Mysql的列索引和多列索引(联合索引)

创建多列索引(联合索引)

CREATE TABLE test (  
    id         INT NOT NULL,  
    last_name  CHAR(30) NOT NULL,  
    first_name CHAR(30) NOT NULL,  
    PRIMARY KEY (id),  
   
INDEX name (last_name,first_name)  
);  

创建多个索引(单个索引)

CREATE TABLE test (  
    id         INT NOT NULL,  
    last_name  CHAR(30) NOT NULL,  
    first_name CHAR(30) NOT NULL,  
    PRIMARY KEY (id),  

    INDEX name (last_name),  
    INDEX name
_2 (first_name)  
);  

当查询语句的条件中包含last_name 和 first_name时,例如:

SELECT * FROM test WHERE last_name='Kun' AND first_name='Li';  

在多列联合索引里,sql会先过滤出last_name符合条件的记录,在其基础上再过滤first_name符合条件的记录。

在last_name和first_name两个单列索引里,mysql的处理方式就不一样了,它会选择一个最严格的索引来进行检索,可以理解为检索能力最强的那个索引来检索,另外一个利用不上了,这样效果就不如多列联合索引了。

但是多列索引的利用也是需要条件的,以下形式的查询语句能够利用上多列索引:

SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE
last_name='Widenius' AND first_name='Michael';
SELECT * FROM test WHERE
last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test WHERE
last_name='Widenius' AND first_name >='M' AND first_name < 'N';

以下形式的查询语句利用不上多列索引:

SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';

多列联合索引比对每个单列索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。

另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。

 

参考推荐:

主键与唯一索引的区别