Home
Posts
Categories
Tags
About
MySQL
发布于: 2024-7-17   更新于: 2024-7-17   收录于: 数据库
文章字数: 308   阅读时间: 2 分钟   阅读量:

1.1 SQL语句执行流程

  • 先通过连接器校验权限
  • 利用分析器进行SQL语句的词法分析和语法饭呢西，构建解析树
  • 使用优化器选择合适的索引和表连接顺序，最终选择一个最佳的执行计划
  • 利用执行器，调用引擎层查询数据，返回结果集中给客户端

1.2 MYSQL的存储引擎

  • InnoDB:是mysql默认的存储引擎，支持事务，表级锁和力度更小的行级锁，具有事务提交，回滚和数据崩溃恢复的功能
    • changebuffer:是Innodb存储引擎中的一个机制，用于暂存对二级索引的插入和更新操作的变更，而不立即去执行，当合适条件时，再写入到二级索引中，
    • changebuffer：提高写入性能，减少对磁盘的频繁写入，批量操作
  • MyISAM:是之前Mysql的默认引擎，不支持事务和行级锁，支持表级锁，锁的粒度较大，更新性能较差，更适合读多写少的场景
  • Memory:相较于InnoDB和MyISAM,Memory是存在内存中的，速度更快，但是不具备持久化能力，适合临时存储

1.3 数据排序

1.3.1索引排序

1.3.1.1 索引的数据结构：

B+树（又可分为聚簇索引和非聚簇索引）： B+树（所有实际数据（值））都存在叶子节点，所有叶子节点又通过链表连接内部节点不存储数据，用于存储指向子节点的指针和索引信息（关键字（它们通常对应于数据库表中某些字段的值））

  • 定义：聚簇索引是一种将表中的数据行按索引的顺序存储的索引。数据的物理存储顺序与索引顺序相同。

  • 特点：

    • 每个表只能有一个聚簇索引，因为数据只能按一种顺序存放。
    • 主要用于主键（Primary Key）。
    • 查询速度快，因为可以直接访问存储的数据。

  • 例子：在一个员工表中，使用员工ID作为聚簇索引，员工记录将按照员工ID的顺序存储。

  • 定义：非聚簇索引是一种将索引结构与表中的数据分开的索引。索引存储的是指向数据行的指针，而不是数据本身。

  • 特点：

    • 一个表可以有多个非聚簇索引，用于不同的列。
    • 查询时需要先查找非聚簇索引，再通过指针找到实际的数据，速度相对较慢。

  • 例子：在同一个员工表中，如果为姓氏创建非聚簇索引，索引将存储姓氏的值和对应员工ID的指针。

哈希，倒排，R-树

1.3.1.2联合索引

联合索引（Composite Index）是数据库中的一种索引类型，它由多个列组成。与单列索引不同，联合索引可以提高对多个列的查询性能，特别是在涉及到这些列的组合条件时。

  • 特点
  1. 由多个列组成：联合索引可以包含两个或更多的列，这使得它适用于复杂查询。
  2. 顺序敏感：联合索引中列的顺序非常重要。在创建索引时，最左边的列会影响索引的使用方式。
  3. 提高查询性能：联合索引可以加速对涉及多个列的查询，例如，使用WHERE条件的查询、ORDER BY排序等。
1.3.1.2.1 最左前缀匹配原则
1.3.1.2.2 覆盖索引
1.3.1.2.3 索引下推

应用在联合索引上

1.3.2 文件排序

  • sort_buffer

  • 内存排序

    • 单路排序
    • 双路排序（涉及到回表）
      • 回表：回表指使用二级索引（非聚簇索引）作为条件进行查询时，由于二级索引只存储了索引字段的值和对应的主键值，无法得到其他数据，如果要查询其他数据，需要根据主键去聚簇索引查找实际的数据行，这个过程称为回表

  • （数据量大时）使用磁盘文件进行外部排序（分块排序）和归并排序

1.4 语句

