首页 > MYSQL之面试篇
头像
社交牛逼一号
发布于 2021-09-17 00:49
+ 关注

MYSQL之面试篇


三大范式

第一范式(1NF):字段不可分

第二范式(2NF):有主键,非主键字段依赖于主键

第三范式(3NF):非主键字段不能相互依赖

1NF:原子性,字段不可再分,否则就是非关系型数据库。

2NF:唯一性,一个表只能说明一个事务。

3NF:不存在传递依赖,没列都与主键有直接关系。

存储过程是像编程语言中的函数一样,封装了我们的代码(PLSQL,T-SQL)

数据库索引(B+树)

在数据结构中,最常见的搜索结构就是二叉搜索树和AVL(高度平衡二叉树),但是在搜索量较大时,树的深度也变大,会造成I/O读写过于频繁,最终导致查询效率底下,因此对于索引而言,多叉树结构为不二选择,B-Tree的各种操作都能导致B树保持较低的高度,从而保证了高效率查询。

B+树:查找效率高,在查找过程中,每次都能抛掉一部分节点,减少遍历个数。

当我们只需要进行两次I/O读写的时候,一个节点可以储存1000个键值,那么3层B+树可以存储10亿数据。

B+树内部有两种节点,一种是索引节点(不保存记录,只保存索引),一种是叶节点(存储所有的数据)。

B+树的叶节点都会呗连城一条链表,按索引从小到大排序

索引的分类:

唯一索引:唯一索引,不允许两行具有相同的索引 主键索引:每个表中的唯一索引,不能为空。 聚集索引(Clustered):表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只能有一个 非聚集索引(Non-clustered):指表的逻辑顺序,可以有多个,小于249个。

事务

事务逻辑上的一组操作,要么全部成功,要么全部失败。

原子性:事务作为一个整体被执行,要么全部失败,要么全部成功 一致性:保证数据库状态从一个一致性状态转变为另一个一致的状态 隔离性:多个事务并发执行,一个事务的执行不应该影响其他事务的执行 持久性:一个事务一旦提交,对数据库的修改应该永久保存

并发问题:

脏读:一个事务读取到了另一个事务还没有提交的数据 不可重复读:在一个事务中,两次读取的数据不同(针对于数据的修改) 幻读:跟不可重复读类似,两次读取到不一样的数据(新增或者删除)

隔离级别:

隔离级别决定了事务可能影响到了另一个事务,mysql和innoDB都支持

读未提交:允许一个事务读取另一个事务还没提交的数据,会导致脏读 读已提交:一个事务中只允许对其他事务已经提交的记录可见,不能避免不可重复读 可重复读:在一个事务开始后,其他事务对数据库的修改在本事务中不可见,直到回滚或者提交。其他操作的增删可见,不能避免幻读问题,在一个事务中查询的解雇都一样,除非更新数据库。 序列化:最高级别的隔离,只允许事务串行化执行

mysql 默认的隔离级别是可重复读

mysql事务支持:

事务不是绑在mysql服务器本身的,而是与存储引擎相关。

视图

一种虚拟的表,具有和物理表相同的功能,可以对其进行增改查的操作。

使用场景:1.不希望访问者获取整个表的信息,拟一个虚表

  1. 查询数据来源于不同的表,把多个表查询的结果联合起来。

drop delete 和 truncate的区别

drop直接删除表,truncate删除表中数据,再插入时自增长id又从一开始,delete删除表中数据,可以加where字句。

数据库的乐观锁和悲观锁

数据库管理系统(DBMS)中的并发控制的任务是确保再多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制和悲观并发控制时并发控制只要采用的技术手段。

悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作

悲观锁是一种利用数据库内部机制提供的锁的方式,也就是对更新的数据加锁,这样在并发期间一旦有一个事务持有了数据库记录的锁,其他线程将不再能对数据进行更新,这就是悲观锁的实现方式。

要使用悲观锁,必须先关闭mysql数据库的自动提交属性。MYSQL数据库默认是自动提交属性。

优点与不足:悲观并发控制实际上是”先取锁再访问“,为数据处理的安全提供了保证。但是在效率方面,处理加锁的机制会让数据库产生额外的开销,有产生死锁的机会。只读事务处理由于不会产生冲突,没必要加锁,会增加系统负载。一个事务如果锁定了某行数据,其他食物就必须等待该事务吃力完成才可以处理那行数据,降低了并行性!

乐观锁:只在提交操作时检查是否违反数据完整性!

乐观锁不会阻塞其他线程并发的控制,不会使用数据库的锁进行实现,不会影响其他线程频繁挂起和恢复,提高并发,一般实现乐观锁的方式就是记录数据版本!

