#相关概念

• 元组：二维表中，行称为元组。关系型数据库中，关系是一场表，表中的每行（即数据库中的每条纪律）是一个元组，每列是一个属性。
• 码：二维表中，列常委码。能唯一标识实体的属性，对应表中的列。
• 候选码：关系中某一属性或属性的值能唯一标识一个元组，而其任何子集都不能在表示，则该属性组为候选码。例如：学生表中，{学号}和{姓名，班级}都是候选码。
• 主码：主键，从候选码中选出来。
• 外码：外键。如果一个关系中的一个属性同时是另一个关系中的主码，则这个属性为外码。
• 主属性：候选码中出现的属性称为主属性。
• 非主属性：不包含在任何一个候选码中的属性。

##drop、delete 与 truncate

用法不同

类型不同

• DML语言：数据库操作语言（Data Manipulation Language），数据库增删改查，针对表中数据
• DDL语言：数据定义语言（Data Definition Language），对数据库内部对象创建、删除、修改的语言，针对数据库结构。

delete为DML语言，执行之后delete操作会放到 rollback segement中，事务提交之后生效，也可回滚。

drop和truncate为DLL原，执行之后立即生效，数据不能回滚。

执行速度不同

drop > truncate > delete

##utf8 和 utf8mb4

• utf8 ：
  • utf8编码只支持1-3个字节
  • 中文占 3 个字节，其他数字、英文、符号占一个字节。
  • emoji 符号占 4 个字节，所以utf8不能存储emoji符号
• utf8mb4 ： UTF-8 的完整实现。最多支持使用 4 个字节表示字符。可以用来存储 emoji 符号。

#数据库范式

##第一范式 1NF

原子性：数据库中属性（数据表中的列）不可再分割。

##第二范式 2NF

在第一范式的基础上，表中的每一列都应该与主键关联。

如在同一张表中存储学生基本信息和学生成绩，违背了第二范式。

每个表中的非主属性完全依赖于码

##第三范式 3NF

表中出非主属性之间不能互相依赖

在第二范式的基础上，消除非主属性之间的依赖关系，只保留非主属性与码的依赖关系

#MySQL数据类型

##整形

tinyint,smallint,middleint,int,bigint，分别占用8、16、24、32、64存储空间

##浮点数

• float
• double
• decimal：高精度小数类型。

三种浮点数都可以指定列宽，例如使用decimal(18,9)表示总共18位，取9位保存小数部分，剩下的保存整数部分。

##字符串

• char：定长，会自动去除末尾的空格
• varchar：变长，不会删除末尾的空格
• blog：使用二进制形式保存
• text：使用字符串形式保存

##时间

• datatime：保存从1001年到999年的日期和时间，精度为秒，使用8字节存储空间，没有时区信息。
• timestamp：保存1970-01-01开的时间。使用4字节保存，只能表示到2038年，有时区信息。

#MySQL基础架构

#MySQL存储引擎

使用show engines命令查看MySQL的存储引擎

常用的有InnoDB和MyISAM引擎

默认使用InnoDB引擎

##MyISAM

• 不支持事务
• 不支持行级锁，只能对整张表加锁。读时加共享锁，写时加排他锁。
• 数据丢失风险

##InnoDB

• 支持事务，实现了四个隔离级别，默认级别时可重复读，通过MVCC（多版本并发控制）+Next-KeyLocking（间隙锁）防止幻读
• 主索引是聚集索引

##MyISAM 和 InnoDB

• 锁：MyISAM使用表级锁，InnoDB使用行级锁
• 事务：MyISAM不支持事务，InnoDb支持事务
• 外键：MyISAM不支持外键，InnoDB支持外键
• 崩溃恢复：MyISAM不支持奔溃后安全恢复，InnorDB支持奔溃后通过日志安全恢复
• 索引实现方式不同：两者都使用B+树的数据结构。MyISAM中索引文件和数据文件分离，InnorDB数据文件同时是索引文件

