MySQL数据库大总结MySQL数据库大总结数据库关系型关系型数据库是基于关系模型的数据库系统，使用结构化查询语言

MySQL数据库大总结

数据库

关系型

关系型数据库是基于关系模型的数据库系统，使用结构化查询语言（SQL）进行数据操作和查询。它们使用表格来组织数据，表格由行和列组成，行表示记录，列表示数据字段。关系型数据库使用预定义的模式和约束来确保数据的一致性和完整性。常见的关系型数据库包括MySQL、Oracle、SQL Server和PostgreSQL。
非关系型

非关系型数据库是一类不使用传统的表格结构和SQL查询语言的数据库系统。它们通常用于存储大规模、非结构化或半结构化的数据，如文档、键值对、列族数据等。非关系型数据库的设计目标是提供更高的可伸缩性、灵活性和性能。常见的非关系型数据库包括MongoDB、Cassandra、Redis和Elasticsearch。

SQL

分类
- DDL：数据定义语言（Data Definition Language，DDL）：DDL用于定义和管理数据库的结构，包括创建、修改和删除数据库、表格、列等对象。常见的DDL语句包括CREATE、ALTER和DROP等。
- DML：数据操作语言（Data Manipulation Language，DML）：DML用于对数据库中的数据进行操作，包括插入、更新和删除数据。常见的DML语句包括SELECT、INSERT、UPDATE和DELETE等。Language。代表关键字为insert、delete 、update。
- DQL：数据查询语言（Data Query Language，DQL）：DQL用于从数据库中查询数据，它是SQL的核心部分。最常用的DQL语句是SELECT，用于从表格中检索特定的数据行和列。
- DCL ：数据控制语言（Data Control Language，DCL）：DCL用于定义和管理数据库的安全性和权限，包括授权用户访问权限、撤销权限和管理角色等。常见的DCL语句包括GRANT和REVOKE等。
- 事务控制语言（Transaction Control Language，TCL）：TCL用于管理数据库的事务，包括开始、提交和回滚事务。常见的TCL语句包括BEGIN、COMMIT和ROLLBACK等。

DDL语句

库和表
- 在数据库中，有两个基本的概念：库（Database）和表（Table）。
- 一个数据库（库）是一个独立的容器，用于存储相关的数据和对象。它可以包含多个表以及其他数据库对象，例如视图、存储过程和触发器等。数据库用于组织和管理数据，并提供对数据的访问和操作。
- 表是数据库中的一个结构化数据集合，用于存储具有相同结构的数据记录。表由行和列组成，其中每一行表示一个记录，每一列表示一个字段。每个字段有一个字段名和一个数据类型，字段名用于标识字段，并提供对该字段的引用。
表
- 字段名
  1. 使用清晰的、描述性的名称：字段名应该清楚地描述字段所代表的含义。使用具有意义的名称可以增加代码的可读性，并使其他开发人员更容易理解和使用数据库。
  2. 遵循命名约定：选择一种命名约定，并在整个数据库中保持一致。常见的命名约定包括下划线命名法（例如：first_name）和驼峰命名法（例如：firstName）。重要的是选择一种约定，并在字段名中使用统一的风格。
  3. 使用小写字母：为了避免大小写的混乱和不一致，建议将字段名统一使用小写字母。这也有助于确保在使用不区分大小写的数据库系统时的一致性。
  4. 避免使用保留字：确保字段名不与数据库系统的保留字冲突。不同数据库管理系统可能具有不同的保留字列表，因此请参考相应的文档以了解保留字。
  5. 尽量简洁而明确：字段名应该尽量简洁，同时又要足够明确。避免使用过长或过于复杂的字段名，同时确保字段名能够准确地表达字段所代表的含义。
  6. 使用一致的命名约定：在整个数据库中保持字段名的一致性，这有助于减少混淆和错误，并提高代码的可维护性。
- 字段类型
  - 整数：整数类型（Integer）：用于存储整数值，包括正整数、负整数和零。
    - 示例字段：age（年龄）、quantity（数量）、score（得分）
  - 小数：小数类型（Decimal/Float）：用于存储小数值，包括定点数和浮点数。
    - 示例字段：price（价格）、rating（评分）、latitude（纬度）、longitude（经度）
  - 字符串：字符串类型（Character/String）：用于存储文本数据，如名称、描述等。
    - 示例字段：name（姓名）、email（电子邮件）、address（地址）、description（描述）
  - 时间：日期和时间类型（Date/Time）：用于存储日期、时间或日期时间数据。
    - 示例字段：birth_date（出生日期）、created_at（创建时间）、last_login（最后登录时间）
- 字段约束
  - 主键约束（Primary Key Constraint）：用于唯一标识表中的每一行数据。主键必须具有唯一性，并且不能包含空值。例如，在一个名为"Customers"的表中，"CustomerID"字段可以被指定为主键约束。
  - 唯一约束（Unique Constraint）：确保字段的值在表中是唯一的，但可以包含空值。例如，在一个名为"Employees"的表中，"Email"字段可以被指定为唯一约束，以确保每个员工的电子邮件地址都是唯一的。
  - 非空约束（Not Null Constraint）：禁止字段包含空值。例如，在一个名为"Orders"的表中，"OrderDate"字段可以被指定为非空约束，以确保每个订单都有一个订单日期。
  - 默认约束（Default Constraint）：为字段提供默认值，如果没有明确指定值的话。例如，在一个名为"Products"的表中，"Quantity"字段可以被指定为默认约束，使得每个产品的数量默认为1。
  - 检查约束（Check Constraint）：限制字段值必须满足特定的条件。例如，在一个名为"Students"的表中，"Age"字段可以被指定为检查约束，要求年龄必须大于等于18。
```
CREATE TABLE stu(
-- 字段列名   类型  约束
    stu_id int PRIMARY KEY, -- 主键约束
    stu_name VARCHAR(20) UNIQUE, -- 唯一约束
    gender char(1) DEFAULT '男', -- 默认值约束
    address VARCHAR(100) not NULL, -- 非空约束
    birthday date 
);
```

DML语句

添加

-- 添加
-- INSERT INTO 表名(字段名...) VALUE();
-- INSERT INTO 表名 VALUE();
-- INSERT INTO 表名 VALUES(),(),...;
INSERT INTO stu(stu_id,stu_name,address) VALUE(1001,'小王','黑龙江大兴安岭');
INSERT INTO stu VALUE(1002,'小林','女','河南鹤壁','1999-12-30');
INSERT INTO stu VALUES(1003,'小李','女','河南鹤壁','1999-12-30'),
                                            (1004,'小七','男','河南鹤壁','1999-12-30'),
                                            (1005,'小张','女','河南鹤壁','1999-12-30'),
                                            (1006,'小米','女','河南鹤壁','1999-12-30');

删除
```
DELETE FROM 表名 [ WHERE 条件 ] ;
```

修改

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... 1 [ WHERE 条件 ] ;

DQL语句

单表查询

查询全部
```
SELECT * FROM 表名 ;
```

查询指定字段

SELECT 字段1, 字段2, 字1 段3 ... FROM 表名 ;

去重查询

SELECT DISTINCT 字段列表 FROM 表名;

起别名查询

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 1 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

算数运算符

比较运算符

对于null值的比较

select * from emp where idcard is null;
select * from emp where idcard is not null;

逻辑运算符

in关键字

select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);

模糊查询

select * from emp where idcard like '%X';
select * from emp where idcard like '__X';

