数据库基本知识

主键

• 主键是数据表中对数据对象能够唯一和完整标识的数据列或数据列的组合
  • 但能唯一和完整标识的列不一定是主键
• 主键要满足的条件：
  • 一个表只能有一个主键
    • 但该主键可以由多个列组成
  • 任意两行主键的值（或组合）都不相同
  • 不能有空值（NULL）
  • 值不允许修改或更新
  • 值不能被重用
    • 如果某行从表中删除，它的主键不能赋给以后的新行
• 使用innodb作为存储引擎的数据库，表必须有主键
  • 因为表数据文件本身就是按B+Tree组织的一个索引结构，主键索引存放的顺序就是数据存放的顺序
  • 如果没有定义主键，数据库会会自动选择一个可以唯一标识数据记录的列作为主键
  • 如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形
• 主键建议使用自增ID
  • 否则会不得不为了将新记录插到合适位置而移动数据，可能造成page分裂，这降低了写入的性能
  • 同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，增大了空间占用
• 主键尽量选择较短的数据类型
  • 因为普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间

索引

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能
• 索引数据可能要占用大量的存储空间
• 索引的效率随表数据的增加或改变而变化
  • 最好定期检查索引，并根据需要对索引进行调整
• 聚簇（主键）索引
  • 聚簇索引叶子节点的值存储的就是要查找的数据，只需要一次查询
  • 主键一定是聚簇索引，修改聚簇索引其实就是修改主键
    • 主键索引的叶子节点就是数据行，包括了所有数据字段
  • 聚簇索引的顺序就是数据存放的顺序
  • 一个表只能创建一个聚簇索引，但可以创建多个辅助索引
    
• 辅助（二级）索引的存储和数据的存储是分离的
  • 一般将索引存在内存中，而数据存在磁盘上
  • 辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录（回表）
• 覆盖索引
  • 当查询的列就是索引字段本身（或者主键）时，这种场景下该普通索引也可以实现一次查询，无需回表
  • 一般通过新增联合索引实现对相应字段的覆盖索引
• MySQL中一张表最多可以创建 64 个非聚簇索引；而且创建非聚簇索引时，列的数量不能超过16个

联合索引

• 联合索引采取最左匹配原则
  • 针对的是创建的联合索引中的顺序, 这个索引的使用顺序就很重要
    • 如果创建了联合索引（x,y,z），如果在条件语句中只有 y 和 z，那么就用不上联合索引
  • MySQL 在匹配联合索引最左前缀的时候，如果遇到了范围查询，比如 <, > 和 between 等，就会停止继续匹配，后面的索引列就无法使用到索引
• 在多条件联合查询的时候最好创建联合索引，因为多个单列索引在多条件查询时只会生效一个索引
• 采用 = 查询或者是 IN 查询时，MySQL 的优化器会自动帮我们调整为可以使用联合索引的形式
• 为什么不创建一个由所有的字段组成的联合索引？
  • 用所有的字段创建组合索引的存储成本比较高
  • 利用率比较低，完全用上的可能性几乎不存在
  • 一旦更改任何一个字段的数据，就必须要改索引，这样操作成本也比较高

自适应哈希索引

• 哈希索引的优点
  • 数据检索的时候效率非常高，通常只需要 O(1) 的复杂度，也就是一次就可以完成数据的检索
• 哈希索引的缺点
  
• 自适应 Hash 索引
  • 自适应指的是不需要人工来制定，系统会根据情况自动完成
    • 如果某个索引值被使用的非常频繁时，就会将这个数据页的地址存放到 Hash 表中
  • 自适应 Hash 索引只保存热数据，数据量并不会很大
    • 因此自适应 Hash 也是存放到缓冲池中，这样也进一步提升了查找效率
  • 采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点
    • 特别是当 B+ 树比较深的时候

索引友好的SQL语句

• 什么时候需要创建索引：
  • 字段的数值有唯一性的限制，比如用户名
  • 频繁作为 WHERE 查询条件的字段
    • 包括UPDATE和DELETE操作，因为需要先检索出来这条记录，然后再对它进行更新或删除
  • 需要经常 GROUP BY 和 ORDER BY 的列
  • DISTINCT 字段需要创建索引
• 什么时候不需要索引：
  • 起不到定位的字段通常是不需要创建索引
    • 即WHERE 或 GROUP BY、ORDER BY 里用不到的字段
  • 频繁更新的字段不一定要创建索引
    • 因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担
  • 表记录太少时不需要创建索引
  • 字段中如果有大量重复数据，也不用创建索引
• 索引失效场景：
  • 对索引进行了表达式计算
  • 对索引使用函数
  • WHERE 子句中，在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引
  • 使用 LIKE 进行模糊查询的时候，以_或%开头
  • 索引列与 NULL 或者NOT NULL 进行判断
  • NOT 等负向查询条件
  • 字符串当整型使用等隐式类型转换
  • 复合索引最左匹配失败
  • 数据少时mysql觉得全表扫描更快
    
    
    

索引原理

• B树
  
  
• B+树
  
  
• 哈希
  

事务

• 当开启了第一个事务，还没有进行 COMMIT 提交时直接进行第二个事务的 BEGIN，这时数据库会隐式地 COMMIT 第一个事务，然后再进入到第二个事务

隔离级别

