MySQL 索引

索引是一种能提高数据库查询效率的数据结构，是数据库中用来提高性能的常用工具；类似于一本字典的目录，可以帮你快速找到对应的记录。

优缺点

优点

索引可以加快数据查询速度，减少查询时间
唯一索引可以保证数据库表中每一行的数据的唯一性
缺点
创建索引和维护索引要耗费时间
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
以表中的数据进行增、删、改的时候，索引也要动态的维护
分类
所有的 MySQL 列都可以被设为索引，对相关列使用索引是提高 SELECT 性能的最佳途径。根据存储引擎可以定义每个表最大的索引数和最大索引长度，每种存储引擎对每张表支持最少 16 个索引，总索引长度至少为 265 个字节；大多数存储引擎有更高的限制。
数据结构
B+树索引：所有数据存储在叶子节点，复杂度为 O(logn)，适合范围查询
哈希索引: 适合等值查询，检索效率高，一次到位
全文索引：MyISAM 和 InnoDB 中都支持使用全文索引，一般在文本类型 char、text、varchar 类型上创建
R-Tree 索引: 用来对 GIS 数据类型创建 SPATIAL 索引
物理存储
聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据（Innodb 存储引擎）
非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列（Innodb 存储引擎）

在 InnoDB 存储引擎中，聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。
聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询

一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个
一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表

而在 MyISM 存储引擎中，它的主键索引，普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据

逻辑维度

主键索引：一种特殊的唯一索引，不允许有空值
普通索引：MySQL 中基本索引类型，允许空值和重复值
联合索引：多个字段创建的索引，使用时遵循最左前缀原则
唯一索引：索引列中的值必须是唯一的，但是允许为空值
空间索引：MySQL5.7 之后支持空间索引，在空间索引这方面遵循 OpenGIS 几何数据模型规则
B+TREE 索引
特点
以 B+TREE 结构存储数据，大大加快了数据的查询速度
在范围查找的 SQL 语句中更加适合（顺序存储）
场景
全值匹配的查询 SQL
联合索引汇中匹配到最左前缀查询
匹配模糊查询的前匹配
匹配范围值的 SQL 查询（not in和<>无法使用索引）
覆盖索引的 SQL 查询
HASH 索引
特点
基于 Hash 表实现，只有查询条件精确匹配 Hash 索引中的所有列才会用到
为 Hash 索引中的每一列都计算 hash 码并存储，所以每次读取都会进行两次查询
Hash 索引无法用于排序

Hash 不适用于区分度小的列上，如性别字段

创建索引

索引在创建表的时候可以同时创建，也可以随时增加新的索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option]...
    
    index_col_name:
        col_name [(length)] [ASC | DESC]

也可以是使用 ALTER TABLE 的语法来增加索引，语法与 CREATE INDEX 类似

删除索引

1	DROP INDEX index_name ON tbl_name

原则

选择唯一性索引

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

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

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

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

要在条件列上创建索引，而不是查询列

最适合索引的列是出现在 WHERE 语法中的列，或连接子句中指定的列，而不是出现在 SELECT 关键词后的列；如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度；为这样的字段建立索引，可以提高整个表的查询速度。

限制索引的数目

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

尽量使用数据量少的索引

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

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

尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

例如：TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间；如果只检索字段的前面的若干个字符，这样可以提高检索速度。

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

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

小表不应建立索引

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

小结

当对索引字段进行范围查询时，只有 BTREE 索引可以通过索引访问，而 HASH 索引则会进行全表扫描；如果一定要使用范围查询，那么创建索引时就应该选择 BTREE 索引。

B+TREE 索引可以进行范围查询，Hash 索引不能。
B+TREE 索引支持联合索引的最左侧原则，Hash 索引不支持
B+TREE 索引支持 ORDER BY 排序，Hash 索引不支持
B+TREE 索引使用 LIKE 进行模糊查询的时候，后面的语句（如 %）可以起到优化的作用，Hash 索引根本无法进行模糊查询
Hash 索引在等值查询上比 B+TREE 索引效率更高，但是索引列的重复值很多的话，Hash 冲突，效率降低

优缺点

优点

缺点

分类

数据结构

物理存储

逻辑维度

B+TREE 索引

特点

场景

HASH 索引

特点

创建索引

删除索引

原则

小结