mysql like 优化走索引 mysql like语句优化

优化 LIMIT 分页

-- 执行耗时：1.379s
SELECT * from vio_basic_domain_info LIMIT 1000000,10;

处理分页慢查询的方式一般有以下几种：

思路一：构造覆盖索引

通过修改 SQL，使用上覆盖索引，比如我需要只查询表中的 app_name、createTime 等少量字段，那么我只需在 app_name、createTime 字段设置联合索引，即可实现覆盖索引，无需全表扫描。
适用于查询列较少的场景，查询列数过多的不推荐，耗时：0.390s。

SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10;

思路二：优化 offset

无法用上覆盖索引，那么重点是想办法快速过滤掉前 100w 条数据。我们可以利用自增主键有序的条件，先查询出第 1000001 条数据的 id 值，再往后查 10 行。
原理：先基于索引查询出第 1000001 条数据对应的主键 id 的值，然后直接通过该 id 的值直接查询该 id 后面的 10 条数据。
适用于主键 id 自增的场景，耗时：0.471s。

SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

方法三：“延迟关联”

耗时：0.439s，延迟关联适用于数量级较大的表。
这里我们利用到了覆盖索引+延迟关联查询，相当于先只查询 id 列，利用覆盖索引快速查到该页的 10 条数据 id，然后再把返回的 10 条 id 拿到表中通过主键索引二次查询。（表数据增速快的情况对该方法影响较小）

SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);

排查索引没起作用的情况

模糊查询尽量避免用通配符'%'开头，会导致数据库引擎放弃索引进行全表扫描

-- 不走索引
SELECT * FROM t WHERE username LIKE '%陈%'
-- 走索引
SELECT * FROM t WHERE username LIKE '陈%'

尽量避免使用 not in，会导致引擎走全表扫描。建议用 not exists 代替

-- 不走索引
SELECT * FROM t WHERE name not IN ('提莫','队长');
-- 走索引
select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','队长') and t1.id = t2.id);

尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE id = 1 OR id = 3

-- 优化方式：可以用 union 代替 or。如下：
SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE score IS NULL

-- 优化方式：可以给字段添加默认值 0，对 0 值进行判断。如下：
SELECT * FROM t WHERE score = 0

尽量避免在 where 条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

可以将表达式、函数操作移动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

当数据量大时，避免使用 where 1=1 的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描

SELECT username, age, sex FROM T WHERE 1=1
-- 优化方式：用代码拼装 SQL 时进行判断，没 where 条件就去掉 where，有 where 条件就加 and。

查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。
如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替

where 条件仅包含复合索引非前导列

如：复合（联合）索引包含 key_part1，key_part2，key_part3 三列，但 SQL 语句没有包含索引前置列"key_part1"，按照 MySQL 联合索引的最左匹配原则，不会走联合索引。

-- 不走索引
select col1 from table where key_part2=1 and key_part3=2
-- 走索引
select col1 from table where key_part1 =1 and key_part2=1 and key_part3=2

隐式类型转换造成不使用索引

如下 SQL 语句由于索引对列类型为 varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;