MySQL索引作用和用法详解

一、引言

• 对于该文章，照例尽量通俗的写，并注明一些细节和可能出现问题的地方。
• 此外，该文章只是写了索引的创建，不代表检索（查询）的应用，检索后面会写道。
• 标注：红色表示十分重要，蓝色表示细节；
• 在阅读这文章之前，如果初学MySQL不久，应已经熟悉：表约束操作、表的创建以及增删改查数据、熟知InnoDB和M有ISAM存储引擎、SQL数据类型更好

二、索引基本概念和功能

1.索引概念

• 索引本质：索引就是数据库表中字段值的复制，该字段称为索引的关键字
• 索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录
• 索引往往通过复杂的数据结构（双向链表、B+树/B树、hash）实现
• MyISAM存储引擎的表支持主索引，InnoDB存储引擎的表支持聚簇索引(主索引)与非聚簇索引(辅助索引)索引优化使用

2.索引的优缺点

@优点：

• MySQL的索引的建立对于MySQL的高效运行非常重要，可以大大提高MySQL的检索速度。
• 在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
• 在表连接的连接条件 可以加速表与表直接的相连

@缺点:

• 但过多使用索引会造成索引滥用，提高了检索速度，缺降低了表的更新速度

如：对表进行增删改查操作时，MySQL不仅要保存数据，还要保存索引文件，建立索引会占用磁盘空间的索引文件

•  在创建索引和维护索引 会耗费时间,随着数据量的增加而增加

3.不使用索引的情况

• 查询中很少使用的列和很少数据的字段，不应该创建索引，否则会降低MySQL性能、增加空间需求
• text、image、bit数据类型字段不应该增加索引
• 修改操作远大于检索操作，不应该增加索引

4.索引关键字选取规则

• 离散度高。例如性别只要男女，离散度低；而学号，每个人对应一个，离散度高。
• 占用存储空间少的字段 。例如 字符串与 int ，选int
• 存储空间固定的字段。例如text与char，选char
• where语句字段、group by分组字段、order by排序字段通常使用索引 ，单独order by 用不了索引，可以使用where/limit
• 尽量使用前缀索引 ，即将字段值的前部分作为索引 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引

三、索引分类

PS：此处没有讲MyISAM与InnoDB引擎，所以这个分类代表常用索引分类。后续参考索引拓展部分。该两个引擎在MySQL5.6版本之后，都支持以下索引。读者在阅读其它文章时，需要分辨一下。

1.  索引按用法技巧分为

• 单列索引 和  组合索引 (最左前缀索引或者多列索引或者复合索引) 
•  单列索引：即一个索引只包含单个字段，一个表可以有多个单列索引
• 组合索引： 即一个索引包含多个字段。遵从最左前缀原则

2. 单列索引：

• 普通索引:(最基本):没有任何约束的索引；通常创建外键约束时自动创建普通索引，索引名与外键约束名相同
• 主键索引(主索引):与主键约束相关。通常在创建主键时自动创建了主索引，索引名与主键约束名相同

          + 主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）

• 唯一索引 unique：与唯一约束相关。通常在创建唯一约束时自动创建唯一索引，索引名与唯一约束名相同

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

• 全文索引 fulltext：全文索引的诞生是为了解决模糊查询效率低的

        + 全文索引支持的字段类型包括 char ，varchar ， text 。是目前实现大数据搜索的关键技术

3.组合索引

即一个索引包含多个字段。遵从最左前缀原则。知道这点即可 

四、索引的创建和删除语法

1.索引的创建

按创建索引的时间分为两大类

• 创建表的时候创建索引   

在建表反括号之前 [无|unique|fulltext]index 索引名(字段名[(长度)][asc|desc])   

 [ ]内属于可选选项，根据参数创建不同索引

 asc与desc表示可选参数，分别表示升序与降序，通常默认asc就行了

例如：创建一个名为information的表，依次定义唯一性、（普通、主）、全文索引、组合索引。

PS : 普通索引与主索引创建方式相同 ，故写一个 ，蓝色为索引名

create table information (

 ID char(20) primary key,          ID普通字符主键

name char(20）not null,           名字普通字符字段

introduction text not null,          介绍文本字段

price int ,                                   价格整型字段

time_s date not null,                  时间date类型字段

unique index ID_unique (No),   唯一性索引

index name_key (name(20)),    普通、主索引

fulltext index introName_fulltext (name,introduction),  全文索引  有name和introduction两个字段   

index priTime_group(price,time_s)                               组合索引 有price与time两个字段

) engine = MyISAM default charset = utf8;

• 在已有的表上创建索引  （通常推荐该种创建索引的方式）

主要有两种

CREATE [无|unique|fulltext]index索引名 ON 表名(字段名[(长度)][asc|desc])

ALTER TABLE 表名 ADD [无|unique|fulltext]index 索引名(字段名[(长度)][asc|desc]） 

例如：创建上方的唯一性索引

create unique index  introName_fulltext on information (name,introduction)

等同于 alter table information add unique introName_fulltext  (name,introduction)

2.索引的删除

 drop index 索引名 on 表名

例如：删除上方的唯一性索引

drop index introName_fulltext   on information

五、索引的失效

可以查看一下别人的分享：http://blog.csdn.net/guoxingege/article/details/51034387

六、索引的拓展—存储引擎

1.常用索引实现的数据结构

• MySQL普遍使用B+Tree实现索引结构

2.MyISAM索引实现

• MyISAM引擎使用B+Tree为索引结构
• MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分

3.InnoDB索引实现

• InnoDB也用B+Tree为索引结构。
• 与MyISAM区别

No.1 ：InnoDB的数据文件本身就是索引文件，MyISAM索引文件与数据文件是分开的

No.2 ：InnoDB的辅助索引data域存储相应记录主键的值，而MyISAM是地址

 PS：这篇文章相当于是初级索引，如果想更加深入，可以学习索引的调优，数据结构，存储引擎等方面。