分组查询

group by

having

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ];
select workaddress, count(*) address_count from emp where age < 45 group by
workaddress having address_count >= 3;

聚合函数

count()
max()
min()
avg()

sum()

SELECT 聚合函1 数(字段列表) FROM 表名 ;

排序

DESC

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排1 序方式1 , 字段2 排序方式2 ;

分页查询限制查询个数

limit(m,n)

-- 限制查询个数 LIMIT(m,n)m从第几条数据开始查询，n查询几条数据
-- LIMIT 2,3   3-5

SELECT * FROM stu LIMIT 2,3;

外键约束

外键约束是关系数据库中的一种机制，用于维护数据的完整性和一致性。它建立了一个表与另一个表之间的引用关系，确保引用的数据存在且保持一致。
具体来说，外键约束定义了一个表中的列（称为外键），它引用了另一个表中的主键列。通过设置外键约束，数据库会在插入或更新数据时验证外键的引用关系，确保引用的主键值存在于关联的表中。
外键约束的作用有以下几点：
- 强制引用完整性：外键约束防止在关联表之间出现不一致的数据，确保数据的完整性。如果引用的主键值不存在，插入或更新操作将被拒绝。
- 维护数据一致性：通过外键约束，数据库可以确保引用关系的一致性。如果关联表中的主键值发生变化或被删除，相应的外键将自动更新或删除。
- 支持数据查询和操作：外键约束可以简化数据库查询和操作，提供了关联表之间的连接和参照。它可以帮助开发者更轻松地编写和执行复杂的查询，同时减少了手动维护引用关系的工作。

级联删除

级联删除是一种外键约束的操作选项，它指定了当删除主表中的记录时，相关联的从表中的相关记录也会自动被删除的行为。
具体来说，当启用级联删除的外键约束时，以下操作将发生：
- 删除主表记录：当从表中有外键引用指向主表中的记录时，如果删除主表中的记录，数据库将自动执行级联删除操作。
- 自动删除相关从表记录：级联删除操作将删除从表中与被删除主表记录相关联的记录。这样可以确保主表和从表之间的关联关系保持一致性。

范式

第一范式（1NF）
- 要求每个表中的每个属性都是原子的，即不可再分。
- 每个属性只能包含单个值，而不能是多值属性或重复的属性。
第二范式（2NF）：
- 要求每个非主键属性完全依赖于主键。
- 通过将非主键属性与完整主键相关联，消除了部分依赖的情况。
第三范式（3NF）：
- 要求每个非主键属性不依赖于其他非主键属性，即消除传递依赖。
- 所有非主键属性都应直接依赖于主键。
巴斯-科德范式（BCNF）：
- 是对第三范式的进一步拓展，要求每个非主键属性都不依赖于任何其他非主键属性，即消除非平凡的函数依赖关系。
第四范式（4NF）：
- 要求消除多值依赖，确保每个非主键属性只依赖于主键的某一部分，而不是整个主键。
第五范式（5NF）：
- 也称作投影连接范式（PJ/NF）或完美范式。
- 要求通过引入联接关系来处理多对多关系，确保没有任何重复数据。

多表关系

一对一

一对一关系表示两个表之间的每个记录在另一个表中只有一个对应记录。
这种关系可以通过在一个表中添加另一个表的主键作为外键来建立。

一对多

一对多关系表示一个表中的记录可以对应另一个表中的多个记录。
这种关系可以通过在多的一方的表中添加另一个表的主键作为外键来建立。

多对多需要一张中间表记录关联的数据

多对多关系表示两个表之间的多个记录可以相互对应。
这种关系通常需要使用一个中间表来实现，中间表包含两个表的主键作为外键。

笛卡尔积

在关系型数据库中，笛卡尔积是指两个表之间的一种操作，它返回这两个表中所有可能的组合。
具体来说，假设有表A和表B，它们分别有m和n条记录。那么它们的笛卡尔积将生成一个包含m * n条记录的结果集，其中每一条记录都是表A中的一条记录与表B中的一条记录的组合。
笛卡尔积操作通常用于没有明确的关联条件，而需要获取所有可能的组合的情况。它可以通过使用SQL中的CROSS JOIN语句来实现。
需要注意的是，当表A和表B的记录数较大时，笛卡尔积可能会生成非常大的结果集，导致性能问题和资源消耗。因此，在使用笛卡尔积操作时，应谨慎考虑数据量和查询需求，确保能够处理得到的结果集。

内连接

-- 内连接 等值内连接 展示两张表相关联的数据（外键关联的两个字段相关联）
-- INNER JOIN on 其中inner可以省略
SELECT 
    emp.emp_id,emp.ename,emp.dept_id,dept.did
FROM
    emp
JOIN
    dept
ON
    dept.did = emp.dept_id;

SELECT e.emp_id,e.ename,e.dept_id,d.did
FROM emp e,dept d
WHERE e.dept_id = d.did;

左外连接

右外连接

--外连接 以一张表为基准表 将另外一张表与之相对应的数据展示出来 基准全部表示

SELECT 
    emp.emp_id,emp.ename,emp.dept_id,dept.did
FROM
    emp
RIGHT JOIN
    dept
ON
    dept.did = emp.dept_id;

子查询

in exists

IN：用于判断一个值是否存在于子查询返回的结果集中。它可以用来检查一个值是否属于一组特定的值
- 示例：SELECT * FROM 表名 WHERE 列名 IN (子查询);
EXISTS：用于检查子查询返回的结果集是否存在记录。它返回一个布尔值（True或False），表示子查询是否返回了至少一条记录。
- 示例：SELECT 列名 FROM 表名 WHERE EXISTS (子查询);

any all some

ANY/SOME：用于比较一个值与子查询返回的结果集中的任何一个值是否满足条件。它可以与比较运算符（如<、>、=等）一起使用。
- 示例：SELECT 列名 FROM 表名 WHERE 列名比较运算符 ANY (子查询);
ALL：用于比较一个值与子查询返回的结果集中的所有值是否都满足条件。它可以与比较运算符一起使用。
- 示例：SELECT 列名 FROM 表名 WHERE 列名比较运算符 ALL (子查询);

函数

字符串函数

CONCAT：用于将多个字符串连接在一起。
- 示例：SELECT CONCAT('Hello', ' ', 'World');返回结果为"Hello World"
SUBSTRING：用于从字符串中提取指定位置的子串。
- 示例：SELECT SUBSTRING('Hello World', 7, 5);返回结果为 "World"
REPLACE：用于替换字符串中的指定子串为新的子串。
- 示例：SELECT REPLACE('Hello World', 'World', 'Universe'); 返回结果为 "Hello Universe"
UPPER：将字符串转换为大写。
- 示例：SELECT UPPER('hello');返回结果为"HELLO"
LOWER：将字符串转换为小写。
- 示例：SELECT LOWER('WORLD');返回结果为"world"
LENGTH：返回字符串的长度。
- 示例：SELECT LENGTH('Hello');返回结果为5
TRIM：去除字符串两端的空格或指定的字符。
- 示例：SELECT TRIM(' Hello ');返回结果为 "Hello"
LEFT：从字符串的左侧提取指定长度的子串。
- 示例：SELECT LEFT('Hello World', 5);返回结果为"Hello"
RIGHT：从字符串的右侧提取指定长度的子串。
- 示例：SELECT RIGHT('Hello World', 5);返回结果为 "World"
INSTR：返回子串在字符串中首次出现的位置。
- 示例：SELECT INSTR('Hello World', 'World');返回结果为7

数值函数

ABS：返回数值的绝对值。
- 示例：SELECT ABS(-10);返回结果为 10
ROUND：对数值进行四舍五入。
- 示例：SELECT ROUND(3.14159, 2);返回结果为 3.14
CEILING：向上取整，返回不小于给定数值的最小整数。
- 示例：SELECT CEILING(4.3);返回结果为 5
FLOOR：向下取整，返回不大于给定数值的最大整数。
- 示例：SELECT FLOOR(4.7);返回结果为 4
SQRT：返回数值的平方根。
- 示例：SELECT SQRT(16);返回结果为4
POWER：返回一个数的指定次幂。
- 示例：SELECT POWER(2, 3);返回结果为8
MOD：返回除法操作的余数。
- 示例：SELECT MOD(10, 3);返回结果为 1
RAND：生成一个0到1之间的随机数。
- 示例：SELECT RAND();返回结果为0.34567
SIGN：返回数值的符号，1表示正数，0表示零，-1表示负数。
- 示例：SELECT SIGN(-15);返回结果为 -1
TRUNCATE：截断数值，保留指定小数位数。
- 示例：SELECT TRUNCATE(3.14159, 2);返回结果为 3.14

日期函数

CURDATE：返回当前日期。
- 示例：SELECT CURDATE();返回结果为当前日期的日期部分，如 "2023-06-29"
CURTIME：返回当前时间。
- 示例：SELECT CURTIME();返回结果为当前时间的时间部分，如"15:30:45"
NOW：返回当前日期和时间。
- 示例：SELECT NOW(); 返回结果为当前日期和时间，如 "2023-06-29 15:30:45"
DATE：从日期时间值中提取日期部分。
- 示例：SELECT DATE('2023-06-29 15:30:45');返回结果为 "2023-06-29"
TIME：从日期时间值中提取时间部分。
- 示例：SELECT TIME('2023-06-29 15:30:45'); 返回结果为 "15:30:45"
DATE_FORMAT：按照指定的格式返回日期时间值的字符串表示。
- 示例：SELECT DATE_FORMAT('2023-06-29', '%Y-%m-%d');返回结果为"2023-06-29"
DATE_ADD：对日期进行加法运算。
- 示例：SELECT DATE_ADD('2023-06-29', INTERVAL 1 DAY);返回结果为"2023-06-30"
DATE_SUB：对日期进行减法运算。
- 示例：SELECT DATE_SUB('2023-06-29', INTERVAL 1 MONTH);返回结果为"2023-05-29"
DATEDIFF：计算两个日期之间的天数差。
- 示例：SELECT DATEDIFF('2023-06-29', '2023-06-15');返回结果为14
DAYOFWEEK：返回日期对应的星期几（1代表星期日，2代表星期一，以此类推）。
- 示例：SELECT DAYOFWEEK('2023-06-29');返回结果为4

流程函数

IF：根据条件判断返回不同的值。
- 示例：SELECT IF(10 > 5, 'Yes', 'No');返回结果为 "Yes"

CASE：根据条件执行不同的逻辑操作。

示例：

SELECT
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'D'
END AS grade
FROM students;

根据学生的分数，返回相应的等级。

COALESCE：返回参数列表中第一个非NULL值。
- 示例：SELECT COALESCE(NULL, 'Value');返回结果为 "Value"
NULLIF：如果两个参数相等，则返回NULL，否则返回第一个参数。
- 示例：SELECT NULLIF(10, 10);返回结果为 NULL
GREATEST：返回参数列表中的最大值。
- 示例：SELECT GREATEST(5, 3, 9);返回结果为 9
LEAST：返回参数列表中的最小值。
- 示例：SELECT LEAST(5, 3, 9);返回结果为3

排序函数

ORDER BY：用于对查询结果进行排序。
- 示例：SELECT * FROM students ORDER BY score DESC;按照学生的分数降序排序，从高到低排列。
ASC：指定升序排序。
- 示例：SELECT * FROM products ORDER BY price ASC;按照产品的价格升序排序。
DESC：指定降序排序。
- 示例：SELECT * FROM employees ORDER BY hire_date DESC;按照员工的入职日期降序排序。
FIELD：根据字段值的顺序进行排序。
- 示例：SELECT * FROM products ORDER BY FIELD(category, 'Electronics', 'Clothing', 'Books');根据产品的类别字段按照指定顺序排序。
RAND：随机排序查询结果。
- 示例：SELECT * FROM users ORDER BY RAND();随机排序用户表中的记录。
FIND_IN_SET：根据字段值在指定字符串集合中的位置进行排序。
- 示例：SELECT * FROM colors ORDER BY FIND_IN_SET(color, 'Red,Green,Blue');根据颜色字段在指定集合中的位置排序。

索引

索引基础

什么是索引
- 索引是数据库中用于快速查找和访问数据的数据结构。它类似于书籍的目录，提供了对数据的快速定位和检索。
- 索引的主要目的是提高数据库查询的性能，减少需要扫描整个数据表的时间。它通过创建一个额外的数据结构，存储了某个或多个列的值以及指向相应数据行的指针或物理地址。
- 数据库中的索引可以基于单个列或多个列的值来创建，这取决于查询的需求。常见的索引类型包括B树索引、哈希索引、全文索引等。
- 使用索引进行查询时，数据库系统会首先检查索引，根据查询条件定位到满足条件的索引键或索引键范围，然后通过索引中的指针或物理地址快速访问相应的数据行。这样可以避免扫描整个数据表，大大提高了查询的效率。
- 虽然索引可以加快查询速度，但索引的创建和维护也需要额外的存储空间和处理开销。因此，在设计数据库时需要权衡索引的使用，根据查询的频率、数据表的大小以及写操作的频率等因素来选择适当的索引策略。
- 总而言之，索引是数据库中的一种数据结构，用于加快数据查询的速度和提高数据库的性能。它通过存储列值和指向数据行的指针，实现了对数据的快速定位和检索。
索引的优点缺点
- 优点
  1. 快速查询：索引可以显著提高数据查询的速度。通过使用索引，系统可以更快地定位和访问所需的数据，而不需要逐个扫描整个数据集。
  2. 提高性能：对于大型数据集和复杂查询，索引可以显著减少查询操作的时间复杂度，从而提高系统的整体性能和响应速度。
  3. 数据唯一性和完整性：索引可以帮助确保数据的唯一性和完整性。通过在索引字段上设置唯一约束，可以防止重复数据的插入，并保持数据的一致性。
  4. 排序功能：索引可以用于对数据进行排序，使得按特定顺序检索数据变得更加高效。
- 缺点
  1. 空间开销：索引通常需要占用额外的存储空间。对于大型数据集，索引的存储需求可能相当大，可能会增加数据存储的成本。
  2. 更新操作的性能影响：当对数据进行更新、插入或删除操作时，索引需要进行相应的维护。这可能导致额外的开销和性能下降，特别是在频繁更新的情况下。
  3. 维护成本：随着数据的增加和变化，索引需要定期进行更新和维护，以确保其有效性和一致性。索引的维护可能需要一定的时间和资源。
  4. 复杂性增加：随着索引的引入，数据库或系统的复杂性可能会增加。需要考虑索引的设计、选择适当的索引类型以及合理使用索引的策略。
索引的种类
- 普通索引（Normal/Index）：普通索引是最基本的索引类型，用于加快对表中数据的查询速度。它可以基于一个或多个列的值创建，并允许重复值。普通索引可以加速等值查询和范围查询，但不要求索引列的唯一性。
- 主键索引（Primary Key Index）：主键索引是一种特殊的索引类型，用于唯一标识表中的每一行数据。主键索引要求索引列的值是唯一的，且不能为空。它的作用是加速根据主键进行查询和确保数据的完整性。在创建主键约束时，数据库系统会自动创建一个主键索引。
- 唯一索引（Unique Index）：唯一索引要求索引列的值是唯一的，但可以包含空值。与主键索引不同，唯一索引并不要求索引列作为表中的主键。唯一索引可用于加速唯一性约束的验证和快速查找唯一值。
- 全文索引（Full-Text Index）：全文索引用于在文本数据中进行全文搜索，它支持关键词搜索、模糊匹配和排序等操作。全文索引在处理大量文本数据的搜索场景中非常有用，如文章、博客、新闻等。它允许用户进行自然语言的查询，并返回与查询相关的匹配结果。
- 这些索引类型在数据库中的使用是根据不同的需求和数据特征而定的。普通索引适用于一般的查询加速，主键索引用于唯一标识行和加速主键查询，唯一索引用于确保唯一性和快速查找唯一值，全文索引用于全文搜索和文本数据的查询。
索引的数据结构
- B+Tree innodb
  - B+Tree的优点和特点
    - 高查询性能：B+树具有较好的平衡性，查询的时间复杂度稳定在O(log n)的级别。同时，由于叶子节点存储了所有的数据记录，范围查询和顺序访问的性能也很高。
    - 适应大规模数据集：B+树的结构使得它能够有效地处理大规模数据集。它的高度可扩展性和平衡性能够满足各种数据规模的需求。
    - 支持范围查询：B+树的有序性和链表连接的特性使得范围查询非常高效。可以通过遍历叶子节点链表来获取满足范围条件的数据。
    - 适用于磁盘存储：B+树的结构设计考虑了磁盘存储的特性，使得它更适合在磁盘上进行数据存储和访问。
- Hash (对象、值-->数字) 重复哈希碰撞(值不用哈希值相同)
  - 哈希索引是一种使用哈希函数进行索引的数据结构，旨在实现快速的等值查询。它将索引键值通过哈希函数映射到特定的存储桶或槽位，以实现高效的数据查找和访问。
  - 哈希索引的特点和工作原理如下：
    1. 哈希函数：哈希索引使用哈希函数将索引键值转换为固定长度的哈希码或哈希值。哈希函数通常具有快速计算和分布均匀的特性，以确保索引键值能够均匀地散列到存储桶中。
    2. 存储桶：哈希索引将数据存储在一组存储桶（buckets）或槽位（slots）中，每个存储桶具有唯一的标识符。哈希函数将索引键值映射到相应的存储桶。
    3. 索引查询：在进行等值查询时，通过哈希函数将查询的索引键值转换为哈希码，并根据哈希码找到对应的存储桶。然后，根据存储桶内的数据结构（如链表、二叉搜索树等），进行进一步的数据查找。
  - 哈希索引的优点和适用场景包括：
    - 快速等值查询：哈希索引通过哈希函数和直接的存储桶查找，实现了快速的等值查询。对于给定的索引键值，可以直接计算出对应的存储桶，无需遍历整个索引结构。
    - 适合高基数字段：哈希索引适用于具有高基数（不重复值很多）的字段，如唯一标识符或散列值。高基数字段可以减少哈希冲突的可能性，提高索引的效率。
    - 不适合范围查询：由于哈希函数的特性，哈希索引不适用于范围查询。范围查询需要遍历多个存储桶才能获取满足条件的数据，因此在范围查询方面哈希索引的性能较差。
    - 冲突处理：由于哈希函数的有限输出空间，可能存在哈希冲突，即不同的索引键值映射到相同的哈希码。冲突通常通过解决冲突的方法，如链表、开放地址法、再哈希等来处理。
- hash索引和Btree索引的区别
  1. 组织方式：
    - 哈希索引使用哈希函数将索引键映射到一个固定长度的哈希值，并将这些哈希值存储在索引中。它通常是基于散列桶的结构，其中每个桶存储具有相同哈希值的索引键。
    - B树索引使用平衡树的结构，将索引键按顺序存储在树的节点中。每个节点包含多个索引键和对应的指针或数据项，使得在树中进行范围查找更加高效。
  2. 查询性能：
    - 哈希索引适用于等值查询，即通过索引键的完全匹配查找数据。它可以在平均情况下以O(1)的时间复杂度快速定位到对应的数据项。然而，对于范围查询或模糊查询，哈希索引的性能较差。
    - B树索引适用于范围查询和模糊查询，因为它的节点按顺序存储索引键。它可以通过树的遍历从根节点开始，按序查找满足条件的数据项。B树索引的查询时间复杂度为O(log N)，其中N是索引键的数量。
  3. 索引维护：
    - 哈希索引对插入和删除操作具有较好的性能，因为它只需要计算哈希值并在相应的桶中插入或删除索引键。但是，哈希索引的动态维护和均衡性较差。
聚簇索引和非聚簇索引
- 聚簇索引：
  - 聚簇索引定义了数据的物理存储顺序，表中的数据按照聚簇索引的键值进行排序并存储在磁盘上。
  - 一个表只能有一个聚簇索引，因为数据必须按照某个键值顺序排列。
  - 聚簇索引的叶子节点包含实际的数据行，因此在满足查询条件时可以直接定位到对应的数据行。
  - 当通过聚簇索引进行范围查询时，性能通常较好，因为相关的数据行在物理上彼此相邻。
- 非聚簇索引：
  - 非聚簇索引是基于表中的列或列组合创建的独立的索引结构，它的叶子节点不包含实际的数据行，而是存储了指向数据行的指针（如行的物理地址或主键值）。
  - 一个表可以有多个非聚簇索引，因为它们与数据的物理存储顺序无关。
  - 非聚簇索引的叶子节点按照索引的键值进行排序，但它们不决定表中数据的物理存储顺序。
  - 通过非聚簇索引进行查询时，首先根据索引定位到对应的数据行指针，然后再通过指针访问实际的数据行。
  - 非聚簇索引适合用于等值查询或满足特定条件的范围查询，但对于需要查询大量数据的范围查询可能性能较差。
回表查询
- 回表查询是指在使用非聚簇索引进行查询时，需要通过索引中的指针返回到实际数据行的过程。当索引无法满足查询的需求时，数据库系统需要使用回表查询来获取额外的数据。
- 在回表查询中，首先使用非聚簇索引定位到满足条件的索引键，然后根据索引中存储的指针（通常是行的物理地址或主键值），进一步访问实际的数据行获取更多的数据。这个过程可能需要进行多次磁盘访问，因为数据行可能分散存储在不同的磁盘页或块中。
- 回表查询可能会导致额外的IO开销，因为需要多次读取磁盘上的数据。尽管非聚簇索引可以快速定位到满足查询条件的数据行指针，但回表查询可能会降低查询的性能，特别是当需要访问大量数据行时。
- 为了减少回表查询的开销，可以考虑使用覆盖索引（Covering Index）。覆盖索引是一种包含了查询所需的所有列的非聚簇索引，这样查询就可以完全通过索引来满足，无需进行回表查询，从而提高查询性能。
索引的建立原则
1. 选择适当的索引列：选择那些经常用于查询条件的列作为索引列。索引应该覆盖经常用于过滤、连接和排序操作的列，以提高查询性能。
2. 考虑列的选择性：选择具有较高选择性的列作为索引列。选择性是指列中不同值的数量与总行数的比率。选择性高的列可以更好地过滤数据，提高索引的效果。
3. 考虑查询的频率：针对经常执行的查询，特别是那些执行时间较长的查询，建立适当的索引可以提高性能。根据查询的频率和重要性，优先考虑为常用查询创建索引。
4. 考虑数据表的大小和更新频率：对于较小的数据表或不经常更新的数据表，可以更广泛地创建索引。但对于大型数据表或频繁更新的数据表，过多的索引可能会增加写操作的开销。
5. 避免过度索引化：过多的索引可能会增加存储空间和维护开销，并降低写操作的性能。避免为每个列都创建索引，而是根据实际需求和查询模式选择合适的索引。
6. 考虑联合索引：当多个列经常同时出现在查询条件中，可以考虑创建联合索引。联合索引可以覆盖多个列，并提供更好的查询性能。
7. 定期维护和优化索引：随着数据的增长和变化，索引的效果可能会下降。定期进行索引的重建、重新组织和优化，以保持查询性能的稳定。

sql优化方案

使用合适的索引：
- 在经常被查询的列上创建索引，以提高查询性能。例如，对于经常用于WHERE条件或连接操作的列，可以创建索引。
- 避免在低选择性列上创建索引，以避免索引扫描的开销。
- 示例：
```
  -- 创建索引
  CREATE INDEX idx_column ON table_name (column_name);
  -- 使用索引的查询
  SELECT * FROM table_name WHERE column_name = 'value';
```
优化查询语句：
- 减少查询中的不必要列，只选择需要的列。
- 使用JOIN操作时，确保连接条件的准确性和完整性。
- 避免使用SELECT *，而是明确列出所需的列名。
- 示例：
```
-- 仅选择所需的列
SELECT column1, column2 FROM table_name;
-- 使用JOIN操作的正确连接条件
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;
```

使用合适的SQL函数和操作符：

避免使用慢速的函数或操作符，例如LIKE操作符以通配符开头的模式匹配
使用更高效的函数和操作符，例如使用IN替代多个OR条件。

示例：

-- 避免使用LIKE '%value'，而使用更快的操作符
SELECT * FROM table_name WHERE column_name LIKE 'value%';
-- 使用IN替代多个OR条件
SELECT * FROM table_name WHERE column_name IN ('value1', 'value2', 'value3');

优化表结构：

合理设计数据库表和关系，减少冗余和不必要的列。
正确设置主键、外键和约束，以保证数据完整性和一致性。

示例：

-- 合理设计表结构，避免冗余列
   CREATE TABLE table_name (
     column1 datatype,
     column2 datatype,
     ...
   );
   
   -- 设置主键和外键
   CREATE TABLE table1 (
     id INT PRIMARY KEY,
     ...
   );
   
   CREATE TABLE table2 (
     id INT PRIMARY KEY,
     table1_id INT,
     FOREIGN KEY (table1_id) REFERENCES table1(id),
     ...
   );

使用适当的缓存机制：

对于频繁查询但不经常更新的数据，可以使用缓存机制（如Redis）来减轻数据库的负载。

示例：

 -- 从缓存中获取数据
   SELECT * FROM cache_table WHERE id = 'value';
   
   -- 如果缓存中不存在，则从数据库查询并更新缓存
   SELECT * FROM table_name WHERE id = 'value';

联合索引
- 联合索引（Composite Index）是一种数据库索引，它基于多个列的值创建。与单列索引不同，联合索引将多个列组合在一起作为索引键，以提高查询性能和加速数据检索。
- 联合索引的创建可以基于多个列的顺序，它可以是两个列、三个列，甚至更多列的组合。这样的索引可以同时考虑多个列的值，从而加快查询的速度和效率。
- 使用联合索引的优势包括：
  1. 减少索引数量：通过将多个列组合为联合索引，可以减少索引的数量。相比为每个列单独创建索引，使用联合索引可以节省存储空间并提高查询性能。
  2. 支持多列查询：联合索引可以有效地支持涉及联合索引中列的查询。当查询涉及到联合索引的列时，数据库可以利用索引的顺序和组合来快速定位匹配的数据行。
  3. 覆盖查询：如果联合索引中的列包含查询所需的所有列，查询可以直接使用索引而无需额外的数据行查找。这种情况下，称之为覆盖查询，可以进一步提高查询性能。
  4. 排序和连接操作的优化：当查询需要按照联合索引的列进行排序或连接操作时，联合索引可以提供更高效的执行计划，减少排序和连接操作的开销。
- 需要注意的是，联合索引也有一些注意事项：
  1. 列顺序的重要性：联合索引的列顺序非常重要。查询语句中的列顺序必须与联合索引的列顺序一致，以充分利用索引。如果查询中的列顺序与索引的列顺序不匹配，索引可能无法发挥作用。
  2. 索引选择性：联合索引的选择性是指不同值的数量与总行数的比率。选择性较高的列可以提供更好的索引效果。如果某些列的选择性较低，可以考虑单独为这些列创建单列索引。
  3. 索引更新的代价：随着联合索引列的增加，索引的更新和维护操作的代价也会增加。因此，在创建联合索引时需要权衡索引的数量和更新操作的频率。
- 最左匹配原则（Leftmost Prefix Rule）是指在联合索引中，如果查询涉及到联合索引的多个列，索引将优先匹配最左边的列，并且只能使用索引中的连续列来进行匹配。
  - 具体来说，最左匹配原则表明以下情况：
    1. 联合索引中的最左边的列是最重要的，它的匹配将首先被考虑。
    2. 联合索引可以支持最左前缀匹配。这意味着，查询可以只使用索引的最左边的连续列进行匹配，并且只有在最左边的列匹配成功后，才会继续匹配后续的列。
  - 最左匹配原则的重要性在于它影响了联合索引的使用效果和查询优化。如果查询涉及到联合索引的多个列，但未按照最左匹配原则的顺序进行匹配，那么索引可能无法发挥作用，导致查询性能下降。
  - 举个例子来说明最左匹配原则的应用：
    - 假设有一个联合索引 (col1, col2, col3)，查询语句中涉及到这三个列的筛选条件，如 WHERE col1 = value1 AND col2 = value2 AND col3 = value3。
    - 按照最左匹配原则，查询可以充分利用索引的效果，因为查询中的列顺序与索引的列顺序一致，而且是连续的。这样可以快速定位符合条件的数据行。
    - 但是，如果查询语句的列顺序不按照最左匹配原则的顺序，比如 WHERE col2 = value2 AND col1 = value1 AND col3 = value3，那么索引可能无法发挥作用。因为查询中的第一个列并不是索引的最左边的列，无法直接利用索引定位数据行，而需要进行全表扫描或其他形式的查询操作。
    - 因此，在设计联合索引和编写查询语句时，应该遵循最左匹配原则，以保证索引的有效使用和查询的优化。

补充索引

前缀索引

前缀索引（Prefix Index）是一种数据结构，用于高效地存储和检索字符串数据。它通过将字符串按照前缀进行分割和排序，以便在搜索时快速定位到具有特定前缀的字符串。这种索引结构通常用于搜索引擎、自动完成功能和字典等应用中。
例如，如果我们有一个包含许多单词的前缀索引，比如["apple", "application", "banana", "bat", "cat"]，当我们搜索以"app"开头的单词时，可以通过在索引中定位到前缀为"app"的起始位置，并依此找到以该前缀开头的所有单词。
前缀索引可以使用不同的数据结构来实现，包括字典树（Trie）、压缩字典树（Compressed Trie）和前缀树（Prefix Tree）等。这些数据结构的选择取决于具体的需求和性能要求。

执行计划 explain

在数据库中，执行计划（Execution Plan）是针对一个查询语句的操作步骤和执行顺序的详细说明。它描述了数据库系统将如何执行查询，并选择哪些索引、算法和操作来获取查询结果。
使用EXPLAIN语句可以获取执行计划。具体的语法可能因数据库管理系统而异，但通常的形式是在查询语句前加上EXPLAIN关键字。执行计划会返回有关查询执行的统计信息和执行顺序，如使用的索引、连接类型、表扫描方式等。
通过查看执行计划，可以评估查询的效率，并帮助识别潜在的性能问题。它可以帮助开发人员和数据库管理员优化查询，例如通过创建适当的索引、重写查询或优化数据库配置。

什么是数据事务？

开启事务
- start transcation或者begin
- 事务的起点
提交事务
- commit
- 提交事务的所有操作，就是将事务中所有对数据库的操作都写到磁盘上的物理数据库中，事务正常结束。
回滚事务
- rollback
- 撤销事务，事务在运行过程中出现某种异常，事务无法继续执行，系统将事务中对数据库已完成的操作全部撤销，滚回到事务开始之前的状态。
- savepoint 断点存档

事务四大特性

原子性（Atomicity）：事务中的操作被视为一个不可分割的整体，要么全部执行成功，要么全部回滚，没有中间状态。
一致性（Consistency）：事务在开始和结束时，数据库的状态必须保持一致。事务执行前后，数据库从一个有效状态转换到另一个有效状态。
隔离性（Isolation）：并发执行的事务之间应该互相隔离，每个事务都感觉不到其他事务的存在，以避免数据不一致性。
持久性（Durability）：一旦事务提交成功，其结果应该永久保存在数据库中，即使系统发生故障或重启。

事务关联的日志

redo log
- Redo日志：Redo日志是一种事务日志，用于确保数据库的持久性和恢复能力。它记录了对数据库的修改操作，例如插入、更新和删除操作。重做日志的主要目的是在数据库崩溃或系统故障后，将这些修改操作重新应用到数据库中，以保持数据的一致性。重做日志通常采用追加方式写入磁盘，以提供高性能和可靠性。
undo log
- Undo日志：Undo日志是另一种事务日志，用于支持事务的回滚操作。它记录了事务执行过程中对数据库的修改操作，以便在事务回滚时可以撤销这些修改。当事务需要回滚时，数据库管理系统会使用Undo日志中的信息将事务的修改操作逆向执行，从而将数据库恢复到事务开始之前的状态。

事务的三种并发问题

脏读（Dirty Read）：脏读发生在一个事务读取了另一个事务尚未提交的数据。如果后续的事务回滚，读取到的数据就是无效的或不一致的。脏读可能导致数据的不准确性和误解。
不可重复读（Non-repeatable Read）：不可重复读发生在一个事务内多次读取同一数据时，得到的结果不一致。这是因为在读取期间，另一个事务对该数据进行了修改或删除。这种情况下，事务无法保持数据的一致性。
幻读（Phantom Read）：幻读发生在一个事务内多次执行同一查询时，得到的结果集不一致。这是因为在查询期间，其他事务插入了新的数据行，使得结果集出现了新的"幻影"行。幻读可能导致查询结果的不确定性和不一致性。

事务的隔离级别

读未提交（Read Uncommitted）：最低的隔离级别。允许事务读取其他事务尚未提交的数据。这种隔离级别可能导致脏读、不可重复读和幻读的问题。
读提交（Read Committed）：要求一个事务只能读取已经提交的数据。它解决了脏读的问题，但仍然可能导致不可重复读和幻读的问题。
可重复度: 可重复读（Repeatable Read）：要求一个事务在整个事务期间多次读取同一数据时，能够得到一致的结果。它解决了脏读和不可重复读的问题，但仍然可能导致幻读的问题。
串行化（Serializable）：最高的隔离级别，要求事务之间完全串行执行。它通过对事务加锁来避免脏读、不可重复读和幻读的问题，但也对并发性能有一定的影响。
举例分析：
1. 读未提交（Read Uncommitted）：
  - 示例：事务A修改了一行数据，但尚未提交。事务B读取了未提交的数据，导致脏读问题。
  - 并发问题：脏读
2. 读提交（Read Committed）：
  - 示例：事务A修改了一行数据并提交，事务B读取同一行数据。在事务B读取期间，事务A又修改了该行数据并提交。事务B的两次读取结果不一致，导致不可重复读问题。
  - 并发问题：不可重复读
3. 可重复读（Repeatable Read）：
  - 示例：事务A读取了一批数据并执行了一个范围查询，事务B在事务A执行期间插入了新的数据行，导致事务A的后续范围查询结果包含了新增的数据行，出现了幻读问题。
  - 并发问题：幻读
4. 串行化（Serializable）：
  - 示例：事务A读取了一批数据并执行了一个范围查询，事务B在事务A执行期间插入、更新或删除了任何相关数据行，由于串行化级别的要求，事务B必须等待事务A完成，确保数据的一致性。
  - 并发问题：无

并发控制方案

LBCC
- 在数据库领域，"LBCC" 指 "Lock-Based Concurrency Control"，即基于锁的并发控制。
- Lock-Based Concurrency Control（LBCC）是一种常见的并发控制机制，用于确保在多用户环境中对数据库进行并发访问时的数据一致性。它通过使用锁来管理对共享资源的访问，以防止并发事务之间的冲突。
- 在 LBCC 中，事务在访问共享资源之前会获取相应的锁。锁可以分为共享锁（读锁）和排他锁（写锁）。共享锁允许多个事务同时读取资源，而排他锁则只允许单个事务进行写操作。
- 当一个事务请求对资源施加锁时，如果该资源已经被其他事务锁定，那么该事务可能需要等待，直到资源可用。这种等待可以避免并发操作对数据造成破坏。
- LBCC 是一种经典的并发控制方法，尽管它能够确保数据的一致性，但它可能会导致锁冲突和并发性能的下降。因此，现代数据库系统通常采用其他更高级的并发控制机制，如多版本并发控制（MVCC）等，以提高并发性能和吞吐量。
MVCC
- MVCC，即多版本并发控制（Multiversion Concurrency Control），是一种常见的数据库并发控制机制。
- MVCC 的主要思想是为每个事务创建一个可见的数据库快照，而不是使用传统的锁机制。每个事务在开始时获取一个时间戳（或版本号），用于标识其开始时间。数据库中的每个数据项都会带有版本信息，包括创建时间戳和删除时间戳。
- 当一个事务读取数据时，它只能看到比自己时间戳早的版本。这样，不同事务之间的读操作可以并发执行，因为它们读取的是不同的版本。如果一个事务修改数据，则会创建一个新的版本，并将修改后的数据写入新版本中。其他事务仍然可以读取旧版本的数据，保证了读写操作的并发性。
- MVCC 的优点是可以提供较高的并发性能，因为读操作不会阻塞其他读操作，并且读写操作之间也可以并发执行。同时，MVCC 也保证了事务的隔离性和数据的一致性。
- 许多主流数据库系统（如Oracle、MySQL、PostgreSQL）都采用了 MVCC 作为并发控制的机制，尤其在高并发的情况下表现良好。它是一种有效的方式来管理并发访问数据库并确保数据的一致性。
- 读写并存的时候，写操作会根据目前数据库的状态，创建一个新版本，并发的读则依旧访问旧版本的数据。
  - 更新一条数据我们版本号+1
  - 删除一条数据版本号+1
  - 我们读取数据的时候，只能读取到当前的版本号之前的数据

锁

分类

锁的粒度
- 行锁（Row Locking）：行锁是最细粒度的锁粒度，它是针对数据库表中的单行数据进行的锁定。当一个事务需要修改或读取某个特定行的数据时，会获取该行的行锁，其他事务必须等待该行锁释放才能继续操作。行锁可以提供最大的并发性，因为不同事务可以同时操作不同的行数据。
- 表锁（Table Locking）：表锁是针对整个数据库表进行的锁定。当一个事务需要修改或读取某个表的数据时，会获取该表的表锁，其他事务必须等待该表锁释放才能继续操作。表锁的粒度较粗，会对并发性能产生较大的影响，因为不同事务需要竞争同一个表级锁。
- 页锁（Page Locking）：页锁是介于行锁和表锁之间的一种锁粒度。它是针对数据库表的一页（通常是固定大小的数据页）进行的锁定。当一个事务需要修改或读取某个页的数据时，会获取该页的页锁，其他事务必须等待该页锁释放才能继续操作。页锁在一定程度上平衡了行锁和表锁的粒度，可以减少锁冲突和提高并发性能。
- 在实际应用中，选择适当的锁粒度非常重要，以确保并发访问数据库时的性能和数据一致性。通常情况下，应尽量使用行锁来减小锁的竞争范围，但在一些特殊情况下，如大规模批量操作或特定的数据访问模式，可能需要使用更粗粒度的锁（表锁或页锁）来满足需求。
业务锁读锁
- 业务锁是一种针对特定业务需求而实现的锁机制，用于保证业务操作的正确性和一致性。它可以是基于数据库的行锁或其他自定义的锁机制。
- 读锁（Read Lock）是一种用于并发读取操作的锁，也称为共享锁（Shared Lock）。读锁允许多个事务同时获取同一资源的读取权限，但不允许写入操作。多个事务可以并发地读取共享资源，彼此之间不会产生冲突。读锁之间是兼容的，即多个事务可以同时持有读锁，但读锁与写锁之间是互斥的，即一个事务持有写锁时，其他事务无法获取读锁。
互斥锁写锁独占锁
- 互斥锁（Mutex Lock）是一种常见的同步机制，用于实现临界区的互斥访问。它保证同一时间只有一个线程可以进入被保护的临界区，防止多个线程同时访问和修改共享资源，从而避免数据竞争和不确定的行为。
- 写锁（Write Lock）是一种独占锁（Exclusive Lock），它提供了对共享资源的独占访问权限。写锁的特点是一次只允许一个线程获取写锁，并且在写锁被持有时，其他线程无法获取读锁或写锁，以确保数据的一致性。写锁适用于对共享资源进行写操作的场景，它防止了并发写操作的冲突。
- 独占锁（Exclusive Lock）是一种用于独占访问共享资源的锁机制。它与共享锁（如读锁）不同，独占锁在一段时间内只允许一个线程持有，其他线程无法同时持有独占锁。独占锁的目的是保证在某个时刻只有一个线程能够执行特定的操作，从而避免并发冲突和数据不一致的问题。
- 互斥锁、写锁和独占锁都是常见的同步机制，用于控制对共享资源的访问。它们的使用和适用场景根据具体情况而定。互斥锁是一种通用的锁机制，用于保护临界区的互斥访问；写锁是一种独占锁，用于保证写操作的独占性；而独占锁是一种广义的锁概念，可以包括写锁在内，用于控制对共享资源的独占访问。
意向锁
- 意向共享锁
  - 意向共享锁（IS锁）是表级锁，用于表示一个事务想要对某个表的某个数据行进行共享访问。意向共享锁不会直接锁定数据行，而是向其他事务声明自己的意图，即希望在该表上获取共享锁。多个事务可以同时持有意向共享锁，而不会产生冲突。当事务想要获取某个数据行的共享锁时，首先需要检查是否存在意向排它锁或排它锁，如果不存在，则可以获取共享锁。
- 意向排它锁
  - 意向排它锁（IX锁）是表级锁，用于表示一个事务想要对某个表的某个数据行进行排它访问（即写操作）。意向排它锁不会直接锁定数据行，而是向其他事务声明自己的意图，即希望在该表上获取排它锁或共享锁。意向排它锁与意向共享锁是互斥的，同一时间只能有一个事务持有意向排它锁或排它锁。
- 意向共享锁和意向排它锁的引入是为了提高并发性能和锁粒度。当一个事务需要对某个数据行进行操作时，它首先检查是否存在意向排它锁，如果不存在，则可以获取共享锁；如果存在意向排它锁或排它锁，则需要等待锁的释放。
乐观锁
- 乐观锁是一种乐观的并发控制策略，它假设并发访问不会导致冲突，而是在更新数据时进行检查。通常，乐观锁会在读取数据时记录一个版本号或时间戳，并在更新数据时检查这个版本号或时间戳是否发生了变化。如果检测到其他事务已经更新了数据，则当前事务的更新操作可能会被中止或回滚。乐观锁适用于并发读取频繁、冲突较少的场景，可以提高并发性能。
悲观锁
- 悲观锁是一种悲观的并发控制策略，它假设并发访问会导致冲突，因此在访问共享资源之前就对其进行加锁，防止其他事务的访问和修改。悲观锁通常使用独占锁（如行锁或表锁）来实现，确保同一时间只有一个事务可以访问被锁定的资源。悲观锁适用于并发冲突较多的场景，可以保证数据的一致性，但会降低并发性能。
字段锁
- 字段锁（Field Locking）是指对数据库表中的某个字段进行锁定，限制其他事务对该字段的访问和修改。字段锁通常使用乐观锁机制实现，通过在字段上添加版本号或时间戳，检查并发冲突并进行冲突处理。
间隙锁
- 间隙锁（Gap Lock）是指在数据库索引中的间隙（两个索引键之间的范围）上设置的锁。间隙锁可以防止其他事务在该间隙内插入新的索引键，从而保持索引的完整性。间隙锁通常用于防止幻读（Phantom Read）的发生，即在一个事务中多次执行同一个查询时，得到的结果集不一致。
临键锁
- 临键锁（Next-Key Lock）是一种结合了间隙锁和行锁的锁机制，用于保护数据库索引的完整性和避免幻读。临键锁会在索引键和间隙之间设置锁，防止其他事务在索引键范围内插入或修改数据。临键锁的设计目的是解决间隙锁和行锁单独使用时可能出现的问题，提高并发性能和数据一致性。
死锁
- 死锁的四个必要条件
  1. 互斥条件：至少有一个资源被标记为只能同时由一个进程使用。
  2. 占有并等待条件：进程已经获取了至少一个资源，并在等待获取其他被其他进程占有的资源。
  3. 不可剥夺条件：资源不能被强制性地从一个进程中剥夺，只能由持有该资源的进程主动释放。
  4. 循环等待条件：存在一个进程的资源请求序列，使得每个进程都在等待下一个资源被释放，形成一个循环等待链。
- 如何避免死锁
  1. 破坏互斥条件：允许多个进程同时访问资源，例如引入共享资源的概念，使得多个进程可以同时读取资源而不产生冲突。
  2. 破坏占有并等待条件：要求进程在开始执行之前一次性申请所有需要的资源，或者在申请资源时采用预先分配策略，即进程只有在获得所有需要的资源时才能开始执行。
  3. 破坏不可剥夺条件：允许系统在必要时剥夺进程的资源。这可以通过引入资源预约和回收机制来实现，使得系统可以在需要时强制回收某个进程的资源以满足其他进程的需求。
  4. 破坏循环等待条件：通过对资源进行适当的排序和分配，破坏进程之间形成的循环等待链。可以采用资源有序分配法，要求每个进程按照固定的顺序申请资源，或者引入资源的层次分级，确保每个进程只能申请比其优先级更低的资源。

视图

数据库视图是一种虚拟的表，它基于数据库中的一个或多个表的查询结果。视图不是实际存储数据的对象，而是在查询时动态生成的结果集。
1. 视图的创建：使用CREATE VIEW语句创建视图。视图由一个SELECT语句定义，并赋予一个名称。
  
  示例：
```
  CREATE VIEW view_name AS
  SELECT column1, column2
  FROM table_name
  WHERE condition;
```
1. 视图的用途：
  - 简化复杂的查询：视图可以封装复杂的查询逻辑，简化查询语句的编写。
  - 数据安全性：通过视图，可以限制用户只能访问特定列或行的数据，保护敏感数据的安全性。
  - 数据抽象：视图可以隐藏底层表的细节，提供一种抽象层，使用户只关注需要的数据。
1. 视图的查询：视图可以像表一样进行查询操作，使用SELECT语句检索数据。
  
  示例：
```
  SELECT * FROM view_name;
```
2. 视图的更新：在某些情况下，可以通过视图对底层表进行更新操作。视图的更新操作将反映在底层表中。
  
  示例：
```
  UPDATE view_name SET column1 = 'new_value' WHERE condition;
```
3. 视图的修改和删除：可以使用ALTER VIEW语句修改视图的定义，使用DROP VIEW语句删除视图。
  
  示例：
```
  ALTER VIEW view_name AS
  SELECT column1, column2, column3
  FROM table_name
  WHERE condition;
  
  DROP VIEW view_name;
```
4. 视图的限制：
  - 视图不能包含ORDER BY子句，除非使用了TOP或LIMIT来限制结果集的大小。
  - 视图的定义可能包含特定数据库管理系统的限制，如不支持子查询或特定函数。

存储过程

存储过程（Stored Procedure）是一段预定义的可重复使用的数据库代码，它被存储在数据库中并可以被调用执行。存储过程可以包含一系列的SQL语句、控制流程、变量和参数，用于完成特定的任务。使用CREATE PROCEDURE语句创建存储过程。存储过程由一组SQL语句和其他逻辑组成，并赋予一个名称。

示例：

  CREATE PROCEDURE procedure_name(parameter1 INT, parameter2 VARCHAR(50))
  BEGIN
   -- 存储过程的逻辑代码
   SELECT column1, column2
   FROM table_name
   WHERE condition;
  END;

存储过程的调用：使用CALL语句调用存储过程，并传递参数。

示例：
```
  CALL procedure_name(10, 'parameter_value');
```

存储过程的参数：存储过程可以接受输入参数和输出参数，用于传递数据。

示例：

  CREATE PROCEDURE procedure_name(IN parameter1 INT, OUT parameter2 VARCHAR(50))
  BEGIN
   -- 存储过程的逻辑代码
   SET parameter2 = CONCAT('Hello, ', parameter1);
  END;

存储过程的变量：存储过程可以定义局部变量，用于在过程执行期间保存中间结果。

示例：

  CREATE PROCEDURE procedure_name()
  BEGIN
   DECLARE var1 INT DEFAULT 0;
   DECLARE var2 VARCHAR(50);
   
   -- 存储过程的逻辑代码
   SET var1 = 10;
   SET var2 = 'Value: ' + var1;
   SELECT var2;
  END;

存储过程的流程控制：存储过程支持条件判断和循环控制等流程控制语句，如IF、CASE、LOOP等。

示例：

  CREATE PROCEDURE procedure_name()
  BEGIN
   DECLARE i INT DEFAULT 0;
   -- 存储过程的逻辑代码
   WHILE i < 10 DO
    IF i % 2 = 0 THEN
     SELECT i;
    END IF;
    SET i = i + 1;
   END WHILE;
  END;

触发器

触发器（Trigger）是数据库中的一种特殊对象，它与表相关联，并在表的数据发生变化时自动触发执行一系列的操作。触发器可以用于实现数据完整性约束、日志记录、审计跟踪和业务逻辑等。以下是关于触发器的详细介绍，并附上一个示例：
1. 触发器的创建：使用CREATE TRIGGER语句创建触发器。触发器与特定表相关联，并定义在表的插入（INSERT）、更新（UPDATE）或删除（DELETE）操作上。
  
  示例：
```
  CREATE TRIGGER trigger_name
  AFTER INSERT ON table_name
  FOR EACH ROW
  BEGIN
   -- 触发器的逻辑代码
   INSERT INTO log_table (event) VALUES ('New record inserted');
  END;
```
1. 触发器的类型：
  - AFTER触发器：在指定操作之后触发执行。
  - BEFORE触发器：在指定操作之前触发执行。
  - INSTEAD OF触发器：替代指定操作的执行。
1. 触发器的事件：
  - INSERT触发器：在插入操作发生时触发。
  - UPDATE触发器：在更新操作发生时触发。
  - DELETE触发器：在删除操作发生时触发。
1. 触发器的引用：在触发器的逻辑代码中，可以使用NEW和OLD引用来访问插入、更新或删除的数据。
  
  示例：
```
  CREATE TRIGGER trigger_name
  AFTER UPDATE ON table_name
  FOR EACH ROW
  BEGIN
   -- 触发器的逻辑代码
   IF NEW.status = 'Completed' THEN
    INSERT INTO log_table (event) VALUES ('Record updated to Completed');
   END IF;
  END;
```
1. 触发器的修改和删除：可以使用ALTER TRIGGER语句修改触发器的定义，使用DROP TRIGGER语句删除触发器。
  
  示例：
```
  ALTER TRIGGER trigger_name
  AFTER INSERT ON table_name
  FOR EACH ROW
  BEGIN
   -- 修改后的触发器逻辑代码
   INSERT INTO log_table (event) VALUES ('New record inserted');
  END;
    DROP TRIGGER trigger_name;
```

MySQL数据库大总结

MySQL数据库大总结

数据库

SQL

DDL语句

DML语句

DQL语句

外键约束

级联删除

范式

多表关系

一对一

一对多

多对多 需要一张中间表 记录关联的数据

笛卡尔积

内连接

左外连接

右外连接

子查询

in exists

any all some

函数

字符串函数

数值函数

日期函数

流程函数

排序函数

索引

索引基础

补充 索引

前缀索引

执行计划 explain

什么是数据事务？

事务四大特性

事务关联的日志

事务的三种并发问题

事务的隔离级别

并发控制方案

锁

分类

视图

存储过程

触发器

多对多需要一张中间表记录关联的数据

补充索引