杂记

大部分原文链接：https://javaguide.cn/database/basis.html，本文整合了别的资料和本人的理解。

关系型数据库基本概念

关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

  1. 基本概念
  • 数据库 : 数据库(DataBase 简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。
  • 数据库管理系统 : 数据库管理系统(Database Management System 简称 DBMS)是一种操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库。
  • 数据库系统 : 数据库系统(Data Base System，简称 DBS)通常由软件、数据库和数据管理员(DBA)组成。
  • 数据库管理员 : 数据库管理员(Database Administrator, 简称 DBA)负责全面管理和控制数据库系统

数据库是一组信息的集合，数据库管理系统是一种大型软件，是操纵和管理数据库的软件。数据库被数据库管理系统管。

  • 元组：元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
  • 码：码就是能唯一标识实体的属性，对应表中的某一个列。所有的元组该列的值两两互斥。
  • 候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
  • 主码 : 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码。- 外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码，则这个属性为外码。外键是可以有重复的，可以是空值。一个表可以有多个外键。
  • 主属性：候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
  • 非主属性： 不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。什么是 ER 图？

属性分为主属性和非主属性，主属性是由候选码构成的。候选码中选一个成为主属性。

为什么不推荐使用

  1. ER图

ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。它提供了一种表示实体类型、属性和连接的方法。是数据库设计的强有力的工具。

ER 图由下面 3 个要素组成：

  • 实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。
  • 属性：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。
  • 联系：即实体与实体之间的关系，在 ER 图中用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。联系可以是一对一的，一对多的，多对多的。
  1. 数据库范式。

（1） 1NF（第一范式）

属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。也就是说，属性一定是一个特殊的值，不能是一个可拆的数组或者元组等。

（2） 2NF（第二范式）

2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。函数依赖就是在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值（X决定Y的值，如果s.X = t.X，那么s.Y = t.Y），那么就可以说 Y 函数依赖于 X，写作 X → Y。如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。

比如Student(学生编号 , 学生姓名, 班级编号, 院系, 课程编号 , 成绩)，非主属性是（ 学生姓名, 班级编号, 院系,成绩）主属性是（学生编号，课程编号），非主属性对主属性肯定是有函数依赖的。但是非主属性（学生姓名, 班级编号, 院系）是只受学生编号的函数依赖的，所以说要修改。

（3） 3NF（第三范式）

在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y（就是Y不是X的子集），Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。

3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。如Student(学生编号, 班级编号, 班长)这就出现学生编号->班级编号，班级编号->班长。这就出现了传递函数依赖。

  1. 数据库设计路径
  • 需求分析 : 分析用户的需求，包括数据、功能和性能需求。
  • 概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。
  • 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
  • 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
  • 数据库实施 : 包括编程、测试和试运行
  • 数据库的运行和维护 : 系统的运行与数据库的日常维护。

非关系型数据库

NoSQL（Not Only SQL 的缩写）泛指非关系型的数据库，主要针对的是键值、文档以及图形类型数据存储。并且，NoSQL 数据库天生支持分布式，数据冗余和数据分片等特性，旨在提供可扩展的高可用高性能数据存储解决方案。

一个常见的误解是 NoSQL 数据库或非关系型数据库不能很好地存储关系型数据。NoSQL 数据库可以存储关系型数据—它们与关系型数据库的存储方式不同。NoSQL 数据库代表：HBase、Cassandra、MongoDB、Redis。

NoSQL可以存的是图，文件或者是键值对。

  1. NoSQL数据库的特征，
  • 灵活性： NoSQL 数据库通常提供灵活的架构，以实现更快速、更多的迭代开发。灵活的数据模型使 NoSQL 数据库成为半结构化和非结构化数据的理想之选。
  • 可扩展性： NoSQL 数据库通常被设计为通过使用分布式硬件集群来横向扩展，而不是通过添加昂贵和强大的服务器来纵向扩展。
  • 高性能： NoSQL 数据库针对特定的数据模型和访问模式进行了优化，这与尝试使用关系数据库完成类似功能相比可实现更高的性能。
  • 强大的功能： NoSQL 数据库提供功能强大的 API 和数据类型，专门针对其各自的数据模型而构建。

SQL

SQL中的术语

  • 数据库（database） - 保存有组织的数据的容器（通常是一个文件或一组文件）。
  • 数据表（table） - 某种特定类型数据的结构化清单。
  • 模式（schema） - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。
  • 列（column） - 表中的一个字段。所有表都是由一个或多个列组成的。
  • 行（row） - 表中的一个记录。
  • 主键（primary key） - 一列（或一组列），其值能够唯一标识表中每一行。

一个系统有很多个数据库，每个数据库有很多个表，每个表有若干个列（上面叫属性），也有若干行（上面叫元组）

SQL语法要点

SQL 语句不区分大小写，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。例如：SELECT 与 select、Select 是相同的。多条 SQL 语句必须以分号（;）分隔。处理 SQL 语句时，所有空格都被忽略。

SQL分类

（1） 数据定义语言（DDL）

数据定义语言（Data Definition Language，DDL）是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。DDL 的主要功能是定义数据库对象。DDL 的核心指令是 CREATE、ALTER、DROP。

（2） 数据操纵语言（DML）

数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句。DML 的主要功能是 访问数据，因此其语法都是以读写数据库为主。DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。这四个指令合称 CRUD(Create, Read, Update, Delete)，即增删改查。

（3） 事务控制语言（TCL）

事务控制语言 (Transaction Control Language, TCL) 用于管理数据库中的事务。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。TCL 的核心指令是 COMMIT、ROLLBACK。

（4） 数据控制语言（DCL）

数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。DCL 的核心指令是 GRANT、REVOKE。

DCL 以控制用户的访问权限为主，因此其指令作法并不复杂，可利用 DCL 控制的权限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。

DML介绍

插入数据

INSERT INTO 表名 用于向表中插入一个新的数据。

1
2
3
4
5
6
# 插入一行
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');
# 插入多行
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com');

into后面如果是一个单独的表名的话，就是默认对应一个行的所有元素。如果后面有括号并括住若干个元素的话，就是插入行的一部分，剩下的部分默认为空。

1
2
INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');

当然查询的结果也是可以作为插入的数据来源

1
2
3
INSERT INTO user(username)
SELECT name
FROM account;
更新数据

UPDATE 语句用于更新表中的记录。update 表 set 字段 = 值（用逗号链接）where 筛选条件

1
2
3
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
删除数据

可以使用delete删除表中指定的数据

1
2
DELETE FROM user
WHERE username = 'robot';

当然也可以使用truncate直接清空表里面的数据

1
TRUNCATE TABLE user;
查询数据

select 列 from 表 （后续可以跟一堆控制语句）控制语句的执行是按照从上到下的顺序进行的，从上到下一层一层过滤。

查询所有列

1
2
SELECT *
FROM products;

可以使用DISTINCT用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。

1
2
SELECT DISTINCT
vend_id FROM products;

当然也可以使用LIMIT语句限制查询结果的数量。

1
2
3
4
5
-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;

也可以使用order by对结果进行排序，先排序的列放前面，后排序的列放后面。不同的列可以使用desc（降序）和asc（升序）进行排序。

1
2
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

这个语句就进行了排序，先按照降序对prod_price进行排序，再按照升序对prod_name进行排序。

分组

可以使用group by对查询进行分组聚合。当然分组的查询也是查询，后面也可以跟控制语句。

总结一下：就是先按照group by对数据进行分组，然后对每组数据进行聚合，生成一行记录。所以说select后面跟的列要么是分组的依据，要么是聚合函数。

1
2
3
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

这个sql的意思是按照cust_name进行分组，然后对cust_address进行聚合，生成记录，然后按照cust_name进行降序排序。

再分组聚合中，同样可以使用where和having函数，只不过where函数必须在分组前使用（不能对聚合的结果进行过滤），对聚合的结果进行过滤的是having。一般来说having和group by进行连用。

1
2
3
4
5
SELECT cust_name, COUNT(*) AS NumberOfOrders
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) > 1;

