【数据库】基础

1.union 和union all的区别

• union会对结果集进行处理排除掉相同的结果
• union all 不会对结果集进行处理，不会处理掉相同的结果

2.preparedStatement和statement的区别

• 任何时候使用preparedStatement而不是statement
• PreparedStatement预编译，防止SQL注入
• PreparedStatement多次使用可提高效率

3.数据库三范式

• 第一范式（1NF）无重复的列（原子性），每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
• 第二范式（2NF）属性完全依赖于主键，如果有哪些数据只和主键的一部份有关的话，它就不符合第二范式。
• 第三范式（3NF）属性不依赖于其它非主属性，每个非关键字列都独立于其他非关键字列。

4.Mysql行级锁

MYISAM引擎只支持表级锁，而INNODB引擎能够支持行级锁，下面的内容也是针对INNODB行级锁展开的:

⑴ 共享锁

又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排它锁。

SELECT ... LOCK IN SHARE MODE;

会对查询出的每一条数据加共享锁，如果其它线程再加排它锁就会阻塞。

⑵ 排它锁

又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。
另外mysql的innodb对数据的update,delete,insert都会给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。所以加上排它锁普通的select还是可以用的。

5.事务的基本要素(ACID特性)

• 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
• 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
• 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
• 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

6.事务的并发问题

• 脏读 对于两个事务T1 和T2 ， T1 读取了已经被T2 更新但还没有被提交的字段。之后，若T2 进行回滚，T1读取的内容就是临时且无效的
• 不可重复读 是指在一个事务中两次读同一行数据，可是这两次读到的数据不一样
• 幻读 系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

7.事务的隔离级别

• 读未提交（Read Uncommitted）事务A读到事务B未提交的事务，就可能出现脏读
• 读已提交（Read Committed）事务A只能读到B已提交的事务（ORACLE等默认），
• 可重复读取（Repeatable Read）在同一个事务内的查询都是事务开始时刻一致的，事务A读则事务B就不能修改，可能出现幻读，但是MySQL的innodb通过MVCC(多版本并发控制)解决了幻读问题。
• 序列化（Serializable) 事务串行化执行，你读的时候别人都看不到

8.数据库查询语言分类

⑴DQL（Data Query Language）

数据查询语言DQL由SELECT子句，FROM子句，WHERE子句组成

⑵DML（Data Manipulation Language）

数据操纵语言DML包含INSERT，UPDATE，DELETE

⑶DDL（Data Definition Language）

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
DDL操作是隐性提交的！不能rollback

⑷DCL（Data Control Language）

数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。

9.mysql导出表结构

 sudo mysqldump -h10.1.210.15 -u用户名 -p密码 数据库名 device_bind_info>device_bind_info.sql
 格式：
 sudo mysqldump -h地址　-u用户名 -p密码　数据库名　表名>导出sql名

10.乐观锁和悲观锁

• 每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
• 悲观锁每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
• 两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

11.drop、truncate和delete的区别

• 执行速度 drop > truncate > delete
• delete一行一行删除，truncate 删除表全部数据，drop 连表数据和表结构一起删除
• delete 是 DML 语句，没提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚
• 有FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句
• delete删除行会出现id不联系，truncate是id重新开始

12.水平分区和垂直分区和读写分离

• 水平分区（Horizontal Partitioning） 这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
  举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。（注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）
• 垂直分区（Vertical Partitioning） 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
  举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
• 读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

13.Mysql执行计划

MySql提供了EXPLAIN语法用来进行查询分析

| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |

<