【数据库】MySQL查询优化

共计 4442 个字符，预计需要花费 12 分钟才能阅读完成。

欢迎关注公众号：【爱编程 】
如果有需要后台回复 2019 赠送 1T 的学习资料 哦！！

在这个快速发展的时代，时间变得
越来越重要，也流逝得非常得快，有些人长大了，有些人却变老了。稍不留神，2019 已经过完了三分之一。回首这四个月收获什么，懂得了什么？欢迎留言分享给我哟。

** 言归正传：
MySQL 的查询怎么才能更快，更合理？除了加索引还有什么可以学习的呢？**

要想更好地学习某样东西，从其原理和运作方式入手更容易掌握。道理你们都懂，我就不废话了。

下图是MySQL 查询执行流程图：

客户端发送一条查询给服务器。
服务器先检查查询缓存，如果命中了缓存，则立刻返回查询在缓存中的结果。否则会进入下一个阶段。

3. 服务端进行 SQL 解析、预处理、再由优化器生成对应的执行计划。
4.MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
5. 将结果返回给客户端。

对于 MySQL，最简单的衡量查询开销的三个指标如下：

响应时间
扫描的行数
返回的行数

没有哪个指标能够完美地衡量查询的开销，但它们大致反映了 MySQL 在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。

查询慢的原因基本都是：我们的不合理操作导致 查询的多余数据太多了 。
常见原因有以下：

1. 查询不需要的记录。
2. 多表关联时返回全部列
3. 总是取出全部列

最简单且见效最快的方式就是给你的条件加索引（主键索引，普通索引，唯一索引等）。注：索引是要另开辟一块 空间存储 的，所以不能不要钱滴都加索引。

MySQL 的子查询实现是非常糟糕的。比如下面的

SELECT * FROM book WHERE book_id IN (SELECT book_id FROM author WHERE author_id = 1)

MySQL 对 IN()列表中的选项有专门的优化策略，一般会认为 MySQL 会先执行子查询返回所有包含 author_id 为 1 的 book_id。

或许你想 MySQL 的运行时这样子的：

SELECT GROUP_CONCAT(book_id) FROM author WHERE author_id = 1
SELECT * FROM book WHERE book_id IN (1,21,3,45,656,766,213,123)

但是，MySQL 会将相关的外层表压到子查询中的，就是下面的样子：

SELECT * FROM book WHERE EXISTS 
(SELECT * FROM author WHERE author_id = 1 AND book.book_id = author.book_id)

原因：因为子查询需要 book_id，所以 MySQL 认为无法先执行这个子查询，而是先对 book 进行全表扫描，然后再根据 book_id 进行子查询。具体可以 EXPLAIN 该 SQL 进行分析。

建议：
1. 使用左外连接（LEFT OUTER JOIN）代替子查询。

SELECT * from book LEFT OUTER JOIN author USING(book_id) WHERE author.author_id = 1

影响因素：还有数据表放的位置等，具体应用场景就只能你自己 explain 该语句对比哪种性能比较好点。

2. 确保 ON 或者 USING 子句的列上有索引
在创建索引的时候就要考虑到关联的顺序。

如果希望 UNION 的各个子句能根据 LIMIT 只取部分结果集，或者希望能够先排好序再合并结果集的话。
第一个例子：会将 author 表和 user 表 两个表都存放到一个临时表 中，再从临时表中取出前 20 条。

(SELECT first_name FROM author ORDER BY last_name)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name)
LIMIT 20

对比上面的这样子，就有很大的改善了。

(SELECT first_name FROM author ORDER BY last_name LIMIT 20)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name LIMIT 20)
LIMIT 20

比如：
求最小值
第一种方案：

SELECT MIN(id) FROM article WHERE author = 'zero'

第二种方案：

SELECT id FROM article USE INDEX(PRIMARY) WHERE author = 'zero' LIMIT 1

和第一种方案的对比，效果其实是一样的，但是它们的性能略有不同，具体还请自己具体场景分析，择优选择。

比如如果想统计文章 id 大于 25 的数量，可以如下：

EXPLAIN SELECT COUNT(*) FROM article WHERE id >25

另外一种思路：可以先查询文章总数，减去小于等于 25 的数量。仅仅提供思路，具体效果还是你具体情况，自己比较，择优选择。

