Detailed introduction to MySql Sql optimization skills sharing

This article mainly introduces MySql Sql optimization skills sharing, which is very good and has reference value. Friends in need can refer to it

One day I found a sql with inner join Although the execution speed is not very slow (0.1-0.2), it does not reach the ideal speed. The two tables are related, and the related 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 about 0.2, which is a lot slower.

EXPLAIN directly 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 2w pieces of data in the p_item table, so this is a full table scan.

Not normal.

Add show warnings and take a look. Note: In some cases SHOW WARNINGS will have no results. 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`,    /*注:直接按主键把值查出来了*/
  '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'
    )
  )

Weird. Why is there CONVERT in Where? We know that if there is 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 reference is not available. The value of the index is the original value, but the processed value is used in this condition.)

Pay attention to this function,It means to convert the encoding of itemid column to utf8mb4. In other words, the encoding of this column is not utf8mb4!

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

Judging from the interpretation results, there is no problem.

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 all constants. Can it be faster?

The execution result is 0.036s. In line with expectations

Experience summary:

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

show warning results, you can see the statement processed by the optimizer. If there is any discrepancy with the original statement, careful comparison and study can reveal the actual problem.

The above is the detailed content of Detailed introduction to MySql Sql optimization skills sharing. For more information, please follow other related articles on the PHP Chinese website!