#MySQL 事务

事务：逻辑上的一组操作，要么都执行，要么都不执行。

##事务的特性

1. 原子性（Atomicitly）：一个事务包含的所有操作，要么全部成功，要么全部失败。
2. 一致性（Consistency）：事务执行前后状态一致。例如转账：转账完成钱的总数不变。
3. 隔离性（Isolation）：是否读未提交。
4. 持久性（Durability）：事务提交后，数据库的更改是永久的。

简称ACID

##事务引发的问题

• 脏读：未提交的事务读到了另一个事务未提交的数据。
• 不可重复读：一个运行较慢事务在事务读取用一记录，两次读到的数据不同，主要针对update和delete。
• 幻读：两次读取到数据条数不同，针对insert。

##事务隔离级别

• (READ-UNCOMMITTED)读未提交：最低级别，允许读取尚未提交的数据变更，可能会导致脏读，不可重复读，幻读。
• (READ-COMMITTED)读已提交：允许读取已提交的数据，只能防止脏读。
• (REPEATABLE-READ)可重复读：一个事务开始，事务过程中所读取到的所有数据不允许被其他事物修改，不能防止幻读。
• (SERIALIZABLE)串行化：最高级别，防止所有问题，所有事务以串行化方式逐个执行。

##事务隔离级别实现方式

基于锁和MVCC机制实现。

串行化隔离级别通过锁实现，其余隔离级别基于MVCC实现。

##默认隔离级别

通过执行SELECT @@transaction_isolation;命令查看

默认隔离级别是可重复读

#MySQL锁

##表级锁和行级锁

• 表级锁：锁定粒度最大的锁，针对非索引字段加锁，对整张表枷锁，实现简单，资源消耗少，枷锁快，不会出现死锁。
• 行级锁：锁定粒度最小的锁，针对索引字段加锁，对操作行加锁。加锁慢，可能出现死锁。

##行级锁使用

InnoDB使用行级锁针对索引字段加锁。

当执行update，delete方法时，如果where条件字段没有命中唯一索引，或者索引失效时，会导致扫描全表并对全表行加锁。

##常见锁🔒的类型

• 共享锁（S锁）：又称读锁，事务在读取数据（select）时取得共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X锁）：又称写锁、独占锁，事务在修改记录（update）的时候获得排他锁，不允许多个事务同时获取。如果一个记录已经被加了拍他锁，则其他事务不能在加其它类型的锁（锁不兼容）。
  默认不加锁，可以显示声明使用锁
  Copy

      SELECT ... LOCK IN SHARE MODE; # 共享锁
  SELECT ... FOR UPDATE; # 排他锁
  
    

• 意向锁：表级锁，用于快速判断表中有没有行锁。
  • 意向共享锁：如果事务有意向对表中数据加共享锁，必须先加意向共享锁。
  • 意向排他锁：如果事务有意向丢表中数据加排他锁，必须先加意向排他锁。

##InnoDB中的锁

InnoDB有三种行锁：

• 记录锁（Record Lock）：单个行记录上的锁。
• 间隙锁（Gap Lock）：锁定一个范围，不包含记录本身。
• 临键锁（Next-Key Lock）：同时使用记录锁和临键锁。

##当前读和快照读（）

InnoDB默认隔离级别（可重读）可以解决幻读问题，通过当前读和快照读。

• 当前读（一致性锁定读）：使用临键锁（Next-Key Lock）进行加锁保证不出现幻读。
• 快照读（一致性非锁定读）：有MVCC机制保障不出现幻读。

两者的区别

快照读：即正常使用select时，使用快照读。如果读取到的数据正在执行update/delete操作，读取操作不会等待记录上的排他锁释放，而是会读取行的上一个快照。

当前读：当在事务隔离级别RC（读已提交）和RR（可重读）下，InnoDB会使用锁定读。

• 在RC级别下：对于快照数据，当前读会读取被锁定行的最新一份快照数据。
• 在RR级别下：对于快照数据，当前读会读取本事务开始时的行数据版本。

