MySql面试题

事务的四大特征

事物特性ACID：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
原子性是指事务包含的所有操作要么全部成功，要么全部失败会滚。undo log
一致性是指一个事务执行之前和执行之后都必须处于一致性状态。
隔离性。mvcc
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是再数据库系统遇到故障的情况下也不会丢失提交的事务的操作。redo log

数据库的三大范式

第一范式：列不可再分---确保数据库表字段的原子性；
第二范式：表必须有主键；非主键字段必须完全依赖于主键；
第三范式：非主键列必须直接依赖于主键，不能存在传递依赖。

第二范式与第三范式的区别：
第二范式是非主键列完全依赖于主键，还是依赖于主键的一部分；
第三范式是非主键是直接依赖于主键，还是直接依赖于非主键。

事务的隔离级别

脏读：指一个事务处理过程里读取到了另外一个未提交的事务中的数据；
不可重复读：指对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，因为在查询间隔另外一个事务修改了该行数据并提交了。
幻读：指某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录

事务隔离就是为了解决以上的脏读、不可重复读、幻读这几个问题。

Seralizable(串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题；
Repeatable read(可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
Read Committed(读已提交)：一个事务只能看到已经提交事务所做的改变。可以避免脏读的发生。
Read Uncommitted(读未提交)：所有事务都可以看到其他未提交事务的执行结果。

查看隔离级别：select @@transaction_isolation;
设置隔离级别：set session transaction isolation;

生产环境大多使用RC。为什么不是RR呢？

utf-8与utf8mb4的区别?

collation指字符集的比较规则

collation=utf8mb4_general_ci，是指使用utf8mb4字符集的前提下，挨个字符进行比较(general)，并且不区分大小写(_ci，case insensitice)。
collation=utf8mb4_bin，就是指挨个比较二进制位大小，此时"debug"和"Debugg"就不是同一个单词。

索引

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

什么情况下需要建索引

1. 经常用于查询的字段
2. 经常用于连接的字段建立索引，可以加快连接的速度；
3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度；

什么情况下不适合建索引

1. where条件中用不到的字段不适合建立索引；
2. 表记录较少。比如只有几百条数据，没必要加索引。
3. 需要经常增删改。
4. 参与列计算的列不适合建索引；
5. 区分度不高的字段不适合建立索引，比如性别。

B+树 与 Hash索引

索引分类

1.  主键索引
 2. 唯一索引
 3. 组合索引
 4. 全文索引
 5. 普通索引

什么是最左匹配原则

聚簇索引

覆盖索引

索引什么情况下失效

Like、Or、Left、NOT、Null、menthod/convert
1. 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引；
2. 以%开头的like查询，如%abc，无法使用索引；
3. 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效；
4. 判断索引列是否不等于某个值时；
5. 对索引列进行运算；
6. 查询条件使用or连接，也会导致索引失效；

MyISAM和InnoDB的区别

1. 存储结构的区别；
2. 存储空间的区别；
3. 可移植性、备份和恢复；
4. 是否支持行级锁；
5. 是否支持事务和崩溃后的安全恢复；
6. 是否支持外键；
7. 是否支持MVCC；
8. 是否支持聚集索引；
9. 全文索引；
10.表主键；
11. 表的行数。

MySQL有哪些锁？

按锁粒度分类，有行级锁、表级锁和页级锁。

按锁级别分类，有共享锁、排他锁和意向锁。

意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求所的类型。InnoDB中两个表锁：
意向共享锁(IS)：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁(IX)：表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

事务可以通过以下语句显式加共享锁或排他锁。
共享锁：select ... LOCK IN SHARE MODE;
排他锁：select ... FOR UPDATE;

MVCC

MVCC(MultiVersion Concurrency Control)，多版本并发控制，一致性非锁定读。

MySQL日志系统

MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是bin log(二进制日志)和redo log(重做日志)和undo log(回滚日志)。

bin log

bin log是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。

redo log

redo log是InnoDB引擎级别，用来记录InnoDB存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，InnoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。

undo log

undo log是用于数据撤销操作，它保留了记录修改前的内容，通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

Server层和存储引擎层

MySQL主要分为Server层和存储引擎层：
Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等，还有一个通用的日志模块。
存储引擎：主要负责数据的存储和读取。Server层通过api与存储引擎进行通信。

Server层基本组件

连接器：当客户端连接MySQL时，Server层会对其进行身份认证和权限校验；
查询缓存：执行查询语句的时候，会先查询缓存，先校验这个SQL是否执行过，如果有缓存这个SQL，就会直接返回给客户端；如果没有命中，就会执行后续的操作。
分析器：没有命中缓存的话，SQL语句就会经过分析器，主要分为两步，词法分析和语法分析，先看SQL语句要做什么，再检查SQL语句是否正确。
优化器：优化器堆查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划；
执行器：首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息；如果有权限，就会根据执行计划区调用引擎的接口，返回结果。

分区分表

exist和in的区别

exists用于对外表记录做筛选。exists会遍历外表，将外表的每一行，带入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。
in是先把后面的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外表查询去查找。
子查询的表比较大的时候，使用exists可以有效减少总的循环次数来提升速度；
外查询的表比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度。

MySQL主从同步

processlist

show processlist 或 show full processList 可以查看当前MySQL是否有压力，正在运行的SQL，有没有慢SQL正在执行。返回参数如下：

1. **id**，线程ID
2. **db**：数据库名称
3. **user**：数据库用户
4. **host**：数据库实例的IP
5. **command**：当前执行的命令，比如Sleep、Query、Connect等
6. **time**：消耗时间，单位秒
7. **state**：执行状态，主要有以下状态：
      Sleep，线程正在等待客户端发送新的请求；
      Locked，线程正在等待锁；
      Sending data，正在处理SELECT查询的记录，同时把结果发送给客户端；
      Kill，正在执行kill语句，杀死执行线程；
      Connect，一个从节点连上了主节点；
      Quit，线程正在退出；
      Sorting for group，正在为GROUP BY做排序；
      Sorting for order，正在为ORDER BY做排序；
8. **info**：正在执行的SQL语句。

大表查询优化

  • 合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引；
  • 建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能；
  • 利用缓存。利用Redis等缓存热点数据，提高查询效率；
  • 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内；
  • 读写分离。经典的数据库拆分方案，主库负责写，从库负责读；
  • 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分；

count(*)、count(1)、count(字段名)的区别

count(1)会统计表中的所有的记录数，包括字段为null的记录；count(字段名)会统计该字段在表中出现的次数，忽略字段为null的情况。即不统计字段为null的记录；

执行效果上：

  • count(*)包含了所有的列，相当于行数，在统计结果的时候，** 不 会忽略列值为null **
  • count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为null
  • count(字段名)只包含列名那一刻，在统计结果的时候，会忽略列值为空(null)的技术，即某个字段值为null时，不统计

执行效率上：

  • 列名为主键，count(字段名)会比count(1)快；
  • 列名不为主键，count(1)会比count(列名)快；
  • 如果表多个列并且没有主键，则count(1)的执行效率优于count(*)；
  • 如果有主键，则select count(主键) 的执行效率是最优的；
  • 如果表只有一个字段，则select count(*)最优。

阿里巴巴Java开发手册有一条强烈建议：不要使用count(列名)或count(常量)来代替count()。count()就是SQL92定义的标准统计行数语法，跟数据库无关。

MySQL中DATETIME和TIMESTAMP有什么区别

  1. timestamp存储4个字节，datetime存储的8个字节。
  2. 对于TimeStamp，客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时，又将其转化为客户端当前时区进行返回。而对于DateTime，不做任何改变，是原样输入和输出。
  3. 存储的时间范围不一样，timestamp存储到2038年，datetime存储到9999年。
  4. 默认值，datetime默认值为null，timestamp默认值为当前时间current_timestamp。

为什么不建议用外键

数据备份

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • 三万字、91道MySQL面试题 附PDF
    数据库基础知识 1. 为什么要使用数据库 数据保存在内存 优点：存取速度快 缺点：数据不能永久保存 数据保存在文件...
    Python百事通阅读 426评论 0赞 1
  • Mysql面试题
    文章目录 MySQL 索引使用有哪些注意事项呢？索引哪些情况会失效索引不适合哪些场景 MySQL 遇到过死锁问题吗...
    祁小彬阅读 542评论 0赞 1
  • java面试之mysql面试题（详解）
    作者 薛之谦qxl 转载请注明出处 我的知乎原文：https://zhuanlan.zhihu.com/p/125...
    薛之谦chj阅读 685评论 0赞 0
  • 【转】mysql 面试题
    Mysql 的存储引擎,myisam和innodb的区别。 答： 1.MyISAM 是非事务的存储引擎，适合用于频...
    yufw阅读 1,277评论 0赞 1
  • mysql面试题
    1. MySQL 中 varchar 与 char 的区别？varchar(50) 中的 50 代表的涵义？ va...
    lj72808up阅读 101评论 0赞 0