mysql查询性能优化探索.pptVIP

MYSQL查询性能优化 为什么查询速度会慢 1 优化数据访问基础 2 重构查询的方式 3 查询执行的基础 4 目 录 mysql查询优化器的局限性 5 优化特定类型的查询 6 and so on... 7 结束 8 目 录 为什么查询速度会慢？ 1.查询速度最重要的参数是响应时间。剖析mysql查询详见以后课时。 2.是否在检索大量超过需要的数据。太多的行和太多的列。 3.mysql服务器是否在分析大量超过需要的数据行。 优化数据访问基础 设计合理的数据表结构：适当的数据冗余 对数据表建立合适有效的数据库索引 数据查询：编写简洁高效的SQL语句 mysql执行顺序： (8) SELECT (9) DISTINCT (11) TOP_specification select_list (1) FROM left_table (3) join_type JOIN right_table (2) ON join_condition (4) WHERE where_condition (5) GROUP BY group_by_list (6) WITH {CUBE | ROLLUP} (7) HAVING having_condition (10) ORDER BY order_by_list 1. 借助 MySQLTuner 配置mysql参数 wget /major/MySQLTuner-perl/master /mysqltuner.pl chmod +x mysqltuner.pl ./mysqltuner.pl //Recommendations 2. 使用innoDB引擎代替MyISAM 全文检索使用替代方案来避免通过数据库来查询， 譬如借助第三方搜索工具。 重构查询的方式 1.切分查询 大的语句一次性完成，可能需要一次锁住很多数据、占满整个 事务日志、耗尽系统资源、阻塞很多小的但重要的查询。切分可 以尽可能小的影响mysql性能，减少mysql复制的延迟。 举例 删除15年12月以前的消息，count(*) = 300w： delete from message where add_time 1449852756 row_affected = 0 do{ row_affected = do_query(delete from message where add_time 1449852756) }while row_affected 0 一次删除1w条数据，可以放宽执行时间、减少锁的持有时间。 2.分解关联查询 *尽量拆分join关联查询：减少锁竞争、高效可拓展、减少冗余 尽量用inner join避免 LEFT JOIN：匹配查询和主表全查 在使用Left (right) join的时候，一定要在先给出尽可能多的 匹配满足条件，减少Where的执行：on的执行优先级高于where 尽量避免子查询，而用join： select ...where...(select...) 查询执行的基础 1.mysql客户端/服务器通信协议 半双工通信协议：服务器和客户端发送数据的动作不能同时发生， 客户端发送单独的请求数据包，查询语句大小受max_allowed_packet 影响。服务器获取响应数据缓存到内存中分多个数据包多次发送， 所有数据发送结束释放查询所占资源。 当查询结果过大时可以采用不缓存数据直接进行处理以便尽早释放 资源： mysql_query(select * from ...,$con); to mysql_unbuffered_query(select * from ...,$con); 2.查询缓存 缓存完整的select查询结果。表更新缓存失效。简单代价小。 需要配置。 解析查询语句之前，如果查询缓存是打开的，优先检查这个查 询是否命中查询缓存中的数据。通过哈希查找。如果命中则检查 用户权限，跳过解析查询步骤直接返回结果。 3.查询优化器 Mysql查询优化器的工作是为查询语句选择合适的执行路径。 mysql优化器优化类型： 重新定义关联表的顺序 将外连接转化为内链接 使用等价交换规则 优化count、min、max（无