快照即记录的历史版本，每行记录可能存在多个历史版本。

#MySQL性能优化

##文件存储

不建议使用MySQL存储二进制文件数据。

建议使用对象存储保存文件，MySQL保存文件地址。

##IP地址存储

可以将IP地址转换成整形存储。

• INET_ATON() ：把 ip 转为无符号整型 (4-8 位)
• INET_NTOA() ：把整型的 ip 转为地址

##尽可能将列定义为 NOT NULL

除非有特别的原因使用 NULL 值，应该总是让字段保持 NOT NULL。

• 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；
• 进行比较和计算时要对 NULL 值做特别的处理。

#MySQL索引

##什么是索引

索引是一种用于快速查找和检索数据的数据结构，常见索引结构有：B树、B+树、Hash表等。

##索引的数据结构

Hash表

哈希表时键值对的集合，通过key可以快速得出value。O(1)。通过对key使用哈希算法计算索引，在通过索引找到value。

缺点：

1. Hash冲突
2. Hash索引不支持顺序和范围查询

B树

beyondhorizon.top

www.codedump.info

B树的详细解释以及插入删除操作等相关内容上文有详细说明。

个人对于B树的理解：普通二叉树的缺点，每个节点只能有两个子节点，每个节点只能容纳一个数据，这导致了两个问题：1. 树的高度很高；2. 逻辑上挨着的节点数据可能树里离的很远，这对于内存中操作数据来说问题不大。

但是在数据库里，数据首先会存储于磁盘上而不是内存上。过高的树需要多次查找，取出结果，比较，再查找，比较，树的高度越高，需要访问磁盘的次数也越多。对于磁盘来说并不是很好的选择。如果满足“局部性原理”，即逻辑上相邻的数据，物理上尽量存储在一起。这样可以减少磁盘访问次数。

B树解决了这一问题，B树的特点：1. 多路树，不再局限于一个节点只能有两个子节点；2. 所有子节点的高度相同。

B树每个节点能容纳更多的数据，这样降低了树的高度，同时逻辑上相邻的数据在物理上存储在相邻的磁盘空间中。在查询时减少磁盘交互次数。

B树：

在上图中，叶子节点有多个数据，查找是会将叶子节点上的数据一次读入内存中，通过在内存中遍历比较，找到需要的数据。

B树每个节点能保存4K的数据，由于磁盘存储数据采用分块的方式，而每个块的大小为4K。每次读取数据都会读取一个节点，4K的数据到内存中。

B+树

B+树子节点不保存数据，只保存关键字的索引，所有的数据都保存在叶子节点上。同时所有叶子节点上的数据构成了一个有序链表。

B+树和B树比较

1. B+树查询速度更稳定。B+树所有数据都存放在叶子节点，而叶子节点高度相同，每次查找次数相同，所以查询速度稳定。
2. B+树叶子节点有序。B+树所有叶子节点数据构成一个有序链表，查询大小区间的数据时更快。
3. B+树全节点遍历更快。B+树只需要遍历叶子节点，B树需要遍历全部节点。
4. B树如果访问的数据离根节点近，此时查找速度快于B+树。

##MyISAM和InnoDB索引

两者都是用B+树作为索引结构

• MyISAM使用非聚簇索引：B+树节点data域中保存数据的地址，在查找时，先找到节点，再通过节点里的地址找到数据。
• InnoDB使用聚簇索引：B+树节点data域中保存完整数据，索引的key是数据库主键。

##索引类型

主键索引

数据表的主键列使用的是主键索引。

如果一张表没有设置主键，InnoDB会先查找表中是否有唯一且不允许为空的字段，如果有则使用该字段为主键，如果没有InnoDB会自己创建一个6Byte的自增主键。

二级索引（辅助索引）

二级索引的叶子节点保存的数据时主键，通过二级索引可以找到主键。

二级索引包含唯一索引、普通索引、前缀索引、全文索引

