MySQL的触发器、事务、MVCC版本并发、内置函数、流程控制、索引及创建索引、慢查询优化、联合索引

一、视图

		什么是视图？
			就是我们执行完SQL语句的结果 就是一张虚拟表 我们可以基于改表做其他操作
			如果这张虚拟表会频繁使用 为了方便可以讲虚拟表保存起来 保存起来我们称之为"视图"
			
		语法结构
			create view 视图名 as SQL语句；	# 注意as不能省略 不是命名的意思
			ex: create view userinfo as select * from emp1 inner join dep1 on emp1.dep_id = dep1.info_id;
			
		视图原理
			在硬盘中视图只有表结构文件 没有表数据文件(不能做增删改)视图通常是用于查询 

二、触发器

		什么是触发器？
			触发器类似于我们python中魔法方法到达什么条件自动触发
			主要是用于对表数据的增、改、删自动触发的功能(增前、增后、改前、改后、删前、删后)
			
		语法结构
			create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
			begin
				sql语句
			end

		触发器注意事项
			触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符号
			所以为了能够完成完整的写出触发器的代码 我们需要临时修改SQL语句中默认的结束符
			修改默认符号我们需要用到 delimiter 需要修改的符号(注意cmd里面是临时修改 配置文件是永久修改)

		'''模拟一个CMD 如果执行失败则写入errlog里面'''
		
		1.	CREATE TABLE cmd (
			    id INT PRIMARY KEY auto_increment,
			    USER CHAR (32),
			    priv CHAR (10),
			    cmd CHAR (64),				# cmd命令
			    sub_time datetime, 			# 提交时间
			    success enum ('yes', 'no')  # no代表执行失败
			);
			
		2.	CREATE TABLE errlog (
			    id INT PRIMARY KEY auto_increment,
			    err_cmd CHAR (64),			# 报错的cmd命令
			    err_time datetime			# 报错时间
			);
		3.	delimiter &&  # 将mysql默认的结束符由;换成&&
	
		4.	create trigger tri_after_insert_cmd after insert on cmd for each row
			begin
			    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
			        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
			    end if;
			end &&
		5.	delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了
		6.	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');
		7. select * from errlog; # 查询errlog表记录
		8. drop trigger tri_after_insert_cmd; # 删除触发器
		

三、事务

		什么是事务？
			事务是用户定义的一系列数据库操作 这些操作可以视为一个完成的逻辑处理工作单元
			要么全部执行 要么全部不执行 是不可分割的工作单元
			
		事务的产生
			数据库中的数据是共享资源 因此数据库系统通常要支持多个用户的或不同应用程序的访问
			并且各个访问进程都是独立执行的 这样就有可能出现并发存取数据的现象 为了避免数据库的不一致性
			这种处理机制称之为“并发控制”，其中事务就是为了保证数据的一致性而产生的一种概念和手段（事务不是唯一手段）
			
		事务的四大特征
			 为了保证数据库的正确性与一致性事务具有四个特征
			 原子性Atomicity
			 	事务的原子性保证事务中包含的一组更新操作是原子的 不可分割的 不可分割是事务最小的工作单位
			 	所包含的操作被视为一个整体 执行过程中遵循 要么全部执行 要不都不执行 不存在一半执行 一半未执行的情况
			 一致性Consistency
			 	事务的一致性要求事务必须满足数据库的完整性约束
			 	且事务执行完毕后会将数据库由一个一致性的状态变为另一个一致性的状态 事务的一致性与原子性是密不可分的
			 隔离性Isolation
			 	事务的隔离性要求事务之间是彼此独立的 隔离的 及一个事务的执行不可以被其他事务干扰
			 持久性Durability
			 	事物的持续性也称持久性 是指一个事务一旦提交 它对数据库的改变将是永久性的
			 	因为数据刷进了物理磁盘了 其他操作将不会对它产生任何影响

		1.	create table user(id int primary key auto_increment,
								name char(32),	#创建一个用户表
								balance int);
								
		2.	insert into user(name,balance)values('LIKE',1000),
								('LEBRON',1000),	# 添加用户
								('LISA',1000);
		3.	start transaction;	# 修改数据之前先开启事务操作
	
		4.	update user set balance=900 where name='LIKE'; 		#买支付100元
			update user set balance=1010 where name='LEBRON'; 	#中介拿走10元
			update user set balance=1090 where name='LISA'; 	#卖家拿到90元
		5.	rollback;	# 回滚到上一个状态(撤回到上一步)
		6.	commit;		# 开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作
		

		拓展知识
			MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
			事务处理中有几个关键词汇会反复出现
			事务(transaction)	回退(rollback)	提交(commit)		保留点(savepoint) 	# 前面三个我们都知道了作用主要第四个
			为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
			创建占位符可以使用 savepoint  savepoint sp01;
			回退到占位符地址	rollback to sp01;		# 保留点在执行rollback或者commit之后自动释放

		在SQL标准中定义了四种隔离级别 每一种级别都规定了一个事务的修改
		1. read uncommitted(未提交读)
			未提交读见明知意事务中修改的数据没有提交 对其他事务也是可见的 事物可以读取未提交的数据 这个现象也称之为"脏读"
		2. read committed(提交读)
			大多数数据库系统默认的隔离级别 一个事务从开始直到提交之前所有的任何修改对其他事务都是不可见的(直到提交了才算修改成功) 这种级别也叫做"不可重复读"
		3. repeatable read(可重复读)
			可重复读是MySQL默认隔离级别 能够解决脏读的问题  但是无法解决"幻读"
			所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录
			当之前的事务再次读取该范围的记录会产生幻行 InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
		4. serializable(可串行读)
			强制事务串行 很少使用改级别
			
		由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable  这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题。

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