这个sql语句首先先找到cust_email不为空的记录，然后对这些记录进行聚合。然后用having语句进行聚合后的过滤。在这个查询中，count函数是在group by阶段起的作用，在where阶段查询就是cust_name 和 * （其他）。

子查询

子查询是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。简单来说，子查询就是指将一个 select 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。

子查询常用在 WHERE 子句和 FROM 子句后边：

  • 当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。对于单行单列、多行单列、单行多列数据，数据会处理成类似于数组的结构。
  • 当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

用于where子句的子查询：子查询要用括号括起来。

1
2
3
4
5
6
select column_name [, column_name ]
from   table1 [, table2 ]
where  column_name operator
    (select column_name [, column_name ]
    from table1 [, table2 ]
    [where])

用于from的子查询，一般来说子查询的结果相当于一张临时表，这里一般需要as关键字来给这个表起个名字。

1
2
3
4
5
select column_name [, column_name ]
from (select column_name [, column_name ]
      from table1 [, table2 ]
      [where]) as temp_table_name
where  condition
where子句
  • = 等于
  • <>不等于
  • < 小于
  • 大于 >
  • <= 小于等于
  • 大于等于 >=
  • IN 指定针对某个列的可能值
  • LIKE 搜索某种模式：像这样：prod_name LIKE '%bean bag%'（%表示匹配任何字符出现任意次数，_表示匹配任何字符出现一次）
  • BETWEEN 在某个范围内

