mysql查询优化

SQL优化，是一个非常重要技能；而我们常说的SQL优化，一般指的是查询优化。

准备工作

首先，我们需要创建一张表a

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key_A1` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `key_A2` int(11) DEFAULT NULL,
  `key_A3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13244 DEFAULT CHARSET=utf8;

插入了10万条数据

mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+

然后建个复合索引

alter table a add index idx_a_key123(key_A1, key_A2, key_A3)

其次，你需要对explain关键字有一定的了解（可以查看我另一篇文章explain详解）以及profile的相关知识（profile的使用）

order by

order by优化，其实就是索引优化，具体看下索引使用技巧;

order by 也是遵循左前缀法则的，不同的是，它需要带上where后面的条件，也就说，如果where条件加上order by的顺序如果是索引的顺序，那么order by就可能根据索引来进行排序。

文字说的可能不好理解。下面来看案例：

图片.png

这样就能利用索引去排序，不会产生内排序

没用上索引则会导致filesort，内排序也会影响性能，最好优化；

图片.png

上面都是左前缀法则。

有点需要注意的是：order by如果有多个字段进行排序，则字段顺序要和索引中的字段顺序一致，索引排序的规则规则也要一样（要么都是升序，要么都是降序）

当然如果数据量大，你可能需要调整sort_buffer_size和max_length_for_sort_data参数的设置。

group by

group by优化和order by类似，group by的实现也是先排序，然后再分组；只不过group by多了个having，能再where中过滤的就在where中过滤，where过滤要比having高。如果group by没用上索引，会有临时表产生，临时表会严重影响性能。

图片.png 图片.png

上面两个例子中只是group by字段的顺序不一样，但是性能确实天差地别。

小表驱动大表

多表查询，有个关键的因素，就是小表驱动大表，先加载小表会会减少磁盘IO；这里不得不提in和exists

曾经看到不少优化建议，建议使用exists，不建议使用in，这个其实是不对的。

in

in的执行过程是，先执行子查询，然后执行主查询，如果子查询中的表相对于主表非常小的话，查询是比较快的。

exists

exists的执行流程是，从主查询中取数据然后去子查询中判断是否为true，true则保留，false则不保留；这个实际上先执行的主查询，如果主查询的表非常大，这回非常影响性能。

案例

图片.png
这里我添加了一张表b，只添加了10条记录

select * from a where key_A1 in (select key_B1 from b)
select * from a where EXISTS (select 1 from b where b.key_B1 = a.key_A1)

上面这两条SQL表达的意思是一样的，但是性能大概了差了20倍，使用in要比exists快20倍。

使用in的explain分析如下：

图片.png

使用exists：

图片.png

使用in现加载的是子查询，使用exists先加载的是exists外面的查询；
而a表是大表，b是小表；我们应该选择先加载小表，故应该用in查询。至于为什么要先加载小表，可以这样来记，这相当于一个嵌套的循环，外层循环需要和磁盘进行IO，外层循环的次数越少，性能就越高。

通过profile查看exists：

图片.png

发现存在大量的数据传输。

distinct

distinct和order by同时使用；一定小心，这很可能会导致索引的失效。

图片.png

使用distinct一定要注意，distinct出重实际上也是通过排序的。所以尽量让distinct的那个字段能够使用索引。where（不能有范围）后面的字段（按顺序）加上distinct的字段如果刚好和索引的顺序一致就能够使用上索引了，实际上也是左前缀法则；加上order by和group by 就无法使用索引了，相当于中间断了。