如何设计一个关系型数据库?

  1. 存储部分:类似文件系统将数据持久化到硬盘上。
  2. 程序实例部分,创建模块:存储管理、缓存机制、SQL解析、日志管理、权限划分、容灾机制、索引管理、锁机制。

数据库的三范式?

  • 第一范式:强调的是列的原子性,即数据库表的每一列都是不可分割的原子数据项。
  • 第二范式:要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
  • 第三范式:任何非主属性不依赖于其他非主属性。

事物的基本要素ACID?

  • 原子性:事物是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  • 一致性:事物执行之前和执行之后,数据库的完整性没有被破坏。
  • 隔离性:同一时间内,只能一个事物操作同一数据,事物之间互不干扰。事物隔离分布不同级别:读未提交、读提交、可重复读、序列化。
  • 持久性:事物执行完后对数据的修改是持久化的。

char和varchar的区别?

  • char:固定长度,比如char(10)的类型,输入值"abc",占用的字节数还是10。
  • varchar:可变长度,占用的字节数是输入的值的字节数加一个用来记录值长度的字节。

where和having的区别?

  • 使用的地方不一样:where可以在select、update、insert、delete语句中使用,而having只能在select语句中使用。
  • 执行顺序不一样:where是在分组之前执行的,而having是在分组以后执行的。即where与having同时存在,先执行where后执行having。

数据库的事物隔离/事物隔离级别?

  • 读未提交(READ-UNCOMMITED):最低级别隔离,事物未提交之前就可以被其他事物读取到。会造成:幻读、脏读和不可重复度。
  • 读提交(READ-COMMITED):事物未提交之前不能被其他事物读取到。会造成:幻读、不可重复度。
  • 可重复度(REPEATABLE-READ):mysql默认的隔离级别,保证多次读取同一个数据时候,其值和事物开始时内容时一致的,禁止读取到别的事物未提交的数据。
  • 序列化:代价最高、最可靠的隔离级别,该隔离能防止幻读、脏读和不可重复度。

如何解决事物的并发问题?

  1. 使用版本检查:在数据库中保留"版本"字段,对数据进行读写操作的时候,带上"版本"判断。比如 update user set status=1 where uid=1 and status=0 就进行了简单的版本检查。版本检查可以作为"乐观锁",解决更新丢失的问题。
  2. 使用共享锁和排它锁或者更新锁:共享锁(S锁)也称为读锁,加S锁的对象只允许被当前事物和其他事物读。能给未添加锁的对象和添加了S锁的对象添加S锁,一个对象可以允许存在多个S锁;排它锁(X锁)也被称为写锁、独占锁,加X锁的对象只允许被被当前事物读写。只能给未加锁的对象添加X锁,一个对象只能有一把X锁,加了X锁的对象不能再加任何其他锁;此外还存在更新锁(U锁),更新锁的存在是因为多数数据库引擎加X锁的步骤是先加S锁,再尝试加X锁,如果存在两个事物同时加了S锁,此时加X锁会出现死锁,因此增加U锁, U锁代表有更新意向,只允许一个事物拿到U锁,该事物发生写后U锁变为X锁,未写时看作S锁

说一下乐观锁和悲观锁?

  • 乐观锁:乐观锁假定每次事物操作数据的时候,其他事物不会同时访问该数据,因此不会在数据库层次上进行锁定。乐观锁需要自己通过程序逻辑来实现,在表中保留"版本"字段,每次事物开始的时候获取版本号,修改的时候再对比查到数据的版本号是否和事物开始的时候一致,一致就完成修改并将版本号+1。不能解决脏读,需要数据库至少开启"读提交"的事物隔离级别。性能高,重试成本高不建议使用。
  • 悲观锁:悲观锁假定事物每次操作数据的时候,会存在其他事物同时访问该数据,使用数据库的锁机制实现。性能低,重试成本高建议使用,但使用不当有死锁的风险。

说说MVCC(多版本并发控制)?

可以认为MVCC是行级锁的一个变种,InnoDB采用了乐观锁的策略,在每行记录后面添加了两个隐藏的列,这两个列保存了行的版本号信息,每开启一个事物,版本号自动更新,事物开始的时候版本号作为事物的版本号,用来和查询的记录所带的版本号做对比。

