MySQL创建索引、修改索引、删除索引的命令

索引的作用

在索引列上，数据库利用各种各样的快速定位技术，能够大大提高查询效率。

特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

例如：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上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍（1000 x 1000 = 100万）

利用索引，MySQL加速了WHERE子句满足条件行的搜索，而在多表连接查询时，在执行连接时加快了与其他表中的行匹配的速度。

创建和删除索引

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

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

1）ALTER TABLE 创建索引

语法如下：

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) ;

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

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

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

index_name，索引名（可选），缺省时MySQL将根据第一个索引列赋一个名称。

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

创建索引的示例如下：

mysql> use tpsc
Database changed
mysql> alter table tpsc add index idx_shili (tpmc ) ;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0

2）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）删除索引

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

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

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

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

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

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

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

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

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

删除索引的操作，如下面的代码：

mysql> drop index idx_shili on tpsc ;
Query OK, 2 rows affected (0.08 sec)
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';

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

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

参考推荐：

Google、Facebook等技术发展历程

MySQL 查看数据库大小、表大小和最后修改时间（推荐）