MySQL数据库大总结

折叠伞Y
175 阅读8分钟

一、SQL基础

  1. MySQL中主键与外键的作用？有哪些区别？
    • 主键用于确定唯一标识（只能有一个，不能重复不能为空）；外键用于与另一张表的关联（可以有多个，可重复可为空）
  2. 说说left join, inner join, outer join区别

image.png

  1. 说一说数据库的内连接和外连接
    • 内连接：即取两表中都有的记录
    • 外连接：连接两个表，并保留其中一个表中没有匹配的行。
  2. 分表查询怎么做
    • 对大表分片/分区。
  3. MySQL怎么做分页
    • limit关键字、offset关键字
  4. MySQL数据库备份命令
    • mysqldump

二、存储引擎、数据库架构

  1. 关系型数据库和非关系型数据库区别，优缺点？
    • 关系型数据库（Oracle、MySQL、SQL Server）：二维表结构组成（行—实体，列—实体的属性）。用SQL语言操作
    • 非关系型数据库（Redis、MangoDB、HBase）：采用键值对、文档、图形等来存储数据。用多种编程语言开发
  2. 一条SQL查询语句是如何执行的？
  3. MySQL数据类型有哪些（数值型、字符型、日期时间型、二进制数据类型等）
  4. MySQL支持哪些存储引擎？默认使用哪个？
  5. MyISAM和InnoDB的区别，如何选择？
  6. 数据库三大范式
    • 第一范式：强调原子性
    • 第二范式：非主键完全依赖于主键（不能是主键的一部分）。
    • 第三范式：每列都和主键列直接相关（主键列！直接！）

三、索引、锁、事务

1、索引

索引概述

  1. 索引概念及作用
  2. 索引类型
  3. 索引优缺点
  4. 索引在项目中的使用方式
    • 缓存、分布式锁、消息队列、延迟队列
  5. 优化器选择不使用索引的情况
    • 范围查找、JOIN链接操作等
  6. 怎么查看MySQL语句有没有用到索引？

聚簇索引与非聚簇索引

  1. 聚簇索引与非聚簇索引的定义及区别？
  2. 为什么官方建议使用自增主键作为索引？
    • 减少分裂和移动的频率
  3. 普通索引上存储的是什么？

覆盖索引和回表

  1. 什么是覆盖索引？
    • 直接从索引获取数据，无需回表进行额外操作
    • 优点：减少大量IO操作（大小小于聚集索引）、性能比回表查询高（只扫描一次）
  2. 如何查看是否使用了覆盖索引？
  3. 覆盖索引的局限
    • 占磁盘空间，性能下降。
  4. 什么是回表？
    • 一次索引不能获取所有信息
  5. 回表对查询性能的影响是什么？
    • 增加额外IO操作、CPU和磁盘负担、网络流量、延迟
  6. 如何避免回表
    • 使用覆盖索引、使用join代替子查询
  7. 聚簇索引需要回表吗
  8. 非聚簇索引一定会回表查询吗？

联合索引

  1. 什么是联合索引
  2. 什么时候使用联合索引
  3. 联合索引有什么需要注意的吗
    • 字段顺序
    • 避免使用多个字段
    • 避免使用null值
  4. 为什么联合索引的字段顺序很重要
  5. 联合索引范围查询
  6. 场景题：如果你创建一个索引的话，比如一个字一个表里面有 a b c 三个字段，对 b 和 c 这两个字段去做一个索引，顺序的话是先 b 或后 c，那这时候我需要去查 c 这个字段能用上这个索引么？

前缀索引

  1. 什么是前缀索引
    • 对文本前几个字符建立索引
  2. 写一个前缀索引语句

最左匹配原则

  1. 什么是最左匹配原则
  2. 联合索引为什么不遵循最左匹配原则会失效？
    • 联合索引的B+树中键值是排好序的
  3. 哪些情况下MySQL不符合最左匹配原则但走了索引？
  4. MySQL不符合最左匹配原则但走了索引是为什么？
    • MySQL优化器做出的选择，使用这个索引比全表扫描高效，就使用。

索引下推

  1. 了解索引下推吗
    • 非聚簇索引遍历过程中，做判断，过滤掉不符合条件的记录，减少回表次数

正确使用索引的一些建议

  1. 什么时候适用索引？
  2. 什么时候不适合使用（创建）索引？
  3. 如何选择在哪些列上建索引
  4. 选择合适的字段创建索引
  5. 索引为什么会失效？

索引优化

  1. 有什么优化索引的方法？
    • 前缀索引优化，覆盖索引优化，主键索引最好是自增的，防止索引失效