• 唯一索引（UNIQUE）：数据表中设置了唯一的字段，不允许重复，允许为null
• 普通索引（INDEX）：数据表中手动设置的索引字段，可以重复，可以为null
• 前缀索引：对于字符串类型的数据，截取字符串前几个字符创建索引。
• 全文索引（FULLTEXT）：检索大文本数据中的关键字信息。

##聚集索引和非聚集索引

聚集索引

聚集索引即索引结构和数据存放在一次的索引。主键索引就是聚集索引。特点：查询快，修改慢。

• 优点：查询速度快，因为叶子节点存放数据并且数据有序，所以查到叶子节点就是查到数据
• 缺点：
  • 依赖于有序的数据：查询依赖于数据的物理存储地址按照索引的顺序排列。
  • 更新代价大：由于数据的物理存储地址按照索引的顺序排列，所以每次插入删除都需要移动数据，使数据在物理上有序，连续。

非聚集索引

索引结构和数据分开存放。二级索引是非聚集索引。二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

• 优点：更新快，叶子节点不存放数据。
• 缺点：
  • 依赖于有序的数据。
  • 可能需要回表二次查询。

##覆盖索引

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

##联合索引

使用表中多个字段创建索引

##最左前缀匹配原则

使用联合索引是，根据联合索引中字段的顺序，从左到右依次匹配查询条件中与联合索引中最左字段匹配的字段。

##索引字段创建原则

1. 需要频繁查询、作查询条件、关联查询的字段
2. 不为null的字段
3. 需要频繁排序的字段
4. 不会频繁更新的字段
5. 尽量使用联合索引而不是单列索引

#MySQL日志

MySQL日志主要由二进制日志（binlog，归档日志），事务日志（redo log）、回滚日志（undo log）组成

##redo log

Redo log是InnoDB所使用的日志系统，记录在每个数据页上做的修改，通过redo log可以用于从错误中恢复数据。保证数据的持久性和完整性。如果MySQL服务异常停机，重启MySQL之后会通过redo log恢复数据。

MySQL数据查询时，从磁盘读取数据以页为单位，查询时会一次性加载一页数据（16kb）Buffer Pool中。后续查询会优先在Buffer Pool中查找。在执行更新操作时，也是有限更新Buffer Pool中的数据，同时记录重做日志缓存（redo log buffer），之后在适当的时机，将缓存刷盘到redo log文件中。

刷盘时机

刷盘：将数据从内存写入磁盘

1. 使用innodb_flush_log_at_trx_commit参数可以控制刷盘时机：
   • 0：每次事务提交时不进行刷盘操作（数据丢失分险）
   • 1：每次事务提交都进行刷盘（默认值）（不会有数据丢失，因为丢失的都是未提交的事务）
   • 2：每次事务提交都把redo log buffer内容写入系统缓存page cache 在默认情况下，每当事务提交时都会调用fsunc对redo log进行刷盘
2. 同时，InnoDB每隔 1 s，会自动将redo log buffer内容写入系统缓存page cache，之后调用fsunc刷盘。
3. 当redo log buffer占用的空间超过innodb_log_buffer_size一半时，触发刷盘。

日志文件组

多个文件存储redo log日志，一个文件写满写写一个，最后一个写满再回头写第一个。

##binlog

binlog记录数据库操作逻辑，记录每次数据表更新。

MySQL主从同步时，会使用binlog来同步数据。

记录格式

通过binlog_format参数指定记录格式

• statement：记录SQL语句原文，但是在执行update_time=now()会导致时间不同。
• row：记录执行SQL的参数，记录具体时间
• mixed：自动判断该SQL是否会有数据不一致问题，如果有使用row，没有使用statement记录

写入机制

事务执行过程中，现将日志写到binlog cache，事务提交之后，在将binlog cache写入binlog。

##两阶段提交

通过redo log和binlog互相配合来尽量保证数据恢复能力，和数据不丢失。

日志记录顺序。

