• 视图
• 触发器
• 事务
• 事务处理
• 四种隔离级别
• 事务日志
• MVCC
• 内置函数
• 存储过程
• 索引
• 索引的意义
• 慢查询
• 优化查询
• 索引模拟

视图

• 1.什么是视图？

视图是类似于临时表，由sql语句执行结果形成的一张虚拟表，不能做增删改操作

在mysql中，视图只有表结构，没有表数据文件；

视图能少用就少用，少用的原因在于；

你创建的时候，等你离职了，接盘你的员工可能就懵了

• 如何操作视图？

#创建
create view 视图名 as sql 语句；
-----------------------------------
#查看创建命令
show create view 视图
------------------------------------
#删除
drop  view 视图;
------------------------------------
create view teacher2course as                 #teacher2course就是视图
select * from teacher inner join course on teacher.tid = course.teacher_id;
------------------------------------
drop view teacher2course

触发器【不推荐使用】

1.什么是触发器？

专门针对sql执行语句的设置，比如sql执行了增删改语句，触发器就可以设定增前，增后，删前，删后，改前，改后。查询操作不能触发。

• delimiter

delimiter $$  指定以$$为结束符
编写需要用到分号的各种语句
delimiter; #再重新指定；为结束符

• new

插入新的语句时，触发器操作也会插入新的数据行
一般用于新数据插入多张表。
eg: 当插入A表的时候，触发器此时针对B表也做操作。
	当A表插入 '我是帅哥时'，B表也会触发插入'我是帅哥'

• old

关键字old，一般用于修改或者删除的原数据
eg;			
A表中，有'我上学了'，触发器对B表操作.

当A表数据，我上学了，修改为‘我放学了’。

此时B表的触发操作是将A表中的old数据’我上学了'进行操作。

• 触发器实战

  按照步骤一步一步来，体验下触发器

1.#创建cmd表
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')
);
------------------------------------------------------------------
2.#创建errolog表
create table errolog(
	id int primary key auto_increment,
	err_cmd char(64),
	err_time datetime,
);
-----------------------------------------------------------------
3.#创建触发器
delimiter $$ #将mysql的默认结束符修改转换为$$
create trigger tri_after_insert_cmd after on cmd for each row 
begin
	if NEW.success = 'no' then  #新记录都会被mysql封装成NEW对象
		insert into errlog(err_cmd,err_time) values(NEW.cmd,sub_time);
	end if;
end $$
delimiter ;  #将mysql的默认结束符再修改回来/

---------------------------------------------------------------------
4.#查看触发器
show triggers;

------------------------------------------------------------
5.#插入测试数据
#往表cmd中插入记录，触发触发器，根据if的条件判断是否插入错误日志
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');
---------------------------------------------------------
6.# 查询errlog表记录
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2022-08-19 17:07:29 |
|  2 | useradd xxx     | 2022-08-19 17:07:29 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)
7.#删除触发器
drop trigger tri_after_insert_cmd

事务(Transaction)

• 1.什么是事务？

比如删除员工信息，删除账号，邮箱，个人信息等一系列操作被称为事务。

• 2.事务的作用？

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
-------------------------------------------------------------------------
比如银行卡转账问题，用户跨行转账，从工商银行转到农业很行，流程工商银行先扣钱，农业银行加钱；
中间因为某些操作意外失败，事务 保证工商银行扣不了钱，农业银行加不了钱，也不会给用户带来什么损失。

• 3.事务的四大特性【ACID】

• 名称	特性
  Atomicity/原子性	执行的事务要么全部成功，要么全部失败回滚到执行事务之前的状态
  Consistency/一致性	事务执行前后的数据必须是一致的
  Isolation/隔离性	多个事务之间相互隔离，彼此独立透明，不被打扰，不过还是根据具体的隔离级别
  Durability/持久性	事务一旦提交，数据的修改就是永久性的

原子性 ：Atomicity

执行的事务，要么全部成功，要么回滚到执行事务之前的状态

