Mysql面试集锦

Mysql面试集锦

[TOC]

1.索引是什么?有什么作用以及缺点

​ 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。也可以理解为索引就是一本书的目录,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚集索引那么需要的空间就会更大。

2.索引的目的是什么

为了高效的查找得到我们所需要的数据,减少分组和排序时间,提高我们的mysql的性能

3.索引对数据库系统的负面影响

虽然索引对于数据库的查询提高了效率,但一定程度上增加了空间的占用,同时写入的速度降低了不少,和原有写入数据相比较,多了一步去维护索引的操作。

4.建立索引的原则

选择唯一性索引,为经常需要查询、排序、分组和联合操作的字段建立索引,限制索引的数目,最左前缀匹配原则(非常重要的原则),尽量选择区分度高的列作为索引,字段尽力设置不为null,索引列上不计算。

5.主键、外键和唯一索引的区别

定义:

主键:唯一标识一条记录,不能有重复的,不允许为空

外键:表的外键是另一表的主键, 外键可以有重复的, 可以是空值

索引:该字段没有重复值,但可以有一个空值

作用:

主键:用来保证数据完整性

外键:用来和其他表建立联系用的

索引:是提高查询排序的速度

个数:

主键:主键只能有一个

外键:一个表可以有多个外键

索引:一个表可以有多个唯一索引

6.MySQL底层实现,MySQL有什么引擎

mysql底层采用B+tree的存储结构,也就是只有叶子节点携带真实数据,每个节点大小为16Kb,大致三层的B+tree就可以存2000W左右的数据,大大的减少了磁盘的IO。我们常见的存储引擎有InnoDB和MyISAM。

7.InnoDB和MyISAM区别,InnoDB替代了MyISAM,那么MyISAM是否一无是处。

  1. 事务支持 > MyISAM:强调的是性能,每次查询具有原子性,其执行速度比 InnoDB 类型更快,但是不提供事务支持。> InnoDB:提供事务支持事务,外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  2. InnoDB 支持行级锁,而 MyISAM 支持表级锁. >> 用户在操作myisam 表时,select,update,delete,insert 语句都会给表自动加锁,如果加锁以后的表满足insert 并发的情况下,可以在表的尾部插入新的数据。
  3. InnoDB 支持 MVCC, 而 MyISAM 不支持
  4. InnoDB 支持外键,而 MyISAM 不支持
  5. 表主键 > MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。> InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6 字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。
  6. InnoDB 不支持全文索引,而 MyISAM 支持。
  7. 可移植性、备份及恢复 > MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。> InnoDB:免费的方案可以是拷贝数据文件、备份binlog,或者用 mysqldump,在数据量达到几十 G 的时候就相对痛苦了
  8. 存储结构 > MyISAM:每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。> InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为 2GB。

8.什么是事务,事务特性

事务是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。在我们的mysql里也是如此,

也就是我们的ACID原则。

A原子性,把一系列的动作视为一个最小的操作(原子操作)

C一致性,从一个状态到另一个状态是一致的,

I隔离性:事务与事务之间是不可见相互隔离的,

D持久性:一旦事务提交,则所做修改就会被永久保存到数据库中。

可以简单说一下可重复读的MVCC机制,面试官也懵圈

  1. Read Uncommitted(读取未提交内容) >> 在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
  2. Read Committed(读取提交内容) >> 这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的 commit,所以同一 select 可能返回不同结果。
  3. Repeatable Read(可重读) >> 这是 MySQL 的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读(PhantomRead)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB 和 Falcon 存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control 间隙锁)机制解决了该问题。注:其实多版本只是解决不可重复读问题,而加上间隙锁(也就是它这里所谓的并发控制)才解决了幻读问题。
  4. Serializable(可串行化) >> 这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

9.如何设计一个高并发的系统(对于mysql来讲)

大致就是我们首先应该考虑到的是读写分离操作,再就是我们常见的分库分表操作,水平切分垂直切分。还可以加入缓存redis操作。合理使用索引,explain进行sql优化。

10.锁的优化策略

优化,也就是最小力度的锁我们的数据,也就是行锁,InnoDB的行锁其实是加在索引字段的,避免行锁的升级为表锁,再就是我们尽量避免间隙锁,尽量避免我们的范围修改,如果真的必须范围修改,那么我应该尽可能的缩小到最小的范围。

11.优化SQL的方法

设置一个主键索引,需主要主键索引一般没有真正业务含义,使用int类型自动增长的,而且不能为null,非主键索引字段优先考虑区分度高的业务情况和最左前缀原则,设置为null。如果真的数据量不大,不建议加索引,反而会影响效率的。选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL, 使用连接(JOIN)来代替子查询,适用联合(UNION)来代替手动创建的临时表。学会使用explain进行SQL分析,实在不行可以打开trace进行分析SQL情况,用完记得关闭。

