preface

Index is an effective way to improve the query in MySQL, but the problem of index failure makes the development students head big! The young man experienced this hair loss problem himself some time ago.

The business scenario

The business scenario is not complicated, but there are two tables: the user table and the user score table, and now you want to find out the details of a user through the join query. Without further ado, let me show you the structure of the table:



As you can see, the structure of the two tables is very simple, and the query is just a bland join query:

select * from cps_user_info ui inner join cps_user_score us on ui.user_id=us.user_id where us.id=1;
Copy the code

Troubleshoot problems

The rule for indexing is that the join field of a join query must have an index, which is fine, and the primary key also has an index. Everything seemed so legit. But even this legitimate SQL online query takes nearly 8 seconds to consume (cps_user_score data is in the millions)! So you hurriedly open the implementation plan:

explain select * from cps_user_info ui inner join cps_user_score us on ui.user_id=us.user_id where us.id=1;
Copy the code



Take a look at the type field of the cpS_user_info table in the execution plan result above: ALL, oops, is the heartbeat feeling, no, is the heartbeat feeling! You can immediately check the index creation, index failure condition, but everything seems to be ok, is there a problem with MySQL? Please keep your head, comrade.

So we tried to change the inner join query to left join and right join, but that didn’t work. This time little xia a little panic, anxious to think of the query table to create the statement, so it is a fierce operation as a tiger, a look at the record of zero to five.



But this construction sentence looks normal ah, this index is created by a master! Huh? Wait, there seems to be a discrepancy, the CHARSET of the two tables is different. Is this reason, holding the question, the little xia modified the table character set (the operation line environment careful operation, in case of a problem can not scold little xia this deception playing with women’s feelings of man deceiving readers).

Alter character set; alter character set;



Cps_user_info type is ref. The index is obviously caused by the different character sets of the two tables! So what’s the underlying reason? In fact, this is an important principle to avoid index failure: index columns do not participate in the calculation! If the character set is different, two tables join query, column value must have conversion calculation process.

extension

If you know the explain extended+show warnings combination command, you can quickly locate the problem by changing the character set of the table back to UTF8 and using the combination command:

mysql> explain extended select * from cps_user_info ui inner join cps_user_score us on ui.user_id=us.user_id where us.id=1;
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------- -----+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------- -----+
|  1 | SIMPLE      | us    | NULL       | const | PRIMARY,idx_uid | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | ui    | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------- -----+
2 rows in set.2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                                             |
+---------+------+------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                                                                                                                                                                                                   |
| Note    | 1003 | /* select#1 */ select `studying`.`ui`.`id` AS `id`,`studying`.`ui`.`user_id` AS `user_id`,`studying`.`ui`.`user_name` AS `user_name`,'1' AS `id`,'001' AS `user_id`,'6.80' AS `score` from `studying`.`cps_user_info` `ui` join `studying`.`cps_user_score` `us` where ((convert(`studying`.`ui`.`user_id` using utf8mb4) = '001')) |
+---------+------+------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------+
Copy the code

User_id using utf8mb4 convert(data.ui.user_id using utf8mb4)

thinking

In fact, the index failure involved in this paper is only the index column involved in the operation, but not explicit mathematical calculation, in fact, in the development of a variety of scenarios, in-depth theory and can identify the actual application of the theory in different scenarios is very important, I wish to share with you! Finally, the SQL created statements are attached for interested readers to practice.

CREATE TABLE `cps_user_info`(
	`id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_uid`(`user_id`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  CREATE TABLE `cps_user_score`(
	`id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) NOT NULL,
  `score` decimal(8.2) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_uid`(`user_id`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ` ` `Copy the code