<div class="clear"></div>
<div class="postBody">
<div class="blogpost-body" id="cnblogs_post_body"><header style='padding: 30px 0px 15px 79.37px; color: rgb(26, 26, 26); font-family: "lucida grande", "lucida sans unicode", lucida, helvetica, "Hiragino Sans GB", "Microsoft YaHei", "WenQuanYi Micro Hei", sans-serif; font-size: 16px; border-left-color: rgb(77, 77, 77); border-left-width: 5px; border-left-style: solid; box-sizing: border-box; background-color: rgb(255, 255, 255);'><h1><span style="font-size: 18pt;">原文链接: </span><a href="http://mrchenatu.com/2017/03/24/mysql-tool/"><span style="font-size: 18pt;">http://mrchenatu.com/2017/03/24/mysql-tool/</span></a></h1></header><div style='color: rgb(26, 26, 26); line-height: 1.8em; padding-right: 79.37px; padding-left: 79.37px; font-family: "lucida grande", "lucida sans unicode", lucida, helvetica, "Hiragino Sans GB", "Microsoft YaHei", "WenQuanYi Micro Hei", sans-serif; font-size: 16px; box-sizing: border-box; background-color: rgb(255, 255, 255);' itemprop="articleBody"><p style="margin: 10px 0px 1.75em; box-sizing: border-box;">本总结来自美团内部分享，屏蔽了内部数据与工具</p><h2><a title="知识准备" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#知识准备"></a>知识准备</h2><h3><a title="索引" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#索引"></a>索引</h3><ul style="list-style: none; margin: 10px 0px; padding: 0px; font-size: 14px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">索引是存储引擎用于快速找到记录的一种数据结构</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">B-Tree，适用于全键值，键值范围或键最左前缀：(A,B,C): A, AB, ABC,B,C,BC</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">哪些列建议创建索引：WHERE, JOIN , GROUP BY, ORDER BY等语句使用的列</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">如何选择索引列的顺序：<ol style="list-style: none; margin: 10px 0px 10px 30px; padding: 0px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">经常被使用到的列优先</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">选择性高的列优先：选择性=distinct(col)/count(col)</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">宽度小的列优先：宽度 = 列的数据类型</li></ol></li></ul><h3><a title="慢查询" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#慢查询"></a>慢查询</h3><h4><a title="原因" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#原因"></a>原因</h4><ol style="list-style: none; margin: 10px 0px; padding: 0px; font-size: 14px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">未使用索引</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">索引不优</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">服务器配置不佳</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">死锁</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">…</li></ol><h4><a title="命令" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#命令"></a>命令</h4><h5><a title="看版本" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#看版本"></a>看版本</h5><p style="margin: 10px 0px 1.75em; box-sizing: border-box;">mysql -V 客户端版本 select version 服务器版本</p><h5><a title="explain 执行计划，慢查询分析神器" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#explain-执行计划，慢查询分析神器"></a>explain 执行计划，慢查询分析神器</h5><ul style="list-style: none; margin: 10px 0px; padding: 0px; font-size: 14px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;"><p style="margin: 10px 0px 1.75em; box-sizing: border-box;">type</p><ul style="list-style: none; margin: 10px 0px 10px 30px; padding: 0px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">const,system: 最多匹配一个行，使用主键或者unique进行索引</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">eq_ref: 返回一行数据，通常在联接时出现，使用主键或者unique索引（内表索引连接类型）</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">ref: 使用key的最左前缀，且key不是主键或unique键</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">range: 索引范围扫描，对索引的扫面开始于某一点，返回匹配的行</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">index:以索引的顺序进行全表扫描，优点是不用排序，缺点是还要全表扫描</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">all: 全表扫描 no no no</li></ul></li><li style="margin: 0px; padding: 0px; box-sizing: border-box;"><p style="margin: 10px 0px 1.75em; box-sizing: border-box;">extra</p><ul style="list-style: none; margin: 10px 0px 10px 30px; padding: 0px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">using index : 索引覆盖，只用到索引，可以避免访问表</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">using where: 在存储引擎检索行后再做过滤</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">using temporary:使用临时表，通常在使用GROUP BY，ORDER BY 时出现（严禁）</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">using filesort: 到非索引顺序的额外排序，当order by col未使到索引时发生（严禁）</li></ul></li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">possible_keys: 显示查询可能使用的索引</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">key:优化器决定采用哪个索引来优化对该表的访问</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">rows:MySQL估算的为了找到所需行要检索的数，优化选择key的参考 （不是结果集的行数）</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">key_len: 使用的索引左前缀的长度(字节数)，亦可理解为使用了索引中哪些字段<ul style="list-style: none; margin: 10px 0px 10px 30px; padding: 0px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">定长字段，int占4个字节、date占3个字节、timestamp占4个字节，char(n)占n个字节</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">NULL的字段:需要加1个字节，因此建议尽亮设计为NOT NULL</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">变长字段varchar(n)，则需要 (n <em style="box-sizing: border-box;">编码字符所占字节数 + 2 、)个字节，如utf8编码的， 个字符<br style="box-sizing: border-box;">占 3个字节，则 度为 n </em>3 + 2</li></ul></li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">强制使用索引: USE INDEX （建议）或 FORCE_INDEX （强制）</li></ul><h4><a title="show 命令" style="background: none; color: rgb(0, 136, 204); box-sizing: border-box; text-decoration-line: none;" href="http://mrchenatu.com/2017/03/24/mysql-tool/#show-命令"></a>SHOW 命令</h4><ul style="list-style: none; margin: 10px 0px; padding: 0px; font-size: 14px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">show status<ul style="list-style: none; margin: 10px 0px 10px 30px; padding: 0px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">查看select语句的执行数 show global status like ‘Com_select’;</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">查看慢查询的个数 show global status like ‘Slow_queries’;</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">表扫描情况 show global status like ‘Handler_read%’; Handler_read_rnd_next / com_select > 4000 需要考虑优化索引</li></ul></li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">show variables<ul style="list-style: none; margin: 10px 0px 10px 30px; padding: 0px; box-sizing: border-box;"><li style="margin: 0px; padding: 0px; box-sizing: border-box;">查看慢查询相关的配置 show variables like ‘long_query_time’;</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">将慢查询时间线设置为2s set global long_query_time=2;</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">查看InnoDB缓存 show variables like ‘innodb_buffer_pool_size’;</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">查看InnoDB缓存的使用状态 show status like ‘Innodb_buffer<em style="box-sizing: border-box;">pool</em>%’; 缓存命中率=(1-Innodb_buffer_pool_reads/ Innodb_buffer_pool_read_requests) <em style="box-sizing: border-box;">100%；缓存率=（Innodb_buffer_pool_pages_data/ Innodb_buffer_pool_pages_total)</em>100%</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">SHOW PROFILES;该命令可以trace在整个执行过程中各资源消耗情况(会话级)</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">SHOW PROCESSLIST; 查看当前有哪些线程正在运行，并且处在何种状态</li><li style="margin: 0px; padding: 0px; box-sizing: border-box;">SHOW ENGINE INNODB STATUS; 可用于分析死锁，但需要super权限</li></ul></li></ul></div></div><div id="MySignature"></div>
