【测开面经】数据库篇

事务

事务是由一系列对系统中数据进行访问和更新操作所组成的一个程序执行逻辑单元。事务是DBMS最基础的单位，事务不可分割。事务的提出是为了解决并发情况下保持数据一致性的问题。
事务的四个基本特征分别是原子性、一致性、隔离性、永久性。

脏读、幻读与不可重复读

• 脏读
  脏读指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另一个事务也访问这个数据，然后使用了这个数据。
• 不可重复读
  指在一个事务内，多次读取同一数据，在这个事务还没有结束时，另一个事务也访问该同一数据，而在第一个事务中的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的。这样就发生在一个事务内两次读到的数据是不一样的。
• 幻读
  指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个数据也修改这个表的数据，这种修改是向表中插入一行数据，那么就会发生操作第一个事物的用户发现表中还有没有修改的数据行，就像出现了幻觉一样。
• 丢失更新
  第一类丢失更新：A事务撤销时，把已经提交的B事务的更新数据覆盖了；
  第二类丢失更新：A事务覆盖B事务已经提交的数据，造成B事务所做的操作丢失。

事务隔离级别

为了解决上述问题，数据库通过锁机制解决并发访问的问题。根据锁定对象的不同：分为行级锁和表级锁；根据并发事务锁定的关系上看：分为共享锁和排他锁。
共享锁（S锁）一般用于不更改或不更新数据的操作（只读操作），如SELECT，如果事务T对数据A加上共享锁，则其他事务只能对A再加共享锁，不能加排他锁。获取共享锁的事务只能读数据，不能修改数据。
排他锁（X锁）用于数据修改操作，例如INSERT、UPDATE、DELETE等。如果事务T对数据A加上了排他锁后，则其他事务不能再对A加任何类型的锁。获取排他锁的事务既能读数据也能修改数据。
基于锁机制，数据库给用户提供了不同的事务隔离级别：

• READ UNCOMMITTED(读取未提交)：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生；
• READ COMMITTED(读取提交)：只有在事务提交后，其更新结果才会被其他事务看见，可以解决脏读问题；
• REPEATED READ(可重复读)：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读；
• SERIALIZATION(可串行化)：事务串行化执行，隔离级别最高，牺牲了系统的并发性，可以杜绝并发事务的所有问题。

事务隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	是	是	是
READ COMMITTED	否	是	是
REPEATED READ	否	否	是
SERIALIZATION	否	否	否

索引

数据库索引

数据库索引是为了增加查询速度而对表字段附加的一种标识，是对数据库表中一列或者多列值进行排序的一种结构。
增加查询速度的原因：DB在执行一条SQL语句时，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集中，如果对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的次数，所以能明显增加查询的速度。
不适合加索引的情况：

• 在查询中很少使用的列或者数据值很少的列不应该创建索引。因为这些列很少用到，有索引和没有索引并不能提高查询速度，相反如果增加索引，，反而会降低系统的维护速度和增大空间需求，因为创建、维护索引要耗费时间，同时需要占物理空间。
• 当对修改性能(如增删改)要求高于检索性能时，也不适合创建索引，当增加索引会提供检索性能，但是会降低修改性能。

索引的优缺点：

• 优点：
    1.大大加快数据的检索速度；
    2.创建唯一性索引，保证数据库表中每一行数据的唯一性；
    3.加速表与表之间的连接；
    在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和分组的时间。
• 缺点：
    1. 创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加；
    2. 索引需要占用额外的物理空间；
    3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

创建索引：
普通索引： CREATE INDEX index_name ON TABLE
唯一性索引：CREATE unique INDEX index_name ON TABLE(COLUMN)
复合索引(最多包含16个字段)：CREATE INDEX index_name ON TABLE(COLUMN1,COLUMN2)

实现的数据结构

B+树索引

B+树

在说B+树之前先了解一下什么是B树（平衡多路查找树）。二叉树是只有左右孩子的树，当数据量越大时，二叉树的节点越多，那么数的高度就越大，当从根节点搜索的时候，影响查询效率，如果这些节点存储在外存储器中，每访问一个节点，相当于进行了一次磁盘I/O操作。为了解决数据量大时查找效率低的问题，出现了B树和B+树。

• B树的特点：
  一颗m阶的B树满足以下条件：
    1.每个节点最多有m-1个关键字，最多有m颗子树，有j个孩子的非叶节点恰好有j-1个关键字，关键字按递增排列；
    2.所有非根节点关键字个数在$[\lceil m/2-1\rceil ,m-1]$，根节点是$[1,m-1]$；
    3.所有叶节点在同一层，B树的叶节点可以看作时一种外部节点，叶子节点不包含任何信息；每个节点都存储key-value对，因此经常访问的元素可能离根节点更近，因此访问也更迅速。
  

