MySQL 基础知识-04

目录

一、MySQL的触发器

二、MySQL的索引（高效查找）

三、MySQL的存储引擎

四、MySQL的事务

---

一、MySQL的触发器

1、介绍

触发器就是一种特殊的存储过程，触发器与存储过程一样，都是一推能够实现某种特定功能的sql语句的封装，但是触发器无需调用，当对数据库表中的数据进行DML操作时会自动的触发，自动执行。

在MySQL中，只有执行insert、delete、undate操作时才能触发触发器的执行。触发器的这种特性可以协助应用在数据库端，确保了数据的完整性，日志记录，数据校验等。触发器只支持行级触发。

案例1：比如现在有两个表：A表（用户表）和日志表，我想要实现对用户表进行插入员工时，日志表自动显示在什么时间什么人对用户表进行了插入操作，当对用户表进行删除员工时，日志表自动更新什么时间什么人对用户表进行了删除操作，像这种我对用户表进行操做（增删改），日志表实现自动更新日志，需要使用触发器（对某张表的修改会影响另一张表）；当对用户表进行增删改操作时，触发器自动触发，自动执行触发器封装的sql语句实现日志表的记录。

案例2：比如有两张表：A表（粉丝表），B表（大博主表），当A表新关注（增加了一条数据）了大博主B时也需要增加一条数据（大博主B多了一个粉丝），保证数据的完整性。

2、特性

1）什么条件触发：I、D、U

2）什么时候触发：在增删改之前或之后

3）触发频率的定义：针对每一行执行还是多行执行

4）触发器定义在表上还是附着在表上

3、创建及删除触发器

 （1）数据准备-自动记录日志

# 用户表
create table user(
	uid int primary key,
	username varchar(50) not null,
	password varchar(50) not null
);

# 用户信息操作日志表
create table user_log(
	id int primary key,
	time timestamp,  # timestamp类型可以根据时区自动获取当前的时间
	log_text varchar(255)
);

（2）实现

需求1：当user表添加一行数据，则会自动在user_log表中添加日志记录

# 创建触发器
create trigger trigger_test01 after insert
on user for each row
insert into user_log(time, log_text) values(now(),'有新用户添加');

# 触发触发器
insert into user values(1001,'张三',123456);

需求2：当user表数据被修改时，则会自动在user_log表中添加日志记录

# 创建触发器
delimiter $$
create trigger trigger_test02 before update
on user for each row
begin
	insert into user_log(time, log_text) values(now(),'有用户信息被修改');
end $$
delimiter ;
# 触发触发器
update user set password = 654321 where uid = 1001;
update user set password = 111111 where uid = 1001;

（3）删除触发器

drop  trigger  [if  exists]  触发器名；

4、NEW与OLD关键字

上文所建立的触发器，如果被触发只能输出“信息被添加/修改”，目前我们的需求是，除了输出“信息被添加/修改”还要输出具体添加/修改的内容（例如显示“有新用户添加:1001 张三 123456”），想要实现这一过程必须在触发器内部获取添加/修改/删除的数据。

 NEW.列名 ： 自动获取插入的数据中的某列

# insert型触发器new

create trigger trigger_test03 after insert
on user for each row
insert into user_log(time, log_text) values(now(),concat('有新用户添加:',new.uid,':',new.username,':',new.password));

# 触发
insert into user values(1002,'李四',123456);

# undate型触发器new,old

create trigger trigger_test04 after update
on user for each row
insert into user_log(time, log_text) values(now(),concat_ws('-','有信息被修改','原数据为',old.uid,old.username,old.password,'新数据为',new.uid,new.username,new.password));

# 触发  修改1002的密码为888888 原密码123456
update user set password = 888888 where uid = 1002;

# delete型触发器old

create trigger trigger_test05 before delete
on user for each row
insert into user_log(time, log_text) values(now(),concat_ws('-','有信息被删除','删除的数据为',old.uid,old.username,old.password));

# 触发  删除1001
delete from user where uid = 1001;

5、其它操作及注意事项

show  triggers；  ： 查看触发器

在触发器中不能对本表进行DML操作，以免发生死循环

触发器在运行时十分耗时，所以尽量少使用触发器，尤其是对那些频繁进行DML操作的表切记不要使用触发器。

二、MySQL的索引（高效查找）

索引用于快速找出在某个列中的有一特定值的行，不使用索引MySQL必须从第一条记录开始读整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，节省了大量的时间。

1、索引的分类

（1）按照结构分类：Hash索引、B+Tree索引(默认)

Hash索引的思想：给定表中的一列数据，将该列每行的值输入进一个特定的函数f(x)计算得到其对应的hash值（hashCode），这个hashCode就是所建立的索引；在查询时，先获得要查询行的索引值（hashCode），然后根据hashCode直接定位。

