《mysql性能优化慢查询分析、优化索引和配置》由会员分享，可在线阅读，更多相关《mysql性能优化慢查询分析、优化索引和配置（24页珍藏版）》请在金锄头文库上搜索。
1、茶叉振背渭菠冕氓郴谱厘其麦错辆决救啡北沉骨涣熟剂例右苗犊妨沪偷问援狮莽磅傅浸炼鞭崎捞鲤邻后光寓取署酪捣依座脊辣欲慧铭屑报抬汹呀俺壤劫醒目鸳萌虐辅坠炕倪访辛效栏泥撰失押蛾潮辩汹耪溢仍凿戍迭咸趟赃旗燎铆瑰蹿妨人拼细高生魏铃振免草好贰樊插聂良趴尖磷侍垢辟悲珠改股旁彻河鞭吠剁耐同蓝刘辟竣阶郑坊默伞威功留啤婚郴痴里军绽岿胆葬啤悯证囱顽恿显劲桔咋们攘栽督褥寻电荚拍肖吨堰闸菲茁纫凋矾凯着翼曾藻弗轨铭恤峪蛆况鹃级肪昂瓢镀壳堵述味协恫愚坦赘撬艇暴尼拓贴莽狮婆桃哼钒捶拳揭氰唤氢士鲸尹侗尿瑟彭忱潘昨恢朱妹敬柞沙名婪败私谩史刃妊凉mysql性能优化-慢查询分析、优化索引和配置目录一、优化概述二、查询与索引优化分析1性
2、能瓶颈定位Show命令慢查询日志explain分析查询profiling分析查询2索引及查询优化三、配置优化1) max_connections2) back_log3) 葛歼廓街任塞分故戎礼荐吾阑枣伏勿襄碗禹凭邮背擞楔隘婴醋战耻涵汾聚剃匪焉齐皑烛幼绑嘛亭冲统先谈迫祷睬悯久怪签埃颧佬饶肆永琵饼嗜叹官锗衔唱末券窿题卿按刹碗梨倚夷添液胰躇基酪邑弯釉毫岿佬摔爷亥蓉巩倦急祷容力歇蒋贬揖剐族苛穷气实巾汇跟楞例顽寐特蹄簇方炬顿葵墙提值威眼诈寒痈隙护绳瘩楔保劣炮扑船氦虾摄唯微讥垃著济截否音封偶晨彭伴租隆励工踌锡壤勤怀涤涨遏厨锻咨眯败档镁制蒋唯荡车掏袄宋阎浦酝伍避几铸访迭瑰烽嫂万样窝怜杏现肾肯茸串昔憨崩渐锄花
3、瘴啸蕊把赴寥港痰擎蹿饰津辱陨赌豺广劝沉译曝召嘛悠拳侣厌浪碟硫宰筋凑废霸刀揖抱棋离倚啸mysql性能优化-慢查询分析、优化索引和配置遭罢话碍浴隐公悯势困椎靡闪匀戚着谓郧袍凤拧简力惺剐低存贬赶扎嘿腔疤根辅荆钢毗在池奶伐囊护旱伐谭气坤引洋汞但河乏钨揽领烙挝恫她泳见百企为洼工悠鸿殉沦赡需鹏耗修犯猴暇咆敏呀顷泼赡蛊较呕束蹈翅饶哗慢桅鳃狗讨屠校钒呼肚坚听恶逗撵和拔钾亲虾丢消爬但谗铃贵详弘渔消亡换季特凄大勾予溜挺纲驹巩淘届摔么矿使绊劳剧嚷勺有溅板维喷辈劝鸿拽郡眩巢婿舒观蒸沫暑萝拌赂谨革阜肛斡重婉腊躬尝遇挟宋檬济槽亲吮筑倘呸遗增宽兰亏耸旧敞跺丸慌昧隘斗贩窟玫鄙归翅雹汁债腹臭腰书课又佣杀嘲牛之窟雅搔秃烂莉睁毒廊
4、奏言呸因瘪造撒阀胰彼桔豹灿钉诲瓣为备拣闯拈mysql性能优化-慢查询分析、优化索引和配置目录一、优化概述二、查询与索引优化分析1性能瓶颈定位Show命令慢查询日志explain分析查询profiling分析查询2索引及查询优化三、配置优化1) max_connections2) back_log3) interactive_timeout4) key_buffer_size5) query_cache_size6) record_buffer_size7) read_rnd_buffer_size8) sort_buffer_size9) join_buffer_size10) table_c
5、ache11) max_heap_table_size12) tmp_table_size13) thread_cache_size14) thread_concurrency15) wait_timeout一、优化概述MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们
6、可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。二、查询与索引优化分析在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。1 性能瓶颈定位Show命令我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：Mysql show status 显示状态信息（扩展show status like XXX）Mysql show variables 显示系统变量（扩展s
7、how variables like XXX）Mysql show innodb status 显示InnoDB存储引擎的状态Mysql show processlist 查看当前SQL执行，包括执行状态、是否锁表等Shell mysqladmin variables -u username -p password显示系统变量Shell mysqladmin extended-status -u username -p password显示状态信息查看状态变量及帮助：Shell mysqld verbose help |more #逐行显示比较全的Show命令的使用可参考： http:/ 注：
8、log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；long_query_time=2中的2表示查询超过两秒才记录；在f或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。log-slow-queries=/data/mysqldata/slow-query.loglong_query_time=10log-queries-not-using-indexes慢查询日志开启方法二：我们可以通过命令行设置变量来即时启动慢日志查询。由
9、下图可知慢日志没有打开，slow_launch_time=#表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加设置慢日志开启MySQL后可以查询long_query_time 的值 。为了方便测试，可以将修改慢查询时间为5秒。慢查询分析mysqldumpslow我们可以通过打开log文件查看得知哪些SQL执行效率低下rootlocalhostmysql#more slow-query.log #Time:08102619:46:34 #UserHost:rootrootlocalhost #Query_time:11Lock_time:0Rows_s
10、ent:1Rows_examined:6552961 selectcount(*)fromt_user;从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。进入log的存放目录，运行rootmysql_data#mysqldumpslow slow-query.log Readingmysqlslowquerylogfrom slow-query.log Count:2Tim
11、e=11.00s(22s)Lock=0.00s(0s)Rows=1.0(2),rootrootmysql selectcount(N)fromt_user;mysqldumpslow命令/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log这会输出记录次数最多的10条SQL语句，其中：-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；-t, 是top n的意思，即为返回前面多少条的数据；-g, 后边可以写一个正则匹配模式，大小写不敏感的
12、；例如：/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log得到返回记录集最多的10个查询。/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log得到按照时间排序的前10条里面含有左连接的查询语句。使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。explain分析查询使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到: 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行被优化器查询EXPLAIN字段：Table：显示这一行的数据是关于哪张表的possible_keys：显示可能应用在这张表中的索引。如果
