索引是数据库中用来提高性能的常用工具，简单的介绍 MySQL5.7 支持的索引类型及其设计原则。

概述

所有的 MySQL 列都可以被设为索引，对相关列使用索引是提高 SELECT 性能的最佳途径。根据存储引擎可以定义每个表最大的索引数和最大索引长度，每种存储引擎对每张表支持最少 16 个索引，总索引长度至少为 265 个字节；大多数存储引擎有更高的限制。
MyISAM 和 InnoDB 存储引擎的表默认创建的都是 BTREE (B 树) 索引，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。

BTREE 索引

特点

  • 以 B+树结构存储数据，大大加快了数据的查询速度

  • 在范围查找的 SQL 语句中更加适合（顺序存储）

    场景

  • 全值匹配的查询 SQL

  • 联合索引汇中匹配到最左前缀查询
  • 匹配模糊查询的前匹配
  • 匹配范围值的 SQL 查询（not in和<>无法使用索引）
  • 覆盖索引的 SQL 查询

HASH 索引

特点

  • 基于 Hash 表实现，只有查询条件精确匹配 Hash 索引中的所有列才会用到
  • 为 Hash 索引中的每一列都计算 hash 码并存储，所以每次读取都会进行两次查询
  • Hash 索引无法用于排序

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

    小结

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

    使用

    创建索引

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

    1. CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    2.   [index_type]
    3.   ON tbl_name (index_col_name,...)
    4.   [index_option]
    5.   [algorithm_option | lock_option]...
    7.   index_col_name:
    8.       col_name [(length)] [ASC | DESC]

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

    删除索引

    1. DROP INDEX index_name ON tbl_name

    原则

  1. 选择唯一性索引

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

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

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

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

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

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

  1. 限制索引的数目

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

  1. 尽量使用数据量少的索引

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

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

  1. 尽量使用前缀来索引

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

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

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

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

  1. 小表不应建立索引

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