mysql常见面试题

1. 数据库三大范式
答：（1）表中的字段不可再分
        (2) 前提需要满足第一范式，一个表必须有一个主键，非主键列必须完全依赖主键，而不是主键的一部分
       (3) 前提必须满足第二范式，非主键列必须直接依赖于主键列，而不是存在依赖传递
    第二范式强调完全依赖主键而不是依赖一部分，第三范式强调直接依赖主键而不是传递依赖 

2. mysql cpu飙升怎么处理？
答：怎么确认mysql造成的cpu飙升？  可以通过top/htop观察mysqld进程的cpu占用率是否过高

3. 哪些原因可能造成cpu飙升？  
答：
慢查询： sql语句或者索引设置不合理；
mysql配置不当：缓存过小、连接数过多
硬件资源限制：CPU增加核心数，内存不够
mysql版本过低

4. 如何解决cpu飙升？
答：查询语句不当：定期清理数据库表中无用的数据，避免使用select *，不必要的子查询，explain看执行计划是不是慢查询
   索引不当：没有索引创建索引，过多索引避免不必要的索引，减少维护成本
   配置不当：配置修改缓存大小，    

5. 如何定位以及优化SQL语句性能的问题
答：定位：通过慢查询日志：找出执行时间超多一定事件的SQL语句；explain分析执行计划：索引，SQL语句
   优化：表设计是否合理，索引是否合理，数据量过大

6. 什么是视图？为什么使用视图？
答：视图是一种虚拟表，不包含数据，通过sql语句定义。通过视图可以查看基表的部分或者全部内容
    使用视图可以完成权限控制和增强数据安全性，简化查询，通过视图可以把逻辑模型和物理模型分离，有些情况使用视图还能优化性能

7. SQL语句在mysql是如何执行的？
答：连接器进行连接，会校验用户信息，通过后进入分析器，调用语法分析生成语法树。接下来查询缓存（select），命中直接返回，否则继续执行。优化器制定执行计划，选择成本最小的计划执行。执行器根据执行计划通过存储引擎操作数据并返回客户端。写操作会进行undolog的写入，记录历史版本信息防止将来需要回滚。写入redolog、bin log、提交事务。

8. 什么是索引？
答：索引是一种有序的存储结构，按照单个或者多个值进行排序，对应一个B+树。
   索引的常用场景：where / group by  /order by 
   区分度不高、经常修改的列，数据量非常少，不要去创建索引

9. 索引失效的场景？
答：左模糊，索引参与运算（索引列使用函数、索引列进行表达式计算、隐式转换）、where 或者or非索引字段

10. mysql索引的目的和代价
答：目的：提升检索效率。代价：需要占用存储空间，每个表索引不要超过6个；每个索引都是个B+树，因此需要在增删改的时候进行维护。

11. 主建索引和唯一索引的区别？
答：主键索引：非空唯一索引，一个表只能有一个主键索引，主键索引包含所有行信息
    唯一索引：值不重复，但是可以为空，只有索引列和主键的值

12. 聚集索引和辅助索引的区别？
答：主键索引对应的就是聚集索引， 非主键索引就是辅助索引。
数据存储：聚集索引B+树叶子结点存储所有行信息，辅助索引对应的B+树叶子结点只存储索引信息和主键信息
获取数据：聚集索引只需要通过一个B+树就能获取相应的信息，辅助索引则需要2个

13. 什么是覆盖索引？原理是什么？
答：直接通过辅助索引B+树就能够获取的要查询的值，而无需通过回表查询获取
select x from table where condition
如果x之间列出的字段全部都在辅助索引B+树中，condition条件中只有某个辅助索引的列.
作用是减少磁盘IO次数

14. undolog和redolog有什么区别？
答：undolog实现原子性中的回滚，以及隔离性中的mvcc。Undo Log是一种重要的日志类型，它记录了如何撤销未提交事务对数据库所做的修改。
redolog记录了数据页的修改，系统崩溃或故障发生后，能够快速且可靠地恢复数据库到一致状态。buffer pool是基于内存的，宕机会导致数据丢失。
redolog的作用就是确保buffer pool提交失去的数据安全性。

undolog记录某次事务完成后的数据状态，记录的是更新之前的值。redolog记录的是某个事务开始前的数据状态，记录更新之后的值

15. 数据库事务的四个特性和含义
答：原子性：事务操作要么都做要么都不做
   一致性：事务前后数据保持一致状态。包括完整数据约束检查，逻辑上的一致
   隔离性：事务之间的影响程度；设置多个隔离级别用来提高并发性能。隔离性会破坏一定程度的一致性，来提升并发性能
   持久性：事务完成，数据变更被存储记录，确保事务能够恢复

16. 什么是隔离级别？
答：隔离级别是事务之间互相影响的程度；数据库通过设置多个隔离级别，适度破坏隔离一致性，来提升数据库的并发性能
   读未提交：最低级别的隔离，允许一个事务可以读取另一个事务未提交的数据，可能导致脏读、不可重复读和幻读
   读已提交：保证一个事务只能读取到已经提交的数据，避免了脏读，但仍可能出现不可重复读和幻读。
   可重复读：保证在同一事务内多次读取同样的数据时，结果始终一致，避免了脏读和不可重复读，但仍可能出现幻读。
   序列化：提供最高级别的隔离，通过强制事务串行执行来避免脏读、不可重复读和幻读，但性能较差，因为会限制并发性。

17. 脏读、幻读、不可重复读出现的原因
答：脏读：一个事务读取了另一个事务未提交的数据
   幻读：指一个事务在读取某个范围的数据时，另一个事务在该范围内插入了新的数据，导致第一个事务在后续读取时发现了“幻影”行
   不可重复读：指一个事务在相同条件下多次读取同一行数据时，得到了不同的结果。

   幻读：除了序列化其他隔离级别都会产生，可以把快照读改成当前读，不改变隔离等级的情况下可以加上gap锁
   不可重复读：读已提交和读未提交会产生，只能提高隔离等级到序列化

18.  SQL注入
答：用户在提交查询请求时，把恶意SQL语句作为参数嵌入原来的SQL语句中，达到通过绕过授权直接操作数据的目的.
   可以使用预编译、字符串正则表达式匹配、函数过滤转译

本博客由挨踢零声赞助