MySQL复习笔记-高效使用索引

引言

原文链接：https://yunsonbai.top/2020/11/18/mysql-useindex/
本文主要记录了高效使用索引的内容，有不足的还望大牛指正。

索引列不能是表达式

其实这个在大学那会老师就讲过，可能没有遇到大数据量之前没有深刻的体会。下边例子中status是索引列:
select … where status=5 会用到索引
select … where status+1=6 全表扫描

人能看明白status+1=6 等价于 status=5 但是MySQL无法解析，只能去原表一个一个的取一个一个的计算。
当然表达式还有类似于 func(status) = 5这种操作也是不行

一句话，想要用索引，就别给索引列加表达式，除非哪天MySQL能解析了。

提高索引的选择性

选择性: 差异数/总数。

• 例子一：
  比如你生成了20个uuid1值，那么差异数就是20，总数也是20，选择性为1，最好的选择性。MySQL中用没有选择性为1的索引呢，有，比如唯一索引。

  可以看到选择性的范围为 0<选择性<=1

• 例子二：
  在某个表中使用status字段做索引，而业务规定，status的取值只能为 0 或者 1，那这个表的选择性为 2 / 总条数，因为你只可能出来0或者1，差异数为2。
  此时随着数据的增长，选择性越来越差。

应该很容易理解，表一定的情况下，使用选择性约高的索引列做查询，绝大情况下MySQL的查询效率就越高。
比如用了唯一索引，一下定位只有一条，根本不用一个一个的去过滤。
如果使用了例子二中的索引列，如果你需要获取特定行，你肯定需要添加附加过滤条件，例如 select … where status=1 and xxx=xx。但是MySQL需要过滤一半的数据给你找出想要的数据。

接着讨论，为什么是绝大情况查询效率高不是一定呢？还是例子二，比如status确实只有0或者1两种情况，但是这表如果当做任务表，这个就需要实际情况实际分析了，status为0是待执行任务，为1是执行完毕的任务，那如果90%以上的数据，都是执行完的任务，而你要查询的是当前待执行的任务，那使用status当索引列进行查询，那效率也是相当的高的。

在使用text等类型是要注意
如果有个字段使用的是text类型，理论上如果把整个字段当做索引，那选择性肯定是很高，但是MySQL不允许索引这种类型的完整列，即便允许，那代价也是相当高，不见得能提升查询效率，试想一下，如果我们的书本，把每节内容当做目录，那这个目录还会有意义么？

解决之道，可以考虑使用前缀索引或者前边提到的类似crc32的策略。

前缀索引就是说，使用原列的前边部分长度的内容当做索引，比如我们有个字段content，类型为text，我们可以考虑使用content[0:5]当做索引，这里5不是定数，根据情况，如果前五个字符就能使得选择性较高，那就可以使用。当然前缀不行我们还可以考虑后缀。

我之前是使用crc32解决的，但是在某个业务场景不能满足，有一次让我查询出二级域名为test.com的数据总数，这个crc32基本就不能用了，当然一条条过滤也能查出来，后来想想为了提高查询效率，把二级域名取出来，单独作为索引列。

使用explain验证

explain是个非常好用的工具，可以方便的验证你接下来要执行的查询语句的特性

• 例子一:
  explain select * from news where id=100 \G
  id: 1
  select_type: SIMPLE

  table: news
   type: const
  

  possible_keys: PRIMARY

      key: PRIMARY
  key_len: 4
      ref: const
     rows: 1
    Extra:
  

  使用了主键索引，常数级(const)操作

• 例子二:
  explain select * from news where title=’xxx’ \G
  id: 1
  select_type: SIMPLE

  table: news
   type: ALL
  

  possible_keys: NULL

     key: NULL
  key_len: NULL
     ref: NULL
    rows: 124
   Extra: Using where
  

  没有使用索引，全表扫描，这个要看看自己的查询语句是否有问题了

• 例子三:
  explain select * from news where id=100 or create_time=’2020-11-05’ \G
  id: 1
  select_type: SIMPLE

  table: news
   type: index_merge
  

  possible_keys: PRIMARY,idx_create_time

      key: PRIMARY,idx_create_time
  key_len: 4,8
      ref: NULL
     rows: 2
    Extra: Using union(PRIMARY,idx_create_time); Using where
  

  这个Extra显示出MySQL做了索引合并，如果对多个索引使用or操作，需要耗费大量的cpu和内存资源在算法的缓存、排序和合并上，如果数据量巨大。一旦遇到这种情况要查看一下查询语句如何避免or操作，或者看看表结构是不是设计上存在问题。

其他关于explain的命令例子不在列举，网上可自行查看，很多资料和字段的解释。

合理安排查询语句列顺序

一般情况需要把选择性高的列放到最前列，当然也不一定选择性高的列放前边就一定能提高性能，这个还要看具体列的值。

举个例子吧， 比如有个表有500万条数据，其实status是一个索引，可能值有0，1，2，3四个值，另外一个字段是active，可能值为1-10000，从选择性来看，显然active的选择性优于status，从一般规律来讲，我们需要报active放在最前边，也就是说会按下边的语句写：
select * from table1 where active=xx and status=xx。 问题来了。