1.4.1 Explain

  1. 基本功能
  • 执行计划：EXPLAIN 语句提供关于查询的执行计划的信息，包括如何访问表、使用了哪些索引、连接类型、估计的行数等。
  • 查询优化：通过分析执行计划，可以识别出潜在的性能瓶颈，比如全表扫描、不适用的索引等，从而对查询进行优化。

1.4.1.2 所有参数

  • id：每个 SELECT 的唯一标识符，用于区分查询块。

  • select_type：表示 SELECT 的类型，例如：

    • SIMPLE：简单查询。
    • PRIMARY：最外层 SELECT。
    • UNION：UNION 查询中的后续 SELECT。
    • SUBQUERY：子查询。

  • table：查询中涉及的表名。

  • type：连接类型或访问方法，常见类型有：

    • ALL：全表扫描。
    • index：索引扫描。
    • range：范围扫描。
    • ref：非唯一性查找。

  • possible_keys：查询可能使用的索引列表。

  • key：实际使用的索引。

  • key_len：使用的索引的长度（字节数）。

  • ref：与索引值一起使用的列或常量。

  • rows：估计读取的行数。

  • Extra：额外的信息，提供查询执行的更多细节，例如是否使用了临时表。

1.4.1.2 三个重要参数

type:使用索引时为index或range,ALL为全表扫描 key：显示使用的索引，没有则为Null rows：查询扫描的行数

1.4.2 count

count(*)和count(1)都会统计表中所有行的数量，包括null值，不需要对具体列进行处理，性能高 count(字段名)会统计指定字段值不为null的行数 MyISAM因为只有表锁，对表的修改是串行，所以能够维护总数，所以count(*)很快，相当于返回一个字段值（如果是并发执行，两个事务同时更新数据可能会导致总数计算错误）

1.4.3 Int

int(11)中的11表示显示宽度，并不影响存储的大小或数值范围；当使用zerofill属性时，位数不够时会在前面填充零

1.4.4 varchar，char

char是固定长度，varchar是可变长度（额外增加1到2个字节来记录字符串长度，但实际上这种运算微乎其微）

1.4.5 delete、drop、truncate

delete删除行数据，但保留表结构和相关的对象，会生成日志文件可以回滚 drop完全删除数据库表，包括数据和结构，将相关.ibd和.frm文件删除，表空间直接回收，无法回滚 truncate 只删除数据，不删除表结构和索引等其他结构:将整张表的数据删除，无法回滚 性能 drop>truncate>delete

1.4.6 exist,in

exist子查询返回布尔值，用来判断是否返回任意行 in子查询返回一个值列表然后进行匹配，处理较大数据集时性能较差

1.5 事务

  • 事务主要通过锁、redoLog、Undo Log、MVCC来实现事务

1.5.1 锁

MySQL利用锁(行锁、间隙锁等)机制，使用数据并发修改的控制，满足事务的隔离性

  • 锁主要使用（test and set）（确定此时是否被占用）保证了互斥性，如排他锁(compare and swap)（确定此时是否是正确的时机）保证了原子性，如共享锁

1.5.1.1 锁类型

  • 行级锁
  • 表级锁
  • 意向锁
  • 共享锁
  • 排他锁
  • 元数据锁
  • 间隙锁
  • 临键锁
  • 插入意向锁
  • 自增锁

1.5.1.2 乐观锁，悲观锁

  • 悲观锁：假设会发生冲突，也因此在操作数据之前就加锁（可以通过一些语句加锁）
  • 乐观锁：乐观锁是一种假设在并发环境中不会发生冲突的机制。它在对数据进行修改时不加锁，而是在提交事务时进行检查，以确保数据的完整性 （通过版本号和时间戳来实现，不加锁）

1.5.2 日志

1.5.2.1 Redo Log(重做日志)

（记录物理结构变化） 它会记录事务对数据库的所有修改，在崩溃时恢复未提交的更改(即未提交的更改不对数据库产生影响)，用来满足事务的持久性

1.5.2.2 Undo Log(回滚日志)

（主要记录逻辑操作） 它会记录事务的反向操作，简单来说就是保存数据的历史版本，用于事务的回滚，使的事务执行失败之后可以恢复之前的样子，实现原子性和隔离性

