数据库原理相关

作者: bigfish1129 | 来源:发表于2018-05-24 16:07 被阅读0次

索引

1.1 什么是索引？

https://www.cnblogs.com/tgycoder/p/5410057.html
　　SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间

聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了

使用索引的场景

定义主键的数据列一定要建立索引。
定义有外键的数据列一定要建立索引。
对于经常查询的数据列最好建立索引。
对于需要在指定范围内的快速或频繁查询的数据列;
经常用在WHERE子句中的数据列。
经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。
对于经常存取的列避免建立索引
限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

MySQL就普遍使用B+Tree实现其索引结构。
B+Tree有以下不同点：

每个结点的指针上限为2d而不是2d+1。
==内结点不存储data，只存储key；叶子结点不存储指针。==

image.png

非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项

1.索引字段要尽量的小：
2.索引的最左匹配特性（即从左往右匹配）：

索引的分类

https://www.cnblogs.com/bypp/p/7755307.html
索引分类
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束（唯一）
3.联合索引
- primary key(id,name):联合主键索引
- unique(id,name):联合唯一索引
- index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
不同的存储引擎支持的索引类型也不一样：
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

正确的使用索引

一、覆盖索引
select id from s1 where id=123;
这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快

二、联合索引

三、索引合并

最左前缀匹配原则，非常重要的原则，
create index ix_name_email on s1(name,email,)

最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器
会帮你优化成索引可以识别的形式
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，
表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、
性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，
这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

事务：

https://www.cnblogs.com/roucheng/p/javashiwu.html
事务必须服从ISO/IEC所制定的ACID原则。
ACID是原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）的缩写。

事务的原子性表示事务执行过程中的任何失败都将导致事务所做的任何修改失效。
一致性表示当事务执行失败时，所有被该事务影响的数据都应该恢复到事务执行前的状态。
隔离性表示在事务执行过程中对数据的修改，在事务提交之前对其他事务不可见。
持久性表示已提交的数据在事务执行失败时，数据的状态都应该正确。

Java事务的类型

Java事务的类型有三种：JDBC事务、JTA（Java Transaction API）事务、容器事务。

分库分表

分库分表
所谓分库分表，就是把原来存储于一个数据库里的数据分块存储到不同的数据库中，把原来存储在一个表里的数据分块存储到不同的表中。

分库分表的常见形式
垂直分库
垂直分库，基本的思路就是按照业务模块来划分出不同的数据库，将原存储于一个数据库中的业务数据分存储于不同数据库中。
如下图示意：

image.png

垂直分表
垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”（字段）进行的。通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中。
如下图示意：

image.png

水平分表
水平分表也称为横向分表，比较容易理解，就是将表中不同的数据行按照一定规律分布到不同的数据库表中（这些表保存在同一个数据库中），这样来降低单表数据量，优化查询性能。
如下图示意：

image.png

水平分库分表
水平分库分表与上面水平分表的思想相同，唯一不同的就是将这些拆分出来的表保存在不同的数据库中。
如下图示意：

image.png