MySQL查询语句优化.ppt

MySQL查询语句优化 Peng xu 2010 11 19 目录 为什么要对查询进行优化哪些方面可以优化MySQL索引机制如何发现慢查询如何分析慢查询日志查询语句优化实例分析 为什么要对查询进行优化 雅虎大部分产品都是对DB读取量大 写入量少一个快速的页面能显著增加用户体验一个没有调试过的SQL会弄死整个网站 哪些方面可以优化 优化硬件 操作系统优化MySQL服务器优化DB设计优化SQL语句优化应用 MySQL索引机制 什么是索引与表数据分开的独立的有序的物理结构索引的数据结构Btree Hash索引的种类主键 唯一索引 普通索引 复合索引 外键索引 如何发现慢查询 1 查看MySQLSlowQuery修改f文件long query time 2log slow queries home y logs mysql mysqld slow log 如何发现慢查询 2 用ysar命令查看在db服务器上安装包ysar ysar mysql用ysar my命令查看 如何发现慢查询 3 查看yapacheslow日志 为yapache安装yapache mod yahoo slow log包找出加载慢的页面 如何分析慢查询日志 mysqldumpslow sc t20host slow log按查询次数排序 top20mysqldumpslow st t20host slow log按查询时间排序 top20 使用MySQL自带的mysqldumpslow命令分析 实例分析所用环境 开发机 h07 操作系统 RHEL64 bitMySQL版本 mysql server 5 1 45 0 查询语句优化实例分析 单表 索引字段上进行运算会使索引失效usenengren 查询2009年12月的文章explainselectdate format from unixtime create time Y m asdfromarticlewheredate format from unixtime create time Y m 200912 orderbycreate timeasclimit10 测试 运算查询更慢explainselectdate format from unixtime create time Y m asdfromarticlewhere create time 100 1259596800andcreate time 1259596800andcreate time 1262275200orderbycreate timeasclimit10 查询语句优化实例分析 单表 MySQL选择的索引不一定是最好的usenengren 原始SQLexplainSELECTcount ascount stock code stock nameFROMarticleWHEREactive type 1ANDcreate time 1259596800GROUPBYstock codeLIMIT10 优化后 执行看效果 选择少的记录集更重要 explainSELECTcount ascount stock code stock nameFROMarticleUSEINDEX idx createtime WHEREactive type 1ANDcreate time 1259596800GROUPBYstock codeLIMIT10 查询语句优化实例分析 单表 简单的分页方法 usenengren 原始SQLexplainselectarticle id titlefromarticleorderbyarticle iddesclimit30000 10 优化后explainselectarticle id titlefromarticlewherearticle id 100000orderbyarticle iddesclimit10 查询语句优化实例分析 两表 两表关联查询增加排序字段 能够启用索引 优化前explainselecti gallery id i imagewidth t fromimage tagtinnerjoinimageioni imageurl t index urllimit0 10 优化后explainselecti gallery id i imagewidth t fromimage tagtinnerjoinimageioni imageurl t index urlorderbyt iddesclimit0 10 查询语句优化实例分析 两表 两表关联查询加上排序字段后有时也无法启用索引 usenengren test 优化前 生产机b和开发机explain结果不一样 需使用AnalyzeTable修复索引 explainselecta article id a title b telfromarticleainnerjoinnengrenbona yuid b yuidorderbya article iddesclimit10 优化后explainselecta article id a title b telfromarticlealeftjoinnengrenbona yuid b yuidorderbya article iddesclimit10 查询语句优化实例分析 三表 一个三表关联查询的优化 usejack 原始SQLexplainselecti id i icon i nickname i name i gender i applyTime v valuefromcontestant infoasiinnerjoincontestant statusassoni id s idleftjoinvotesasvoni id v uidwheres status 查询语句优化实例分析 三表 使用子查询优化selecta id a icon a nickname a name a gender a applyTime b valuefrom selecti id i icon i nickname i name i gender i applyTimefromcontestant infoasiinnerjoincontestant statusassoni id s idwheres status 查询语句优化实例分析 三表 三表关联SQL的简化 原始SQLexplainselectcount i id as cnt fromcontestant infoasileftjoincontestant statusassoni id s idleftjoinvotesasvoni id v uidwheres status 查询语句优化实例分析 三表 与上面的SQL取得的结果不同explainselectcount fromcontestant infoasileftjoin selectidfromcontestant statuswhereid 38570 assoni id s id 如果contestant status是contestant info的子集 votes是contestant status的子集 那么优化后的SQLexplainselectcount id as cnt fromcontestant statuswherestatus 查询语句优化实例分析 三表 有时MySQL选择的索引是最好的 减少记录集比去掉filesort更重要 usenengren 原始SQLexplainSELECTb grade a FROMarticleASaforceindex idx createtime INNERJOIN SELECTm n grade n point n point toplistFROMnengrenASmINNERJOINnengren pointASnONm yuid n yuidWHEREm status 1ANDn grade 4 ASbONa yuid b yuidWHEREa forecast type 1ANDa forecast yn 0ANDa trend 1ANDa active type 1ORDERBYa create timeDESCLIMIT10 优化后explainSELECTa article id n grade a titleFROMarticleASaINNERJOINnengrenasmona yuid m yui