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)

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

# MySQL