MySql SQL optimization tips and mysqlsql tips

MySql SQL optimization tips and mysqlsql tips

One day I found that the execution speed of an SQL statement with inner join was not very slow (0.1-0.2), but it did not reach the ideal speed. The two tables are associated, and the associated fields are all primary keys. The queried fields are unique indexes.

The SQL statement is as follows:

SELECTp_item_token.*,p_item.product_typeFROMp_item_tokenINNER JOIN p_item ON p_item.itemid = p_item_token.itemidWHEREp_item_token.token ='db87a780427d4d02ba2bd49fac8xxx';

In the table p_item_token, itemid is the primary key and token is the unique index. In p_item, itemid is the primary key.

The ideal speed should be around seconds. But it is about 0.2, which is much slower.

EXPLAIN the plan directly

EXPLAINSELECT  p_item_token.*,  p_item.product_typeFROM  p_item_tokenINNER JOIN p_item ON p_item.itemid = p_item_token.itemidWHERE  p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';

Result:

Check the red box above. In the p_item table, there are 2 million data records, and this is the full table scan.

Abnormal.

Add show warnings. Note: In some cases, show warnings will fail. I don't know why. We recommend that you run the test database locally.

EXPLAINSELECT  p_item_token.*,  p_item.product_typeFROM  p_item_tokenINNER JOIN p_item ON p_item.itemid = p_item_token.itemidWHERE  p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';SHOW WARNINGS;

Result 2 shows code = 1003, followed by an SQL statement. This statement is the final statement that mysql will execute after rewriting the entered SQL statement according to the rules.

/* Select #1 */SELECT 'invalid eb612d78407a91a9b3854ffffffff' AS 'itemid',/* Note: The value is directly identified by the primary key */'delimiter' AS 'Token ', '2017-12-16 10:46:53 'AS 'create _ time', 'as 'ftoken', 'P _ db '. 'P _ item '. 'product _ type' AS 'product _ type' FROM 'P _ db '. 'P _ item_token' JOIN 'P _ db '. 'P _ item' WHERE (CONVERT ('P _ db '. 'P _ item '. 'itemid' USING utf8mb4) = 'descrieb612d78407a91a9b3854fffffff '))

Strange. How does a CONVERT exist in Where? We know that if the where condition contains a function on the left side of the equation, that is, the field to be queried, it will lead to a slow process. (My understanding: slow because the index cannot be used. The index value is the original value. This condition uses the processed value .)

Note that this function converts the code of the itemid column to utf8mb4. That is to say, the encoding of this column is not utf8mb4!

Open the table and change the encoding of the itemid column in both tables to utf8. Run the explanation again.

The explanation result shows that there is no problem.

Let's look at the statements in result 2:

/* select#1 */SELECT  '0000eb612d78407a91a9b3854fffffff' AS `itemid`,  'db87a780427d4d02ba2bd49fac8cf98b' AS `token`,  '2016-12-16 10:46:53' AS `create_time`,  '' AS `ftoken`,  'cxx' AS `product_type`FROM  `toy_item_plat`.`p_item_token`JOIN `toy_item_plat`.`p_item`WHERE  1

All the constants in this select statement are constants. Is the speed low?

The result is 0.036 s. As expected

Experience summary:

Explain can check whether the execution plan meets expectations. If there is a large number of rows, it indicates that a full table scan has occurred and will be a performance bottleneck in the future.

The result of show warning shows the statements processed by the optimizer. If there is any discrepancy with the original statement, the actual problem can be found through careful comparison.