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中的存储引擎是用于管理数据存储和检索的核心组件。
  1. InnoDB:支持事务处理,行级锁定机制,可靠性高,适合大批量的增删改操作和高并发读取操作。
  2. MyISAM:不支持事务处理,表级锁定机制,适合于插入、更新较少但频繁的数据,读取远多于写入的应用场景。
  3. Memory:基于内存的存储引擎,快速高效,但数据只能存在于内存中,适合于小型缓存、临时表等。
  4. CSV:将数据以CSV格式存储在文本文件中,数据可读性好但不支持索引,适合于一些简单查询的场景。
  5. Archive:存储大量历史数据的场景,压缩比较高,但不支持修改和删除操作。
功能MylSAMMEMORYInnoDB
存储限制256TBRAM64TB
支持事务NoNoYes
支持全文索引YesNoNo
支持B树索引YesYesYes
支持哈希索引NoYesNo
支持集群索引NoNoYes
支持数据索引NoYesYes
支持数据压缩YesNoNo
空间使用率N/A
支持外键NoNoYes

事务(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.索引优化(全值匹配)

  1. 创建组合索引 用的时候全部用上 也就是全值匹配

    1. create index index_one on student(name,id,address);    创建组合索引
    2. select * from student where name = '张三' and id='4' and address = '北京市'; 
  2. 最左前缀法则 查询从索引的最左前列开始 并且不跳过索引中的列

    1. select * from student where name = '张三' ;
    2. select * from student where name = '张三'  and id = '3';
    3. select * from student where name = '张三'  and address = '河北省';   低效率 
  3. 范围查询的右边不可以使用索引 不然会导致索引失效 效率变低

    1. select * from student where name = '张三' and id  > '3' and address = '北京市';   前面两个用到索引但是address没有用到索引 
    2. 不可在索引上进行运算操作,索引会失效

      1. select * from student where substring(name,3,) = '科技';
    3. 字符串不加单引号,造成索引失效

      1. select * from student where name = '张三' and id = 1;
  4. 在索引中使用or进行条件查询 索引会失效
  5. 在索引中使用like进行模糊查询以%开头会失效
  6. 在查询数据的时候 全表查询扫描比索引还快 那不推荐使用索引 一般这种情况由数据本身而定

    1. create index index_one on student(address); 创建单列索引
    2. select * from student where address = '北京市'; 数据重复较多 不会使用索引
    3. select * from student where address = '河北省'; 数据只有一个 会使用索引
  7. is null、is not null 有时有效 有时索引失效(从多数找少数 索引有效、从少数找多数 索引无效)

    1. create index index_one on student(name); 创建单列索引 当name字段的值都为字符串类型 没有null值
    2. select * from student where name is null; 判断name字段为null 有效
    3. select * from student where name is not null; 判断name字段不是null 索引无效

2.SQL优化

  1. 尽量避免使用子查询
  2. in替换or

    1. select * from student where id = 1 or id = 10; 低效
    2. select * from student where id in(10,12,15);
  3. limit分页查询优化

    1. 随着表数据量的增加,直接用分页查询会越来越慢 优化方法应设置一个主键并对主键添加索引 (主键约束默认会有索引)
    2. select * from student a,(select id from student order by id limit 90000,10) b where a.id = b.id; 对其进行排序在使用子查询
  4. order by 排序优化

    1. 对结果没有排序要求 尽量少使用排序。
    2. 排序的情况下建议配合主键带有索引查询速度相对来说较快一些
  5. 数据插入优化

    1. 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  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
最后修改:2023 年 04 月 25 日
如果觉得我的文章对你有用,请随意赞赏