还是银行卡转账的例子，用户跨行转账，
A银行向B银行转1w，比如A银行此时减少1w,B银行增加1W。
如果A银行执行减1w操作的时候，中途操作失败，B银行没收到.
那么就会回滚，回滚到 A银行操作减钱的时候。

一致性 ：Consistency

事务执行前后的数据必须是一致的

用户的A银行和B银行总资产10001.
无论AB银行之间怎么转账，两个银行加起来的总资产还是10001

隔离性 : Isolation

多个事物之间数据要相互隔离，即彼此独立和透明互不影响，不被打扰，但是还是要看具体的隔离级别

持久性 : Durability

事务一旦被提交（commit,rollback） 数据的改变是永久性的

• 事务操作模拟战

#准备测试数据
create table  user(
	id int primary key auto_increment,
	name char(32),
	balance int
);
-------------------------------------------------
insert into user(name,balacne)
	values 
	('jason',1000),
	('kevin',1000),
	('tank',1000);
--------------------------------------------------’】
start transaction;

update user set balance=900 where name='jason';
update user set balance=1010 where name = 'kevin';
update user set balance=1090 where naem = 'tank';

rollback; #回滚事务上一个状态  此时整个事务结束 

commit;  开启事务之后，只要不执行commit ,数据都没有刷新到硬盘


--------查看事务------------------
show engine innodb status\G;

-----拓展----------
savepoint 保留点，在事务中，如果想回滚到指定的sql语句中，用savepoint即可
语法： 
rollback to savepoint;

事务处理

• 事务型存储引擎

innodb和NDB cluster 
第三方 xtraDB PBXT

• 事务处理关键字

• 事务（transaction）

• 回退（rollback）

• 提交（commit）

• 保留点（savepoint）

• 为了支持回退部分事务处理，必须能在事务处理快中合适的位置

  放置占位符。这样如果需要回退，可以回退到某个占位符（保留点）

  创建占位符可以使用savepoint

  占位符（保留点） 再执行rollback或者commit之后自动释放

四种隔离级别

sql标准中定义了四种隔离级别，每一种级别都规定了一个事务中所作的修改

innodb 支持所有隔离级别

set transaction isolation level 级别

1.read uncommitted(未提交读)

  事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读" 

2.read committed(提交读)

 大多数数据库系统默认的隔离级别
  一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读" 

3.repeatable read(可重复读)

能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题 

4.serializable(可串行读)

强制事务串行执行，很少使用该级别       

• 级别命令操作设置

1.查看当前会话隔离级别
select @@tx_isolation;

2.查看系统当前隔离级别
select @@global.tx_isolation;

3.设置当前会话隔离级别
set session transaction isolatin level repeatable read;

4.设置系统当前隔离级别
set global transaction isolation level repeatable read;

​

事务日志

事务日志可以帮助提高事务的效率 
	存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
  事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

MVCC（多版本并发控制）

控制并发的方法，主要用来提高数据库的并发性能

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
	一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason						1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username		create_version		delete_version
    jason						1									2
    jason01					2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
		username		create_version		delete_version
    jason01	

内置函数

# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;

存储过程

类似于python中的自定义函数

delimiter 临时结束符
create procedure 名字(参数,参数)
begin
	sql语句;
end 临时结束符
delimiter ;

delimiter $$
create procedure p1(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;


# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

"""
查看存储过程具体信息
	show create procedure pro1;
查看所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;
"""

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

• 索引分类

primary key：对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
unique key除了可以加快数据查询还有额外的限制
index key只能加快数据查询 本身没有任何的额外限制

• 索引操作

  #创建索引
  alter table test add index index_name(name);
  #创建索引
  create index index_name on test(name);
  #查看索引
  desc table;
  #查看索引
  show index from table;
  #删除索引
  alter table test drop key index_name;
  #添加唯一性索引
  alter table student add unique key uni_xxx(xxx);
  #查看表中数据行数
  select count(*) from city;
  #查看去重数据行数
  select count(distinct name) from city;

• 前缀索引

  根据字段的前N个字符建立索引

  alter table test add index idx_name(name(10));
  

• 联合索引

  多个字段建立一个索引

  例：
  where a.女生 and b.身高 and c.体重 and d.身材好
  index(a,b,c)
  特点：前缀生效特性
  a,ab,ac,abc,abcd 可以走索引或部分走索引
  b bc bcd cd c d ba ... 不走索引
  

• 原则：把最常用来做为条件查询的列放在最前面

  #创建people表
  create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));
  #创建联合索引
  alter table people add index idx_gam(gender,age,money);

