37.MySQL进阶知识

标签：事务 进阶 37 存储 查询 索引 MySQL 数据 主键

MySQL进阶知识

【一】试图

1）概念

• 视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用

• 在计算机科学中，视图（View）是一种虚拟表，其内容是一个或多个基本表的查询结果。

• 视图可以用于简化复杂查询、隐藏敏感数据、实现数据安全性和完整性约束等

• 视图可以是虚拟的，也可以是物化的。

• 总之，视图是一种查询结果的抽象表示，它提供了一种灵活和安全的方式来访问和操作数据库中的数据。

• • 通过使用视图，用户可以根据自己的需求获取所需的数据，而无需直接访问底层的基本表。

2）优点

• 简化复杂查询
• 数据安全性
• 数据完整性
• 逻辑数据独立性
• 性能优化

3）使用

1.语法

create view 视图名（字段名） as 虚拟表的查询SQL语句

2.创建视图

• 需要有视图名、原表名、过滤条件
• 创建的视图表名后面要跟上字段名，每一个字段名的类型和约束条件是延续原本表中的类型和约束条件的

create view 视图名(字段名1,字段名2) as 
select 字段名1,字段名2 from 表名 where 条件;

3.使用视图查询

• 创建成功后，便可像查询普通表一样使用视图进行数据筛选

delect * from 视图名;

4.更新视图

• 更新的是视图表的数据而不是原本的表中的数据

• 不是所有视图都可以更新，需要满足一定的条件

update 视图名 set 字段名=字段值 where  条件;

5.删除视图

deop view 视图名

【二】触发器

1）概念

在满足对表数据进行增删改的情况下，自动触发的功能，称为触发器

• 触发器是数据库管理系统中的一个概念，它是一种在数据库中定义的特殊对象，用于在满足特定条件时触发自动化的操作或逻辑。

• • 触发器主要用于监视数据库表的增删改操作，并在满足指定条件时触发相关的响应行为。

• 触发器通常与数据库表关联，当数据库表发生特定的数据变化时，触发器会自动执行相关的操作

• • 比如插入、更新、删除或查询数据等。

• 触发器可以作为一种数据库的约束，用于保证数据的完整性和一致性。

2）特点

• 触发器通常与表一起创建、修改和删除。
• 触发器可以在特定的数据操作之前或之后触发执行。
• 触发器可以根据用户定义的条件判断是否执行相应的逻辑。
• 触发器可以调用存储过程、函数、触发其他触发器等，实现更复杂的业务逻辑。

3）使用情况

增前、增后、删前、删后、改前、改后

4）使用

1.语法

create trigger 触发器的名字 
before/after insert/update/delete 
on 表名 for each row 
begin
	SQL语句
end

5）自定义触发器

1.创建触发器

• 使用数据库管理系统提供的语法，创建一个新的触发器对象。
• 创建时需要指定触发器的名称、触发时机、触发的表、触发时执行的逻辑

2.定义触发器逻辑

• 在创建触发器时，您需要定义触发器在触发时所执行的逻辑。
• 这可以是任何数据库支持的操作

3.定触发条件

• 触发条件是一个逻辑表达式，当表中的数据满足该表达式时，触发器才会被激活执行相关的逻辑。

4.绑定触发器

• 将触发器绑定到相应的表上。一般情况下，触发器会与指定的表相关联，当该表发生特定的数据操作时，触发器才会被触发执行。

5.测试触发器

• 在绑定触发器后，您可以进行一些测试操作，验证触发器的逻辑是否按照预期执行。

6）示例

# 创建表
create table cmd (
  id int primary key auto_increment,
  user char (32),
  priv char (10),
  cmd char (64),
  sub_time datetime, #提交时间
  success enum ('yes', 'no') #0代表执行失败
);
# 创建日志表  存储错误的命令日志以及时间 
create table errlog (
  id int primary key auto_increment,
  err_cmd char (64),
  err_time datetime
);


# 将mysql默认的结束符由;换成$$
delimiter $$  
# 创建的触发器结束符要以 ; 结尾
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
  if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
      insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
  end if;
end $$
# 结束之后记得再改回来，不然后面结束符就都是$$了
delimiter ; 


# 存数据
insert into cmd (
    user,
    priv,
    cmd,
    sub_time,
    success
)values
  ('kevin','0755','ls -l /etc',NOW(),'yes'),
  ('kevin','0755','cat /etc/passwd',NOW(),'no'),
  ('kevin','0755','useradd xxx',NOW(),'no'),
  ('kevin','0755','ps aux',NOW(),'yes');

【三】事务

1）概念

• 事务可以被看做是一个原子性的操作，一次执行多个命令
• 所有命令要么都成功、要么都失败

2）四大特性

1.原子性（Atomicity）

• 事务被视为一个原子操作，不可再分割。
• 要么所有的操作都成功执行，要么所有的操作都会被回滚到事务开始前的状态，确保数据的一致性。

2.一致性（Consistency）

• 事务执行前后，数据库应保持一致的状态。
• 在事务开始之前和结束之后，数据库必须满足所有的完整性约束，如数据类型、关系等。

