索引在数据库开发中占了很重要的位置，数据量少的时候，查询的速度可能不会很慢，当数据量超过百万级别时，没有索引查询就会非常缓慢，MySQL索引的建立对于MySQL的高效运行是很重要的；首先介绍一下索引的本质，再介绍几种常见的MySQL索引类型，索引的创建和使用实例的知识，以及索引的优化和注意事项等。

一、 索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
接下来先创建一个表，先不指定主键，创建命令如下：

1
2
3
4
5
6
7
CREATE TABLE `minhow` (
   `id` int(11) NOT NULL COMMENT '用户id',
   `username` varchar(12) NOT NULL DEFAULT '' COMMENT '用户名',
   `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
   `age` tinyint(2) NOT NULL COMMENT '年龄',
   `sex` tinyint(1) NOT NULL COMMENT '性别'
 );

然后通过存储过程，插入100W条记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Delimiter $
CREATE PROCEDURE minhow(IN begin INT, IN end INT)
BEGIN
	DECLARE id INT;
	DECLARE num INT;
	DECLARE str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	SET num = 0;
	SET id = begin;
	WHILE num < end DO
	INSERT INTO minhow(id, username, nickname, age, sex) VALUES(id, substring(str, FLOOR(1 + RAND() * 44), 6), substring(str, FLOOR(1 + RAND() * 44), 6), ROUND(0 + RAND() * 1), ROUND(0 + RAND() * 1));
	SET id = id + 1;
	SET num = num + 1;
	END WHILE;
END;
$

该存储过程的username，nickname，age, sex是随机生成的，插入这100W数据大概用了三分钟的时间，数据如下：

index_1

然后执行一条普通的查询语句，如下：

index_2

大概用时30ms左右，再使用explain命令查看执行计划，如果对explain命令比较陌生，可以查看官网explain了解详情，执行计划如下:

index_3

圈红色的地方是比较需要关注的地方，type依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL；type为all，表示全表查询；key为null，表示没有使用索引，rows为89W+，表示一共查询了80多万数据才找到结果，这个数据量是非常庞大的。现在对表添加不同的索引，再看看结果。

二、 索引的创建

2.1 普通索引

这是最基本的索引，它没有任何限制；也是我们大多数情况下用到的索引。下面在username字段创建普通索引，创建和删除命令如下：

1
2
3
CREATE INDEX index_name ON TABLE(column(length));//直接创建
ALTER TABLE table_name ADD INDEX index_name ON (column(length)); //通过修改表结构的方式添加
DROP INDEX index_name ON TABLE;//删除索引

接着在username字段上创建普通索引，创建成功后，查看索引的状态：

index_4

再通过username查找一条记录，实例如下：

index_5

从图中可以看出，这次查询用到了index_username索引，使用索引的类型是index，普通索引；大概查找90958条数据就得出结果，所以在username上加普通索引是个不错的选择。

2.2 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似，创建命令如下：

1
2
CREATE UNIQUE INDEX unique_name ON TABLE(column(length));//直接创建
ALTER TABLE table_name ADD UNIQUE unique_name(column(length)); //通过修改表结构的方式添加

接着在id字段上创建唯一索引，创建成功后，通过id查找一条记录：

index_6

从图中可以看出，这次查询用到了unique_id索引，type为const，表示表中最多有一个匹配行，const用于比较primary key或者unique索引；因为只匹配一行数据，所以很快。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

2.3 主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引；主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的；(注：在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)。创建命令：

1
ALTER TABLE table_name ADD PRIMARY KEY primary_key_name(column(length)); //通过修改表结构的方式添加

接着在id上创建主键索引，创建成功后，通过id查找一条记录：

index_7

这次查询用到了PRIMARY KEY 索引，因为只能含有唯一的一条非空记录，所以查询也是非常快的。

2.4 全文索引

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。如果需要处理的数据量很大，响应时间就会很长。在生成全文类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。（注：切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法）。创建命令：

1
ALTER TABLE table_name ADD FULLTEXT fulltext_name(column(length)); //通过修改表结构的方式添加

index_8

通过上图，可以看到添加全文索引用了10多秒的时间，还出现了警告；所以全文索引对于大数据量的表还是尽量少用。执行查询计划，如下图：

index_9

这里用到了fulltext_nickname的全文索引，但是type为ALL，差不多查询了全部记录才得出结果。

2.5 组合索引

上面的四种索引都是单列索引，而组合索引是多列索引，一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引包含多个字段。创建命令：

1
CREATE INDEX id_username ON table_name (column,column1,column2); //通过修改表结构的方式添加

在username，age，sex上加上组合索引，再执行查询计划如下：

index_10

从图中可以看出使用了，这次查询用到了username_age_sex组合索引，组合索引遵循”最左前缀”原则，简单的理解就是只从最左面的开始组合；例如：1.只查询age，sex就没用到索引；因为少了username这个字段2.查询WHERE username LIKE ‘%ABC’ AND sex = 1 AND age = 0也没用到索引，并不是只要包含这三列的查询都会用到该组合索引，因此使用LIKE作范围查询时，需要特别注意；而这三种情况都能用到组合索引：1.username2.username，sex3.username，sex，age。

三. 索引的优化和注意事项

3.1 索引优点

1.1 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性。
1.2 建立索引可以大大提高检索的数据,以及减少表的检索行数。
1.3 在表连接的连接条件 可以加速表与表直接的相连 。
1.4 在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)。
1.5 建立索引,在查询中使用索引 可以提高性能。

3.2 索引缺点

2.1 在创建索引和维护索引 会耗费时间,随着数据量的增加而增加。
2.2 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间。
2.3 当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

3.3 使用索引注意事项

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的，一般来说：
3.1 在经常需要搜索的列上,可以加快索引的速度。
3.2 主键列上可以确保列的唯一性。
3.3 在表与表的而连接条件上加上索引,可以加快连接查询的速度。
3.4 在经常需要排序(order by),分组(group by)和的distinct 列上加索引可以加快排序查询的时间,(单独order by 用不了索引，索引考虑加where或加limit)。
3.5 在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)。
3.6 like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’将可以用到索引。
3.7 索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ‘ ‘字符串。
3.8 使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作。
3.9 不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描。
3.10 选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快。

3.4 索引选择

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好；以下情况下不建议建索引：
4.1 查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求。
4.2 很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率，这是由B+Tree的性质决定的。
4.3 定义为text和image和bit数据类型的列不应该增加索引。
4.4 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系。

最后更新： 2018年01月06日 19:10

原始链接： http://blog.minhow.com/articles/database/mysql-index-use/

赏
× 请我吃糖~
打赏二维码