范式
- 第一范式：每一列都是不可分割的
- 第二范式：非主属性完全依赖于主键
-
索引
是帮助MySQL高效获取数据的数据结构
- 底层一般是（b树、b+树，或者是hash）
- MyISAM、InnoDB存储引擎只支持BTREE索引
- 可以使用explain查看是否使用索引
索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的
优点
大大加快数据的检索速度
- 加速表的连接
- 显著减少查询中分组和排序的时间
-
缺点
建立和维护索引耗费时间空间，会降低修改、增加、删除数据的效率
类型
唯一索引 不可以有重复值 可有空值
- 普通索引 可以有空值和重复值
- 主键索引 特殊的唯一索引，会自动创建
- 单列索引 和 组合索引
- 全文索引 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
-
什么时候适合
经常被查询的字段
- 经常用作表连接的字段
- 经常需要排序、分组的字段
- 某列经常作为最大最小值
- 频繁作为where中的查询条件
-
注意事项
尽量使用数据量少的索引
- 尽量选择区分度高的列作为索引
-
什么时候失效
使用组合索引时，没有遵循“最左前缀”原则
- 索引列上做任何操作，例如计算、函数、类型转换，导致索引失效而转向全表扫描
- MySQL在使用不等于（!=或者<>）的时候无法使用索引，导致全表扫描
- LIKE以通配符开头（%abc）索引失效，变成全表扫描的操作
- OR语句前后没有同时使用索引
- 数据类型出现隐式转换
如果数据库估计全表扫描比索引快，则不使用索引(比如非常小的表)
什么时候不适合
频繁更新的字段不适合建立索引
- where条件中用不到的字段不适合建立索引
- 数据比较少的表不需要建索引
- 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值
-
实现原理
MyISAM
- 使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址
- MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
InnoDB
A(Atomicity)原子性：表示事务是一个不可再分割的工作单位，事务的操作要么全部成功，要么全部失败回滚
- C(Consistency)一致性：表示事务开始之前和结束之后，数据库的完整性没有被破坏。也就是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性
- I(Isolation)隔离性：表示并发的事务是相互隔离的，也就是一个事务的内部操作都必须封锁起来，不会被其他事务影响
D(Durability)持久性：表示事务一旦提交后，该事务对数据做的更改便持久保存在数据库中，并不会回滚
隔离级别
读未提交
- 可能 脏读、不可重复读、幻读 不加锁读
- 读已提交
- 可能 不可重复读、幻读 不加锁读
- 可重复读
- 不会有 不加锁读
可串行化
数据量特别大时，性能降低
- 为了解决数据量过大，数据库性能降低的问题（硬件方面或者分库分表）
- 原有数据库拆分为若干个数据库，大表拆分为若干个小表，让单一库单一表的数据量减少，从而提高性能
-
垂直切分
垂直分库
把单一数据库按照业务进行划分，专库专表， 主要是不同表，放到不同的数据库中，进行专门的业务处理
-
垂直分表
操作数据库中某张表，把这张表中一部分字段存到一张新表里，再把这张表的另一部分字段数据存到另外一张表里 主要是根据不同的数据段进行拆分
-
水平切分
水平分库
一个数据库分成相同结构的数据库，减少每个数据库的数据量，
比如课程ID奇数将其分到课程数据库A，课程ID偶数分到课程数据库b
水平分表
应用和问题
应用
在数据库设计时考虑垂直分库和垂直分表
随着数据库数据量的增加，不要马上考虑做水平切分，首先考虑缓存处理，读写分离，使用索引等等方式，如果这些方式不能根本上解决问题了，再考虑水平分库和水平分表
分库分表问题
跨节点连接查询问题（分页、排序）
-
并发问题
脏读：读取未提交数据
- 当一个事务读取另一个事务尚未提交的修改时，产生脏读
- 解决：写记录时加锁就不会有脏读了
- A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读
- 不可重复读：前后多次读取，数据内容不一致
- 同一查询在同一事务中多次进行，由于其他提交事务所做的修改，每次返回不同的结果集
- 解决方案：读记录时加锁
- 事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，称为不可重复读
幻读：前后多次读取，数据总量不一致
- 为了解决数据量过大效率底下的问题
- 主服务器做增删改操作
- 从服务器做查询操作
- 原理
- 主服务器开启binlog日志，记录增删改操作
- 从服务器实时监控binlog日志变化、数据同步
- 将主服务器的bin-log日志传到从服务器，从服务器根据日志内容将数据还原到本地
- 从服务器分摊主服务器的查询压力（负载均衡）
主从故障切换：把数据最多的变成新的主库，重新修改相关配置信息，重启数据库服务
锁
主要用来实现事务的隔离性、锁机制也为MySQL实现各种隔离级别提供了保证
读读不需要锁，不会有问题
- 写写一般是排队
- 读写可能会有脏读、不可重复读、幻读的问题，各个数据库厂商对SQL标准的支持可能都不一样
怎么解决：
-
写锁/排他锁（Exclusive Lock，X Lock）
当前写操作未完成前，会阻断其他写锁和读锁。这就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的数据
- delete：获取记录的x锁，再执行delete mark操作
- update：
- 未修改该记录的键值，并且被更新的列所占用的存储空间在修改前后未发生变化
- 先定位再获取，再x锁，最后修改
- 未修改该记录的键值，并且最少有一个被更新的列占用的存储空间在修改前后发生变化
- 先定位，获取x锁，再删掉，再插入一条新记录
- 修改了该记录的键值，则相当于在原记录上delete之后再insert，加锁操作按照delete和insert的规则进行
- 未修改该记录的键值，并且被更新的列所占用的存储空间在修改前后未发生变化
insert
针对同一份数据，多个事务的多操作可以同时进行不会互相影响，相互不阻塞
从数据操作的粒度划分
锁的粒度越小，并发度越高，但越消耗资源，因此需要在高并发响应，和系统性能两方面平衡
表级锁
锁定整张表，不依赖存储引擎，开销最小，避免死锁，并发度低
- MyISAM在select前，给表加读锁
- MyISAM，增删改操作前加写锁
-
意向锁
InnoDB支持的一种多粒度锁，允许行级锁和表级锁共存，是一种表锁
- 意向锁的存在是为了协调表锁和行锁的关系，支持多粒度（表锁与行锁）的共存
- 意向锁是一种不与行级锁冲突的表级锁
- 表明某个事务正在某些行持有了锁，或者该事务正准备去持有锁
- 分为意向共享锁（事务有意向对表中的某些行加共享锁)，意向排他锁（事务有意向对表中的某些行加排他锁)
- 意向锁是由存储引擎自己维护的，无法手动操作
- 在为数据行加共享/排他锁之前，InnoDB会先获取该数据行的所在数据表的对应意向锁
- 用来告诉其他事务已经有了锁定了表中的某些记录
- 想要获取某些记录的锁，就要在表上加意向锁
- 意向锁之间互不排斥，但除了is和s兼容外，意向锁会与共享锁和排他锁互斥
- IX，IS是表级锁，不会和行级的XS发生冲突，只会和表级的XS发生冲突
意向锁在保证并发的前提下，实现了行级锁和表锁共存且满足事务隔离性的要求
自增锁
是当向含有自增列的表中插入数据的时候需要获取的一种特殊的表级锁
元数据锁
保证读写的正确性
- 比如一个查询正在遍历表中的数据，另一个线程在对表的结构变更，这是不行的
当对一个表做增删改查操作时加mdl读锁，修改表结构时+mdl写锁
页级锁
页级别的锁，介于行锁和表锁之间
每个层级的锁数量是有限的，锁会占用空间，每个层级的锁超过了阈值就会有锁升级
行锁
也叫做记录锁（锁在单个记录上）
- 行级锁只在存储引擎层实现
- 优点
- 锁粒度小，冲突概率低，并发度高
缺点
为了防止出现幻影记录
-
临键锁
既能锁定某条记录，也能防止其他事务在该记录前面插入
- 相当于记录锁+间隙锁
-
对待锁的态度划分
乐观锁
读的时候不需要上锁，更新的时候会判断期间别人有没有更新
- 不采用数据库本身的锁机制而是通过程序实现
- 适用于多读场景可以提高吞吐量
-
悲观锁
每次拿数据都会上锁，别人想拿数据就会阻塞直到他拿到锁
- 开销大
-
按加锁方式划分
显式锁
隐式锁
全局锁
-
死锁
死锁是指两个及以上的事务在同一资源上互相占用，同时请求锁定对方的占用资源，导致恶性循环的现象。
- 当多个事务试图以不同的顺序锁定资源时，就会产生死锁的风险。多个事务锁定同一个资源也会造成死锁
- 条件
- 两个及以上的事务
- 每个事务都已经持有锁并且申请新的锁
- 锁资源同时只能被同一个事务持有或者不兼容
- 事务之间因为持有锁和申请锁导致彼此循环等待
- 关键在于两个及以上的事务加锁顺序不一致 ``` Transaction1: START TRANSACTION; UPDATE table SET price = 12 where id = 1; UPDATE table SET price = 13 where id = 2; COMMIT;
Transaction2: START TRANSACTION; UPDATE table SET price = 15 where id = 2; UPDATE table SET price = 20 where id = 1; COMMIT; ```
- 假设这两个事务都执行第一条UPDATE语句，更新了一行数据，同时锁定了这行数据，然后这两个事务都试图执行第二条UPDATE语句，此时发现该行数据已被锁定，此时两个事务都要等待对方释放锁，同时又持有对方需要的锁，最终陷入死循环，造成死锁
- 对于死锁问题，
- MySQL的InnoDB存储引擎目前的解决方案是，将持有最少行级排他锁的事务进行回滚。对于事务型系统，死锁是无法避免的，所以应用程序在设计的时候必须考虑如何处理死锁。多数情况都是回滚某一事务，最后再重新执行因死锁回滚的事务即可。、
- 等待直至超时
- 使用死锁检测进行死锁处理（有向图成环）
如何避免
锁所在事务信息
- 索引信息
- 表锁/行锁信息
- type_mode
- 其他信息
- 一堆比特位等
连接池
- 限定数据库的连接个数，不会由于数据库连接过多导致系统运行缓慢或者崩溃
- 数据库连接不需要每次都去创建或销毁，节约了资源
- 数据库连接不需要每次都去创建，响应时间更快
存储引擎
不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二之选，速度快
InnoDB
- 支持事务、行级锁定、外键
- 提供了具有提交、回滚、崩溃恢复能力的事务安全
- 比起MyISAM，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
是以页为单位来管理存储空间的，在真正访问页以前，需要把在磁盘上的页缓存到内存中的buffer pool才能访问，所有变更必须要先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘(checkPoint机制)，通过缓冲池来优化cpu和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降的太快
MyISAM
不支持事务、外键
- 访问速度快，对事务完整性没有要求
-
Memory
使用存在内存中的内容来创建表
- 默认使用哈希索引
- 一旦关闭MySQL服务，表的数据消失，表的结构还在
- 经典用法，用户的在线状态
mvcc
三种log
binlog（Binary Log）
数据库层的，一个事务提交，一次性写入bin log
记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息
默认情况下，二进制日志功能是开启的，启动时可以重新配置—log-bin[=file_name]选项，修改二进制日志存放的目录和文件名称
redo log(重做日志）
提供再写入操作，恢复提交事务修改的页操作，用来保证事物的持久性
存储引擎层（InnoDB）生成的日志，记录的是“物理级别”的页修改操作，比如页号xxx、偏移量yyy，写入了zzz数据，主要为了保证数据的可靠性
采用了WAL（Write-Ahead Logging）先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log，，当发生宕机且数据未刷到磁盘时，就看通过redo日志恢复，保证acid中的d
- 好处
- 降低了刷盘频率
- 占用的空间很小
- 特点
- 顺序写入磁盘
- 事务执行过程中，redo log不断记录
- 组成
- 重做日志缓冲（redo log buffer），其是易失的，默认16M
- 连续内存空间，被划分为若干个redo log block，一个block 是512字节大小
- 重做日志文件（redo log file），是持久的
先写redo log buffer再以一定频率写到redo log file
redo log buffer刷到 redo log file先丢到文件系统缓存（page cache），再由操作系统刷到redo log file中
默认策略
每次事务提交时都进行同步
InnoDB是事务的存储引擎，它通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指重做日志，在InnoDB存储引擎中，由两部分组成，即redo log和undo log
redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的
undo log（回滚日志）
回滚行记录到某个特定版本，用来保证事务的原子性、一致性
存储引擎层（InnoDB）生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了insert语句操作，那么undolog就记录一条与之相反的delete操作，主要用于事务的回滚（undolog记录的是每个修改操作的逆操作）和一致性非锁定读（undo log回滚行记录到某种特定的版本—mvcc，即多版本并发控制）
是原子性的保证，事务中更新数据的前置操作就是写入一个undo log
把回滚时需要的东西记录下来
只是逻辑日志，物理结构可能并不一样，对事务回滚时，只是将数据库逻辑地恢复到原来的样子，不是redo的逆日志
作用回滚数据、
mvcc（InnoDB中，mvcc是通过undo实现的
数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子
redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment），undo段位于共享表空间内。
其他
- 单台数据库达到性能瓶颈时
- 可以添加数据库代理
- 误操作drop语句导致数据库被破坏，请给出恢复的大致步骤
- 切割好bin-log日志文件，
- 将切割好的bin-log日志文件中的sql语句分离出来，并找到drop库的语句将其删掉
- 将之前全备数据导入数据库
- 将分离出的sql语句导入到数据库
- 将切割的bin-log日志文件删除，再次刷新bin-log日志，到此数据库已恢复成功
若有收获，就点个赞吧
0 人点赞