3.隔离性（Isolation）

• 事务的执行结果对其他并发执行的事务是隔离的。
• 即一个事务的执行不应受到其他事务的干扰，各个事务之间应该相互独立工作，从而避免数据的不一致性。

4.持久性（Durability）

• 也叫永久性
• 一旦事务被提交，其结果应该永久保存在数据库中，并且可以被系统故障恢复。
• 即使系统发生宕机或崩溃，事务提交后的更改也应该是永久性的。

3）作用

在操作多条数据的时候，可能会出现某几条操作不成功的情况

• 数据一致性、并发控制、故障恢复、高效允许、数据完整性和安全性

# 示例  用户提交订单操作
● 检查库存： 
  ○ 系统需要检查所需商品的库存是否足够。
  ○ 如果库存不足，系统会提示用户库存不足，无法完成订单。
● 扣减库存： 
  ○ 如果库存充足，系统会将所购商品对应的库存数量减少。
● 生成订单： 
  ○ 系统会生成一个新的订单，包括订单号、商品信息、购买数量、价格等相关信息。
● 计算总价： 
  ○ 根据订单中的商品信息和购买数量，系统会计算出订单的总价格。
● 更新用户账户： 
  ○ 根据用户选择的支付方式，在扣除相应金额后，系统会更新用户账户余额或积分。
● 生成支付信息： 
  ○ 系统会生成相应的支付信息，以便用户完成支付。
● 通知物流部门： 
  ○ 系统会通知物流部门准备配送相关商品。
● 发送订单确认邮件/短信： 
  ○ 系统会向用户发送订单确认的邮件或短信，包括订单详细信息、配送信息等。
● 监控商品配送： 
  ○ 系统会跟踪订单的配送情况，并向用户提供订单状态更新。
● 完成订单： 
  ○ 当用户收到商品并确认满意后，订单状态会被更新为“已完成”。

4）使用

1.语法

# 开启事务
start transaction;
# 回滚(回到事务执行之前的操作)
rollback;
# 二次确认(确认之后无法回滚)
commit;

2.示例

# 先开启事务
start transaction;
# 插入数据
insert into user(username,password) values('opppp',666);
# 数据存在
# 发现数据不对，事务回滚
rollback;
# 数据消失
# 如果确认数据正常，直接提交事务
commit;
------------
# 再次插入数据
# 直接插入数据
insert into user(username,password) values('opppp',666);
# 发现数据存在
# 进行事务回滚
rollback;
# 数据依然存在

【四】存储过程

1）概念

存储过程就类似于Python中的自定义函数

内部包含了一系列可以执行的SQL语句，存储过程存储在MySQL服务端中，可以通过调用存储过程触发内部的SQL语句

• 存储过程是在关系型数据库中存储的一组自定义的SQL语句集合，可以接收参数并返回结果。
  • 它们被封装在数据库服务器中，并由应用程序通过调用存储过程来执行特定的数据库操作。

2）特点

• 预编译：

• • 存储过程在首次创建时会被编译和优化，之后每次执行时都不需要再进行编译，这样可以提高数据库的执行效率。

• 数据库端执行：

• • 与应用程序中直接执行SQL语句相比，存储过程在数据库服务器端执行，减少了网络传输开销，提高了数据访问性能。

• 代码重用：

• • 存储过程可以被多个应用程序共享和重用，避免了重复编写相同的SQL语句，提高了开发效率。

• 安全性：

• • 通过存储过程，可以将对数据库的访问权限限制在一定范围内，从而提高数据的安全性。

• 事务支持：

• • 存储过程可以包含事务处理逻辑，保证数据库操作的一致性和完整性。

• 简化复杂操作：

• • 存储过程可以执行复杂的数据操作和计算，简化了应用程序的开发和维护过程。

3）使用

1.定义存储器

create procedure 存储过程的名字(形参1,形参2...)
begin
	sql 代码
end

2.调用

call 存储过程的名字();

3.查看存储过程具体信息

show create procedure 存储过程的名字;

4.查看所有存储过程

show procedure status;

5.删除存储

drop procedure 存储过程的名字;

4）三种开发模式

1.第一种(提前编好存储过程)

• 应用程序

• • 程序员写代码开发

• MySQL

• • 提前编好存储过程，供应用程序调用

• 优点

• • 开发效率提升、执行效率提升

• 缺点

• • 考虑到人为因素、跨部门沟通等问题
  • 后续的存储过程的扩展性差

2.第二种(自己动手写据库操作)

• 应用程序

• • 程序员写代码开发之前
  • 涉及到数据库操作需要自己动手写

• 优点

• • 扩展性高

• 缺点

• • 开发效率低
  • 编写SQL语句繁琐，并且后续还需要考虑优化问题

3.第三种(ORM框架)

• 应用程序

• • 只写程序代码
  • 不写SQL语句
  • 基于别人写好的操作MySQL的Python的框架直接调用即可(ORM框架)

• 优点

• • 开发效率比上面的两种高

• 缺点

• • 语句的扩展性差
  • 可能会出现效率低下的问题

5）参考步骤

