注册用户享全站资源 并成为粉丝 不定时福利发放
 

MySQL慢查询优化 EXPLAIN详解

6
发表时间:2018-11-30 10:45

摘要:慢查询排查: show status; // 查询mysql数据库的一些运行状态,show status like 'uptime'; // 查看mysql数据库启动多长时间,myisam存储引擎长时间启动需要进行碎片整理,查看慢查询,show status like 'slow_queries'...

慢查询排查

        show status;  // 查询mysql数据库的一些运行状态

       show status like 'uptime';

// 查看mysql数据库启动多长时间,myisam存储引擎长时间启动需要进行碎片整理

查看慢查询 show status like 'slow_queries'; 查询慢查询时间 show variables like 'long_query_time'; 设置慢查询时间 set long_query_time = 0.5;

分析执行情况 EXPLAIN详解

       分析查询语句的执行情况,可以分析出所查询的表的一些特征:EXPLAIN/

DESCRIBE/DESCSELECT * FROM...;

MySQL慢查询优化 EXPLAIN详解

每个字段说明:

id:SELECT标识符。这是SELECT的查询序列号。


select_type:表示SELECT语句的类型。它可以是以下几种取值:

  SIMPLE:表示简单杳询,其中不包括连接查询和子查询;

  PRIMARY:表示主查询,或者最外层的查询语句;

  UNION:表示连接查询的第2个或后面的查询语句;

  DEPENDENT UNION:连接查询中的第2个或后面的SELECT语句,取决于外面的查询;

  UNION RESULT:连接查询的结果;

  SUBQUERY:子查询中的第一个SELECT语句;

  DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询;

  DERIVED:导出表的SELECT (FROM语句的子查询)。

table:表示查询的表。


type:表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型:

    (1)	system
        该表仅有一行的系统表。这是const连接类型的一个特例。

    (2)	const

       数据表最多只有一个匹配行,它将在查询开始时被读取,并在余下的査询优化中作为常量对待。

       const表查询速度很快,因为它们只读取一次。

       const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。

在下面查询中,tb1_name可用const表: SELECT * from tb1_name WHERE primary_key=1; SELECT * from tb1_name WHERE primary_key_part1=1 AND primary_key_part2=2 (3) eq_ref

       对于每个来自前面的表的行组合,从该表中读取一行。当一个索引的所有部分都在查询中使用,

       并且索引是UNIQUE或者PRIMARY KEY时,即可使用这种类型。

       eq_ref可以用于使用“=”操作符比较带索引的列。

       比较值可以为常量或者一个在该表前面所读取的表的列的表达式。

在下面例子中,MySQL可以使用eq_ref来处理ref_tables: SELECT * FROM ref_table,other_table WHERE ref_table.key_cloumn = other_table.cloumn; SELECT * FROM ref_table, other_tbale WHERE ref_table.key_cloumn_part1 = other_table.cloumn AND ref_table.key_cloumn_part2 = 1; (4)ref

       对于来自前面的表的任意组合,将从该表中读取所有匹配的行。

       这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况,或者查询中使用了索引列在左子集,既索引中左边的部分列组合。

       ref可以用于使用=或者<=>操作符的带索引的列。

以下的几个例子中,mysql将使用 ref 来处理ref_table: select * from ref_table where key_column=expr; select * from ref_table,other_table where ref_table.key_column=other_table.column; select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1; (5)ref_or_null 这种连接类型类似ref,不同的是mysql会在检索的时候额外的搜索包含null值的记录。在解决子查询中经常使用该链接类型的优化。 在以下的例子中,mysql使用ref_or_null 类型来处理 ref_table: select * from ref_table where key_column=expr or key_column is null; (6)index_merge 该链接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。 (7)unique_subquery 该类型替换了下面形式的IN子查询的ref: value in (select primary_key from single_table where some_expr) (8)index_subquery 这种连接类型类似 unique_subquery。可以替换IN子查询,不过它用于在子查询中没有唯一索引的情况下, 例如以下形式: value in (select key_column from single_table where some_expr) (9)range

       只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。ken_len包含所使用索引的最长关键元素。

       当使用 =, <>, >,>=, <, <=, is null, <=>, between, 或 in操作符,用常量比较关键字列时,类型为range。

下面介绍几种检索制定行的情况: select * from tbl_name where key_column = 10; select * from tbl_name where key_column between 10 and 20; select * from tbl_name where key_column in (10,20,30); select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30); (10)index 连接类型跟ALL一样,不同的是它只扫描索引树。它通常会比ALL快点,因为索引文件通常比数据文件小。 (11)ALL

       对于前面的表的任意行组合,进行完整的表扫描。如果第一个表没有被标识为const的话就不大好了,在其他情况下通常是非常糟糕的。

       正常地,可以通过增加索引使得能从表中更快的取得记录以避免ALL。

