MySQL-常见索引及使用

一、认识索引

如果没有索引查询数据只能整表扫描，数据量大的时候速度很慢。通过建立索引，能够显著提升数据查询速度，这是索引最大的优点。但建立索引也会降低数据插入、删除、更新的速度，因为执行这些操作都需要维护更新索引。因此，索引适合建立在频繁查询的场景中。

二、常见索引类型

• 主键索引（primary key）：最多只有一个（可以是复合主键），值不能为空且不能重复

• 唯一索引（unique）：可以有多个，值不能重复

• 普通索引（index）：可以有多个，值允许重复

• 全文索引（fulltext）：用于大量文字字段的检索，要求使用MyISAM引擎，不支持中文

三、索引的底层实现

索引有hash索引和B+树索引，而MySQL索引的默认底层实现是B+树。

hash索引和B+树索引的区别：

索引	区别
Hash索引	等值查询效率高，不能排序，不能进行范围查询，不支持模糊查询，查询性能不稳定
B+树索引	数据有序，支持范围查询，查询性能稳定

四、索引的使用

1、创建索引：

（1）创建主键索引：

方式一：创建表的时候，直接在字段名后面指定primary key（以user表为例）

• create table user(id int primary key),name(varchar(30))；

方式二：在创建表的最后，指定某列或几列为主键索引

• create table user(id int,name varchar(30),primary key(id))；

方式三：创建表以后再添加主键

• alter table user add primary key(id);

主键索引的特点：

• 一个表中，最多有一个主键索引（可以是复合主键）

• 主键索引的效率高（主键不可重复）

• 创建主键索引的列，值不能为null，且不能重复

• 主键索引的列基本都是int

（2）创建唯一索引：

方式一：在表定义时，在某列后面直接指定unique唯一属性

create table user(id int primary key,name varchar(30) unique)

方式二：在创建表时，在表的后面指定某列或几列为unique

create table user(id int primary key,name varchar(30),unique(name))

方式三：创建表以后再添加

alter table user add unique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一索引

• 查询效率高

• 如果在某一列建立唯一索引，必须保证这列不能有重复的数据

• 如果一个唯一索引上指定not null，等价于主键索引。

（3）创建普通索引

第一种方式：表定义的时候  index(name)

第二种方式：表创建后添加  alter table user add index(name)

第三种方式：创建一个名为idx_name的索引

                      create index idx_name on user(name)

 普通索引特点：

• 一个表中可以有多个普通索引，在实际开发中用的较多

• 如果某列需要索引，但有重复的值，那么就用普通索引

（4）全文索引

全文索引要求存储引擎必须是MyISAM，而且默认的全文索引不支持中文。如果检索中文，使用sphinx的中文版coreseek.

来看一个例子：

使用下面的语句直接查询得到了结果：

使用explain命令分析发现上面语句并没有使用到索引：

那么索引到底应该该如何正确使用？

再通过explain命令查看发现这次使用了索引：

2、查询索引

• show keys from 表名

• show index from 表名

• desc 表名

3、删除索引

• 删除主键索引：alter table 表名 drop primary key

• 删除其他索引：alter table 表名 drop index 索引名

• drop index 索引名 on 表名

五、索引创建的原则

• 比较频繁作为查询条件的字段应该创建索引

• 唯一性太差的字段不适合单独创建索引

• 更新频繁的字段不适合创建索引

• 不会出现在where子句中的字段不该创建索引