1.创建存储过程

• 使用数据库管理工具（如MySQL Workbench、SQL Server Management Studio等），编写存储过程的SQL语句，并在数据库中创建该存储过程。
• 存储过程的创建可以包括参数的定义和逻辑的实现。

2.参数定义

• 如果存储过程需要接收参数，可以在存储过程的创建语句中定义参数类型、名称和属性。
• 参数可以分为输入参数（用于传递数据给存储过程）和输出参数（用于存储过程返回结果给调用者）。

3.存储过程逻辑

• 在存储过程中编写SQL语句或其他处理逻辑，用于完成特定的数据库操作。
• 这些逻辑可以包括查询、插入、更新、删除等操作，也可以包括条件判断、循环、异常处理等控制结构。

4.调用存储过程

• 在应用程序中通过SQL语句调用存储过程。
• 调用存储过程时，提供必要的参数，并获取可能的输出结果。
• 调用存储过程可以使用特定的SQL语法，也可以通过数据库连接的API来实现。

5.处理存储过程返回的结果

• 根据存储过程的设计，处理从存储过程返回的结果。
• 这可能包括读取查询结果、解析输出参数值等。
• 处理结果的方式与具体的应用程序和数据库访问框架有关

6）示例

# 创建表
create table emp(
    id int primary key auto_increment,
    name varchar(50),
    dep_id int 
);
# 把原本的结束符替换到
delimiter $$
# 创建存储过程
create procedure p1(
    # in表示这个参数必须只能是传入不能被返回出去
	in m int,
    in n int,
    # out表示这个参数可以被返回出去
    # 还有一个inout表示即可以传入也可以被返回出去
    out res int
)
begin
    select name from emp where dep_id > m and dep_id <n;
     # 将res变量修改，用来标识当前的存储过程代码确实执行了
    set res = 666;
end$$
delimiter ;
# 定义存储过程中的变量
set @res=100;
# 查看写好的存储过程
select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.02 sec)
# 调用存储过程
call p1(1,5,@res);
# 查看写好的存储过程
select @res;
+------+
| @res |
+------+
|  666 |
+------+
# 把原本的结束符替换到
delimiter $$
# 创建存储过程
create procedure insert_data(
	in name varchar(50),
    in dep_id int
)
begin
     insert into emp(name,dep_id) values (name,dep_id);
end$$
delimiter ;
call insert_data("dream",200);

7）优点

• 代码重复
• 数据库性能优化
• 安全性增强

【五】函数

1）概念

• 跟存储过程是有区别的，存储过程是自定义函数，函数就类似于内置函数
• 注意与存储过程的区别，MySQL内置的函数只能在SQL语句中使用

2）字符串函数

将多个字符串连接成一个字符串

• concat（st1,st2）

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
-- 输出：Hello World

返回字符串的子串

• substring（str，start，length）

SELECT SUBSTRING('Hello World', 1, 5) AS Substring;
-- 输出：Hello

将字符串转大写

• upper（str）

SELECT UPPER('hello world') AS Uppercase;
-- 输出：HELLO WORLD

将字符串转换为小写

• length（str）

SELECT LOWER('HELLO WORLD') AS Lowercase;
-- 输出：hello world

返回字符串的长度

• length（str）

SELECT LENGTH('Hello World') AS Length;
-- 输出：11

移除指定字符

• Trim、LTrim、RTrim

SELECT TRIM('   hello world   ') AS TrimmedString;
-- 输出：hello world

SELECT LTRIM('   hello world   ') AS LeftTrimmedString;
-- 输出：hello world

SELECT RTRIM('   hello world   ') AS RightTrimmedString;
-- 输出：   hello world

获取左右起始指定个数字符

• Left/Right

SELECT LEFT('Hello World', 5) AS Left;
-- 输出：Hello

SELECT RIGHT('Hello World', 5) AS Right;
-- 输出：orld

返回读音相似值(对英文效果)

• Soundex

SELECT SOUNDEX('John Doe') AS Soundex;
-- 输出：J460

3）数值函数

对数值进行四舍五入

• ROUND(num, decimals):

select ROUND(3.14159, 2) as RoundedNum; 
# 3.14

返回不大于给定数值的最大整数

• FLOOR(num):

select FLOOR(3.99999) as FloorNum; 
# 3

返回不小于给定数值的最小整数

• CEILING(num):

select CEILING(3.00001) as CeilingNum; 
# 4

返回给定数值的绝对值

• ABS(num):

select ABS(-123.45) as AbsNum; 
# 123.45

4）日期和时间函数

返回当前日期和时间

• now():

select now() as CurrentDateTime; 
-- 当前日期和时间

返回当前日期

• curdate():

select curdate() as CurrentDate; 
-- 当前日期

返回当前时间

• curtime():

select curtime() as CurrentTime; 
-- 当前时间

格式化日期

• date_format(date, format):

select date_format(NOW(), '%Y-%m-%d %H:%i:%s') as FormattedDateTime; 
-- 格式化后的日期和时间

5）聚合函数

计算符合条件的行数

• count(column):

select count(*) as RowCount from Employees;

