Maintain a trading system, the trading record table Tradelog contains the trade serial number (TradeID), trader ID (operator), trading time (T_modified) and other fields.

create table 'tradelog' (
	'id' int(11) not null.'tradeid' varchar(32) default null.'operator' int(11) default null.'t_modified' datetime default null.primary key ('id'),
	key 'tradeid' ('tradeid'),
	key 't_modified' ('t_modified')
) engine = InnoDB default charset = utf8mb4;
Copy the code

Implicit type conversion

select * from tradelog where tradeid = 110717;
Copy the code

The trade number has its own index on the TradeID field. But explain this statement, this statement goes when the full table scan. The tradeid field type is vARCHar (32), but the input parameter is an integer, so a type conversion is required

To compare a string to a number, convert the string to a number, so the previous statement is converted to:

select * from tradelog where CAST(tradeid as signed int) = 110717;
Copy the code

This triggers the rule that by performing a function on an index field, the optimizer will disable tree search. If our statement is like this:

select * from tradelog where id = "83126";
Copy the code

No rules are triggered at this point. Because comparing a string to a number converts a string to a number. Now that the string is to the right of the comparator, it will only convert the right-hand side to a number, so no conversion will take place, so it will still search the tree.

Implicit character encoding conversion

Suppose the system has the trade_detail table to record the trade details. We insert some data into the tradelog table tradelog and trade_detail.


mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL./* Procedure */
  `step_info` varchar(32) DEFAULT NULL./* Step information */
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1.'aaaaaaaa'.1000, now());
insert into tradelog values(2.'aaaaaaab'.1000, now());
insert into tradelog values(3.'aaaaaaac'.1000, now());

insert into trade_detail values(1.'aaaaaaaa'.1.'add');
insert into trade_detail values(2.'aaaaaaaa'.2.'update');
insert into trade_detail values(3.'aaaaaaaa'.3.'commit');
insert into trade_detail values(4.'aaaaaaab'.1.'add');
insert into trade_detail values(5.'aaaaaaab'.2.'update');
insert into trade_detail values(6.'aaaaaaab'.3.'update again');
insert into trade_detail values(7.'aaaaaaab'.4.'commit');
insert into trade_detail values(8.'aaaaaaac'.1.'add');
insert into trade_detail values(9.'aaaaaaac'.2.'update');
insert into trade_detail values(10.'aaaaaaac'.3.'update again');
insert into trade_detail values(11.'aaaaaaac'.4.'commit');
Copy the code

Call this SQL statement:

select d.* from tradelog l,trade_detail d where d.tradeid = l.tradeid and l.id = 2;
Copy the code

The execution steps are as follows: 1. Find the row L2 in the tradelog table by id; 2. Fetch the value of the tradeID field from L2; 3. The procedure is to determine whether the tradeID values match one by one by iterating through the primary key index.

Step 3 here is not what we expected, because the tradeID field in the trade_detail is indexed, and we expected to be able to quickly locate rows of equivalent value by using the tradeID index. But that’s obviously not how it’s done here. This is because the character set of the two tables is different, one is UTF8, the other is UTF8MB4, so the index of the associated field is not used when doing the table join query. Change the third step of the execution step to a separate SQL statement:

select * from trade_detail where tradeid = $L2.tradeid.value;
Copy the code

The character set for $l2.tradeid. value is UTF8MB4; Utf8mb4 is a superset of UTF8, so when the two types of strings are compared, MySQL converts UTF8MB4 to UTF8MB4 and then compares them. Also equivalent to:

select * from trade_detail where convert(tradeid using utf8mb4) = $L2.tradeid.value;
Copy the code

This triggers a function operation on the index field, and the optimizer discards the tree search function. This is why different character sets join queries, associated fields do not follow the index.