mysql面试60题和答案

mysql 60题和答案

1. mysql中有哪几种锁？

  1. 表锁：开销小，加锁快，不会产生死锁，锁的粒度大，发生锁冲突的概率最高，并发度最低
  2. 行锁：开销大，加锁慢，会产生死锁，锁的粒度最小，发生锁冲突的概率最低，并发度最高
  3. 页面锁，开销和加锁时间介于表锁和行锁之间，会产生死锁，锁的粒度介于表锁和行锁之间，并发度适中
  4. 全局锁，锁住之后不能对数据库进行操作，一般用于数据库备份时使用

2. 简述MyISAM和InnoDB的区别？

  • MyISAM:
  1. 不支持事务，但是每次查询都是原子的
  2. 支持表级锁，即每次操作都是对整个表加锁
  3. 存储表的总行数
  4. 一个MyISAM表由三个文件组成：索引文件、表结构文件，数据文件
  5. 采用非聚集索引，索引文件的数据域存储指向数据文件的指针，辅助索引与主索引基本一致，但是辅助索引不用保证唯一性
  • InnoDB
  1. 支持ACID事务，支持事务的四种隔离级别(读未提交、不可重复度、可重复度、串行化)
  2. 支持行级锁和外键约束，因此可以支持写并发
  3. 不存储总行数
  4. 主键索引采用聚集索引，索引的数据域存储数据文件本身，辅助索引的数据域存储主键，因此通过副索引查找数据时，需要先查找到主键
    然后再进行回表，

3. 事务的四大特性ACID

  1. A，原子性：事务开始后的所有操作，要么全部执行完，要么都不做，不可能停滞在中间环节，事务过程中如果出错，会回滚到
    事务开始前的状态，就跟一切都没有发生一样，整个过程是原子的
  2. C. 一致性：事务开始前和结束后，数据库的完整性没有被破坏，例如A向B转账，不能说A扣了钱，B没有收到
  3. I. 隔离性：同一时刻，只允许一个事务请求同一数据，不同事务之间没有任何干扰，比如A正从银行卡取钱，在取钱结束前B不能向这张银行卡转账
  4. D. 持久性: 事务完成后，事务对数据库的所有操作都会被更新到数据库，不能回滚

4. 事务的并发问题

  1. 脏读：事务A读取了事务B更新的数据，此时事务B回滚操作，事务A读取到的数据就是脏数据
  2. 不可重复读：事务A重复读取同一数据，在这个过程中事务B对该数据做个更新，导致事务A每次读取的数据不一致
  3. 幻读：系统管理员A正在修改成绩表中的分数为ABCDE，在这个过程中系统管理员B插入了一条成绩分数的数据，
    系统管理员A结束后发现有一条数据没改
    小结：不可重复读和幻读很容易混淆，不可重复度侧重于修改，幻读侧重于新增或删除
    解决不可重复度只需要锁住满足条件的行即可，解决幻读需要锁表

5. mysql事务的隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是 mysql默认的事务隔离级别，不会出现脏读，可以重复度
串行化（serializable） 否 否 否 无论读写都会锁住整张表，不会出现幻读，但是性能太低
事务隔离级别从低到高排序：读未提交--->不可重复度--->可重复度--->串行化
事务的隔离级别越高，越能保证数据的完整性和一致性，但是对并发的性能影响也越大

6. mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？

  1. read uncommitted 脏读，读到未提交的数据
  2. read committed 脏读，不可重复读
  3. repeatable read 可重复读
  4. serializable 串行化

7. char和varchar的区别？

  1. char和varchar在存储和检索方面有所不同
  2. char的列长度固定为创建表时声明的长度，长度值范围是1-255
  3. 当char值被存储时，它们被用空格填充到特定长度，检索char值时需要删除尾随空格

8. 主键和候选键有什么区别？

  1. 表格的每一行都由主键唯一标识，一个表只能有一个主键
  2. 主键也是候选键，按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用

9. Myisamchk用来做什么的

它用来压缩Myisam表，这样减少了磁盘和内存使用

10. 如果一个表有一列定义为TIMESTAMP将发生什么

每当行被更改时，时间戳字段将获取当前时间戳

11. 设置列为AUTO INCREMENT时，如果在表中达到最大值会发生什么情况

它会停止递增，任何进一步的插入都会产生错误，因为秘钥已被使用

12. 你怎么看到为表格定义的所有索引

show index from <table_name>

13. like声明中的%是什么意思

%对应于0个或更多个字符，_仅代表一个字符

14. mysql如何优化distinct

使用group by代替distinct

15. 可以使用多少列创建索引

任何标准表最多可以创建16个索引列

16. NOW()和CURRENT_DATE()有什么区别

NOW()返回年月日时分秒，CURRENT_DATE()返回年月日

17. 通用sql函数

CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
FORMAT(X, D)- 格式化数字X到D有效数字。
CURRDATE(), CURRTIME()- 返回当前日期或时间。
NOW（） – 将当前日期和时间作为一个值返回。
MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） – 从日期值中提取给定数据。
HOUR（），MINUTE（），SECOND（） – 从时间值中提取给定数据。
DATEDIFF（A，B） – 确定两个日期之间的差异，通常用于计算年龄
SUBTIMES（A，B） – 确定两次之间的差异。
FROMDAYS（INT） – 将整数天数转换为日期值。