执行更新操作时先写入redo log，并将状态设置为prepare，事务提交后，将更新的操作写入binlog，在将redo log设置为commit阶段。

如果在redo log状态为prepare阶段时，MySQL服务异常，在恢复数据时由于事务没有提交，没有binlog，MySQL会选择回滚 这一事务。

如果在事务提交，并且有binlog时，MySQL会选择通过binlog恢复数据，并且将redo log状态设置为commit阶段。

##undo log

undo log保证事务的原子性，用于在事务异常时回滚操作。

所有的事务操作都会先记录回滚日志，在实际执行操作。同时，回滚日志先于数据持久化到磁盘上。

##三种日志的作用

• 使用redo log保证事务的持久性
• 使用undo log保证事务的原子性
• 使用redo log和undo log 保证数据不丢失
• 使用binlog完整数据备份，主从，保证数据的一致性

#MVCC

##MVCC

Multi-Version Concurrency Control，多版本并发控制。是一种并发控制的方法，在数据库管理系统中，实现对数据库的并发访问。

##实现原理

隐式字段

• DB_ROW_ID：隐式自增主键，如果数据表没有主见，InnoDB自动以DB_ROW_ID等字段生成聚集索引
• DB_TRX_ID：最近插入/修改的事务ID，记录当前记录最后一次修改该记录的事务ID
• DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本
• DRFAULT_BIT：删除flag

undo log

ReadView

事务在进行快照操作时生成的读视图，在该事务执行快照的时候，会生成数据库系统当前的快照，记录并维护系统当前活跃事务的ID

ReadView的三个属性

• trx_list：未提交事务ID列表
• up_limit_id：记录trx_list列表中最小的事务ID
• low_limit_id：下一个事务ID，就是当前最大ID + 1

#SQL执行流程

• 连接器：数据库连接，身份认证，权限校验
• 分析器：词法分析（提取关键词，select、表命、查询条件等）、语法分析（检查SQL是否符合MySQL规范）
• 优化器：使用（IO成本 + CPU成本）最小的索引来执行查询
• 执行器：调用存储引擎接口执行SQL
  • 查询BufferPool中是否有需要查询的数据，如果没有去磁盘查询，之后将数据放入BufferPool
  • if执行的是update，记录undo log，在BufferPool中udate数据。（BufferPool中的数据又可能和数据库中的数据不一致）
  • BufferPool中的数据已更新，记录redo log（先写入到redo log buffer中，再在合适时间写入磁盘中。）
  • 记录bin log
  • 将bin log文件名和当前语句更新内容记录到redo log
  • redo log后添加commit（两阶段提交）

#索引失效

操作符（ = ） 左右两边的数据类型不同时，会发生隐式转换。

##失效的情况

• 当where左边为数值类型时会发生隐式转换，索引不会失效，对效率影响不大。
• 当where左边为字符类型时会发生隐式转换，索引会失效，查询变成全表扫描，效率低。

##字符串 => 数值转换方式：

• 所有不以数字开头的字符串都转换为0。（例如abc，abc123 => 0）
• 以数字开头的字符只截取读一个数字段。（例如123abc4 => 123，012ab4 => 12）

##失效案例

索引失效案例：num2为varchar类型

    SELECT * FROM `test1` WHERE num2 = 10000;

  

执行这条SQL时索引失效，发生全表扫描，因为'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的

#索引失效

1. 上述例子中，由于类型转换导致索引失效
2. 使用!= > < 导致索引失效
3. 模糊查询，如%S%，会导致索引失效，S%不会导致索引失效

#MySQL中语句执行优先级

##单表

form > where > group by > select > order by > limit

##多表

from > join > on > where > group by > AVG,SUM > having > select > distinct > order by > limit

#慢查询优化

使用EXPLINE关键字分析查询语句

##查询使用索引

• 使用LIKE 模糊查询时，第一个时%查询不走索引

##优化数据库结构

将字段较多的表拆分，将不经常查询的数据新建表