复习数据库

复习着发现可以与数据结构等内容联系起来，赞。

数据库事务

数据库中一组原子性的SQL操作，彼此状态一致。具有ACID特性。^[1]

1. 原子性：数据库事务是一个整体，其中的SQL操作要么全部提交成功commit要么全部失败回滚rollback，不可分割；
2. 一致性：与原子性有联系。事务总是从一个一致状态转换到另一个一致状态；
3. 隔离性：事务之间彼此互不影响，一个事务在提交之前，对其他事务是不可见的。
4. 持久性：一个事务一旦提交成功，他所做的修改就会永久性的存储在数据库中。

MySQL的4种隔离级别

1. read uncommitted 读未提交：一个事务在提交之前，对其他事务是可见的，即事务可以读取未提交的数据。存在“脏读”（读到了脏数据）问题；
2. read committed 不可重复读：事务在提交之前，对其它事务是不可见的。存在“不可重复读”（两次查询的得到的结果可能不同，即可能在查询的间隙，有事务提交了修改）问题。解决了“脏读”问题。
3. repeatable read：在同一事务中多次读取的数据是一致的。解决了脏读和不可重复读问题，存在“幻读”（在事务两次查询间隙，有其他事务又插入或删除了新的记录）。— MySQL默认隔离级别。
4. serializable可串行化：强制事务串行化执行。即一个事物一个事物挨个来执行，可以解决上述所有问题。

锁、粒度

1. 共享锁、读锁：互不阻塞，优先级低；
2. 排他锁、写锁：阻塞其他锁，优先级高，即确保在一个事务写入时不受其他事务的影响。
3. 锁粒度：锁定的数据量越少（粒度越小），并发程度越高，但相应的加锁、检测锁、释放锁用的系统开销也随之增大。
4. 锁策略：锁开销与数据安全性之间的平衡
   1. 表锁：锁住整张表，读锁互不阻塞，写锁阻塞其他所有读写锁（同一张表）。开销最小。
   2. 行级锁：对每一行数据（记录）加锁，开销大，并发程度高。

InnoBD对死锁的处理

此处死锁与OS死锁类似，多个事务互相持有对方所有要申请资源的锁不释放，造成环路死锁。

MySQL InnoDB引擎在检测到死锁循环依赖后，回滚到持有最少行级锁的事务。

索引的作用、实现方法

1. 概念：对数据库表列进行恰当索引，以快速找到匹配的记录行数，相较于全表扫描，可大大加快查找的速度。
2. 作用：加快查找速度。
3. 实现方法：B+树索引、哈希索引 等
   1. B+树索引：在B-tree上改进得到，其非叶子节点均为key值，叶子节点是key-data键值对。叶子节点前后有序且相连。
   2. 哈希索引：哈希索引：通过对key进行hash(crc/MD5/sha128/sha256…)，将记录存储在不同bucket中，以实现常数级的查找。需要注意避免哈希冲突（链表法、线性探测、二次探测、公共溢出区）。默认无序
   3. 为什么有了B+树索引还要hash索引？
      1. B+树默认有序，而hash默认无序，所以hash索引无法用于排序。
      2. 哈希索引O(1) 快于 B+树O(logn)；
      3. 哈希索引只能进行等值查询，而B+树可以进行等值、部分前缀、范围查询。
      4. 底层实现不同：B+树是非线性结构，hash桶是线性结构。
      5. 对于某些场景，热点页、活跃查询页，需要借助哈希索引实现快速查询。
   4. 索引越多越快？
      1. 否。索引使用B+树、hash桶数据结构实现，会占用内存、维护需要系统开销，一般的插入删除都要进行结构的调整。这些操作都消耗时间，所以索引太多反而拖慢查找时间。
      2. 有时，数据量不多时，建立索引还不如全表查询。
      3. 索引在加快了检索速度的同时，插入删除修改都需要DBMS动态更新内部索引结构，耗费开销。

InnoDB MVCC

多版本并发控制，是为了避免加锁而实现的。一般的实现方法是存储快照来实现的。

InnoDB实现方式是在记录后添加两个隐藏列（表项），分别是事务创建时间、过期时间，存储系统版本号（系统版本号随着事务的创建而递增）？？？

INSERT 时加上开始版本号，UPDATE/DELETE时加上过期版本号，这样一来在SELETE时，就只访问开始系统版本号小于当前的事务的版本号、过期时间要么未定义要么在当前版本号之后的记录，这样就可以保证：访问的记录是在本事务开始前就存在而且在本事务期间没有过期（被删除或被修改过的）。可以避免脏读、不可重复读、幻读的问题。（个人觉得）

（此处有点迷糊，当做参考吧）

MySQL存储引擎简介

1. InnoDB，支持事务，行级锁，间隙锁（避免幻读），热备份，MVCC，并发较好，系统资源占用多。

2. MyISAM，默认存储引擎，不支持事务、行级锁，只支持表锁，存储性能有优势，查询速度快，系统占用资源少。

4. InnoDB	MyISAM
   支持MVCC	不支持
   不支持全文索引	支持全文索引
   B+树索引、自适应哈希索引	B+树索引
   支持外键	不支持
   前缀压缩技术，占用存储空间更小，但影响查找	原始数据存储
   支持事务	不支持事务
   支持行级锁、表锁	支持表锁

