复习 数据库

目录：

ACID

数据库的事务隔离级别

数据引擎

聚簇索引和非聚簇索引

索引

索引类型

Mysql优化

Mysql对于千万级大表怎么优化

Mysql 容灾 备份

 ACID

• 原子性 ： 保证事务中的所有操作全部执行或全部不执行
• 一致性 ： 保证数据库始终保持数据的一致性 事务操作之前和之后都是一致的
• 隔离性 ： 多个事务并发执行 和多个事务串行执行的  结果一样
• 持久性 ： 事务操作完成后 对数据的影响是持久的 即使数据库因故障而受到破坏 数据库也能恢复（日志）

数据库的事务隔离级别

• 串行化 ： 所有事务一个接着一个执行 避免幻读 对于基于锁实现的并发控制的数据库来说 串行化要求在执行范围查询时 需要获取范围锁  如果不是基于锁实现的并发控制的数据库 则检查到有违反串行的事务时 会回滚该事务
• 可重复读 ： 所有被Select获取的事务都不可以被修改 这样可以避免一个事务前后读取不一致的情况 但是没有办法控制幻读 因为虽然无法修改数据 但是可以增加数据  因为强恶意事务没有范围锁
• 读已提交 ：被读取的数据可以被其他事务修改 导致不可重复读  也就是 事务读取时获取读锁 读完立即释放（不需要等事务结束）而写锁是事务提交后才释放 释放读锁后 就有可能被其他事务修改  隔离等级也是SQL Server 的默认等级
• 读未提交 ：最低的隔离等级 允许其他事务看到没有提交的数据  会导致脏读

  级别由强到弱 ： 串行化 —— 可重复读 —— 读已提交 —— 读未提交 每个级别解决一个问题   级别越低  性能越差

数据引擎

在Mysql中 常用的数据引擎有两种 MyISAM  InnoDB

MyISAM  管理非事务表 如果有大量的索引和存储  MyISAM是更好的选择

InnoDB   用于事务处理应用程序  具有众多特性包括ACID事务支持 如果有大量的INSERT或UPDATE 用 InnoDB 可以提高多用户并发操作性能

两者的异同：

• 最主要区别是InnoDB 支持事务处理 外键 和行级锁    MyISAM 支持表级锁 不支持事务 行级锁 和外键
• MyISAM 的执行性能 比InnoDB 好很多 在少并发 的环境下  查找插入等要比InnoDB好很多
• 建议在事务高并发 或对事务有要求时使用InnoDB  MyISAM 保存形式是文件 在跨平台使用MyISAM转移数据会方便很多
• MyISAM是非事务安全型 InnoDB 是事务安全型
• MyISAM支持全文索引 InnoDB 不支持全文索引
• （行级锁指的是最小粒度锁）

具体差异：

• InnoDB不支持FULLTEXT的查找
• InnoDB不保持表的具体行数 只要执行select count（*）from table 就要全部检索一遍 而MyISAM 只要读出保存好的行数即可     注意： count（*）有where语句时 两种表的执行操作都是一样的
• 对于AUTO-INCREMENT类型的字段  MyISAM表中可以和其他字段一起联合索引 而InnoDB表中必须含有只有该字段的索引
• DELETEFROM table 时 InnoDB不会重新建表 而是一行一行的删除
• InnoDB是索引和数据紧密捆绑的 没有使用压缩会导致InnoDB 比 MyISAM 大不少 MyISAM 的索引和数据是分开的  而且索引有压缩 内存使用率高 可以加载更多索引

为什么MyISAM引擎查找比InnoDB引擎快？（聚簇索引和非聚簇索引）

InnoDB使用的是聚簇索引 InnoDB数据引擎本身就是索引文件 将主键组织到一颗B+树中 而行数据存储在叶子结点上 用where id =“14” ；这样条件查找主键 则按照B+树的索引算法即可查到对应的叶结点 之后获得行数据 若对Name列进行条件检索 则需要连两个步骤 第一步是在索引B+树中检索Name 到达其叶子结点获得主键 第二步使用主键在主索引B+树中再进行一次B+树索引  最后到达叶子节点 可以获得整行数据

MyISAM使用的是非聚簇索引  非聚簇索引的两颗B+树看起来没什么区别 节点的结构完全一致只是存储的内容不一致 主索引B+树节点存储的是主键 辅助键索引B+树存储的是辅助键 表数据存储在独立的地方 这两颗b+树的叶子结点都是用一个地址指向真正的表数据 对于真正的表数据来说这两个键没有任何区别 由于索引树是独立的 通过辅助键索引无需访问主键的B+数

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式