18. mysql支持事务吗

  1. 在缺省模式下，mysql是autocommit模式的，所有数据库更新操作都会及时提交，所以在缺省情况下，mysql是不支持事务的
  2. 但是mysql如果使用InnoDB存储引擎的话，就支持事务了, Myisam存储引擎就不支持事务

19. 存储金钱的两种数据类型：decimal/numeric

20. mysql有关权限的表有哪几个

user、db、tables_priv、columns_priv

21. mysql数据库作发布系统的存储，一天5万条以上的增量，预计运维三年，怎么优化

  1. 设计良好的数据结构，允许部分数据冗余，尽量避免join查询，提高效率
  2. 选择合适的表字段类型和存储引擎，适当的添加索引
  3. mysql库主从读写分离
  4. 找规律分表，减少单表中的数据量，提高查询效率
  5. 添加缓存机制
  6. 不经常改动的页面，生成静态页面
  7. 优化sql

22. 锁的优化策略

  1. 读写分离
  2. 分段加锁
  3. 减少锁持有时间
  4. 多个线程尽量以相同的顺序获取资源

23. 索引的底层实现原理和优化

  1. mysql索引底层使用的是B+tree, 经过优化的B+tree
  2. 主要是在所有叶子节点中增加了指向下一个叶子节点的指针，叶子节点形成了一个链表，InnoDB建议使用默认的自增主键作为主索引

24. 什么情况下设置了索引但是无法使用

  1. 使用like查询并且%开头的语句不能走索引
  2. 索引字段使用了函数
  3. or语句前后没有同时使用索引
  4. 数据类型出现隐式转换

25. 实践中如何优化mysql

  1. sql语句及索引的优化
  2. 数据库表结构的优化
  3. 系统配置的优化
  4. 硬件的优化

26. 简单描述mysql中, 索引、主键、唯一索引、联合索引的区别，对数据库的性能有什么影响（从读写两方面）

  1. 索引是一种特殊的文件，它们包含对数据表里所有记录的引用指针
  2. 索引的任务就是加快对数据的访问速度
  3. 普通索引允许被索引的列的数据包含重复值，如果索引的列不允许有重复值就可以使用唯一索引
  4. 主键索引是特殊的唯一索引，一张表中只能有一个主键索引，由primary key关键字定义
  5. 索引可以包含多个数据列，如index(columnA, columnB)，就是联合索引
  6. 因为索引的存在，大大提高了数据库的查询效率，但是会降低增加、删除、修改表的速度，因为执行这些代码时还要操作索引文件

27. sql注入漏洞产生的原因，如何防止？

程序开发过程中没有规范书写sql语句，没有对特殊字符进行过滤都会导致sql注入的风险
使用orm可以有效的防止sql注入，sql语句书写尽量不要省略双引号和单引号，过滤掉关键词select update delete insert *等

28. 对于关系型数据库而言，索引是相当重要的概念，请回答索引相关的几个问题

  1. 索引的目的是什么？
  • 快速访问数据表中的特定信息，提高检索速度
  • 创建唯一索引，保证数据库表中每一行数据的唯一性
  • 加速表与表之间的链接

  1. 索引对数据库系统的负面影响是什么？
    创建索引和维护索引也需要耗费时间，这个时间随着数据量的增加而增大，索引需要占用物理空间，
    不光是表需要占据数据空间，每个索引也需要占用物理空间，对表进行增、删、改的时候索引也需要动态维护，
    这就降低了数据的维护速

  2. 为数据表建立索引的原则有哪些？

  • 在最频繁使用的，用以缩小查询范围的字段上建立索引
  • 在频繁使用的需要排序的字段上建立索引

  1. 什么情况下不宜建立索引？
    对于查询中很少设计的列以及重复值很多的列，不宜设计索引
    对于一些特护的类型不宜建立索引，比如text类型

  2. 为什么重复值很多的列不宜设计索引？
    因为如果重复值特别多，比如性别列只有男和女，当我们为性别列建立了索引的话，
    辅助索引每次查询男或女都会查询出一半左右的主键值，然后再拿着这一半的主键值去走聚簇索引（主索引），反而比全表扫描性能还低，
    这种情况下就不能建立索引

29. 解释mysql外连接、内连接、与自连接的区别？

交叉连接：交叉连接又叫笛卡尔积表，它是指不使用任何条件，将一个表中的所有记录与另外一张表中的所有记录一一匹配
内连接：根据条件，只筛选两个表中都有的记录
外连接：分为左外连接、右外连接、全外连接
左外连接：左表为主表，左表中的记录全部显示，右表中所有匹配的记录显示，不匹配的记录显示为NULL
右外连接：与左相反
全外连接：mysql目前还不支持全外连接，但是可以使用union合并左外+右外=全外

30. mysql中的事务回滚机制概述