当然where子句可以使用与或非逻辑，对应and or not关键词。

join

JOIN 是“连接”的意思，顾名思义，SQL JOIN 子句用于将两个或者多个表联合起来进行查询。

连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。也即是说join可以把两张表连接成一张临时的大表。

1
2
3
4
select table1.column1, table2.column2...
from table1
join table2
on table1.common_column1 = table2.common_column2;

在上面的sql语句中table1.common_column1 = table2.common_column2 是连接条件，只有满足此条件的记录才会合并为一行。连接表时，SQL 会根据连接条件生成一张新的临时表。ON 就是连接条件，它决定临时表的生成。WHERE 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 JOIN-ON 了。

另外，如果两张表的关联字段名相同，也可以使用 USING子句来代替 ON，举个例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
# join....on
select c.cust_name, o.order_num
from Customers c
inner join Orders o
on c.cust_id = o.cust_id
order by c.cust_name;

# 如果两张表的关联字段名相同，也可以使用USING子句：join....using()
select c.cust_name, o.order_num
from Customers c
inner join Orders o
using(cust_id)
order by c.cust_name;

sql允许在join左边加上一些关键词，一般是内连接，左外连接，右外连接和外连接。

  • INNER JOIN 内连接 （默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。
  • LEFT JOIN / LEFT OUTER JOIN 左(外)连接 返回左表中的所有行，即使右表中没有满足条件的行也是如此。
  • RIGHT JOIN / RIGHT OUTER JOIN 右(外)连接 返回右表中的所有行，即使左表中没有满足条件的行也是如此。
  • FULL JOIN / FULL OUTER JOIN 全(外)连接 只要其中有一个表存在满足条件的记录，就返回行。
组合

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：

  • 所有查询的列数和列顺序必须相同。
  • 每个查询中涉及表的列的数据类型必须相同或兼容。
  • 通常返回的列名取自第一个查询。

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

1
2
3
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

union和join的区别？

UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

DDL介绍

数据库
  • 创建数据库 CREATE DATABASE test;
  • 删除数据库 DROP DATABASE test;
  • 使用数据库 use test;
数据表

创建数据表：

1
2
3
4
5
6
CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';

数据表每一项要规定类型（必须），非空要求，初始值（不必须），当然可以根据已有的表创建新表，或者是select的结果创建新表。

数据表可以进行修改

添加列

1
2
ALTER TABLE user
ADD age int(3);

删除列

1
2
ALTER TABLE user
DROP COLUMN age;

修改列

1
2
ALTER TABLE `user`
MODIFY COLUMN age tinyint;

添加主键

1
2
ALTER TABLE user
ADD PRIMARY KEY (id);

删除主键

1
2
ALTER TABLE user
DROP PRIMARY KEY;
视图

视图是基于 SQL 语句的结果集的可视化的表。视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。

简化复杂的 SQL 操作，比如复杂的联结；只使用实际表的一部分数据；通过只给用户访问视图的权限，保证数据的安全性；更改数据格式和表示。

创建视图的方法：

1
2
3
4
CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;
索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

可以通过下面的方式创建索引：

1
2
CREATE INDEX user_index
ON user (id);

当然在index前面加上unique就是创建唯一索引。

添加索引：

1
ALTER table user ADD INDEX user_index(id)

具体的索引问题

DDL中的约束语言
  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。
  • DEFAULT - 规定没有给列赋值时的默认值。

TCL介绍（事务处理）

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

指令：

  • START TRANSACTION - 指令用于标记事务的起始点。
  • SAVEPOINT - 指令用于创建保留点。
  • ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到
  • START TRANSACTION 语句处。
  • COMMIT - 提交事务。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

