查询性能优化

1.MySQL能够使用如下三种方式应用where条件， 从好到坏依次为：
1.在索引中使用where条件来过滤不匹配的记录。 这是在存储引擎层完成的。
2.使用索引覆盖扫描（在Extra列中出现了Using index） 来返回记录， 直接从索引中过滤不需要的记录并返回命中的结果。 这是在MySQL服务器层完成的， 但无须再回表查询记录。
3.从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过撼。
2.如果发现查询需要扫描大量的数据但只返回少数的行， 那么通常可以尝试下面的技巧去优化它：
1.使用索引覆盖扫描， 把所有需要用的列都放到索引中， 这样存储引擎无须回表获取对应行就可以返回结果了（在前面的章节中我们已经讨论过了）。
2.改变库表结构。例如使用单独的汇总表（这是我们在第4章中讨论的办告）。
3.重写这个复杂的查询， 让MySQL优化器能够以更优化的方式执行这个查询（
3.一个复杂查询还是多个简单查询？
设计查询的时候一个需要考虑的重要问题是， 是否需要将一个复杂的查询分成多个简单的查询。在传统实现中， 总是强调需要数据库层完成尽可能多的工作， 这样做的逻辑在于以前总是认为网络通信、 查询解析和优化是一件代价很高的事情。
但是这样的想法对于MySQL并不适用，MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。 现代的网络速度比以前要快很多， 无论是带宽还是延迟。 在某些版本的MySQL上， 即使在一个通用服务器上， 也能够运行每秒超过10万的查询， 即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。 所以运行多个小查询现在已经不是大问题了。MySQL内部每秒能够扫描内存中上百万行数据， 相比之下， MySQL晌应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。 别害怕这样做， 好好衡量一下这样做是 不是会解决效率问题。
分成多个简单查询的好处：
1.让缓存的效率更高。 许多应用程序可以方便地缓存单表查询对应的结果对象。 例如，查询中的某个表的数据已经被缓存了， 那么应用就可以跳过一个查询。 再例如， 应用中已经缓存了ID为123、567、9098的内容， 那么第三个查询的IN（）中就可以少几个ID。 另外， 对MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了， 而拆分后， 如果某个表很少改变， 那么基于该表的查询就可以重复利用查询缓存结果了。
2.将查询分解后，执行单个查询可以减少锁的竞争。
3.在应用层做关联， 可以更容易对数据库进行拆分， 更容易做到高性能和可扩展。
4.查询本身效率也可能会有所提升
5.可以减少冗余记录的查询。 在应用层做关联查询， 意味着对于某条记录应用只需要 查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。 从这点看， 这样的重构还可能会减少网络和内存的消耗。
6.更进一步， 这样做相当于在应用中实现了哈希关联， 而不是使用MySQL的嵌套循环关联。 某些场景哈希关联的效率要高很多（本章后续我们将讨论这点）。
4.in()优化：
1.一个慢的in语句：

select * from applicant_info where 
caseCategoryCode in (
select caseCategoryCode from business_info where businessCode = 'fasfasdfsda')

用exists优化：

EXPLAIN select * from applicant_info where 
EXISTS (select 1 from business_info where businessCode = 'fasfasdfsda'
and business_info.caseCategoryCode = applicant_info.caseCategoryCode)

用inner join优化：

EXPLAIN select applicant_info.* from applicant_info,business_info
where businessCode = 'fasfasdfsda'
and business_info.caseCategoryCode = applicant_info.caseCategoryCode

5.union优化：

select caseCategoryCode,businessCode,tradeCode from applicant_info
union all
select caseCategoryCode,businessCode,tradeCode from business_info
limit 200

这条查询将会把 applicant_info中的200条记录和 business_info表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。 可以通过在UNION的两个子查询中分别加上一个
LIMIT 200来减少临时表中的数据：

(select caseCategoryCode,businessCode,tradeCode from applicant_info
limit 200)
union all
（select caseCategoryCode,businessCode,tradeCode from business_info
limit 200）
limit 200

现在中间的临时表只会包含 400条记录了，除了性能考虑之外，在这里还需要注意一点： 从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要加上一 个全局的ORDER BY和LIMIT操作。
6.索引合并优化：

在or语句的字段中都有索引，查询时会进行索引合并，这种看似性能得到一定的提升，但是性能并不是很好，索引合并会消耗一定的资源。用union all性能会更好
7.count()优化：
COUNT （）是一个特殊的函数， 有两种非常不同的作用，它可以统计某个列值的数量， 也 可以统计行数。 在统计列值时要求列值是非空的（不统计 NULL）。 如果在 COUNT（）的括号中指定了列或者列的表达式， 则统计的就是这个表达式有值的结果数。
下面sql都可以达到找出name='深圳众签科技有限公司’的数量：

select sum(if(name='深圳众签科技有限公司',1,0)) from applicant_info;
select count(if(name='深圳众签科技有限公司',1,null)) from applicant_info;
select count(1) from applicant_info where name='深圳众签科技有限公司';

1.如果对数量不需要特别精准，如在线人数，活跃人数等，可以使用explain代替。
2.如果需要查出的是大于某一数值的数量，那么可以通过下面sql优化：

SELECT  count(*) from world.City  
select COUNT(*) from world.City where  ID<= 5;
再加个相减

3.使用覆盖索引。
4.使用汇总表
5.使用缓存
7.优化关联语句：
1.确保ON或者 USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A 和表B用列c关联的时候， 如果优化器的关联顺序是B、A， 那么就不需要在 B表的对应列上建上索引。 没有用到的索引只会带来额外的负担。 一般来说， 除非 有其他理由， 否则只需要在关联顺序中的第二个表的相应列上创建索引。
2.确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列， 这样MySQL才有可能使用索引来优化这个过程。
3.当升级MySQL的时候需要注意：关联语句、 运算符优先级等其他可能会发生变化 的地方。 因为以前是普通关联的地方可能会变成笛卡儿积， 不同类型的关联可能会 生成不同的结果等。
8.优化子查询
关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替
9.优化group by，ORDER BY:
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列， 这样MySQL才有可能使用索引来优化这个过程。
10优化limit：

SELECT film_id, description from sakila,film ORDER BY title LIMIT 500 5; 

优化：

SELECT film.film_id, film.description
FROM sakila.film
INNER Join (
SELECT film id FROM sakila.film ORDER BY title LIMIT 500 5 
) AS lim USING(film_id);