sql执行流程
1 from 找表
2 on 关联条件帅选
3 join 关联表操作
4 where 条件筛选
5 group by 进行分组
6 avg,sum… 执行函数
7 having 分组后筛选
8 select 执行查询
9 distinct 去重操作
10 order by 排序操作
11 limit 分页操作
从上交那居有查重排限
1.客户端发送一条查询给服务器。
2.服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
3.服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。
4.MySQL根据优化器生成的执行计划,再调用存储引擎的API来执行查询。
5.将结果返回给客户端。
存储引擎
存储引擎是数据库底层软件组织,数据库管理系统通过存储引擎对数据库进行创建、查询、修改等操作。不同的存储引擎提供不同的索引技巧、存储机制。
Innodb(B+树)
Innodb底层是B+树,每个节点包含一个page,page大小固定为16k。其中非叶子节点只包含键值,叶子节点包含完整数据
适用场景:
1:支持事务。
2:可以从灾难中恢复(通过bin-log等方式)
3、外键约束
4、支持自动增加列属性
5、经常更新的表,适合多重并发的更新请求
Tukodb
和B+树类似,每个child除了指向child还会指向一个Message buffer ,这个message buffer是一个FIFO队列来缓存更新操作。
当一个操作到message buffer上时就可以返回,不需要搜索到叶子节点,缓存的更新会在查询时,或后台异步合并到对应的节点中。适用于访问频率不高的数据或历史归档
MyIsAM
MySQL默认的存储引擎,不支持事务,也不支持行锁和外键,当insert或update时会锁定整个表效率低。但是读取速度快而且不占大量内存和存储资源
Memory
也叫堆内存,使用在内存中的数据创建表,每个memory表对应一个磁盘文件,由于数据存放在内存中所以访问速度很快,同时支持散列索引和b树索引
索引
是一种一列或者多列的值进行排序的结构,可以快速访问数据表中数据信息
优点:1、大大加快了数据检索速度。2、将随机IO变成顺序IO。3、加速了表跟表之间地链接
缺点:1、时间和空间上都有消耗
索引的数据结构
为哈希和B+树
B+树所有的数据有序存放在叶子节点上所以查找效率高
B+树分为主索引和辅助索引,主索引为聚簇索引,辅助索引为非聚簇索引。
聚簇索引就是以主键作为B+树索引的键值所构成的B+树索引,聚簇索引的叶子节点存储着完整的数据记录
非聚簇索引就是以非主键作为B+树索引的键值所构成的B+树索引,非聚簇索引的叶子节点存储着主键值。也就是通过非聚簇索引找到主键值然后通过聚簇索引找到数据域
哈希索引,用哈希值作为key,指向数据行的指针作为value
哈希索引和B+树索引区别
1、哈希索引不支持范围查找
2、哈希索引不支持排序
3、哈希索引不支持模糊查询和多列索引的最左前缀匹配
4、哈希索引容易出现哈希冲突
索引种类
主键索引、组合索引、普通索引、全文索引、唯一索引
聚簇索引和非聚簇索引
区别就是聚簇索引把索引和数据放一起保存,非聚簇索引把二者分开保存。前者叶子节点保留了数据行,后者叶子节点保存的是指向数据行的地址
索引使用场景
1、中大型表
2、多个字段经常被查询适合组合索引
3、字段多且字段没有重复适合唯一索引
4、字段多有重复考虑普通索引
索引设计原则
1、where后面的列或者连接句子中指定的列
2、索引列区分度越高,索引效果越好
3、尽量用段索引
4、尽量用最左前缀匹配
5、不要过度索引
索引在什么情况下失效
1、条件上有or
2、索引参与隐式类型转换
3、索引使用函数
4、索引参与计算
5、索引用!、=、<>等
6、索引字段加上is null或not null
三范式
1、列中元素不可再分
2、列与主键相关
3、列与主键直接相关而不是间接相关
事务
1、原子性:事务各操作不可分,要不都执行要不都不执行
2、一致性:事务完成后数据处于一致状态
3、持久性:事务完成后他对数据库的修改永久保持
4、隔离性:每个事务之间是相对独立的
存储过程
一组为了完成特定功能的sql集合,只需要编译一次即可,再次调用时只需写入过程名称和参数即可,个人理解为函数
优化思路:
1、尽量避免循环,用聚合函数等代替
2、用try catch处理错误异常
3、中间结果用于存放临时表,加索引
4、少用游标
5、事务越短越好
6、查找语句尽量不要放在循环内
数据库并发策略
一般为乐观锁、悲观锁、时间戳
乐观锁就是假定在读数据时没有其他用户对该数据进行修改
悲观锁假定在读数据时有用户对数据进行修改
时间戳一般作为一列加入到表中,操作该数据就对时间戳加一,如果时间戳大于以前的就修改,否则就不修改
数据库锁
行级锁,表级锁,页级锁
行级锁是一种排他锁,防止其他事务修改此行
表级锁,对整张表进行加锁
页级锁位于行级锁和表级锁之间,一次锁定相邻的一组记录,表级锁速度快,冲突多,行级锁速度慢,冲突少。
多级join带来什么问题
• 降低查询效率
每个 join 操作都会增加查询的复杂度和时间,而且 join 操作通常需要读取磁盘上的数据,如果 join 操作过多,就会导致查询时间变长,降低查询效率。
• 增加系统负担
过多的 join 操作会占用系统的大量资源,包括 CPU、内存、磁盘等,从而增加系统的负担,影响系统的性能。
• 可读性差
过多的 join 操作会导致 SQL 语句的可读性变差,使得 SQL 语句难以理解和维护。
数据一致性和低冗余带来了什么问题(多表连接的性能问题,增加io次数)
回表 有哪些方式可以防止回表查询
通俗的讲就是,如果select所需获得列中有非索引列,一次索引查询不能获取所有信息,需要到表中找到相应的列的信息。这就叫回表。也就是说,需要从辅助索引(Secondary Index)中获取额外的数据列,需要通过辅助索引回到主键索引(Primary Index)中进行查找。
而如果根据一次索引查询就能获得所有列信息,就不需要回表。
覆盖索引查询:如果查询语句中的字段都包含在辅助索引中,且辅助索引覆盖了查询所需的所有字段,就不会触发回表操作。此时,MySQL可以直接从辅助索引中获取查询所需的数据,而无需回到主键索引。
使用聚集索引进行查询:如果查询语句使用聚集索引(即主键索引)进行查询,并且所需的字段都包含在聚集索引中,就不会触发回表操作。因为聚集索引中包含了完整的数据行,可以直接从聚集索引中获取所需的数据。
mysql binlog了解吗
二进制格式的文件
记录了所有DDL和DML(除了select)事件形式记录,语句的消耗事件
作用与主从复制和数据的增量恢复
二进制索引文件和二进制文件
二进制索引文件记录了所有的二进制文件
二进制文件记录了DDL,DML
mysql隔离级别,读已提交和可重复读是怎么实现的,可重复读如何解决幻读
脏读:事务A修改数据,事务B读取了数据后事务A报错回滚,修改的数据没有提交到数据库中,此时事务B读取修改的数据就是一个脏读,也就是一个事务读取到另一个事务未提交的数据就是脏读。
不可重复读:事务A在同一个事务上多次读取同一个数据,在事务A还没有结束时,事务B修改了该数据,由于事务B的修改,导致事务A两次读取的数据不一致,就出现了不可以重复读的现象。
幻读:事务A根据条件查询得到N条数据,但此时事务B更改或者增加了M条符合事务A查询的条件的数据。这样当事务A再次查询的时候发现会有N + M条数据,产生了幻读。
读未提交:可能会出现脏读、不可重复读、幻读,读取未提交事务的数据,数据撤回了,就是一种脏读。如果其他事务修改同一个数据,事务读取的数据也是不同的,所以也存在不可重复读。同时也能读取到其他事务添加的数据,所以也存在幻读。
读已提交:该隔离级别只能读取到其他事务提交后的数据,所以不存在脏读。但是在第一次读取数据后,其他事务修改后数据并提交事务,此时事务读取到数据就和第一次读到的数据不一致了,也就存在不可重复读。同时其他事务可以添加多条数据,也存在幻读。
可重复度读:表示整个事务看到的事务和开启后的事务能看到的数据是一致的,既然数据是一致的,所以不存在不可重复读。而且不会读取其他事务修改的数据,也就是不存在脏读。而对同一个批数据,可能会存在添加的情况,所以可能会存在幻读的情况。
串行化:当发生读写锁冲突时,后面的事务要等前面的事务执行完毕之后再执行,所以一定是先读或者先写的执行完毕之后再执行后读或者写,读写按照顺序依次进行,所以不存在脏读、不存在不可重复读、也不存在幻读。
MySQL MVCC 的原理
在 MySQL 中,MVCC 是一种多版本并发控制(Multi-Version Concurrency Control)的机制。MVCC 主要用于实现数据库的事务(Transaction)、并发控制(Concurrency Control),以及读一致性(Read Consistency)。
MVCC 主要在 InnoDB 存储引擎中实现,通过在每行记录后面保存两个隐藏的列来实现。这两个隐藏的列分别是:
- 创建版本号(CREATION)
- 删除版本号(DELETION)
当开始一个新的事务时,事务版本号会自动递增。这两个隐藏列的值会被设置为当前的事务版本号。这意味着在同一时间可以有多个版本的行存在,每个版本都对应一个或多个指定的事务。
对于任何特定的 SQL 查询,MVCC 会返回所有满足下列条件的行:
行的创建版本号小于或等于事务版本号。
行的删除版本号要么未定义,要么大于事务版本号。
这就意味着,每个事务会看到一个它开始时的数据库快照,因此可以实现非锁定读(Non-Locking Reads),也就是读一致性。
MVCC 的主要优点是它允许数据库进行更高的并发操作。因为读操作和写操作不会互相阻塞,所以可以大大提高数据库的性能。然而,MVCC 也有其缺点,例如它需要更多的磁盘空间来存储多个版本的行,并且对于大量的更新操作,可能需要更多的 CPU 和 I/O 资源来清理旧的行版本。
为什么用B+树索引,它解决了什么问题?其他索引的结构?
mysql索引为什么用b+树
LSM数和B+树区别?
答
关系型数据库与非关系型数据库的区别,并举例
关系型数据库(RDBMS)
关系型数据库基于关系模型,数据以表格的形式存储,每个表有其唯一的键(key)来标识每一行数据。具有一系列的操作符来操纵这些表格,可以在多个表之间进行联接操作以获取数据。
特点:
• 数据之间存在预定义的关系,例如一对一,一对多,多对多等。
• 数据以表格形式存储,每个表有多个列,每个列对应一个属性。
• 支持复杂的查询语句,如SQL。
• 强调数据的一致性和完整性,支持ACID(原子性、一致性、隔离性、持久性)事务。
例子:
• MySQL
• PostgreSQL
• Oracle Database
• MS SQL Server
非关系型数据库(NoSQL)
非关系型数据库不依赖于传统的行列式结构,而是使用更灵活的数据模型,如键值对、文档、列存储、图形数据库等。这些数据库设计用于处理大规模数据分布在多个服务器上。
特点:
• 没有预定义的数据模式,对数据的组织和格式更加灵活。
• 横向可扩展,适合大数据和实时应用程序。
• 不保证ACID,但一般会提供最终一致性。
• 支持简单查询,对复杂查询支持不如RDBMS。
例子:
• 键值存储:Redis, DynamoDB
• 文档存储:MongoDB, CouchDB
• 列存储:Cassandra, HBase
• 图形数据库:Neo4j, Amazon Neptune