12.谈谈三大范式,什么时候使用反范式设计

第一范式(1NF):确保每列保持原子性即列不可分

第二范式(2NF):属性完全依赖于主键,也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

第三范式(3NF):属性和主键不能间接相关(减少数据冗余,这样就可以通过主外键进行表之间连接)

比如我们表比较多,需要关联时,但我们的A表只需要关联B表的一个字段,而且每次都需要关联查询你,这时我们可以采用A表放置一个冗余字段来存B表的那个字段。这个操作其实就是一个反范式的。

13.说几个mysql中你常用的函数

sum、count 、avg、min、max

14.varchar(100)和varchar(200)的区别

占用内存空间大小肯定是不一致的,但是占用我们磁盘的大小是一致的,我们存储字符串”abc”,完全是一样的磁盘空间,但是对于varchar(100)来说,接收到的字符串长度太长了就会报错的。后面的数字代表可存储的字节数。

15.varchar(20)和int(20)中的20含义一样吗

显然不一致, int(M) M表示的不是数据的最大长度,只是数据宽度,并不影响存储多少位长度的数据;varchar(M) M表示的是varchar类型数据在数据库中存储的最大长度,超过则不存;

16.如何开启慢日志查询?

有2种方式,一是修改mysql的配置文件,二是通过set global语句来实现。slow_query_log = ON,打开日志,long_query_time = 2,设置时间,2秒就算是慢查询,然后重启mysql服务即可,进入mysql控制台,输入SET GLOBAL slow_query_log = ‘ON’;SET GLOBAL long_query_time = X;不需要重启服务就可以得到慢查询日志。

17.有哪些数据库优化方面的经验?

  1. 用 PreparedStatement, 一般来说比 Statement 性能高:一个 sql发给服务器去执行,涉及步骤:语法检查、语义分析, 编译,缓存。
  2. 有外键约束会影响插入和删除性能,如果程序能够保证数据的完整性,那在设计数据库时就去掉外键。
  3. 表中允许适当冗余,譬如,主题帖的回复数量和最后回复时间等4. UNION ALL 要比UNION 快很多,所以,如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话,那么就使用 UNIONALL。>>UNION 和 UNION ALL 关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。>1. 对重复结果的处理:UNION 在进行表链接后会筛选掉重复的记录,Union All 不会去除重复记录。>2. 对排序的处理:Union 将会按照字段的顺序进行排序;UNION ALL 只是简单的将两个结果合并后就返回。

18.请简述常用的索引有哪些种类?

  1. 普通索引: 即针对数据库表创建索引
  2. 唯一索引: 与普通索引类似,不同的就是:MySQL 数据库索引列的值必须唯一,但允许有空值
  3. 主键索引: 它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引
  4. 组合索引: 为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。即将数据库表中的多个字段联合起来作为一个组合索引。

19.mysql 的复制原理以及流程。

Mysql 内建的复制功能是构建大型,高性能应用程序的基础。将 Mysql 的数据分布到多个系统上去,这种分布的机制,是通过将 Mysql 的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。* 复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。

主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器在日志中读取的最后一次成功更新的位置。

从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。过程如下

\1. 主服务器把更新记录到二进制日志文件中。

\2. 从服务器把主服务器的二进制日志拷贝到自己的中继日志(replay log)中。3. 从服务器重做中继日志中的时间,把更新应用到自己的数据库上。

20.表中有大字段 X(例如:text 类型),且字段 X 不会经常更新,以读为为主,将该字段拆成子表好处是什么?

如果字段里面有大字段(text,blob)类型的,而且这些字段的访问并不多,这时候放在一起就变成缺点了。MYSQL 数据库的记录存储是按行存储的,数据块大小又是固定的(16K),每条记录越小,相同的块存储的记录就越多。此时应该把大字段拆走,这样应付大部分小字段的查询时,就能提高效率。当需要查询大字段时,此时的关联查询是不可避免的,但也是值得的。拆分开后,对字段的 UPDAE 就要 UPDATE 多个表了

21.MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的?

InnoDB 行锁是通过给索引上的索引项加锁来实现的,这一点 MySQL 与Oracle 不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!

可重复读的MVCC机制


  转载请注明: 晓笙的BLOG Mysql面试集锦

 上一篇
title: 从输入URL到页面加载发生了什么date: 2019-10-14 08:30:28tags: [前端经典面试题]categories: 面试题 从输入URL到页面加载发生了什么[TOC] 一、总体来说分为以下几个过程: DN
2019-10-14 ZongMan
下一篇 
RabbitMQ RabbitMQ
2019-09-24 ZongMan