其他

  1. 模糊查询（%abc 和 abc%）是否用到索引
  2. SQL表里面有学生姓名，语文成绩、数学成绩、英语成绩，查询平均分最高的三个同学？如何优化？
  3. A、B、C三列怎么设计索引
  4. 普通索引和唯一索引怎么选？

2、事务

  1. 什么是MySQL事务？（定义、ACID特性、使用场景、隔离级别等）
  2. ACID靠什么保证？
    • 原子性：重做日志(redo log)和撤销日志(undo log)
    • 一致性：约束检查
    • 隔离性：不同的锁机制
    • 持久性：多种机制（缓冲区、写前日志等）
  3. MySQL事务的隔离级别有哪些？它们之间有什么区别？
    • 读未提交
    • 读已提交
    • 可重复读（默认）
    • 串行化
  4. 并发事务带来了哪些问题？
    • 一致性问题、更新丢失、脏读、不可重复读、幻读
  5. 什么是脏读、不可重复读和幻读？如何避免这些问题？
  6. MySQL事务如何数据恢复？

2、锁

  1. 什么是MySQL锁？
  2. MySQL锁分类（锁粒度、锁模式、加锁机制、兼容性）
  3. 表级锁和行级锁
    • 表级锁：MySQL中锁定粒度最大的一种锁，针对非索引字段。和存储引擎无关
    • 行级锁：锁定粒度最小，针对索引字段。和存储引擎有关，在存储引擎层面实现的
  4. 共享锁和排他锁
    • 共享锁（S锁，读锁）
    • 排他锁（X锁，写锁/独占锁）
    • 排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容
  5. 意向锁有什么作用？
    • 为了支持InnoDB的多粒度锁，解决表锁和行锁共存的问题。
    • 无意向锁，得遍历所有数据判断有无行锁。有意向锁（这个表级锁），可直接判断是否有行被锁定。
  6. MyISAM和InnoDB之间的锁机制有什么不同？
  7. 如何使用InnoDB实现表级锁
  8. 乐观锁和悲观锁
    • 悲观锁：一个事务拿到悲观锁后，其他任何事务都不能修改该数据。行锁、表锁、读锁、写锁均为悲观锁。
    • 乐观锁：在更新数据时才进行版本检查，而不是在读取数据时就进行锁定。
  9. MySQL中如何加行锁？
  10. MySQL遇到过死锁问题吗，你是如何解决的？
    • 查看死锁日志show engine innodb status;
    • 找出死锁sql
    • 分析sql加锁情况
    • 模拟死锁案发
    • 分析死锁日志
    • 分析死锁结果
  11. 如何避免死锁

四、MySQL性能优化

  1. 线上SQL调优经验？
    • slow_query_log日志+explain、减少索引扫描行数、建立联合索引、虚拟列+联合索引
  2. 如何做索引优化？
    • 分页优化、尽量使用覆盖索引、SQL优化、设计优化、硬件优化、使用force index()
  3. 如何定位慢查询？
    • 开源工具（Arthas、Prometheus、Skywalking）、MySQL自带日志
    • 手动开启日志。先在(/etc/my.cnf)中配置两项功能：slow_query_log=1、long_query_time=2，配置完使用/var/lib/mysql/localhost-slow. log指令
  4. SQL语句执行很慢，如何分析呢？
  5. SQL慢查询原因
    • 没有用到索引、表数据量太大、优化器选错了索引
  6. SQL查询偶尔慢会是什么原因？
    • 数据库在刷新脏页、没有拿到锁
  7. MySQL慢查询怎么优化？
    • 合理设计索引、索引优化、SQL优化、适时清理数据、建立分区、优化查询语句、使用缓存、读写分离、数据异构到es、冷热数据分离、升级数据库类型
  8. 有哪些常见的SQL优化手段？
    • 索引优化、查询优化、数据表优化、数据库参数优化、数据库架构优化、数据库连接优化
  9. 索引下推
    • 可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数
  10. 如何分析SQL的性能
    • 执行计划分析、监控查询日志、监控系统资源使用情况、性能分析工具、压力测试工具
  11. 大表中有一条SQL语句查询比较慢，怎么排查？
  12. MySQL的性能调优方法？ - 表结构和索引优化、SQL语句优化、MySQL参数优化、硬件及系统配置
  13. 缓存与数据库不一致怎么解决？
    • 设计缓存过期时间、强制刷新缓存、使用缓存和数据库双写、使用分布式锁

五、日志

avatar
文章
阅读
粉丝