MySQL表欄位字元集不同導致的索引失效問題

標籤：

1. 概述

昨天在一位同學的MySQL機器上面發現了這樣一個問題，MySQL兩張表做left join時，執行計畫裡面顯示有一張表使用了全表掃描，掃描全表近100萬行記錄，大並發的這樣的SQL過來資料庫變得幾乎不可用了，今天和大家一起分享下這個問題的原因及解決辦法，希望可以協助大家更好的學習MySQL資料庫，一起來看看吧。MySQL版本為官方5.7.12。

2. 問題重現

首先，表結構和表記錄如下：

mysql> show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`code` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_code` (`code`),

KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> show create table t2\G

*************************** 1. row ***************************

Table: t2

Create Table: CREATE TABLE `t2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`code` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_code` (`code`),

KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

mysql> select * from t1;

+—-+——+———————————-+

| id | name | code |

+—-+——+———————————-+

| 1 | aaaa | 0752b0e3c72d4f5c701728db8ea8a3f9 |

| 2 | bbbb | 36d8147db18d55e64c8b5ea8679328b7 |

| 3 | cccc | dc3bab5197eeb6b315204f0af563c961 |

| 4 | dddd | 1bb4dc313a54e4c0ee04644d2a1fe900 |

| 5 | eeee | f33180d7745079d2dfaaace2fdd74b2a |

+—-+——+———————————-+

5 rows in set (0.00 sec)

mysql> select * from t2;

+—-+——+———————————-+

| id | name | code |

+—-+——+———————————-+

| 1 | aaaa | bca3bc1eb999136d6e6f877d9accc918 |

| 2 | bbbb | 77dd5d07ea1c458afd76c8a6d953cf0a |

| 3 | cccc | 3ac617d1857444e5383f074c60af7efd |

| 4 | dddd | 8a77a32a7e0825f7c8634226105c42e5 |

| 5 | eeee | 0c7fc18b8995e9e31ca774b1312be035 |

+—-+——+———————————-+

5 rows in set (0.00 sec)

2張表left join的執行計畫如下：

mysql> desc select * from t2 left join t1 on t1.code = t2.code where t2.name = ’dddd’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t2

partitions: NULL

type: ref

possible_keys: idx_name

key: idx_name

key_len: 83

ref: const

rows: 1

filtered: 100.00

Extra: NULL

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

filtered: 100.00

Extra: Using where; Using join buffer (Block Nested Loop)

2 rows in set, 1 warning (0.01 sec)

可以明顯地看到，t2.name = ‘dddd’使用了索引，而t1.code = t2.code這個關聯條件沒有使用到t1.code上面的索引，一開始Scott也百思不得其解，但是機器不會騙人。Scott用show warnings查看改寫後的執行計畫如下：

mysql> show warnings;

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note | 1003 | /* select#1 */ select `testdb`.`t2`.`id` AS `id`,`testdb`.`t2`.`name` AS `name`,`testdb`.`t2`.`code` AS `code`,`testdb`.`t1`.`id` AS `id`,`testdb`.`t1`.`name` AS `name`,`testdb`.`t1`.`code` AS `code` from `testdb`.`t2` left join `testdb`.`t1` on((convert(`testdb`.`t1`.`code` using utf8mb4) = `testdb`.`t2`.`code`)) where (`testdb`.`t2`.`name` = ’dddd’) |

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

在發現了convert(testdb.t1.code using utf8mb4)之後，Scott發現2個表的字元集不一樣。t1為utf8，t2為utf8mb4。但是為什麼表字元集不一樣（實際是欄位字元集不一樣）就會導致t1全表掃描呢？下面來做分析。

（1）首先t2 left join t1決定了t2是驅動表，這一步相當於執行了select * from t2 where t2.name = ‘dddd’，取出code欄位的值，這裡為’8a77a32a7e0825f7c8634226105c42e5’;

（2）然後拿t2查到的code的值根據join條件去t1裡面尋找，這一步就相當於執行了select * from t1 where t1.code = ‘8a77a32a7e0825f7c8634226105c42e5’;

（3）但是由於第（1）步裡面t2表取出的code欄位是utf8mb4字元集，而t1表裡面的code是utf8字元集，這裡需要做字元集轉換，字元集轉換遵循由小到大的原則，因為utf8mb4是utf8的超集，所以這裡把utf8轉換成utf8mb4，即把t1.code轉換成utf8mb4字元集，轉換了之後，由於t1.code上面的索引仍然是utf8字元集，所以這個索引就被執行計畫忽略了，然後t1表只能選擇全表掃描。更糟糕的是，如果t2篩選出來的記錄不止1條，那麼t1就會被全表掃描多次，效能之差可想而知。

3. 問題解決

既然原因已經清楚了，如何解決呢？當然是改字元集了，把t1改成和t2一樣或者把t2改成t1都可以，這裡選擇把t1轉成utf8mb4。那怎麼轉字元集呢？

有的同學會說用alter table t1 charset utf8mb4;但這是錯的，這隻是改了表的預設字元集，即新的欄位才會使用utf8mb4，已經存在的欄位仍然是utf8。

mysql> alter table t1 charset utf8mb4;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,

`code` varchar(50) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_code` (`code`),

KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

只有用alter table t1 convert to charset utf8mb4;才是正確的。

但是還要注意一點，alter table 改字元集的操作是阻塞寫的（用lock = node會報錯）所以業務高峰時請不要操作，即使在業務低峰時期，大表的操作仍然建議使用pt-online-schema-change線上修改字元集。

mysql> alter table t1 convert to charset utf8mb4, lock=none;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.

mysql> alter table t1 convert to charset utf8mb4, lock=shared;

Query OK, 5 rows affected (0.04 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`code` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_code` (`code`),

KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

現在再來查看執行計畫，可以看到已經沒問題了。

mysql> desc select * from t2 join t1 on t1.code = t2.code where t2.name = ’dddd’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t2

partitions: NULL

type: ref

possible_keys: idx_code,idx_name

key: idx_name

key_len: 83

ref: const

rows: 1

filtered: 100.00

Extra: Using where

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: ref

possible_keys: idx_code

key: idx_code

key_len: 203

ref: testdb.t2.code

rows: 1

filtered: 100.00

Extra: NULL

2 rows in set, 1 warning (0.00 sec)

4. 注意點

（1）表字元集不同時，可能導致join的SQL使用不到索引，引起嚴重的效能問題；

（2）SQL上線前要做好SQL Review工作，盡量在和生產環境一樣的環境下Review；

（3）改字元集的alter table操作會阻塞寫，盡量在業務低峰操作，建議用pt-online-schema-change;

（4）表結構字元集要保持一致，發布時要做好審核工作；

（5）如果要大批量修改表的字元集，同樣做好SQL的Review工作，關聯的表的字元集一起做修改。

5. 問題討論

最後問一個問題，假設現在t1和t2表的字元集還未修改，如果上面那個問題SQL換成如下（即把t2 left join t1換成t1 left join t2），還會出現索引失效問題嗎？為什嗎？

select * from t1 join t2 on t1.code = t2.code where t1.name = ’dddd’

 

文章來源：InsideMySQL

 

MySQL表欄位字元集不同導致的索引失效問題