高性能MYSQL——6章查询执行的基础（1）

排序优化
mysql 排序很消耗性能。

1. 排序时会对每一个排序记录分配一个足够长的定长空间来存放，vachar 分配 3个字节
2. 除非查询的所有列都来自一个表，否则都会使用到临时表。
   5.6以后，mysql进行了很多改进，当只需返回部分排序结果的时候，例如有limit，mysql不再对所有结果排序，而是根据实际情况，抛弃不满足条件的结果。

关联子查询优化

SELECT * FROM sakila.film WHERE film_id IN (    SELECT film_id FROM sakila.flim_act WHERE actor_id =1);

MYSQL 会将其优化为关联查询 而不是 IN(1,23,344,5)

SELECT * FROM sakila.film WHERE EXISTS (    SELECT * FROM sakila.film_actor WHERE actor_id =1     AND film_actor.film_id = film.film_id)

首先mysql会对film表进行全表扫描，然后根据返回的film_id 逐个执行子查询，如果外层是一个很大的表，逐个查询性能会非常糟糕。

可以优化为:

SELECT film.* FROM sskila.film     INNER JONIN sakila.film_actor USING(film_id)WHERE actor_id =1;

或者内层使用 GROUP_CONCAT() 查询出所有的ID，然后外层用IN来查询。

子查询也可能QTS比关联查询效率高：例如当返回结果中只有一个表中的某些列的时候~~~额……感觉这种情况还是比较少见，而且效率就是低也不会低很多。。。。。。具体还是具体分析。

UNION的限制：

（SELECT  * FROM skila.actor     ORDER BY last_name)    UNION ALL(SELECT  * FROM skila.customer     ORDER BY last_name)    LIMIT 20;

可以分别在两条语句中分别加一个limit 20。来限制取出来的数据，然后再合并再取出20条。

等值传递----并行执行：mysql无法利用多喝特性来并行执行查询。哈希关联----：不支持松散索引扫描：mysql不支持松散索引扫描,也就是无法安装不连续的方式扫描一个索引。(这个不是特别明白。明天再研究研究。)    select ... FROM tb WHERE b BETWEEN 2 AND 3;最大值和最小值。--------    first_name 无索引：    SELECT MIN(actor_id) FROM　sakila.actor WHERE first_name ="PENELOPE";    优化为:===>>>    SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name ='PENELOPE' LIMIT 1;首先，first_name 没有索引，如果没有 USE INDEX 的话应该查到的是随机的一个值吧？ 使用了索引，可以尽量少查记录数。同时获取到最小值（）

各个数据库的无order by时的默认排序（代验证）

这是不靠谱的。潜规则。===========Oracle:oracle对无order by的语句返回的结果不进行排序，oracle此时的处理方式是按照数据的物理存储顺序来读取数据。因为rowid是每行数据的地址，所以有时候看起来会像是使用rowid排序的。但这个顺序是可能被打乱的，在表的数据被删除后，rowid会被新插入的数据占用。所以一个无order by查询结果看起来也可能是个杂乱无章的。oracle的数据库实现就一个原则，怎么快怎么效率高就怎么来。大多数情况下不需要排序还非得按主键排序这不是浪费资源么？这和oracle的表结构是有关系的，因为oracle的表结构默认是按堆存放的。按堆存放的意思就是，随便存，存的时候就是乱序的。如果你建表的时候就是建的按索引组织的表，那么它返回的时候就会默认排序了。sqlserver:在不指定Order by的情况下，sqlserver会根据执行计划实际查询方式来得到数据，而执行计划会根据sql中很多的因素（的查询列，where条件，order by等）而使用不同的索引，最终出来的结果很可能是不同的。MySQL:对于 MyISAM 表 ，Select 默认排序是按照物理存储顺序显示的。而InnoDB 表，会按主键的顺序排列。“Select” 不加 “Order by”时， MySQL 会尝试以尽可能快的方法（MySQL 实际的方法不见得快）返回数据。由于访问主键、索引大多数情况会快一些（在Cache里）所以返回的数据有可能以主键、索引的顺序输出，这里并不会真的进行排序，主要是由于主键、索引本身就是排序放到内存的，所以连续输出时可能是某种序列。在一些情况下消耗硬盘寻道时间最短的数据会先返回。DB2：DB2的尚不清晰，排序好像与sort heap相关。

