MYSQL索引之基础知识

本篇文章，我们将从索引基础开始，介绍什么是索引以及索引的几种类型，然后学习如何创建索引以及索引设计的基本原则。

1、索引概念

• 索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
• 索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

2、索引种类

普通索引：
仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

唯一索引：
与普通索引类型，不同的是：加速查询 + 列值唯一（可以有null）

ALTER TABLE `table_name` ADD UNIQUE (`column`);

主键索引：
加速查询 + 列值唯一（不可以有null）+ 表中只有一个

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) ;

组合索引：
多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

ALTER TABLE `table_name` ADD INDEX index_name_name_name (`column1`, `column2`, `column3`);

全文索引：
对文本的内容进行分词，进行搜索。全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

ps. 索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

3、索引设计的原则

3.1 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

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

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

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

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

3.4 限制索引的数目

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

3.5 尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。

例如，对一个CHAR（100）类型的字段进行全文检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

3.6 尽量使用前缀来索引

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

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

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

3.8 小表不应建立索引

包含大量的列并且不需要搜索非空值的时候可以考虑不建索引

注：
文档最后推荐几篇不错文章：
mysql索引的新手入门详解
mysql索引知识点汇总