MySQL实战第三讲

最新推荐文章于 2023-07-03 22:09:00 发布

猴子会跳舞

最新推荐文章于 2023-07-03 22:09:00 发布

阅读量561

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_51809035/article/details/127089178

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

count（*）语句到底是怎么做的
计数的方法
- 用缓存系统保存计数
- 在数据库保存计数
不同count
order by怎么工作的
如何正确地显示随机消息
- 磁盘临时文件表
- 随机排序方法
为什么这些SQL语句逻辑相同，性能却差异巨大

count（*）语句到底是怎么做的

每种引擎实现方式不一样

MyISAM引擎是把记录总数记录在磁盘上，需要时直接返回，效率高
InnoDB引擎会一行行把数据读出来，然后累积计数

InnoDB引擎之所以不像MyISAM那样做，原因是多版本并发控制MVCC。
看下图，解释一下
在这里插入图片描述
三个会话查询到的行数都不相同，这和InnoDB事务设计有关。
InnoDB会判断每一条记录是否可见。
count（*）只会看到本事务开始之前提交的事务。

一点点优化：
我们知道主键索引树的叶子节点是数据，而普通索引树的叶子结点是主键值。所以普通索引树比主键索引树小很多，对于count（*）来说，遍历哪个索引树得到的结果逻辑是一样的，mysql优化器会找到最小的树来遍历，保证逻辑正确的情况下，尽量扫描少的数据量。

TABLE_ROWS 不准确
索引统计的值是通过采样来估算的，TABLE_ROWS 就是从这个采样估算得来的，因此它也很不准，官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用。

计数的方法

用缓存系统保存计数

mysql插入一条记录，redis里面+1
但是有以下两点问题

缓存系统可能会丢失更新，比如Redis重启导致值不对
即使 Redis 正常工作，这个值还是逻辑上不精确的。
第二个问题：
因为我们始终是插入数据和更新redis 两个步骤是分开的，
所以如果在这两步骤之间做这样的操作就会不准确：
查看总行数，并向mysql取最新记录，那么无论如何这两个操作一定有一个操作在逻辑上不准确。

在数据库保存计数

把这个计数直接放到数据库里单独的一张计数表 C 中
在这里插入图片描述

因为使用了redo log，所以不存像redis丢数据的情况。
因为会话1没有提交，所以对于会话2读取C时这条语句是不可见的。

这样逻辑上就是一样的了

不同count

count(*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能，有哪些差别
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

count()、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
count(列名)会过滤为null的情况，所以相同条件的查询下，count()的数量一定是大于等于count(列名)的。

分析性能差别的时候，你可以记住这么几个原则：

server 层要什么就给什么；
InnoDB 只给必要的值；
现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

count(主键)：InnoDB引擎会遍历整张表，把每一行的id都取出来。返回给server层。server层拿到id后，判断是不可能为空的，按行累加
count（1）：InnoDB遍历整张表，但不取值。server层对于返回的每一行，放一个1进去，判断是不可能为空的，按行累加

count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

count（字段）：
- 如果字段定义为 not null，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
- 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
count（*）：并不是把所有字段全部取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段) <count(主键id)<count(1) 约等于 count(*)

order by怎么工作的

假设要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。

select city,name,age from t where city='杭州' order by name limit 1000  ;

现在我们来看看这个语句是怎么执行的：

全字段排序

为了避免全表扫描，我们需要给 city 字段加上索引
然后 explain 命令看看这个sql的执行情况：
(Extra 这个字段中的“Using filesort”表示的就是需要排序)
在这里插入图片描述

关于Extra 中的字段时都是什么含义：
using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

msqyl会分配给每个线程一块内存用于排序，叫 sort_buffer
city字段上加的有索引，示意图：
在这里插入图片描述
语句执行流程：

初始化sort_buffer，确认我们需要放入到sort_buffer 中的三个字段是 city,name,age
拿第一个杭州（ID_X），拿到主键id，回表（去主键索引拿city,name,age这三个字段放到sort_buffer 中）
拿第二个杭州，然后和步骤2 一样，知道杭州都拿完
对sort_buffer 中的数据按照字段 name 做快速排序
按照排序结果取前1000 行返回给客户端

这个排序过程暂且叫：全字段排序
示意图：感觉图不是很好理解 😳
在这里插入图片描述
对于 name 排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。
如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

查看排序语句是否使用了临时文件：


/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

在这里插入图片描述
在filesort_summary中可以看到SQL查询结果的总行数rows、检查的行数examined_rows等信息。
其中number_of_tmp_files字段表明排序过程产生的临时文件个数，如果是0，说明排序在内存中进行，大于0则表示使用了外部排序。

注：

