一个SQL的查询流程是怎样的?
- 客户端发送查询SQL到服务端。
- 服务端会在查询缓存中查询是否有此SQL对应的数据,有则直接返回。
- 查询缓存中没有SQL对应的数据,则服务端会对SQL语句进行词法分析、语法分析。
- 服务端会对SQL进行默认优化,然后生成一个执行计划。
- 服务端会将SQL执行计划交给SQL执行器。
- SQL执行器会根据SQL执行计划,调用存储引擎执行SQL查询逻辑,并将查询结果存储到缓存,然后返回给客户端。
一个SQL语句的更新流程是怎样的?
- 加载数据到缓存,例如加载名字为张三的记录所有的整页数据(相当于索引树的一个结点,16KB)
- 写入更新数据的旧值到undo日志(回滚日志-是一种逻辑日志,记录的反向操作)中,方便回滚;
- 执行器更新BufferPool缓存池中的内存数据;
- 写入redo日志,将操作结果(这里的日志为物理日志,例如更新哪个地址中的哪页数据)写入Redo Log Buffer缓冲区;
- 准备提交事务,redo日志写入磁盘文件中;
- 提交事务,将更新操作写入到binlog日志(需要开启),binlog日志写入磁盘,binlog主要用来恢复数据库磁盘里的数据;
- 写入commit标记到redo日志文件里,提交事务完成,该标记为了保证事务提交后的redo与binlog数据一致;
- 将Buffer Pool缓冲池中修改后的数据随机写入磁盘,以Page为单位写入,执行完后,磁盘中的name=李四。
MySQL服务端常用的组件有哪些?
连接器(管理连接、权限效验)、解析器(词法解析、语法解析)、优化器(对SQL进行基本调优,生成执行计划)、执行器(调用执行引擎执行sql)
常用SQL子句的执行顺序是怎样的?
MySQL中InnoDB和MyISAM存储引擎有什么不同?
MyISAM索引文件和数据文件是分离的(非聚集/簇)
表存储在磁盘中,如果不改动,存储MySQL安装目录的该库的data目录下
对于MyISAM存储引擎用三个文件存储数据 xx.frm(表结构文件),xx.MYD (数据文件),xx.MYI(索引文件)
InnoDB存储引擎索引与数据是不分离的(聚集/簇)
表存储在磁盘中,如果不改动,存储MySQL安装目录的该库的data目录下
对于 InnoDB 存储引擎用两个文件存储数据 xx.frm(表结构文件),xx.idb (数据文件+索引文件)
- 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
- 聚集索引-叶节点包含了完整的数据记录
- 一张表只有一个聚集索引(即主键索引),其他索引都是二级索引,叶子节点的 data 中存储叶子节点的主键值,由该值进行回表操作到主键索引中查找数据,这样既能满足快速找到数据也能保证数据的一致性和节约存储空间。如果没有唯一主键,则MySQL会选择唯一列,如果没有,会自己维护一个隐藏列(如rowid)作为聚簇索引。
InnoDB&MyISAM异同点:
1、InnoDB 支持事务,MyISAM 不支持事务。(MySQL 将默认存储引擎从 MyISAM (5.1之前) 变成 InnoDB 的重要原因)
2、InnoDB 支持外键,MyISAM 不支持
3、InnoDB 是聚集索引,MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针
4、 InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。MyISAM 用一个变量保存了整个表的行数。执行是可以直接返回
5、 InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,导致并发访问受限。(MySQL 将默认存储引擎从 MyISAM (5.1之前) 变成 InnoDB 的重要原因)
你了解binlog吗?它的作用是什么?
归档日志,一般用于记录sql的更新逻辑,可以实现数据的备份,数据库节点之间数据的同步。
你了解undolog吗?它的作用是什么?
回滚日志,一般用于记录更新操作的反向sql操作,用于保证事务的原子性、一致性。
你了解redolog吗?它的作用是什么?
重做日志,可以循环使用,一般用于记录sql的物理操作,用于保证事务的持久性,可用于故障恢复。
你设计数据库时要考虑哪些问题?
数据库的名字、数据库的编码、数据库中表的数量、数据库上的基本操作
设计数据库表时回考虑哪些问题?
表的名字、字段名、字段类型、字段约束、字段默认值、注释、字段个数、设计范式、反范式
mysql中常用字段类型有哪些?
- 字符型(char、varchar、text)
- 数值型(tinyint、int、bigint、decimal)
- 日期型(date、datetime、timestamp)
- 二进制(blog)
- 其他(enum、set、json)
使用数据类型时,有什么规则?
使用简单数据类型、最小数据类型、小数用decimal,避免使用text和blog这样的大数据类型。
说说mysql中的六大约束有哪些?
- 主键约束(primary key)
- 非空约束(not null)
- 默认值约束(default)
- 唯一约束(unique key)
- 检查约束(check)
- 外键约束(foreign key)
说说表设计时的三大范式?
范式是一种设计规范,一种关系模式,可以对表的设计起到一个指导性作用。
第一范式(1NF):描述的是字段名不可再分。例如姓名可再分为姓和名,这属于可再分。
第二范式(2NF):描述的是不存在非主键字段对主键字段的部分依赖。
第三范式(3NF):不存在非主键字段对主键字段的传递依赖。
如何理解表设计时的反范式?
范式设计为我们进行表设计提供一些指导性思想,但实际项目中有时为了提高查询效率,可能会在表中适当的添加一些冗余字段。就类似于将课程名添加到成绩表中,这样查询成绩表时可以直接查询出课程名,不需要再去关联课程表进行查询了。但是这种冗余可能会带来更新的复杂度。例如更新课程表的课程名时,还要去更新成绩表中的课程名。