MYSQL相关知识
MySQL:
1.SQL 语句基本的执行链路?
客户端 -> Server(连接器 分析器 优化器 执行器) -> 存储引擎
2.redo log 和 binlog
redo log: 存储引擎层的日志。 InnoDB 引擎特有的一套日志系统, MylSAM没有哦。
Write-Ahead Logging策略,先写日志,再写磁盘, 确保异常重启数据不丢失。
记录某个数据页做了什么操作。
循环写。
binlog: Server层的日志
记录某个ID行做了什么操作。
追加写。
3.事务:一组操作。 4个特性,隔离级别,并发问题。
set autocommit=1
start transaction
xx
commit/rollback
隔离级别:可重复读, 一致性视图解决方案。 MylSAM引擎不支持。
#事务自动和手动提交
SHOW VARIABLES LIKE '%AUTOCOMMIT%'; -- ON 1, OFF 0
4.索引分类:
理解索引的模型 : 哈希, 有序数组, N叉树, 跳表, LSM树
为什么不用二叉树?
为什么不用B树?
为什么用B+树? B+ 树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数
主键索引,普通索引
回表 索引覆盖可规避回表
回表是指,InnoDB 在普通索引 a 上查到主键 id 的值后,再根据一个个主键 id 的值到主键索引上去查整行数据的过程。
覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。
页分裂,页合并
支持最左前缀
联合索引: MySQL5.6后出现的索引下推策略可减少回表次数 like走索引下推
5.锁设计: 处理并发问题。
范围分类: 全局锁, 表级锁, 行锁。
全局锁:锁住整个数据库, 只允许查询操作。 MylSAM引擎中全库备份的时候会用到。
表级锁: 两种锁。
表级锁一:表锁。 显式使用: lock tables ..read/write
表级锁二:元数据锁, MDL锁。 隐式使用: 访问表时系统默认会自动加上。 线程1读写表过程中, 线程2变更表结构,MDL锁会阻塞。
行数据的读写操作:自动加MDL读锁。
表结构的操作:自动加MDL写锁。
行级锁: 相对于表锁,有效的减少了锁冲突,提升业务并发度。 MylSAM引擎不支持。
两阶段锁协议 : 事务提交后,行锁才能释放。
死锁:
#开启死锁检测 ON
SHOW VARIABLES LIKE '%innodb_deadlock_detect%';
#锁等待时间
SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%';
死锁的预防: 控制并发度。 表的设计上进行优化: 比如一个账户设计成10个账户。
视图: MYSQL两种视图: 1. 虚拟表, 2.InnoDB引擎下MVCC的一致性视图。 支持读已提交和可重复读隔离级别下实现的。 事务期间定义:我看到哪些数据。
MVCC下快照工作原理:数据表中的每一行记录,其实可能有多个版本 (row),每个版本有自己的 row_trx_id,这个id是严格递增的。
MVCC下的读是事务一致性读。
MVCC下的写是读最新版本数据然后写操作。 更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”。
串:一致性读,当前读,行锁。
6.索引选择
普通索引和唯一索引
InnoDB 的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将整页读入内存。在 InnoDB 中,每个数据页的大小默认是 16KB。
查询过程:选择普通还是唯一性能几乎一样。
更新过程:普通索引性能好, 因为唯一索引还需要判断是否唯一。
change buffer: 减少读磁盘IO. 普通索引更新会用到change buffer, 而唯一索引更新无法用到,因为新增和更新前需要把数据页先读进内存。
redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。小结
树的理解:https://database.51cto.com/art/201911/605881.htm
磁盘包括磁道和扇区, 扇区是最小单元, 大小是512字节 , 由于扇区太小, 为了读取方便, 将相邻扇区组合成"块" , 大小4K 。 4K是磁盘得一次IO。
MYSQL一次会从磁盘读取B+树的一个节点到内存中。 故读取节点越少,IO的次数就越少,数据查询的效率就更快。
Spring和SpringMVC这两个框架,Spring是父容器,SpringMVC是其子容器,子容器可以看见父容器中的注册的Bean,反之就不行。
子容器可以访问父容器对象,而父容器不可以访问子容器对象。
明确边界,Spring根容器负责所有其他非controller的Bean的注册,而SpringMVC只负责controller相关的Bean的注册。
如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭 change buffer。
show VARIABLES LIKE '%innodb_change_buffer_max_size%';
SET GLOBAL innodb_change_buffer_max_size=0;
7. MySQL 选错了索引问题 为什么?怎么解决?
(1). 索引统计信息不准确, 解决: 【analyze table xx】
(2). 优化器误判的情况, 解决:应用端用 【select * from t_user force index(idx_name) where name='test'】 来强行指定某个索引 或者 增加或删除索引绕过该问题。
MySQL 优化器优化时会考虑的因素: 1.扫描行数 2.是否使用临时表 3. 是否需要排序
扫描行数是"采样统计"得到的,结果不精确。 修正办法是, 执行命令:【analyze table xx】 重新统计索引信息;
8. MYSQL 给字符串(手机号,邮箱)建立合理索引
(1). MySQL 是支持前缀索引的,也就是说,你可以定义字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。
【alter table t_user add index index1(email)】
【alter table t_user add index index2(email(6))】
使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。 区分度越高越好。
索引长度越长,占用磁盘空间越大,搜索效率越低。
(2). 倒序存储
【select field_list from t where id_card = reverse('input_id_card_string')】
9. MYSQL 数据库表的空间回收
InnoDB 的数据是按页存储的,如果我们删掉了一个数据页上的所有记录,会怎么样?答案是,整个数据页就可以被复用了。
区别记录的复用和数据页的服用。
delete 命令是不能回收表空间的。
删除会留下空洞, 插入出现页分裂也会留下空洞。如何收缩表空间从而去除空洞? 重建表。 新建表A, 将表B读出来插入表A, 删除表B。
区别重建表3种: optimize table、analyze table 和 alter table
analyze table : 只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
optimize table t 等于 recreate+analyze。
alter table: 就是recreate, 对表数据重建。
# 表大小统计SQL
SELECT
table_name AS '表名',
concat(truncate(data_length/1024/1024,2),' MB') AS '大小'
FROM information_schema.tables
WHERE
table_schema = 'ydzj_pd'
and table_name='t_hplc_family_import'
SELECT
table_name AS '表名',
concat(truncate(data_length/1024/1024,2),' MB') AS '大小'
FROM information_schema.tables
WHERE
table_schema = 'ydzj_pd'
group by TABLE_NAME
order by data_length desc
10. count(*)很慢问题
MyISAM 引擎把一个表的总行数存在了磁盘上,
InnoDB 引擎把数据一行一行地从引擎里面读出来,然后累积计数。
由于多版本并发控制(MVCC)的原因,InnoDB 表“应该返回多少行”也是不确定的。
解决办法:
基本思路:你需要自己找一个地方,把操作记录表的行数存起来。
11. order by 工作原理
sort_buffer_size,就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。
但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。
12. 我查这么多数据,会不会把数据库内存打爆?
select “结果集”存在哪里呢? MySQL 是“边读边发的”。
实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的:
(1)获取一行,写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的,默认是 16k。
(2)重复获取行,直到 net_buffer 写满,调用网络接口发出去。
(3)如果发送成功,就清空 net_buffer,然后继续取下一行,并写入 net_buffer。
(4)如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。
内存的数据页是在 Buffer Pool (BP) 中管理的,在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上,Buffer Pool 还有一个更重要的作用,就是加速查询。
Buffer Pool 对查询的加速效果,依赖于一个重要的指标,即:内存命中率。
【show engine innodb status】 结果中,查看一个系统当前的 BP 命中率。
InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法,这个算法的核心就是淘汰最久未使用的数据。
由于 InnoDB 对 LRU 算法做了改进,冷数据的全表扫描,对 Buffer Pool 的影响也能做到可控。
13. join可不可以用
【select * from t1 straight_join t2 on t1.a=t2.a】 t1 是驱动表,t2 是被驱动表。
在这个流程里:对驱动表 t1 做了全表扫描,这个过程需要扫描 100 行;
而对于每一行 R,根据 a 字段去表 t2 查找,走的是树搜索过程。由于我们构造的数据都是一一对应的,因此每次的搜索过程都只扫描一行,也是总共扫描 100 行;
所以,整个执行流程,总扫描行数是 200。
怎么选择驱动表?
使用 join 语句,性能比强行拆成多个单表执行 SQL 语句的性能要好;如果使用 join 语句的话,需要让小表做驱动表。
eg:
被驱动表的行数是 M。
每次在被驱动表查一行数据,要先搜索索引 a,再搜索主键索引。
每次搜索一棵树近似复杂度是以 2 为底的 M 的对数,记为 log2M,
所以在被驱动表上查一行的时间复杂度是 2*log2M。
在这个 join 语句执行过程中,驱动表是走全表扫描,而被驱动表是走树搜索。
驱动表的行数是 N,执行过程就要扫描驱动表 N 行,
然后对于每一行,到被驱动表上匹配一次。
因此整个执行过程,近似复杂度是 N + N*2*log2M。
N 对扫描行数的影响更大,因此应该让小表来做驱动表。
14. 临时表
【create temporary table】
区别 临时表 和 内存表?
临时表写数据的时候是写到磁盘上的。
内存表只在内存中。
sort buffer、内存临时表和 join buffer。
这三个数据结构都是用来存放语句执行过程中的中间数据,以辅助 SQL 语句的执行的。
其中,我们在排序的时候用到了 sort buffer,
在使用 join 语句的时候用到了 join buffer。
explatin 后 extra列有Using temporary,表示使用了临时表;
MySQL 什么时候会使用内部临时表?
1.如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果;
2.join_buffer 是无序数组,sort_buffer 是有序数组,临时表是二维表结构;
3.如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如我们的例子中,union 需要用到唯一索引约束, group by 还需要用到另外一个字段来存累积计数。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
2020-05-17 java.util.Queue源码
2019-05-17 git报错 git push origin master fatal: 'origin' does not appear to be a git repository fatal: Could not read from remote repository. Please make sure you have the correct access rights and the repositor