聚簇索引和非聚簇索引的区别：

聚簇索引一个表只能有一个，非聚簇索引一个表可以有多个。

聚簇索引的叶子节点存放的是主键值和数据行，一般为表的主键（主索引），支持覆盖索引。

非聚簇索引的叶子节点存放的是主键值或指向数据行的指针，因此在使用辅助索引进行查找时，需要先查找到主键值，然后在通过主索引进行查找。

相关链接：
https://blog.csdn.net/a549654065/article/details/100128772?ops_request_misc=%7B%22request%5Fid%22%3A%22158229262219724847037466%22%2C%22scm%22%3A%2220140713.130056874..%22%7D&request_id=158229262219724847037466&biz_id=0&utm_source=distribute.pc_search_result.none-task

索引

         因为在使用二叉树时候 查找数据因为二叉树深度过大造成I/O读写过于频繁 导致查询率低下  所以使用多叉树结构 B树的各种操作能使B树保持在一个较低的高度

•        直接创建索引和间接创建索引
•        普通索引和唯一性索引
•        聚簇索引和非聚簇索引
•        单个索引和符合索引

B树：

  B树又名平衡多路查找树 一颗m阶的B树特性如下

•      树中每个节点最多含有m个孩子（m>2）
•      除了根节点和叶子节点之外  其他每个节点至少有 【celi（m/2）】个孩子  （celi（x）是一个取上限的函数）
•      根节点至少有两个孩子 除非B树只有一个节点 ：根节点
•      所有的叶子结点都出现在同一层 叶子节点不包含任何关键字信息（可以看做是外部节点或查询失败的节点 实际上这些节点不存在 指向这些节点的指针都为null）

什么情况下适合建立索引？

• 为经常出现在关键字 order by ，group by，distinct 后面的字段建立索引
• 为经常用作查询选择的字段 建立索引
• 为经常建立表连接的属性上 建立索引
• 在unicon等集合操作上的结果集上建立索引
• 考虑使用索引覆盖 对数据很少被更新的表 如果用户经常只查询其中几个字段 可以考虑在这几个字段上建立索引 从而将表的扫描改为索引的扫描

索引的优缺点？

优点：

• 创建唯一性索引 保证数据表中所有的数据的唯一性
• 大大加快了数据的检索速度 这也是创建索引最主要的目的
• 加速数据库中表的连接 特别是在实现数据的参考完整性方面有很大意义
• 通过使用索引可以在查询中使用优化隐藏器提高系统的性能
• 在使用分组和排序子句进行数据检索时 可以减少查询中分组和排序的时间

缺点：

• 创建索引和维护索引需要时间 随着数据量增加而增加
• 索引需要占用物理空间 除了数据表要占用数据空间之外 每个索引都要占用一定的物理空间 如果建立聚簇索引需要的空间就会更大
• 当对表中的数据进行增删改时 索引也需要维护 增加了维护成本和时间 

索引失效（以下情况失效）

• 如果条件中有or 即使有条件带索引也不会使用  
• like查询以%开头时 索引失效
• 如果列类型是字符串 那么一定要用引号引起来 不然索引会失效
• 如果mysql自行估计扫描全表 比索引快 则不会使用索引
• 对于多列索引不是使用的第一部分 不使用索引

索引类型

• 普通索引 index  ： 这种索引是基于B+树来建立的 对于不同的数据引擎B+树不同 可以建立单列或者多列的索引 遵循从左到右匹配的规则
• 唯一索引 Unique-index ： Mysql的唯一约束性就是基于这个索引完成的
• 全文索引 fulltext  ： 这种索引主要是基于字符串的划分可以代替like 但是很慢尽量少用

B-树索引/B+树索引  B-树索引是节点存储数据 B+树是叶子节点存储数据 B+树的每个节点是一个数据块 在查询时读取一个数据库 一个数据块里可能有多个索引字段 所以在建立主键时 尽量选择size比较小的字段 这样一个数据块就可以存储较多的数据 减少I/O次数 提高查询效率 对于Innodb引擎来说尽量选择自增字段为主键 这样可以减少插入和更新时索引更新的复杂度

哈希索引 通索引字段和hash函数映射到磁盘上 和hash表类似

顺序索引 使用二分查找

