MySQL Query的优化

Query语句的优化思路和原则主要提现在以下几个方面：

         1. 优化更需要优化的Query；

         2. 定位优化对象的性能瓶颈；

         3. 明确的优化目标；

         4. 从Explain入手；

         5. 多使用profile

         6. 永远用小结果集驱动大的结果集；

         7. 尽可能在索引中完成排序；

         8. 只取出自己需要的Columns；

         9. 仅仅使用最有效的过滤条件；

         10.尽可能避免复杂的Join和子查询；

合理设计并利用索引

1）B-Tree索引

         一般来说，MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode，而且到任何一个LeafNode的最短路径的长度都是完全相同的，所以我们大家都称之为B-Tree索引当然，可能各种数据库（或MySQL的各种存储引擎）在存放自己的B-Tree索引的时候会对存储结构稍作改造。如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很小的改造，在每一个LeafNode上面出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息，这主要是为了加快检索多个相邻LeafNode的效率考虑。

2）Hash索引

         Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

         Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；

         Hash索引无法被利用来避免数据的排序操作；

         Hash索引不能利用部分索引键查询；

         Hash索引在任何时候都不能避免表扫面；

         Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

3）Full-text索引

         Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

         索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。

         是否需要创建索引，几点原则：较频繁的作为查询条件的字段应该创建索引；唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；更新非常频繁的字段不适合创建索引；

不会出现在WHERE子句中的字段不该创建索引；

Join语句的优化

         尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。

         优先优化NestedLoop的内层循环；

         保证Join语句中被驱动表上Join条件字段已经被索引；

         当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

ORDER BY，GROUP BY和DISTINCT优化

1）ORDER BY的实现与优化

         优化Query语句中的ORDER BY的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY操作的性能。

         优化排序：

                  1.加大max_length_for_sort_data参数的设置；

                  2.去掉不必要的返回字段；

                  3.增大sort_buffer_size参数设置；

2）GROUP BY的实现与优化

         由于GROUP BY实际上也同样需要进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。

3）DISTINCT的实现与优化

         DISTINCT实际上和GROUP BY的操作非常相似，只不过是在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。

MySQL Query的优化

Query语句的优化思路和原则主要提现在以下几个方面：

         1. 优化更需要优化的Query；

         2. 定位优化对象的性能瓶颈；

         3. 明确的优化目标；

         4. 从Explain入手；

         5. 多使用profile

         6. 永远用小结果集驱动大的结果集；

         7. 尽可能在索引中完成排序；

         8. 只取出自己需要的Columns；

         9. 仅仅使用最有效的过滤条件；

         10.尽可能避免复杂的Join和子查询；

合理设计并利用索引

1）B-Tree索引

         一般来说，MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode，而且到任何一个LeafNode的最短路径的长度都是完全相同的，所以我们大家都称之为B-Tree索引当然，可能各种数据库（或MySQL的各种存储引擎）在存放自己的B-Tree索引的时候会对存储结构稍作改造。如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很小的改造，在每一个LeafNode上面出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息，这主要是为了加快检索多个相邻LeafNode的效率考虑。

2）Hash索引

         Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

         Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；

         Hash索引无法被利用来避免数据的排序操作；

         Hash索引不能利用部分索引键查询；

         Hash索引在任何时候都不能避免表扫面；

         Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

3）Full-text索引

         Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

         索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。

         是否需要创建索引，几点原则：较频繁的作为查询条件的字段应该创建索引；唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；更新非常频繁的字段不适合创建索引；

不会出现在WHERE子句中的字段不该创建索引；

Join语句的优化

         尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。

         优先优化NestedLoop的内层循环；

         保证Join语句中被驱动表上Join条件字段已经被索引；

         当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

ORDER BY，GROUP BY和DISTINCT优化

1）ORDER BY的实现与优化

         优化Query语句中的ORDER BY的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY操作的性能。

         优化排序：

                  1.加大max_length_for_sort_data参数的设置；

                  2.去掉不必要的返回字段；

                  3.增大sort_buffer_size参数设置；

2）GROUP BY的实现与优化

         由于GROUP BY实际上也同样需要进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。

3）DISTINCT的实现与优化

         DISTINCT实际上和GROUP BY的操作非常相似，只不过是在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。