1.5.2.3 binlog

（记录逻辑操作的变化）

  1. 数据恢复：通过 binlog，可以在数据库故障后恢复数据。
  2. 主从复制：主服务器记录变更，从服务器根据 binlog 实现数据同步。
  3. 审计和分析：用于跟踪数据库的变更历史。
  • Binlog 的格式：
  1. Statement：记录 SQL 语句。
  2. Row：记录数据行的变化。
  3. Mixed：两者结合。

Binlog 主要用于恢复数据和复制，不同于 Redo log（用于事务持久性）和 Undo log（用于回滚操作）。 Binlog 记录的是已提交的事务操作，而不涉及未提交的事务。

1.5.3 MVCC(多版本并发控制)

满足了非锁定读（通常指事务中执行的SELET查询）的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性

  • MVCC是一种并发控制机制，允许多个事务同时读取和写入数据库，而无需互相等待，从而提高数据库的并发性能
  • 在MVCC中，数据库会为每个事务创建一个数据快照，每当数据被修改时，MYSQL不会立即覆盖原有数据，而是生成新版本的记录，每个记录都保留了对应的版本号和时间戳（通常是使用undolog实现的，需要旧版本，就将相应的旧版本加载到内存中，而不用修改）
  • 多个版本串联起来就形成了一条版本链，这样不同时刻启动的事务可以无锁的获得不同版本的数据（普通读），此时读（普通读操作不会阻塞）

1.5.4 问题

  • 长事务：阻塞资源、死锁风险、主从延迟、回滚导致时间浪费

  • 脏读:
    本质是：一个事务读取了另一个事务尚未提交的数据，随后如果该事务回滚，读取的数据可能会失效，导致数据不一致。（可以对更新操作枷锁解决）

  • 不可重复读:
    本质是：在同一事务中，多次查询同一条记录，发现后续查询的结果与前一次查询不一致，通常是因为其他事务更新了该记录。通常发生了UPDATE（对查询行加锁解决）

  • 幻读:
    本质是：在同一事务中，多次查询的结果不一致，通常是因为其他事务在并发插入或删除数据时导致的。(INSERT或DELETE)

  • 发生死锁了怎么解决:

    1. 自动检测与回滚：MYSQL自带死锁检测机制（innodb_deadlock_detect）当检测到死锁时，数据库会自动回滚其中一个事务，以解除死锁、或者有等待超时的参数（innodb_lock_wait_timeout）:当获取当前锁的等待时间超出阈值时，就释放锁进行回滚。
    2. 手动kill发生死锁的语句
    SHOW ENGINE INNODB STATUS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
KILL <thread_id>;

  • 单点故障： 一般使用主从架构来避免单点故障

1.5.5 事务隔离级别

  • 读未提交（最低级别）（容易发生脏读，不可重复，幻读）
  • 读已提交（容易发生不可重复读问题，可能发生幻读）（对更新操作枷锁）（也有行级锁，不过时间较短，可以避免脏读）
  • 可重复读(MySQL默认的隔离级别)（可能引发幻读问题）（数据库会锁定已被查询的行）（还有间隙锁和临键锁（行级锁和间隙锁的结合）避免幻读）
  • 串行化（最高级别，降低并发性能）

1.6 架构

1.6.1 主备架构

主机和备机，备机不对外提供服务，只是同步主机数据，当主机出现故障则取而代之

1.6.2 主从架构

写请求指派到主机，读请求指派到从机，（一般而言主从就是读写分离）

  • 读写分离：
  1. 使用代码封装
  2. 使用中间件

1.6.2.1 主从同步机制

  • 一种数据复制技术，用于将主数据库的数据同步到一个或多个从数据库中，主要通过binlog实现数据的复制
  • 主从复制类型：异步复制（主库不需要等待从库的响应）（Mysql默认）、同步复制、版同步复制

1.6.2.2 主从同步延迟

延迟是必然存在的，无论怎么优化都无法避免，只能减少

  • 常见解决办法：
  1. 二次查询
  2. 强制将写之后立马读的操作转移到主库
  3. 关键业务读写都走主库
  4. 使用缓存

