mysql查询优化技术_MySQL查询优化

覆盖索引

索引包含(覆盖)查询中所需的所有投影列，称为覆盖索引，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

避免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子句过滤条件数据类型，与传入参数类型保持一致。