mysql索引能修改吗_MySQL 创建索引、修改索引、删除索引的命令-Fun言

索引的作用

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

例如：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 创建索引

语法如下：

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 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 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 WHERElast_name='Widenius';

SELECT * FROM test WHERElast_name='Widenius' AND first_name='Michael';

SELECT * FROM test WHERElast_name='Widenius' AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test WHERElast_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';

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

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

参考推荐：