mysql 复习一

1. 数据库事物ACID（原子性、一致性、隔离性、持久性）

• 原子性：事务要么执行成功，要么执行失败回滚
• 一致性：事务执行前和执行后，对数据库的完整性没有破坏，是一致的
• 持久性：事务执行后，对数据库的影响是永久性的
• 隔离性：事务间是相互隔离，互不影响的

2. 脏读、幻读、不可重复读

• 脏读：事务A读取了事务B更新未提交的数据，事务B回滚
• 不可重复读：事务A读取了事务B更新前和更新提交后的数据，两次数据不一致
• 幻读：事务A读取了事务B插入前和插入后提交的数据，两次数据不一致

3. 事物的隔离级别（读未提交、不可重复读、可重复读、可序列化读）

• 读未提交：读写，都不加锁，存在脏读，不可重复读，幻读
• 不可重复读：读不加锁，写加行锁，存在不可重复读和幻读
• 可重复读：读通过读取快照的方式，写加行锁，存在幻读
• 序列化读：对整表加锁，无并发问题

Mysql事务和锁

4. MySQL遇到的死锁问题、如何排查与解决

• 问题：当线程A获取了锁1，又要获取锁2，线程B获取了锁2，又要获取锁1，则出现了死锁
• 排查：SELECT * FROM information_schema.INNODB_TRX查找正在提交的事务
• 解决：1.梳理事务逻辑，避免死锁；2.杀死死锁的进程；3.设置锁的超时时间

5. explain命令

主要关注的字段如下：

• select_type:查询类型，有简单查询，联合查询和子查询
• key：使用的索引
• rows：扫描的行数

6. 索引类别（B+树索引、全文索引、哈希索引），索引的原理和使用场景

• Hash索引：底层数据结构是Hash表，可一次定位到要查询的数据，但是只适用于查询一条记录，且不支持多列联合索引
• B+ Tree索引：底层是B+ 树的结构，每个磁盘的数据项要尽可能小，即索引字段要尽量小，从而提高数据项存储数据的个数，降低树的高低，减少IO次数，从而提高搜索效率，大部分场景使用该索引，可进行范围查询，联合索引等
• 全文索引：用于查找文本中的关键字，并不是直接比较是否相等
• 复合索引：使用多个索引分级的方式进行索引查询，例如要查全国的某个小学生，首先通过地方的索引找到学生所在学校的地区，再通过学校索引找到所在学校，再通过班级索引找到所在班级，最后找到这个学生，复合索引相对于使用单个索引的效率更高，但是会存在索引失效的问题，例如不按顺序或复合索引靠前的字段未出现在查询条件中，都无法正常使用复合索引。

MySQL索引原理以及查询优化
B树和B+树的插入、删除图文详解
联合索引在B+树上的存储结构及数据查找方式

7. 说说 SQL 优化之道

1. 让运维人员对mysql服务器进行内核优化

2. 通过性能测试对配置参数进行调优，如连接数量，缓存大小，锁超时时间

3. 通过设计更好的表结构和查询的SQL语句

   1. 表结构设计
      1. 标识符规范命名，且表字段等都有注释
      2. 列的类型尽量选择较小的，能用int，不用double，尽量使用定长，数据量尽量尽量小，能使用枚举，则不用varchar，从而数据库整体变小，也可提高索引使用效率
      3. 对于 where、group by、order by、多表关联查询的字段建立索引
      4. 在三范式的基础上可适当对表增加常用字段的冗余，从而减少频繁联表查询
      5. 为表设置主键

4. SQL执行优化

   1. 查询使用索引，避免select * 和 like%%，对于前%的情况，可以将字段进行reverse的方式，进行查询
      2. 通过Explain来优化sql
      3. 判断数据是否存在，不使用count，使用select 1 from tb_xxx limit 1;

   2. 查询时不开启事务
      5. 使用插入式更新，如下

   insert into tb_student (stu_no, stu_name) values (1, 'abc') on conflict (stu_no) do update set stu_name =excluded.stu_name;
   

8. 数据库的几大范式

1. 第一范式：数据库的每列都是不可分解的原子值
2. 第二范式：数据库的每列都和主键相关
3. 第三范式：数据库的每列都和主键直接相关

9. 存储引擎的 InnoDB与MyISAM区别，优缺点，使用场景

• InnoDB:1.支持事务和行锁，MyISAM不支持；2.索引和数据存储在一个文件系统中，数据本身就是根据主键形成的B+Tree结构，data值就是数据，而辅助索引的data值则是主键，故辅助索引需要遍历两遍树找到数据；3.不支持全文索引
• MyISAM：1.性能更优，占用存储空间少；2.索引和数据存储在不同的文件系统中，文件可压缩，data的值存储的是数据的引用地址；3.支持全文索引

https://blog.youkuaiyun.com/yupi1057/article/details/81287007)

10. 聚集索引和非聚集索引区别

• 聚集索引：索引排列的方式和表记录排列的方式一致，查询更快，原因是向后不用查多久就能查到，但是插入更新较慢，会对索引重排，效率较低
• 非聚集索引：索引排列的方式和表记录排列的方式不一致，插入更新较快，查询相对较慢

11. PG与mysql对比

• PG优点：
  • 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；
  • 存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；
  • 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；
• mysql优点：
  • MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；
  • MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；

MySQL与 PostgreSQL 数据库功能对比

参考

20个数据库常见面试题讲解