如果你还记得之前说过的mysql基于成本选择索引的话,那就不可避免地想到,实际的IO成本和以及回表所需的成本是和计算规则严重不符的,换句话说,当我们确认自己足够了解数据的前提下,我们指定索引的查询效率是高于系统为我们提供的选择的。那么如何指定索引呢?
自力更生!我的查询我做主
强制选择索引
SELECT *
FROM table_name
FORCE INDEX (`index_name`)
WHERE
指定索引
USE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
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 = '软件学院';
结果
那么 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的学生的科目成绩及学生信息),这类条件我们大概可以分为两类:
- 只和单表有关系的条件:
t1.m1 > 1
- 和两个表都有关系的条件:
t1.m1 = t2.m2
、t1.n1 > t2.n2
-
驱动表: 首先被查询的表,并通过这个表去查询另一个表
-
被驱动表: 被驱动表查询的表叫做被驱动表
-
内连接: 只输出匹配条件行的表 +外连接: 匹不匹配都要输出
- 左外连接:左边的表为驱动表
- 右外连接
这个匹配指的是:驱动表的记录 在被驱动表中被匹配,因此左外连接和右外连接搜索输出行数是会有区别的
对于这类”区别“,我们可能不想让她们出现,也可能想让她们出现,因此为了正确的表示我们的想法,我们将过滤条件分为了 where 和 on
嵌套循环连接(Nested-Loop Join)
就是一次for循环,
- 先找到驱动表的符合条件的数据行,
- 再根据每个数据行去被驱动表中查询
第二步每次都是全表查询的话可能会过于慢,因此考虑用索引
基于块的嵌套循环连接(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的条件(范围)
听起来很难理解,不妨
一次查询的结果可能是许多行,也可能是一行,也可能是一个数, 因此我们有
//子查询是一个数字
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
待完善
文章末尾请带上以下文字及链接:本文正在参与「掘金小册免费学啦!」活动, 点击查看活动详情