MySQL面试题

(Updated: )
Java面试

数据库范式

  • 第一范式：原子性（列不可分）
  • 第二范式：非主属性完全依赖主属性（要有主键）
  • 第三范式：非主属性互相不依赖，消除传递（不可存在传递依赖）

范式和反范式的理解

  • 范式
    • 理解：符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则，在关系型数据库中，这种规则就是范式。包含第一范式，第二范式，第三范式。
    • 优点：减少了数据冗余，数据表更新操作快，占用存储空间少
    • 缺点：查询时需要对多个表进行关联，查询性能降低，更难进行索引优化
  • 反范式：
    • 理解：没有冗余的数据库未必是最好的数据库，有时候为了提高运行效率，就必须降低范式标准，适当保留冗余数据。降低范式就是增加字段，允许冗余，达到以空间换时间的目的。（通过冗余数据来提高查询性能）
    • 优点：可以减少表关联，可以更好的进行索引优化
    • 缺点：存在大量冗余数据，数据维护成本更高

事务四大特性（ACID）

  • 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  • 一致性（Consistency）：事务前后数据的完整性必须保持一致。
  • 隔离性（Isolation）：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其它事务的操作数据所干扰，多个并发事务之间要相互隔离。
  • 持久性（Durability）：一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

数据库并发问题

  • 丢失修改：T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改。
  • 脏读：T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，那么T2读取的数据是脏数据。
  • 不可重复读：T1读取一个数据，T2对该数据做了修改。如果T1再次读取这个数据，此时读取的结果和第一次读取的结果不同。
  • 幻读：T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

数据库隔离级别

  • READ-UNCOMMITTED（读取未提交）：可以读取到未提交数据，事务回滚后，读取数据实际并不存在。
  • READ-COMMITTED（读取已提交）：只能读取已提交数据，但是会导致当前事务读取的数据前后值不一样。
  • REPEATABLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。
  • SERIALIZABLE（可串行化）：所有事务操作依次顺序执行。

MySQL中索引实现有哪些数据结构

  • Hash算法
  • 平衡二叉树
  • 红黑树
  • B树
  • B+树

给你一条慢SQL，你该怎么做

加索引需要注意什么

  • 前导模糊查询无法命中索引
  • 数据类型隐式转换无法命中索引
  • 复合索引如果查询条件不包括索引最左边的字段，不能命中索引
  • 负向条件无法命中索引
  • 执行计算不会命中索引

最左前缀优先

复合索引如果查询条件不包括索引最左边的字段，不能命中索引。

有哪些索引

单列索引（普通、唯一、主键）、组合索引、全文索引和空间索引。

MVCC是什么（博客园）

MVCC（Multi-Version Concurrency Control）多版本并发控制，是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

MVCC是为了解决什么

  • MYSQL事务型存储引擎（InnoDB）不使用一种简单的行锁机制，和MVCC–多版本并发控制来一起使用。
  • 锁机制可以控制并发操作，但是其系统开销较大，而MVCC可以在大多数情况下代替行级锁，使用MVCC，能降低其系统开销。

MVCC具体实现

通过在每行记录后面保存两个隐藏的列来实现的，这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。

如何判断一个查询是慢查询（如何定位慢查询）

执行一条mysql查询语句，查看运行时间。

如何将慢查询定位到日志中

设置慢查询时间，启动mysql的时候指定记录慢查询，执行查询语句如果是慢查询会生成一个日志文件。

SQL语句优化（博客园）

  1. 在表中建立索引，优先考虑where、group by使用到的字段。
  2. 尽量避免使用select *，返回无用的字段会降低查询效率。
  3. 尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。
  4. 尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。
  5. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。
  6. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。
  7. 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。
  8. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。

为什么使用limit效率低

在MySQL中，LIMIT OFFSET偏移量特别大时，效率会非常低！如果说LIMIT 1000，10，一个偏移量很小的值，一般是没有问题的。但是，比如说，LIMIT 10000000，10时就有些费劲了，让你等到花儿都谢了是没有问题的。这到底是为啥子呢？究其原因，是因为MySQL的查询并非先跳过10000000条，再查询10条，而是先查询再跳过。所以上面例子，要先查询出10000000行之后，再取10条，速度当然很慢，并且跳过的行数越多，会越慢。

MySQL如何优化

  • 表的设计合理化(符合3NF)。
  • 添加适当索引(index) 。
  • SQL语句优化。
  • 分表分库技术(水平拆分、垂直拆分)。
  • 进行读写分离。
  • 编写存储过程。
  • 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小]。
  • mysql服务器硬件升级。
  • 定时的去清除不需要的数据，定时进行碎片整理。

SQL Select语句完整的执行顺序

from->where->group by→having→select→order by。

left join和inner join的区别

  • 内连接：查询的是两张表的交集部分。
  • 左外连接：查询的是左表所有数据以及其交集部分。
  • 右外连接：查询的是右表所有数据以及其交集部分。

防止sql注入的方式

  • 预编译语句：如，seletc * from user where username = ?，sql语句语义不会发生改变，sql语句中变量用?表示，即使传递参数时为”admin or ‘a’ = ‘a’”，也会把这整体当作一个字符去查询。
  • Mybatis框架中的mapper方式中的#也能很大程度的防止sql注入（$无法防止sql注入）。

Mysql性能优化

  1. 当只要一行数据时使用limit 1。
  2. 选择正确的数据库引擎。
  3. 用not exists代替not in。
  4. 对操作符的优化，尽量不采用不利于索引的操作符。

必看sql面试题

sql面试题（学生表_课程表_成绩表_教师表

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

  • count(1)和count(*)：统计所有记录的条数，包括那些为null的记录。
  • count(字段)：统计该字段不为null的记录条数。

mysql给性别建立索引 和 直接查询 有区别吗

性别字段为什么不适合建索引

访问索引需要付出额外的IO开销，从索引中拿到的只是地址，真正访问数据还要对表进行一次IO。如果从100万条数据中取出几个数据，利用索引迅速定位，访问索引的IO开销就非常值。如果从100万条数据中取出50万条数据，需要访问索引50万次，访问表50万次，加起来的开销不会比全表扫描要小。

mysql中联合索引是如何存储的

工作中用好 MySQL 的联合索引，你需要注意这些“手段”！

联合索引的存储结构也是一颗b+树，树中数据项的key包含多个字段的值，叶子节点根据复合索引的多个字段顺序排列。

explain执行计划

MySQL explain 详解