示例：
向B树中插入6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

• B+树的特点：
  一颗m阶的B+树满足以下条件：
    1.有n颗子树的非叶节点中含有n个关键字(B树是n-1个)，这些关键字不保存数据，只用来索引，所以可以容纳更多的节点元素；
    2.B+树每个非根节点关键字个数的范围是$[\lceil m/2\rceil ,m]$，根节点为$[1,m]$；
    3.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接（叶子节点组成一个链表）。所有的叶子结点使用链表相连，便于区间查找和遍历。B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好；
    4.所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
  
• B树和B+树的区别：

1. B树的每个节点都存储数据，所有节点组成了这棵树，而B+树只有叶子节点存储数据，叶子节点包含了这棵树的所有数据，所有的叶子节点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用；
2. B树叶节点包含的关键字和其他节点包含的关键字是不重复的，B+树的索引项只包含对应子树的最小和最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址；
3. 节点关键字个数不同（见两种树的特点）；
4. B+树中查找，无论查找是否成功，每次都是一条从根节点到叶节点的路径。

• B树和B+树的共同优点：
  考虑磁盘IO的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，m的大小取决于磁盘页的大小。

哈希索引

待完成

慢查询

开启慢查询

在my.ini配置文件中添加1）long_query_time=2;2)slow_query_log=On

分析慢查询日志

利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句，使用方法，在select语句前加上explain就可以。
EXPLAIN列解释

table	显示这一行数据关于哪张表
type	显示连接使用了何种类型，从好到坏的连接类型是const、eq_reg、ref、range、index、ALL
possible_keys	显示可能应用在这张表中的索引
key	实际使用的索引
key_len	使用的索引的长度
ref	显示索引的哪一列被使用了
rows	MySQL认为必须检查的用来返回请求数据的行数
extra	关于MySQL如何解析查询的额外信息

慢查询原因及优化

• 索引没有起作用：
  1）使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符是“%”，索引不会起作用；
  2）使用多列索引的查询语句，，一个索引最多可以包括16个字段，只有查询条件使用了这些字段中的第一个字段，索引才会被使用。
• 数据库结构设计不合理：
  1）将字段很多的表分解成多个表，如果有些字段使用的频率比较低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢；
  2）对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，将原来的联合查询改为对中间表的查询，以此来提高查询效率。
• 查询过大：
  1）将一个大的查询分解为多个小查询，对每一个表进行一次单表查询，然后查询结果在应用程序中进行关联；
  2）优化LIMIT分页，尽可能使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。

关系型数据库和非关系型数据库的区别

关系型数据库

• 关系型数据库：
  关系型数据库是采用关系模型来组织数据的数据库。关系模型就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
  关系模型中常用的概念：

关系	一张二维表，每个关系都具有一个关系名，也就是表名
元组	二维表的一行，在数据库中被称为记录
属性	二维表中的一行，在数据库中被称为字段
域	属性的取值范围
关键字	一组可以唯一标识元组的属性，数据库中常称为主键，由一个或者多个列组成
关系模型	对关系的描述

• 关系型数据库的优缺点：
  优点：
    1）贴合逻辑，容易理解；
    2）使用便捷，通用的SQL语言使得操作关系型数据库方便；
    3）丰富的完整性（实体完整性、参照完整性、用户定义的完整性）大大降低了数据冗余和数据不一致的概率。
  缺点：
    1）在一张包含海量数据的表中出查询，硬盘I/O是一个很大瓶颈，效率会非常低；
    2）灵活度不佳，当需要对数据库系统进行升级和扩展时，往往需要停机维护和数据迁移；
    3）在关系型数据库中，导致性能欠佳的最主要的原因是多表的关联查询，以及复杂的数据分析类型的SQL报表查询，为了保证数据库的ACID特性，必须尽量按照其要求的范式进行设计。

非关系型数据库

• 非关系型数据库：
  指非关系型、分布式的，并一般不保证遵循ACID的数据存储系统。非关系型数据库以键值对存储，且结构不固定，每个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构。严格意义上，非关系型数据库不是一种数据库，应该以一种数据结构化存储方法的集合，可以是文档或者键值对等。
• 非关系型数据库的优缺点：
  优点：
    1）格式灵活，存储数据的格式可以是key-value键值对、文档形式、图片形式等；
    2）高扩展性、部署简单，成本低。
  缺点：
    1）不提供SQL支持，没有事务处理；
    2）当面对复杂查询的数据时，性能欠佳。

数据库引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。

SQL语句执行过程

执行过程

存储引擎查看

这里使用的是Navicat Premium

更换引擎方式

• 修改配置文件my.ini
  将my-small.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB。
• 在建表时指定

create table t1{
id int primary key,
...
}type=MyISAM;

