MySQL查询计划

为什么80%的码农都做不了架构师？>>>   

1、Explain显示查询计划

explain ops stmt，stmt可以是select/insert/update/delete，其中ops如下

（1）EXTENDED

显示SQL语句的详细查询执行计划，之后可以通过SHOW WARNINGS命令查看详细的信息

（2）PARTITIONS

显示SQL语句的带有分区表信息的查询执行计划

（3）FORMAT = TRADITION/JSON

TRADITIION：传统类型，按行隔离

JSON：JSON格式

2、查询实例

（1）建表

CREATE TABLE t1 (id1 INT, a1 INT, b1 INT, PRIMARY KEY(id1)); 
CREATE TABLE t2 (id2 INT, a2 INT, b2 INT); 
CREATE TABLE t3 (id3 INT UNIQUE, a3 INT, b3 INT); 
CREATE TABLE t4 (id4 INT, a4 INT, b4 INT); 
CREATE TABLE t5 (id5 INT UNIQUE, a5 INT, b5 INT);

（2）准备数据

准备t1

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertT1`()
BEGIN
set @counter=0;  
while @counter < 10000 do
SET @col2 = FLOOR(1 + (RAND() * 10000));
set @col3 = FLOOR(1 + (RAND() * 10000));
insert into t1(id1,a1,b1) values(@counter,@col2,@col3);
set @counter = @counter +1;
END while;
END$$
DELIMITER ;

   call insertT1;

准备t2

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertT2`()
BEGIN
SET @counter = 0;
while @counter < 100 do
SET @col2 = FLOOR(1 + (RAND() * 10000));
set @col3 = FLOOR(1 + (RAND() * 10000));
insert into t2(id2,a2,b2) values(@counter,@col2,@col3);
set @counter = @counter +1;
end while;
END$$
DELIMITER ;

准备t3

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertT3`()
BEGIN
SET @counter = 0;
while @counter < 100 do
SET @col2 = FLOOR(1 + (RAND() * 10000));
set @col3 = FLOOR(1 + (RAND() * 10000));
insert into t3(id3,a3,b3) values(@counter,@col2,@col3);
set @counter = @counter +1;
end while;
END$$
DELIMITER ;

准备t4

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertT4`()
BEGIN
SET @counter = 0;
while @counter < 7 do
SET @col2 = FLOOR(1 + (RAND() * 10000));
set @col3 = FLOOR(1 + (RAND() * 10000));
insert into t4(id4,a4,b4) values(@counter,@col2,@col3);
set @counter = @counter +1;
end while;
END$$
DELIMITER ;

准备t5

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertT5`()
BEGIN
SET @counter = 0;
while @counter < 10 do
SET @col2 = FLOOR(1 + (RAND() * 10000));
set @col3 = FLOOR(1 + (RAND() * 10000));
insert into t5(id5,a5,b5) values(@counter,@col2,@col3);
set @counter = @counter +1;
end while;
END$$
DELIMITER ;

（5）explain

explain select * from (t1 left join t2 on true),(t3 full join t4 on true),t5
where id1=id2 and id2 = id3 and id3 = id4 and id4 = id5;

结果

说明：

A、id表示对象操作顺序，数字越大，越先被执行

B、表连接顺序为t4，t5，t3，t1，t2，因为t4表的记录最少，因此mysql优化后，先连接t4

C、因为t5，t3，t1有索引可以用，因此用索引定位这三个表的数据

D、t2表的数据多，而且又没有索引可以用，因此放到最后连接。连接使用了Extra列表明的块嵌套循环连接算法，并且使用了连接缓存。

转载于:https://my.oschina.net/scipio/blog/292777