MySQL中字段字符集不同导致索引不能命中

  今天写了一个sql,其中涉及的表中的数据量都差不多为50w左右,查询发现用了8s。这个只是测试服上数据,放到正式服上,肯定一运行就挂了。

SELECT
    Orders. NO,
    GuidNo,
    Orders.CreateTime,
    sum(OrderItem.Quantity) AS Quantity,
    Brand. NAME AS BrandName,
    member.Mobile,
    Street AS deliveryaddress,
    Area
FROM
    Orders
INNER JOIN OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNo
INNER JOIN Brand ON Brand.Id = Orders.BrandId
INNER JOIN member ON member.Id = 13
INNER JOIN memberaddress ON member.Id = memberaddress.MemberId
WHERE
    orders.GuidNo IN (
     SELECT
            orderpayment.OrderGuidNo
        FROM
            paymentrecord
        LEFT JOIN orderpayment ON paymentrecord.`No` = orderpayment.PaymentNo
        WHERE
            paymentrecord.PaymentMethod = 'MemberCard'
        AND paymentrecord.Payer = 13
    )
GROUP BY
    GuidNo;

然后就用EXPLAIN分析了一下,发现Orders表没有命中索引,但是查询Orders中的GuidNo已经设置了索引,但就是不能命中。

 

然后我将上面的语句分为两个语句。首先将sql语句修改为:将子查询的数据直接写在了sql中,查询用了0.12s。

SELECT
    Orders. NO,
    GuidNo,
    Orders.CreateTime,
    sum(OrderItem.Quantity) AS Quantity,
    Brand. NAME AS BrandName,
    member.Mobile,
    Street AS deliveryaddress,
    Area
FROM
    Orders
INNER JOIN OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNo
INNER JOIN Brand ON Brand.Id = Orders.BrandId
INNER JOIN member ON member.Id = 13
INNER JOIN memberaddress ON member.Id = memberaddress.MemberId
WHERE
    orders.GuidNo IN (
        '0A499C5B1A82B6322AE99D107D4DA7B8',
        '18A5EE6B1D4E9D76B6346D2F6B836442',
        '327A5AE2BACEA714F8B907865F084503',
        'B42B085E794BA14516CE21C13CF38187',
        'FBC978E1602ED342E5567168E73F0602'
    )
GROUP BY
    GuidNo

第二个:单独运行子查询的Sql,也才用了0.1s

        SELECT
            orderpayment.OrderGuidNo
        FROM
            paymentrecord
        LEFT JOIN orderpayment ON paymentrecord.`No` = orderpayment.PaymentNo
        WHERE
            paymentrecord.PaymentMethod = 'MemberCard'
        AND paymentrecord.Payer = 13

这么问题就清晰了,一定是子查询和父查询关联的问题。因为子查询单独很快,父查询用子查询数据直接查的时候也很快,就是在他俩结合的时候很慢。大致能将问题锁定在这两个关联字段OrderGuidNo上。

最后发现 orderpayment 表和 Orders 表字符集是不同的。一个表的字符集是:utf8_general_ci,一个是:utf8mb4_general_ci。(不查不知道,发现一个数据库中,很多表的字符集都不相同)

修改orderpayment表的字符集和表中OrderGuidNo的字符集为:utf8_general_ci

ALTER TABLE orderpayment DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; //修改表的字符集
ALTER TABLE orderpayment CHANGE OrderGuidNo OrderGuidNo VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; //修改字段的字符集

然后在用EXPLAIN分析一下,可以看到都用上了索引。

然后运行,查询了0.112秒。

 

posted @ 2018-09-06 15:47  MicroHeart!  阅读(...)  评论(... 编辑 收藏