"Type conversion" of SQL optimization

In the daily work of the DBA, the majority of SQL optimization time, usually is the SQL statement performance problem or schema design problems, recently encountered a type conversion problem, so here to share, the nonsense is not much to say, directly build the table to test.

Mysql> Create TableT1 (IDint, nameChar( -),Key(ID)); Query OK,0Rows Affected (0.02sec) MySQL> Insert  intoT1Select 1,'AA'; Query OK,1Row affected (0.00sec) Records:1Duplicates:0Warnings:0MySQL> Insert  intoT1Select 2,'BB'; Query OK,1Row affected (0.00sec) Records:1Duplicates:0Warnings:0MySQL> Create TableT2Select *  fromT1; Query OK,2Rows Affected (0.03sec) Records:2Duplicates:0Warnings:0MySQL> 

To perform a query test:

Mysql>ExplainSelecta.ID fromT1 asAInner JoinT2 asB ona.ID=b.id; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------- -----+|Id|Select_type| Table |Type|Possible_keys| Key  |Key_len|Ref|Rows|Extra|+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------- -----+|  1 |Simple|A| Index |Id|Id| 5       | NULL |    2 |UsingIndex                    ||  1 |Simple|B|  All   | NULL          | NULL | NULL    | NULL |    2 |Usingwhere; UsingJoinBuffer|+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------- ------+

What's the situation? The using join buffer appears because we used the CREATE TABLE XXX select * from XXX directly when creating the T2 tables, and this method of creating the table with the data is stripped of the index. Let's look at the table structure:

Mysql>ShowCreate Tablet2\g*************************** 1. Row***************************       Table: T2Create Table:CREATE TABLE' T2 ' (' ID ')int( One)DEFAULT NULL, ' name 'Char( -)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=UTF81Rowinch Set(0.00sec) MySQL> 

You can see the index above the ID is gone, plus the index regression topic to test.

MySQL>altertableaddkey0 rows affected (0.03 0 0 0 mysql >     

To make a join query again:

Mysql>ExplainSelecta.ID fromT1 asAInner JoinT2 asB ona.ID=b.id;+----+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------- -----+|Id|Select_type| Table |Type|Possible_keys| Key  |Key_len|Ref|Rows|Extra|+----+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------- -----+|  1 |Simple|A| Index |Id|Id| 5       | NULL      |    2 |UsingIndex              ||  1 |Simple|B|Ref|Id|Id| 5       |Test.a.id|    1 |Usingwhere; UsingIndex |+----+-------------+-------+-------+---------------+------+---------+-----------+------+---------------------- ----+2Rowsinch Set(0.00sec) MySQL> 

You can see that the execution plan above is correct, there is no exception, then when the type conversion occurs? Very simply, we changed the ID field of the T2 table to a string.

MySQL>altertablechar(2 rows affected (  0.062  0  0mysql>

Mysql>ExplainSelecta.ID fromT1 asAInner JoinT2 asB ona.ID=b.id;+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------- ------------------+|Id|Select_type| Table |Type|Possible_keys| Key  |Key_len|Ref|Rows|Extra|+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------- -------------------+|  1 |Simple|A| Index |Id|Id| 5       | NULL |    2 |UsingIndex                                 ||  1 |Simple|B| Index |Id|Id|  A      | NULL |    2 |Usingwhere; UsingIndex; UsingJoinBuffer|+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------- ------------------+2Rowsinch Set(0.00sec) MySQL> 

Obviously the implementation of the plan changes, in the case of small data volume or implementation of the situation is good, if the concurrency is large, or the amount of data is large, then it will be a disaster, which is sometimes when we are optimizing SQL, we found that the index is the implementation of the plan is not right, then you should see if there is a type conversion.

"Type conversion" of SQL optimization