内存排序（优先队列 order by limit 返回少量行常用，提高排序效率，但是注意order by limit n,m 如果n过大可能会涉及到排序算法的切换）
内存排序（快速排序）
外部排序（归并排序）

number_of_tmp_files （磁盘临时文件）表示的是，排序过程中使用的临时文件数，MySql 将需要排序的数据分成12 份，每一份单独排序后存在这些临时文件中，然后把这12 个有序文件再合并成一个有序的大文件

如果需要排序的数据量小于 sort_buffer_size ，number_of_tmp_files 就是 0，说明排序中内存中就可以完成。
sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

示例表中有 4000 条满足 city='杭州’的记录，所以你可以看到 examined_rows=4000，表示参与排序的行数是 4000 行。

sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

同时，最后一个查询语句 select @b-@a 的返回结果是 4000，表示整个执行过程只扫描了 4000 行。

这里需要注意的是，为了避免对结论造成干扰，我把 internal_tmp_disk_storage_engine 设置成 MyISAM。否则，select @b-@a 的结果会显示为 4001。

这是因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1。

全字段排序的缺点：我们往sort_buffer 中放的值（city，name，age）太多了，这意味着内存中存储的行就少了，就更容易使用临时文件，更容易分成多个临时文件，导致性能变差。

rowid 排序

为了解决全字段排序的缺点，我们调整一个参数的大小：

SET max_length_for_sort_data = 16;

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

city、name、age 这三个字段的定义总长度是 36

（city 和 name 是varchar(16) ，英文：一个varchar占一个字节，一共32字节，age是int(11)， int无论后面是数字几，都占用4个字节），所以是36字节。
如果是按中文算的话utf-8 一个varchar 是3字节，就是（16+16）*3+4=100
这里按英文算的。

36大于我们设置的16，所以会换一中算法，新的执行流程：

初始化sort_buffer ，却放入两个字段 name 和 id。
从以city 为索引的索引树上找杭州，拿到主键ID_x
到主键索引找全部数据（回表），取name，和id两个字段，放入到sort_buffer 中
从city索引树上拿下一个杭州，直到city不是杭州为止
对sort_buffer 中的数据按照字段 name排序
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中（回表）取出 city、name 和 age 三个字段返回给客户端。

这就是 rowid 排序
这种算法中要回表两次
在这里插入图片描述
首先，图中的 examined_rows 的值还是 4000，表示用于排序的数据是 4000 行。但是 select @b-@a 这个语句的值变成 5000 了。
因为这时候除了排序过程外，在排序完成后，还要根据 id 去原表取值。由于语句是 limit 1000，因此会多读 1000 行。（增加的1000 来自第6 步的回表操作）在这里插入图片描述

OPTIMIZER_TRACE 的结果中，有两个信息也变了。

sort_mode 变成了<sort_key,rowid> ，表示参与排序的只有 name 和 id 这两个字段。
number_of_tmp_files 变成 10 了，是因为这时候参与排序的行数虽然仍然是 4000 行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。

全字段排序 VS rowid 排序

当内存足够大师，优先选择全字段排序，把需要的字段都放到 sort_biffer 中，这样排序后就会直接从内存中返回查询结果了。不用回表去取数据
当内存比较小时，为防止多个临时文件影响效率，才会采用rowid排序算法，这样一个排序就可以排序更多的行，但是要再回表去取数据。

体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

通过索引进行排序

联合索引（a，b），按字段a 顺序排列，当有多个字段a时，按字段b顺序排列。

之所以我们需要排序是因为，city字段的索引树下面的杭州是无序的，所以我们为什么不建立一个联合索引呢？

这样在city相同的情况下，name是有序的

一起来看：
创建一个 city 和 name 的联合索引

alter table t add index city_user(city, name);

在这里插入图片描述
查询流程：

从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
从索引 (city,name) 取下一个记录主键 id；重复上面回表的操作，知道查到第1000条记录，或者是不满足 city='杭州’条件时为止

在这里插入图片描述
Extra 字段中没有 Using filesort 了，也就是不需要排序了。而且由于 (city,name) 这个联合索引本身有序，所以这个查询也不用把 4000 行全都读一遍，只要找到满足条件的前 1000 条记录就可以退出了。也就是说，在我们这个例子里，只需要扫描 1000 次。

我们再来想一想现在联合索引的叶子结点上有 city，name，id，因为一个age就需要回表操作，回表操作消耗性能，所以我们可以用 索引覆盖解决这个问题。
覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

创建一个 city、name 和 age 的联合索引

alter table t add index city_user_age(city, name, age);

对于 city 字段的值相同的行来说，还是按照 name 字段的值递增排序的，此时的查询语句也就不再需要排序了