四、MVCC多版本并发控制

		MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作
		其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)
		
		InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
		一个列保存了行的创建时间
		一个列保存了行的过期时间(或删除时间)  	# 本质是系统版本号
		每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

		例如：	刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
					 username		create_version		delete_version
					LebronJames			  1					
		可以看到，我们在content列插入了LebronJames这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
		然后我们将LebronJames修改为LebronJames01，实际存储是这样的
				    username		create_version		delete_version
				   LebronJames			 1					   2
				  LebronJames01			 2
		可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2
		然后将新数据写入，将新数据的create_version标记为新的事务id(版本号会随着事务的更新而更新)

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

五、储存过程

		类似于Python中的自定义函数
		
		语法结构
			delimiter && 	# 涉及到begin语法需要修改临时结束符
			create procedure 名字(参数,参数)
			begin
				sql语句;
			end 临时结束符
			delimiter ;		# 用完再修改回去

		ex:
			delimiter &&
			create procedure p1(
			    in m int,  		# in表示这个参数必须只能是传入不能被返回出去
			    in n int,  
			    out res int)  	# out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
			begin
			    select name from teacher where tid > m and tid < n;		# name 表示用户设置的数字
			    set res=0;  # 用来标志存储过程是否执行
			end &&
			delimiter ;		# 用完修改成默认结束符号

			
			# 针对res需要先提前定义
			set @res=10;  定义	# 相当于python中设置一个变量名值为10
			select @res;  查看
			call p1(1,5,@res)  调用
			select @res  查看

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

六、内置函数

		ps: 可以通过help 函数名  查看帮助信息！
			1.Trim、LTrim、RTrim	# 移除指定字符

2.Lower、Upper # 大小写转换

3.Left、Right # 获取左右起始指定个数字符

4.Soundex # 返回读音相似的值(只对英文 中午无效)

eg:客户表中有一个顾客登记的用户名为like
但如果这是输入错误真名其实叫liku可以使用soundex匹配发音类似的
where Soundex(name)=Soundex(‘liku’)

5.日期格式:date_format 获取时间相关操作

		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');
		'''获取2017年3月1日的书'''
		select * from blog where Date(sub_time) = '2017-03-01';
		'''获取2016年的书再加7月'''
		select * from blog where year(sub_time)= 2016 and month(sub_time)=07;

七、流程控制

		if条件语句
			delimiter //					# 设置结束符号
			CREATE PROCEDURE proc_if ()		# 创建一个存储过程
			BEGIN
			
			    declare i int default 0;	# 声明变量
			    if i = 1 THEN				# 如果i 等于1 那么 THEN(那么)
			        SELECT 1;				# 选择1
			    ELSEIF i = 2 THEN			# elif i 等于2 那么 THEN(那么)
			        SELECT 2;				# 选择2
			    ELSE						
			        SELECT 7;				#  否则 选择7
			    END IF;						# 结束if判断
			
			END //							# 结束语句
			delimiter ;						# 修改回结束符号

		while循环
			delimiter //
			CREATE PROCEDURE proc_while ()	# 设置循环
			BEGIN
			    DECLARE num INT ;			# 声明变量
			    SET num = 0 ;				# 设置变量
			    WHILE num < 10 DO			# 当num小于10 做
			        SELECT
			            num ;
			        SET num = num + 1 ;		# num += 1
			    END WHILE ;					# 结束循环
			
			END //
			delimiter ;

八、索引

		什么是索引？
			索引像是一本书的目录 它能让你更快的找到想要的内容
			让获取的数据更有目的性 从而提升数据库检索数据的性能
		
		索引在MySQL中也叫做键 是存储引擎用于快速找到记录的一种数据结构
		Primary key		Unique key		Index key
		上述三种键在数据查询的时候都可以快速的查询
		Primary key		Unique key	除了可以加快查询数据还有额外的限制(非空且唯一 唯一)
		Index key 只能加快数据查询 本身没有任何的额外限制
		
		真正理解索引加快数据查询的意义
			索引的存在可以加快数据的查询 但是会减慢数据的增删
			
		索引相关概念
			聚集索引  Primary key
				 聚集索引就是表的主键 在Innodb存储引擎规定下一张表只能有一个主键 (可以看着主目录)
			辅助索引  Unique key	 Index key
				辅助索引就是除了主键之外的键都是辅助索引(可以看出附加的目录)
				查询数据的时候不可能都是用ID作为筛选条件 这个时候就用不到聚集索引了
				索引就需要给其他字段建立索引 这些索引就叫做辅助索引(无论有多少个索引都基于主键去查)
			覆盖索引
				只在辅助索引的叶子节点中就已经找到了我们想要的数据
				ex: slelect name from user where name = 'LebronJames'	# 条件就是结果 这就是覆盖索引
			非覆盖索引
				select age from user where name = 'LebronJames'
				上述语句叫做非覆盖索引 虽然查询的时候有了索引字典name 但是要查是age字段 所以还需要利用主键去查找

