6.高性能MySQL --- 查询性能优化(2)

1.MySQL 子查询实现很糟糕，最糟的是包含 in 的子查询 //子查询改为连接查询
2. min()  有时候可以改用 limit 1 

查询优化器的提示：
1.high_priority , low_priority // 多语句访问一张表的优先级，对表锁有效，对 行锁的存储引擎无效

2.delayed // 对 insert， replace 有效， 该语句立刻返回给客户端，并将插入的行数据放入缓冲区，然后在表空的时候再将数据插入。对日志系统非常好

3.straight_join // 这个提示可以放在select 关键字后面(让所有表按出现的顺序关联)，也可以放在任何关联表之间(固定前后2张表的关联顺序)

4.sql_samll_result,sql_big_result //对select 有效，告诉优化器对 group by 和 distince 如果使用临时表排序，sql_samll_result结果集小，使用内存临时表，
sql_big_result 结果集很大，使用磁盘临时表

5.sql_buffer_result  //告诉优化器将查询结果放入一个临时表，然后尽可能的释放表锁。与客户端缓存不同

6.sql_cache 和 sql_no_cache //结果集是否应该缓存在查询缓存中

7.sql_calc_found_rows //让 MySQL 返回的结果集包含更多信息

8.for update 和 lock in share mode // 控制select语句的锁机制，只针对行锁存储引擎有效。对符合条件的数据行加锁，

9.using index, ignore index, force index // 告诉优化器使用或者不适用哪些索引来查询记录
 for order by , for group by 来指定是否对排序和分组有效
 force index 和 use index 基本相同，force index 会告诉优化器全表扫描的成本远高于索引扫描

优化器的一些参数：

1.optimizer_search_depth //控制优化器在穷举执行计划时的参数，如果查询长时间处于 statistic ，可以考虑

2.optimizer_prune_level  //默认打开，优化器根据扫描行数是否跳过某些执行计划

3.optimizer_swich //这个参数包含了开启或者关闭优化器某些特性的标志位，控制索引合并的特性

1.优化 count(),可以统计行数，也可以统计某个列的数量(要求非空)
 MyISAM 没有 where 的话 count() 可以会更快，也就是说 count 全表很快，先count 全表，再减去count小的
 count() 里面是表达式的话，就是统计表达式有值的结果集
计算同一列不同值的数量：
	select if (color = 'red' , 1,0) red ...
	select count( color = 'red' or null) red ...

2.优化关联查询
	确保 on, using 列上有索引
	确保 group by , order by 只涉及一个表的列

3.优化子查询
	尽可能使用关联

4.优化 group by 和 distinct 
关联查询分组，通常采用表的标识符 进行分组 效果比较好
如果 group by 没有指定排序，默认按分组标识符排序
如果不关心结果集的排序，而这种默认排序又导致了文件排序，可以用order by null

5.limit 优化
通过延迟关联，先通过覆盖索引找到相应的主键。再根据主键获取原表数据
offset 太大问题 ： 如 limit 1000,20 改成  id >1000, limit 20

6.优化 sql_calc_found_rows 
limit 语句中加上 sql_calc_found_rows,可以去掉 limit 以后满足的行数(不确定)

7.优化 union 
除非确实需要消除重复的行，否则一定使用 union all。如果没有all,MySQL 会给临时表加上distince 选项，会导致对临时表进行唯一性检查，代价非常高
	

6.5 MySQL 查询优化器的局限性

6.5.1 关联子查询

6.5.2 union 的限制

6.5.3 索引合并优化

6.5.8最大值和最小值优化

---

6.6 查询优化器的提示(hint)

---

6.7 优化特定类型的查询

6.7.1 优化 count() 查询

6.7.2 优化关联查询

6.7.4 优化 group by 和 distinct

6.7.5 优化 limit 分页

6.7.6 优化 sql_calc_found_rows

6.7.7 优化 union 查询

6.7.9 使用用户自定义变量

---

6.8 学习案例

6.8.1 使用 MySQL构建一个队列

6.8.2 计算两点之间的距离

6.8.3 使用用户自定义函数

---

http://www.cnblogs.com/heat-man/p/4945708.html

http://blog.sina.com.cn/s/blog_5f66526e0101dd7g.html