DCL介绍（权限管理）

要授予用户帐户权限，可以用GRANT命令。要撤销用户的权限，可以用REVOKE命令。这里以 MySQL 为例，介绍权限控制实际应用。

1
2
3
4
GRANT privilege,[privilege],.. ON privilege_level
TO user [IDENTIFIED BY password]
[REQUIRE tsl_option]
[WITH [GRANT_OPTION | resource_option]];
  • 在GRANT关键字后指定一个或多个权限。如果授予用户多个权限，则每个权限由逗号分隔。
  • ON privilege_level 确定权限应用级别。MySQL 支持 global（*.*），database（database.*），table（database.table）和列级别。如果使用列权限级别，则必须在每个权限之后指定一个或逗号分隔列的列表。
  • user 是要授予权限的用户。如果用户已存在，则GRANT语句将修改其权限。否则，GRANT语句将创建一个新用户。可选子句IDENTIFIED BY允许您为用户设置新的密码。
  • REQUIRE tsl_option指定用户是否必须通过 SSL，X059 等安全连接连接到数据库服务器。
  • 可选 WITH GRANT OPTION 子句允许您授予其他用户或从其他用户中删除您拥有的权限。此外，您可以使用WITH子句分配 MySQL 数据库服务器的资源，例如，设置用户每小时可以使用的连接数或语句数。这在 MySQL 共享托管等共享环境中非常有用。

REVOKE 撤销权限语法：