假设给定的这一列数据的值完全不重复（比如主键），即使值不同，在hash值计算过程中也可能出现相同的hashCode，这就做hash冲突，我们可以设计一个足够好的f(x)函数，尽可能的使各行的hashCode值不同，即使存在hash冲突（比如上图我们为id列建立索引，004与006产生的hashCode值相同为123，目前我们想查询id=006的行，这时先将006输入f(x)得到其hash值123，通过hash值查出004这一行，此时以该行为开始继续向下查询005（不是），006（是，查到了），即使存在hash冲突我们也只需要查3行内容，并没有查整张表），也可以减少对整张表的查询。

在MySQL中一般使用B+Tree索引，因为hash索引是通过字段的值计算出一个固定的hash值，它本身没有范围的概念，无法进行范围查找，比如:

select * from student where sid > 40;  # 无法使用hash索引

 B+Tree思想：给定表中的一列数据，将该列每行的值输入进一个特定的Tree结构中计算得到其对应的索引值；在查询时，先获得要查询行的索引值，然后直接定位。

例如：查找id = 007的信息，如果没有索引系统会一行一行的查找，最坏的情况就是对整张表进行查找，使用了B+Tree索引后将007输入到系统构建的Tree结构中，首先查询根节点，007>005，则去右子树中查找，007>006，继续去右子树中查找，最后找到了007的索引值，然后根据此索引值直接找到结果，可以看出有B+Tree索引只查了两次就找到了对应的行，极大的加快查找速度。

（2）按照功能分类

 2、单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引

（1）普通索引

普通索引没有任何限制，允许在定义索引的列中插入重复值和空值。

创建索引

方式一：在创建表时直接指定

方式二：在创表之后，直接创建索引

create   index   索引名   on   表名(字段名);

方式三：在创表之后，通过修改表结构添加索引

alter   table   表名    add   index   索引名(字段名)；

查看索引

查看数据库所有索引

select   *   from   mysql.`innodb_index_stats`   a  where  a.`database_name`  =  '数据库名'; 

 说明：n_diff_pfx01表示该字段去重之后的个数

            n_diff_pfx02 普通索引在创建后会自动与主键放在一起统计去重之后的个数

            n_leaf_pages  表示该索引的叶子节点实际占用多少页面 

            size 表示该索引共占用多少页面

查看表中的所有索引

select  *  from  mysql.`innodb_index_stats`  a  where  a.`database_name` = '数据库名'  and  a.table_name  like  '%表名%';

show  index  from  表名 ;

删除索引

drop  index  索引名   on  表名；

alter  table  表名   drop   index   索引名；

（2）唯一索引

索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。

创建索引

方式一：在创建表时直接指定

方式二：在创表之后，直接创建索引

create   unique   index   索引名   on   表名(字段名);

方式三：在创表之后，通过修改表结构添加索引

alter   table   表明    add   unique   索引名(字段名)；

查询与删除与普通索引一样

（3）主键索引

注意：只要设置了主键，系统会默认为其创建主键索引

 3、组合索引

组合索引也叫复合索引，即在建立索引时使用多个字段，例如同时将身份证号与手机号建立索引，同样可以建立普通索引和唯一索引。

创建索引

create   index    索引名   on   表名(字段名1，字段名2)；  ： 创建普通索引

create  unique  index    索引名   on   表名(字段名1，字段名2)；  ：创建唯一索引

注意：组合索引遵循最左原则（在创建索引时不同字段的顺序不同则索引也不同）

create index index_phone_name on student(phone_num,name);

select * from student where name = '张三';
select * from student where phone_num = '122345456';
select * from student where phone_num = '122345456' and name = '张三';
select * from student where name = '张三' and phone_num = '122345456';
select * from student where name = '张三' or phone_num = '122345456';
select * from student where phone_num = '122345456' or name = '张三';
select * from student where phone_num = '122345456' or phone_num = '12222456';
select * from student where name = '张三' or name = '李四';
/*
上述select语句只有2347能用到index_phone_name索引,因为组合索引遵循最左原则，index_phone_name
索引先写的是phone_num,后写的name，在匹配的时候也是从左到右，1号语句没有phone_num，无法匹配
*/

# explain select * from student where name = '张三' and phone_num = '122345456';
# explain 可以查看此次查询是否用了索引

or关键字是否使用索引请看：mysql使用or时索引情况__幻羽_的博客-CSDN博客

4、全文索引-fulltext

（1）介绍

全文索引是基于相似度的查询，类似与模糊匹配like，但在大量的数据面前，全文索引要比like查询速度快，但是全文索引可能存在精度问题，一般不使用。

