MySQL优化特定类型的查询（书摘备查）

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433213

1. 优化count

count有两种不同的工作方式：统计值的数量和统计行的数量。值是一个非空的表达式（null意味着没有值）。如果在count()的括号中定义了列名或其它表达式，count就会统计这个表达式值的次数。

count的另外一种形式就是统计结果中行的数量。当MySQL知道括号中的表达式永远不会为null的时候，它就会按这种方式工作。最明显的例子就是count(*)，它是count的一种特例，它不会把通配符*展开成所有列，而是忽略所有列并统计行数。

一个常见的错误就是在想统计行数的时候，在count的括号中放入列名。如果想知道结果的行数，应该总是使用count(*)。

myisam对于没有where子句的count(*)很快，它仅仅是统计表中行的数量而已。如果mysql知道某列（col）不可能为null，那么它在内部也能把(col)转化为count(*)。

简单优化

select count(*) from world.city where id > 5;

改写为：

select (select count(*) from world.city) - count(*) from world.city where id <= 5;

使用一个查询统计一列中不同值的数量

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

使用count代替sum的等价查询：

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

更多复杂的优化

通常来说，使用了count的查询很难优化，因为它们通常需要统计很多行（访问很多数据）。在mysql内部优化它的唯一其它选择就是覆盖索引。如果这还不够，那么就需要更改应用程序架构。可以考虑使用汇总表，还可以利用外部缓存系统，比如数据库缓存服务器（MemCached）。在优化过程中，通常都会面临相似的窘境，那就是只能在速度、精确、简单三个特性中选两个。

2. 优化联接

. 确保on或using使用的列上有索引。在添加索引时要考虑联接的顺序。比如联接表A和B的时候使用了列C，并且优化器按照从B到A的顺序联接，那就不需要在B上添加索引。没有使用的索引会带来额外的开销。通常来说，只需要在联接中的第二个表上添加索引，除非因为其它的原因需要在第一个表上添加索引。

. 确保group by或order by只引用了一个表中的列，这样，mysql可以尝试对这些操作使用索引。

. 要谨慎地升级mysql。因为在不同的版本中，联接的语法，运算符的优先级及其它行为会发生改变。

3. 优化子查询

对子查询的最重要的建议就是尽可能地使用联接。联接之所以更有效一些，是因为mysql不需要在内存中创建临时表来完成逻辑上的需要两个步骤的查询工作。

4. 优化group by和distinct

在很多情况下，mysql对这两种方式的优化基本是一样的。实际上，优化过程要求它们可以互相转化。这两种查询都可以从索引受益。通常来说，索引也是优化它们的最重要手段。

当不能使用索引时，mysql有两种优化group by的策略：使用临时表或文件排序进行分组。任何一种方式对于特定的查询都有可能是高效的。可以使用sql_small_result强制mysql选择临时表，或者使用sql_big_result强制它使用文件排序。

如果要对联接进行分组，那么通常对表的ID列进行分组会更加高效，例如下面的查询效率就不够高：

select actor.first_name, actor.last_name, count(*)

from sakila.film_actor inner join sakila.actor using (actor_id)

group by actor.first_name, actor.last_name;

而下面的查询效率会更高：

select actor.first_name, actor.last_name, count(*)

from sakila.film_actor inner join sakila.actor using (actor_id)

group by film_actor.actor_id;

按照actor.actor_id分组比film_actor.actor_id效率更高，因为扫描更少的行。这个查询利用了演员的姓名依赖于actor_id这一事实，所以它会返回相同的结果。但这并不意味着每次在select中选择非分组的列都会得到同样的结果，可以通过配置sql_mode参数来禁止在select中使用未在group by中出现的列。如果根本不在意得到的值，或者知道每个分组中的数据都是不同的，那么就可以使用min()或max()绕过sql_mode的限制，就像下面这样：

select min(actor.first_name), max(actor.last_name), ...;

完美主义者会认为分组条件错了，这种看法是正确的。虚假的min()或max()说明查询的结构有问题，但有时候我们只想让mysql尽可能快地执行查询。完美主义者会喜欢下面这个查询方案：

select actor.first_name, actor.last_name, c.cnt

from sakila.actor inner join

(select actor_id, count(*) as cnt from sakila.film_actor group by actor_id) as c using (actor_id);

子查询会创建并填充临时表，有时这种方式带来的开销会比稍微变通一点的方案高一些。要记住，子查询创建的临时表是没有索引的。

在一个分组查询中，select子句使用非分组的列通常都不是一个好主意，因为结果可能是不确定的，并且如果更改了索引或优化器采用了不同的策略，那么结果页可能被轻易地改变。大部分这样的查询都应该看成“事故”（服务器不会对这种查询发出警告信息），它们可能是懒惰的结果，但是这肯定不是为了优化而故意设计的。最好可以显示地报告这种情况。建议在服务器的sql_mode参数中加上only_full_group_by，这样服务器就会对这种查询产生一个错误信息。

除非定义了order by，否则mysql会自动对group by里面的列进行排序。因此，如果显示包括一个含有相同列的order by子句，则对mysql的实际执行性能没有什么影响。如果查询包括group by，但想要避免排序结果的消耗，可以使用order by null来跳过自动排序。也可以在group by后面加上asc或desc来限定排序的类别。

