数据库入门之学习《MySQL实战45讲》第16讲-第20讲笔记

最新推荐文章于 2024-07-11 10:26:16 发布

Geffin

最新推荐文章于 2024-07-11 10:26:16 发布

阅读量418

点赞数 2

本文链接：https://blog.csdn.net/Geffin/article/details/90728480

版权

数据库专栏收录该内容

12 篇文章 5 订阅

订阅专栏

《MySQL实战45讲》第16讲

sort_buffer

MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size。

如果排序的数据量小于sort_buffer_size，排序将会在内存中完成
如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后再将这些文件合并成一个大文件

全字段排序

通过索引将所需的字段全部读取到sort_buffer中
按照排序字段进行排序
将结果集返回给客户端

缺点

造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

优点
MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

rowid排序

通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
按照排序后的顺序，取id进行回表取出想要获取的数据
将结果集返回给客户端

优点
更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问

缺点
回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问

《MySQL实战45讲》第17讲

如何显示随机信息

mysql> select word from words order by rand() limit 3;

该方法比较简单，但是需要使用临时表，同时需要执行排序操作。

MySQL 的表是用什么方法来定位“一行数据”的

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法回表了？

其实不是的。如果你创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

这也就是排序模式里面，rowid 名字的来历。实际上它表示的是：每个引擎用来唯一标识数据行的信息。

对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
MEMORY 引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。

其实，order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

是不是所有的临时表都是内存表呢？其实不是的。tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是 InnoDB，当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

优先队列排序算法

与归并排序算法不同，该算法没有用到临时文件。归并排序算法会将所有列排好序，但是如果我们的业务只需要取出固定数量的最大（小）值，就不必使用这么大的计算量。优先队列排序算法会维护一个最大（小）堆，堆的元素数量为我们需要的数据数量。

随机排序方法

不论是使用哪种类型的临时表，order by rand()这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。

下面我们提供一种思路，假设要从一个表随机取3个数据，可以采用下面的算法

取得整个表的行数，记为 C；
根据随机方法得到 Y1、Y2、Y3；
再执行三个 limit Y, 1 语句得到三行数据。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； // 在应用代码里面取 Y1、Y2、Y3 值，拼出 SQL 后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

《MySQL实战45讲》第18讲

条件字段函数操作

如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定.(对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。)

隐式类型转换

MySQL 里的转换规则：在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

例如，假设tradeid的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

mysql> select * from tradelog where tradeid=110717;

对于优化器来说，这个语句相当于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

隐式字符编码转换

两个表的字符集不同，做表连接查询的时候可能用不上关联字段的索引。

当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先将一个字符集转化为另一个字符集然后再进行比较，实际上还是要用到字符集转换函数。

实际上，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。（“按数据长度增加的方向”进行转换）

《MySQL实战45讲》第19讲

为什么我只查一行的语句，也执行这么慢？

如果 MySQL 数据库本身就有很大的压力，导致数据库服务器 CPU 占用率很高或 ioutil（IO 利用率）很高，这种情况下所有语句的执行都有可能变慢，不属于我们今天的讨论范围。

表级锁

原因一等 MDL 锁：现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。解决方法：就是找到谁持有 MDL 写锁，然后把它 kill 掉。

原因二等 flush：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。
在这里插入图片描述

行锁

由于访问 id=1 这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。

在这里插入图片描述

索引

由于没有索引，只能走全局扫描

回滚

在这里插入图片描述
session B 执行完 100 万次 update 语句后，id=1 这一行处于什么状态呢？

在这里插入图片描述

session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。

带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。