1.6.3 主主架构

两个都是主机，一般情况下不会有主主架构（当两条写请求同时打到两个主库，他们的id是一样的，当数据同步时就会一条被覆盖）

1.7 分库分表

1.8 缓存

  1. 查询缓存(MYSQL8.0已废除)（需要满足条件：同一个查询SQL，表没有发生变化）（条件太苛刻，所以后续废除了）
  2. InnoDB缓冲池（buffer_pool）：InnoDB存储引擎的核心缓存组件，缓冲池缓存了数据页，索引页和其他相关信息（一页默认是16kb） ![[Pasted image 20241016191410.png]]

1.8.1 Doublewrite Buffer

  • 写入 Doublewrite Buffer：当有数据需要从内存缓冲池写入磁盘时，InnoDB 首先会将数据页写入一个专门的内存区域，称为 Doublewrite Buffer。这个区域大约有 2MB 大小，能够容纳 128 个 16KB 的数据页。

  • 将 Doublewrite Buffer 刷入磁盘的共享区域：当 Doublewrite Buffer 被填满或达到某个触发条件后，InnoDB 会将这些数据页一次性写入磁盘的一个共享区域（在 InnoDB 的表空间文件中）。这一步写入是顺序写入，非常高效。

  • 写入数据页到最终位置：接着，InnoDB 将这些已经写入到共享区域的页，再次写入到其在数据库表空间中的实际位置（即它们应该存储的地方）。

  • 恢复机制：如果在这两个步骤之间出现系统崩溃，数据库可以通过检查 Doublewrite Buffer 的共享区域来恢复那些损坏的页，确保数据一致性。

1.8.2 Log Buffer

暂存redo Log 批量写入操作将日志写入磁盘，降低I/O操作的频率

1.9 数据库三大范式

  • 第一范式（1NF）：

    • 要求每个表中的字段值是原子的（即不可再分），每个列都应该包含单一值，且每行都是唯一的。

  • 第二范式（2NF）：

    • 在满足 1NF 的基础上，要求表中的每个非主键字段完全依赖于主键，而不是部分依赖。也就是说，非主键字段不能依赖于主键的一部分。

  • 第三范式（3NF）：

    • 在满足 2NF 的基础上，要求非主键字段之间不能有传递依赖。即一个非主键字段不能依赖于另一个非主键字段。
MySQL
数据库
事务
锁
Redis
NoDrop
Be Water, My Friend.

文章

4

分类

3

标签