使用rollup优化group by

分组查询的一个变化就是要求mysql在结果内部实现超级聚合（Supper Aggregation）。可以在group by后面加上with rollup来实现这个需求，但是它也许没有被很好地优化。可以使用解释器检查执行方法，确认分组是否已经通过文件排序或临时表来完成，然后移除with rollup，并查看分组方法是否有变化。

有时在应用程序里面进行超级聚合会更好，尽管那意味着要从服务器提取更多列。也可以在from子句中使用子查询或临时表来保持中间结果。

最好的方式是把with rollup移到应用程序里面。

5. 优化order by

在某些情况中，mysql可以使用一个索引来满足order by子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

create table rental (

...

primary key (rental_id),

unique key rental_date (rental_date, inventory_id, customer_id),

key idx_fk_inventory_id (inventory_id),

key idx_fk_customer_id (customer_id),

key idx_fk_staff_id (staff_id),

...

);

下面的查询可以使用索引进行排序：

... where rental_date = '2005-05-25' order by inventory_id, customer_id;

... where rental_date = '2005-05-25' order by inventory_id desc;

... where rental_date > '2005-05-25' order by rental_date, inventory_id;

下面的查询不能使用索引进行排序：

... where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc;

... where rental_date = '2005-05-25' order by inventory_id, staff_id asc;

... where rental_date = '2005-05-25' order by customer_id;

... where rental_date > '2005-05-25' order by inventory_id, customer_id;

... where rental_date = '2005-05-25' and inventory_id in (1,2) order by customer_id;

6. 优化limit和offset

在分页系统中使用limit和offset是很常见的，它们通常会和order by一起使用。索引对于排序较有帮助，如果没有索引就需要大量文件排序。

一个常见的问题是偏移量很大，比如查询使用了limit 10000,20，它就会产生10020行数据，并且会丢掉前10000行。这个操作的代价非常高。假设所有页面的访问频率相等，平均每个查询扫描表的一半数据，为了这种查询，可以限制一个分页里访问的页面数目，或者让偏移量很大时查询效率更高。

一个提高效率的简单技巧就是在覆盖索引上进行偏移，而不是对全行数据进行偏移。可以将从覆盖索引上提取出来的数据和全行数据进行联接，然后取得需要的列。这样更有效率。看看下面的查询：

select film_id, description from sakila.film order by title limit 50,5;

如果表非常大，这个查询最好写成下面的样子：

select film.film_id, film.description

from sakila.film inner join

(select film_id from sakila.film order by title limit 50,5) as lim using (film_id);

这种方式效率更高，它让服务器在索引上面检查尽可能少的数据，一旦取得了需要的行，就把它们联接到完整的表上面，并取出其余的列。相似的技巧可以应用到有limit子句的联接上面。

有时可以把limit转换为位置性查询，服务器可以以索引范围扫描的方式来执行。例如，如果预先计算并且索引了一个表示位置的列，那么就可以把查询写成下面这样：

select film_id, decription from sakila.film where position between 50 and 54 order by position;

类似的问题还有对数据进行排名，但它往往和group by混在一起，基本可以肯定的是需要预先计算和存储排名。

如果确实需要优化分页系统，也许应该利用预先计算好的汇总数据。作为替代方案，可以联接只含有order by子句需要的主键和列的冗余表，也可以考虑使用全文检索。

7. 优化sql_calc_found_rows

对于分页显示，另外一种常用的技巧是对含有limit的查询添加语句sql_calc_found_rows，这样就可以知道没有limit的时候会返回多少行数据。这个选项只是告诉服务器生成结果并去掉结果中不需要的部分，而不是在得到需要的数据后就立即停止。这个选项的代价很高。

一个较好的设计就是把页面调度放到“下一页”链接上。假设每页有20个结果，那么查询就应该limit 21行数据并且只显示20行，如果结果中有21行，就会有下一页。

另外一种办法就是提取并缓存大量的数据，比如1000行数据，然后从缓存中获取后续页面的数据。这种策略让应用程序知道一共有多少数据。如果结果少于1000行，那么应用程序就知道有多少页；如果多于1000行，程序就可以显示“找到的数据多于1000个”。这两种策略都比重复产生完整的结果，然后丢弃绝大部分要高得多。

如果不能使用这两种策略，但可以使用覆盖索引，那么使用单独的count(*)也比sql_calc_found_rows快得多。

8. 优化联合

mysql总是通过创建并填充临时表的方式执行union，它不能对union进行太多的优化。

可能需要把where、limit、order by或其它条件手工地（比如将它们恰当地从外部查询拷贝到union的每个select语句中）“下推”到union中，以帮助优化器优化它。

重要的是始终要使用union all，除非需要服务器消除重复的行。如果忽略了all关键字，mysql就会向临时表添加distinct选项，它会利用所有行来决定数据的唯一性。这种操作开销很大。但是要知道all不会删除临时表，mysql总是把结果放在临时表中，然后再把它们取出来，即使没有必要这么做（比如可以把数据直接返回给客户端）时也会如此。