MySql Sql optimization tips sharing

One day I found that the execution speed of a 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 primary keys, and the queried fields are unique indexes.

The sql is as follows:

SELECT
p_item_token.*,
p_item.product_type
FROM
p_item_token
INNER JOIN p_item ON p_item.itemid = p_item_token.itemid
WHERE
p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';

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

According to the ideal speed, it should be around 0.03s. But the actual value is around 0.2, which is much slower.

Direct EXPLAIN to see the plan

EXPLAIN
SELECT
  p_item_token.*,
  p_item.product_type
FROM
  p_item_token
INNER JOIN p_item ON p_item.itemid = p_item_token.itemid
WHERE
  p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';

result:

Pay attention to the big red box above. There are 20,000 records in the p_item table, so this is a full table scan.

That's not normal.

Add show warnings and see. Note: In some cases SHOW WARNINGS will have no effect. I don't know the reason yet. It is recommended to run with a local test database.

EXPLAIN
SELECT
  p_item_token.*,
  p_item.product_type
FROM
  p_item_token
INNER JOIN p_item ON p_item.itemid = p_item_token.itemid
WHERE
  p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';
SHOW WARNINGS; 

Result 2 shows code=1003. There is a SQL statement behind it. This statement is the final statement executed by MySQL after rewriting the SQL statement we entered according to the rules.

/* select#1 */
SELECT
  '0000eb612d78407a91a9b3854ffffffff' AS `itemid`, /*Note: directly press the primary key to find the value*/
  'db87a780427d4d02ba2bd49fac8cf98b' AS `token`,    
  '2016-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
      ) = '0000eb612d78407a91a9b3854fffffff'
    )
  )

That's strange. Why is there a CONVERT in Where? We know that if there is a function on the left side of the equation in the where condition, that is, the field to be queried, it will cause slowness. (My understanding: it is slow because the index is not used. The value of the index is the original value, but the value used in this condition is the processed value.)

Note that this function converts the encoding of the itemid column to utf8mb4. In other words, 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.

There is no problem in explaining the results.

Look at the statement 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

This select is full of constants. How can it be slow?

Execution result: 0.036s. Meets expectations

Lessons learned:

Explain can check whether the execution plan meets expectations. If there are large rows, it means that a full table scan has occurred, which will become a performance bottleneck in the future.

If you use the show warning command, you can see the statements processed by the optimizer. If there are discrepancies with the original statement, careful comparison and study can reveal the actual problem.