对指定列的值求和

• sum(column):

select sum(Salary) as SalarySum from Employees;

计算指定列的平均值

• avg(column):

select avg(Salary) as AverageSalary from Employees;

找到指定列的最小值

• min(column):

select min(Salary) as MinSalary from Employees;

找到指定列的最大值

• max(column):

select max(Salary) as MaxSalary from Employees;

【六】流程控制

1）概念

• case：
  • 根据表达式的值来决定返回哪个结果。它主要用于处理非确定性的情况，例如用户选择不同的选项或者数据库中存在不同的数据。
• if、 else：
  • 根据条件判断是否执行某个操作。它主要用于简单的逻辑判断。
• while、 until：
  • 当某条件满足时重复执行一段代码。它可以用于循环执行某个操作直到满足某个条件为止。
• for：
  • 重复执行一段代码直到满足结束条件。它主要用于遍历数组或集合。

2）if语句

在满足条件时执行一组语句，否则执行另一组语句

if expression then
    statements;
else
    statements;
end if;
# condition是一个条件表达式，如果为真，则执行第一组语句；否则执行第二组语句。

3）case语句

基于多个条件执行不同的操作

# 简单CASE语句的基本语法
case expression
    when value1 then
        statements;
    when value2 then
        statements;
    ...
    else
        statements;
end case;
# expression是待比较的表达式，当它等于某个值时，执行相应的语句。

# 搜索CASE语句的基本语法
case
    when condition1 then
        statements;
    when condition2 then
        statements;
    ...
    else
        statements;
end case;
# 每个WHEN子句后面跟着一个条件表达式，当某个条件为真时，执行相应的语句。

4）while语句

在满足条件的情况下反复执行一组语句

while condition do
    statements;
end while;
# 只要condition为真，statements将被不断执行

5）loop语句

用于无限循环执行一组语句，直到遇到LEAVE语句或满足特定条件时才退出循环

loop
    statements;
    if condition then
        leave;
    end if;
end loop;
# 在循环中使用LEAVE语句来手动退出循环

6）for语句

它可以让你在一组范围内迭代并执行一系列语句

for var in start,increment, end do
	statements;
end for;
# 从start开始，每次递增increment的值，一直循环到end，然后退出循环

【七】索引

1）概念

• 索引是存储引擎用于快速找到记录的一种数据结构，这也是索引最基本的功能。

• 索引对于良好的性能非常关键。

  • 数据量越大时，索引对性能的影响也越重要，好的索引可以将查询性能提高几个数量级。
  • 在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但是在数据量逐渐增大时，糟糕的索引会使MySQL的性能急剧的下降。

• 索引优化是查询性能优化最有效的手段。

• 如果想要在一本书中找到某个特定主题，一般会先看书的目录，找到对应的页码，然后直接翻到对应的页码即可查看。

  • 在MySQL中，存储引擎用类似的方法使用索引

    • 首先在索引中找到对应的值

      然后根据匹配的索引记录找到对应的数据行。

• 简单的说，数据库索引类似于书前面的目录，能加快数据库的查询速度。

2）类型

• primary key：主键约束
  • 不仅可以加速查询速度，还具有对应的约束条件
• unique：唯一约束
  • 不仅可以加速查询速度，还具有对应的约束条件
• index key：索引
  • 只有加速查询速度的功能
• foreign key：外键约束
  • 不是用来加速查询的

3）本质

• 通过不蹲的缩小想要的数据范围筛选出最终的结果 ，同时将随机事件(一页一页的翻)变成顺序时间(先找目录再找数据)

• 也就是说我们有了索引机制，我们可以总是用一种固定的方式查询数据

4）缺点

• 当表中有大量数据存在的前提下，创建索引的速度回非常慢
• 在索引创建完毕后，对表的查询性能会大幅度的上升，但是写的性能也会大幅度下降

5）使用

1.语法

show index from 表名;

mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | sn         |            1 | sn          | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | classes_id |            1 | classes_id  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> desc student;-- 实现表结构 --
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn         | int(11)     | YES  | UNI | NULL    |                |
| name       | varchar(20) | YES  |     | unkown  |                |
| qq_mail    | varchar(20) | YES  |     | NULL    |                |
| classes_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

2.创建索引

create index  索引名 on 表名(字段名);
# 索引名的命名规则一般是：index_表名_列名

# 示例
create index index_student_name on student(name);

3.查看索引

drop index 索引名 on 表名;

drop index index_student_name on student;

6）索引的数据结构

• B树：

  • 本质上就是一个N叉的搜索树（查找树）

• B+树：

  • 在B树的基础上进行改进

1.B+的特点

• 一个结点，可以存储N个key,N个Key划分出N个区间（B树是N+1个）；
• 每个结点的Key值，都会在子节点中存在（并且是子节点的最大值）；
• B+树的叶子结点首位相连，类似链表；
• 整个树的所有数据都包含在叶子结点中。所以非叶子结点的Key最终都会出现在叶子结点中。
• B+树还有一个显著特点，他的每一个叶子结点都关联这一个记录，这个记录就是我们实际数据库里每一个表里的每一行记录。