• 建表后更改
  ALTER TABLE t1 type=InnoDB;
  查看修改结果：show table status from t1或者 show create table t1

常见存储引擎

InnoDB和MyISAM

InnoDB是事务型数据库的首选引擎，支持事务安全表(ACID)，支持行锁定和外键，采用B+树实现，索引和数据存储在同一个文件中，InnoDB是MySQL默认引擎。InnoDB要比ISAM和MyISAM引擎要慢。
MyISAM是ISAM（ISAM不支持事务处理，不支持外来键，不能容错、不支持索引）的扩展格式，除了提供ISAM所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制(表级锁)来优化多个并发的读写操作，MyISAM强调快速读取操作（所以在进行大量数据操作都是读取操作的WEB开发中MySQL如此受青睐），其代价就是需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间，否则碎片也会随之增加，最终影响数据访问性能。

对比

• 存储结构
  InnoDB使用共享表空间存储方式，所有数据存在一个单独的表空间中，而这个表空间是由很多个文件组成的，一个表可以跨越多个文件存在，InnoDB表空间的最大限制为64TB，这个大小是包括这个表的所有索引和其他相关数据。使用单独表空间存储方式，每个表的数据以一个单独的文件来存储，单表限制了文件系统的大小。
  MyISAM中每个表被存在分离的文件中，每个MyISAM中的表在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型：.frm文件存储表定义，MYD文件存储表的数据；MYI文件存储表的索引。

• 存储空间
  InnoDB存储引擎在主内存建立其专用的缓冲池来缓存数据和索引，所以需要更多的内存和存储。
  MyISAM可被压缩，存储空间较小，支持三种不同的存储格式：静态表（默认，数据末尾不能有空格，会被去掉）、动态表、压缩表。MyISAM引擎支持对表的压缩，压缩后的空间比压缩前减少60%-70%，但是压缩后的表是只读的，这个要注意，根据使用场景判断是否需要压缩。

• 索引和数据
  索引是帮助MySQL高效获取数据的数据结构。MyISAM和InnoDB都使用B+树。
  InnoDB中B+树的叶节点data域保存了完整的数据记录，所以InnoDB的数据文件本身就是索引文件，索引和数据是紧密捆绑的。data域的key是数据表的主键。没有使用压缩从而造成InnoDB比MyISAM体积庞大不少。
  
  上图是InnoDB主索引示意图，可以看到叶节点包含了完整的数据记录。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
  InnoDB的辅助索引data域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会根据辅助索引找到主键，再根据主键索引找到实际的数据。所以InnoDB的所有辅助索引都引用主键作为data域。
  
  MyISAM B+树的data域存储的内容为实际数据的地址，也就是说他的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，所以MyISAM的索引文件和数据文件是分开的，索引文件仅保存数据记录的地址。MyISAM索引是有压缩的，内存使用率就对应提高了不少。
  
  在MyISAM中，主索引和辅助索引(Secondary Key)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。与InnoDB不同的是MyISAM辅助索引存储的data域存储的是地址而不是主键。假设以Col1为主键，Col2建立辅助索引，则此索引的结构：
  

• 是否保存行数
  InnoDB中不保存表的具体行数，执行SELECT COUNT(*) FROM TABLE，InnoDB要扫描一遍全表来统计行数；
  MyISAM中存储了表的行数，直接取已经保存好的值而不需要进行全表扫描。

• 锁
  InnoDB支持的是行级锁，MyISAM支持的是表级锁。但由于InnoDB锁的粒度更小，写操作不会锁定全表，所以在并发度较高时，使用InnoDB引擎会提升效率。但是使用行级锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

• 可移植性、备份和恢复
  InnoDB可以拷贝数据文件、备份binlog，或者用mysqldump，在数据量达到几十G时就相对痛苦了；
  MyISAM引擎中的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，在备份和恢复时可单独针对某个表进行操作。

HEAP

HEAP引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，所以使用该引擎的表拥有极高的插入、更新和查询效率。这种存储引擎默认使用哈希索引，其速度比使用B+树要快。由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如进程发生异常、重启或者计算机关机等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。

存储引擎选择

去重

1. 防止表中出现重复数据
   当表中未添加数据时，可以在MySQL数据表中设置指定的字段为PRIMARY KEY(主键)或者UNIQUE(唯一)索引老保证数据的唯一性。
2. 过滤删除重复值

• 查看重复数据：
  SELECT COUNT(*) as repetitions，id FROM student GROUP BY id HAVING repetitions>1;
  查询语句返回student表中重复的记录数
• 删除重复数据：
  1）DELETE FROM student GROUP BY id HAVING COUNT(id)>1;
  2）SELECT DISTINCT id FROM student；
  3）CREATE TABLE tmp SELECT id,name,sex FROM student GROUP BY id;DROP TABLE student;ALTER TABLE tmp RENAME TO student;