从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

在这里插入图片描述

Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。

如何正确地显示随机消息

我们来看一下这个语句是怎么执行的：

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

这个语句的意思是：随机排序取前 3 个

explain 命令：
在这里插入图片描述
Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。

因此这个Extra的意思是：需要临时表，并且需要在临时表上排序。

对于 InnoDB 表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。

这条语句的执行流程是这样的：

创建一个临时表。表里有两个字段，第一个字段是 double 类型，字段 R（用来放rand随机数），第二个字段放word，记为字段 W。这个表没有建索引。
从 words 表中，按主键顺序取出 word 值。调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
从内存临时表中取出 R 值和位置信息，存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

简单点来说：
每个word对应一个rand（临时表中，word，rand），按rand排序（sort_buffer，rand，pos），取前三个word（根据pos去临时表中取word）

我们可以通过慢查询日志（slow log）验证扫描行数是否正确

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

在这里插入图片描述
pos是位置信息

首先我们先搞清楚一个事情，如果有主键，那么每一行就有一个唯一标识，如果没有主键，每一行就会有一个隐藏主键rowid，这个隐藏主键就是唯一标识。

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

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

磁盘临时文件表

sort_buffer_size：sort_buffer 的大小，
number_of_tmp_files：临时文件的个数
（sort_buffer_size越小，number_of_tmp_files越大）
.
max_length_for_sort_data：如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法，换成rowid排序

tmp_table_size ：配置限制了内存临时表的大小，默认值是 16M

如果临时表大小超过这个限制，就会转成 磁盘临时表
为了使用磁盘临时表，我们需要把 tmp_table_size 设置成 1024，把 sort_buffer_size 设置成 32768, 把 max_length_for_sort_data 设置成 16

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

在这里插入图片描述
R 字段存放的随机值就 8 个字节，rowid 是 6 个字节，总行数是 10000，这样算出来就有 140000 字节，超过了 sort_buffer_size 定义的 32768 字节了，但number_of_tmp_files 的值居然是 0 ！！！

filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。

所以没有使用到临时文件（没有使用归并算法）
这是因为 mysql使用了一个新的排序算法：优先队列排序算法

在这里插入图片描述
归并排序的缺点，把10000条数据都排好了，但是我们只需要前3个，后面的9997都浪费了，所以我们使用优先队列排序
执行流程：

对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；
（对数据结构印象模糊的同学，可以先设想成这是一个由三个元素组成的数组）取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；
重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。

这样堆里就是最小的3条记录了

这个流程结束后，我们构造的堆里面，就是这个 10000 行里面 R 值最小的三行。然后，依次把它们的 rowid 取出来，去临时表里面拿到 word 字段，这个过程就跟上一篇文章的 rowid 排序的过程一样了。

再看另一条 sql语句：

select city,name,age from t where city='杭州' order by name limit 1000  ;

这个语句就没有使用优先队列，因为limit 是 1000，也就是说对的大小就是1000 行的name，rowid，超过 sort_buffer_size 大小，所以只能使用归并排序。

随机排序方法

怎么随机取记录呢？

取得整个表的行数，并记为 C。
取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
再用 limit Y,1 取得一行。

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行，执行代价比随机算法 1 的代价要高。

这个算法相比 order by rand() 比起来，执行代价还是小很多的。

这样取三条记录就可以这样写了：

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；

这个的扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1)
其实我们还可以继续优化：

分页查询优化方式：

假设Y1，Y2，Y3是由小到大的三个数，则可以优化成这样，这样扫描行数为Y3
id1 = select * from t limit @Y1，1；
id2= select * from t where id > id1 limit @Y2-@Y1，1；
select * from t where id > id2 limit @Y3 - @Y2，1；

这样我们就只需要扫描 C+Y3+3 条记录了。

这样我们就可以通过主键避免全表扫描

为什么这些SQL语句逻辑相同，性能却差异巨大

如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。

条件字段函数操作

这个语句会很慢

mysql> select count(*) from tradelog where month(t_modified)=7;

（如果t_modified上有索引的话）这个语句会很快

mysql> select count(*) from tradelog where t_modified=‘2022-1-1’;

B+ 树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。

也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。
其实还是可能用t_modified这个索引的，只要t_modified 比主键索引小，拿还是会用t_modified来进行遍历操作。

隐式类型转换

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

下面这个就会全表扫描，优化器会放弃走树搜索功能
tradeid 定义是 varchar（32），是字符串

mysql> select * from tradelog where tradeid=110717;

下面这个就会走索引

select * from tradelog where id="83126";

隐式字符编码转换

utf8mb4 是 utf-8 的超类，“按数据长度增加的方向”进行转换的。
MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。