2.B+的优势

• 当前一个结点保存更多的key,最终树的高度是相对更矮的（B树也有这个优点），查询的时候可以减少IO的访问次数。
• 所有的查询最终都会落在叶子结点上（查询任何一个数据，经过的IO访问次数，是一样的。）稳定是很重要的，稳定可以让程序员对程序的运行效率有更准确的评估。
• B+树的所有叶子结点都用链表进行了链接(并且是一个双向链表)，这样就支持更直接的范围查询了。同时代码也更好写了。
• 由于数据都在叶子结点上，非叶子结点只存了key，所以我们就可以将叶子结点的一部分进行缓存（B树非叶子结点是存记录的），这样可以进一步减少IO次数。

3.小结

• 总之，B+树是一种高效的数据结构，具有平衡性、多路搜索、顺序访问性和存储利用率高等特点，适用于需要高效查询和排序的场景。
• 只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
• 查询次数由树的层级决定，层级越低次数越少

7）聚集索引(主键索引)

1.概念

• 聚集索引（Clustered Index）是关系型数据库中的一种索引类型，它决定了表中数据的物理存储顺序。
• 在一个表中，只能有一个聚集索引。
• 聚集索引对表进行了重新组织，使得数据按照聚集索引的键值顺序存储在磁盘上。
• 由于聚集索引决定了数据的物理存储顺序，因此通过聚集索引可以快速地找到特定范围内的数据
• MySQL的聚簇索引是基于B+树的数据结构实现的，它会把数据存储在索引的叶子节点上，叶子节点之间按顺序链接，使得按主键进行搜索时速度最快。
• 如果没有主键，如果按主键搜索，速度是最快的

2.特点

• 数据的逻辑顺序和物理顺序是一致的，通过聚集索引可以直接访问特定行，因此聚集索引的查询速度很快。
• 聚集索引的键值必须是唯一的，不允许重复值存在。
• 当表中的数据发生插入、删除或更新操作时，聚集索引需要进行相应的调整以保持数据的有序性，这可能会对性能产生一定影响。
• 如果表中没有定义聚集索引，那么表的数据存储顺序将按照物理地址来存储。
• 表不建立主键，也会有个隐藏字段是主键，是主键索引
• 主键索引对于按照主键进行查询的性能非常高。

3.语法

数据列不允许重复，不允许为NULL，一个表只能有一个主键

alter table table_name add primary key(column);

4.小结

• 聚集索引适用于经常需要按照某个特定的列或列组进行查询的情况。

• • 例如，在一个订单表中，如果根据订单号频繁地进行查询，那么可以将订单号作为聚集索引，这样可以提高订单查询的效率。

• 需要注意的是，聚集索引的选择需要根据具体的业务需求和数据访问模式进行权衡。

• • 在一些特定情况下，聚集索引可能并不适合或者不符合最佳实践，此时可以考虑使用非聚集索引等其他索引类型。

8）辅助索引（普通索引）

1.概念

• 辅助索引（Non-clustered Index）是关系型数据库中的一种索引类型，与聚集索引相对应。
• 辅助索引的键值并不决定数据在磁盘上的物理存储顺序，而是创建一个额外的数据结构来帮助加快查询速度。一个表可以有多个辅助索引。

2.特点

• 辅助索引的建立并不直接影响数据的物理存储顺序，它是基于聚集索引（如果存在）或表本身的物理存储结构进行构建的。
• 辅助索引的键值可以重复，允许在同一个键值上有多行数据。
• 辅助索引存储着键值和指向实际数据行的指针，通过辅助索引可以快速定位到具体的数据行。
• 辅助索引的维护对数据的插入、删除、更新操作的性能有一定影响，因为每次操作都需要更新辅助索引。

3.语法

允许在定义索引的列中插入重复值和NULL值。

一个表允许多个列创建普通索引

alter table table_name add index index_name(column);

4.小结

• 辅助索引适用于类似于搜索或排序等需要频繁进行的查询操作。

• • 当查询涉及到的列不是聚集索引的键值时，辅助索引可以提供更高效的查询性能。
  • 例如，在一个学生信息表中，如果经常按照学生的姓名进行查询，那么可以在姓名列上创建一个辅助索引，这样可以加速姓名查询的速度。

• 需要注意的是，在设计数据库时，应根据实际情况综合考虑聚集索引和辅助索引的选择。

• • 聚集索引适用于经常以特定顺序扫描整个表或者频繁按照某个列进行范围查找的场景，而辅助索引则适用于单列或多列的等值或范围查找场景。
  • 理想情况下，辅助索引应当能够满足大部分查询需求，从而避免全表扫描。

9）唯一索引（unique）

1.概念

指该索引的所有值都是唯一的，不允许出现重复值

2.语法

alter table table_name add unique key index_name(column);

3.注意事项

• 与普通索引不同的是，如果尝试向包含唯一索引的列中插入重复的值，则会引发错误。
• 唯一索引可以用于确保数据的一致性和完整性，并且可以帮助提高查询性能。

10）组合索引(联合索引)

1.概念

