MYSQL 5.7 升級 8.0 後的 由於字符集導致的大問題 ？

MYSQL 8.0 已經很多年了，但是，但是，但是，還有很多公司和業務專案在MYSQL5.6 ,5.7上繼續奮鬥，這還不是一個重要的問題，重要的問題是早期在MYSQL 5.7 上的一些基礎，並未進行改變後到了MYSQL 8 上的使用一段時間產生的問題。

這裡在MYSQL5.6,MYSQL.5.7上大部分的表還都是 utf8 ， default charset =utf8  而在這些資料庫升級的情況下，表基本上是照搬到MYSQL 8.0上的，但是後續會產生一個問題。建立新的表。此次我們採用MYSQL 最新的版本之一，MYSQL 8.030 來進行相關的問題的分析和查詢。

下面就是一個典型的例子，在建立一個MYSQL的表的情況下，如果開發部指定 default charset=utf8 則預設建立新表就是utf8mb4 ,而這樣就會產生一個嚴重的問題。

一個資料庫中的表的字符集不一致。然後就會產生一個問題，兩個表的字符集不同，如果兩個表之間的查詢是不關聯的，這到不會造成什麼嚴重的問題，而如果這兩個表產生了之間的關聯性那麼問題就出現了。

我們創造一個奇怪的資料庫，以及表，這裡的表的字符集在 utf8  和  utf8mb4 之間混合著。

在這樣的情況下，會產生如下一些假設,下面是一些表的表結構，其中orders的表，是UTF8MB4 格式 排序是 utf8mb4_9000_ai_ci  ，而我們的payments表是utf8mb3 的格式。

CREATE TABLE `orders` (

  `ordernumber` varchar(200) CHARACTER SET utf8 NOT NULL,

  `orderDate` date NOT NULL,

  `requiredDate` date NOT NULL,

  `shippedDate` date DEFAULT NULL,

  `status` varchar(15) CHARACTER SET utf8 NOT NULL,

  `comments` text CHARACTER SET utf8,

  `customernumber` varchar(200) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`ordernumber`),

  KEY `customerNumber` (`customernumber`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci；

 CREATE TABLE `payments` (

  `customerNumber` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,

  `checkNumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,

  `paymentDate` date NOT NULL,

  `amount` decimal(10,2) NOT NULL,

  PRIMARY KEY (`customerNumber`,`checkNumber`),

  KEY `idx_payment` (`paymentDate`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3；

1   使用 left join 的時候，兩個表，誰在前誰在後，最佳化器是否能正確執行相關結果。

explain select *

from payments as p 

left join (select * from orders) as o on p.customerNumber = o.customerNumber 

where paymentdate > '2005-01-01';

explain select *

from orders as o 

left join (select * from payments) as p on p.customerNumber = o.customerNumber 

where paymentdate > '2005-01-01';

語句如上，第一個語句為 utf8 作為驅動表，可以明顯看到因為兩個表的字符集和collation的不同，導致無法走索引進行查詢，這裡也就是 payments  的主鍵與order 的主鍵無法進行正確的連線和比對，而資料庫沒有辦法，走了另外的最佳化方式，透過HASH JOIN 的方式進行處理。 

那麼我們如果反過來進行查詢的話情況是不是有變化，有些文章中提到變換驅動表關係，可以在有些版本上可以解決由於字符集不同的問題，導致的索引失效的問題。

那麼我們變換一下驅動表的位置，整體的查詢計劃進行了變化，相關的執行計劃的效率稍有提高。

我們將語句實際執行，並檢視profilings ，這裡可以看到的是，我們將payments 放到上面的情況下 executing  為 700 而將ORDER 放到驅動表的情況下，execute 變為 1742 

因為怕測試中的一些查詢與表的行數等特徵影響，我們在變化一下兩個表的coding .

mysql> ALTER TABLE orders CONVERT TO CHARACTER SET utf8;

Query OK, 0 rows affected, 1 warning (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 1

mysql> 

mysql> 

mysql> ALTER TABLE payments CONVERT TO CHARACTER SET utf8mb4;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

這裡我們再次做相關的測試，發現調整後，

我看可以看到，實際上經過以上的操作和分析，如果作為驅動表，表小並且是utf8mb4的情況下，要比驅動表大並且是 utf8 的情況略好。

但如何，都不如統一的字符集讓資料庫的查詢更能良好的執行。在我們統一字元到 utf8mb4 後，整體的查詢正常了

所以以上列子中，主要是說明在MYSQL 5.7 遷移過來的表大部分都是 UTF8MB3 ，而如果MYSQL 8 不做任何處理，則新建的表是 UTF8MB4 ，所以，希望MYSQL 的同學注意以上問題，注意這樣的情況，儘量避免。

另外還有一些事情，需要深入，有的時候即使字符集不同，collation的排序在某些情況下，在字符集不同的情況下還可以走索引。