数据库相关笔记

数据库基本数据类型

1. 整数类型
   tinyint，smallint，mediumint，int，bigint
   
2. 浮点数 && 定点数
   float，double，decima(定点数)
   
   注意：

• DECIMAL类型的取值范围与DOUBLE类型相同。
• 注意的是DECIMAL类型的有效取值范围是由M和D决定的。
• 其中，M表示的是数据的长度，D表示的是小数点后的长度。

3. 文本类型

• TINYTEXT - 1个字节(255个字符)
• TEXT - 64KB(65,535个字符) - 约 6万5
• MEDIUMTEXT - 16MB(16,777,215个字符) - 接近1678 多万
• LONGTEXT - 4GB(4,294,967,295个字符) - 接近 43 亿

事务的特点

事务 是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）。

事务的四大特性：

• 原子性 (atomicity）:强调事务的不可分割.

  • 事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

• 一致性 （consistency）:事务的执行的前后数据的完整性保持一致.

  • 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

• 隔离性 （isolation）:一个事务执行的过程中,不应该受到其他事务的干扰

  • 一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持续性 （durability） :事务一旦结束,数据就持久到数据库

  • 也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

事务隔离级别

• DEFAULT （默认）
  使用数据库本身使用的隔离级别
  ORACLE（读已提交） MySQL（可重复读）

• READ_UNCOMMITTED （读未提交）
  这是事务最低的隔离级别，它允许另外一个事务可以看到这个事务未提交的数据。这种隔离级别会产生脏读，不可重复读和幻像读。

• READ_COMMITTED （读已提交）
  一个事务要等另一个事务提交后才能读取数据。这种事务隔离级别可以避免脏读出现，但是可能会出现不可重复读和幻像读。

• REPEATABLE_READ （可重复读）
  开始读取数据（事务开启）时，不再允许修改操作。解决了不可重复读，但不能解决幻读。

• SERIALIZABLE（序列化）
  Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

脏读、不可重复读、幻读

• 脏读：
  一个事务可以读取另一个未提交事务的数据

  • 如：事务A开启进行了查询数据，同时事务B开启，修改了其中一笔数据，但并未提交，这时事务A又进行了查询，这时就有两笔不一样的数据，然后事务B并没有结束，事务B进行了事务回滚，这样事务A就读取了事务B修改后未提交的数据。因为这里出现这种根本原因是未对数据进行提交，就进行了读取。
  • 需进行 读已提交（Read Committed）就能解决

• 不可重复读：一个事务进行读取，分别读取到了不同的数据——着重数据修改/删除

  • 如：事务A对数据进行查询，这时事物B开启，对其中一笔数据进行了修改，然后进行了提交（这里进行了提交），然后事务A又对数据进行了查询，发现同一笔不同了，所以事务A读取了两笔不同的数据，两次读取同笔数据有了不同的数据。出现这种根本原因是在事务A进行读操作时，其他事务对数据进行了修改。
  • 进行 可重复读（Repeatable read）就能解决。

• 幻读：一个事务进行读取，分别读取到了不同的数据——着重 数据的新增

  • 事务A对数据进行查询，这时事物B开启，对其中一笔数据进行了新增，然后进行了提交（这里进行了提交），然后事务A又对数据进行了查询，发现查询所得的结果集是不一样的。幻读针对的是多笔记录。
  • 需进行 Serializable 序列化 就能解决。

不可重复读和幻读的区别

从总的来看，两者都是对数据进行了两次查询，但两次查询的结果都不一样。但如果你从控制的角度来看, 两者的区别就比较大。

不可重复读重点在于update和delete，而幻读的重点在于insert。

对于前者, 只需要锁住满足条件的记录
对于后者, 要锁住满足条件及其相近的记录

• 避免不可重复读需要锁行。
• 避免幻影读则需要锁表。
• 如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复 读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会 发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
• 互联网项目一般使用 读未提交 的隔离级别

Spring事务传播属性

1. required（默认属性）
   如果存在一个事务，则支持当前事务。如果没有事务则开启一个新的事务。
   被设置成这个级别时，会为每一个被调用的方法创建一个逻辑事务域。如果前面的方法已经创建了事务，那么后面的方法支持当前的事务，如果当前没有事务会重新建立事务。

2. Mandatory
   支持当前事务，如果当前没有事务，就抛出异常。

3. Never
   以非事务方式执行，如果当前存在事务，则抛出异常。

4. Not_supports
   以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

5. requires_new
   新建事务，如果当前存在事务，把当前事务挂起。

6. Supports
   支持当前事务，如果当前没有事务，就以非事务方式执行。

7. Nested
   支持当前事务，新增Savepoint点，与当前事务同步提交或回滚。
   嵌套事务一个非常重要的概念就是内层事务依赖于外层事务。外层事务失败时，会回滚内层事务所做的动作。而内层事务操作失败并不会引起外层事务的回滚。

数据库重要——索引

• 覆盖索引
  覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。
  一文搞懂
  1
  2
  3

关于聚簇索引的选择

• 聚簇索引默认是主键
• 如果表中没有定义主键，InnoDB 会选择一个 唯一且非空 的索引来代替
• 如果以上两种都没有，InnoDB 会 隐式 地定义一个主键来作为聚簇索引

关于最左前缀原则注意点

问题：假设现在有一个组合索引为（a，b，c），如果 where 条件中只有 where b=1 会不会用到组合索引？

答案：会。

• 根据 mysql 的原理，只要是索引或者满足索引的一部分就可以使用 index 方式扫描 （explain 下的 type 类型），mysql 都可能会用到这个组合索引
• 缺点：效率不高。mysql 会从索引中的第一个数据一个一个的查找到最后一个数据，直到找到符合判断条件的某个索引

数据库调优方案

常用方法：
使用索引；sql优化；数据库设计；缓存…（explain 优化 sql）
参考文章1
参考文章2

海量数据的存储

mysql 执行 sql 过程：

• 接收 sql
• 将 sql 放到排队队列中
• 执行 sql
• 返回执行结果

其中较耗时的操作是 排队等待的时间，sql 执行的时间。（在 sql 等待的同时，肯定有 sql 在执行，因此我们需要缩短 sql 的执行时间）

• 使用分表方案可以解决海量数据的效率问题—— 将字段拆分到不同表中，将原表中的 string 类型字段拆分到其他表，能够加快主表的查询

  • 垂直分表——按字段分
    假设一个数据库有 3000w 用户记录，包括字段 id，username，password，sex，age，email 等几十个字段，而用户登录时只需要 username，password 字段，查找 username，password 字段过程比较慢，此时可以专门为 username，password 字段独立建立一个表————按需建表
  • 水平分表——按记录分
    假设一个数据库有 3000w 用户记录，处理速度比较慢，这时可以将 3000w 记录分成 5 份，放在不同的机器上。这样就可以避免一次性访问过多的数据，将大数据细分成小数据，提高访问效率——大数化小

• 实际应用
  在实际的场景中，需要把垂直分表与水平分表结合使用。假设一个数据库有 3000 w 用户的话，可以先考虑垂直拆分，再水平拆分——先将其他字段拆分到 user_info 表中，用户主表只留下 username，password 等关键字段；之后再进行水平拆分，将用户和用户信息表分为多个同样结构的表。

常见分表方法

可略——常用语句

cankao
常用SQL

关键字顺序

• 编写顺序
  select–from–where–group by–having–order by
• 执行顺序
  from–where–group by–having–select–order by

参考文章