• 组合索引是一种由两个或更多列组成的索引。
• 当查询涉及多个列时，组合索引可以大大提高查询性能。

2.语法

alter table table_name add index index_name(column1, column2, ...);

3.缺点

• 虽然组合索引可以提高查询性能，但是也存在一些缺点。
• 例如，如果添加了不需要的列，或者删除了不需要的列，可能会导致组合索引变得无效。
• 此外，如果创建了太多的组合索引，也可能会增加索引维护的成本。
• 因此，在创建组合索引时需要谨慎考虑。

11）全文索引

1.概念

• 全文索引是一种特殊的索引，它可以用来存储和检索文本数据。
• 全文索引可以包含单词、短语和其他类型的文本内容，并支持模糊匹配和近似匹配。

2.语法

create fulltext index index_name on table_name(colum);

3.注意事项

• 需要注意的是，只有MyISAM和InnoDB存储引擎支持全文索引。
• 此外，创建全文索引可能会增加索引维护的成本，并且可能会降低其他类型的查询性能。
• 因此，在创建全文索引时需要权衡其利弊。

11）前缀索引

1.概念

• 前缀索引是一种特殊的索引，它只存储索引列的一部分，而不是完整的值。
• 前缀索引通常用于处理非常大的列，例如IP地址或邮政编码。
• 在这种情况下，全列索引可能会消耗大量的存储空间，并且可能会导致查询性能下降。
• 前缀索引可以大大减少索引大小，并且可以更快地执行范围查询。

2.语法

alter table table_nmae add index index_name (column(length));
# length参数表示要保留的字符数。

12）覆盖索引

1.概念

• 覆盖索引（Covering Index）是指在关系型数据库中，创建一个辅助索引包含了查询所需要的所有列，从而避免了对主表进行额外的数据检索操作。
• 只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

2.语法

select name from user where name='dream';

3.特点

• 覆盖索引包含了查询所需要的所有列，无需再通过辅助索引定位到实际的数据行。这样，数据库可以直接从索引中获取到查询所需的数据，而不需要再访问主表，提高了查询性能。
• 覆盖索引减少了磁盘I/O操作的次数。相比于使用辅助索引定位到数据行后再读取数据，覆盖索引可以直接从索引中读取所需数据，减少了磁盘读取的次数，从而加快了查询速度。
• 覆盖索引在一些特定的查询场景下非常有效，特别是当查询只需要返回索引包含的列时。例如，如果需要查询一个学生表中的学生姓名和年龄，而这两个列在一个名为"student_idx"的辅助索引上都有覆盖，那么查询时就可以直接使用该覆盖索引，而无需再去读取主表中的其他列，从而提高查询效率。

4.小结

• 根据具体的查询需求，我们可以通过创建适当的覆盖索引来提高查询性能。

• • 需要考虑的因素包括查询的列、查询的条件、表的大小以及对数据插入和更新操作的影响。
  • 创建过多的覆盖索引可能增加了存储空间的占用，并对数据的插入、删除和更新操作性能产生负面影响。

• 因此，在创建覆盖索引时需要权衡索引的选择，避免过度索引化的情况。

13）非覆盖索引

1.概念

• 非覆盖索引是指在数据库中的索引结构中，存储了对应的键值（例如：主键、唯一键、普通索引）以及相应的行的定位信息（如物理存储位置或行标识），但没有包含查询所需的其他列数据。
• 当执行一个查询时，使用非覆盖索引需要通过索引定位到对应的行，并进一步访问主表来获取所需的列数据。
• 与覆盖索引相比，非覆盖索引需要进行额外的查询操作来检索主表中的其他列数据，因此在某些情况下可能会导致性能下降。
• 虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

2.语法

select age from user where name='dream';

3.注意事项

• 列选择性：索引的列选择性是指该列上不同值的数量与总行数之间的比率。当列具有较高的选择性时，非覆盖索引的效果通常会更好。因为高选择性的列能够更快地筛选出满足查询条件的行。
• 查询性能评估：在设计索引时，需要仔细评估查询的频率和性能需求。如果某个查询经常执行，而且对性能要求很高，那么建立合适的非覆盖索引可以提升查询效率。

4.小结

• 总结而言，非覆盖索引是一种常见的索引类型，在特定场景下可以提供高效的筛选能力和降低磁盘I/O操作的优势。
• 但在选择索引类型时，需要综合考虑查询需求、列选择性以及数据表大小等因素，以选择最合适的索引优化方案。

【八】事务隔离机制

1）数据库读现象的本质

• 是数据库在高并发场景下
• 多个同时执行的事务带来的影响

2）数据库三大读现象

1.脏读

概述

• 事务1和事务2并发执行
• 事务1改了数据
• 事务2读取了以后
• 但事务1进行了回滚
• 导致事务2读取的数据有误。

解释

• 脏读是指当一个事务读取了其他事务尚未提交的数据时发生的现象。
• 换句话说，脏读表示读取到的数据并不一定会最终存入数据库中，因此这些数据实际上是不存在的。
• 脏读现象发生在读取到了不一定存在的数据的情况下。

总结

• 脏读指的是读当前事务到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，
• 也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读

