如何理解mysql5.6版本的致命点以及两表关联的字段类型相同的重要性

本篇文章为大家展示了如何理解mysql5.6版本的致命点以及两表关联的字段类型相同的重要性，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

背景:  mysql 版本 5.6

由于我一个同事在做一个报表的时候,关联的两张表都有建索引,但是由于发现字段类型不同而导致虽然两张表只有几十万级别的两张表只关联了7天的数据(查出来的结果其实只有1294条)竟然要关联出近500S的时间,所以这个是当时设计数据库表的时候没设计好,然后挖的坑.版本5.7以上的好像会自动转化成一样的字符类型(在另外一个版本5.7以上的mysql 数据库试下这种字段类型不同的发现依然会走索引)

发现此问题之后,我做了个转化:

SELECT n.order_code .....(中间的脚本省略)

(select sum(re.order_rebate) from  t_bm_order_return re where re.order_id=concat(n.order_id,) and  re.state_result=成功 and re.activity_no not in(商家慢必赔)) rebate_amount

FROM np_order n left join web114_order_ledger wol on n.order_id=wol.order_id   .............

        WHERE   

  n.create_time >= 2017-05-12

AND n.create_time < 2017-05-18 ;

主要是主表np_order  和 t_bm_order_return  的关联,concat(n.order_id,) 转化成字符类型

但是这个还不是最优化的:后面又发现关联表那边有这样的一个语句:

FROM np_order n left join web114_order_ledger wol on n.order_id=wol.order_id,

(SELECT *  FROM (SELECT MAX(og.order_goods_id) ogd,SUM(goods_info_num) gin FROM np_order_goods og GROUP BY og.order_id) t

        LEFT JOIN np_order_goods ogs ON t.ogd=ogs.order_goods_id) nog

他这种写法可以等价于:

FROM np_order n left join web114_order_ledger wol on n.order_id=wol.order_id,

(SELECT og.order_id, og.goods_id, SUM(goods_info_num) gin

          FROM np_order_goods og

         GROUP BY og.order_id,og.goods_id) nog

这样子的一个写法 ,时间又少了6s:

还有select from 里面的子查询中有两条语句:

(SELECT bos.shopname FROM web114_bm_outline_shop bos WHERE bos.shopcode=(SELECT osi.shopcode FROM web114_order_shop_info osi WHERE n.order_id=osi.order_id LIMIT 0,1)) order_shopname,

(SELECT bos.isdn FROM web114_bm_outline_shop bos WHERE bos.shopcode=(SELECT osi.shopcode FROM web114_order_shop_info osi WHERE n.order_id=osi.order_id LIMIT 0,1)) order_shop_isdn,

这两条语句其实可以提到 from 后面 跟主表直接关联,也可以改成这样:

(SELECT bos.shopname FROM web114_bm_outline_shop bos ,web114_order_shop_info osi  WHERE bos.shopcode= osi.shopcode and  n.order_id=osi.order_id LIMIT 0,1) order_shopname,

(SELECT bos.isdn FROM web114_bm_outline_shop bos ,web114_order_shop_info osi WHERE bos.shopcode= osi.shopcode and  n.order_id=osi.order_id LIMIT 0,1) order_shop_isdn ,

性能进一步提升:

,

当然还有其他部分的子查询可以进行再优化,这边也没需要优化到非得1s以内的,想要再优化,可以explain 进行分析

<img src="file://D:\Program Files\QQ\qq_info\709837562\Image\C2C\IU5((

MySQL性能分析及explain用法的知识是本文我们主要要介绍的内容，接下来就让我们通过一些实际的例子来介绍这一过程，希望能够对您有所帮助。

1.使用explain语句去查看分析结果

如explain select * from test1 where id=1;会出现：id  selecttype  table  type possible_keys  key key_len  ref rows  extra各列。

其中，

type=const表示通过索引一次就找到了；

key=primary的话，表示使用了主键；

type=all,表示为全表扫描；

key=null表示没用到索引。type=ref,因为这时认为是多个匹配行，在联合查询中，一般为REF。

各个属性的含义

id

select查询的序列号

select_type

select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

table

输出的行所引用的表。

type

联合查询所使用的类型。

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

key

显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

key_len

显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

ref

显示哪个字段或常数与key一起被使用。

rows

这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。

Extra 

如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。

如果是where used，就是使用上了where限制。

如果是impossible where 表示用不着where，一般就是没查出来啥。

如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

上述内容就是如何理解mysql5.6版本的致命点以及两表关联的字段类型相同的重要性，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。