MySQL

发表于 | 更新于 | 分类于 | 次阅读
MySQL数据库集锦

1.数据库三范式

  • 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
  • 第二范式（2NF）：首先满足1NF。另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
  • 第三范式（3NF）：任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

2.ACID

  • Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
  • Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  • Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3.char和varchar的区别

  • char(n) ：固定长度类型。比如订阅 char(10)，当你输入”abc”三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。优点：效率高；缺点：占用空间。适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

  • varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varchar 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

4.内连接、左连接、右连接

　　内连接关键字：inner join；左连接：left join；右连接：right join。

　　内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

5.MySQL索引

　　索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

　　具体来说 MySQL 中的索引，不同的数据引擎实现有所不同。但目前主流的数据库引擎的索引都是 B+ 树实现的。B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

为什么MySQL数据库索引选择使用B+树

6.数据库的事务隔离

　　MySQL的事务隔离是在 MySQL.ini配置文件里添加的，在文件的最后添加：transaction-isolation = REPEATABLE-READ

　　可用的配置值：READ-UNCOMMITTED（未提交）、READ-COMMITTED（读提交）、REPEATABLE-READ（可重复读）、SERIALIZABLE（串行化）。

  • read-uncommitted：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
  • read-committed：读提交，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
  • repeatable-read：可重复读，默认级别，保证多次读取同一个数据时。其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
  • serializable：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

　　脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

　　不可重复读 ：是指在一个事务内，多次读同一数据。

　　幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

7.MySQL常用引擎

  • InnoDB引擎：InnoDB 引擎提供了对数据库 ACID事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的，所以在并发度较高的场景下使用会提升效率的。

  • MyIASM引擎：MySQL的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

8.MySQL行锁和表锁

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

  • 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
  • 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

9.乐观锁和悲观锁

  • 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
  • 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

　　数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1。这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

10.MySQL问题排查方法

  1. 使用show processlist命令查看当前所有连接信息。
  2. 使用explain命令查询 SQL 语句执行计划。
  3. 开启慢查询日志，查看慢查询的 SQL。

11.MySQL性能优化

  1. 为搜索字段创建索引。
  2. 避免使用 select *，列出需要查询的字段。
  3. 垂直分割分表。
  4. 选择正确的存储引擎。
谢谢你请我吃糖果！
0%