数据库索引-复制-事务详谈

数据库学习笔记

标签（空格分隔）： 数据库

数据库

1. 数据库优化的一些手段

• 尽量避免 null 类型的列
  使得索引、索引统计、值比较都比较复杂、使用更多的存储空间、null 的列为索引时，每个索引记录需要额外一个字节、
• datetime timestamp
  timestamp 只使用 datetime 一半的存储空间。并且会根据时区变化。
• 整数类型
  tinyint、smallint、mediumint、int、bigint 分别使用 8/16/24、32/64 位存储空间
  unsigned 属性表示不允许负值

• 字符串类型
  varchar char 的区别
  varchar 存储可变长的字符串、需要用额外 1、2 一个字节记录字符串长度信息、适合最大长度比平均长度大很多。。列的更新很少，碎片不是问题
  char 是定长的，适合经常变更的数据、因为不容易产生碎片。也适合非常短的列、比如 char(1)存储 y 和 n 的值，char 只需要一个字节，而 varchar 需要两个字节，额外一个字节存储长度信息

2. 索引知识

分为 btree 索引和哈希索引

数据库索引的实现
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
创建索引可以大大提高系统的性能。

• 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 第三，可以加速表和表之间的连接，
• 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。

• 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。和三一个意思
• 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

加一条:

并不是在所有查询条件下出现的列都需要添加索引,访问表中很少一部分行时,使用 b+ 树才有意义.对于像性别,地区,类型字段,没必要加.因为可取值范围很小,会取出数据库中大部分数据.查询优化器不会使用索引.如果某个字段取值范围广,几乎没有重复.使用 B+ 树索引是最合适的.当取出的数据占表中大部分数据时,查询优化器不会使用 B+ 树索引.根据专家的研究,当取出的数据量超过表中数据量的 20% 时,优化器不会使用索引.而是进行全表扫描.

误区改正

B+ 树索引并不能找到一个给定键值的具体行,而是被查找数据所在的页.然后数据库通过把页读入内存,再在内存中进行查找得到数据!!这个我以前也不知道啊.这样感觉靠谱多了

数据库集群负载均衡算法

1、最简单的办法，随机
2、轮询
3、最快响应
4、哈希
5、权重
6、最少连接数
哪种算法最优取决于具体的工作负载，比如最少连接数算法，当有新机器加入集群时，大量的连接会涌向该机器。这时候缓存中数据还没有预热，反而达不到最好的效果
当一台服务器请求数过多时，限定最大并发数，超过的请求放在队列中，分发到其他空闲的机器上

3. 分区：

为什么要分表和分区？
日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD 数据文件，.MYI 索引文件，.frm 表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app 读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app 读写的时候操作的还是大表名字，db 自动去组织分区的数据。

mysql 分表和分区有什么联系呢？

1. 都能提高 mysql 的性高，在高并发状态下都有一个良好的表现。
2. 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果 merge 这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
3. 分表技术是比较麻烦的，需要手动去创建子表，app 服务端读写时候需要计算子表名。采用 merge 好一些，但也要创建子表和配置子表间的 union 关系。
   4.表分区相对于分表，操作方便，不需要创建子表。

3. 数据库拆分类型

• 垂直拆分
  把关联性不强的表拆分到不同的 dbserver
• 水平拆分
  适用于单表数据量很大的时候.根据字段来拆分.如根据 userid 再哈希分表.把不同字段分到不同的库中.常用字段和不常用字段分开存储.

4. 拆分之后面临的问题

a. 事务问题

• 使用分布式事务[细节]
  由数据库管理事务,这样性能代价高!
• 由应用程序和数据库共同控制
  将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务，并通过应用程序来总控各个小事务。性能有优势.需要应用程序在事务控制上做灵活设计。如果使用了 spring 的事务管理，改动起来会面临一定的困难。

b. 跨节点 join

• 解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的 id,根据这些 id 发起第二次请求得到关联数据。

c. 跨节点的 count,order by,group by 以及聚合函数问题

• 这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点 join 问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

