MySQL：查询优化

一、查询慢的原因

       通常来讲MySQL数据库查询需要经历的周期：从客户端，到服务端，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。这里涉及到网络、IO、cpu、上下文切换、系统调用、生成统计信息、锁等待时间等流程，如图所示：

 二、优化数据访问

         1、查询不需要的记录，优化方案：在查询后面添加limit

         2、多表关联时返回全部列，优化方案：删除必备返回字段

         3、总是取出全部列，优化方案：不使用 * 号

         4、重复查询相同的数据，优化方案：使用缓存

三、执行过程的优化

        1、使用查询缓存

              注意：在后续MySQL版本中，已经将MySQL自带的缓存删除。主要原因：mysql提供的缓存命中率不高，而且还需要不         断维护缓存中数据是否过期，以及缓存淘汰等问题

        2、查询优化——查询优化器

              优化器：当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最          后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划；          mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小          的一个。

           （1）、select count(*) from film_actor;  show status like 'last_query_cost';（显示最后一次查询消耗的时间） 可以看到这         条查询语句大概需要做1104个数据页才能找到对应的数据，这是经过一系列的统计信息计算来的；

              统计的信息有：每个表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况

           （2）、在很多情况下mysql会选择错误的执行计划，原因如下：

                        执行计划的成本估算不等同于实际执行的成本；

                        mysql的最优可能跟你想的不一样；

                        mysql不考虑其他并发执行的查询；

                        mysql不会考虑不受其控制的操作成本。

           （3）、优化器的优化策略

                        静态优化：直接对解析树进行分析，并完成优化；

                        动态优化：动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关；

                        注意：mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估；

           （4）、优化器的优化类型

                         重新定义关联表的顺序；

                         将外连接转化成内连接，内连接的效率要高于外连接；

                         使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式；

           （5）、优化count(),min(),max()；

           （6）、预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行               处理；

           （7）、索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引；

           （8）、某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问；

           （9）、关联查询：join三种实现方式：Simple Nested-Loop Join、Index Nested-Loop Join、Block Nested-Loop Join

 

 

四、优化特定类型的查询                         

        1、优化count()查询；

        2、优化关联查询：

           （1）、确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序；

           （2）、确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程；

        3、优化子查询：优化建议是尽可能使用关联查询代替；

        4、优化limit分页：优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列；

        5、优化union查询：除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的            时候给临时表加上distinct的关键字，这个操作的代价很高；

        6、推荐使用用户自定义变量