为什么不用顺序索引？

  使用二分查找   假设有磁盘块b个 需要读取log2b才能找到索引项 如果数据库的顺序文件含有溢出块时 需要顺序读取多个磁盘块 查询效率过低

  随着文件增大索引查询功能和顺序扫描性能都会下降 主要是文件必须按照顺序文件组织 顺序索引一般是聚簇索引 非聚簇索引午饭建立稀疏索引 从而导致无法建立多级索引 如果是聚簇索引还需要数据文件的排序操作 如果通过溢出块处理 那么查找的性能会下降

Mysql优化

1. 数据引擎上 如果不需要事务的支持建议用MyIISAM数据引擎 查询插入和删除都很快捷

2. SQL语言上 建立索引后 尽量少用子查询 in ， exist ，null， is like， ！= 不要对where后面的语句使用函数 这些操作会导致索引不可用而导致全表扫描 不要用select*返回无用的字段 先where查询再join等

3. 数据缓存redis

Mysql对于千万级大表怎么优化

（1）优化sql和索引

（2）添加缓存 memcached redis

（3）主从复制 主主复制 读写分离 可以在应用层做 效率高 也可以用第三方工具做 比如360的atlas

（4）试用mysql自带的分区表 对应用透明无需更改代码 但是sql语句需要针对分区表做优化 sql条件中要带上分区条件的列 从而使查询定位到少量的分区上 否则会扫描全部分区

（5）分布式系统  如果以上都做了 就做垂直拆分 跟据模块的耦合度讲一个大的系统分为多个小系统也就是分布式系统

（6）最后考虑水平切分 最麻烦 考验技术 要选择一个合理的sharding key 为了有好的查询效率 表的结构也要改 做一定的冗余 应用也要改 sql中尽量带sharding key 将数据定位到限定的表上去查询 而不是扫描全部的表 

通常造成数据库瓶颈的原因是：

• 1、数据库系统本身性不佳，例如你用桌面数据库Access 来做web应用的后台数据库 显然是不行的；
• 2、数据库结构设计不合理，导致不必要的、过多的数据库访问，因此称为瓶颈，这需要优化数据表结构设计，必要时引入Cache系统；
• 3、数据库连接池设置不当，为了提供数据库的服务能力，在有潜在并发访问数据库的情况下，我们会使用连接池技术，如果池的大小 与实际需求不符，择会带来访问数据库时的排队等候，
• 4、程序实现适当，例如对 数据库连接长期占有，导致其它访问必须进行排队，因而成为瓶颈，
   

解决缓存一致性问题的方法有一下2种：

1. 通过在总线加LOCK#锁的方式
2. 通过缓存一致性协议

在早期的CPU当中，是通过在总线上加LOCK#锁的形式来解决缓存不一致的问题。因为CPU和其他部件进行通信都是通过总线来进行的，如果对总线加LOCK#锁的话，也就是说阻塞了其他CPU对其他部件访问，从而使得只有一个CPU能使用这个变量的内存。比如上面例子中，如果一个线程在执行i=i+1，如果在执行这段代码的过程中，在总线上发出了LOCK#锁的信号，那么只有等待这段代码完全执行完毕之后，其他CPU才能从变量i所在的内存读取变量，然后进行相应的操作。这样就解决了缓存不一致问题。

但是上面的方式会有一个问题，由于在锁住总线期间，其他CPU无法访问内存，导致效率低下。

所以就出现了缓存一致性协议。该协议保证了每个缓存中使用的共享变量的副本是一致的。它的核心思想是：当CPU向内存写入数据时，如果发现操作的变量是共享变量，即在其他CPU中也存在该变量的副本，会发出信号通知其他CPU将该变量的缓存置为无效状态，因此当其他CPU需要读取这个变量时，发现自己缓存中缓存行是无效的，那么它就会从内存重新读取。java中的volatile就是该协议的实现，volatile的实现原理可参见volatile底层实现。

https://blog.csdn.net/weixin_42359693/article/details/85400701?ops_request_misc=%7B%22request%5Fid%22%3A%22158237335619726867857695%22%2C%22scm%22%3A%2220140713.130056874..%22%7D&request_id=158237335619726867857695&biz_id=0&utm_source=distribute.pc_search_result.none-task

mysql 容灾 备份

https://blog.csdn.net/index_ling/article/details/53892866?ops_request_misc=%7B%22request%5Fid%22%3A%22158237349919726874032133%22%2C%22scm%22%3A%2220140713.130056874..%22%7D&request_id=158237349919726874032133&biz_id=0&utm_source=distribute.pc_search_result.none-task