mysql查询性能优化二

1.关联子查询优化:使用EXISTS 替代IN查询
select * from film where film_id in (select film_id from film_actor where actor_id= 1)
mysql优化器改写后：
select * from film where exits ( select * from film_actor where actor_id= 1 and film.film_id = film_actor.film_id );
mysql执行计划：先选择对film全表扫描，然后根据film_id逐个执行子查询，如果外面表数据很大，则会很糟糕。
优化(2)后：
selcet film.* from film inner join film_actor using(film_id) where actor_id = 1;
2.子查询和exists可以等价互换，根据场景去使用
3.UNION的限制,当使用limit时，要在每个子句上都使用，效率高
4.MIN(),查询最小值,当name是非索引，则会进行全表扫描
SELECT MIN(act_id) FROM actor WHERE name = “mark”
优化sql:
SELECT act_id FROM actor USE INDEX(PRIMARY) WHERE name = “mark” LIMIT 1;
这个策略会让mysql扫描少的行。
5.COUNT()函数
统计结果集行数；统计某个列值的数量（统计列值数量要求为非null）；
常见的错误是在括号内指定了一个列却希望统计结果集的行数。如果想知道结果集行数最好使用COUNT(*),性能会更好。
6.简单的优化
例如1
SELECT COUNT(*) FROM city WHERE ID >5
通过SHOW STATUS 的结果可以看到该查询需要扫描4097行。如果将条件反转一下，先查id小于等于5的数据，然后用总数据减去能得到同样的结果，却可以将扫描行数减少很多，5行内。
SELECT (SELECT COUNT() FROM city) -COUNT() WHERE ID<=5
例如2
需求：在同一个查询中统计同一个列的不同值的数量，即返回不同颜色的商品数量，不能使用or,也不能在where中指定颜色，因为颜色的是互斥的。
sql方案1
SELECT SUM(IF(color = ‘blue’,1,0)) AS blue,SUM(IF(color = ‘red’,1,0)) AS red FROM items;
sql方案2
SELECT COUNT(color = ‘blue’ OR NULL) AS blue,COUNT(color = ‘red’ OR NULL) AS red FROM items
7.优化关联查询
1）确保ON或者USING子句的列上有索引，在创建索引时要考虑关联顺序，当表A和表B用列C来关联的时候，如果优化器关联的顺序时B，A，则就不需要在表B的对应的列上建立索引。只需要在关联顺序的第二个表的相应列创建索引。
2）确保任何ORDER BY 和GROUP BY 只是涉及到一个表中的列，这样MYSQL才能使用索引来优化该过程。
8.优化子查询
优化子查询，尽量用关联来替代，但不是绝对的，如果mysql5.6或更新的版本或者MariaDB，就可以忽略这个建议。
9.LIMIT分页优化
分页操作使用LIMIT和order by子句，若对应的列有索引，通常效率还不错，否则mysql需要大量的文件排序操作。
当偏移量非常大的时候，如Limit 10000,10，这样代价很大，优化这个查询，要么在页面中限制分页数量，要么优化大偏移量的性能。
优化此类分页最简单的办法是使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作在返回所需的列。对于大偏移量的时候，效率提升非常大。
SELECT film_id,desc From film ORDER BY tiltle LIMIT 50,5;
优化后sql:
SELECT film_id,desc From film INNER JOIN (SELECT film_id film ORDER BY title Limit 50,5) AS lim USING(film_id);
这里“延迟关联” ，提升效率，mysql会扫描很少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以优化关联查询中的LIMIT子句。

LIMIT和OFFSET的问题，其实是OFFSET的问题，他会导致mysql扫描大量不需要的行然后再抛弃掉，如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用offset，例如，若需要按照租赁记录做分页，那么可以根据最新一条租借记录向后追溯，这样可行是因为租借记录的主键是单调增长的。
使用下面查询获得第一组结果
SELECT * FROM rental Order by retal_id DESC LIMIT 20;
假设上面查询返回的主键为16049 到 16030 的租借记录，那么下一页查询就应该从16030这个点开始
SELECT * FROM rental where retal_id < 16030 order by retal_id DESC limit 20
10.UNION优化
尽量使用UNION ALL，如果不需要过滤调重复的数据，因为如果使用UNION，则mysql，会使用DISTINCT关键字来对临时表的数据做唯一性检查，代价非常高。
11.避免重复查询刚更新的数据
若在更新行的同时需要返回最新的数据，要怎样避免重复查询呢？可以使用变量来解决。
例如：客户需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么。
简单方法：
update table set lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM table WHERE id = 1;
使用变量:
update table set lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now；
看起来也是2个查询，但第二个查询不需要查询任何表，很快返回。