九、索引底层原理

		索引底层用的是树 那是什么是树呢 ？
			树是一个数据结构 主要用于优化数据查询 就是用来快速的查找数据

		这个就是树 第一层树根 第二层树枝 第三层树叶  来说说他的底层原理
		我们现在找一个数字30 如果我们不按照树来找 我们肯定是从1开始数类似于一页一页的翻
		哪有了这个索引有了这个数是怎么找的呢？
		假设第一层是主键 会涉及到磁盘 我们磁盘会把数据分为段 一段一段的来查询 先读一层 它会自动分层
		第一层会分为三小层 第一层会从1-28 第二层会到28-65 第三层65-100 依次寻找 依次分层 总共经历三层就找到了 这就是树
		但是数分为很多种
		
		二叉树
			所有的数据都是基于二叉树来的 每次都会分为两层 
		B数
			除了叶子节点可以有多个分支 其他节点最多只能两个分支(叶子结点就是树叶)
			所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的 树的层级越高表示读取时间越慢 )
			缺点树的存储数据量太小了所以有了另外一个树
		B+树
			只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
			所以第一层级能够存储的ID主键就更多了 就意味着树的层级越矮 查找速度越快(所以我们就知道创建表的时候为什么把ID当做主键值了)
				
		B*树
			在树节点添加了通往其他节点的通道 减少查询次数

十、慢查询优化

		在查询数据的时候 有些人用30分组有些人10分组有些人1分钟这是为什么呢 ？
		我们怎么优化这些SQL语句呢！ 这个时候就有一个关键字explain语句

explain命令用法

explain select 字段名 from 表名;

		常见的索引扫描类型
			index	range	ref	eq_ref	const	system	null 
		从左到右 性能从最差到最好 我们认为至少要达到range级别
		index：Full Index Scan index与ALL区别为index类型只遍历索引树
		range：索引范围扫描 对索引的扫描开始于某一点 返回匹配值域的行 显而易见的索引范围扫描是带有between或者where子句里带有<,>查询

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

		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';

		eq_ref：类似ref 区别就在使用的索引是唯一索引 对于每个索引键值 表中只有一条记录匹配 
				简单来说 就是多表连接中使用primary key或者 unique key作为关联条件A
				
		const、system：当MySQL对查询某部分进行优化 并转换为一个常量时 使用这些类型访问
					  如将主键置于where列表中 MySQL就能将该查询转换为一个常量
					  
		NULL：MySQL在优化过程中分解语句 执行时甚至不用访问表或索引 
			  例如从一个索引列里选取最小值可以通过单独索引查找完成

十一、建立索引的原则(规范)

		为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
			1、选择唯一性索引
				唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
			2、为经常需要排序、分组和联合操作的字段建立索引
			3、为常作为查询条件的字段建立索引
				如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
				因此，为这样的字段建立索引，可以提高整个表的查询速度。
			4、尽量使用前缀来索引
				如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段
				进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
			5、限制索引的数目
				索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
				修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
			6、删除不再使用或者很少使用的索引
				表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。
				数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

十二、测试索引

		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<3000)do
			        insert into s1 values(i,'LebronJames','male',concat('LebronJames',i,'@king'));
			        set i=i+1;
			    end while;
			END$$ #$$结束
			delimiter ; #重新声明分号为结束符号
			
		3. 查看存储过程
			show create procedure auto_insert1\G 
		4. 调用存储过程
			call auto_insert1();
		
		# 表没有任何索引的情况下
		select * from s1 where id=300;
		# 避免打印带来的时间损耗
		select count(id) from s1 where id = 300;
		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 = 'LebronJames'  # 速度仍然很慢

		范围问题
		# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
		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 < 100;
		select count(id) from s1 where id != 3;
		alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
		select count(id) from s1 where name = 'LebronJames';  # 又慢了
		
		create index idx_name on s1(name);  # 给s1表的name字段创建索引
		select count(id) from s1 where name = 'LebronJames'  # 仍然很慢！！！
		"""
		再来看b+树的原理，数据需要区分度比较高，而我们这张表全是LebronJames，根本无法区分
		那这个树其实就建成了“一根棍子”

		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='LebronJames' 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='LebronJames' 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='LebronJames' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
		select count(id) from s1 where name='LebronJames' 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='LebronJames' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉 
		

十三、联合索引

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

技术小白记录学习过程，有错误或不解的地方请指出，如果这篇文章对你有所帮助请点点赞收藏+关注 谢谢支持 !!!