9
目录
相关文章
Redis
2.1 Redis的应用场景 缓存：用作缓存层，减少数据库负载，提高数据读取速度 实时系统：redis支持快速的数据写入和读取，非常适合实时分析 消息队列：利用Redis的list和pub/sub，可以实现轻量级的消息队列 分布式锁：Redis可以用作分布式锁的实现，确保在分布式系统中资源的安全访问，避免竟态条件 计数器：Redis的原子性操作非常适合用作计数器 2.2 内存存储 2.2.1 redis为什么这么快 reids将数据存储在内存中，提供那个快速的读写速度，相比传统的磁盘数据库，内存访问速度快很多 redis使用单线程事件驱动模型结合I/O多路复用，提高了并发效率（6.0引入多线程：随着数据规模的增长，请求量的增加，redis的执行瓶颈主要在于网络I/O，引入多线程处理可以提高网络I/O的处理效率，减少阻塞） 提供了多种高效数据结构 2.2.2 内存淘汰机制 redis的内存淘汰策略一共由8种 不淘汰数据（默认）： noeviction:当运行内存超过最大设置内存的时候，不会淘汰数据，而是直接返回报错禁止写入 设置了过期事件的数据淘汰 volatile_random:随机淘汰掉设置了过期时间的key volatile-ttl:优先淘汰掉较早过期的key volatile-lfu（3.0之前默认策略）：淘汰掉所有设置了过期时间的，然后醉酒未使用的key volatile-lfu(4.0后新增)：与上面类似，淘汰掉最少使用的key 所有数据的数据淘汰: allkeys-random:随机淘汰掉任意的key allkeys-lru:淘汰掉缓存中最久没有使用的key allkeys-lfu（4.0后新增）：淘汰掉缓存中最少使用的key 2.2.3 过期删除机制 定期删除(每隔一段时间（默认100毫秒）随机检查一定数量的键，如果发现过期键则删除) 惰性删除:只有查询到相关数据才执行检查和删除操作，容易造成内存泄漏 2.2.4 内存碎片化 redis的内存碎片化是指内存使用中出现小块空间被闲置，无法被有效利用 redis默认使用jemalloc作为内存分配器，它是按照固定大小来分配内存的 INFO memory:可以通过INFO memory来查看内存碎片率（mem_fragmentation_ratio） # Memory used_memory:1000000 ## 实际申请的内存空间 used_memory_human:977.54K used_memory_rss:1200000 ##表示实际占用的物理内存空间（含内存碎片） used_memory_rss_human:1.14M mem_fragmentation_ratio:1.20 ratio大于1证明存在内存碎片（过大需要清理），小于一说明已经使用swap用上磁盘空间了。
2024-7-17
Leetcode刷题心得
哈希 字母异位词 先将字符数组排序把异位词处理成相同格式，方便分类 getOrDefault(key,new class)方法,当集合中存在相应键值对，则取出相应的值，若没有，则创建新的键值对并返回相应的值 class Solution { public List<List<String>> groupAnagrams(String[] strs) { Map<String,List<String>> reflect=new HashMap<>(); boolean contain =false; for(String str:strs){ char[] array=str.toCharArray(); Arrays.sort(array); String key=new String(array); List<String> list=reflect.getOrDefault(key,new ArrayList<String>()); list.add(str); reflect.put(key,list); } return new ArrayList<List<String>>(reflect.values()); } } 双指针 盛最多水的容器 设计两个指针指向数组两端，移动较小的那个元素，因为水桶盛水量取决于较小的那一块，每移动一次就舍弃一个元素，因为当前计算结果就是当前以此元素为边的最大容量（不算舍弃过的元素（并不是真的舍弃，只是后续不再使用）） class Solution { public int maxArea(int[] height) { int length=height.
2024-7-17
心理学与生活
感知与记忆 颜色 *超越视觉 颜色不仅仅是我们对光波的一种感知，更是一种视觉经验 视网膜上对颜色识别起作用的细胞叫视锥细胞 知觉 *超越理解 知觉分为觉察、分辨、确定 知觉特性： 选择性（selectivity） 解释性（comprehension） 整体性（integrality） 恒常性（permanent） 错觉 *超越事实 外界纷繁的刺激，我们丰富的生活，都不断的帮助我们建立一个更好的视觉系统，让它发挥更好的作用 我们视觉系统的建立，伴随着大量的经验堆叠，以及视觉神经元在此过程中建立的联系 我们常说 耳听为虚，眼见为实，但实际上，眼见也未必为实 记忆 *给我一杯忘情水 记忆分为 外显记忆（explicit memory）: 内隐记忆（implicit memory）:不需要意志控制，不需要意志努力 陈述性记忆（declarative memory）：一种依靠语言描述来进行的记忆 程序性记忆（procedural memory）：一种关于怎么做事情的过程、步骤等具体操作的记忆 情景记忆（episodic memory）：一种对于过去经验中时间、地点、过程等的记忆 语义记忆(semantic memory)：一种对抽象符号的记忆 时间 感觉记忆（sensory memory）：0.5~4秒 短时记忆（short-term memory）：5秒到1分 长时记忆（long-term memory）：1分钟以上 最容易遗忘的记忆 语义记忆 外显记忆 陈述性记忆 难以遗忘的记忆 情景记忆 程序性记忆 内隐记忆 重构 *那些不真实的回忆 记忆重构（memory reconstruction） 记忆的存储过程是一个动态过程，在这个过程中一些已经有的经验会发生变化 人们会利用概括、归类等方式来重构信息 重构过程中，信息变得更加简单，不重要的细节完全被忽略，，突出和强调哪些重要的细节，这个故事就变得更加合理、符合背景和常识
2024-7-17