Mysql进阶篇下

触发器

• MySQL触发器是一种数据库对象，用于在特定表上定义某些操作的自动响应。
• 例如，在向一个表中插入新数据时，触发器可以自动更新另一个表或者发送一条通知邮件。

-- 实现:搞一个用户表用来存储账号密码 当添加一条记录的时候 会自动触发插入 更新 删除等记录 

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索引：最常见的索引类型，适用于大多数数据类型，包括数字、字符串和日期等。
• 哈希索引：适用于精确查找，只能用于等值比较（=），无法用于范围查找。
• 全文索引：适用于长文本字段，例如文章或博客内容，在搜索时提供更好的性能和准确性。
• 空间索引：主要用于地理空间数据，可以对坐标点和多边形等空间对象进行高效查询。
• 组合索引：将多个列的值组合在一起作为索引键，可用于多列范围查询上，效果较单一的索引更好。
• 前缀索引：只对列的前几个字符建立索引，可以节省索引存储空间，但会导致查询性能下降。

单列索引(普通索引)

• 一个索引只包含单个列，一个表可以有多个单列索引（也就是说可以给表的一列或者一个字段设置为单列索引）

创建单列索引(普通索引)

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值。唯一约束可以保证数据完整性，避免重复数据的出现。

-- 创建学生表 最后一行为给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：存储大量历史数据的场景，压缩比较高，但不支持修改和删除操作。

事务(transaction)

• 维护数据库的完整性，要么全部执行成功，要么全部回滚。
• 事务通常有四个特性，即ACID
• 原子性（Atomicity）事务中的所有操作要么全部完成，要么全部不完成
• 一致性（Consistency）事务执行前后，数据库的状态必须保持一致
• 隔离性（Isolation）多个事务并发执行时，每个事务都应该与其他事务隔离开来，互相之间不能干扰
• 持久性（Durability）事务完成后，对数据库的修改应该永久保存下来，即使系统出现故障也不会丢

 -- 创建一个银行账户表
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;

锁机制

• MySQL中的锁可以分为两类：共享锁和排他锁。
• 共享锁（Shared lock）又称读锁，允许多个事务同时持有该锁，并发读取同一资源，但任何一个事务持有了共享锁，就阻止其他事务获得独占锁。
• 排他锁（Exclusive lock）又称写锁，只允许一个事务持有该锁，用于对资源进行修改或删除操作，其他事务无法同时持有共享锁或排他锁，此时需要等待持有排他锁的事务释放锁之后才能继续进行操作。
• 锁还可以根据作用范围分为行级锁和表级锁。
• 行级锁是在数据行级别上进行加锁，只锁定需要访问的部分数据，其他线程可以访问未加锁的数据行。
• 表级锁是在整个表上进行加锁，锁定整张表，其他线程不能对该表进行任何读写操作。（不会造成死锁）

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  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+