快捷導(dǎo)航

MySQL中因字段字符集不同導(dǎo)致索引不能命中的解決方法

更新時(shí)間：2018年09月10日 15:50:30 作者：MicroHeart！

這篇文章主要給大家介紹了關(guān)于MySQL中因字段字符集不同導(dǎo)致索引不能命中的解決方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

什么是索引？為什么要建立索引？

索引用于快速找出在某個(gè)列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個(gè)表，直到找出相關(guān)的行，表越大，查詢數(shù)據(jù)所花費(fèi)的時(shí)間就越多，如果表中查詢的列有一個(gè)索引，MySQL能夠快速到達(dá)一個(gè)位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)，那么將會(huì)節(jié)省很大一部分時(shí)間。

例如：有一張person表，其中有2W條記錄，記錄著2W個(gè)人的信息。有一個(gè)Phone的字段記錄每個(gè)人的電話號(hào)碼，現(xiàn)在想要查詢出電話號(hào)碼為xxxx的人的信息。

如果沒有索引，那么將從表中第一條記錄一條條往下遍歷，直到找到該條信息為止。

如果有了索引，那么會(huì)將該P(yáng)hone字段，通過(guò)一定的方法進(jìn)行存儲(chǔ)，好讓查詢?cè)撟侄紊系男畔r(shí)，能夠快速找到對(duì)應(yīng)的數(shù)據(jù)，而不必在遍歷2W條數(shù)據(jù)了。其中MySQL中的索引的存儲(chǔ)類型有兩種：BTREE、HASH。也就是用樹或者Hash值來(lái)存儲(chǔ)該字段，要知道其中詳細(xì)是如何查找的，就需要會(huì)算法的知識(shí)了。我們現(xiàn)在只需要知道索引的作用，功能是什么就行。

引言

今天寫了一個(gè)sql，其中涉及的表中的數(shù)據(jù)量都差不多為50w左右，查詢發(fā)現(xiàn)用了8s。這個(gè)只是測(cè)試服上數(shù)據(jù)，放到正式服上，肯定一運(yùn)行就掛了。

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分析了一下，發(fā)現(xiàn)Orders表沒有命中索引，但是查詢Orders中的GuidNo已經(jīng)設(shè)置了索引，但就是不能命中。

解決過(guò)程

然后我將上面的語(yǔ)句分為兩個(gè)語(yǔ)句。首先將sql語(yǔ)句修改為：將子查詢的數(shù)據(jù)直接寫在了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

第二個(gè)：?jiǎn)为?dú)運(yùn)行子查詢的Sql，也才用了0.1s

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

這么問(wèn)題就清晰了，一定是子查詢和父查詢關(guān)聯(lián)的問(wèn)題。因?yàn)樽硬樵儐为?dú)很快，父查詢用子查詢數(shù)據(jù)直接查的時(shí)候也很快，就是在他倆結(jié)合的時(shí)候很慢。大致能將問(wèn)題鎖定在這兩個(gè)關(guān)聯(lián)字段OrderGuidNo上。

最后發(fā)現(xiàn) orderpayment 表和 Orders 表字符集是不同的。一個(gè)表的字符集是：utf8_general_ci，一個(gè)是：utf8mb4_general_ci。（不查不知道，發(fā)現(xiàn)一個(gè)數(shù)據(jù)庫(kù)中，很多表的字符集都不相同）

修改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分析一下，可以看到都用上了索引。