MySQL查询优化

MySQL查询优化常见手段有:分解关联查询、覆盖索引、延迟关联(deferred join)、强制索引查询(force index)等。

Sql Joins syntax

覆盖索引

若索引包含(覆盖)查询中所需的所有投影列,称为覆盖索引,MySQL直接从索引上返回数据。

延迟关联

延迟关联(deferred join)结合覆盖索引做条件过滤,利用返回的主键再关联回原表获得更多的投影列。

SELECT  
c1,c5,c6,c7 
FROM a
where c2='M' order by c3 limit 100000, 10;

SELECT
 c1,c5,c6,c7 FROM a
INNER JOIN 
(SELECT id FROM a as b WHERE b.c2='M' ORDER BY b.c3 LIMIT 100000, 10) AS c
USING(id);

强制索引

如果了解force index的用途,并清楚知道当前查询所涉及的表,使用force index强制索引或许是个选择。

STRAIGHT_JOIN

若事实证明MySQL的自动优化策略,没有STRAIGHT_JOIN指定驱动表的方式有效,可以尝试STRAIGHT_JOIN。

union all代替union

union all的前提是两个结果集中没有重复数据,union对结果集做合并时会进行唯一性过滤。

in和exists

in为先执行子查询,exists先执行外层表,二者区别在于驱动表的顺序

in适合外层表数据集大,子查询数据集小的情况。

exists适合于外层表数据小,内层表数据集大。

分页优化

若ID是自增有序,在无过滤条件的分页场景中,依赖于ID先做范围筛选,再加载数据分页数据。

select xx from a 10000,10;
select xx from a where id > 10000 limit 10;

like索引

对索引字段进行like查询时,能把‘%’加在中间或最后,就应避免把‘%’加在最左边。

避免在索引列上使用函数过滤

SELECT count(*)FROM us_hotdog_purchases WHERE YEAR(purchase_time)='2018'
SELECT count(*)FROM us_hotdog_purchases WHERE buying_at> ='2018-01-01'AND purchase_at <'2019-01-01'

避免OR条件

SELECT count(*) FROM fb_posts WHERE username = 'Mark' OR post_time > '2018-01-01'

SELECT …
FROM …
WHERE username = 'Mark'
    UNION
SELECT …
FROM …
WHERE post_time > '2018-01-01'

避免混合排序

SELECT username, post_type FROM fb_posts ORDER BY username ASC, post_type DESC

避免运算操作

select xx from a where c1*5=10;
select xx from a where c1 = 2;

避免隐式类型转换

join字段类型尽量保持一致,避免隐式类型转换。

where子句过滤条件的数据类型,与传入参数的类型保持一致。