前言
正确的建立索引可以大幅度地提升查询效率，但是如果“随意”创建索引，反而会背道而驰，不仅不会带来查询性能的提升，反而会在新增、修改数据的时候维护索引而导致性能的下降。所以，本文主要讲述MySQL中该如何创建、删除以及查看表的索引，最重要的还是讲什么情况下要创建索引、什么情况下不应该创建索引，以及创建索引的注意点。
索引概述
首先我们简单概括下索引，索引（Index）是帮助MySQL高效获取数据的数据结构， 可以类别成新华字典中的目录页，而MySQL中InnoDB存储引擎的索引数据结构是一棵"B+树"。
对于索引底层数据结构的掌握非常关键，这对于你理解如何正确建立和使用索引非常重要，关于索引数据结构的详细内容请移步： 一步步带你设计MySQL索引数据结构。
索引分类
- 从功能逻辑上说，索引主要有 4 种，分别是主键索引、普通索引、唯一索引、全文索引。
- 普通索引：不附加任何限制条件，只是用于提高查询效率。
- 唯一索引：限制该索引的值必须是唯一的，但允许有空值。
- 主键索引：特殊的唯一性索引 ，在唯一索引的基础上增加了不为空的约束
- 全文索引：使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找。
- 按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
- 聚簇索引： 存放全部数据的索引，也就是我们的主键索引，如果没有定义主键，MySQL会按照一定规律找一个字段用来做聚簇索引。
- 非聚簇索引：也就二级索引或者辅助索引，就是除了聚簇索引以外其他的都是非聚簇索引。
- 按照作用字段个数进行划分，分成单列索引和联合索引。
- 单列索引：只有一个字段作为索引。
- 联合索引：有多个字段作为索引。
索引使用
下面讲讲如何给表添加、删除、查看索引。
查看索引
通过使用SHOW INDEX FROM TABLE语句可以查看表的索引使用情况：
SHOW INDEX FROM TABLE
例子：
创建索引
创建表的索引有两种方式，一种是隐式，一种显示。
- 隐式创建索引
使用CREATE TABLE创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时会隐式在指定列上创建了一个索引。
- 显示创建索引
1). 创建表的时候显示创建索引
CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |DESC]
UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；ASC或DESC指定升序或者降序的索引值存储。
例子：
# 创建唯一索引 CREATE TABLE test1( id INT NOT NULL, name varchar(30) NOT NULL, UNIQUE INDEX uk_idx_id(id) );
2). 在已经存在的表上创建索引
- 使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
例子：
# 创建普通索引 ALTER TABLE table_name test1 ADD INDEX idx_name name asc;
- 使用CREATE INDEX创建索引
CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中， CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]
删除索引
1).使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_name;
2). 使用DROP INDEX语句删除索引
DROP INDEX index_name ON table_name;
- 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。
10种适合创建索引场景
这一小节主要来讲讲我们在什么情况下可以创建索引，以及在创建索引的过程中需要注意的一些点。
- 频繁作为
WHERE查询条件的字段
某个字段在SELECT、UPDATE、DELETE语句的 WHERE 条件中经常被使用到，那么就需要考虑给这些字段创建索引了。尤其是在 数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
- 经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立组合索引 。
- 字段的数值有唯一性的限制
索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引，当然如果是主键索引的话，对于数据更加严格，最好数据是递增的，而且不能更改。这样可以更快速地通过该索引来确定某条记录。
- 区分度高的列适合作为索引
区分度是指字段值的分布情况，比如“性别”这个字段，基本只有男(1)和女(0)两种情况，那么它的区分度非常低，如果用来作为索引就非常糟糕。
那么如何判断一个字段的区分度呢？
可以使用公式select count(distinct a) / count(*) from t1 计算字段的区分度，越接近1越好，一般超过33%就算比较高效的索引了。
DISTINCT字段需要创建索引
有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。
比如查询语句SELECT DISTINCT(student_id) FROM `student_info`;我们可以针对student_id字段添加索引，因为索引会对数据按照某种顺序进行排序，去重会快很多。
- 多表 JOIN 连接操作时，考虑创建索引
- 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
- 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致。
- 建立索引字段数据类型在业务允许范围内尽量小
以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，这是因为：
- 数据类型越小，在查询时进行的交操作越快。
- 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
- 使用字符串前缀创建索引
字符串很长的数据作为索引字段有两个问题：
- B+树索引中的记录需要把该列的完整字符串存储起来，更费时，且浪费空间。
- B+数据查询定位的过程中会进行字段比较匹配，如果字符串过长，那在做字符串比较时会占用更多的时间。
我们可以通过截取字段的前面一部分内容建立索弓l,这个就叫前缀索引， 比如语法：alter table shop add index(address(12))，这里的12就是获取address字段的前12位建立索引。
我们可以通过count(distinct left(列名, 索引长度))/count(*)方式获取表中数据的区分度，选择一个合适的数值, 越接近于1越好，说明越有区分度。
例如：
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度 count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度 count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度 count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度 from company;
- 联合索引的字段顺序
- 有单字段查询的字段放在左侧。由于"最左前缀原则"，可以增加联合索引的使用率。
- 联合索引把区分度搞(散列性高)的列放在前面。
- 多个字段同时查询的情况下，联合索引优于单值索引
如果有多个字段都要进行查询，那么这种情况下创建联合索引由于单个索引，一方面可以大幅减少索引的数量，降低新增、修改数据时维护索引的成本，另外一方面，正确的联合索引的查询效率是要优于单值索引。
6种不适合创建索引场景
这里基本是上面适合创建索引场景的方面，这里也单独列出来，做一个强调。
- 在where中使用不到的字段，不要设置索引
WHERE条件 (包括 GROUP BY、ORDER BY) 里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。
- 数据量小的表最好不要使用索引
如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引 对查询效率的影响并不大。
- 区分度低的数据不要建立索引
字段中如果有大量重复数据、区分度低的列上也不适合创建索引。比如"性别"字段上只有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
- 经常更新的字段谨慎建立索引
因为更新表中的索引字段，需要同步维护索引数据结构，会降低更新时间。
- 不建议用户无序的值作为索引
因为索引的一大特点就是有序，添加一条数据，会按照顺序插入到索引结构中，如果这个值较小，会导致其他记录发生移动，也就是所谓的“页分裂”，严重影响性能，这也是为什么一直要求表中的主键ID是自增的。
- 限制表中的索引数量
一个表的索引数量太多的话，就要谨慎考虑添加新的索引了，一般不超过6个，这时候我们可以：
- 删除不再使用或者很少使用的索引
- 清理冗余或者重复的索引
总结
本文对索引的类别做了一个讲解，以及详细叙述创建、删除、查看表中索引的方式，最最关键的还是我们该如何正确的建立索引，这直接影响到查询性能。如果对大家有帮助的话，留下一个赞吧。
