Mysql进阶篇下
触发器
- MySQL触发器是一种数据库对象，用于在特定表上定义某些操作的自动响应。
- 例如，在向一个表中插入新数据时，触发器可以自动更新另一个表或者发送一条通知邮件。
| 属性 | 说明 |
|---|---|
| create trigger 触发名 before/after 触发事件(这个事件可以写insert update delete) on 表名 for each row 执行语句; | 一个执行语句的触发器 |
| create trigger 触发名 before/after 触发事件 on 表名 for each row begin 执行语句列表 end; | 多个执行语句的触发器 |
| drop trigger 触发器名; | 删除触发器 |
-- 实现:搞一个用户表用来存储账号密码 当添加一条记录的时候 会自动触发插入 更新 删除等记录
create database mydb4;
use mydb4;
-- 用户表
create table user(
uid int primary key,
username varchar(10) not null,
password varchar(10) not null
);
-- 日志表
create table user_log(
id int primary key auto_increment,
time timestamp,
log_txt varchar(255)
);
-- insert 触发器
create trigger trigger_test after insert -- create trigger 触发器的名字 after 触发事件
on user for each row -- on 表名 for each row
insert into user_log values(null,now(),'插入了一条数据'); -- 对log表进行插入数据
-- 用户表开始添加数据
insert into user values(1,'admin','666');
-- 查看log表
select * from user_log;
+----+---------------------+-----------------------+
| id | time | log_txt |
+----+---------------------+-----------------------+
| 1 | 2023-04-21 18:09:25 | 插入了一条数据 |
+----+---------------------+-----------------------+
-- 不管是更新插入删除 alter和before的用法都是一样的 只要不加条件。都是在对一个表执行成功后 另一个表才会有日志信息出现
-- update 多个语句触发器
create trigger trigger_test2 before update
on user for each row
begin
insert into user_log values(null,now(),'有用户的信息被修改');
end;
-- 对用户表的密码进行修改 修改一次触发器触发一次 修改多次触发器触发多次
update user set password = '666666' where uid = 1;
-- 查看log表内容
select * from user_log;
+----+---------------------+-----------------------------+
| id | time | log_txt |
+----+---------------------+-----------------------------+
| 1 | 2023-04-21 18:09:25 | 插入了一条数据 |
| 2 | 2023-04-21 18:20:28 | 有用户的信息被修改 |
+----+---------------------+-----------------------------+触发器中的new和old
- new 新的 之后的
- old 旧的 之前的 把旧衣服扔了吧(把之前的衣服扔了吧)
-- insert new 触发器
create trigger trigger_test after insert
on user for each row
insert into user_log values(null,now(),concat_ws(',','有用户信息被新增，新增之后为:',new.uid,new.username,new.password));
-- 新增一个用户
insert into user values(2,'admin666','666666');
select * from user_log;
+----+---------------------+---------------------------------------------------------------+
| id | time | log_txt |
+----+---------------------+---------------------------------------------------------------+
| 1 | 2023-04-21 18:09:25 | 插入了一条数据 |
| 2 | 2023-04-21 18:20:28 | 有用户的信息被修改 |
| 5 | 2023-04-21 18:50:50 | 有用户信息被新增，新增之后为:,2,admin666,666666 |
+----+---------------------+---------------------------------------------------------------+
-- 删除触发器 触发器新建多了 每一次修改或者更新都会有好几个记录被添加到日志文件去 所以在测试的时候就删除触发器重新弄
drop trigger trigger_test;
-- update 触发器
create trigger trigger_test after update
on user for each row
insert into user_log values(null,now(),concat_ws(',','有用户信息被修改，修改之前为:',old.uid,old.username,old.password));
-- 修改数据
update user set password = '999999' where uid = 2;
select * from user_log;
+----+---------------------+---------------------------------------------------------------+
| id | time | log_txt |
+----+---------------------+---------------------------------------------------------------+
| 1 | 2023-04-21 18:09:25 | 插入了一条数据 |
| 2 | 2023-04-21 18:20:28 | 有用户的信息被修改 |
| 5 | 2023-04-21 18:50:50 | 有用户信息被新增，新增之后为:,2,admin666,666666 |
| 6 | 2023-04-21 19:38:47 | 有用户信息被修改，修改之前为:,2,admin666,666666 |
+----+---------------------+---------------------------------------------------------------+
-- delete 触发器
create trigger trigger_test after delete
on user for each row
insert into user_log values(null,now(),concat_ws(',','有用户信息被删除，删除之前为:',old.uid,old.username,old.password));
delete from user where uid = 2;
select * from user_log;
+----+---------------------+---------------------------------------------------------------+
| id | time | log_txt |
+----+---------------------+---------------------------------------------------------------+
| 1 | 2023-04-21 18:09:25 | 插入了一条数据 |
| 2 | 2023-04-21 18:20:28 | 有用户的信息被修改 |
| 5 | 2023-04-21 18:50:50 | 有用户信息被新增，新增之后为:,2,admin666,666666 |
| 6 | 2023-04-21 19:38:47 | 有用户信息被修改，修改之前为:,2,admin666,666666 |
| 8 | 2023-04-21 19:47:24 | 有用户信息被删除，删除之前为:,2,admin666,000000 |
+----+---------------------+---------------------------------------------------------------+- Mysql中的触发器不能表进行insert,update,delete操作 以免递归循环触发
- 确保触发器中的代码是高效且不会产生死锁或阻塞。
- 频繁的增删改操作不要使用触发器 会导致性能下降也会消耗资源
- 触发器应该仅在必要时使用，因为它们会增加数据库的负担。
索引
- 在 MySQL 中，索引就像是一本书的目录，它提供了一个快速查找数据的方式。如果一张表没有索引，MySQL 将会扫描整张表来找到所需的数据，这会消耗大量的时间和资源。而有了索引，MySQL 可以更快地定位到指定数据的位置，从而加快查询速度
索引分类
- BTree索引：最常见的索引类型，适用于大多数数据类型，包括数字、字符串和日期等。
- 哈希索引：适用于精确查找，只能用于等值比较（=），无法用于范围查找。
- 全文索引：适用于长文本字段，例如文章或博客内容，在搜索时提供更好的性能和准确性。
- 空间索引：主要用于地理空间数据，可以对坐标点和多边形等空间对象进行高效查询。
- 组合索引：将多个列的值组合在一起作为索引键，可用于多列范围查询上，效果较单一的索引更好。
- 前缀索引：只对列的前几个字符建立索引，可以节省索引存储空间，但会导致查询性能下降。
单列索引(普通索引)
- 一个索引只包含单个列，一个表可以有多个单列索引（也就是说可以给表的一列或者一个字段设置为单列索引）
创建单列索引(普通索引)
| 属性 | 说明 |
|---|---|
| index 索引名(字段) | 创建普通索引 |
create database mydb5;
use mydb5;
-- 创建学生表 最后一行为给name字段添加一个索引
create table student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
index index_name(name) -- 这里索引创建完成后 默认是btree索引
);
-- 查询name字段 只有在大批量数据的情况下查询速度才会快
select * from student where name = '张三';
-- 方式二创建索引
create index index_gender on student(gender);
-- 方式三 修改表结构创建索引
alter table student add index index_age(age);查看单列索引(普通索引)
-- 前面都是一样的 后面需要指定一个数据库的名字 查看数据库中的索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';
-- 查看表中索引 mydb5为库名 student为表名
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';
-- 查看表中所有的索引
show index from student;删除单列索引(普通索引)
-- 删除索引
drop index 索引名 on 表名
drop index index_name on student;
alter table 表名 drop index 索引名
alter table student drop index index_gender;唯一索引
- 唯一索引是一种索引类型，它要求所有数据行的索引列的值必须唯一，可以包含NULL值。唯一索引可以用来加速搜索和排序操作。
- 唯一约束是一种表约束，它要求所有数据行的约束列的值必须唯一，不允许包含NULL值。唯一约束可以保证数据完整性，避免重复数据的出现。
| 属性 | 说明 |
|---|---|
| unique 索引名(字段) | 创建唯一索引 |
-- 创建学生表 最后一行为给phone_num字段添加一个索引
create table student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
unique index_phone(phone_num) -- 这里索引创建完成后 默认是btree索引
);
-- 方式二 create unique index 索引名 on 表名(字段)
create unique index index_card on student(card_id);
-- 方式三修改表结构添加索引 alter table 表名 add unique 索引名(字段);
alter table student add unique index_phone_num(phone_num);主键索引
- MySQL 在创建主键约束时，会自动生成一个主键索引来保证唯一性和快速查找。这个主键索引可以加速对表的查询和操作。
索引特点总结
- 提高查询性能：索引可以加速数据检索，特别是在大型数据集的情况下。
- 加快排序操作：如果使用索引来排序结果，那么MySQL可以使用索引来避免对表进行排序。
- 约束唯一性：索引可以强制保证表中某列的唯一性。
- 优化连接操作：当多个表连接时，使用索引可以提高连接的效率。
然而，索引也有缺点：
- 占用存储空间：索引需要额外的存储空间，这可能会导致数据库规模变大。
- 减慢写操作：添加、更新和删除数据时，索引需要更新，这可能会导致写操作变慢。
- 增加维护成本：索引需要定期维护，否则可能会导致性能下降。
存储引擎
- MySQL中的存储引擎是用于管理数据存储和检索的核心组件。
- InnoDB：支持事务处理，行级锁定机制，可靠性高，适合大批量的增删改操作和高并发读取操作。
- MyISAM：不支持事务处理，表级锁定机制，适合于插入、更新较少但频繁的数据，读取远多于写入的应用场景。
- Memory：基于内存的存储引擎，快速高效，但数据只能存在于内存中，适合于小型缓存、临时表等。
- CSV：将数据以CSV格式存储在文本文件中，数据可读性好但不支持索引，适合于一些简单查询的场景。
- Archive：存储大量历史数据的场景，压缩比较高，但不支持修改和删除操作。
| 功能 | MylSAM | MEMORY | InnoDB |
|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB |
| 支持事务 | No | No | Yes |
| 支持全文索引 | Yes | No | No |
| 支持B树索引 | Yes | Yes | Yes |
| 支持哈希索引 | No | Yes | No |
| 支持集群索引 | No | No | Yes |
| 支持数据索引 | No | Yes | Yes |
| 支持数据压缩 | Yes | No | No |
| 空间使用率 | 低 | N/A | 高 |
| 支持外键 | No | No | Yes |
事务(transaction)
- 维护数据库的完整性，要么全部执行成功，要么全部回滚。
- 事务通常有四个特性，即ACID
- 原子性（Atomicity）事务中的所有操作要么全部完成，要么全部不完成
- 一致性（Consistency）事务执行前后，数据库的状态必须保持一致
- 隔离性（Isolation）多个事务并发执行时，每个事务都应该与其他事务隔离开来，互相之间不能干扰
- 持久性（Durability）事务完成后，对数据库的修改应该永久保存下来，即使系统出现故障也不会丢
| 属性 | 说明 |
|---|---|
| begin | 开启事务 |
| commit | 提交事务 |
| rollback | 回滚事务 |
-- 创建一个银行账户表
create table account(
id int primary key,
name varchar(20),
money double
);
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);
select * from account;
+----+----------+-------+
| id | name | money |
+----+----------+-------+
| 1 | zhangsan | 1000 |
| 2 | lisi | 1000 |
+----+----------+-------+
-- 设置Mysql的事务为手动提交 utocommit = 0 1是自动提交
select @@autocommit;
set autocommit = 0;
-- 开始事务
begin
-- 模拟转账操作
update account set money = money -200 where name = 'zhangsan';
update account set money = money +200 where name = 'lisi';
-- 回滚事务 比如第一次插入了两个数据 开启事务 无论做何修改提交回滚事务会还原默认的两条数据 相当于撤回之前的操作
rollback;
-- 提交事务 提交事务后所有操作将生效
commit;锁机制
| 存储引擎 | 表级锁 | 行级锁 |
|---|---|---|
| MyLSAM | 支持 | 不支持 |
| InnoDB | 支持 | 支持 |
| MEMORY | 支持 | 不支持 |
| BDB | 支持 | 不支持 |
- MySQL中的锁可以分为两类：共享锁和排他锁。
- 共享锁（Shared lock）又称读锁，允许多个事务同时持有该锁，并发读取同一资源，但任何一个事务持有了共享锁，就阻止其他事务获得独占锁。
- 排他锁（Exclusive lock）又称写锁，只允许一个事务持有该锁，用于对资源进行修改或删除操作，其他事务无法同时持有共享锁或排他锁，此时需要等待持有排他锁的事务释放锁之后才能继续进行操作。
- 锁还可以根据作用范围分为行级锁和表级锁。
- 行级锁是在数据行级别上进行加锁，只锁定需要访问的部分数据，其他线程可以访问未加锁的数据行。
- 表级锁是在整个表上进行加锁，锁定整张表，其他线程不能对该表进行任何读写操作。（不会造成死锁）
| 属性 | 说明 |
|---|---|
| lock table 表名 read ; | 读锁 |
| lock table 表名 write; | 写锁 |
| unlock tables; | 释放锁 |
create database mydb6;
use mydb6;
-- 创建一个学生表 并设置存储引擎为myisam
create table student(
id int primary key,
name varchar(20),
book varchar(20)
)engine=myisam;
insert into student values(1,'张三','红楼梦'),(2,'李四','水浒传');
select * from student;
+----+--------+-----------+
| id | name | book |
+----+--------+-----------+
| 1 | 张三 | 红楼梦 |
| 2 | 李四 | 水浒传 |
+----+--------+-----------+
-- 给学生表添加可读锁
lock table student read;
select * from student;
+----+--------+-----------+
| id | name | book |
+----+--------+-----------+
| 1 | 张三 | 红楼梦 |
| 2 | 李四 | 水浒传 |
+----+--------+-----------+
-- 报错
update student set name = '王五';
-- 释放锁
unlock tables;
-- 如果两个终端同时对同一张表加锁并尝试访问其他数据，则需要先释放该表的锁，然后才能执行其他操作
-- 给学生表添加写锁
lock table student write;
update student set name = '王五' where id = 1;
-- 在这里 当另一个终端进行访问学生表数据的时候就会变成挂起的状态 出现等待 若想让第二个终端访问此表 需要释放锁
-- 释放锁
unlock tables;InnoDB引擎行锁
- InnoDB和MyISAM是MySQL数据库中两种常见的存储引擎。它们之间的区别在于：
- 事务支持：InnoDB支持事务处理，而MyISAM不支持。
- 锁定机制：InnoDB使用行级锁定，而MyISAM使用表级锁定。在高并发环境下，InnoDB可以更好地支持并发访问，而MyISAM可能会出现锁定冲突问题。
- 数据完整性：InnoDB支持外键约束和回滚操作，而MyISAM不支持。
- 性能：在读取大量数据时，MyISAM比InnoDB更快。但是，InnoDB在处理大量并发查询时性能更好。
InnoDB实现了以下两种类型的行锁
- 共享锁：又为读锁。多个事务对同一数据可以共享一把锁，都能访问到数据，但是只能读不可修改。
- 排他锁：又为写锁。锁住不可共存，可以对数据读取和修改，其他事务无法读取或修改被锁定的资源。直到持有该锁的事务释放它。
- InnoDB实现对表进行加共享锁 还可以查询其他表数据 但MyISAM不可以
- InnoDB 存储引擎在默认情况下会自动使用行级锁来保护数据，并且需要结合事务一起使用。
create table innodb_lock(
id int(11),
name varchar(20),
sex varchar(1)
);
insert into innodb_lock values (1,'100','1'),
(3,'3','1'),
(4,'400','0'),
(5,'500','1'),
(6,'600','0'),
(7,'700','0'),
(8,'800','1'),
(9,'900','1'),
(1,'200','0');
-- 创建两个索引
create index index_id on innodb_lock(id);
create index index_name on innodb_lock(name);
-- 模拟两个终端同时的操作
-- 修改事务为手动提交
set autocommit = 0;
-- 开启事务
begin;
-- 修改数据
update innodb_lock set sex = '2' where id = 1;
-- 当另一个终端修改以上数据的时候会出现挂起的情况 这个是事务的隔离特性 在默认情况下innodb也会自动使用行锁保护数据
-- 因为行级锁会将被修改的行加锁，其他事务需要等待该锁释放后才能进行操作 以确保数据的一致性。
-- 提交事务
commit;
-- 终端一
update innodb_lock set sex = '2' where id = 4;
-- 终端二
update innodb_lock set sex = '2' where id = 9;
-- 如果修改的不是同一个就不会出现错误Mysql优化
1.索引优化（全值匹配）
创建组合索引 用的时候全部用上 也就是全值匹配
- create index index_one on student(name,id,address); 创建组合索引
- select * from student where name = '张三' and id='4' and address = '北京市';
最左前缀法则 查询从索引的最左前列开始 并且不跳过索引中的列
- select * from student where name = '张三' ;
- select * from student where name = '张三' and id = '3';
- select * from student where name = '张三' and address = '河北省'; 低效率
范围查询的右边不可以使用索引 不然会导致索引失效 效率变低
- select * from student where name = '张三' and id > '3' and address = '北京市'; 前面两个用到索引但是address没有用到索引
不可在索引上进行运算操作，索引会失效
- select * from student where substring(name,3,) = '科技';
字符串不加单引号，造成索引失效
- select * from student where name = '张三' and id = 1;
- 在索引中使用or进行条件查询 索引会失效
- 在索引中使用like进行模糊查询以%开头会失效
在查询数据的时候 全表查询扫描比索引还快 那不推荐使用索引 一般这种情况由数据本身而定
- create index index_one on student(address); 创建单列索引
- select * from student where address = '北京市'; 数据重复较多 不会使用索引
- select * from student where address = '河北省'; 数据只有一个 会使用索引
is null、is not null 有时有效 有时索引失效（从多数找少数 索引有效、从少数找多数 索引无效）
- create index index_one on student(name); 创建单列索引 当name字段的值都为字符串类型 没有null值
- select * from student where name is null; 判断name字段为null 有效
- select * from student where name is not null; 判断name字段不是null 索引无效
2.SQL优化
- 尽量避免使用子查询
用in替换or
- select * from student where id = 1 or id = 10; 低效
- select * from student where id in(10,12,15);
limit分页查询优化
- 随着表数据量的增加，直接用分页查询会越来越慢 优化方法应设置一个主键并对主键添加索引 (主键约束默认会有索引)
- select * from student a,(select id from student order by id limit 90000,10) b where a.id = b.id; 对其进行排序在使用子查询
order by 排序优化
- 对结果没有排序要求 尽量少使用排序。
- 排序的情况下建议配合主键带有索引查询速度相对来说较快一些
数据插入优化
- insert into student values(1,2,3),(4,5,6); 或者用事务在多行插入后提交事务
3.优先级总结
system > const > eq_ref > ref > range > index > all
explain select * from student; -- type字段为all就是最低
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+