情况一：绝大部分status都是3，只有十几条是0。上边的语句还对于status=0的情况就不是最优，显然把status放在前边，直接过滤出十几条后在过滤active，效率更高。

情况二：延续情况一，如果是status=3.那显然，上边的语句就非常合适。

在选择怎么是有索引或者怎么建立索引的时候还是要考虑当前的应用场景，来实现最优的查询效果。从经验角度讲，一般情况居多，把选择性高的索引放在最前边一般可以得到不错的查询效率。

理解聚簇索引和非聚簇索引

聚簇索引

聚簇索引不是索引类型，是一种数据存储方式。以InnoDB为例。聚集是把相邻的数据和键值紧凑的存储。
InnoDB会在B-Tree树上的节点中存索引，而在叶子中存储所有的行数据，节点中的索引用的是主键。InnoDB值聚集在同一页面中的记录。

聚集的数据的优点

• 相关数据存放在一起。尤其在获取相邻id的数据时，能减少磁盘io，只需读取少数数据页即可
• 数据访问快。索引和数据在同一个B-Tree树中，不用读取数据行
• 最大限度的提高了io密集型应用的性能
• 行号发生改变，只要主键值不变，二级索引不用维护。(得益于二级索引的索引结构)

缺点

• 插入速度严重依赖插入的值。按照自增主键或者自增整形作为聚簇索引插入是最快的。
• 更新代价增加，如果更新聚簇索引列，有可能使服务器付出很大的代价，比如有上千万条数据，我们把中间某条充当聚簇索引的列改了，会导致行移动。还可能会导致页分裂。
• 二级索引访问需要两次索引，而不是一次。（二级索引，叶子节点存储的是主键值，存储引擎使用二级索引时，先找到叶子节点的主键值，再根据主键值去聚集索引中查对应的行）

插入数据时需要注意:

• 尽量使用自增主键列作为聚簇索引的节点值
• 不要使用散列值作为聚簇索引节点值，这样基本失去了聚集性，对于io密集型应用，不是个好选择。

为什么尽量不要使用散列作为聚簇索引的节点值？

• 型数据:
  对于自增型int类型作为节点值的话，InnoDB在存储数据时，直接把新数据放在页的最后边，当达到最大填充因子时，也就是达到页的15/16，会把新的记录记到新的页，这样一来页基本都被顺序的数据填满。
• 散列数据
  因为新插入的数据的主键不一定比之前的数据大，不能直接放在索引的最后，需要找到合适的位置，然后分配空间，导致InnoDB不得不频繁的做页分裂，大量数据被移动，最终还会导致碎片。严重影响插入效率。

非聚簇索引

非聚簇索引，当然也是一种数据存储方式，以MyISAM为例，对于非聚簇索引，在B-Tree树中，节点存储索引值，叶子节点存储行号，也就说说，还需要读取数据行获取数据。
非聚簇索引存储方式，主键和二级索引无差别，都是节点存储索引值，叶子存储行号

缺点:

• 一旦行号改变，所有非聚簇索引数据都要重新建立。
• 需要额外的读取数据行。

理解覆盖索引

索引包含需要查询的字段的值，称为覆盖索引。
充分利用覆盖索引的优点:

• 减少数据访问量，数据就在索引上。
• 如果能充分利用覆盖索引，可以极大提高io密集型应用的性能，因为索引小可以加载到内存。
• 顺序存储，有利于范围查询，极少i/o

到底该怎么用？如果一个表中存在索引 user_id，那么select user_id from tablename limit 10 这个语句就是用了覆盖索引。

怎么确定是否使用了覆盖索引，如果查询语句是用了覆盖索引，当使用explain时，Extra列可以看到Using index

冗余索引

新建索引时，比如已有索引keyA_B(a, b) 这时候如果在单独创建keyA(a)，这就造成了索引冗余，因为keyA_B的最左前缀索引已经包含了key_A，在创建key_A就是一种冗余。但是如果创建key(b, a)就不算冗余索引。

冗余索引会带来的问题：很显然你创建的索引越多，对于增、改、删都会带来额外的开销，因为这些操作都可能引起索引的变化，不得不去维护。

小技巧

• 避免使用多个范围条件。在进行范围查询时，范围字段的后续字段将无法使用索引，例如 where age > xx and status > xx。 这时候status将无法使用索引，如果status的值只有 0 1 2 3四种结果，可以换成 where status in (2,3) and age > xx。 这样MySQL可以同时使用 status和age两个索引（in操作实际是等值操作）
• 范围条件放在where语句最后，由于前边提到的原因，如果使用 where age>xx and status=2 and type=2 后边的status和type都将无法使用索引，所以要换成 where status=2 and type=2 and age>xx
• 谨慎使用limit。对于limit 0 10，不管怎么样，都会很快返回，但是对于 limit 1000000, 10 则会慢的出奇，因为偏移量很大，MySQL需要过滤大量的数据来去掉不用的数据。解决办法之一是延迟关联，先使用覆盖索引查出来主键，在用主键去查。看下边的例子，其中sub_task_id是唯一索引，原表数据量3000万+：
  select from yuntable limit 1000000,10; 需要3秒以上的时间
  select from yuntable inner join (select id from yuntable limit 1000000,10) as tpm_table using(id); 只需要200毫秒左右