5. 生成全局唯一 ID 的问题

发现了一种新的方法
auto_increment_increment/auto_increment_offset
在只有两台的情况下：
可以配置这两台服务器的自增幅度为 2，其中一台偏移量设置为 1、另一台设置为 2，这样一个生成的一定是奇数，另一个生成的是偶数，不会重复
那么多台呢？
我觉得是偏移量设置不同的数据段?错!

6. mysql 复制

两种方式：基于行的复制 和 基于语句的复制
都是通过在主库存放二进制日志，从库重放日志的方式实现异步数据同步，并且无法保证主备之间的延迟，一些大的语句可能导致几秒，几分钟甚至几个小时的延迟

一主多备，这样重复数据很多，服务器缓存中也存储了大量相同的数据，这是不合理的，讨论如何解决？

复制的步骤：

1. 主库把数据变更记录到二进制日志
2. 从库将主库的日志复制到自己的中继日志（首先备库启动一个工作线程，io 线程，建立和主库的 socket 连接，然后在主库启动一个二进制转储线程，把二进制写进备库的中继日志。没有任务可做时进入睡眠状态，而不会轮询。独立于 sql 线程，是单线程，瓶颈就在这里）
3. 从库通过中继日志把数据保存到从库中

这种架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说 io 线程能够独立于 sql 线程工作。但是这种架构也限制了复制的过程。其中最重要的一点是主库上并发运行的查询在从库上只能串行化执行。因为只有一个 sql 线程重放中继日志的事件。这是很多负载均衡的性能瓶颈所在。虽然有一些针对该问题的解决办法。但是大多数用户仍然受制于单线程。

有三种日志文件，区别如下：
Redo log

记录的是新数据的备份。在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，
但是 RedoLog 已经持久化。系统可以根据 RedoLog 的内容，将所有数据恢复到最新的状态。

binlog

binlog 日志用于记录所有更新且提交了数据或者已经潜在更新提交了数据（例如，没有匹配任何行的一个 DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

binlog 作用

1.恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。
2.在主复制服务器上记录所有将发送给从服务器的语句。

上面的说法不是很清晰，再换一种说法

mysql使用3个线程来执行复制功能（一个在主服务器上，另外两个在从服务器上）。当发出start slave时，从服务器创建一个io线程。以连接主服务器并发送它记录在二进制日志中的语句。主服务器创建一个线程将二进制日志中的文件发送到从服务器。然后从服务器io线程把语句保存在本地的中继日志中。从服务器开启第三个线程：sql线程，从中继日志中读取日志执行。

问题就出在这里。哈哈，因为服务器上并发执行的语句在这里统统都是串行执行。不慢才怪呢。 所以很多的解决方案都是把单线程复制改为多线程。淘宝的canal也是一种。我还没怎么用。细节不清楚

复制配置

7. 事务隔离级别

未提交读
当前事务未提交、其他事务也能读到

提交读
当前事务提交之后，其他事务才能看到

可重复读
该级别解决了同一事务多次读取同样记录的结果是一致的。但是理论上，还是无法解决另一个幻读问题。
幻读 是指当前事务读取某个范围内的记录时，其他事务在该范围又加入新的纪录，之前的事务再次从该范围读取数据时，会产生幻行。这个的换行是对于 insert 操作来说的，对于 update 操作能保证没有幻行问题。

innodb 通过多版本并发控制来解决这个问题
该级别是 mysql 默认的事务隔离级别
####不可重复读和幻读的区别####
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于 update 和 delete，而幻读的重点在于 insert。

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

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是 MySQL、ORACLE、PostgreSQL 等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的 MVCC（多版本并发控制）来避免这两种问题。

可串行化
最高的事务隔离级别
强制事务串行化执行，避免了幻读问题，这种级别会在数据的每一行都加锁，会产生大量的超时和锁竞争，实际中很少用到，除非要确保数据的一致性且没有并发问题

数据库死锁问题解决办法
数据库实现了各种死锁检测和死锁超时机制

mysql 中的事务

8. 多版本并发控制细节（MVCC）