只有字段的数据类型为char、varchar、text及其系列才可以建立全文索引，在数据量较大时，想将数据放入一个没有全局索引的表中，然后为其创建全文索引，要比先为一张空表建立全文索引然后加入数据，速度快的多。

MySQL5.6之前只有MylSAM存储引擎支持全文索引，在5.6之后MylSAM与InnoDB均支持全文索引

注意全文索引有两个变量：最小搜索长度、最大搜索长度，在查询时想要对一个词语使用全文索引必须满足这个词语的长度在最小搜索长度、最大搜索长度之内。

show variables like  '%ft%';  :   查看最小搜索长度、最大搜索长度的默认值

全文索引并不是其字面意思上对全文建立索引，它可以给某列建立全文索引，建立好全文索引意味着在使用该字段进行模糊匹配时查询速度会变快。

（2）创建全文索引

方式一：在创表时直接指定（不建议）

# 文章表
create table t_article(
	id int primary key auto_increment,   # 文章id
	title varchar(333),                  # 文章标题
	content varchar(1000),               # 文章内容
	writing_date date,                   # 文章写作日期
	fulltext (content)
);

方式二：在创建表之后，直接指定

create  fulltext  index  索引名   on   表名（字段名）；

方式三：在创建表之后，修改表结构添加索引

alter  table  表名   add   fulltext   索引名（字段名）；

（3）使用全文索引match、against关键字

数据准备：content列建立了全文索引

与常用的模糊匹配like不同，全文索引有自己的语法格式，使用match、against关键字

match(col1,col2,.....匹配那列)   against(查询内容)

select * from t_article where match(content) against('me');
# 没有结果,innodb存储引擎默认最小最大搜索长度为3，84
select * from t_article where match(content) against('you');

5、空间索引-spatial

（1）介绍

MySQL在5.7之后才支持空间索引，空间索引是对空间数据类型建立的索引，MySQL中有4种空间数据类型：geometry（几何）、point（点）、linestring（线）、polygon（多边形），创建空间索引的列必须声明not null，一般不使用。

（2） 创建索引

方式一：在创建表时直接指定

spatial key  索引名（字段名）

create table shop_info(
	id int primary key auto_increment comment '编号',
	shop_name varchar(64) not null comment '店铺名称',
	geom_point geometry not null comment '店铺经纬度',
	spatial key geom_index(geom_point)
);

方式二：在创建表之后，直接指定

create  spaial  index  索引名   on   表名（字段名）；

方式三：在创建表之后，修改表结构添加索引

alter  table  表名   add   spatial   索引名（字段名）；

6、索引的原理B+Tree

从前文对B+Tree索引的介绍可知，B+Tree索引的关键就是这个树结构怎么去构建，如何构建一种优秀的树结构尽可能的使每次查询次数降到最低，速度最快。即：使构建的数深度尽可能小。

（1）二叉树

如果构建的Tree结构如右图所示，查找时同样是一行一行找，与没建立索引是一样的。

如果构建的Tree结构如左图所示，则与hash索引一样，无法进行范围查找，因为在进行范围查找是需要跨越多个节点（回旋）。例如查sid>5的行。

（2）平衡二叉树（避免了上述右图的情况）

 无论是二叉树，还是平衡二叉树都不适合做B+Tree索引的树结构

（3）B树与B+树

Data Structure Visualization  ： 可以查看B树与B+树的构建过程

在构建B树与B+树时需要指定其阶数n，即每个节点最多可以有n-1个值，n个分支，如果超过n-1个则会进行分裂，中间的节点向上提取（遇到偶数阶时最左边的向上提取），两边的节点分开。

B树在范围查找时需要回旋查询

在构建B+树时，如果出现分裂，中间的节点向上提取的同时还会保留一份放到右叶子节点中（遇到偶数阶时最左边的向上提取），两边的节点分开。所有的叶子节点之间存在双向链表连接，B+树支持范围查找（不需要回旋），MySQL默认存储引擎InnoDB和MyISAM引擎都是使用B+树来实现索引的。

注意：阶数n越大树的深度越小

（4）MySQL的MyISAM和InnoDB引擎实现索引的不同

例如：0x07存放的是Bob这一行数据在内存中的存放的地址 

 7、总结

三、MySQL的存储引擎

MySQL的核心就是存储引擎，它就像电脑的操作系统一样，实现了对MySQL数据库的整体调控。

show  engines;   :    查看存储引擎

show  variables  like  '%defaule_storage_engine%';   :    查看当前的默认存储引擎

show  create  table  表名；  ： 查看创建表的sql语句，其中engine后就是该表使用的存储引擎

show create table shop_info;

