首页 技术 正文
技术 2022年11月19日
0 收藏 504 点赞 3,465 浏览 3274 个字

目录

笔记做的不好,因为还是又不少地方没有能够理解。见谅,后面理解了在更新…………

16 | “order by”是怎么工作的?

场景:

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` int(11) 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;select city,name,age from t where city='杭州' order by name limit 1000 ;

全字段排序

Extra 这个字段中的“Using filesort”表示的就是需要排序,MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。

图中“按 name 排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数 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 = 'Inndb'/* 执行语句 */
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'/* 计算 Innodb_rows_read 差值 */
select @b-@anumber_of_tmp_files 表示的是,排序过程中使用的临时文件数。

MySQL会将需要排序的文件分割成多份,小份单独排序后合一。(sort_buffer_size 越小,需要分成的份数越多,

number_of_tmp_files 的值就越大。)

rowid 排序

MySQL 认为排序的单行长度太大 会使用 这个参数 max_length_for_sort_data 来提示MySQL要换一个算法。

过程:因为多了一次索引,排序使用的字段少了,所以数据量就下来了,相应的所需要的临时文件也变少了。

17 | 如何正确地显示随机消息?

场景:

英语学习App  每天  根据用户的级别推荐  随机的单词随着单词表的变大,这个操作越来越慢mysql> CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

内存临时表

order by rand() 来实现这个逻辑。

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

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

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

这也就是排序模式里面,rowid 名字的来历。

实际上它表示的是:每个引擎用来唯一标识数据行的信息。

  • 对于有主键的 InnoDB 表来说,这个 rowid 就是主键 ID;
  • 对于没有主键的 InnoDB 表来说,这个 rowid 就是由系统生成的;
  • MEMORY 引擎不是索引组织表。

在这个例子里面,你可以认为它就是一个数组。因此,这个 rowid 其实就是数组的下标。

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

磁盘临时表

不是所有的临时表都是内存表。

tmp_table_size 这个配置限制了内存临时表的大小,默认值是 16M。如果临时表大小超过了 tmp_table_size,那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是 InnoDB,是由参数 internal_tmp_disk_storage_engine 控制的。

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

MySQL 5.6 版本引入的一个新的排序算法,即:优先队列排序算法

但是如果超过了我设置的 sort_buffer_size 大小,所以只能使用归并排序算法,因为要维护的堆太大了。

随机排序方法

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;

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

在 MySQL 中,有很多看上去逻辑相同,但性能却差异巨大的 SQL 语句。对这些语句使用不当的话,就会不经意间导致整个数据库的压力变大。

案例一:条件字段函数操作

更新中…………

小结

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

MySQL 的优化器确实有“偷懒”的嫌疑,即使简单地把 where id+1=1000 改写成 whereid=1000-1 就能够用上索引快速查找,也不会主动做这个语句重写。

因此,每次你的业务代码升级时,把可能出现的、新的 SQL 语句 explain 一下,是一个很好的习惯。

19 | 为什么我只查一行的语句,也执行这么慢?

场景:

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;在里面插入了 10 万行记录

第一类:查询长时间不返回

mysql> select * from t where id=1;

一般碰到这种情况的话,大概率是表 t 被锁住了。

接下来分析原因的时候,一般都是首先执行一下 show processlist 命令,看看当前语句处于什么状态。

然后我们再针对每种状态,去分析它们产生的原因、如何复现,以及如何处理。

  • 等 MDL 锁

  • 等 flush

  • 等行锁

第二类:查询慢

20 | 幻读是什么,幻读有什么问题?

https://www.cnblogs.com/zwtblog/p/15134977.html

https://www.cnblogs.com/zwtblog/p/15132201.html#基本语法

幻读

在同一个事务内,读取到了别人插入的数据,导致前后读出来结果不一致

如何解决幻读?

现在你知道了,产生幻读的原因是,行锁只能锁住行,

但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (GapLock)。

顾名思义,间隙锁,锁的就是两个值之间的空隙。

间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的。

相关推荐
python开发_常用的python模块及安装方法
adodb:我们领导推荐的数据库连接组件bsddb3:BerkeleyDB的连接组件Cheetah-1.0:我比较喜欢这个版本的cheeta…
日期:2022-11-24 点赞:878 阅读:9,474
Educational Codeforces Round 11 C. Hard Process 二分
C. Hard Process题目连接:http://www.codeforces.com/contest/660/problem/CDes…
日期:2022-11-24 点赞:807 阅读:5,889
下载Ubuntn 17.04 内核源代码
zengkefu@server1:/usr/src$ uname -aLinux server1 4.10.0-19-generic #21…
日期:2022-11-24 点赞:569 阅读:6,724
可用Active Desktop Calendar V7.86 注册码序列号
可用Active Desktop Calendar V7.86 注册码序列号Name: www.greendown.cn Code: &nb…
日期:2022-11-24 点赞:733 阅读:6,479
Android调用系统相机、自定义相机、处理大图片
Android调用系统相机和自定义相机实例本博文主要是介绍了android上使用相机进行拍照并显示的两种方式,并且由于涉及到要把拍到的照片显…
日期:2022-11-24 点赞:512 阅读:8,118
Struts的使用
一、Struts2的获取  Struts的官方网站为:http://struts.apache.org/  下载完Struts2的jar包,…
日期:2022-11-24 点赞:671 阅读:5,278