1
2
3
4
5
REVOKE   privilege_type [(column_list)]
        [, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...

  • 在 REVOKE 关键字后面指定要从用户撤消的权限列表。您需要用逗号分隔权限。
  • 指定在 ON 子句中撤销特权的特权级别。
  • 指定要撤消 FROM 子句中的权限的用户帐户。

存储过程

一系列 SQL 操作的批处理。存储过程可以由触发器，其他存储过程以及 Java， Python，PHP 等应用程序调用。命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;

    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
;;
DELIMITER ;

使用存储过程：

1
2
3
set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

触发器

创建触发器，当触发器被触发的时候就执行一段特殊的sql。

1
2
3
4
5
6
7
8
9
DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user',  now());
END $
DELIMITER ;

MySQL经典面试题

  1. 索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

优点：

  • 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

底层数据结构：

  • 哈希表
  • BST树
  • 红黑树
  • B树或者是B+树

按照底层存储方式角度划分：

  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。（找到了索引就找到了需要的数据，那么这个索引就是聚簇索引）
  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。（索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询）

这两个索引的优缺点集中在更新时间和查询时间上。

按照应用维度划分：

  • 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
  • 普通索引：仅加速查询。
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
  • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。比如说对于一个非覆盖的索引，索引只存了主键，但是如果要查询的只是主键，那就称发生了“覆盖索引”。就不用再回表查询了。
  • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。联合索引使用最左前缀匹配，如果查询的数据不满足最左前缀匹配，就不会使用索引。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
  • 前缀索引：对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

建设索引的一些建议：

  • 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。被作为条件查询的字段：被作为
  • WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

除了考虑索引的字段，还需要考虑：

  • 被频繁更新的字段应该谨慎更新索引
  • 尽量建立联合索引（建立索引需要存储消耗）
  • 避免冗余索引。

在使用查找的时候，为避免索引失效，需要：

  • 避免使用否定操作符
  • 尽量不要对列名使用函数
  • 不要在列上进行运算
  • 使用like时尽量不要通配符（%，_）开头
  • 不要做隐式数据转换
  1. Innodb为什么要用自增id作为主键？

每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当每一页写满，就会自动开辟一个新的页。如果不使用自增主键，那么每次新增新纪录都要被插到现有索引页的中间某个位置，频繁的移动和分页会造成大量的碎片，得到了不够紧凑的索引结构。

  1. MySQL执行SQL是怎么执行的？
  • 客户端请求->
  • 连接器（验证用户身份，给予权限）->
  • 查询缓存（存在缓存就直接返回，不存在就执行后续操作）->
  • 分析器（对SQL进行词法分析、语法分析）->
  • 优化器（对SQL进行优化，选择最优的执行方案）->
  • 执行器（看看用户的是否有执行权限）->
  • 去引擎层获取数据返回
  1. MySQL的内部构造？

可以分为引擎层和服务层两部分。

服务层包括连接器，查询缓存，分析器，优化器，执行器等，涵盖大多数核心服务功能，所有跨存储引擎的功能都在这一层实现，比如说存储过程、触发器、视图等。

存储引擎层服务数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎。

  1. drop、delete 与 truncate 区别？
  • drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
  • truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
  • delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。
  • truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而 delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segment 中，事务提交之后才生效。
  1. MySQL优化。
  • 为搜索字段创建索引
  • 避免使用Select *
  • 选择比较好的存储引擎

（1）为什么使用B+树？

  • 索引的结构组织要尽量减少查找过程中磁盘IO的存储次数。B+树可以通过遍历叶子节点实现整棵树的遍历。这样可以方便扫库。
  • B+树的查找效率更加稳定，因为B树有可能在中间节点找到数据。
  • B+树磁盘读写代价更低，因为B+树内部节点没有指向关键字具体信息的指针，所以内部节点相对B树更小。

（2）为什么数据库要进行分库和分表？

减小数据库的单库单表负担，提高查询性能，缩短查询时间。

（3）表锁和行锁

对表和对行上锁。上锁的对象不一样。由于上锁的粒度不一样就导致了死锁概率和并发度的不同。

（4）垂直拆分和水平拆分

垂直拆分：解决表与表之间的io竞争。就是把不同的表放在不同的服务器上。

水平拆分：解决单表中数据量增长的压力。将一个表拆成若干个表。

（5）高并发的解决思路？

  • 在Web服务框架中加入缓存
  • 增加索引
  • 主从服务器读写分离
  • 数据库拆分
  • 分布式存储
  1. 数据库隔离级别
  • 未提交读。事务中发生了修改，即使没有提交，其他事务还是可见的。会导致脏读，不可重复读和幻影读。
  • 提交读。对一个事物从开始到提交之前，所做的修改时其他事务不可见的。可以改善脏读。
  • 重复读。对一个记录读取多次的记录时相同的也就是说读两次A，前后两次读取的A是一致的。可以阻止脏读和不可重复读。
  • 可串行化读。在并发的情况下，和串行化的读取结果一致。脏读，不可重复读和幻影读都可以解决。

脏读：事务A：让x从50变到了100。回滚；事务B：在修改后回滚前读了x，那么读出来的是100。但是这个100是脏数据。这个就是脏读。

不可重复读：事务A：读一次x，过一会再读一次x。事务B：在A第一次读之后，第二次读之前让x+1，那么事务A的两次读会出现结果不一样的情况。

幻读：事务A：读一次x的数量，过一会再读一次x的数量。事务B：在A第一次读之后，第二次读之前插入了一个新的x。那么事务A的两次读会出现结果不一样的情况。

不可重复读的重点在于修改，幻读的重点在于新增或者删除。

  1. 为什么有事务回滚机制？

恢复机制是通过回滚日志（undo log）实现的，所有的事务进行的修改会记录到这个回滚日志中，然后根据回滚日志再对数据库进行操作。（事物的提交相当于根据回滚日志进行操作）

作用：

  • 能够在发生错误或者用户执行rollback的时候进行回滚。
  • 当系统发生崩溃的时候可以通过查询回滚日志将之前未完成的事务进行回滚。
  1. InnoDB和MyISAM的区别
  • InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
  • MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
  • MyISAM 不支持外键，而 InnoDB 支持。
  • MyISAM 不支持 MVCC，而 InnoDB 支持。
  • 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构。
  • MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
  • InnoDB 的性能比 MyISAM 更强大。
  1. 视图的作用是什么？可以更改吗？

视图时虚拟的表，只包含使用时动态检索数据的查询，不包含任何列和数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据。视图不能有索引，也不能有关联的触发器和默认值。大部分的视图都不可以更改。

  1. 事务四大特性？
  • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；（使用undo log进行控制）
  • 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  • 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。（使用redo log）

AID是手段，C是目的。

  1. 乐观锁和悲观锁

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁就是普通的悲观锁，先获取锁在进行业务操作，在数据库中锁分为共享锁（S锁）和排他锁（X锁），这个模型很像之前操作系统中介绍的读写锁模型。

MVCC可以认为是一种乐观锁。乐观锁就是先进行业务操作，然后最后实际更新数据的时候进行数据是否被更新过的检查。

  1. MySQL的CHAR和VARCHAR有什么区别？

CHAR不变长，VARCHAR变长。