MySQL: 索引的基本操作与设计原则及优化

概述

索引分类

索引的是否支持依赖于存储引擎的具体实现，

从功能上划分索引共有四种，分别是：普通索引、唯一索引、主键索引、全文索引。显然，普通索引涵盖了单列索引与联合索引，以及空间索引。

除前述之外，对于字段较长的内容可以在建立索引的同时设置索引长度，即为前缀索引。

其中Memory引擎不支持全文索引，支持hash索引。而InnoDB和MyIsam不支持hash索引。

对于索引的各类操作

创建

一般情况下如果一个表存在主键，那么InnoDB默认便会为其创建索引，即称之为主键索引。主要使用PRIMARY KEY(主键列)。进行声明。

而普通索引与组合索引一般使用INDEX 索引名(列...n)

eg: INDEX idx_sname(sname)、INDEX idx_sname_ssex(sname, ssex)

而全文索引使用FULLTEXT INDEX 索引名(列...n)

eg: FULLTEXT INDEX idx_desc(desc)

空间索引使用SPATIAL INDEX 索引名(列)

eg:SPATIAL INDEX idx_geo(geo)

删除

而如果想要删除这些索引一般使用ALTER 以及DROP INDEX 两种命令

eg1: ALTER TABLE 表名 DROP INDEX 索引名

eg2: DROP INDEX 索引名ON 表名

隐藏

主要目的在于想要强制停止使用某个索引，但由于删除可能无法进行，只得退而求其次而选择隐藏索引。

使用方法分别是在创建索引时使用INVISIBLE进行标记，和使用ALTER语句进行修改是否可用的状态。

eg1: INDEX idx_sname(sname) INVISIBLE

eg2: ALTER TABLE student ALTER INDEX sname INVISIBLE（VISIBLE）

设计原则

常见需要创建索引的场景

1. 字段具备唯一性限制必须建立唯一索引
2. 频繁作为WHERE查询条件的字段必须建立索引
3. 经常作为GROUP BY以及ORDER BY的字段需要建立索引
4. UPDATE、DELETE的WHERE条件字段必须建立索引
5. DISTINCT字段需要建立索引

索引选择

1. 对于字符串选择前缀索引, 通过 count(distinct left(字符串列, 前缀长度) / count(*) 计算合适的前缀长度
2. 使用最频繁的列设置为联合索引中的左列, 即当a、b、c建立联合索引等价于建立了a、ab、abc。也意味着要想使用已创建的联合索引，必须要保证列的使用顺序。(最左前缀原则)
3. 在多个字段都要创建索引的情况下, 联合索引优于单列索引

错误的选择

1. 有大量重复数据的列无需创建索引, 主要缘故在于二级索引在找到后需要回表操作, 重复数据过多有时倒不如不使用索引。
2. 避免对经常更新的表创建过多的索引, 这也就意味着索引也会频繁的变动
3. 无序数据不建议作为索引, 主要缘故在于存储引擎底层需要去维护有序性

索引优化

对于索引优化而言，大多产生于索引失效以及没有充分利用到索引的情况下，因此必须要重新去设计我们的索引。

而对于索引失效问题，主要缘故是使用索引与否依赖于优化器的成本预估。也就是说，下面虽然列出了许多导致索引失效的场景，但并不意味着索引一定会失去。

1. 未按照最佳左前缀法则使用列进行查询，即索引为abc，而查询 使用ac则会导致索引失效
2. 计算、函数、类型转换导致索引失效
3. 联合索引中某一字段运用范围查找(!=、<>、between)，将导致后续列无法使用索引。(顺序指的是索引创建时各列的顺序，而非SQL语句的条件顺序。因此在创建联合索引时应当把牵扯到范围查询的列放到最后)
4. 使用 IS NULL 可以使用索引，使用IS NOT NULL不可以使用索引，因此如果有查询是否为空的需求，理应使用int类型以0和1作为标记。(原因同上，同理还有NOT　LIKE)
5. LIKE以不确定开头(%LIKE)使用不能使用索引。(B+记录有序性) (Alibaba开发手册: 页面搜索严禁左模糊和全模糊，如有需要则走搜索引擎解决)

推荐的索引设计

由于B+Tree底层聚簇索引的有序缘故，因此主键设计要满足两个条件。其一为全局唯一，即无论是单机状态亦或分布式多系统状态都要能保证唯一性。其二为主键要具备单调递增的特性，后来生成的主键都要在之前主键的后面，以此保证插入时不会导致数据页的页分裂。

因此比较推荐的是拥有有序性的UUID。例如MySQL在8.0提供的使UUID压缩且可具备有序性的方法UUID_TO_BIN，亦或是雪花算法实现的UUID。

覆盖索引

将经常性需要查询的列统一为其创建联合索引，自此MySQL通过联合索引去查询这些数据时，发现存储的列已包含要获得的数据，从而无需回表操作。这种索引称之为覆盖索引。

创建自定义的哈希索引

当需要对某个较长字段进行检索时，可对其进行哈希处理，然后依照哈希值去建立索引，这样较长字段就可以获得更好的性能。

一些概念

索引下推(ICP)

当查询条件对应列部分或全部被设为某个联合索引，且成功使用了该索引时，会同时对索引中的数据去匹配那些处于联合索引中列的条件。从而在回表操作前提前的对数据通过多个条件进行过滤，即使某个条件对应列会导致索引失效。