数据库相关知识

索引

索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构

你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引

MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引

• 索引加快数据库的检索速度
• 索引降低了插入、删除、修改等维护任务的速度
• 唯一索引可以确保每一行数据的唯一性
• 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
• 索引需要占物理和数据空间

索引的存储 

一条索引记录中包含的基本信息包括：键值（即你定义索引时指定的所有字段的值）+逻辑指针（指向数据页或者另一索引页）

 当你为一张空表创建索引时，数据库系统将为你分配一个索引页，该索引页在你插入数据前一直是空的。此页此时既是根结点，也是叶结点。每当你往表中插入一行数据，数据库系统即向此根结点中插入一行索引记录。当根结点满时，数据库系统大抵按以下步骤进行分裂：
A）创建两个儿子结点
B）将原根结点中的数据近似地拆成两半，分别写入新的两个儿子结点
C）根结点中加上指向两个儿子结点的指针 

索引的优缺点

优点（提高数据检索，排序效率）

1可以提高数据检索效率，降低数据库的IO成本

2通过索引列对数据进行排序，降低数据排序成本，降低cpu的消耗

3提高表与表之间的连接，提高group by 分组效率

缺点（占用一定的存储空间，降低数据维护的效率）

1除了数据表占数据空间之外,每一个索引还要占一定的物理空间。即索引要占据数据库的物理存储空间,数据量越大,占用空间也越大

2虽然索引大大提高了查询速度,但是如果我们对表进行插入、修改、删除等操作的话,索引需要动态维护,所以会降低数据的维护速度。因为更新表时,不仅要保存数据,还需要花费时间去建立和维护索引,而且随着数据量的不断增加,维护成本也会不断加大

索引查询一定能提高查询的性能吗？

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索

什么情况下应不建或少建索引？

表记录太少

经常插入、删除、修改的表

数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

经常和主字段一块查询但主字段索引值比较多的表字段

事务

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务的四个基本属性

1. 原子性（Atomicity） 事务的原子性是指事务是一个不可分割的工作单位，这组操作要么全部发生，否则全部不发生。

2. 一致性（Consistency） 在事务开始以前，被操作的数据的完整性处于一致性的状态，事务结束后，被操作的数据的完整性也必须处于一致性状态。

3. 隔离性（Isolation） 事务隔离性要求系统必须保证事务不受其他并发执行的事务的影响，

4. 持久性（Durability） 一个事务一旦成功提交，它对数据库的改变必须是永久的，即便是数据库发生故障也应该不回对其产生任何影响。

DML.DDL.DCL.DQL

1)DQL(Dimensional Query Language)
           数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：SELECT <字段名表>FROM <表或视图名>WHERE <查询条件>

2).DML（data manipulation language）：

          DML用来对数据库里的数据进行操作的语言,;例如SELECT、UPDATE、INSERT、DELETE
 3).DDL（data definition language）：

        DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用,主要的命令有CREATE、ALTER、DROP、TRUNCATE等 

 4).DCL（Data Control Language）：

        DCL是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

drop、delete、truncate三种删除命令

差别

• delete和truncate只删除表的数据不删除表的结构
• 速度,一般来说: drop> truncate >delete
• delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
  如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

应用场景

• 不再需要一张表的时候，用drop
• 想删除部分数据行时候，用delete，并且带上where子句
• 保留表而删除所有数据的时候用truncate

超键、候选键、主键、外键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

三个范式。

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

视图

视图是从一个表或几个基本表导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存放在原理啊的基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。

视图一经定义，就可以和基本表一样被查询，被删除，也可以在一个视图上再定义一个新的视图，但对视图的更新（增、删、改）操作则有一定的限制。

视图的优缺点:

优点：

查询简单化。视图能简化用户的操作

数据安全性。能够对机密数据提供安全保护

视图使用者能以多种角度看待同一数据。

逻辑独立性。视图对重构数据库提供了一定程度的逻辑独立性

缺点：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据。

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

四个必要条件：

互斥条件，请求和保持条件，环路等待条件，不剥夺条件。

解决死锁

解决死锁思路，一般就是切断环路，尽量避免并发形成环路。

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁；

死锁与索引密不可分，解决索引问题，需要合理优化索引。

乐观锁和悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

MySQL的约束

NOT NULL: 约束字段的内容一定不能为NULL。

UNIQUE: 约束字段唯一性，一个表允许有多个Unique约束。

PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。

FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键。

CHECK: 用于控制字段的值范围。

UNION和UNION ALL的区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

UNION的效率高于UNION ALL

主键使用自增ID还是UUID

如果是单机的话，选择自增ID；如果是分布式系统，优先考虑UUID，但还是最好公司自己有一套分布式唯一ID生产方案。自增ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长的值范围，多库合并，也有可能出现问题。uuid：适合大量数据的插入和更新操作，但它是无序的，插入数据效率慢，占用空间大。

MySQL多表连接有哪些方式？怎么用的？这些连接都有什么区别？

连接方式：左连接、右连接、内连接

使用方法：

左连接：select * from A LEFT JOIN B on A.id=B.id;

右连接：select * from A RIGHT JOIN B on A.id=B.id;

内连接：select * from A inner join B on a.xx=b.xx;（其中inner可以省略）

区别：

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。