EXPLAIN SELECT (SELECT COUNT(*) FROM article) - COUNT(*) FROM article WHERE id <=25

题外话：
如果需要区分不同颜色的商品数量时，可以如下做法：

seelct count(color = 'blue' OR NULL) as blue,COUNT(color = 'red' OR NULL)  AS RED FROM items

它们的优化最有效的方法就是用索引来。
但是 GROUP BY 有时候用得不对，索引是会失效的。
比如：把两个单独的索引合并成一个组合索引，即把 where 条件字段的索引和 group by 的分组字段索引组合成一个。

解决方法：参考这篇函数索引

下面这条查询，非常常见。

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

但是如果这个表很大的时候，那么这个 50 变成 100654 这样子的话，这里 MySQL 就要扫描 100654+ 5 条数据，然后丢弃 100654 条，仅仅去最后 5 条。
一种思路：

select film_id,description from film inner join (select film_id from film order by title limit 50,5) as lim USING(film_id);

该思路是通过 延迟关联 将大大提升查询效率，它让 MySQL 扫描尽可能少的页面。获取需要访问的记录后，再更加关联列会原表查询所需要的所有列。以上并不一定符合你，具体还需 explain 对比择优使用。

小结：
总体来说都是围绕着 尽量少全表扫描，尽量使用索引 进行优化。
最后往往是要自己在实际场景 多用 explain 分析 是否有更好的 sql 解决方案。

1．隐式转换导致索引失效.
这一点应当引起重视. 也是开发中经常会犯的错误. 由于表的字段 tu_mdn 定义为 varchar2(20), 但在查询时把该字段作为 number 类型以 where 条件传给 Oracle, 这样会导致索引失效.

 错误的例子：select * from test where tu_mdn=13333333333;
 正确的例子：select * from test where tu_mdn='13333333333';

2. 对索引列进行运算导致索引失效
所指的对索引列进行运算* 包括(+，-，，/，! 等)

 错误的例子：select * from test where id-1=9;
 正确的例子：select * from test where id=10;

3. 使用内部函数导致索引失效.
对于这样情况应当创建基于函数的索引.

// 错误的例子：select * from test where round(id)=10; // 说明，此时 id 的索引已经不起作用了
 // 正确的例子：首先建立函数索引
create index test_id_fbi_idx on test(round(id));
// 然后 
select * from test where round(id)=10;

4. 不要将空的变量值直接与比较运算符（符号）比较。
如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。

5. 不要在 SQL 代码中使用双引号。
因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。

6. 以下使用会使索引失效，应避免使用

a. 使用 <>、not in、not exist、!=
b. like “%_” 百分号在前（可采用在建立索引时用 reverse(columnName)这种方法处理）
c. 单独引用复合索引里非第一位置的索引列. 应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被 where 子句引用时，优化器才会选择使用该索引。
d. 字符型字段为数字时在 where 条件里不添加引号.
e. 当变量采用的是 times 变量，而表的字段采用的是 date 变量时. 或相反情况。

暂时统计到这么多，如果有更多的以后再补充。

EXPLAIN 是用来分析 SQL 执行情况分析的

EXPLAIN 命令的输出内容大致如下:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列的含义如下:

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.

ref: 哪个字段或常数与 key 一起被使用

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

更详细的可以参考这篇【性能优化神器 Explain 使用分析】或者【高性能 MySQL】

查询优化目的就是为了快速得到结果，所以每当写完 SQL 应该思考以下几点：

是否需要全表查询以及返回的数据是否合理。
是否需要索引，索引是否合理。
是否有更好的解决办法。

如果对 Java、大数据感兴趣请长按二维码关注一波，我会努力带给你们价值。觉得对你哪怕有一丁点帮助的请帮忙点个赞或者转发哦。
关注公众号 【爱编码】，回复2019 有相关资料哦。

背景

原理

MySQL 发送查询请求，到底做了什么工作？

是什么导致 MySQL 查询变慢了？

常用优化技巧

1. 用索引

2. 关联子查询

3.UNION 使用

4. 最大值和最小值

5.COUNT()查询

6.GROUP BY 和 DISTINCT

7.limit 分页

索引会失效的场景

MySQL 的 EXPLAIN 的使用

总结

最后

Just My Socks（注册教程内含优惠码）