数据库漫谈
数据库漫谈
常见的关系型数据库和非关系型数据及其区别
关系型数据库
关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织
优点：
- 易于维护：都是使用表结构，格式一致；
- 使用方便：SQL语言通用，可用于复杂查询；
- 复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
- 读写性能比较差，尤其是海量数据的高效率读写；
- 固定的表结构，灵活度稍欠；
- 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
非关系型数据库
非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。
优点：
- 格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
- 速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
- 高扩展性；
- 成本低：nosql数据库部署简单，基本都是开源软件。
缺点：
- 不提供sql支持，学习和使用成本较高；
- 无事务处理；
- 数据结构相对复杂，复杂查询方面稍欠。
非关系型数据库的分类和比较：
- 文档型
- key-value型(Hive)
- 列式数据库
- 图形数据库(Neo4j)
SQL之五种JOIN连接及各类型JOIN解析
JOIN连接之内连接(INNER JOIN)
- INNER JOIN内连接也叫显性内连接,展示的是两表之间的交集，即获取的是符合查询条件的信息
- 隐形内连接：where连接
JOIN连接之左连接/左外连接(LEFT JOIN/LEFT OUTER JOIN)
左连接是以LEFT JOIN为基准进行查询，左连接查询的就是左表的全部信息和符合查询条件的信息两部分。
如果左表展示出来的全部信息，右表没有相对应的信息，则右表记录为null。
JOIN连接之右连接/右外连接(RIGHT JOIN/RIGHT OUTER JOIN)
右连接与左连接正好相反，以RIGHT JOIN为基准，展示的信息是RIGHT JOIN右边右表的全部信息加上左右两边符合关联查询条件的。 如果右表展示出来的全部信息，左表没有相对应的信息，则左表记录为null。
JOIN连接之全连接(FULL JOIN)
Full outer join展示的是a表和b表的全部信息(a和b的并集)。但需要注意的是，对于没有匹配的记录(即a.id和b.id没有一一对应的)，则会以null作为值。可以使用IFNULL判断。
JOIN连接之交叉连接(笛卡尔积)(CROSS JOIN)
不带where条件子句，它返回的是被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积
如果带where，返回或显示的是匹配条件成立的行
数据库索引
索引的作用和缺点
作用：（IO、唯一、分组排序）
- 提高数据检索效率，减少IO成本
- 通过创建唯一性索引，可以保持数据库表中每一行数据的唯一性
- 减少查询中分组和排序的时间
缺点：（时空占用、增删改）
- 创建索引和维护索引要耗费时间，而且时间随着数据量的增加而增大
- 索引需要占用物理空间，如果要建立聚簇索引，所需要的空间会更大
- 在对表中的数据进行增删改时需要耗费较多的时间，因为索引也要动态地维护
索引的使用场景
应创建索引的场景
- 经常需要搜索的列上
- 作为主键的列上
- 经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
- 经常需要根据范围进行搜索的列上
- 经常需要分组排序的列上
- 经常使用在where子句上面的列上
不应该创建索引的场景
- 查询中很少用到的列
- 对于那些具有很少数据值的列，比如数据表中的性别列，bit数据类型的列（就两种值）
- 对于那些定义为text，image的列，因为这些列的数据量相当大
- 当对修改性能的要求远远大于搜索性能时，因为当增加索引时，会提高搜索性能，但是会降低修改性能
索引的分类与说明
主键索引 设定为主键后数据库会自动建立索引，innodb为聚簇索引
单列索引 一个索引只包含单个列，一个表可以有多个单列索引
唯一索引 索引列的值必须唯一，但允许有空值
复合索引 一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建索引） 如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引，形成索引覆盖可以提高查询的效率， 复合索引遵从最左前缀原则
聚集索引 指索引项的排序方式和表中数据记录排序方式一致的索引。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放只能有一种排列方式，所以一个表只能有一个聚集索引。
非聚集索引
与聚集索引相反，索引顺序与物理存储顺序不一致。 非聚集索引的使用场合为： 查询所获数据量较少时； 某字段中的数据的唯一性比较高时；
非聚集索引必须是稠密索引
稠密索引 在稠密索引中，文件中的每个搜索码值都对应一个索引值，也就是说，稠密索引为数据记录文件的每一条记录都设一个键-指针对。
稀疏索引 在稀疏索引中，只为搜索码的某些值建立索引项，也就是说，稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存存储单元连续
数据库的锁
锁的分类
按数据操作的类型分类
- 读锁（共享锁，Share Lock）：针对同一份数据，多个读操作可以同时进行而不会互相影响。若事务T对数据对象A加上读锁，则事务T只能读A；其他事务只能再对A加读锁，而不能加写锁，直到事务T释放A上的读锁。这就保证了其他事务可以读A，但在事务T释放A上的读锁之前不能对A做任何修改。
- 写锁（排它锁，Exclusive Lock）：写锁只可以加一个，当前写操作没有完成前，它会阻断其他写锁和读锁。若事务T对数据对象A加上写锁，则只允许事务T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在增删改操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。
按数据操作的颗粒度分类
- 表锁：MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：表锁开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低
- 行锁：MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。行级锁分为共享锁和排他锁。InnoDB与MyISAM的最大不同有两点：一是支持事务;二是采用了行级锁。
特点：行锁开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 页锁：页锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间介于表锁和行锁之间：会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。
死锁和避免死锁
产生死锁的必要条件
- 互斥条件：一个资源只能被一个线程占有，当这个资源被占用后其他线程就只能等待。
- 请求和保持条件：当进程因请求资源而阻塞时，对已获得的资源保持不放。
- 不剥夺条件：当一个线程不主动释放资源时，此资源一直被拥有线程占有。
- 循环等待条件：在发生死锁时，必然存在一个进程–资源的环形链。
如何解决死锁问题
- 预防死锁
资源一次性分配：一次性分配所有资源，这样就不会再有请求了：（破坏请求条件）
可剥夺资源：即当某进程获得了部分资源，但得不到其它资源，则释放已占有的资源（破坏不可剥夺条件）
资源有序分配法：系统给每类资源赋予一个编号，每一个进程按编号递增的顺序请求资源，释放则相反（破坏环路等待条件）
- 避免死锁
银行家算法
- 检测死锁
首先为每个进程和每个资源指定一个唯一的号码；然后建立资源分配表和进程等待表。
- 解除死锁
当发现有进程死锁后，便应立即把它从死锁状态中解脱出来，常采用的方法有：
剥夺资源：从其它进程剥夺足够数量的资源给死锁进程，以解除死锁状态；
撤消进程：可以直接撤消死锁进程或撤消代价最小的进程，直至有足够的资源可用、死锁状态消除为止；所谓代价是指优先级、运行代价、重要性和价值等。
数据库的避免死锁
- 通过表级锁来减少死锁产生的概率。对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率
- 多个程序尽量约定以相同的顺序访问表。如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁的产生概率。（资源有序分配法）
- 同一个事务尽可能做到一次锁定所需要的所有资源，这样可以减少死锁产生概率（资源一次性分配）
乐观锁与悲观锁
悲观锁与乐观锁是根据操作时是否锁住资源来判别的。悲观锁获取到锁时，必须要锁住资源；乐观锁则不会。
悲观锁
悲观锁之所以悲观，那是因为它觉得如果不锁住这个资源，别的线程就会来争抢，造成数据结果错误，所以悲观锁为了确保结果的正确性，会在每次获取并修改数据时，都把数据锁住，让其他线程无法访问该数据，这样就可以确保数据内容万无一失，从这点看悲观锁特别稳。
像 Java 中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。
悲观锁通常多用于写多比较多的情况下（多写场景），避免频繁失败和重试影响性能。
乐观锁
乐观锁顾名思义，比较乐观。相比于悲观锁，它是不锁住资源的，因为它觉得自己在操作资源时并不会有其他线程干扰。因此，为了保障数据的正确性，它在操作之前，会先判断在自己操作期间，其他线程是否有操作。如果没有，直接操作；如果有，则根据业务选择报错或者重试。
乐观锁的这把锁，其实就是依赖的 CAS （compare and swap：比较并交换）算法或版本号机制。所以，它在操作资源之前并不需要获得锁，直接读取资源到自己的工作内存内操作。
乐观锁通常多于写比较少的情况下（多读场景），避免频繁加锁影响性能，大大提升了系统的吞吐量
数据库的乐观锁和悲观锁
- 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
实现方式：使用数据库中的锁机制
- 乐观锁：假设不会发生并发冲突，只在事务提交时检查是否违反数据完整性。乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。
实现方式：一般会使用版本号机制或CAS算法实现。
数据库优化
选取最适用的字段属性
- 在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
- 尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。
使用连接（JOIN）来代替子查询(Sub-Queries)
连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
使用联合(UNION)来代替手动创建的临时表
union可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。
使用事务
要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。
要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。
事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。（锁库）
改变锁的粒度
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
有些情况下我们可以通过锁表或锁行的方法来获得更好的性能。
使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。
优化查询语句
- 最好是在相同类型的字段间进行比较的操作
- 在建有索引的字段上尽量不要使用函数进行操作。
- 应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
为数据库分配更多内存
数据库一般部署在云服务器上，为数据库分配更大内存应对较大数据操作量的场景
文件格式优化（分布式数据库）
如：Hive的⽂件存储格式包括以下⼏类：TEXTFILE、SEQUENCEFILE、RCFILE、ORCFILE。其中TEXTFILE为默认格式，建表时不指定则默认为这个格式，导⼊数据时会直接把数据⽂件拷⻉到hdfs（Haddop文件系统）上不进⾏处理。部分较⼤数据源的存储格式可以由TEXTFILE变为ORCFILE。