可以认为 mvcc 是行级锁的变种，在很多情况下避免了加锁操作，因此开销更低，实现思路大体都是非阻塞的读，写操作只锁定必要的行

- 每行数据都存在一个版本，每次数据更新时都更新该版本
- 修改时Copy出当前版本随意修改，个事务之间无干扰
- 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道。。。，而 Innodb 的实现方式是：

- 事务以排他锁的形式修改原始数据
- 把修改前的数据存放于undo log，通过回滚指针与主数据关联
- 修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

二者最本质的区别是，当修改数据时是否要排他锁定，如果锁定了还算不算是 MVCC？

Innodb 的实现真算不上 MVCC，因为并没有实现核心的多版本共存，undo log 中的内容只是串行化的结果，记录了多个事务的过程，不属于多版本共存。但理想的 MVCC 是难以实现的，当事务仅修改一行记录使用理想的 MVCC 模式是没有问题的，可以通过比较版本号进行回滚；但当事务影响到多行数据时，理想的 MVCC 据无能为力了。

比如，如果 Transaciton1 执行理想的 MVCC，修改 Row1 成功，而修改 Row2 失败，此时需要回滚 Row1，但因为 Row1 没有被锁定，其数据可能又被 Transaction2 所修改，如果此时回滚 Row1 的内容，则会破坏 Transaction2 的修改结果，导致 Transaction2 违反 ACID。

理想 MVCC 难以实现的根本原因在于企图通过乐观锁代替二段提交。修改两行数据，但为了保证其一致性，与修改两个分布式系统中的数据并无区别，而二提交是目前这种场景保证一致性的唯一手段。二段提交的本质是锁定，乐观锁的本质是消除锁定，二者矛盾，故理想的 MVCC 难以真正在实际中被应用，Innodb 只是借了 MVCC 这个名字，提供了读的非阻塞而已。
5.总结
也不是说 MVCC 就无处可用，对一些一致性要求不高的场景和对单一数据的操作的场景还是可以发挥作用的，比如多个事务同时更改用户在线数，如果某个事务更新失败则重新计算后重试，直至成功。这样使用 MVCC 会极大地提高并发数，并消除线程锁。

分布式事务(XA)
多个独立的事务资源参与到一个全局的事务中.。事务资源通常是 RDBMSs，不过也可以是其它种类的资源。
对于一个分布式事务，您必须使用 SERAILIZABLE 隔离等级，以实现 ACID 性质。对于一个非分布式事务，使用 REPEATABLE READ 就足够了。但是对于分布式事务，使用 REPEATABLE READ 是不够的。
数据库分布式事务也使用了两段式提交的规则.和 zookeeper 的协议多么相似..

MySQL 表最大能达到多少

MySQL 3.22 限制的表大小为 4GB。由于在 MySQL 3.23 中使用了 MyISAM 存储引擎，最大表尺寸增加到了 65536TB（2567 – 1 字节）。由于允许的表尺寸更大，MySQL 数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的，而不是由 MySQL 内部限制决定的。
InnoDB 存储引擎将 InnoDB 表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为 64TB。
在下面的表格中，列出了一些关于操作系统文件大小限制的示例。这仅是初步指南，并不是最终的。要想了解最新信息，请参阅关于操作系统的文档。
操作系统
文件大小限制

OLAP:在线事务分析
OLTP在线事务分析

表空间.默认是一个表空间,设置之后可以按表分表空间.
分区:
分区的类型:

• range 分区.根据分区列的值来分区.不同范围的值放在不同区.应用:日期列的分区,按年份放到不同分区.
  好处:删除 08 的数据,只需要删除 08 所在的分区即可
  加快某些查询操作:查询 08 的销售额: explain partitions select * from sales where date>'2008'
• list 分区
  分去的值是离散的
  partition by list(b)
  partition p0 values in(1, 3, 4, 5)
• hash 分区
  1.要被 hash 的列值指定一个表达式,由用户自己定义
  2.指定分区数量

• key 分区
  和 hash 分区类似.不过分区的方法由 mysql 数据库提供的函数来分区

