MySQL面试题总结

MySQL1. 数据库事务的 ACID 特性原子性Atomicity事务中的操作要么全部成功要么全部失败。事务是一个不可分割的单元要么全部执行要么全部回滚。如果事务中的任何操作失败所有操作都将被回滚到事务开始之前的状态以保证数据的一致性。一致性Consistency事务的执行应使数据库从一个一致性状态转移到另一个一致性状态。在事务开始和结束时数据库的完整性约束应得到满足确保数据的正确性和一致性。隔离性Isolation每个事务在执行过程中都应该与其他事务隔离。并发事务的执行应当互不干扰每个事务应该感知不到其他事务的存在或并发执行。隔离级别定义了不同事务之间的可见性和互相影响的程度。持久性Durability一旦事务提交成功其对数据库的修改应该永久保存即使系统发生故障或重启也应该能够保持数据的持久性。最简回答ACID 特性是指原子性Atomicity、一致性Consistency、隔离性Isolation和持久性Durability用于保证数据库事务的可靠性和一致性。2. MySQL 存储引擎特性比较事务并发外键备份崩溃恢复其他InnoDB事务型行级锁支持在线热备概率低聚簇索引, MVCC特性MyISAM非事务型表级锁不支持不支持慢, 易丢失压缩表, 空间数据索引MySQL 默认是 InnoDB 存储引擎适合比较庞大的应用场景。InnoDBMySQL 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性InnoDB 还支持外键约束。MyISAMMyISAM 既不支持事务、也不支持外键其优势是访问速度快但是表级别的锁定限制了它在读写负载方面的性能因此它经常应用于只读或者以读为主的数据场景。最简回答InnoDB 是 MySQL 的默认存储引擎支持事务处理、行级锁和外键而 MyISAM 不支持事务、只有表级锁并且不支持外键。3. 数据库的事务隔离级别SQL 标准定义了四个隔离级别READ-UNCOMMITTED (读取未提交)最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读、幻读或不可重复读。READ-COMMITTED (读取已提交)允许读取并发事务已经提交的数据可以阻止脏读但是幻读或不可重复读仍有可能发生。REPEATABLE-READ (可重复读)对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生。SERIALIZABLE (可串行化)最高的隔离级别完全服从 ACID 的隔离级别。所有的事务依次逐个执行这样事务之间就完全不可能产生干扰也就是说该级别可以防止脏读、不可重复读以及幻读。隔离级别脏读不可重复读幻读READ-UNCOMMITTED√√√READ-COMMITTED×√√REPEATABLE-READ××√SERIALIZABLE×××注√ 表示可能出现该问题× 表示可以避免该问题事务隔离级别越严格数据库效率越低。MySQL 默认的事务隔离级别是REPEATABLE-READ 可重复读 级别简称 RR 级别会出现 幻读 问题。最简回答数据库事务隔离级别是指在多个并发事务同时执行时各个事务之间的隔离程度常见的隔离级别有读未提交 (Read Uncommitted)、读已提交 (Read Committed)、可重复读 (Repeatable Read) 和串行化 (Serializable)。4. 索引的类型种类从数据结构角度树索引 (O(log(n)))Hash 索引从物理存储角度聚集索引 (clustered index)非聚集索引 (non-clustered index)从逻辑角度普通索引唯一索引主键索引联合索引5. [热] 索引会失效的情况有哪些下面列举几种不走索引的 SQL 语句索引列参与表达式计算当索引列在 WHERE 条件中参与了数学运算时索引会失效。SELECTsnameFROMstuWHEREage1030;函数运算当索引列作为函数的参数使用时索引会失效。SELECTsnameFROMstuWHERELEFT(date,4)1990;%词语%-模糊查询当使用LIKE进行模糊查询时如果通配符%出现在搜索词的开头索引会失效如果%仅在结尾则可以走索引。SELECT*FROMmanongWHEREunameLIKE码农%-- 走索引SELECT*FROMmanongWHEREunameLIKE%码农%-- 不走索引字符串与数字比较不走索引当字符串类型的字段与数字进行比较时会发生隐式类型转换导致索引失效。CREATETABLEa(achar(10));EXPLAINSELECT*FROMaWHEREa1-- 走索引EXPLAINSELECT*FROMaWHEREa1-- 不走索引同样也是使用了函数运算查询条件中有OR如果WHERE条件中包含OR即使其中部分条件带有索引也可能不会使用。换言之要求OR连接的所有字段都必须建立索引才可能生效。select*fromdeptwherednamexxxorlocxxordeptno45;正则表达式不使用索引6. [热] MySQL 索引底层原理MySQL 的索引底层结构是B 树。B 树的特点B 树是一种平衡多路搜索树具有以下核心特点叶子节点存储数据所有关键字都保存在叶子节点中。叶子节点之间通过链表连接形成一个有序的序列。非叶子节点仅存索引非叶子节点只存储索引字段的值和指向子节点的指针不保存实际数据。这使得单个节点能容纳更多关键字从而降低树的高度加快搜索速度。数据指针叶子节点不仅包含索引字段的值还包含指向对应实际数据的指针。在 B 树索引中每个节点的大小是固定的通常与磁盘页的大小如 16KB 或 32KB相当这有助于优化磁盘 I/O。索引查询快的原因路径长度短B 树是平衡的所有叶子节点的深度相同。查询时只需要进行几次磁盘 I/O 操作相当于树的高度即可定位到数据速度很快。顺序访问优势叶子节点通过链表连接且关键字有序。对于范围查询如BETWEEN、、可以通过顺序扫描叶子节点链表来高效获取数据避免了大量的随机 I/O。最小化磁盘 I/OB 树具有较高的填充因子每个磁盘页节点能存储大量关键字这显著减少了查询过程中所需的磁盘 I/O 次数。总结B 树的平衡性、有序的叶子节点链表、以及对磁盘 I/O 的优化共同构成了 MySQL 索引高效查询的基础。通过这种结构数据库可以大幅减少磁盘访问次数显著提升查询性能。最简回答MySQL 索引底层使用B 树数据结构。它是一种平衡树非叶子节点只存索引叶子节点存数据并通过链表连接。这种结构通过减少磁盘 I/O 次数和支持高效的范围查询实现了快速的数据定位和检索。7. 聚集索引与非聚集索引InnoDB 聚集索引的叶子节点存储行记录因此 InnoDB必须要有且只有一个聚集索引1如果表定义了PK则PK就是聚集索引2如果表没有定义PK则第一个not NULL unique列是聚集索引3否则InnoDB会创建一个隐藏的row-id作为聚集索引所以PK查询非常快直接定位行记录。聚集索引聚集索引又叫聚簇索引所有的数据都存在聚集索引上叶子节点直接对应数据从中间级的索引页的索引行直接对应数据页。innodb 存储引擎中的主键默认就会创建一个聚集索引每张表只能创建一个聚集索引。记录的索引顺序与物理顺序相同更适合between and 和 order by 操作。非聚集索引非聚集索引又叫非聚簇索引、辅助索引所有的数据和索引的目录是分开存的叶子节点不存放具体的整行数据叶子结点不直接指向数据页而是存储的这一行的主键的值。记录的索引顺序与物理顺序无关。每张表可以有多个非聚集索引需要更多磁盘和内存多个索引会影响insert和update的速度。举例表中有四条记录1, shenjian, m, A3, zhangsan, m, A5, lisi, m, A9, wangwu, f, B两个B树索引分别如上1图和2图1id为PK聚集索引叶子节点存储行记录2name为KEY非聚集索引叶子节点存储PK值即id8. MySQL 回表查询既然从普通索引无法直接定位行记录那普通索引的查询过程是怎么样的呢通常情况下需要扫描两遍索引树。例如select*rom twherenamelisi;如粉红色路径需要扫码两遍索引树1先通过普通索引定位到主键值id52在通过聚集索引定位到行记录这就是所谓的回表查询先定位主键值再定位行记录它的性能较扫一遍索引树更低。注意什么是覆盖索引只需要在一棵索引树上就能获取SQL所需的所有列数据无需回表速度更快。比如select id,name from user where name‘shenjian’;9. MySQL 最左匹配原则MySQL联合索引遵循最左前缀匹配原则即最左优先查询的时候会优先匹配最左边的索引。例如当我们在 (a,b,c) 三个字段上创建联合索引时实际上是创建了三个索引分别是(a)、(a,b)、(a,b,c)。查询条件中包含这些索引的时候查询就会用到索引。例如下面的查询条件就可以用到索引1select*fromtable_namewherea?;2select*fromtable_namewherea?andb?;3select*fromtable_namewherea?andb?andc?;其他查询条件不包含这些索引的语句就不会用到索引例如1select*fromtable_namewhereb?;2select*fromtable_namewherec?;3select*fromtable_namewhereb?andc?;如果查询条件包含(a,c)也会用到索引相当于用到了(a)索引。10. count(\*)、count(1)、count(字段)的区别count(\*) 是直接统计所有记录的行数无需对任何列进行计算因此最快。count(1) 比 count(列名) 快的原因是1 是一个常量不需要计算而 count(列名) 需要对指定列进行计算增加了额外的计算开销。count(字段) 在实际情况中却经常比 count(\*) 和 count(1) 慢因为还需要对指定列进行计算并且如果指定的列含有 NULL 值就会被忽略掉影响了统计的准确性。结论效率由高到低排序a. count(\*) 最快b. count(1) 快c. count(字段) 慢11. [热]MySQL优化方案服务器优化增加CPU、内存、网络、更换高性能磁盘表设计优化字段长度控制、添加必要的索引SQL优化避免SQL命中不到索引的情况架构部署优化一主多从集群部署分库分表垂直分库、水平分表编码优化实现读写分离12. [热]SQL优化方案1. 优化查询条件使用索引确保所有涉及到的列都有适当的索引。SELECT*FROMtableWHEREcolumnvalue;CREATEINDEXidx_columnONtable(column);避免模糊查询%开头的通配符会使索引失效尽量避免在查询条件中使用以%开头的LIKE语句。SELECT*FROMtableWHEREcolumnLIKEvalue%;2. 使用合适的数据类型使用最小可能的数据类型选择最合适的数据类型不要使用比实际需要更大的数据类型。CREATETABLEexample(column1INT,column2VARCHAR(50));避免使用存储过大的数据类型避免使用TEXT、BLOB等存储过大的数据类型因为它们会占用更多的存储空间和I/O操作。3. 减少查询次数使用JOIN查询通过优化JOIN语句避免使用多个单表查询。SELECT*FROMtable1INNERJOINtable2ONtable1.idtable2.id;使用批量操作合并多个相似的操作为一个更大的操作减少多次查询和事务提交。INSERTINTOtable(column1,column2)VALUES(value1,value2),(value3,value4),...;4. 优化索引检查索引使用情况通过EXPLAIN或其他性能分析工具检查查询是否使用了适当的索引。EXPLAINSELECT*FROMtableWHEREcolumnvalue;删除不必要的索引移除未使用或被其他索引覆盖的冗余索引减少索引维护的开销。5. 避免使用SELECT *明确列出所需的列只选择需要的列避免不必要的数据传输和处理。SELECTcolumn1,column2FROMtable;13. 如何设计数据库表数据库设计规范根据业务模块拆分数据库业务模块垂直分库同一业务模块的表在一个数据库里表设计规范表名、字段名全部小写英文带下划线字段类型长度根据实际需求选择设计基础字段主外键ID、时间、逻辑删除、版本添加必要冗余字段添加必要索引常用于查询的单字段或组合索引单表索引建议控制在5个以内规划分表单表超500万行或容量超2G才分表14. MySQL都有哪些函数数字函数聚合函数avg、sum、count、min、min绝对值ABS(x)取余MOD(x,y)日期函数系统当前时间NOW()日期转字符串DATE_FORMAT(date,format)字符串转日期STR_TO_DATE(str,format)日期间隔天数DATEDIFF(date1,date2)字符串函数拼接字符串CONCAT(s1,s2…sn)截取字符串SUBSTR(s, start, length)替换字符串REPLACE(s,s1,s2)15. MySQL 三大日志文件MySQL中总共包括三类日志文件redolog, undolog, binlogbin log 归档日志二进制日志作用用于复制在主从复制中从库利用主库上的binlog进行重播实现主从同步。用于数据库的基于时间点的还原。内容逻辑格式的日志可以简单认为就是执行过的事务中的sql语句。但又不完全是sql语句这么简单而是包括了执行的sql语句增删改反向的信息也就意味着delete对应着delete本身和其反向的insertupdate对应着update执行前后的版本的信息insert对应着delete和insert本身的信息。MySQL 的 二进制日志 (binlog)默认的有效期是永久的也就是不会自动删除。通常需要手动定期清理旧的binlog文件以避免占用过多的磁盘空间。binlog有三种模式Statement基于SQL语句的复制、Row基于行的复制以及Mixed混合模式redo log 重做日志作用确保事务的持久性。防止在发生故障的时间点尚有脏页未写入磁盘在重启mysql服务的时候根据redo log进行重做从而达到事务的持久性这一特性。内容物理格式的日志记录的是物理数据页面的修改的信息其redo log是顺序写入redo log file的物理文件中去的。undo log 回滚日志作用保存了事务发生之前的数据的一个版本可以用于回滚同时可以提供多版本并发控制下的读MVCC也即非锁定读内容逻辑格式的日志在执行undo的时候仅仅是将数据从逻辑上恢复至事务之前的状态而不是从物理页面上操作实现的这一点是不同于redo log的。最简回答Redo log(重做日志)用于保证事务的持久性它在事务提交前将数据写入磁盘以防止数据丢失。Undo log(回滚日志)用于事务的回滚操作它记录了事务对数据的修改以便在事务回滚时进行数据恢复。Binlog(二进制日志)记录了数据库的所有修改操作包括对数据的增删改操作用于数据恢复、复制和灾难恢复。Redo log和Undo log是InnoDB存储引擎特有的日志而Binlog是MySQL服务器层的日志。