possible_keys

      possible_keys字段是指MySQL在搜索表记录时可能使用哪个索引。如果这个字段的值是NULL,就表示没有索引被用到。这种情况下,就可以检查WHERE子句中哪些字段哪些字段适合增加索引以提高查询的性能。创建一下索引,然后再用explain 检查一下。


key

     key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候,这个字段的值就是NULL。想要让MySQL强行使用或者忽略在 possible_keys字段中的索引列表,可以在查询语句中使用关键字force index, use index或 ignore index。参考SELECT语法。


key_len

      key_len 字段显示了mysql使用索引的长度。当key 字段的值为NULL时,索引的长度就是NULL。注意,key_len的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。


ref

      表示使用哪个列或常数与索引一起来查询记录。


rows

      显示MySQL在表中进行查询时必须检查的行数。


Extra

      本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释:

        distinct
        MySQL当找到当前记录的匹配联合结果的第一条记录之后,就不再搜索其他记录了。 

        not exists
        MySQL在查询时做一个LEFT JOIN优化时,当它在当前表中找到了和前一条记录符合LEFT JOIN条件后,就不再搜索更多的记录了。下面是一个这种类型的查询例子:
        select * from t1 left join t2 on t1.id=t2.id where t2.id is null;

       假使 t2.id 定义为 not null。这种情况下,MySQL将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。

       当在 t2中找到一条匹配的记录时,这就意味着 t2.id 肯定不会都是null,就不会再在 t2 中查找相同id值的其他记录了。

       也可以这么说,对于 t1 中的每个记录,mysql只需要在t2 中做一次查找,而不管在 t2 中实际有多少匹配的记录。

range checked for each record (index map: #)

       mysql没找到合适的可用的索引。取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引(如果有的话),并且使用这个索引来从表里取得记录。

       这个过程不会很快,但总比没有任何索引时做表连接来得快。

using filesort

       MySQL需要额外的做一遍从已排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录,并且将所有符合where条件的记录的要排序的键和指向记录的指针存储起来。

       这些键已经排完序了,对应的记录也会按照排好的顺序取出来。详情请看"7.2.9how mysql optimizes order by"。

using index 字段的信息直接从索引树中的信息取得,而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。 using temporary mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了group by和order by子句,它以不同的方式列出了各个字段。 using where

       where子句将用来限制哪些记录匹配了下一个表或者发送给客户端。

       除非你特别地想要取得或者检查表种的所有记录,否则的话当查询的extra字段值不是using where并且表连接类型是all或index时可能表示有问题。

如果你想要让查询尽可能的快,那么就应该注意extra字段的值为using filesort和using temporary的情况。


会员登录

会员登录

登录免费下载全站资源

获取验证码
登录
登录
开发简历

开发简历

简历模板网站自取

入坑需谨慎

入坑需谨慎

高薪完全靠自己

微信赞助-Java帮帮社区

微信赞助-Java帮帮社区

非盈利性学习社区

支付宝赞助-Java帮帮社区

支付宝赞助-Java帮帮社区

将分享做到极致

大公司资讯
文章附图

近日,在美国推出满一年的Facebook视频服务Watch,宣布正式向全球推广,这预示着视频领域中,YouTube...

文章附图

据彭博社北京时间9月19日报道,科技行业最引人注目的法律大战可能正在进入尾声。据高通CEO史蒂夫·莫伦科夫(Ste...

Java帮帮公众号生态

Java帮帮公众号生态

总有一款适合你

Java帮帮-微信公众号

Java帮帮-微信公众号

将分享做到极致

Python帮帮-公众号

Python帮帮-公众号

人工智能,爬虫,学习教程

大数据驿站-微信公众号

大数据驿站-微信公众号

一起在数据中成长

九点编程-公众号

九点编程-公众号

深夜九点学编程

程序员服务区-公众号

程序员服务区-公众号

吃喝玩乐,听学吐画

Java帮帮学习群生态

Java帮帮学习群生态

总有一款能帮到你

Java学习群

Java学习群

与大牛一起交流

大数据学习群

大数据学习群

在数据中成长

九点编程学习群

九点编程学习群

深夜九点学编程

python学习群

python学习群

人工智能,爬虫

测试学习群

测试学习群

感受测试的魅力

Java帮帮生态承诺

Java帮帮生态承诺

一直坚守,不负重望

初心
勤俭
诚信
正义
分享
合作品牌 非盈利生态-优质内容分享传播者
关于我们
友链申请
友链交换:加帮主QQ2524138991 留言即可 24小时内答复  
全站内容非商业用途,内容来源于网友,并遵循 CC BY-NC 4.0 许可,如有异议请联系客服。
会员登录
获取验证码
登录
登录
我的资料
留言
回到顶部