查询优化_给出查询优化算法

B-Tree索引主要作用于WHERE和ORDER BY子句

1.如果索引了多列，要遵守最左前缀法则。所谓最左前列，指的是查询从索引的最左前列开始，并且不跳过索引中的列

2.当MySQL一旦估计检查的行数可能会”太多”，范围查找优化将不会被使用。

3.索引列不应该作为表达式的一部分，即也不能在索引列上使用函数

4.尽量借用覆盖索引，减少select * from …语句使用

5.ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

6.慎用left join语句,避免创建临时表 使用left join语句的时候，避免出现创建临时表。

7.高选择性索引列。 尽量使用高选择性的过引来过滤数据。

8.谨防where子句中的OR。where语句使用or，且没有使用覆盖索引,会进行全表扫描。应该尽量避免这样OR语句。尽量使用UNION代替OR

9.LIMIT与覆盖索引 limit子句，使用覆盖索引时比没有使用覆盖索引会快很多

 

一、关联查询优化

（1）保证被驱动表的join字段已经被索引

（2）left join 时，选择小表作为驱动表，大表作为被驱动表。

（3）inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

（4）子查询尽量不要放在被驱动表，有可能使用不到索引。

二、子查询优化

（1）有索引的情况下 ：用  inner join 是最好的  其次是 in  ，exists最糟糕 

（2）无索引的情况下用 

        a.小表驱动大表

            因为join 方式需要distinct ，没有索引distinct消耗性能较大 所以  exists性能最佳 in其次  join性能最差？

       b.无索引的情况下大表驱动小表

             in 和 exists 的性能应该是接近的  都比较糟糕  exists稍微好一点 超不过5%     但是inner join 优于使用了 join buffer 所以快很多 如果left join 则最慢

A小于B用exist
B小于A用in

三、order by 查询优化

CREATE TABLE `friends` ( 
`ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`uid`bigint(20) UNSIGNED NOT NULL DEFAULT '0', 
`fuid` bigint(20) UNSIGNED NOT NULL DEFAULT'0', 
`fname` varchar(50) NOT NULL DEFAULT '', 
`fpicture` varchar(150) NOT NULL DEFAULT'', 
`fsex` tinyint(1) NOT NULL DEFAULT '0', 
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
 
ALTER TABLE`friends` ADD INDEX uid_fuid (uid, fuid);

1.ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

      MySQL支持二种方式的排序，FileSort和Index，Index效率高.

      它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

2.ORDER BY满足两情况，会使用Index方式排序:

（1）ORDER BY 语句使用索引最左前列

（2）使用Where子句与Order BY子句条件列组合满足索引最左前列

（3）where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

以下情况，会使用FileSort方式的查询

（1）检查的行数过多，且没有使用覆盖索引。

（2）使用了不同的索引，MySQL每回只采用一个索引。

（3）对索引列同时使用了ASC和DESC。

（4）where语句与order by语句，使用了不同的索引。

（5）where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式。

（6）where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询。

（7）order by子句中加入了非索引列,且非索引列不在where子句中。

（8）order by或者它与where组合没有满足索引最左前列。

（9）当使用left join，使用右边的表字段排序。

3.尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

        index(a,b,c)

        where a = const and b > const order by b , c 不会出现 using filesort  b , c 两个衔接上了

       但是：where a = const and b > const order by  c 将会出现 using filesort 。因为 b 用了范围索引，断了。而上一个  order by 后的b 用到了索引，所以能衔接上 c 

4.如果不在索引列上，filesort有两种算法：双路排序和单路排序

（1）双路排序

           a.MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

           b.从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

         多路排序需要借助 磁盘来进行排序。所以 取数据，排好了取数据。两次 io操作。比较慢

         单路排序 ，将排好的数据存在内存中，省去了一次 io 操作，所以比较快，但是需要内存空间足够。

（2）单路排序

        a.取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

         b.从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，

       它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

（3）结论及引申出的问题

     a.由于单路是后出的，总体而言好过双路

     b.但是用单路有问题

         在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

（4）优化策略

       a.增大sort_buffer_size参数的设置:用于单路排序的内存大小

       b.增大max_length_for_sort_data参数的设置:单次排序字段大小。(单次排序请求)

       c.去掉select 后面不需要的字段:select 后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的

提高Order By的速度

      1. Order by时select * 是一个大忌只Query需要的字段 这点非常重要。在这里的影响是：

           a.当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。

           b. 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

2. 尝试提高 sort_buffer_size

       不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

3. 尝试提高 max_length_for_sort_data

        提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 阿萨德

四、group by 查询优化

group by 跟order by的优化策略是一样的 group by是县排序再分组

1.group by实质是先排序后进行分组，遵照索引建的最佳左前缀

2.当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

3.where高于having，能写在where限定的条件就不要去having限定了。