Shulou(Shulou.com)06/02 Report--

The problem of implicit conversion after MySQL index failure is solved. Aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Common index failures:

1. Conditional index field "unclean": function operation, operation operation

two。 Implicit type conversion: string conversion value; other type conversion

3. Implicit character coding conversion: conversion according to the direction of large length of character-encoded data to avoid data interception

1. Common index failure scenarios root@test 10:50 > show create table t_num\ index * 1. Row * * Table: t_numCreate Table: CREATE TABLE `tnum` (`id` int (11) NOT NULL AUTO_INCREMENT, `c1` int (11) NOT NULL, `c2` varchar (11) NOT NULL PRIMARY KEY (`id`), KEY `ix_ c1` (`c1`) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4root@test 10:51 > select * from t_num +-+ | id | C1 | c2 | +-- + | 1 | 2 |-2 | 2 | 2 |-1 | | 3 | 0 | 4 | 1 | | 5 | 2 | 2 | +-- + # add an index to the C1 field root@test 10:52 > alter table t_num add index ix_c1 (C1) # under standard usage, the index is valid root@test 10:55 > explain select * from t_num where C1 =-1 +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | t_num | NULL | ref | ix_c1 | ix_c1 | 4 | const | 1 | 100.00 | NULL | + -+ 1. Conditional Field function Action # add abs () absolute value function to C1 in where You can see type=ALL, full table scan, and comparison in the Server layer after absolute value processing root@test 10:58 > explain select * from t_num where abs (C1) = 1 +-+ | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-- + -+ | 1 | SIMPLE | t_num | NULL | ALL | NULL | 5 | 100.00 | Using where | +- -+

As above, the functional operation on the index field, that is, when the where condition column is not clean, it may destroy the order of the index value (organize the index tree according to the value of C1), so the optimizer decides to give up the walking index tree search function.

However, under the conditional field function operation, the full table scan is not complete, and the optimizer does not completely abandon the field index.

# Select the data to be queried. There are only id and C1 fields. You can see type=index. The ix_c1 index root@test 10:59 > explain select id,c1 from t_num where abs (C1) = 1 is used. +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | t_num | NULL | index | NULL | ix_c1 | 4 | NULL | 5 | 100.00 | Using where Using index | +-+- -+

As above, because the ix_c1 index tree is constructed by the root node C1 and the leaf node id, although the index location is abandoned because of the function operation on C1, the optimizer can choose to traverse the index tree and use the overlay index (Using index) without going back to the table, return the required id and C1 data to the Server layer for subsequent abs () and where filtering.

2. Condition field operation # in where condition, operate on C1 root@test 11:03 > explain select * from t_num where C1 + 1 = 2 +-+ | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-- + -+ | 1 | SIMPLE | t_num | NULL | ALL | NULL | 5 | 100.00 | Using where | +- -+

As above, although the "+ 1" operation does not break the ordering of the C1 index, the optimizer still does not use the index to locate quickly. Therefore, to the left of the equal sign, note that the operation on the index field is optimized.

3. Implicit type conversion # add an index to the c2 field root@test 12:30 > alter table t_num add index ix_c2 (c2); # in the case of standard use (note: C2 is of type varchar), the index is valid root@test 12:30 > explain select * from t_num where c2 = "2" +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | t_num | NULL | ref | ix_c2 | ix_c2 | 42 | const | 1 | 100.00 | NULL | +- -+ # remove the quotation marks to the right of the equal sign That is, when comparing a string with a numeric value, the index fails root@test 12:30 > explain select * from t_num where c2 = 2 +-+ | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-- + -+ | 1 | SIMPLE | t_num | NULL | ALL | ix_c2 | NULL | 5 | 20.00 | Using where | +- -+

As mentioned above, the c2 field is a varchar type, which is a comparison between a string and a numeric value. In this case, MySQL converts a string into a number, that is, c2 is CAST (c2 AS signed int), which is equivalent to a function operation on the condition field, and the optimizer abandons the tree index location.

4. Implicit character Encoding conversion # create a t_cou table with the same structure as the previous t_num The only different settings are table character set CHARSET=utf8root@test 14:02 > show create table t_cou\ gateway * 1. Row * * Table: t_couCreate Table: CREATE TABLE `tcou` (`id` int (11) NOT NULL AUTO_INCREMENT, `c1` int (11) NOT NULL, `c2` varchar (10) NOT NULL PRIMARY KEY (`id`), KEY `ix_ c1` (`c1`), KEY `ix_ c2` (`c2`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8root@test 14:02 > insert into t_cou select * from t_num # join table, t_num and t_cou are related to query via c2 field root@test 14:03 > select n.* from t_num n-> join t_cou c-> on n.c2 = c.c2-> where n.c1 = 1 +-+ | id | C1 | c2 | +-- + | 4 | 1 | 1 | +-+ root@test 14:23 > explain select n. * from t_num n join t_cou c on n.c2 = c.c2 where c.c1 = 1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -- +-- + | 1 | SIMPLE | c | NULL | ref | ix_c1 | ix_c1 | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | | n | NULL | ref | ix_c2 | ix_c2 | 42 | func | 1 | 100.00 | Using index condition | +- -+-+ # execution Plan Analysis: # 1. Operation of the c table, using the ix_c1 to navigate to a row of data # 2. The row data located from the c table is taken to the c2 field to manipulate the n table, t_cou is called the driven table, and t_num is called the driven table # 3.ref=func indicates that the function operation is used, which refers to n.c2=CONVERT (c.c2 USING utf8mb4) # 4. At the same time, when Using index condition,ix_c2 reads the query, it uses the pushed condition filter, and the table root@test 14:23 > explain select n. * from t_num n join t_cou con n.c2 = c.c2 where n.c1 = 1 is returned only if the condition is met. +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +- -+ | 1 | SIMPLE | n | NULL | ref | ix_c1 Ix_c2 | ix_c1 | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | index | NULL | ix_c2 | 32 | NULL | 5 | 100.00 | Using where Using index Using join buffer (Block Nested Loop) | + -+ # execution plan analysis: # 1. Operation of the n table, using the ix_c1 to navigate to a row of data # 2. The row data located from the n table is taken to the c2 field to manipulate the c table. T_num is called the driven table and t_cou is called the driven table # 3. The same n.c2=c.c2 will convert the character set of c.c2, that is, add a function operation to the index field of the driven table, when the index fails # 4.BNL, when the table join, the driven table data is read into join buffer, and if the driven table join field has no index, the whole table is scanned. Each row is compared with the join buffer data and returned as the result set.

As above, analyze the execution plan of t_num and t_cou as driven table and driven table respectively, and summarize:

When comparing strings of two different character set (encoding) types, utf8mb4 and utf8, MySQL first converts the utf8 string to the utf8mb4 character set, and then makes a comparison. Why? Character set utf8mb4 is a superset of utf8. When implicit automatic type conversion is done, in order to avoid data errors caused by truncation in the conversion process, the conversion will be carried out "in the direction of increasing data length".

In the process of table join, adding a function operation to the index field of the driven table will lead to a full table scan of the driven table.

Optimization techniques:

Modify the character set of a unified join field

Start with the driver table and convert the character set of the connection field into the character set of the connection field of the driven table.

Root@test 18:09 > explain select n. * from t_num n join t_cou con convert (n.c2 using utf8) = c.c2 where n.c1 = 1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | n | NULL | ref | ix_c1 | ix_c1 | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | ref | ix_c2 | ix_c2 | 32 | func | 1 | 100.00 | Using where | Using index | +-+- -+ 2. Type conversion 1, string conversion begins with the # character 0root@test 18:44 > select convert ("abc") Unsigned integer) +-- + | convert ("abc") Unsigned integer) | +-+ | 0 | +-+ # 'abc' = 0 is valid Therefore, it is important to use the corresponding type to the right of the equal sign when querying. 0 matches the data at the beginning of the field character.'0' only matches 0root@test 18:44 > select 'abc' = 0. +-+ | 'abc' = 0 | +-+ | 1 | if the number begins with +-+ #, it is directly intercepted to the first position that is not a character root@test 18:45 > select convert ("123abc", unsigned integer) +-- + | convert ("123abc") Unsigned integer) | +-+ | 123 | +-+ 2, Time type conversion root@test 19:11 > show create table time_demo\ gateway * 1. Row * * Table: time_demoCreate Table: CREATE TABLE `time_ demo` (`id` int (11) NOT NULL AUTO_INCREMENT `c1` datetime DEFAULT NULL, `c2` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `ix_ c1` (`c1`) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4root@test 19:15 > select count (*) from time_demo +-+ | count (*) | +-+ | 11 | +-+ root@test 19:16 > select * from time_demo limit 4 +-+ | id | C1 | c2 | +-- + | 1 | 2022-01-08 00:01:01 | 2022-01-08 | | 2 | 2022-01-06 23:01:01 | 2022-01-06 | 3 | 2022-01-06 00:00:00 | 2022-01-06 | 4 | 2022-01-08 00:00:00 | 2022-01-08 | +-+ # 1.date to datetime: append 00:00:00root@test 19:11 > select * from Time_demo where C1 between "2022-01-06" and "2022-01-08" +-+ | id | C1 | c2 | +-- + | 2 | 2022-01-06 23:01:01 | 2022-01-06 | | 3 | 2022-01-06 00:00:00 | 2022-01-06 | | 4 | 2022-01-08 00:00:00 | 2022-01-08 | +-+ # result analysis: C1 is datetime type When comparing, the date type in between and is converted to datetime#, that is, where C1 between "2022-01-06 00:00:00" and "2022-01-08 00:00:00" # same as where C1 > = "2022-01-06 00:00:00" and C1 explain select * from time_demo where C1 between "2022-01-06" and "2022-01-08" +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | time_demo | NULL | range | ix_c1 | ix_c1 | 6 | NULL | 3 | 100.00 | Using index condition | + -- +- -+ # format date to datetimeroot@test 19:23 > select date_format ("2022-01-08" "Y-%m-%d H:%i:%s") +-+ | date_format ("2022-01-08" "% Y-%m-%d% H:%i:%s") | +-- + | 2022-01-06 00:00:00 | +-- -+ # 2.datetime to date: directly intercept part of date root@test 19:47 > select date (C1) from time_demo limit 1 +-+ | date (C1) | +-+ | 2022-01-06 | +-+ # 3.date to time, which is meaningless, but directly becomes 00:00:00. The solution to the problem of implicit conversion after the MySQL index fails is shared here. I hope the above content can be of some help to you. If you still have a lot of questions to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.