MySQL查询优化

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

Sql Joins syntax

覆盖索引

如果索引包含(覆盖)查询中所需要的所有投影列,就称之为覆盖索引,MySQL从索引上返回需要的数据。

延迟关联deferred join

利用延迟关联(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的用途,并清楚知道当前查询所涉及的表,在查询时使用force index强制索引或许是个选择。

STRAIGHT_JOIN

如果事实证明MySQL的自动优化策略,没有STRAIGHT_JOIN指定驱动表的方式来得猛烈,试试STRAIGHT_JOIN也无妨。

union all代替union

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

清楚in和exists, not in和not 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

where子句避免运算操作

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

避免隐式类型转换

join字段类型尽量保持一致,避免发生隐式类型转换。where子句过滤条件的数据类型,与传入参数的类型保持一致。