Mysq逻辑架构和执行流程?

  • 最上层的服务类似其他CS结构,比如连接处理、授权处理。
  • 第二层是mysql的服务层,包括SQL的解析分析优化,存储过程触发器视图也在这一层实现。
  • 最后一层是存储引擎的实现。

说一下mysql的常用引擎?

  • InnoDB引擎:Mysql5.5后的默认引擎,InnoDB引擎提供了对事物的支持,支持行级锁和外键约束,它的设计目的是处理大容量数据的数据库系统。缓存索引和数据,对内存要求较高。
  • MyISAM引擎:不提供事物管理,不支持行级锁和外键约束。在执行插入、更新操作的时候会锁定整个表,会导致效率变低。只缓存索引。当表的读操作远远多于写操作,且不需要事物支持,选择MyISAM引擎比较好。

InnoDB和MyISAM的区别?

  • 是否支持事物:InnoDB支持事物,MyISAM不支持事物。
  • 是否支持行级锁:InnoDB支持行级锁,而MyISAM只支持表级锁。
  • 是否支持外键:InnoDB支持外键,而MyISAM不支持外键。
  • 缓存:InnoDB缓存索引和数据,而MyISAM只缓存索引。
  • 崩溃恢复:MyISAM奔溃后发生损坏的概率比InnoDB高得多,而且恢复更慢。

Mysql索引如何实现的?

索引是满足特定查找算法的数据结构,而这些数据机构会以某种方式指向数据,从而达到快速查找数据。Mysql中的索引,不同的数据引擎有不同的索引实现,但是主流的数据库引擎的索引都是基于B+树实现的。

B树和B+树的概念和区别?

  • B数:是一种多路搜索树,关键字和记录是放在一起,叶子节点可以看作外部节点,不包含任何信息;越靠近根节点的记录查找时间越短,只要找到关键字即可查找记录的存在。
  • B+树:非叶子节点只有关键字和指向下一个节点的索引,记录放在叶子节点中。每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还需要在比较关键字。

为什么选择B+树作为索引结构?

  • B+树的非叶子节点不存放记录,每个非叶子节点可以存放元素比B树更多, 这样可以减少磁盘的访问次数(一次磁盘访问的时间远大于关键字的比较时间) ,所以实际中B+树的性能可能还会好些。
  • B+树的叶子节点使用指针连接在一起,方便顺序遍历和范围查询。
  • B+树的查询效率更加稳定,每次查询的效率基本一样。

B+树的叶子节点都可以存放哪些东西?

可能存放整行数据,也可能是主键的值。

什么样的信息能称为索引?

主键、唯一键、普通键都可以,只要能让数据有一定区分性的字段。

索引的一些相关知识点:

  • 聚簇索引:将数据存储与索引放在了一块,找到索引也就找到了数据。
  • 非聚簇索引:将数据存储与索引分开的结构,索引结构的的叶子节点指向了数据的对应行。
  • 聚簇索引具有唯一性,一个表仅有一个聚簇索引。
  • 聚簇索引默认是主键,如果表中没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键作为聚簇索引。
  • MyISAM没有聚簇索引,都是二级索引。
  • 覆盖索引:指的是一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。

最佳左前缀法则?

指的是查询从索引最左前列开始并且不跳过索引中的列。每个左边的字段都是后面一个字段的一整个树,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。 要按照顺序命中索引

mysql问题排查都有哪些手段?

  • 开启慢查询日志,查看慢查询的SQL。
  • 使用explain命令查看SQL的执行计划。
  • 使用show processlist命令查看当前mysql的连接情况。

SQL优化可以从哪些方面考虑?

  • 合理创建索引。
  • 合理编写SQL语句:不使用 select * ,使用 limit 来限制返回的数据, in 包含的值不应该过多等。
  • 防止索引失效:保证最左前缀法;尽量不使用前缀模糊查询;避免索引列参与计算或者使用了函数;join查询使用小表驱动大表。避免在where子句中对字段进行null值判断。
  • 合理创建表字段:最好不要给数据库留null,尽可能的使用not null填充数据库。