MySQL JOIN时对两个VARCHAR字段调用CONVERT(USING utf16)的原因排查

Let’s break down exactly what’s happening here and why that CONVERT call is appearing in your execution plan.

The Root Cause: Character Set/Collation Mismatch #

Even though you noted stock.product_id and prices.sku are both VARCHAR(128), MySQL doesn’t only check data type and length when comparing columns in a JOIN. It also validates their character set and collation settings.

In your case, prices.sku is likely using the utf16 character set, while stock.product_id uses a different one (like utf8mb4, latin1, etc.). When MySQL tries to compare values from these mismatched columns, it can’t perform a direct, index-friendly comparison—so it automatically converts the values from the non-matching column (stock.product_id) to match the character set of the other column (prices.sku). That’s exactly why you see CONVERT(stock.product_id USING utf16) in the SHOW WARNINGS output.

Why This Hurts Performance #

This on-the-fly conversion creates two key performance issues visible in your EXPLAIN results:

1. Full table scan on stock: Since MySQL has to convert every product_id value before comparing it to prices.sku, it can’t use any index on stock.product_id. This explains the type: ALL for the stock table—MySQL is scanning all 128k rows.
2. ref: func for prices: Instead of using a direct index lookup against a column value, MySQL uses the result of the CONVERT function to look up in prices' index. While it’s still using an index (sku_channel), this is less efficient than a direct column-to-column index join.

How to Verify the Mismatch #

To confirm the character set/collation difference, run these queries to check the full column details:

-- Check stock.product_id's charset and collation
SHOW FULL COLUMNS FROM stock LIKE 'product_id';

-- Check prices.sku's charset and collation
SHOW FULL COLUMNS FROM prices LIKE 'sku';

Look at the Collation column in both results—they’ll be different (e.g., one might be utf16_general_ci and the other utf8mb4_general_ci).

Fixing the Issue #

The best long-term fix is to align the character set and collation of both columns so no conversion is needed. For example, if you want to standardize stock.product_id to match prices.sku:

-- Alter stock.product_id to match prices.sku's charset/collation (replace values as needed)
ALTER TABLE stock MODIFY COLUMN product_id VARCHAR(128) CHARACTER SET utf16 COLLATE utf16_general_ci;

After making this change, re-run your EXPLAIN EXTENDED query. You should see ref show the column name (e.g., stock.product_id) instead of func, and the stock table will use an index if one exists on product_id.

内容的提问来源于stack exchange，提问作者atoms