很多人甚至不知道如何优化Like%xxx%%xxx这样的模糊匹配，即使列上有选择性高的索引，也不会使用。在MySQL中，ICP特性、全文索引、生成列索引可以用来解决这类问题。下面将推送ICP、全文索引、根据索引条件生成列索引以及如何使用它们来解决模糊匹配的SQL性能问题。下推索引条件ICPICP介绍MySQL5.6开始支持ICP（IndexConditionPushdown）。在不支持ICP之前，进行索引查询时，先根据索引查找数据，然后根据where条件进行过滤，扫描大量不需要的数据，增加了数据库IO操作。支持ICP后，MySQL在提取索引数据时可以判断是否可以进行where条件过滤，将部分where过滤操作放在存储引擎层，提前过滤掉不需要的数据，减少不需要的数据被扫描。I/O开销。在某些查询下，服务器层可以减少从存储引擎层读取数据，从而提高数据库的整体性能。ICP具有以下特点ICP相关控制参数index_condition_pushdown：默认开启索引条件下推，设置为off则关闭ICP特性。mysql>showvariableslike'optimizer_switch';|optimizer_switch|index_condition_pushdown=on#开启或关闭ICP功能mysql>seoptimizer_switch='index_condition_pushdown=on|off';ICP流程假设用户表users01(id,name,nickname,phone,create_time)，表中数据有11W。由于ICP只能用于二级索引，所以在name和nickname列上创建复合索引idx_name_nickname(name,nickname)，分析SQL语句select*fromusers01wherename='Lyn'andnicknamelike'%SK%'在关闭和打开的ICP执行中。关闭ICP特性的SQL性能分析，开启profiling，跟踪SQL执行过程中各个阶段的资源使用情况。mysql>setprofiling=1;关闭ICP特征分析SQL执行mysql>setoptimizer_switch='index_condition_pushdown=off';mysql>explainselect*fromusers01wherename='Lyn'andnicknamelike'%SK%';|1|SIMPLE|users01|NULL|ref|idx_name_nickname|idx_name_nickname|82|const|29016|100.00|Usingwhere|#查看SQL执行过程中各个阶段的资源使用情况mysql>showprofilecpu,blockioforquery2;|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+------------------------------+--------+---------+-------------+------------+------------+|开始|0.000065|0.000057|0.000009|0|0|......|执行|0.035773|0.034644|0.000942|0|0|#执行阶段耗时0.035773秒。|end|0.000015|0.000006|0.000009|0|0|#status状态变量分析|Handler_read_next|16384|##请求读取的行数|Innodb_data_reads|2989|#物理读取的数据总数|Innodb_pages_read|2836|#LogicalreadTotal|Last_query_cost|8580.324460|#SQL语句的开销COST，主要包括IO_COST和CPU_COST。通过explain分析执行计划。当关闭CP特性时，SQL语句使用复合索引idx_name_nickname，Extra=UsingWhere。首先通过复合索引idx_name_nickname的前缀从存储引擎中读取name='Lyn'的所有记录，然后在服务器端使用where过滤nicknamelike'%SK%'。handler_read_next=16384表示扫描了16384行数据，SQL实际只返回12行，耗时50ms。对于这种扫描大量数据行，只返回少量数据的SQL，可以从两个方面来分析。1.索引选择率低：对于索引(name,nickname)，name作为前导列出现，CBO会选择使用该索引，因为扫描索引的代价小于全表扫描的代价，但是因为name列的基数不高，导致在索引中扫描大量数据，导致SQL性能低下。Column_name:nameCardinality:6可以看出users01表中name的不同值只有6个，6/114688的选择率很低。2、数据分布不均：对于wherename=?，当name数据分布不均时，SQL第一次返回的值返回的结果集很小，CBO会选择使用索引，缓存SQL执行计划在同时其实不管name传什么值，以后都会用到indexscan。这其实是错误的。如果传入的name值为Fly100，返回表中80%的数据，使用全表扫描会更快。|名称|计数(*)|+----------------+----------+|Grubby|12||Lyn|1000||Fly100|98100|MySQL8.0引入列直方图统计特性，主要针对索引列数据分布不均的情况进行了优化。启用ICP特性的性能分析启用SQL执行的ICP特性分析mysql>setoptimizer_switch='index_condition_pushdown=on';#executionplan|1|SIMPLE|users01|NULL|ref|idx_name_nickname|idx_name_nickname|82|const|29016|11.11|Usingindexcondition|#status状态变量分析|Handler_read_next|12||Innodb_data_reads|2989||Innodb_pages_read|2836||Last_query_cost|8580.324460|从执行计划可以看出复合索引idx_name_nickname,Extra=Usingindexcondition,并且只有12被扫描行数据显示，ICP特征被索引条件下推，SQL总共耗时10ms。与禁用ICP特性相比，SQL性能提升5倍。开启ICP特性后，由于nickname等like条件可以通过索引进行过滤，存储引擎层通过索引与where条件进行比较，剔除不符合条件的记录。这个过程不需要读取记录，只把过滤后的记录返回给server层。记录下来，减少不必要的IO开销。Extrausingwhere展示的索引扫描方式：当查询使用到索引时，需要回表查询需要的数据。usingindex条件：查询使用了索引，但需要回表查询数据。usingindex：当查询使用覆盖索引时会出现。usingindex&usingwhere：查询使用了索引，但是需要的数据都可以在索引列中找到，不需要回表查询数据。模糊匹配重写优化启用ICP特性后，对于name='Lyn'和nicknamelike'%SK%'的条件，可以使用复合索引（name，nickname）减少不必要的数据扫描，提高SQL性能。但是对于wherenicknamelike'%SK%'全模糊匹配查询，是否可以利用ICP特性来提升性能呢？首先，在昵称上创建一个单列索引idx_nickname。mysql>altertableusers01addindexidx_nickname(nickname);#SQL执行计划|1|SIMPLE|users01|NULL|ALL|NULL|NULL|NULL|NULL|114543|11.11|Usingwhere|从执行计划看type=ALL,Extra=Usingwherego扫描的是全部，没有使用ICP功能。辅助索引idx_nickname(nickname)里面包含了主键id，相当于(id,nickname)的复合索引。尝试使用覆盖索引功能将SQL重写为selectIdfromusers01wherenicknamelike'%SK%'。|1|SIMPLE|users01|NULL|index|NULL|idx_nickname|83|NULL|114543|11.11|Usingwhere;Usingindex|从执行计划中，type=index,Extra=Usingwhere;使用索引，索引全扫描，但是需要的数据都可以在索引列中找到，不需要回表。利用这个特性，原始SQL语句先获取主键id，然后通过id关联到原表，分析其执行计划。选择*fromusers01a,(selectidfromusers01wherenicknamelike'%SK%')bwherea.id=b.id;|1|SIMPLE|users01|NULL|index|PRIMARY|idx_nickname|83|NULL|114543|11.11|Usingwhere;Usingindex||1|SIMPLE|a|NULL|eq_ref|PRIMARY|PRIMARY|4|test.users01.id|1|100.00|NULL|从执行计划来看，索引idx_nickname没有了，不需要回表访问数据，执行时间从60ms减少到40ms，type=index表示没有使用ICP特性，而是使用where；使用索引可以通过使用索引扫描而不回表的方式来减少资源开销，提高性能。全文索引MySQL5.6开始支持全文索引，可以在变长字符串类型上创建全文索引，加速模糊匹配业务场景下的DML操作。它是一个倒排索引（reverseindex）。创建全文索引时，会自动创建6个辅助索引表（auxiliaryindextables）。同时支持索引并行创建。并行度可以通过参数innodb_ft_sort_pll_degree来设置。对于大表，这个值可以适当增加参数值。在删除全文索引表的数据时，会引起对辅助索引表的大量删除操作。InnoDB内部使用标记删除将删除的DOC_ID记录在特殊的FTS_*_DELETED表中，但不会减少索引的大小。需要通过设置参数innodb_optimize_fulltext_only=ON后，再运行OPTIMIZETABLE重建全文索引。全文索引特点INNATURALLANGUAGEMODE两种检索模式：默认模式，自然语言检索，AGAINST('Kanfeng'INNATURALLANGUAGEMODE)等价于AGAINST('Kanfeng')。INBOOLEANMODE：布尔模式，表格是字符串前后的字符有特殊含义，比如要查找包含SK但不包含Lyn的记录，可以使用+、-符号。AGAINST(布尔模式中的'+SK-Lyn');这时，搜索'%Lyn%'这样的昵称，通过反向索引关联数组，我们可以知道Lyn这个词存储在文档4中，然后在具体的辅助索引表中定位。全文索引分析为表users01的昵称添加支持中文分词的全文索引mysql>altertableusers01addfulltextindexidx_full_nickname(nickname)withparserngram;查看数据分布#设置当前全文索引表mysql>setglobalinnodb_ft_aux_table='test/users01';#查看数据文件mysql>select*frominformation_schema.innodb_ft_index_cache;+--------+--------------+------------+-------------+--------+----------+|WORD|FIRST_DOC_ID|LAST_DOC_ID|DOC_COUNT|DOC_ID|POSITION|+------+-----------+-------------+------------+--------+-----------+................|看风|7|7|1|7|3||大笑|7|7|1|7|0|全文索引相关对象分析#全文索引对象分析mysql>SELECTtable_id,name,spacefromINFORMATION_SCHEMA.INNODB_TABLESwherenamelike'test/%';|1198|test/users01|139|#存储从索引中清理出来的标记为删除的文档ID，其中_being_deleted_cache是??_being_deleted表的内存版本。|1199|test/fts_000000000000000000000000000000000000000000000000000000004Ae_being_deleted|140||1200|test/fts_00000000000000000000000004ae_being_being_delet_cache_cache|141|＃＃＃存储存储索引索引索引其中_deleted_cache是_deleted表的内存版本。|1202|test/fts_00000000000004ae_deleted|143||1203|test/fts_00000000000004ae_deleted_cache|144|模糊匹配优化对于sql语句nicknamelike'%watch%'后面的条件CBO默认不会选择使用nickname索引。匹配全文索引的SQL写法：match(nickname)against('Kanfeng')。mysql>explainselect*fromusers01wherematch(nickname)against('看风');|1|SIMPLE|users01|NULL|fulltext|idx_full_nickname|idx_full_nickname|0|const|1|100.00|Usingwhere;ft_hints:sorted|使用全文索引方式查询，type=fulltext，同时命中全文索引idx_full_nickname。从上面的分析可以看出，在MySQL中，可以利用全文索引来提高SQL对完全模糊匹配%%查询的效率。生成列MySQL5.7开始支持生成列。生成的列是根据表达式的值计算的。有两种模式：虚拟和存储。如果未指定，则默认为VIRTUAL。创建语法如下：col_namedata_type[GENERATEDALWAYS]AS(expr)[**VIRTUAL**|**STORED**][NOTNULL|NULL]生成列特性VIRTUAL生成列用于复杂的条件定义，可以简化和统一查询，不占空间，访问列会做计算。STORED生成的列作为物化缓存，对于复杂的情况可以降低计算成本和占用磁盘空间。支持创建辅助索引，分区和生成的列可以模拟功能索引。不支持存储过程，用户自定义函数的表达式，NOW()、RAND()等NONDETERMINISTIC内置函数，不支持子查询生成列。reverse(nickname));#查看生成列信息mysql>showcolumnsfromusers01;|reverse_nickname|varchar(200)|YES||NULL|VIRTUALGENERATED|#虚拟生成列模糊匹配优化对于like'%xxx'后where条件索引不可用扫描可以通过使用MySQL5.7的生成列来模拟函数索引来解决。具体步骤如下：使用内置的逆向函数将喜欢'%风云'逆向喜欢'云风%'，并根据该函数添加虚拟生成列。在虚拟生成列上创建索引。重写SQL以通过生成像reverse('%风云')的列进行过滤，并在生成的列上使用索引。添加虚拟生成列并创建索引。mysql>altertableusers01addreverse_nicknamevarchar(200)generatedalwaysas(reverse(nickname));mysql>altertableusers01addindexidx_reverse_nickname(reverse_nickname);#SQL执行计划|1|SIMPLE|users01|NULL|range|idx_reverse_nickname|idx_reverse_nickname|1UUL0。可见对于like'%xxx'不能使用索引的场景，可以通过基于生成列的索引方式解决。总结介绍了索引条件下ICP下推、全文索引、生成列的特点。可以利用这些特性来优化像%xxx%或者像%xxx这样的模糊匹配的业务SQL，可以有效减少不必要的数据读取，减少IO扫描和CPU开销，提高服务稳定性。对于MySQL各个版本发布的新特性，尤其是优化器和SQL相关的特性，大家要多多关注和了解，也许能找到适合自己业务场景的特性。我是敖丙，知道的越多，不知道的越多，下期见。