2.不可重复读

概述

• 事务1读取了数据
• 事务2修改了数据并且提交了
• 接着事务1再次读取
• 发现两次的数据不相同

解释

• 不可重复读是指在一个事务内多次读取同一批数据，但在事务结束之前，这批数据可能发生了变化，导致读取结果不一致的情况。
• 不可重复读的产生通常是由于在事务A多次读取同一数据的过程中，事务B对数据进行了更新并提交。

总结

• 解释：不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况
• 导致的原因：事务 A 多次读取同一数据，但事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致

3.幻读

概述

• 本质上说是不可重复读的一种现象
• 事务1更改或查询了数据
• 在极短时间内,事务2又插入了一条新的数据
• 导致事务1在接下来的查询中
• 就会发现有⼏列数据是它先前所没有的。

解释

• 幻读的本质在于某一次select操作得到的结果无法支撑后续的业务操作。
• 具体来说，例如在执行select判断某条记录是否存在时，假设该记录不存在，准备插入该记录，但在执行insert时却发现该记录已经存在，导致无法插入，这即是幻读的发生。

总结

• 幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。
• 更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读

3）事务的四大特性

1.原子性（Atomicity）

• 事务是一个不可再分割的单位，要么全部执行成功，要么全部回滚到初始状态，没有中间状态。
• 这意味着如果事务中的任何一步操作失败，整个事务都会被回滚，以确保数据的一致性。

2.一致性（Consistency）

• 事务执行前后，数据库的状态必须保持一致。
• 这意味着事务在执行期间对数据的操作必须满足预定义的规则和完整性约束，以确保数据的有效性和正确性。

3.隔离性（Isolation）

• 多个事务可能同时执行，事务之间应该相互隔离，互不影响。
• 隔离性确保每个事务的操作在逻辑上独立于其他并发事务的操作，从而避免了数据不一致的问题。

4.持久性（Durability）

• 一旦事务提交，对数据库的更改应该是永久性的，即使在系统故障的情况下也不应该丢失。
• 持久性通过将事务记录在持久存储介质（如磁盘）上来实现。

4）隔离的四种级别

• 而隔离性顾名思义指的就是事务彼此之间隔离开

• • 多个事务在同时处理一个数据时彼此之间互相不影响
  • 如如果隔离的不够好就有可能会产生脏读、不可重复度、幻读等读现象

• 隔离性总共分为四种级别，由低到高依次为

• • Read uncommitted（未提交读）
  • Read committed （提交读）
  • Repeatable read（可重复读）
  • Serializable（串行化）

1.Read uncommitted（读未提交）

• 最低的隔离级别，在这个级别下，一个事务可以读取到另一个事务尚未提交的数据，可能导致脏读（Dirty Read）问题，即读取到未经验证的数据。

解决：

• 存在脏读、不可重复读和幻读问题。

2.Read committed（读已提交）

• 在这个级别下，一个事务只能读取到已经提交的数据，避免了脏读问题。
• 但是可能会出现不可重复读（Non-repeatable Read）问题，即同一事务中，两次读取相同的记录可能得到不同的结果，因为其他事务修改了这些记录。

解决：

• 解决了脏读问题，但仍可能出现不可重复读和幻读。

3.Repeatable read（可重复读取）

• 在这个级别下，事务开始读取数据后，其他事务无法修改这些数据，保证了同一个事务内两次读取相同记录的一致性。
• 但是可能会出现幻读（Phantom Read）问题，即同一查询在同一事务中两次执行可能返回不同的结果，因为其他事务插入或删除了符合查询条件的记录。

解决：

• 解决了脏读和不可重复读问题，但仍可能出现幻读。

4.Serializable（串行化）

• 最高级别的隔离级别，要求事务串行执行，事务之间完全隔离，避免了脏读、不可重复读和幻读问题。
• 但是这会牺牲并发性能，因为并发事务被限制为顺序执行。

解决：

• 解决了脏读、不可重复读和幻读问题，但在效率方面有所牺牲。

【九】锁机制

1）概念

• 我们可以通过一个很简单的比喻来理解事务的锁机制。

• 比如同一个办公室的同事们

• • 都想使用打印机打印文件
• • • 如果不加以控制
    • 可能出现两个人同时打印不同的内容在一个文件里
    • 就会引起内容混乱。
• • 于是，我们就引入了锁的概念
• • • 当有并发的多个事务同时操作同一份数据时
    • 只有“抢到”了锁的事务
    • 才能真正去操作数据
    • 使得数据的安全性得到保证。

2）数据库锁的分类

1.按粒度分

• 数据库的锁按粒度分为

• • 行级锁
  • 表级锁
  • 页级锁

行级锁
1.概念
● ⾏级锁是Mysql中锁定粒度最细的⼀种锁
  ○ 表示只针对当前操作的⾏进⾏加锁。
● ⾏级锁能⼤⼤减少数据库操作的冲突。
  ○ 其加锁粒度最⼩，但加锁的开销也最⼤。
● ⾏级锁分为共享锁和排他锁。

2.特点
● 开销⼤，加锁慢；
● 会出现死锁；
● 锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼

3.⾏级锁解释
● 由于数据库的库和表都是事先建好的
  ○ 所以我们针对数据库的操作一般都是针对记录。
  ○ 而对记录进行的四种操作（增删改查）
  ○ 我们可以分为两类 
    ■ 增删改属于读操作
    ■ 而查询属于写操作。
● 写操作默认就会加锁，且加的是互斥锁
  ○ 很容易理解，在进行写行为的时候一定是必须“排他”的。
  ○ 读操作默认不受任何锁影响
  ○ 但是互斥锁和共享锁都可以加。
● 读操作加互斥锁 for update;
● 读操作加共享锁 lock in share mode;
提示：关于共享锁和互斥锁，我们将在下一小节更详细地讲述

4.行级锁锁的是索引
● 行级锁锁的是索引 
  ○ 命中索引以后才会锁行
  ○ 如果没有命中索引
  ○ 会把整张表都锁起来。
● 命中主键索引就锁定这条语句命中的主键索引 
  ○ 命中辅助索引就会先锁定这条辅助索引
  ○ 再锁定相关的主键索引
  ○ 考虑到性能，innodb默认支持行级锁
  ○ 但是只有在命中索引的情况下才锁行，
● 否则锁住所有行 
  ○ 本质还是行锁
  ○ 但是此刻相当于锁表了
  
  5.行级锁的三种算法
● 1、Record lock
● 2、Gap lock
● 3、Next-key lock
● 其中 Next-key lock 为MySQL默认的锁机制
  ○ 相当于另外两种锁的功能的整合
  ○ 并能够解决幻读问题。
● 提示：
  ○ 在RR事务隔离机制下，才会锁间隙
  ○ 而RR机制是mysql的默认事务隔离机制。
  ○ 所以，在默认情况下，其实innodb存储引擎锁的是行以及间隙.
● 我们可以用一个实验来验证上述关于行锁的结论

2.按级别分

• 数据库的锁按级别分为

• • 共享锁，排他锁，共享锁
  • 又被称作读锁，s锁
• • • 含义是多个事务共享同一把锁
    • 其中每个事务都能访问到数据
    • 但是没有办法进行修改。

注意：

• 如果事务T对数据A加上共享锁后
• 则其他事务只能对A再加共享锁或不加锁（在其他事务里一定不能再加排他锁但是在事务T自己里面是可以加的）

排他锁又被称作互斥锁，写锁，x锁

• 含义是如果有一个事务获取了一个数据的排他锁
• 那么其它的事务都无法再次获得该数据的任何锁了
• 但是排他锁支持文件读取，修改和写入。

3.按使用方式分

• 数据库的锁按使用方式分为

• • 悲观锁、乐观锁

悲观锁（Pessimistic Locking）
● 顾名思义指的是对外界将要进行的数据修改操作持悲观态度
● 因此，在整个数据处理过程中，将数据处于锁定状态。
● 现在由于互联网的高并发架构，即使加上悲观锁也无法保证数据不被外界修改，因此不推荐使用。

乐观锁（Optimistic Locking）
● 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突
● 所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测
● 如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
● 通常乐观锁的实现是在表中加一个字段（可能是时间戳或版本号）
● 在写入的时候会查询一下版本号 
  ○ 如果版本号没有改变，就写入数据库并同时改变版本号。
● 从本质上来说，乐观锁并没有加锁 
  ○ 所以效率会大大提升
  ○ 但也有一定的缺陷，就是可能导致一部分任务的写入失败。

【十】数据库的三大范式

1）第一范式

• 第一范式，我们通常也叫 1NF

• 第一范式要求我们必须遵守原子性

• • 即数据库表的每一列都是不可分割
• • • 每列的值具有原子性，不可再分割
    • 每个字段的值都只能是单一值

2）第二范式

• 第二范式，我们通常也叫 2NF

  • 第二范式(2NF)是在第一范式(1NF)的基础上建立起来得
  • 满足第二范式(2NF)必须先满足第一范式(1NF)

• 第一范式要求我们必须遵守原子性

• 第二范式要求表中的所有列，其数据依赖于主键

  • 即一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系
    • 如果表是单主键，那么主键以外的列必须完全依赖于主键，其它列需要跟主键有关系
    • 如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分

3）第三范式

• 第三范式，我们通常也叫 3NF

  • 第三范式(3NF)是在第二范式(2NF)的基础上建立起来得
  • 满足第三范式(3NF)必须先满足第二范式(2NF)

• 第一范式要求我们必须遵守原子性

• 第二范式要求表中的所有列，其数据依赖于主键

  • 即一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系
    • 如果表是单主键，那么主键以外的列必须完全依赖于主键，其它列需要跟主键有关系
    • 如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分

• 第三范式要求表中每一列数据不能与主键之外的字段有直接关系

  • 表中的非主键列必须和主键直接相关而不能间接相关
  • 非主键列之间不能相关依赖，不存在传递依赖

标签：事务,进阶,37,存储,查询,索引,MySQL,数据,主键
From： https://www.cnblogs.com/Mist-/p/18236508