SQL优化

1. 在经常性的检索列上，建立必要索引，以加快搜索速度，避免全表扫描（索引覆盖扫描）；
2. 多次查询同样的数据，可以考虑缓存该组数据；
3. 审视select * form tables, 影响性能，在Oracle中也是影响性能，最起码列出属性字段。
4. 切分查询，（大查询切分成为小查询，避免一次性锁住大量数据）。
5. 分解关联查询，（单表查询，结果在应用程序中进行关联，可以减少处理过程中的锁争用）
6. 尽量先做单表查询；
7. …

profile的作用和用法

用于保存SQL语句的执行状态，需要手动开启，才可以查看。

set profiling = 1; 开启
show profiles; 显示SQL查询的profiles概况
show profile all for query X; 查看第X条语句的所有执行情况。
show profile cpu, block io, memory for query X; 查看部分profile信息。

图片：https://uploadfiles.nowcoder.com/files/20190731/9084855_1564581954903_1027722-20190731141049676-1016503398.png

脏读、幻读

脏读：一个事务读取 另一个事务修改，但是尚未提交的数据。造成两个事务得到的数据不一致。

幻读： 在同一个事务中，当同一查询多次执行的时候，由于其他插入操作的事务提交，会导致每次返回不同的结果集。幻读是事务非独立执行时候发生的一种现象。

不可重复读：在同一个事务范围内多次执行查询，同一个查询在time1时刻读取某一行，在time2时刻重新读取这一行数据时，发现这一样数据已经被修改，可能被更新了，也可能被删除了。

内连接、外连接

1. 内连接（自然连接）：查询每个学生及其选修课程的情况，没有选课的学生不会列出

select Student.*, SC.*
from Student , SC
where Student.Sno = SC.Sno;

2. 外连接：查询… 没有选课的学生也会列出

select Student.8* ， SC.*
from Student LEFT JOIN SC ON(Student.Sno=SC.Sno);

左连接：

left join(左连接) 返回包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
使用场景：可以保持左表完整加入另一表中的数据。

count和sum的区别，以及count(*)和count(列名)的区别

求和用累加sum() ，求行的个数用累计count；

count(*) 包含了所有列，在统计结果时不会忽略列值null;

count(列名) 只包括列名那一项，会忽略列值为空的计数。

主键、外键

主键确定一条记录的唯一标识，如学生信息表，学号；

外键用于与另一张表关联。能确定另一张表记录的字段，用于保持数据的一致性。

缓存技术

Redis可以实现缓存机制， Redis是一个key-value存储系统。

支持的value类型：string(字符串)、 list(链表)、set(集合)和zset(有序集合)。这些数据类型都支持push/pop、add/remove及取交集并集和差集及更丰富的操作，而且这些操作都是原子性的。

在此基础上，redis支持各种不同方式的排序。与memcached一样，为了保证效率，数据都是缓存在内存中。区别的是redis会周期性的把更新的数据写入磁盘或者把修改操作写入追加的记录文件，并且在此基础上实现了master-slave(主从)同步。

Redis作为一个高性能的key-value数据库具有以下特征：

1、多样的数据模型：

2、持久化：使用RDB快照的方式，将内存中的数据不断写入磁盘；或使用类似MySQL的AOF日志方式，记录每次更新的日志。

3、主从同步（master-slave）

其他代码

1. 从数据库中统计总成绩前10的学生

select * 
from (select T.*, ROW_NUMBER() over(PARTITION BY 班级 order by 成绩 desc) RN
      from T 
     )where RN<=10;

2. 某个表格存着s_name subject score 三个字段，比如某一行是 张三 数学 76，现在要选取出所有科目成绩都大于80分的学生名字，请写出sql语句

select s_name
from table_n
where s_name NOT IN (select s_name from table_n
                    where score<=80);

3. 写两个sql语句，统计XX人数、选出课程编号不为XX的学生学号

select s_calss , count(*),
from Student
where not exists(select * from SC 
                 where SC.课程号='XX' AND Student.学号=SC.学号);

4. 多表查询：按照department_id 查询 employees(员工表)和 departments(部门表) 的信息

select ...
from ...
where (select e.last_name, e_department_id,d.department_name
      from employees e, department d
      where e.department_id=d.department_id);

5. 基本增删改查

insert into table1(field1,field2) values(value1,value2);
delete from table1 where 条件范围;
update table1 set field1=value1 where 范围;
select * from table1 where field1 like '%value1%';
select * from table1 [where 范围] order by field1,field2 [desc];
select count as totalcount from table1; 总数

create table t1(
	id int primary key,
	name nvarchar(255)
);

添加聚集索引
alter table table_name add primary key(colum_name);
create clustered index clustered_index on table_name(colum_name); SQLserver上

创建数据库
create database demo_db default character set utf8 collate utf8_general_ci;
授权
grant all privileges on demo_db 
刷新修改
flush privileges

摘录复习自：

1. https://www.nowcoder.com/tutorial/97/7d14246976434f489277d982aaa7aab2 ↩