查询优化总结

查询性能优化

    1.检查应用程序是否获取超过需要的数据。访问过多的行和列
    2.检查MySQL服务器是否分析了超过需要的行（分析开销指标：执行时间，检查和返回的行，检查的行和访问类型（EXPLAIN的type列，包括全表扫描，索引扫描，范围扫描，唯一查找扫描，常量。访问速度依次递增））

重构查询的方式

    1.可以考虑把一个复杂查询分解多个简单查询
    2.缩短查询
    3.把多表连接分解成多个单个查询，在应用程序端实现连接操作

查询执行过程

   

    客户端/服务器协议

             协议半双工的，可以发送和接受数据，不能同时进行。无法控制流程，一旦一方发送数据，另一方在发送回复消息前，必须完全接受对方发送的数据。客户端如果只有部分数据，要么接受完整数据后，丢弃不需要的数据，要么断开连接。（limit子句的重要性就在这）

             查询状态：每个MySQL线程在任何时刻都有一个状态来标识正在进行的事情。

                                1.休眠：线程等待客户端新查询

                                2. 查询：线程正在查询或向客户端发送数据

                                3. 锁定：线程正在等待服务器授予一个表锁

                                4.分析和统计：线程正在检查存储引擎的统计信息并且优化查询

                                5. 拷贝到磁盘上的临时表：线程正在处理查询并把结果拷贝到临时表，结果可能是GROUP BY，对文件排序，UNION

                                6. 排序结果：线程正在对查询结果排序

    查询优化过程：解析（解析成解析树），预处理（检查）和优化。

    优化：静态优化（简单的探测解析树来完成）和动态优化。MySQL只进行一次静态优化，每次运行查询时候进行动态优化。
    常用优化类型：  1.对连接中的表重新排序
                                  2.将外连接转换成内连接
                                  3.代数等价法则（优化掉不可能的限制和常量条件）
                                  4.优化COUNT(),MIN(),MAX()
                                  5.计算和减少常量表达式
                                  6.覆盖索引
                                  7.子查询优化
                                  8. 相等传递（可以辨认一个查询中有两个列相等的情况）
    MySQL联接执行策略
            MySQL将每个查询语句看成一个联接，把每个联接看成一个嵌套循环，用一个循环从表中读取数据，在利用一个嵌套循环从下一个表中发现匹配数据。当发现一行匹配数据时，根据SELECT字句中列输出。接着查找更多匹配的行，如果没有，就回溯到前一个表，用新的行开始下一轮迭代。
            MySQL将UNION看成一系列单个查询，将结果写入临时表，在读取出来形成结果。
            处理FROM子查询时，先执行子查询，把结果放到临时表，再进行下一步处理
    执行计划：采用左深度树
    联接优化器：决定了多表查询最佳执行顺序
    MySQL查询优化的限制
            子查询优化得最差的是where子句中使用IN。MySQL会试着让IN列表与外部表产生联系，可能外表会先执行并且做全表扫描，对每一次执行在做子查询应该把LIMIT、ORDER BY条件添加到UNION内部的每一个子句上，如果在外部MySQL会把所有表放到临时表中，在做条件处理
             MySQL不支持扫描不连续的索引
            不能很好优化MIN()，MAX()
            不能对同一个表进行update同时进行select

优化特定类型的查询

    1）优化COUNT
            count统计值的数量和统计行的数量。COUNT(*)统计行的数量
    2）优化联接
            确保ON或USING使用的列上有索引
            确保GROUP BY或ORDER BY只引用一个表中的列
    3）优化子查询
            尽可能使用联接。（子查询创建的临时表没有索引）
    4）优化GROUP BY和DISTINCT
            都可以从索引中获益
            不使用索引时，MySQL优化GROUP BY：使用临时表或文件排序进行分组。使用SQL_SMALL_RESULT强制选择临时表，SQL_BIG_RESULT强制使用文件排序
            MySQL会自动对GROUP BY里面的列进行排序
    5）优化LIMIT和OFFSET
            在覆盖索引上进行偏移，将从覆盖索引上提取出来的数据和全行数据进行联接，取得需要的列
    6）优化UNION
            始终使用UNION ALL。除非需要服务器消除重复的行