MySQL查询语句优化.ppt

MySQL查询语句优化 Peng.xu 2010.11.19 目录 为什么要对查询进行优化 哪些方面可以优化 MySQL索引机制 如何发现慢查询 如何分析慢查询日志 查询语句优化实例分析 为什么要对查询进行优化 雅虎大部分产品都是对DB读取量大，写入 量少 一个快速的页面能显著增加用户体验 一个没有调试过的SQL会弄死整个网站 哪些方面可以优化 优化硬件、操作系统 优化MySQL服务器 优化DB设计 优化SQL语句 优化应用 MySQL索引机制 什么是索引 与表数据分开的独立的有序的物理结构 索引的数据结构 Btree、Hash 索引的种类 主键、唯一索引、普通索引、复合索引、外键索引 如何发现慢查询 1、查看MySQL Slow Query 修改f文件 long_query_time = 2 log-slow-queries = /home/y/logs/mysql/mysqld-slow.log 如何发现慢查询 2、用ysar命令查看 在db服务器上安装包ysar、ysar_mysql 用ysar my命令查看 如何发现慢查询 3、查看yapache slow日志 为yapache安装yapache_mod_yahoo_slow_log包找 出加载慢的页面 如何分析慢查询日志 mysqldumpslow -s c -t 20 host-slow.log 按查询次数排序，top 20 mysqldumpslow -s t -t 20 host-slow.log 按查询时间排序，top 20 使用MySQL自带的mysqldumpslow命令分析 实例分析所用环境 开发机： 操作系统：RHEL 64-bit MySQL版本：mysql_server- 查询语句优化实例分析（单表） 索引字段上进行运算会使索引失效 use nengren; 查询2009年12月的文章 explain select date_format(from_unixtime(create_time),%Y%m) as d from article where date_format(from_unixtime(create_time),%Y%m) = 200912 order by create_time asc limit 10; 测试，运算查询更慢 explain select date_format(from_unixtime(create_time),%Y%m) as d from article where (create_time - 100) = 1259596800 and create_time = 1259596800 and create_time 1259596800 GROUP BY stock_code LIMIT 10; 优化后，执行看效果。选择少的记录集更重要。 explain SELECT count(*) as count,stock_code ,stock_name FROM article USE INDEX(idx_createtime) WHERE active_type=1 AND create_time 1259596800 GROUP BY stock_code LIMIT 10; 查询语句优化实例分析（单表） 简单的分页方法 use nengren; 原始SQL explain select article_id,title from article order by article_id desc limit 30000,10; 优化后 explain select article_id,title from article where article_id 100000 order by article_id desc limit 10; 查询语句优化实例分析（两表） 两表关联查询增加排序字段，能够启用索引 优化前 explain select i.gallery_id,i.imagewidth,t.* from image_tag t inner join image i on i.imageurl=t.index_url limit 0,10; 优化后 explain select i.gallery_id,i.imagewidth,t.* from image_tag t inner join image i on i.imageurl=t.index_url order by t.id desc limit 0,10; 查询语句优化实例分析（两表） 两表关联查询加上排序字段后有时也无法启用索引 use nengren_test; 优化前（生产机b和开发机explain结果不一样，需使用 Analyze Table修复索引） explain select a.article_id,a.title,b.tel from article a inner join nengren b on a.yuid = b.yuid order by a.article_id desc limit 10; 优化后 explain select a.article_id,a.title,b.tel from article a left join nengren b on a.yuid = b.yuid order by a.article_id desc limit 10; 查询语句优化实例分析（三表） 一个三表关联查询的优化 use jack; 原始SQL explain select i.id, i.icon, i.nickname, , i.gender, i.applyTime, v.value from contestant_info as i inner join contestant_status as s on i.id=s.id left join votes as v on i.id=v.uid where s.status 强制使用索引优化 explain select i.id, i.icon, i.nickname, , i.gender, i.applyTime, v.value from contestant_info as i force index(atime) inner join contestant_status as s on i.id=s.id left join votes as v on i.id=v.uid where s.status 查询语句优化实例分析（三表） 使用子查询优化 select a.id,a.icon,a.nickname,,a.gender,a.applyTime,b.value from (select i.id, i.icon, i.nickname, , i.gender, i.applyTime from contestant_info as i inner join contestant_status as s on i.id=s.id where s.status 查询语句优化实例分析（三表） 三表关联SQL的简化 原始SQL explain select count(i.id) as cnt from contestant_info as i left join contestant_status as s on i.id=s.id left join votes as v on i.id=v.uid where s.status MYSQL自动优化，先处理where 举例说明join与where的关系 explain select count(*) from contestant_info as i left join contestant_status as s on i.id=s.id where s.id 38570; 查询语句优化实例分析（三表） 与上面的SQL取得的结果不同 explain select count(*) from contestant_info as i left join (select id from contestant_status where id 38570) as s on i.id=s.id; 如果contestant_status是contestant_info的子集，votes是 contestant_status的子集，那么优化后的SQL explain select count(id) as cnt from contestant_status where status 查询语句优化实例分析（三表） 有时MySQL选择的索引是最好的，减少记录集比去掉filesort更重要 use nengren; 原始SQL explain SELECT b.grade,a.* FROM article AS a force index(idx_createtime) INNER JOIN (SELECT m.*,n.grade,n.point,n.point_toplist FROM nengren AS m INNER JOIN nengren_point AS n ON m.yuid = n.yuid WHERE m.status = 1 AND n.grade 4) AS b ON a.yuid = b.yuid WHERE a.forecast_type = 1 AND a.forecast_yn = 0 AND a.trend = 1 AND a.active_type = 1 ORDER BY a.create_time DESC LIMIT 10; 优化后 explain SELECT a.article_id,n.grade,a.title FROM article AS a INNER JOIN nengren as m on a.yuid = m.yuid inner join nengren_point as n on a.yuid = n.yuid WHERE a.forecas