数据版本为数据增加一个版本标识,读取数据时,将版本标识的值一同读出,数据和版本标识一同更新。当我们提交更新的时候,判断数据库对应记录的当前版本信息与第一次取出来的版本标识进行时,如果当前版本与第一次的版本相同,就更新,否则就认为是过期数据。

有点与缺点:乐观并发控制相信事务之间的数据竞争的概率是比较小的,因此尽可能直接做下去,直到提交得到时候才去锁定,不会产生任何的锁。但如果直接简单这么做,还是有可能遇到不可预期的结果,例如两个事务都读取了数据库的某一行,经过修改以后回写数据库,这时就会遇到问题。

MyIASM Innodb

都是使用的B+树。

主键就是聚集索引吗?主键和索引有什么区别?

主键可以是聚集索引也可以是非聚集索引,在SQLServer中,主键的创建必须依赖于索引,默认创建的是聚集索引,但是可以显式指定为非聚集索引。Innodb 作为mysql存储引擎时,默认按照主键进行聚集,如果没有定义主键,它会试着使用唯一的非空聚集索引来代替。

如果没有这种索引,Innodb 就会定义颖仓的主键然后再上面进行聚集,所以对于聚集索引来说,创建主键的时候,就自动创建了组件的聚集索引。

实践中如何优化MySQL

实践中,MySQl 的优化主要涉及SQL语句及索引的优化,数据表结构的优化,系统配置的优化和硬件的优化四个方面

SQL语句优化:

SQL语句的优化主要包括三个问题,即如何防线有问题的SQL,如何分析SQL的执行计划以及如何优化SQL。

1MySQL的慢查询日志。用来记录再MySQL中响应超时的语句。超过具体运行时间超过long_query_time值得sql,默认为10s。


当我们查询出了IO大得语句,或者发现未命中得索引得语句,都是我们优化得对象。

2通过explain查询和分析SQL的执行计划:

使用EXLAIN关键字可以知道MySQL是如何处理SQL语句的,通过explain命令可以得到表的读取顺序,数据读取操作的操作类型,那些索引可以使用,那些索引被实际使用,表之间的引用以及每张表有多少行被优化器查询等问题,当扩展列extra出现的Using filesort和Using temporay 则往往表示SQL需要优化了。

3SQL语句的优化

1 优化insert语句 一次插入多个值

2 尽量避免再where子句中使用!= 或者<>操作符,否则将引擎放弃使用索引而进行全表扫描

3 尽量避免再where子句中对字段进行null值判断,斗则将导致引擎放弃使用索引,进行全表扫描

4 优化嵌套查询:子查询可以被更有效率的链接jion代替

5 很多时候exists 代替in 是一个好的选择

6 选择最有效的表名顺序,数据库的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表将最处理。

在from子句包含多个表的情况下:如果三个表完全额米有关系,将记录和列名最少的表,写在最后。

如果有三个以上的连表查询:如果三个表有关的话,将引用最多的表放在最后,被其他表所引用的表放在最后。

7 用IN 代替OR

8 select 子句中避免使用*号

索引优化:

建议在经常作查询选择的字段,经常作表链接的字段以及经常出现在order by group by distinct 后面的字段建立索引,但必须注意一下几种可能会引起索引失效的情形。

数据库结构优化

选择合适数据类型

使用较小的数据类型解决问题

使用简单的数据类型 mysql处理int要比varchar容易

尽可能的使用not null 定义字段

尽量避免text类型,非用不可时最好考虑分表。

一般情况下 表的设计应该遵循三大范式

表的垂直拆分

吧含有多个列的表拆成多个表,解决表款度问题。

表的水平拆分

表的水平拆分用于解决数据表中数据过大的问题,水平拆分每一个表的结构都完全一直。

对id进行hash运算,如果要拆分成5个表,mod(id , 5)取出0~4个值

针对不同的hashId将数据存入不同的表中。

表的水平拆分会带来一些问题和挑战,包括跨分区表的数据查询、统计及后台报表的操作等问题,但也带来了一些切实的好处:

表分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度; 表中的数据本来就有独立性,例如表中分别记录各个地区的数据或不同时期的数据,特别是有些数据常用,而另外一些数据不常用。 需要把数据存放到多个数据库中,提高系统的总体可用性(分库,鸡蛋不能放在同一个篮子里)。

系统配置的优化

操作系统配置的优化:增加TCP支持的队列数

mysql配置文件:innodb缓存池设置(innodb_buffer_pool_size,推荐总内存的75%)和缓存池的个数(innodb_buffer_pool_instances)

硬件的优化

cpu:核心数多并且主频高的

内存:增大内存

磁盘配置和选择:磁盘性能

全部评论

(1) 回帖
加载中...
话题 回帖