H5W3
当前位置:H5W3 > mysql > 正文

《MySQL深入研究》学习总结

前言

接上文,继续学习后续章节。细心的同学已经发现,我整理的并不一定是作者讲的内容,更多是结合自己的理解,加以阐述,所以建议结合原文一起理解。

第13章《为什么表数据删除一般,表文件大小不变?》

我们在知道MySQL表的存储,在8.0之前的版本,表结构相关数据存在.frm文件中,表数据存在.ibd文件中。可以通过innoDB_file_per_table控制,OFF表示表数据页放在系统共享表空间,这时候删除表数据,文件大小也不会变。

但即使,表数据单独存在.ibd文件中,当我们通过delete删除时,发现文件大小也不会变。

这是因为表数据在B+结构中,当我们删除一条数据时,并不会真把这个数据给彻底删除,只是在这个位置上做个删除的标记。被标记删除的位置,可以被复用,等待下次有数据插入时,可以保存在该位置,但实际文件大小不会表的。不仅删除,插入也是如此,当插入时,出现也分裂,就可能出现空洞。也就是说一个表经过大量的增删改,就会出现大量的空洞。所以我们会发现但我们删除表数据,文件大小也不会表。

那么如何解决这个问题,把表压缩下呢?

可以通过:重建表

这里,你可以使用alter table A engine=innoDB命令来重建表。

在5.5版本版本之前,这个命令做了如下几个操作:

1、新建一个与表A一样结构的表B。

2、将表A的数据按ID自增的顺序写入表B。

但是在这个过程中,增删改是会丢失的。

所以在5.6版本之后,引入了Online DDL。

1、新增一个临时文件,扫描A主键的所有数据页。

2、根据数据页中表A的记录生成B+树,存储到临时文件。

3、在生成过程中,将对A的所有更新操作,记录在row log 文件中。

4、临时文件生成完毕后,将row log中的数据,维护到临时文件中,然后用临时文件替代表A的数据文件。

第14章 《count(*)特别慢,怎么办?》

当一张数据量很大的表,比如一张1千万数据量的表,做count(*)时,很慢这是什么原因呢?

首先我们要从存储引擎区分,在MyISAM中,count(*)的结果记录在文件中,所以会直接返回。

而innoDB中,count(*)时,需要一行一行扫描统计行数。所以当数据量很大时,就会变的很慢。

那为什么innoDB不跟MyISAM一样呢,这是因为MyISAM是不支持事务的。而在innoDB中,同一时刻,由于可重复读的隔离级别特性,不同事务做统计查询,查询的结果可能是不一样的。

所以不能直接用一个统计给所有事务通用。

那业务上我们的确需要统计全表的数量,怎么办?

1、把count(*)的数据保存在Redis中,每当插入删除时,更新缓存,但这不适用于带where条件的查询。

2、把count(*)的数值存在mysql表中。

优缺点:

如果把统计数据放在Redis,在单机器下,宕机就没法用了,虽然可以通过集群来达到高可用。当数据库的操作与Redis的操作,存在数据一致性的问题。比如既要返回count数据,又要返回最新的100条数据,就可能出现最新的数据不一定在count统计中,或者统计了最新的数据,但并不在表中,想一想这是为什么?

所以建议,如果对count的实时性不是特别高的时候,可以使用该方案。

正因为有Redis和MySQL没法同时支持分布式事务,如果把count数据存在MySQL表中,通过事务就可以解决这样的问题,但显然两次表操作也是缺点所在。

count(*) 、count(1) 、count(id)、 count(字段)不同方式有什么区别?

count(*)和count(1)都是只统计行数,不返回数据,性能最优,MySQL官方也说明了两者本质没有区别。

count(id)扫描所有id,再统计行数,多了id字段的返回,索引性能比count(*)和count(1)略差.

count(字段)扫描该字段并返回,如果该字段允许为null.就得统计不为null的数据,所以比count(id)性能差。

第16章《order by是如何工作的?》

背景条件:

有这样一张表

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
--执行这样的一个查询语句,MySQL是怎么工作的呢?
select city,name,age from t where city='杭州' order by name limit 1000;

1、首先根据辅助索引查询出city=”杭州”的数据。

2、发现第一个city=“杭州”数据时,得到对应的id,再根据id去主键索引中查出该行的完整数据。

3、查询得到完整数据后,取city,name,age三个字段,放入sort buffer中。

4、重复上述动作,查询出索引city=“杭州”的数据。

5、将sort buffer中的数据,按name排序,取出前1000条并返回。查询结果。

这个称为全字段排序

几个概念:

sort buffer:MySQL为了排序的高效,为每个线程会在内存中开辟一块内存,专门用于排序。

sort buffer size:决定了排序缓冲区的大小,超过排序缓冲区的数据,便只能使用额外的文件用于排序。

max_length_for_sort_data:限定用于排序时,单行数据的大小,超过这个大小的行,就会使用另外一个排序方法,如下。

几个疑问:

如果单行的数据很大,sort buffer中可存放的数据就少了,那么就要分成很多临时文件(归并排序),排序的性能就会很低,怎么办?

MySQL发现当行数据的大小超过设置的max_length_for_sort_data时,就会采用另外一种算法,整个排序过程如下:

1、首先根据辅助索引查询出city=”杭州”的数据。

2、发现第一个city=“杭州”数据时,得到对应的id,再根据id去主键索引中查出该行的完整数据。

3、查询得到完整数据后,取id,name两个字段,放入sort buffer中。

4、重复上述动作,查询出索引city=“杭州”的数据。

5、将sort buffer中的数据,按name排序,取出前1000条。

6、再根据sort buffer中name与id的对应关系,根据id再回表遍历查询出整行数据,取name,city,age三个字段返回给客户端。

我们会发现比第一种方法,多了一次回表查询。这个成为rowid排序

第18章《为什么这些SQL语句逻辑相同,性能却差异巨大?》

主要讲了三个场景,其实SQL优化的核心就是,能不能用到索引,能不能减少回表查询,能不能使用到覆盖索引,本质就是以空间换时间。

场景一:条件字段做函数操作

当一个查询语句的where条件字段做了函数操作,是无法走索引的,比如where id +1 =2;

本质上就是因为MySQL无法判断该字段函数操作后不再有序了,只能全索引扫描。

场景二:隐式类型转换

就是类型转换,比如原本字段是varchar,缺没有带“”查询,就会针对这个字段做类型转换函数,MySQL发现字符类型和数值类型比较时,会把字符串转换成数值。

显然当一个查询语句的where条件字段做了函数操作,是无法走索引的。

场景三:隐式字符编码转换

当两个表关联查询,关联条件的两个字段,字符集编码不一致时,也需要进行函数转换,同样的也不会走索引。

本文地址:H5W3 » 《MySQL深入研究》学习总结

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址