Mysql索引

作者：刘通亮 

一、索引简介

索引是对数据库表中的一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。

原理：索引是经过某种算法优化的一种数据结构，可以快速查找。在mysql中使用索引可以快速找到被查询的数据，避免全表扫描，从而确定这一行记录的位置。

1、索引的优点和缺点

优点：

1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。(比如微信中的openId,unionId)
2. 可以大大加快数据的查询速度，这是创建索引的最主要的原因。
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。
4. 在使用分组和排序字句进行数据查寻时，也可以显著减少查询中分组和排序的时间。

缺点：

1. 创建索引和维护索引要好费时间，并且随着数据量的增加所耗费的时间也会增加。
2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3. 当对表中的数据尽心更增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

2、索引的分类

(1)  普通索引和唯一索引（重要）

普通索引:mysql中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

(2)  单列索引和组合索引（重要）

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

组合索引：指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的最左边的字段时，索引才会被使用。遵循最左前缀集合。

(3)  全文索引（了解）

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。Mysql中只有MyISAM存储引擎支持全文索引（Mysql5.6以后InnoDB存储引擎也支持全文索引，笔者在Mysql5.72中在InnoDB存储引擎的表中建立过，但是实际应用没有做研究）。

(4)  空间索引（了解）

空间索引是对空间数据类型的字段建立的索引，Mysql中的空间数据类型有4种，分别是:GEOMETRY(几何)、POINT(点)、LINESTRING(线段)、POLYGON(多边形)。Mysql中使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

3、索引的设计原则

1. 索引并非越多越好，一个表中如有大量的索引，不仅占中磁盘空间，而且会影响insert、update、delete等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新。
2. 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。从而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
3. 数量小表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
4. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无需建立索引。如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
5. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引能确保定义的列的数据完整性，以提高查询速度。
6. 在频繁进行排序或者分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

二、创建索引

1  创建表的时候创建索引

create  table  table_name  [col_name data_type]
[unique|fulltext|spatial]  [index|key]  [index_name]  (col_name [length]) [asc | desc]

示例（普通索引和唯一索引）:

(1)   普通单列索引

创建user表，有id，name,phone，age四个字段，在name字段上建立普通索引。

Mysql索引

查看索引:

有三种方式:

1. show create table user

Mysql索引

上图中箭头指的那一行key和index是等价的，都表示索引。

2. desc user

Mysql索引

PRI表示主键（非空的唯一索引）UNI表示唯一索引MUL表示普通索引(列上可以有重复值)

3. show index from user（重要）

Mysql索引

上图中描述了user表中所有的索引的详细信息。具体的含义可以自己去查，这里不在废话。

(2)   创建唯一索引

创建user表，有id，name,phone，age四个字段，在phone字段上建立唯一索引。

第一种方式

Mysql索引

第二种方式

Mysql索引

这种方式唯一索引的名字会自动生成，默认为字段名

Mysql索引

注：读者也可以尝试用show create table user或者desc user去查看下。

Mysql索引

（3）创建组合索引

创建user表，有id，name,phone，age四个字段，在phone、name、age字段上建立组合索引。

Mysql索引

查看索引

Mysql索引

建立唯一组合索引

Mysql索引

查看索引

2  在已经存在的表上创建索引

（1）使用alter table语句创建索引

alter  table  table_name  add  [unique|fulltext|spatial] 
[index|key]  [index_name] (col_name[length],...)  [asc|desc]

示例：普通组合索引

先创建user表，有id，name,phone，age四个字段

Mysql索引

在phone、name、age字段上建立组合索引。

Mysql索引

查看索引

Mysql索引

（2）使用create index语句创建索引

create  [unique|fulltext|spatial]  index  index_name
on  table_name (col_name[length],...)  [asc|desc]

示例：普通组合索引

先创建user表，有id，name,phone，age四个字段

Mysql索引

在phone、name、age字段上建立组合索引。

Mysql索引

查看索引

Mysql索引

三、删除索引

1. 使用alter table语句删除索引

alter  table  table_name  drop  index  index_name

2. 使用drop index语句删除索引

drop  index  index_name  on  table_name

示例:

Mysql索引

Mysql索引