为什么 SQL 语句不要过多的 join?

free命令缓存buff/cache

查看内存的命令:free 或者 top,其中使用free命令都可以看到信息

1
2
3
4
$ free -mh
total used free shared buff/cache available
Mem: 2.0G 516M 677M 23M 806M 1.2G
Swap: 0B 0B 0B
  • total 总内存
  • used 已用内存
  • free 空闲内存
  • buff/cache 已使用的缓存
  • avaiable 可用内存

清理已使用的缓存(buff/cache):

1
sync; echo 3 > /proc/sys/vm/drop_caches

但是注意在线上切勿执行这条命令,原因之一可以通过 MySQL 的 join指令说明

SQL Join

SQL中的join可以根据某些条件把指定的表给结合起来并将数据返回

join的方式有:5 种

  • inner join 内连接

MySQL-inner join 内连接

  • left join 左连接

MySQL-left join 左连接

  • right join 右连接

MySQL-right join 右连接

  • full join 全连接

MySQL-full join 全连接

在项目开发中如果需要使用join语句,如何优化提升性能?

  • 数据规模较小:可全部塞进内存
  • 数据规模较大:可以通过增加索引来优化join语句的执行速度,可以通过冗余信息来减少join的次数,尽量减少表连接的次数,一个SQL语句表连接的次数不要超过5次

join语句是相对比较耗费性能,因为在执行join语句的时候必然要有一个比较的过程,逐条比较两个表的语句是比较慢的,因此我们可以把两个表中数据依次读进一个内存块中,以MySQL的InnoDB引擎为例,使用语句show variables like '%buffer%'可以查到相关的内存区域

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
22 rows in set (0.00 sec)

其中join_buffer_size的大小将会影响我们join语句的执行性能

大部分数据库中的数据最终要保存到硬盘上,并且以文件的形式进行存储。以MySQL的InnoDB引擎为例:

  • InnoDB以页(page)为基本的IO单位,每个页的大小为16KB
  • InnoDB会为每个表创建用于存储数据的.ibd文件

这意味着我们有多少表要连接就需要读多少个文件,虽然可以利用索引,但还是免不了频繁的移动硬盘的磁头

也就是说频繁的移动磁头会影响性能,现在的开源框架都喜欢说自己通过顺序读写大大的提升了性能,比如hbase、kafka

那Linux有对此做出优化吗?再执行一次free命令看一下

1
2
3
4
$ free -m
total used free shared buff/cache available
Mem: 2.0G 610M 198M 23M 1.2G 1.1G
Swap: 0B 0B 0B

奇怪缓存怎么竟然占用1.2G多

  • buff/cache里面存的是什么?
  • 为什么buff/cache占了那么多内存,可用内存即availlable还有1.1G?
  • 为什么可以通过两条命令来清理buff/cache占用的内存,而想要释放used只能通过结束进程来实现?

这么随便就释放了buff/cache所占用的内存,说明它就不重要,清除它不会对系统的运行造成影响

想起来《CSAPP》(深入理解计算机系统)里面说过一句话

存储器层次结构的本质是,每一层存储设备都是较低一层设备的缓存

存储器层次结构

翻译成人话,就是说Linux会把内存当作是硬盘的高速缓存

相关资料:http://tldp.org/LDP/sag/html/buffer-cache.html

Join算法

面试官:如果让你来实现Join算法你会怎么做?

我:无索引的话,嵌套循环就完事了嗷。有索引的话,则可以利用索引来提升性能.

面试官:说回join_buffer 你认为join_buffer里面存储的是什么?

我:在扫描过程中,数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer

面试官:有索引的情况下是怎么处理的?

我:这个就比较简单了,直接读取两个表的索引树进行比较就完事了嗷,我这边介绍一下无索引的处理方式

(1)Nested Loop Join

1
2
3
4
5
6
7
8
9
10
sql = 'select * from outerTable left join innerTable on'
+ 'outerTable.id = innerTable.id'
for (outerRow in outerTable) {
for (innerRow in innerTable) {
if (compare(outerRow, innerRow)) {
resultRow combine(outerRow, innerRow)
sendToReplyBuffer(resultRow)
}
}
}

嵌套循环,每次只读取表中的一行数据,也就是说如果outerTable有10万行数据,innerTable有100行数据,需要读取10000000次(假设这两个表的文件没有被操作系统给缓存到内存,我们称之为冷数据表)

当然现在没啥数据库引擎使用这种算法(太慢了)

(2)Block Nested Loop

1
2
3
4
5
6
7
8
9
10
11
12
byte[] joinBuffer = new byte[joinBufferSize]

for (; fi11(joinBuffer, outerTable) != EOF;) {
for (innerRow in innerTable) {
for (outerRow in joinBuffer) {
if (compare(outerRow, innerRow) {
resultRow = combine(outerRow, innerRow)
sendToReplyBuffer(resultRow)
}
}
}
}

Block 块,也就是说每次都会取一块数据到内存以减少I/O的开销

当没有索引可以使用的时候,MySQL InnoDB 就会使用这种算法

考虑以下两个表 t_a 和t_b

1
2
3
4
5
6
7
8
CREATE TABLE `t_a` (
`id` int(11) NOT NULL,
`content` varchar(32) DEFAULT NULL,
`str_idx` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `t_a` ADD PRIMARY KEY (`id`), ADD KEY `str_idx` (`str_idx`);
ALTER TABLE `t_a` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
1
2
3
4
5
6
7
8
CREATE TABLE `t_b` (
`id` int(11) NOT NULL,
`content` varchar(32) DEFAULT NULL,
`str_idx` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `t_b` ADD PRIMARY KEY (`id`), ADD KEY `str_idx` (`str_idx`);
ALTER TABLE `t_b` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> desc t_a;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| content | varchar(32) | YES | | NULL | |
| str_idx | varchar(32) | YES | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc t_b;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| content | varchar(32) | YES | | NULL | |
| str_idx | varchar(32) | YES | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

当无法使用索引执行join操作的时候,InnoDB会自动使用 Block Nested Loop 算法

1
2
3
4
5
6
7
8
mysql> explain select * from t_b left join t_a on t_b.content = t_b.content;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t_b | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | SIMPLE | t_a | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

总结

上学时,数据库老师最喜欢考数据库范式,直到上班才学会一切以性能为准,能冗余就冗余,实在冗余不了的就join。如果join真的影响到性能,试着调大join_buffer_size,或者换固态硬盘。

参考资料:

转载至:https://blog.csdn.net/u013939918/article/details/106736557

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2021 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :