Mysql 优化的艺术| 小册免费学

1,072 阅读7分钟

如果你还记得之前说过的mysql基于成本选择索引的话,那就不可避免地想到,实际的IO成本和以及回表所需的成本是和计算规则严重不符的,换句话说,当我们确认自己足够了解数据的前提下,我们指定索引的查询效率是高于系统为我们提供的选择的。那么如何指定索引呢?

自力更生!我的查询我做主

强制选择索引

SELECT * 

FROM table_name

FORCE INDEX (`index_name`)

WHERE 

指定索引

USE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

image.png

CREATE TABLE student (
    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';
insert into student values(1111,'肚子疼','软件学院');
insert into student values(2222,'范统','计算机学院');
insert into student values(3333,'是真相','计算机学院');
create index name on student(name);
create index major on student(major);
explain select name from student use index (major)
where name = '肚子疼' and major = '软件学院';

结果 }RKD1RG$BE@JGPR.png

那么 use index 和 force index有什么区别呢

use index 是给出一个选择,mysql可以不选择使用索引,而改为全表扫描的方式解决。

禁止索引

IGNORE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

join引发的一系列问题

在SQL Server中,我们所常见的表与表之间的Inner Join,Outer Join都会被执行引擎根据所选的列,数据上是否有索引,所选数据的选择性转化为Loop Join,Merge Join,Hash Join这三种物理连接中的一种。

连接 内连接 外连接

join将两个表的数据连接到一起,一般情况下,两个表连接后数据行数为Num1 * Num2 ,当然,这种连接方式可能不是很常用,大多数时候还是通过某种条件(比如,学号末四位为1234的学生的科目成绩及学生信息),这类条件我们大概可以分为两类:

  1. 只和单表有关系的条件:t1.m1 > 1
  2. 和两个表都有关系的条件:t1.m1 = t2.m2t1.n1 > t2.n2
  • 驱动表: 首先被查询的表,并通过这个表去查询另一个表

  • 被驱动表: 被驱动表查询的表叫做被驱动表

  • 内连接: 只输出匹配条件行的表 +外连接: 匹不匹配都要输出

    • 左外连接:左边的表为驱动表
    • 右外连接

这个匹配指的是:驱动表的记录 在被驱动表中被匹配,因此左外连接和右外连接搜索输出行数是会有区别的

对于这类”区别“,我们可能不想让她们出现,也可能想让她们出现,因此为了正确的表示我们的想法,我们将过滤条件分为了 where 和 on

嵌套循环连接(Nested-Loop Join)

就是一次for循环,

  1. 先找到驱动表的符合条件的数据行,
  2. 再根据每个数据行去被驱动表中查询

第二步每次都是全表查询的话可能会过于慢,因此考虑用索引

基于块的嵌套循环连接(Block Nested-Loop Join)

就像之前说的一样,mysql消耗主要在IO,普通的嵌套循环IO消耗未免过大,可不可以换一次循环顺序,即将被驱动表的数据分,每次IO读取一块。循环驱动表中的数据行与该数据块匹配,这样的好处就是显著的减少了IO的消耗。

合并连接(Merge Join)

merge这次很眼熟,大概是数据结构里面归并排序时看到过,实际上Merge Join和归并排序也差不多,Merge Join其实上就是将两个有序队列进行连接,需要两端都已经有序,所以不必像Loop Join那样不断的查找循环内部的表。其次,Merge Join需要表连接条件中至少有一个等号查询分析器才会去选择Merge Join。

  • 对于两个有序的数据表AB
  • Merge Join首先从两个输入集合中各取第一行,如果匹配,则返回匹配行。假如两行不匹配,则有较小值的输入集合+1 通常来说Merge Join如果输入两端有序,则Merge Join效率会非常高,但是如果需要使用显式Sort来保证有序实现Merge Join的话,那么Hash Join将会是效率更高的选择。但是也有一种例外,那就是查询中存在order by,group by,distinct等可能导致查询分析器不得不进行显式排序,那么对于查询分析器来说,反正都已经进行显式Sort了,何不一石二鸟的直接利用Sort后的结果进行成本更小的MERGE JOIN?在这种情况下,Merge Join将会是更好的选择。

哈希连接 hash join

哈希匹配连接相对前面两种方式更加复杂一些,但是哈希匹配对于大量数据,并且无序的情况下性能均好于Merge Join和Loop Join。对于连接列没有排序的情况下(也就是没有索引),查询分析器会倾向于使用Hash Join。

hash算法不可避免地需要对数据进行hash【因此会尽量选择数据量小的当作驱动表】,那么势必会造成大量的CPU消耗,个人觉得对数据量大的情况不适合

在join时,驱动表合适与否势必会对效率产生影响

mysql中指定了连接条件时,满足查询条件的记录行数少的表为驱动表;如未指定查询条件,则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的。

因此,对于inner join的方式,如果想制定驱动表,可以使用STRAIGHT_JOIN

子查询优化篇

什么是快乐子查询啊

子查询就是在一次查询A中使用另一次查询B的结果作为A的条件(范围)

听起来很难理解,不妨 image.png

一次查询的结果可能是许多行,也可能是一行,也可能是一个数, 因此我们有

//子查询是一个数字
SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);
//子查询是一行(其实这跟一个数字没什么区别)
SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
//子查询是许多行
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

除了IN以外,还有ANY/SOME/ALL等用法自行摸索

子查询的优化

IN子查询的优化

将子查询物化后转换为内连接

当子查询结果为两个时,即select * from user where userid in (3,4);本质上等价于select *from user where userid = 3 or userid = 4;

这类等价如果在子查询结果特别大的时候会造成十分大的消耗,因此我们为子查询的结果集建立一个临时表

当然这仅限于子查询与外部查询不相关的时候。

  • 临时表会去重
  • 为其建立hash索引

如果结果集特别大,就会不大好都放入内存中,那我们就建立一个B+索引

物化:结果集->临时表,这个临时表我们也叫他为物化表。

对于 select * from user where userid in (select cuserid from orders where name = '盲盒');

我们可以理解为

select * from user inner join orders where user.userid = orders.cuserid and orders.name = '盲盒';

在将子查询转换为内连接后,mysql会根据成本选择一个表作为驱动表,具体怎么选,读者可以自己思考下,驱动表这部分在之前也讲过了。

将子查询转换为semi-join

待完善

文章末尾请带上以下文字及链接:本文正在参与「掘金小册免费学啦!」活动, 点击查看活动详情