基本SQL语句练习

基本语法

select 用于从数据库中选取数据
select distinct 用于当包含多个重复值时，返回唯一不同的值
where 用于提取那些满足指定条件的记录
and / or 基于一个以上条件对记录进行过滤
order by 用于对结果集按照一列或者多列进行排序，默认升序，使用desc关键字降序（按第一列升序，第二列降序order by c1,c2 ASC|DESC）
insert into 用于向表中插入新纪录（第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：INSERT INTO table_name VALUES (value1,value2,value3,...);第二种形式需要指定列名及被插入的值：INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);）insert into select 和select into from 的区别在于前者需要插入的表存在，后者要求不存在
update 用于更新表中已存在的记录，如果忽略where子句，所有的记录都将被更新
delete 用于删除表中的记录，如果忽略where子句，所有记录都将被删除，但是表结构、属性、索引将保持不变
select top 用于规定要返回的记录的数目，SELECT * FROM Websites LIMIT 2; SELECT TOP 50 PERCENT * FROM Websites;
like 用于在where子句中搜索列中的指定模式，SELECT * FROM Websites WHERE name LIKE 'G%';寻找name以G开头的用户，'%a' //以a结尾的数据，'a%' //以a开头的数据，'%a%' //含有a的数据，‘_a_’ //三位且中间字母是a的，'_a' //两位且结尾字母是a的，'a_' //两位且开头字母是a的，可以使用regexp来操作正则表达式
in 允许在where子句中规定多个值，与=类似，但in可以规定多个值
between 用于选取介于两个值之间的数据范围内的值，可以是数值、文本或者日期，between v1 and v2
别名 列的别名语法select name as n from table_name，表的别名语法select name from table_name as tn
inner join 如果表中有至少一个匹配，则返回行，SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
left join 即使右表中没有匹配，也从左表返回所有行
right join 即使左表中没有匹配，也从右表返回所有的行
full join 只要其中一个表中存在匹配，则返回行
union 用于合并两个或多个select语句的结果集，默认的，union操作符选取不同的值，如果允许重复的值，使用union all，union结果集中的列名总是等于第一个select语句中的列名，SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;，只能在最后使用一个order by命令，是将两个查询结果合在一起之后，再进行排序
select into 从一个表复制数据，然后把数据插入到另一个新表中，SELECT * INTO newtable [IN externaldb] FROM table1;
insert into select 从一个表复制数据，然后数据插入到一个已存在的表中，目标表中任何已存在的行都不会受影响，INSERT INTO table2 SELECT * FROM table1;
create database 用于创建数据库
create table 用于创建数据库中的表
约束 not null(某列不能存储NULL)，unique(保证某列的每行必须有唯一的值)，primary key(not null和unique的结合)，foreign key(保证一个表中的数据匹配另一个表中的值的参照完整性)，check(保证列中的值符合指定的条件)，default(规定没有列赋值时的默认值)
create index 用于在表中创建索引，CREATE INDEX index_name ON table_name (column_name)
撤销 drop index撤销索引，drop table撤销表，drop database 删除数据库
alter table 用于在已有的表中添加、删除或修改列，添加列—ALTER TABLE table_name ADD column_name datatype，删除列—ALTER TABLE table_name DROP COLUMN column_name，修改列—ALTER TABLE table_name MODIFY COLUMN column_name datatype
auto_increment 会在新纪录插入表中时生成一个唯一的数字
Views 视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系，视图提供了一个统一访问数据的接口（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限），从而加强了安全性，使用户只能看到视图所显示的数据，视图还可以被嵌套，一个视图中可以嵌套另一个视图。创建视图CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition，更新视图CREATE OR REPLACE VIEW view_name AS，删除视图DROP VIEW view_name
SQL 函数

练习

• 学生表（姓名，成绩）：查第15名成绩

select ifnull(
(select distinct 成绩
from 学生表
order by 成绩 desc
limit 1 offset 14),NULL
) as 第十五名成绩

使用distinct 成绩进行成绩去重
limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据,等价于limit x,y，并且limit不支持运算
如果没有第二高的成绩，返回空值，所以这里用判断空值的函数（ifnull）函数来处理特殊情况。ifnull(a,b)函数解释：如果value1不是空，结果返回a，如果value1是空，结果返回b

拓展
第N高的成绩

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m INT;
set m = n-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull(
          (select distinct Salary
          from Employee
          order by Salary desc
          limit 1 offset m),null
      )   
  );
END

• 学生成绩表（姓名，班级，成绩）：查每个班级及格和不及格的人数

select 班级,
sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from 学生成绩表
group by 班级