模糊匹配like %%怎么优化

开启ICP（索引条件下推）

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。****

可以通过索引筛选，返回给Server层筛选后的记录，减少不必要的IO开销。

如果where后只有一个 like ‘%xxx%’条件，表有主键的前提下，可以通过子查询优化

1
2
3

select  * from users01 a , 
(select id from users01 where nickname like '%SK%') b
 where a.id = b.id;

建立全文索引

两种检索模式

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
IN NATURAL LANGUAGE MODE：默认模式，以自然语言的方式搜索，AGAINST(‘看风’ IN NATURAL LANGUAGE MODE ) 等价于AGAINST(‘看风’)。
IN BOOLEAN MODE：布尔模式，表是字符串前后的字符有特殊含义，如查找包含SK，但不包含Lyn的记录，可以用+，-符号。
AGAINST(‘+SK -Lyn’ in BOOLEAN MODE);

创建全文索引：

1	alter table users01 add fulltext index idx_full_nickname(nickname) with parser ngram;

改写like语句：

1	select * from users01 where match(nickname) against('看风');

生成列（虚拟列）

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

索引很长的字段怎么优化？

有时候需要索引很长的字符(例如BLOB,TEXT,或者很长的VARCHAR)，这样会使得索引又大又慢。

改用哈希索引。
InnoDB是支持Btree索引，但不显式支持hash索引。可以使用生成列来间接使用哈希索引。
使用字符串的前几个字符作为索引(即前缀索引)。

1
2
3

ALTER TABLE `city_demo` ADD INDEX `idx_city` (`city`(7)) USING BTREE ;
-- 或者这个也行
ALTER TABLE `city_demo` ADD KEY `idx_city` (`city`(7))

当然在选择前缀时要选择合适的前缀索引长度，保持好的选择性。其方法主要是计算数据分布。

-- 查询重复次数最多的10条完整城市名称及其数量(图1)
SELECT COUNT(*) cnt, city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
-- 查询重复次数最多的10条城市名称(前3个字符)及其数量,可以发现:前3个字符的相同数量过大,不适合做前缀索引(图2)
SELECT COUNT(*) cnt, LEFT(city,3) pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
-- 查询重复次数最多的10条城市名称(前7个字符)及其数量,可以发现:前7个字符的相同数量和完整城市名称很相近了,可以考虑作为做前缀索引(图3)
SELECT COUNT(*) cnt, LEFT(city,7) pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

MRR

MRR全称是Multi-Range Read，是MYSQL5.6优化器的一个新特性，在MariaDB5.5也有这个特性。优化的功能在使用二级索引做范围扫描的过程中减少磁盘随机IO和减少主键索引的访问次数。二级索引通过将主键放在buffer中排序将随机IO转换为顺序IO。对于随机读写能力较弱的机械硬盘有比较大优化。

裸泳的猪

mysql_优化系列_索引优化

模糊匹配like %%怎么优化

索引很长的字段怎么优化？

MRR