如何创建mysql索引以及索引的优缺点

mysql教程：索引的使用以及索引的优缺点

1. 索引（index）是帮助MySQL高效获取数据的数据结构。

它对于高性能非常关键，但人们通常会忘记或误解它。

索引在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引，也能有好的性能， 但是当数据增加的时候，性能就会下降很快。

Tip:蠕虫复制,可以快速复制大量的数据

例：insert into emp select * from emp

2. MySQL中常见的索引

◆普通索引 ◆唯一索引 ◆主键索引 ◆组合索引 ◆全文索引◆外键 （只有innodb存储引擎才支持）

2.1普通索引：

这是最基本的索引，它没有任何限制。有以下几种创建方式：

有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON tablename(username(length))

◆修改表结构

ALTER tablename ADD INDEX indexName (username(length))

Tip：length可以小于字段实际长度如果是BLOB 和 TEXT 类型，必须指定length ,下同

◆创建表的时候直接指定

CREATE TABLE mytableuuu( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName (username(length)) )

CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL)

create index index1 on mytable(id) //创建普通索引

◆删掉索引：

drop index index1 on mytable

有一个概念，

行定义：在声明字段（列）的时候定义的，比如primary key

表定义：在所有字段（列）声明完之后定义的，比如primary key，index

CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,index index1(username))

3.0唯一索引（unique）

索引列的值必须唯一，但允许有空值。

1）创建索引：Create UNIQUE INDEX indexName ON tableName(tableColumns(length))

2）修改表结构：Alter tableName ADD UNIQUE [indexName] ON (tableColumns(length)

3）创建表的时候直接指定：Create TABLE tableName ( [...], UNIQUE [indexName](tableColumns(length))

4.0主键索引（primary key）

我们可以通过查看索引的属性来判断创建索引的方法。

查看索引的语法格式如下：

SHOW INDEX FROM <表名>[ FROM <数据库名>]

语法说明如下：

<表名>：指定需要查看索引的数据表名。

<数据库名>：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test语句表示查看 test 数据库中 student 数据表的索引。

示例

使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息，SQL 语句和运行结果如下所示。

mysql>SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要参数说明如下：

参数 说明

Table 表示创建索引的数据表名，这里是 tb_stu_info2 数据表。

Non_unique 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。

Key_name 表示索引的名称。

Seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。

Column_name 表示定义索引的列字段。

Collation 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。

Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。

Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。

Packed 指示关键字如何被压缩。若没有被压缩，值为 NULL。

Null 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。

Index_type 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 显示评注。

---