Mysql学习系列 -- 索引设计原则以及常见索引区别

背景：项目需要，新建了三个表，三个表数据插入、更新、查询频率都比较高。建表之初，考虑表中数据量较小，未来也不可能很大，并未考虑索引建立。提交DBA上线时，DBA却强烈建议我添加表索引。可我考虑到上述的情况，没听取DBA的建议。

结果，后期，我为自己的自负付出了代价：系统性能在数据全量检索时出现了数据拉取不到的问题。最后，我用重新修改三个表结构，添加索引，结果，系统性能上真的得到了提升。

那么，添加和不添加索引为何会有那么大的差别呢？索引到底是什么？我应该如何添加索引？添加时又要注意哪些问题呢？别急，下面我们一一来学习。

一、索引是什么

索引：是一个单独的，存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。这里有两点需要注意

（1）建立索引是需要消耗磁盘空间的，有空间上的开销。

（2）索引与表数据是一一对应的关系，这种关系需要维持，尤其是当数据表结构发生变化了时，会产生性能消耗。

数据库中的索引有主键索引、唯一索引、一般索引、聚簇索引等几大项。想要了解索引的工作原理，请参考原理

二、索引设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的？

1．为唯一性字段创建唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。如果数据库中某字段具有唯一性，强烈建议为这个字段创建唯一性索引。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，请为该字段添加索引。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文
检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。找到并删除他，别问我为什么，上面已经说明了。

----------------------------------------------------------

三、索引注意事项

（1）表的主键、外键必须有索引；

（2）数据量超过300w的表应该有索引；

（3）经常与其他表进行连接的表，在连接字段上应该建立索引；

（4）经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

（5）索引应该建在区分度高的字段上；

（6）索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

（7）复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

（8）组合索引建立需要注意事项

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

4、总结

索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加、删除时的性能。