索引条件下推ICP

MySQL 5.6开始支持ICP（Index Condition Pushdown），不支持ICP之前，当进行索引查询时，首先根据索引来查找数据，然后再根据where条件来过滤，扫描了大量不必要的数据，增加了数据库IO操作。在支持ICP后，MySQL在取出索引数据的同时，判断是否可以进行where条件过滤，将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据，减少了不必要数据被扫描带来的IO开销。在某些查询下，可以减少Server层对存储引擎层数据的读取，从而提供数据库的整体性能。

ICP相关控制参数
index_condition_pushdown：索引条件下推默认开启，设置为off关闭ICP特性。

  1. mysql>show variables like 'optimizer_switch';
  2. | optimizer_switch | index_condition_pushdown=on
  3. # 开启或者关闭ICP特性
  4. mysql>set optimizer_switch = 'index_condition_pushdown=on | off';
  5. # 开启profiling进行跟踪SQL执行期间每个阶段的资源使用情况。
  6. mysql>set profiling  = 1;

在开启ICP特性后，对于条件where name = ‘Lyn’ and nickname like ‘%SK%’可以利用复合索引(name,nickname)减少不必要的数据扫描，提升SQL性能。

辅助索引idx_nickname(nickname)内部是包含主键id的，等价于(id，nickname)的复合索引，尝试利用覆盖索引特性将SQL改写为select Id from users01 where nickname like ‘%SK%’ **。

全文索引

MySQL 5.6开始支持全文索引，可以在变长的字符串类型上创建全文索引，来加速模糊匹配业务场景的DML操作。它是一个inverted index（反向索引），创建fulltext index时会自动创建6个auxiliary index tables（辅助索引表），同时支持索引并行创建，并行度可以通过参数innodbft_sort_pll_degree设置，对于大表可以适当增加该参数值。
删除全文索引的表的数据时，会导致辅助索引表大量delete操作，InnoDB内部采用标记删除，将已删除的DOC_ID都记录特殊的FTS*_DELETED表中，但索引的大小不会减少，需要通过设置参数innodb_optimize_fulltext_only=ON后，然后运行OPTIMIZE TABLE来重建全文索引。

全文索引特征

  • 从MySQL 5.7开始内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。
  • 由于全文索引的缓存和批量处理的特性，Insert&Update操作是在事务提交时处理，只能看到提交后的数据。
  • 全文索引使用函数MATCH() ….. AGAINST()来进行检索，MATCH()中列个数及顺序必须和索引定义保持一致。
  • 只能用于InnoDB和MyISAM的表，不支持分区表，不支持%通配符搜索。
  • MATCH()列表与表的全文索引定义列完全匹配。
  • MySQL优化器Hint对于全文索引会被限制。

两种检索模式

  • IN NATURAL LANGUAGE MODE：默认模式，以自然语言的方式搜索，AGAINST(‘看风’ IN NATURAL LANGUAGE MODE ) 等价于AGAINST(‘看风’）。
  • IN BOOLEAN MODE：布尔模式，表是字符串前后的字符有特殊含义，如查找包含SK，但不包含Lyn的记录，可以用+，-符号。
    AGAINST(‘+SK -Lyn’ in BOOLEAN MODE);

对于SQL语句后面的条件nickname like ‘%看风%’默认情况下，CBO是不会选择走nickname索引的，该写SQL为全文索引匹配的方式：match(nickname) against(‘看风’)。

生成列

MySQL 5.7开始支持生成列，生成列是由表达式的值计算而来，有两种模式：VIRTUAL和STORED，如果不指定默认是VIRTUAL，创建语法如下：

col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL]

生成列特征

  • VIRTUAL生成列用于复杂的条件定义，能够简化和统一查询，不占用空间，访问列是会做计算。
  • STORED生成列用作物化缓存，对于复杂的条件，可以降低计算成本，占用磁盘空间。
  • 支持辅助索引的创建，分区以及生成列可以模拟函数索引。
  • 不支持存储过程，用户自定义函数的表达式，NONDETERMINISTIC的内置函数，如NOW()， RAND()以及不支持子查询

    生成列使用

    1. #添加基于函数reverse的生成列reverse_nickname
    2. mysql>alter table users01 add reverse_nickname varchar(200) generated always as (reverse(nickname));
    3. #查看生成列信息
    4. mysql>show columns from users01;
    5. | reverse_nickname | varchar(200) | YES  |     | NULL              | VIRTUAL GENERATED | #虚拟生成列
    6. 复制代码

    模糊匹配优化

    对于where条件后的like ‘%xxx’是无法利用索引扫描，可以利用MySQL 5.7的生成列模拟函数索引的方式解决，具体步骤如下：
  1. 利用内置reverse函数将like ‘%风云’反转为like ‘云风%’，基于此函数添加虚拟生成列。
  2. 在虚拟生成列上创建索引。
  3. 将SQL改写成通过生成列like reverse(‘%风云’)去过滤，走生成列上的索引。

添加虚拟生成列并创建索引。

  1. mysql>alter table users01 add reverse_nickname varchar(200) generated always as (reverse(nickname));
  2. mysql>alter table users01 add index idx_reverse_nickname(reverse_nickname);
  3. #SQL执行计划
  4. |  1 | SIMPLE      | users01 | NULL       | range | idx_reverse_nickname | idx_reverse_nickname | 803     | NULL |    1 |   100.00 | Using where |
  5. 复制代码

可以看到对于like ‘%xxx’无法使用索引的场景，可以通过基于生成列的索引方式解决。

作者：敖丙
链接：https://juejin.cn/post/6911101492497743879
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。