注意事项:
只支持水平分区,不支持垂直分区.
当表中存在主键或者索引时,分区列必须是唯一索引的一个组成部分

分区的性能
对于分区要辩证的看.
对于 OLAP 应用:分区一般可行.应该要频繁扫描一张大表,如果按时间戳分区,只会扫描一个分区
对于 OLTP 应用:一般都是需要几条数据而已.由 B+ 树的索引原理可知,对于一张大表,一般的 B+ 树需要 2-3 的磁盘 IO,
1.分区后,对于只是针对主键的查询,的确可以提高查询速度.只需要扫描一个分区即可
2.对于针对非主键的查询,则要扫描全部分区,如果分成了 10 个分区,一般要 20 多次磁盘 io,而不分区的查询只是 2 次磁盘 io(假设每个分区 2 次磁盘 io)
索引,优化

9. mysql 5.7 最新特性

• Security improvements.mysql.user table 不允许密码不允许为空
• Online ALTER TABLE.修改索引的时候,不执行 table copy 的操作
• InnoDB enhancements(增强)
• JSON support.
• the innodb_buffer_pool_size parameter is dynamic:不需要重启机器就能重新分配 buffer size

10. redis 和 memcached 比较

一篇比较 redis 和 memcached 的文章

• 网络 IO 模型
  Memcached 是多线程，非阻塞 IO 复用的网络模型，分为监听主线程和 worker 子线程，监听线程监听网络连接，接受请求后，将连接描述字 pipe 传递给 worker 线程，进行读写 IO, 网络层使用 libevent 封装的事件库，多线程模型可以发挥多核作用，但是引入了 cache coherency 和锁的问题，比如，Memcached 最常用的 stats 命令，实际 Memcached 所有操作都要对这个全局变量加锁，进行计数等工作，带来了性能损耗。
  
  Redis 使用单线程的-多路复用 IO 模型，自己封装了一个简单的 AeEvent 事件处理框架，主要实现了 epoll、kqueue 和 select，对于单纯只有 IO 操作来说，单线程可以将速度优势发挥到最大，但是 Redis 也提供了一些简单的计算功能，比如排序、聚合等，对于这些操作，单线程模型实际会严重影响整体吞吐量，CPU 计算过程中，整个 IO 调度都是被阻塞住的。

• 内存管理方面
  Memcached 使用预分配的内存池的方式.新数据可能被踢出
  Redis 使用现场申请内存的方式来存储数据,非临时数据不会被踢出

• 数据一致性问题
  Memcached 提供了 cas 命令，可以保证多个并发访问操作同一份数据的一致性问题。 Redis 没有提供 cas 命令，并不能保证这点，不过 Redis 提供了事务的功能，可以保证一串 命令的原子性，中间不会被任何操作打断。

• 存储方式及其它方面
  Memcached 基本只支持简单的 key-value 存储，不支持枚举，不支持持久化和复制等功能
  Redis 除 key/value 之外，还支持 list,set,sorted set,hash 等众多数据结构，提供了 KEYS
  进行枚举操作，但不能在线上使用，如果需要枚举线上数据，Redis 提供了工具可以直接扫描其 dump 文件，枚举出所有数据，Redis 还同时提供了持久化和复制等功能。

12. redis 基于 AEevent 的事件处理

Redis 实现了自己的一套和 libevent 类似的事件驱动机制，主要用于处理时间事件和文件事件。文件事件底层主要是指网络 IO 事件的处理，底层使用的可能是 select,epoll,或者是 kqueue。Redis 使用自己实现的 AE 而不是像 memcache 使用的 libevent 使得其性能更好，因为 libevent 为了其通用性增加了很多扩展功能显然会降低使用它的性能。

简单说来 Redis 使用的事件处理机制就是通过一个主 aeMain 循环在单线程执行，在每一次循环中首先查看是否需要有其他阻塞的客户端或者是 aof 需要执行。然后在具体的 aeProcessEvents 中则根据传递的参数判断如何处理文件事件和时间事件。