事务是用户定义的一个数据库操作序列，这些操作要么都做完，要么都不做，
事务回滚是将一个事务中对数据库的更新操作撤销，事务回滚需要通过InnoDB中的回滚日志undo log来实现

31. sql语言包括哪几部分？ 每部分都有哪些操作关键字？

数据定义语言DDL：create/alter/drop table, create/drop index
数据操作语言DML: select insert update delete
数据控制语言DCL: grant revoke
数据查询语言DQL: select

32. 完整性约束包括哪些？

数据的完整性是指数据的精确性和可靠性
与表有关的约束，包括列约束：not null非空约束，primary key主键约束，foreign key外键约束, unique唯一约束等

33. 什么是锁？

加锁是实现数据库并发控制的非常重要的技术，分为读锁和写锁，当一个事务获取了读锁，
其它事务就只能获取读锁，不能获取写锁，
当一个事务优先获取了写锁，其它事务既不能获取读锁也不能获取写锁
但是mysql中又分为当前读和快照度，上面说的是当前读的情形，快照读的话不影响其它事务获取写锁
基本锁类型包括行级锁和表级所，InnoDB是行级锁，MyISAM是表级锁

34. 什么叫视图？游标是什么？

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增删改查操作，视图由一个或多个表的行列组成的集合
对视图表的修改不影响基本表，它使我们查询数据更容易
游标：对于查询出来的结果集作为一个单元来有效的处理，游标可以定在该单元中的特定行，
一般不使用游标，但是需要逐条处理的时候就需要使用游标

35. 如何通俗的理解三个范式？

第一范式是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式是对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性
第三范式是对字段冗余性约束，即任何字段不能由其它字段派生出来，它要求字段没有冗余
范式优点：减少数据冗余，使得更新快，体积小
缺点：查询需要多表关联查询，效率低

36. 什么是基本表？什么是视图？

  1. 基本表是本身独立存在的表，视图是一种虚拟的表，视图是由一个或多个基本表导出的表，
    视图本身不独立存储在数据库中，一种虚表

37. 试述视图的优点？

视图能够简化用户操作，视图能对机密数据提供安全保护

38. NULL是什么意思？

Null表示Unknown未知，它不表示""空字符串，

主键、外键和索引的区别

主键：唯一标识一条记录，不能有重复的，不能为空
外键：表的外键是另一个表的主键，外键可以是重复的
索引：该字段没有重复值，但可以有一个空值

  • 作用
    主键：用来保证数据的完整性
    外键：用来和其它表建立联系使用
    索引：用来提高查询效率
  • 个数
    主键：一个表只能有一个主键
    外键：一个表可以有多个外键
    索引：一个表可以有多个唯一索引

你可以用什么来确保表格里的字段只接受特定范围里的值

check限制，它在数据库表格里被定义，用来限制输入该列的值
触发器也能够限制数据库表格里字段能够接收的值，但是要求触发器在表格里被定义
在某些场景下回影响性能

说说对sql语句优化有哪些方法（选择几条）？

  1. 能使用索引覆盖就使用索引覆盖，避免回表查询，大大提高查询效率
  2. 首先考虑where以及order by涉及的列上建立索引，避免全表扫描
  3. 不要在索引列上进行is null判断或函数运算，否则会走全表扫描
  4. 如果要对两个不同字符集的表的索隐列进行关联查询，要显示的将其字符集统一在关联查询，否则mysql会隐式的加函数运算就全表扫描了

锁的讲解

锁的优点：保证数据并发访问的一致性和有效性
锁的缺点；对数据库并发访问性能有一定影响

悲观锁：每次去拿数据时都认为别人会修改，所以每次拿到数据的时候都会上锁，别人想拿到数据就会block直到拿到数据
传统的关系型数据库用到了很多这种锁机制：行锁、表锁、读锁、写锁都是在操作之前先上锁

乐观锁：每次去拿数据时都认为别人不会修改数据，所以不上锁，但是在更新的时候会判断一下别人在此期间有没有更新这个数据
可以使用版本号等机制，乐观锁适用于多读的场景，提高吞吐量

悲观锁和乐观锁的区别：
两种锁各有各的优缺点，乐观锁适用于写比较少的场景，即冲突很少发生时，这样省去了锁的开销，提高了系统吞吐量，
但是如果经常产生冲突，上层应用会不断的重试，反而降低了性能，这种情况适用于悲观锁

  • 表锁：开销小，加锁快，不会产生死锁，并发低

  • 行锁：开销大，加锁慢，会产生死锁，并发高

  • 页面锁：开销和并发介于表锁和行锁之间，也会产生死锁

  • 共享锁：共享锁也是悲观锁的一种，在sql语句后面加 lock in share mode，就相当于对某些资源加上共享锁了

  • Innodb中大部分场景使用行锁，因为行锁和事务是我们选择InnoDB的理由

  • 但是在个别特殊事务中，也可以考虑使用表锁

表锁和行锁的应用场景

  1. 表锁适用于并发性不高，以查询为主，少量更新的应用
  2. 而行锁适用于高并发环境下，对事务完整性要求较高的系统

参考文章

posted @ 2022-03-29 09:50  专职  阅读(334)  评论(0)  编辑  收藏  举报