SQL > select * from ‘MySQL’ where a cast can cause a deadlock. SQL > select * from ‘MySQL’ where a cast can cause a deadlock. SQL > select * from ‘MySQL’ where a cast can cause a deadlock.
Type 1: The column type does not match the WHERE value type and cannot match the index, causing a full table scan.
Data preparation:
create table t1 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;
insert into t1(cell) values (‘111’),(‘222’),(‘333’);
(1) Cell attribute is of vARCHAR type;
(2) Primary key of cell, namely clustered index;
(3) T1 inserts 3 test data;
Test statement:
explain select * from t1 where cell=111;
explain select * from t1 where cell=’111′;
Select * from cell where (cell type) where (cell type);
Select * from cell where (cell type);
Test results:
(1) Cast, can not match the index, need to scan the full table, that is, 3 records;
(2) Same type, matching index, 1 record;
Voice-over: For explain_, see two tools for MySQL deadlock analysis __. _
Type 2: The character encoding of two join tables is different and cannot match the index, resulting in the calculation of the Cartesian product by a nested loop.
Data preparation:
create table t2 (
cell varchar(3) primary key
)engine=innodb default charset=latin1;
_insert into t2(cell) values (‘111’),(‘222’),(‘333’),(‘444’),(‘555’),(‘666’);
_
create table t3 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;
insert into t3(cell) values (‘111’),(‘222’),(‘333’),(‘444’),(‘555’),(‘666’);
(1) The t2 and T1 character sets are different, insert 6 test data;
(2) The T3 and T1 character sets are the same, also insert 6 test data;
(3) In addition, t1, T2 and T3 tables have the same structure;
Test statement:
explain select * from t1,t2 where t1.cell=t2.cell;
explain select * from t1,t3 where t1.cell=t3.cell;
(1) join table T1 and t2 (different character set), associated attribute is cell;
(2) join table T1 and t3 (same character set), associated attribute is cell;
Test results:
(1) Different t1 and T2 character sets, different storage space;
(2) When T1 and T2 joined, all 3 records of T1 were traversed, and each record of T1 had to traverse all 6 records of T2, resulting in a Cartesian product loop calculation with invalid indexes.
(3) When T1 and T3 join, all three records of T1 are traversed, and each record of T1 uses T3 index, that is, one row of record is scanned;
Voice-over: Please enlarge the picture.
conclusion
Two types of covert cases that cannot take advantage of indexes:
(1) the table column type is not consistent with where value type;
(2) Character encoding of join table is different;
homework
create table t1 (
cell varchar(3) primary key
)engine=innodb default charset=utf8;
insert into t1(cell) values (‘111’),(‘222’),(‘333’);
create table t2 (
cell char(3) primary key
)engine=innodb default charset=utf8;
_insert into t2(cell) values (‘111’),(‘222’),(‘333’),(‘444’),(‘555’),(‘666’);
_
create table t3 (
cell int primary key
)engine=innodb default charset=utf8;
insert into t3(cell) values (111),(222),(333),(444),(555),(666);
(1) T1, T2, and T3 have different cell types: vARCHar (3), char(3), and int respectively;
(2) The encoding type is the same, both are UTF8;
T1, t2, t1, t3 join query, can match index?
explain select * from t1,t2 where t1.cell=t2.cell;
explain select * from t1,t3 where t1.cell=t3.cell;
The “actual result” and “what you think” may not be the same.