索引的意义

​ 索引的存在可以加快数据的查询 但是会减慢数据的增删

​ 缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

索引底层原理

树:是一种数据结构 主要用于优化数据查询的操作

二叉树:两个分支
B树(B-树)、B+树、B*树
	B树:
		除了叶子节点可以有多个分支 其他节点最多只能两个分支
  		所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
	B+树:
       只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
	B*树
    	在树节点添加了通往其他节点的通道 减少查询次数

慢查询优化

• explain

• 详解请参考其它文章

查看该SQL语句有没有使用上了索引，有没有做全表扫描

没做索引就是ALL

mysql> explain select * from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

• 1）index

  Full Index Scan，index与ALL区别为index类型只遍历索引树。
  

• 2）range

  索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
  

• 3）ref

  使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
  

  	mysql> alter table city drop key idx_code;
  	mysql> explain select * from city where countrycode='chn';
  	mysql> explain select * from city where countrycode in ('CHN','USA');
  	mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

• 4）eq_ref

  类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
  

  	join B
  	on A.sid=B.sid

• 5）const

  仅仅能查出一条的SQL语句并且用于Primary key 或 unique索引；
  

• 6）system

  ​ 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

  • 如将主键置于where列表中，MySQL就能将该查询转换为一个常量*

  mysql> explain select * from city where id=1000;
  

• 7）null

从上到下，性能从最差到最好，我们认为至少要达到range级别

mysql> explain select * from city where id=1000000000000000000000000000;

• Extra（扩展）

Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer

如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引

mysql> explain select * from city where countrycode='CHN' order by population;

当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现

key_len: 越小越好

• 前缀索引去控制

rows: 越小越好

优化查询：建立索引

• 选择唯一性索引

• 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如:
学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
主键索引和唯一键索引，在查询中使用是效率最高的。

• 联合索引

• 如果重复值较多，可以采用

• 2．为经常需要排序、分组和联合操作的字段建立索引

• 注：如果经常作为条件的列，重复值特别多，可以建立联合索引

例如:
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作

• 字段索引

  如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。

  因此，为这样的字段建立索引，可以提高整个表的查询速度。

• 3.1 经常查询

• 3.2 列值的重复值少

• 前缀索引

  如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
  会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度

• 注意索引的局限性

5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
6．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

语句查询注意事项

• 1.没有查询条件，或者查询条件没有建立索引，不能全盘扫描

在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。
1）对用户查看是非常痛苦的。
2）对服务器来讲毁灭性的。
3）SQL改写成以下语句：

• 2.查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from city where population>3000 order by population;

1）如果业务允许，可以使用limit控制。
2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。

• 3索引本身失效，统计数据不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决

• 4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)

• 5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误

mysql> create table test (id int ,name varchar(20),telnum varchar(10));
mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
mysql> explain select * from test where telnum=120;	
mysql> alter table test add index idx_tel(telnum);	
mysql> explain select * from test where telnum=120; 	
mysql> explain select * from test where telnum=120; 	
mysql> explain select * from test where telnum='120';

• 6． <> ，not in 不走索引

mysql> select * from tab where telnum <> '1555555';	
mysql> explain select * from tab where telnum <> '1555555'

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in尽量改成union

EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
\#改写成
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

• 7．like "%_" 百分号在最前面不走

\#走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';

\#不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';

• 8.单独引用联合索引里非第一位置的索引列

索引模拟实战

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉 

• 联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快