Mysql查询优化

    主要介绍慢查询日志、sql的解析预处理及生成执行计划、查询各个阶段所消耗的时间等

一 如何获取有性能问题的SQL

    通过用户反馈获取存储性能的sql

    通过慢查询日志获取存储性能问题的sql

    实时获取存在性能问题的sql

二 慢查询日志介绍

    使用慢查询日志获取有性能的sql

        show_query_log 启动停止记录慢查日志

        show_query_log_file 指定慢查日志的存储路径及文件

        long_query_time 指定记录慢查日志sql执行时间的阀值

        long_queries_not_using_indexes 是否记录未使用索引的sql

    慢查询日志中记录的内容

  

    常用的慢查日志分析工具（mysqldumpslow）

 

    -t(top) 指定取前几条作为结果输出

    三 如何实时获取有性能问题的sql

select id,`user`,`host`,DB,comman,`time`,`state`,info from information_schema.PROCESSLIST where time>=60;

        sql的解析预处理及生成执行计划

            mysql服务器处理查询请求的整个过程

                    1 客户端发送sql请求给服务器

                    2 服务器检查是否可以在查询缓存中命中该sql

                    3 服务器端进行sql解析、预处理、再由优化器生成对应的执行计划

                    4 根据执行计划，调用存储引擎api来查询数据

                    5 将结果返回给客户端

四 查询缓存对sql性能的影响

        注意：每一次检查查询缓存是否可用的时候，都会锁定缓存。对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率，所以在这种情况下建议大家不要使用查询缓存

        查询缓存的设置

           query_cache_type:设置查询缓存是否可用

           query_cache_size:设置查询缓存的内存大小

           query_cache_limit:设置查询缓存可用存储的最大值

           query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据

           query_cache_min_res_unit:设置查询缓存分配的内存块的最小单位

 五 将sql语句转化为一个执行计划

            mysql依照这个执行计划和存储引擎进行交互，这个阶段包括了多个子过程

                包括检查语法是否使用了正确的关键字

                关键字的顺序是否正确等

                预处理阶段是根据mysql规则进一步解析树是否合法

                检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义

            语法检查通过了，查询优化器就可以生成查询计划了

六 造成mysql生成错误的执行计划的原因

                统计信息不准确

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

                mysql优化器所认为的最有可能与你认为的最优的不一样

                mysql从不考虑其他并发的查询，这可能会影响当前的查询速度

                mysql有时候也会基于一些固定的规则来生成执行计划

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

七  mysql优化器可优化的sql类型

                重新定义表的关联顺序

                将外连接转换为内连接

                使用等价变换规则将（5=5 and a>5)将改写a>5

                优化count(),min(),max()

                将一个表达式转化为常数表达式

                使用等价变换规则

                子查询优化

                提前终止查询

八 如何确定查询各个阶段所消耗的时间

    使用profile

    set profiling=1;启动profile,这是一个session级的配置

    show profiles;查看每一个查询所消耗的时间

    show profile for query N

九 特定SQL的查询优化

    大表的修改最好分批处理

        

    如何修改大表的表结构

        对表中的列字段进行修改，改变字段的宽度时还是会锁表，直接操作的化无法解决主从数据库延迟的问题

    解决办法：先建一个表格，然后将旧表的数据同步给新表，然后删除旧表

       

如何优化not in和<>查询

   

使用汇总表优化查询

   

    UNINO操作用于合并两个或多个SELECT语句的结果集。默认地，UNION操作选取不同的值。如果允许重复的值，请使用UNION ALL