CREATE TABLE `shop_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
  `shop_name` varchar(64) NOT NULL COMMENT '店铺名称',
  `geom_point` geometry NOT NULL COMMENT '店铺经纬度',
  PRIMARY KEY (`id`),
  SPATIAL KEY `geom_index` (`geom_point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

alter  table  表名   engine = 引擎名；  ：  修改表的存储引擎

四、MySQL的事务

1、介绍

在MySQL中事务（Transaction）是由存储引擎实现的，只有innoDB支持事务功能，事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行

例子：银行转账

create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);

insert into account values(null,'zhangsan',1000);
insert into account values(null,'lisi',1000);

update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;

需求：zhangsan给lisi转200元

假设没有错误，则zhangsan少200元，lisi加200元，sql语句全部执行

假设在第一次update之后，出现了系统bug，没有执行第二次update：

                如果没有事务，则sql语句没有全部执行，此时zhangsan莫名奇妙少了200元，但lisi没

                收到，这200元不翼而飞

                如果有事务，则sql语句全部不执行，不会出现莫名丢200元的情况，保证数据的完整性

2、事务的操作 

begin 或  start  transaction；   ： 开启事务，即开启事务之后的所有sql命令都会绑定在一起，这些命令要么全部执行要么全部不执行

commit；   ： 提交事务，sql语句全部执行完后使其生效

rollback；  ： 回滚事务，执行过程中出现错误，将所有sql语句操作历史记录全部清空

注意：默认情况下MySQL每执行一条sql语句会自动执行开启、提交、回滚事务。有的时候这种方式是不好的，例如银行转账服务。

# 默认情况下
# 自动开启事务
update account set money = money - 200 where id = 1;   # 正常
# 自动提交事务

# 自动开启事务 
update account set money = money + 200 where id = 2;   # 异常
# 自动回滚事务


# 正确的方式
# 自动开启事务
update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;
# 提交事务
# 自动回滚事务

默认的情况会出现200元不翼而飞的情况。因此在某些特殊的场景下需要手动设置事务的提交。

我们可以通过set命令控制事务的自动提交

set  autocommit  = 0； : 禁止自动提交

set  autocommit  = 1； ：开启自动提交（默认）

select @@autocommit;  ：  查看autocommit的值

假设此时已设为关闭自动提交

update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;
commit;

等价于：

begin;
update account set money = money - 200 where id = 1;
update account set money = money + 200 where id = 2;
commit;
rollback;

3、事务的特性

（1）原子性：要么全部执行，要么全部不执行

（2）一致性：系统从一个正确的状态，迁移到另一个正确的状态

（3）隔离性：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其它事务不可见

（4）持久性：事务一旦提交，则其结果是永久性的

4、事务的隔离级别

（1）介绍

隔离性即不同的事务不能同时对一个对象进行操作，必须隔离开，事务A在操作这个对象时，其它事务不可操作。

READ UNCOMMITTED : 读未提交

一个事务可以读取另一个未提交事务的数据，会造成脏读（A事务读到B事务没有提交的数据，一旦B事务回滚，则A事务读到的数据还是没回滚的脏数据）

READ COMMITED        : 读提交（Oracle默认）

一个事务要等另一个事务提交后才能读取数据，可避免脏读，但会造成不可重读（A事务在没有提交之前，多次查看数据看到的数据不一致）

REPEATEABLE READ  : 可重复读（mysql默认）

可以理解为在读数据时将该数据重新存了一份，之后无论多少次读都会读重新存的这份，即使在中间过程中有其它事务把原始的数据进行了修改，也不会读到新修改的值，可避免脏读，不可重读，但会造成幻读（A事务在提交之前与提交之后看到的数据不一致，在A事务没提交之前无论多少次查询查到的数据都不会变化，即使有别的事务把该值进行了修改，A事务也无法读到，此时实际上这个值已经发生了变化，但A事务始终读到的值不变---幻读）

例如：你老婆已经把你银行卡里的钱全花了，但你自己始终看到自己卡了还有钱，--幻读。

SERIALIZABLE             : 序列化

最高级别，在该级别下，事务串行顺序执行，上述问题可全部避免，但效率很低，一般不适用，A事务在操作表时，表会被锁起，B事务无法操作。

从上到下隔离级别越大，即事务与事务之间关联的程度越小

 （2）操作

show   variables   like   '%isolation%';   :   查看隔离级别

设置隔离级别：

set  session  transaction   isolation   level   事务隔离级别字符串（read uncommitted、read committed、repeatable read、serializable）;

补充：

表可以创建普通表与临时表，普通表就是create  table  表名（字段,.....）；创建完普通表后，只要不手动删除它会一直存在，而创建的临时表在断开与数据库的连接时会自动删除。

临时表创建语法：        create   temporary    table    表名

                                     as

                                     select语句