• 数据库高并发访问时，可以通过降低数据库的隔离标准，来换取事务之间的并发能力
  • 隔离级别越低，意味着系统吞吐量（并发程度）越大，但同时也意味着出现异常问题的可能性会更大
  • 需要在性能和正确性上进行权衡和取舍
    
    

• 在 MySQL 中，默认的隔离级别是可重复读
• 隔离的实现机制是锁，隔离级别越高锁的代价越大

MVCC（Multiversion Concurrency Control）

• MVCC机制可以解决不可重复读和幻读问题
  • 在可重复读的情况下，InnoDB 可以通过 Next-Key 锁 +MVCC 来解决幻读问题
  • 在读已提交的情况下，即使采用了 MVCC 方式也会出现幻读
• MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行
  • 这样可以让读写互相不阻塞，以提升事务并发处理能
• MVCC 的核心就是 Undo Log+ Read View
  • “MV”就是通过 Undo Log 来保存数据的历史版本，实现多版本的管理
  • “CC”是通过 Read View 来实现管理，通过 Read View 原则来决定数据是否显示
    • 针对不同的隔离级别，Read View 的生成策略不同，也就实现了不同的隔离级别

Undo Log

• MVCC 是通过数据行的多个版本管理来实现数据库的并发控制，简单来说它的思想就是保存数据的历史版本
• 每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号）
  • 这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序
  • 这个ID记录在数据行中的隐藏列db_trx_id中
• 快照被保存在了位于回滚段的 Undo Log 里
  • 通过隐藏列db_roll_ptr记录了回滚指针，指向了改行的 Undo Log
  • 回滚指针将数据行的所有快照记录都通过链表的结构串联了起来
  • 每个快照的记录都保存了当时的事务号，这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找
    

Read View

• 快照读： 读取的是历史快照数据
  • 不加锁的简单的 SELECT 都属于快照读
• 当前读： 读取最新数据，而不是历史版本的数据
  • 加锁的 SELECT，或者对数据进行增删改都会进行当前读
• Read View 用于解决行的可见性问题，其保存了当前事务开启时所有活跃（还没有提交）的事务列表
  • 也就是保存了不应该让这个事务看到的其他的事务 ID 列表
    
    

InnoDB 是如何解决幻读的

锁

• 共享锁
  • 共享锁也叫读锁或 S 锁
  • 共享锁锁定的资源可以被其他用户读取，但不能修改
    • 在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改
    • 当对数据表加上共享锁的时候，该数据表就变成了只读模式
• 排它锁
  • 排它锁也叫独占锁、写锁或 X 锁
  • 排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改
    • 在进行INSERT、DELETE或者UPDATE的时候，数据库会自动使用排它锁，防止其他事务对该数据行进行操作
• 意向锁
  • 给更大一级别的空间示意里面是否已经上过锁
    • 打算锁表的事务就能迅速获得足够的锁信息并决定下一步行动
  • 例如给某一行数据加上了排它锁，数据库会自动给更大一级的空间（数据页或数据表）加上意向锁
    • 因为如果表中的某个数据行被上了行锁，我们就无法获取该表的排它锁
    • 其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可
    • 否则每次给表加锁时，需要首先遍历所有行，检查是否已经加锁了
• 乐观锁
  • 认为对同一数据的并发操作属于小概率事件，不采用数据库自身的锁机制，而是通过程序来实现
  • 采用版本号机制或者时间戳机制实现，更新前判断当前拿到的数据是否最新
• 悲观锁
  • 对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性
  • 乐观锁和悲观锁并不是锁，而是锁的设计思想
    
• 死锁就是多个事务（如果是在程序层面就是多个进程）在执行过程中，因为竞争某个相同的资源而造成阻塞的现象
  • 发生死锁，往往是因为在事务中，锁的获取是逐步进行的
  • 当死锁发生的时候，就需要一个事务进行回滚，另一个事务获取锁完成事务，然后将锁释放掉
• 死锁避免：
  • 如果事务涉及多个表，则尽量一次锁定所有的资源，而不是逐步来获取
  • 如果事务需要更新数据表中的大部分数据，则采用锁升级的方式，比如将行级锁升级为表级锁
  • 不同事务并发读写多张数据表，可以约定访问表的顺序
  • 采用乐观锁时不会发生死锁

存储引擎

表空间

• 每张表都会单独保存为一个.ibd 文件
  
  
• 数据库 I/O 操作的最小单位是页
  • 在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载
  • 按类型划分的话，常见的有数据页（保存 B+ 树节点）、系统页、Undo 页和事务数据页等
    
    
    
    
    
    
    
    

内存缓冲池

SQL语句执行流程

SELECT查询顺序

优化器

• 生成最佳执行计划的策略通常有以下两种方式
  • 基于规则的逻辑优化（RBO）
    • 规则就是人们以往的经验，或者是采用已经被证明是有效的方式
    • 一般是基于关系代数等价性进行逻辑变换，减少无用开销
      • 例如尽可能早地进行过滤，复杂条件分解和合并，子查询转JOIN等
    • 每条sql经过RBO优化出来的结果都是固定的
  • 基于代价的物理优化 （CBO）
    • 根据代价评估模型，计算每条可能的执行计划的代价，从中选择代价最小的作为执行计划
      • 例如读写IO、网络带宽，基数，数据分布等
    • 会利用数据表中的统计信息来做判断，针对不同的数据表，查询得到的执行计划可能是不同的
• 代价计算