两个原因分析查询慢的sql: 是否查询过多的行或列；是否分析了过多的行

1. 查询过多数据：查询全部只取10行(应该用LIMIT)；多表连接查询全部列；select *, 会使许多优化无用(coving index,增加io,内测，cpu负担)
2. 查询时 检查的过多的数据：
几个可以作为参考的标准：执行时间；检查的行数；返回的行数
通过索引会更快，type=ref;type=all全表扫描，Using where:使用where丢弃数据
Mysql会在三种情况下使用where，前两者不会读取table数据：
查找index时，过滤掉不符合的index
查找coving index，将不符合的row 过滤掉（Using index in the Extra）
读取table数据，将不符合的row 过滤掉（Using where in the Extra）
当检查了大量的row，只返回少量result：
使用coving index
change schema，比如使用summary tables
重写复杂sql，使优化器能执行优化
 
重构query的方法
1. 复杂查询和多个查询：
常规使用更少的查询做更多的事，主要考虑网络因素
但是mysql对连接处理很快，复杂sql的数据处理可能很慢
可以尝试用多个查询去代替复杂查询
2. 减少每次数据处理量
删除过期数据为例：将一次全部删除 改为每次一万条，执行多次，中间还可以设置sleep
Mysql如何实现查询
1. 流程
客户端发请求
mysql查看query cache，有匹配直接从cache里返回缓存的result
否则server解析、预处理、优化sql为query execute plan
query execute engine执行plan
server返回数据
2. client与server的协定：只有一方能发信息，或收或取，不能同时收取
比如client/server在完全接收到整个信息前，不能做响应
每个mysql的连接或线程都有显示它作什么的状态：SHOW FULL PROCESSLIST
Sleep：等待请求
Query：或者正在执行查询，或者正在返回结果
Locked：等待table锁的授权
Analyzing and statistics: 检查引擎的统计信息 优化查询
Copying to tmp table：处理查询，将结果存入temp table
Sorting result：正在给结果排序
Sending data：在查询阶段直接传输数据；生成结果；将结果返回client
3. Query Cache
大小写敏感
缓存命中，在检查权限，如果也通过返回结果
 
4. 查询优化处理
分析、预处理、优化
解析将query分析为tree；预处理负责查看table index privilege
估算统计信息：表或index的数据量、cardinality(number of distinct values),列的长度等（不考虑cache，假定都从io取）
优化最终方案可能不会最优：
统计信息错误
估算与实际查询不符
其他query影响
有时会根据固定规则执行查询，比如有MATCH()就是用FULLTEXT index，即使有更快的非FULLTEXT index
有时无法估算execution，可能错过最优
优化会做的事情：
调整join顺序，不一定按照query声明的顺序
将outer join改为inner join
优化代数规范：a>b and b=5优化为a>5 and b=5
减少常量表达式
使用coving index
子查询优化
提前结束：LIMIT 
IN:并不是简单的or，in里是排序的，查找时间是O(logn),OR是O(n)
还有很多.....
如果你知道优化器没用最佳的，你可以使用hint帮助它
Mysql执行join的方式类似Oracle的nested loop join；inner join的外表选择很关键，可以执行更少的查询
filesort:不能用index实现排序，要查询row，无论是memory还是真的disk
排序占用空间会很大：为每个排序row分配固定大小空间(varchar)
对于join排序：如果只查第一个表的列，Extra=Using filesort;否则需要把数据存放在临时表然后对临时表filesort：Using temporary;Using filesort
剩下的步骤：引擎执行查询、返回结果
 
5. Mysql查询优化器的局限性
对子查询优化性差，尤其是in(subquery)
index merge:同时使用两个index Using union(PRIMARY,idx_fk_film_id);但有时缓存 排序 合并操作会占用大量cpu和内存，使用可以使用IGNORE INDEX禁用某些index
多cpu也无法并行执行
loose index scan：使用两个列组成的index的右侧列做 range scan：Using index for group-by
对min() max()的优化不好
 
优化特殊类型的query
1. count()
查row的数量要用count(*)，count(name)是不算null的
MyISAM只有没有where条件时，count(*)才非常快
当where a>5时，尝试 count(*) - a<=5
2. join
在on和using的列上有index
group by和order by的列在一个表上，这样就可以使用index
升级mysql要注意：可能会影响性能，返回不同结果，甚至语法错
3. GROUP BY and DISTINCT
join时 以table的主键分组 要比value分组快
？使用filesort or temporary table会更快
4.  LIMIT and OFFSET
LIMIT 10000, 20, it is generating 10,020 rows and throwing away the first 10,000 of them, which is very expensive
使用coving index会改进性能
5. SQL_CALC_FOUND_ROWS
只是通知server产生再抛弃无用的result set，很expensive
如果想生成nextpage link，那么每页20row 则查21行
每页20row 查100row，比5页都查再排序效率高
6. 优化Union
尽量用UNION ALL，union会增加一个distinct，用整行去保证uniqe，很expensive
 
Hints
可以控制执行计划
HIGH_PRIORITY and LOW_PRIORITY:与其他查询相比的优先级，比如都争锁的话
DELAYED：用于insert、replace，使statement立即返回，将row放于buffer在table free时insert进去。
STRAIGHT_JOIN：强制使用query的顺序做join
SQL_SMALL_RESULT and SQL_BIG_RESULT：select时何时使用临时表，group by/discintc排序
SQL_BUFFER_RESULT:将结果存入临时表，尽快释放表锁
SQL_CACHE and SQL_NO_CACHE:sql是否用query cache
SQL_CALC_FOUND_ROWS：即使有LIMIT也计算整个result set
FOR UPDATE and LOCK IN SHARE MODE：select时加锁
USE INDEX, IGNORE INDEX, and FORCE INDEX：使用或不使用哪个index
 
In MySQL 5.0 and newer，some system variables that influence the optimizer：
optimizer_search_depth ：计算最优执行计划的深度
optimizer_prune_level：基于已选row 跳过某些plan
 
User-defined variables
1. 是一些值的临时存储器，只要你连着server 就不会丢失
2. 特性及缺点：
不能query cache
需要名字和主键时 不能用它(table/column name;in limit)