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

共计 3340 个字符，预计需要花费 9 分钟才能阅读完成。

行业资讯    
数据库    
MySQL 数据库    
如何理解 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,) 转化成字符类型 img src= file://D:\Program Files\QQ\qq_info\709837562\Image\C2C\IU5((, 时间大大的缩短:

但是这个还不是最优化的: 后面又发现关联表那边有这样的一个语句:
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 版本的致命点以及两表关联的字段类型相同的重要性，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。