提纲：

🔥优化

  • 数据库

  • 索引

  • 表

  • Explain语句解析 SQL 语句（性能从高到底）

🎈面试八股真题（27个）

  • 1、数据库的三范式是什么

  • 2、MySQL数据库引擎有哪些

  • 3、说说InnoDB与MyISAM的区别

  • 4、数据库的事务

  • 5、索引是什么

  • 6、SQL优化手段有哪些

  • 7、简单说一说drop、delete与truncate的区别

  • 8、什么是视图

  • 9、什么是内联接、左外联接、右外联接？

  • 10、并发事务带来哪些问题?

  • 11、事务隔离级别有哪些?MySQL的默认隔离级别是?

  • 12、大表如何优化？

  • 13、分库分表之后,id 主键如何处理？

  • 14、说说在 MySQL 中一条查询 SQL 是如何执行的？

  • 15、为什么 SELECT COUNT(*) FROM table 在 InnoDB 比MyISAM 慢？

  • 16、说说 InnoDB 与 MyISAM 有什么区别？

  • 17、MySQL 索引类型有哪些？

  • 18、什么时候不要使用索引？

  • 19、说说什么是 MVCC？

  • 20、MVCC 可以为数据库解决什么问题？

  • 21、说说 MVCC 的实现原理

  • 22、请说说 MySQL 数据库的锁？

  • 23、说说什么是锁升级？

  • 24、怎样尽量避免死锁的出现？

  • 25、主键和候选键有什么区别？

  • 26、主键与索引有什么区别？

  • 27、MySQL 如何做到高可用方案？

一、优化

1. 数据库

  • 1、数据增删改不频繁的情况下，可以使用查询缓存，并设置较大的 query_cache_size

  • 2、配置全局 InnoDB 引擎缓存大小，即适当增大 innodb_buffer_pool_size

  • 3、若数据库单个事务很大，或是写入非常频繁，可以适当调大 binlog 缓存大小，binlog_cache_size

  • 4、InnoDB 默认每秒进行一次 redo-log 缓存写入磁盘，若相信自己的 MySQL 服务器，可以设置为当redo-log 缓存写满时再写入磁盘，并调大 innodb_log_buffer_size 大小

    ......

  • # 核心思想：MySQL 是 I/O 密集型应用，如何减少 I/O 次数是 MySQL 数据库优化的核心

2.索引

  • 1、经常需要进行前导 like 模糊匹配的，不建索引

  • 2、经常使用负向条件的，如 !=，不建索引

  • 3、使用时，遵守最左前缀法则

  • 4、字段过长的，可以用 prefix 作前缀索引

  • 5、条件查询中 or 字段的条件没有使用索引时，其他索引也会失效

  • 6、查询时发生运算，也会索引失效，最典型的字符串匹配，字符串值不加 ' '，MySQL 会进行类型转换，相当于一次运算

  • 7、利用覆盖索引，可以从索引中直接获取查询的字段，减少回表查询

    ......

3.表

  • 数据类型

    • 1、尽量不要用 DOUBLE 存储小数，有内存和精度问题，可以使用 INT 按固定倍率进行存储

    • 2、尽量不要用 BLOB 数据存放图片等大型文件

    • 3、可以用 ENUM 枚举类型来存储状态值

      ......

  • 表结构

    • 1、适当添加冗余字段，减少多表查询

    • 2、大表拆分

      • 竖直拆分

        • 垂直分表：将一张表的字段拆开分配到多张表中，一般将常用字段放在一个表中，将不常用字段放在另一张表中

        • 垂直分库：将一张表中的字段按照业务分配到不同的表中，适合业务耦合度非常低的场景

        • 优点：垂直分表可以使查询热点字段的效率更高，垂直分库可以使表的结构和逻辑清晰，便于维护

        • 缺点

          • 1、垂直分库分表并没有减少数据数量

          • 2、垂直分库分表需要在服务器代码中对 SQL 语句进行大量修改，并且不可避免的带来了多表查询

          • 3、对于主键和一些所有业务通用的字段，垂直分库分表必然导致这些字段的冗余，从而消耗存储空间

      • 水平拆分

        • 水平分表：在同一个数据库中，把一张表的数据分到多张表进行存储，一般按照主键 ID 进行划分

        • 水平分库：将一张表的数据分到多个数据库中的多张表进行存储，类似于 Redis 分片

        • 优点：水平分库分表解决了单表单库数据存储量上限的问题

        • 缺点

          • 1、对于一些范围查找，会涉及到跨节点的 JOIN 连接，性能会非常差——可以在进行两次查询，在服务端代码实现数据的聚合

          • 2、事务的一致性会变差——可以将一个跨节点的事务分成多个小的单节点事务，或是使用分布式事务

          • 3、进行 MySQL 数据库扩容难度大，不易于维护

4.Explain语句解析 SQL 语句（性能从高到底）

  • 1、ID —— 表示将 SQL 语句划分为多个操作后，每个操作的顺序

  • 2、select_type —— 表示 SQL 语句查询类型

    • Simple：单表查询，如果是多表联查就是多次 Simple

    • Primary & Subquery：表示嵌套查询中的外查询，以及用作 Where 子句中查询条件的子查询

    • derived：表示嵌套查询的子查询用作 From 子句中表的子查询

    • Union Select/Union Result：前者表示 Union 子句后的并列查询，后者表示将两者拼合的操作，效率最低

  • 3、type —— 表示查询过程

    • null：没有进行查询，如 now() 函数获取当前时间

    • system：查询的表中只有一行数据

    • const：通过主键索引进行精确匹配查找

    • eq_ref：通过唯一索引进行精确匹配查找

    • ref：通过索引进行精确匹配查找，结果可能存在多条

    • range：通过索引进行范围查找，一般 sql 优化到这个等级即可

    • Index：遍历索引

    • all：遍历了表

  • 4、possible keys/key：表示可以使用的索引以及执行时真正使用的索引，可以通过 Select Using/Ignore/Force Index() 推荐/忽视/强制使用某个索引

  • 5、Extra：一些额外信息

    • Using Index/Using Index Condition：前者表示覆盖索引，没有进行回表，后者表示只是使用了索引但没有覆盖，进行回表查询

    • Using Filesort：表示没有使用索引的 Order By 排序

    • Using Temporary：表示将查询结果作为中间表进行了排序，如 Order By/Group By 等

二、面试八股真题🎈🎈🎈

1、数据库的三范式是什么

  • 第一范式：列不可再分

  • 第二范式：行可以唯一区分，主键约束

  • 第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束

  • 三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

2、MySQL数据库引擎有哪些

  • 如何查看mysql提供的所有存储引擎： mysql> show engines;

  • mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE

    • MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎

    • Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些

    • Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表

    • MERGE：是一组MYISAM表的组合

3、说说InnoDB与MyISAM的区别

  • 1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

  • 2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

  • 3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  • 4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

  • 5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

4、数据库的事务

  • 什么是事务？

    • 多条sql语句，要么全部成功，要么全部失败。

  • 事务的特性：

    • 原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性

      (Durabiliy)。简称ACID

    • 原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。

    • 一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。

    • 隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰

    • 持久性：一旦