一般，我们看到术语“索引”和“键”交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。

单列索引和多列索引

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。

多列索引：即一个索引包含多个列，但是此时列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列（一般用在WHERE字句带AND操作会有用）。

1）普通索引

普通索引是MySQL最基本的索引类型，它没有任何限制。

2）唯一索引

它与前面的”普通索引”类似，不同的就是：索引列的值必须唯一，但允许为NULL。如果是联合唯一索引，则列值的组合必须唯一。

这里要说明一点，唯一键约束和主键约束创建时都会默认隐式创建同名的唯一索引，以键名为索引名。唯一键约束索引列的值必须唯一，但允许为NULL。而主键约束索引不允许有空值，且在一张表中只允许有一个主键约束，但允许存在复合主键索引，也就是两个字符共同组成一个主键索引，两个字段组合必须唯一。一般是在建表的时候同时创建主键约束。

唯一键约束和主键约束与唯一索引的区别是，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除。但你可以先创建唯一索引，后创建主键约束或唯一键约束。这样显式创建的唯一索引不受主键约束或唯一键约束失效的影响。

3）全文索引

FULLTEXT类型索引，可以在CHAR,VARCHAR或者TEXT类型的列上创建，之前仅仅是MYISAM存储引擎支持，从MySQL5.6版本开始支持InnoDB引擎的全文索引，语法层面上大多数兼容之前MyISAM的全文索引模式。全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

4）空间索引

MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从素有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的就是PostgreSQL的PostGIS。

索引的创建

第一种：普通索引

直接创建索引

1

   

mysql> create index INDEX_NAME on TABLE_NAME(Column[length]) [using btree|hash];

   

修改表结构添加索引

1

   

mysql> alter TABLE_NAME add index [INDEX_NAME] on (Column[length]) [using btree|hash];

   

创建表时创建索引

1

   

mysql> create table TABLE_NAME( [...],INDEX [INDEX_NAME] (Column[length]) [using btree|hash];

   

[length]可选，如果是CHAR或VARCHAR类型，length可以小于字段实际长度。如果是 BLOB 和 TEXT 类型，必须指定 length。

[using btree|hash]可选，btree或hash都是用来排序算法，默认是btree。

第二种：唯一索引

直接创建索引

1

   

mysql> create unique index INDEX_NAME on TABLE_NAME(column(length));

   

修改表结构添加索引

1

   

mysql> alter TABLE_NAME add unique index [INDEX_NAME] on (Column(length));

   

创建表时创建索引

1

   

mysql> create table TABLE_NAME ( [...], UNIQUE [INDEX_NAME] (Column(length));

   

创建唯一键约束

1

   

mysql> alter TABLE_NAME add unique key [INDEX_NAME] on (Column(length));

   

创建主键约束

1

   

mysql> create table TABLE_NAME(id INT NOT NULL AUTO_INCREMENT, Name VARCHAR(16) NOT NULL,PRIMARY KEY(id));

   

这里就体现出来了，我们使用唯一索引和唯一键约束都可以创建一条唯一索引。在创建唯一键约束时会自动创建一条唯一键索引。

第三种：全文索引

创建表时指定索引

1

   

mysql> create table TABLE_NAME([...],FULLTEXT INDEX INDEX_NAEM(info));

   

第四种：联合索引

直接创建索引（联合唯一索引）

1

   

mysql> create unique index INDEX_NAME on TABLE_NAME(FIELD1,FIELD2);

   

修改表结构添加索引（联合唯一索引）

1

   

mysql> alter table TABLE_NAME add unique index INDEX_NAME(FIELD1,FIELD2);

   

创建表时创建索引（普通联合索引）

1

   

mysql> create table t3 (id INT NOT NULL,NAME CHAR(30) NOT NULL,age INT NOT NULL,info VARCHAR (255),INDEX multiIdx (id, NAME, age (100));

   

索引删除

1

   

mysql> drop index INDEX_NAME on TABLE_NAME;

   

查看索引

1

2

3

4

5

6

7

8

9

10

11

12

13

14

   

mysql> show create table TABLE_NAME;

mysql> show index from TABLE_NAME;

Table: user（表名）

Non_unique: 0（是否为唯一键）

Key_name: PRIMARY（索引名字）

Seq_in_index: 1（索引中的列顺序号从1开始）

Column_name: account_id（列名）

Collation: A（列怎样在索引中被排序。在MySQL中，这可以有值A（升序) 或NUL）

Cardinality: 21941（索引中唯一值的数量。这可通过运行isamchk -a更改）

Sub_part: NULL（如果列只是部分被索引，索引字符的数量。NULL，如果整个键被索引）

Packed: NULL

Null:

Index_type: BTREE（索引类型）

Comment:

   

如何检测所创建的索引是否有效（或者说索引使用情况）

1

   

mysql> show status like 'Handler_read%';

   

Handler_read_key越高越好。

Handler_read_rnd_next越高越不好。

索引性能测试

1）分别在没有索引和有索引的情况下执行查询。

2）分别在没有索引和有索引的情况下新增数据。

创建没有索引的表

1

2

3

   

mysql> create database it;

mysql> use it;

mysql> create table indextest(id int,number int,password varchar(50));

   

插入数据

1

   

$ for ((i=1;i<=500000;i++));do `mysql -uroot -predhat -e "insert into it.indextest values($i,floor($i+rand()*$i),md5($i));"`;done

   

创建有索引的表

1

2

3

   

mysql> create table indextest01(id int,number int,password varchar(50));

mysql> insert into indextest01 select * from indextest;

mysql> create index iid on indextest01(id) using btree;

   

查看表

1

2

3

4

5

   

mysql> show tables\G

*************************** 1. row ***************************

Tables_in_it: indextest

*************************** 2. row ***************************

Tables_in_it: indextest01

   

刷新查询缓存，如果没有效果重启MySQL是最有效的

1

   

mysql&gt; reset query cache;

   


1

2

3

4

5

6

7

8

9

10

11

   

mysql> select number,password from indextest where id>200000;

300000 rows in set (6.31 sec)

 

mysql> select number,password from indextest where id>200000;

300000 rows in set (0.23 sec)

 

mysql> select number,password from indextest01 where id>200000;

300000 rows in set (0.35 sec)

 

mysql> select number,password from indextest01 where id>200000;

300000 rows in set (0.18 sec)

   

执行查询计划

1

   

mysql&gt; explain select number,password from indextest01 where id&gt;200000\G;

   

查询计划可以用于输出查询的详细信息，可以看出此条SQL语句有没有用到索引。

另外一个没有索引的表在接收数据导入时比有索引的表要快很多。因为当一个表建立索引之后，导入数据时。每导入一行数据随之索引就需要变化，从而导致导入数据慢。

本文出自https://www.ywnds.com/?p=7796