Mysql 常用查询性能优化

对于大多数中小型应用，最多和最明显的的性能问题应该是出自最底层的数据库，数据库的性能又很复杂，SQL优化，索引等等，

在我工作七年的生涯中，也踩过无数的坑，以下写6点比较常用的心得，帮助大家避免和绕过一些比较明显的坑。

1.查询优化

如果你想查询所有在电影 Academy Dinosaur 中出现的演员，千万不要这样写查询：

SELECT * FROM sakila.actor
    INNERT JOIN sakila.film_action USING(actor_id)
    INNERT JOIN sakila.film USING(film_id)
    WHERE sakila.film.title = “Academy Dinosaur”;

应该只取需要的列

SELECT sakil.actor.* FROM sakila.actor …..;

2.子查询优化

INNER JOIN 很多可以可以代替 IN 和 EXISTS等子查询

3.在同一表上更新和查询

MYSQL不允许对同一张表进行查询和更新，比如：

DELETE FROM tb1 WHERE id in (select id from tb2 where tb2.col = 1);//这句SQL虽然合法，但是无法在MYSQL中执行

SQL本身没有问题，只是MYSQL不允许这样处理，可以生成新的表来绕过限制，比如

delete from tb1 where id in (
select a.id from (select id from tb2 where tb2.col = 1) as a
)

4.优化COUNT查询

如何在同一个查询中统计同一列的不同值的数量，以减少查询的语句量。例如，假设可能需要通过一个查询返回各种不同颜色的商品数量，以减少查询的语句量。此时不能使用OR语句，因为这样做就无法区分不同颜色的商品数量，也不能在WHERE条件中指定颜色，比如：select count(*) from items where color=‘blue’ and color = ‘red’; 因为条件是互斥的，下面的查询可以解决这个问题：

select sum(if(color=‘blue’,1 ,0 )) as blue, sum(if(color = ‘red’, 1, 0)) as red from items;

也可以使用COUNT() 而不是 SUM() 实现同样的目的，只需要将满足的条件设置为真，不满足条件设置为NULL即可；

select count(color=‘blue’ or null) as blue, count(color=‘red’ or null) as red from items;

5.优化查询子查询

关于优化子查询最重要的建议就是，使用关联查询代替，如果是MySQL5.6或者更新的版本，那么可以直接忽略

6.优化LIMIT分页

系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量实现，同时加上合适的ORDER BY字句，如果有对应的索引，效率通常会不错。

但是有一个常见的问题，就是在偏移量非常大的时候，例如 LIMIT 1000,20 这时候MySQL需要查询10020 条记录然后只返回最后20条，前面1000条都被抛弃，代价非常高。

如果表非常大的时候，查询最好改为下面的样子：

select film.film_id, film.description from film
inner join (
    select film_id from film order by title limie 50, 5
) as lim using(film_id)