Mysql查询优化

查询缓存

1.可以使用如下的语句来判断MySQL是否开启了查询缓存功能：

show variables like '%query_cache%';

2.如果想查看MySQL是否是读取的缓存，可以使用如下的语句：

show status like 'qcache_hits';

值得注意的是，如果表的数据存在更新的话，和该表相关的所有缓存都会被清空。如果当前的查询正好命中缓存，那么此次查询就会省略之后的所有操作，并直接从缓存中返回数据。

3.查询执行计划

MySQL服务器层对SQL语句进行一系列优化之后，会生成一颗查询指令树，然后执行引擎会利用api调用完成查询指令树并返回结果。可以使用如下的步骤来查看，MySQL优化之后的查询：

1.explain extended select*from actor;
2.show warnings;

时间去哪了 (SQL执行时间；SQL等待时间；然后通过分析&推理确认为什么时间去那里了。)

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

对于MySQL，最简单的衡量查询开销的三个指标如下：
响应时间
扫描的行数
返回的行数

没有哪个指标能够完美地衡量查询的开销，但它们大致反映了MySQL在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。
查询慢的原因基本都是：我们的不合理操作导致查询的多余数据太多了。
常见原因有以下：
1.查询不需要的记录。
2.多表关联时返回全部列
3.总是取出全部列

常用优化技巧

1.用索引

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

2.关联子查询

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子句的列上有索引
在创建索引的时候就要考虑到关联的顺序。

3.UNION使用
如果希望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

4.COUNT()查询
比如如果想统计文章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

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

6.limit分页
下面这条查询，非常常见。

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变量时.或相反情况。