在同一个表中执行更新

UPDATE tb1 AS T1    INNER JOIN (     SELECT count(*) AS cnt      FROM tb1     GROUP BY type)AS T2 USING(id)SET T1.cnt = T2.cnt

查询优化器 hint

HIGHT_PRIRITY 和 LOW_PRIORITY 设置优先级
DELAYED 对INSERT 和 REPLACE 有效，主要试用与日志系统。可以将要插入的数据放入缓冲区，然后一次性批量插入。 PS( LAST_INSERT_ID 失效。不是所有的存储引擎都支持)

STRAIGHT_JOIN 修改关联查询中的主驱动表。

SQL_SMALL_RESULT SQL_BIG_RESULT 在group by 和 distinct 时会用到，用于数据排序在内存中排序还是在文件系统中排序

SQL_CACHE SQL_NO_CACHE

优化特定类型的查询

count
①count(*) 效率高于 count(name)
②count(NULL) 空的话不会计算
eg:
SELECT COUNT(color=’blue’ OR color=’red’) from item;
===>
SELECT sum(IF color =’blue’, 1, 0) AS blue, SUM( IF color=’red’, 1, 0) AS red from item;
===>
SELCT COUNT(color = ‘blue’ OR NULL) AS blue,COUNT(color = ‘red’ OR NULL) AS red FROM item

优化GROUP BY 和 DISTINCT
①尽量使用索引覆盖来查询，group by 和distinct 的字段在在一个表里面、
②sql_model 模式严格模式来写

WITH　ROLLUP
普通的 GROUP BY 操作，可以按照部门和职位进行分组，计算每个部门，每个职位的工资平均值：

如果我们希望再显示部门的平均值和全部雇员的平均值，普通的 GROUP BY 语句是不能实现的，需要另外执行一个查询操作，或者通过程序来计算。如果使用有 WITH ROLLUP 子句的 GROUP BY 语句，则可以轻松实现这个要求：

+------+------+-----------+  | dep | pos | avg(sal) |  +------+------+-----------+  | 01 | 01 | 1500.0000 |  | 01 | 02 | 1950.0000 |  | 01 | NULL | 1725.0000 |  | 02 | 01 | 1500.0000 |  | 02 | 02 | 2450.0000 |  | 02 | NULL | 2133.3333 |  | 03 | 01 | 2500.0000 |  | 03 | 02 | 2550.0000 |  | 03 | NULL | 2533.3333 |  | NULL | NULL | 2090.0000 |  +------+------+-----------+  10 rows in set (0.00 sec)

优化LIMIT
①使用索引覆盖,延迟加载
SELECT * FROM sakila.film
INNER JOIN (
SELECT film_id FROM sakila.fim
ORDER BY title LIMIT 50,5
)AS lim USING (film_id)

②使用索引时：从书签记录页开始计算，防止limit，offset 取消掉大量数据
SELECT * FROM sakila.rental WHERE rental_id < 1630 ORDER　BY rental_id DESC LIMIT 20;

SQL_CALC_FOUND_ROWS
使用方法

mysql> select SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> select FOUND_ROWS(); 

第二句会返回无limit的行数。看了很多文章的意思是 用count(*) 的效率比这种方式效率高。
使用SQL_CALC_FOUND_ROWS 会扫描所有的符合条件的字段；LIMIT 扫描到满足条件的行数不再扫描。

总结就是用上面的这个方式，用主键来做书签limit

UNION 优化
除非确实需要服务器消除重复的行。否则，一定要用UNION ALL 。
如果没有ALL 关键字，Mysql会给临时表加上 DISTINCT选项，这样做代价很大。即使有ALL Mysql，依然会使用临时表存储结果，先放入临时表，然后再从临时表中取出。再返回给客户端。这样代码会很大。