数据库知识学习笔记

数据库

标签： 数据库

点击查看markdown链接

---

这是个人学习笔记。内容比较多而且杂，不是适合所有人看。只是个人学习的总结

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.表分区相对于分表，操作方便，不需要创建子表。

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

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

5. mysql复制

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

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

复制的步骤：

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

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

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

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

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

复制配置

6. 事务隔离级别

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

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

可重复读
该级别解决了同一事务多次读取同样记录的结果是一致的。但是理论上，还是无法解决另一个幻读问题。
幻读 是指当前事务读取某个范围内的记录时，其他事务在该范围又加入新的纪录，之前的事务再次从该范围读取数据时，会产生幻行
innodb通过多版本并发控制来解决这个问题
该级别是mysql默认的事务隔离级别

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

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

mysql中的事务

7. 多版本并发控制细节（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)
索引,优化

8. 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

9. 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还同时提供了持久化和复制等功能。

3. redis 基于AEevent的事件处理

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

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