全州建设完小网站荔枝视频在线观看免费最新

当前位置： 首页 > news >正文

全州建设完小网站,荔枝视频在线观看免费最新,站长工具流量统计,优秀网页制作模板存储过程 存储函数 存储函数是有返回值的存储过程#xff0c;存储函数的参数只能是in类型的。具体语法如下#xff1a; characteristic 特性 练习#xff1a; 从1到n的累加 ​​​​​​ create function fun1(n int) returns int deterministic begindeclare total i…存储过程 存储函数 存储函数是有返回值的存储过程存储函数的参数只能是in类型的。具体语法如下 characteristic 特性 练习 从1到n的累加 ​​​​​​ create function fun1(n int) returns int deterministic begindeclare total int default 0;while n0 do set total :total n;set n : n-1;end while;return total; end;fun1(100);触发器 触发器是与表有关的数据库对象指在insert/update/delete 之前或之后触发并执行触发器中定义的SQL语句集合触发器的这种特性可以协助应用在数据库端确保数据的完整性日志记录 数据校验等操作。 使用别名old和 new 来引用触发器中发生变化的记录内容这与其他的数据库都是相似的。现在触发器还只支持行级触发不支持语句级触发。 语法 创建 create  trigger trigger_name before/after insert/update/delete on tbl_name for each row   –行级触发器 begin  trigger_stmt; end; 查看 show triggers; 删除 drop trigger [ schema_name.]trigger_name;  – 如果没有指定schema_name默认为当前数据库。 – 触发器 – 需求通过触发器记录user表的数据变更日志user_logs)包含增加修改删除– 准备工作日志表 user_logscreate table user_logs(id int(11) not null auto_increment,operation varchar(20) not null comment 操作类型insert/update/delete,operate_time datetime not null comment 操作时间,operate_id int(11) not null comment操作的id,opetate_params varchar(500) comment操作参数,primary key(id) )engineinnodb default charsetutf8; – 插入数据触发器 create trigger tb_user_insert_triggerafter insert on tb_user for each row begin insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES(null,insert,now(),new.id,concat(插入的数据内容为id,new.id,name,new.name,,phone,NEW.phone,,email,NEW.email,,profession,NEW.profession)); end;– 查看 show triggers;– 删除 drop trigger tb_user_insert_trigger;– 插入数据到tb_userinsert into tb_user(id,name,phone,email,profession,age,gender,status,createtime)values();– 修改数据触发器create trigger tb_user_update_triggerafter update on tb_user for each row begin insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES(null,update,now(),new.id,concat(更新之前的数据id,old.id,name,old.name,,phone,old.phone,,email,old.email,,profession,old.profession,|更新之后的数据id,new.id,name,new.name,,phone,NEW.phone,,email,NEW.email,,profession,NEW.profession));end;– 查看触发器 show triggers;update tb_user set age20 where id 23;– 删除触发器create trigger tb_user_delete_triggerafter delete on tb_user for each row begin insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES(null,delete,now(),old.id,concat(更新之前的数据id,old.id,name,old.name,,phone,old.phone,,email,old.email,,profession,old.profession); end;show triggers;锁 概述全局锁表级锁行级锁 概述 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源       CPU、RAM、I/O 的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。 磁盘I/O MySQL中的锁按照锁的粒度分分为以下三类 全局锁锁定数据库中的所有表表级锁每次操作锁住整张表行级锁每次操作锁住对应的行数据 全局锁 介绍 全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。 其典型的使用场景是做全库的逻辑备份对所有表进行锁定从而获得一致性视图保证数据的完整性。 mysqldump: 备份 加全局锁 flush tables with read lock; 数据备份mysqldump -uroot -p1234  itcast itcast.sql不要在mysql里执行要在windows里执行mysqldump不是sql命令 解锁unlock tables; 特点 数据库中加全局锁是一个比较重的操作存在以下问题 如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆。如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导致主从延迟。 在InooDB引擎中我们可以在备份时加上参数  –single-transaction 参数来完成不加锁的一致性数据备份。 mysqldump  –single-transaction -uroot -p123456 itcast itcast.sql 表级锁 介绍 表级锁每次操作锁住整张表。锁定力度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。 对于表级锁主要分为以下三类 表锁元数据锁meta data lockMDL意向锁 表锁 对于表锁分为两类 表共享读锁read lock表独占写锁write lock 语法 加锁lock tables 表名… read/write.释放锁unlock tables / 客户端断开连接。 加读锁本客户端只能读 不能写其他客户端也是 加写锁本客户端读写都行其他客户端读写都不可以 元数据锁menta data lock, MDL) MDL加锁过程是系统自动控制无需显示使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。 意向锁 为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。 意向共享锁IS由语句select … locak in share mode 添加。  意向排它锁IX由insert 、update、delete、select … for update 添加。 意向共享锁IS与表锁共享锁read兼容与表锁排它锁write互斥。意向排它锁IX与表锁共享锁read) 及排它锁write都互斥意向锁之间不会互斥。 意向锁 主要解决的问题就是在InnoDB引擎中表锁和行锁的冲突问题。 行级锁 介绍 行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的效率最低并发度最高应用在InnoDB存储引擎中。 InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录家的锁。对于行级锁主要分为以下三类 行锁Record Lock锁定单个记录的锁防止其他事务对此进行update和delete。在RC\RR隔离级别下都支持。间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行Insert产生幻读。在RR隔离级别下都支持。临键锁Next-Key Lock): 行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。 行锁 InnoDB实现了以下两种类型的行锁 共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。排它锁X允许获取排它锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排它锁。 行锁-演示 默认情况下InnoDB在 REPEATABLE READ 事务隔离级别运行InnoDB使用next-key 锁进行搜索和索引扫描以防止幻读。 针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。InnoDB的行锁是针对于索引家的锁不通过索引条件检索数据那么InnoDB将对表中所有记录加锁此时就会升级为表锁。 间隙锁/临键锁-演示 默认情况下InnoDB在 REPEATABLE READ 事务隔离级别运行InnoDB使用 next-key 锁进行搜索和索引扫描以防止幻读。 索引上的等值查询唯一索引给不存在的记录加锁时优化为间隙锁。索引上的等值查询普通索引向右遍历时最后一个值不满足查询需求时next-key lock退化为间隙锁。索引上的范围查询唯一索引–会访问到不满足条件的第一个值为止。 注意间隙锁唯一目的是防止其他事务插入间隙间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。 InnoDB引擎 逻辑存储结构 表空间ibd文件一个mysql实例可以对应多个表空间用于存储记录、索引等数据。 段分为数据段Leaf node segment、索引段Non-leaf node segment)、回滚段Rollback segment)InnoDB是索引组织表数据段就是B树的叶子节点索引段即为B树的非叶子节点。段用来管理多个Extent区。 区表空间的单元结构每个区的大小为1M。默认情况下InnoDB存储引擎页大小为16k即一个区中一共有64个连续的页。 页是InnoDB存储引擎磁盘管理的最小单元每个页的大小默认为16KB为了保证页的连续性InnoDB存储引擎每次从磁盘申请4-5个区。 行InnoDB存储引擎数据是按行进行存放的。 Trx_id每次对某条记录进行改动时都会把对应的事务id赋值给trx_id隐藏列。 Roll_pointer每次对某条记录进行改动时都会把旧的版本写入到undo日志中然后这个隐藏列就相当于一个指针可以通过它来找到该记录修改前的信息。 架构 MySQL5.5版本开始默认使用InnoDB存储引擎它擅长事务处理具有崩溃恢复特性在日常开发中使用非常广泛。下面是InnoDB架构图左侧为内存结构右侧为磁盘结构。 架构-内存架构 Buffer Pool 缓冲池是主内存中的一个区域里面可以缓存磁盘上经常操作的真是数据在执行增删改查操作时先操作缓冲池中的数据若缓冲池没有数据则从磁盘加载并缓存然后再以一定频率刷到磁盘从而减少磁盘IO加快处理速度。 缓冲池以page为单位底层采用链表数据结构管理Page根据状态将Page分为三种类型 free page:  空闲page未被使用。clean page被使用page数据没有被修改过。dirty page脏页被使page数据被修改过页中数据与磁盘的数据产生了不一致。 Change Buffer 更改缓冲区针对非唯一耳机索引页在执行DML语句时如果这些数据Page没有在Buffer Pool中不会直接操作磁盘而会将数据变更存在更改缓冲区change Buffer中在未来数据被读取时再将数据合并恢复到Buffer Pool中再将合并后的数据刷新到磁盘中。 Change Buffer的意义时什么 与聚集索引不同二级索引通常是非唯一的并且以相对随机的顺序插入二级索引。同样删除和更新可能会影响索引树中不相邻的二级索引页如果每一次都操作磁盘会造成大量的磁盘IO。有了ChangeBuffer之后我们可以在缓冲池中进行合并处理减少磁盘IO。 Adaptive Hash Index自适应hash索引用于优化对Buffer Pool数据的查询InnoDB存储引擎会监控对表上各索引页的查询如果观察到hash索引可以提升速度则建立hash索引称之为自适应hash索引。 自适应哈希索引无需人工干预是系统根据情况自动完成。 参数adaptive_hash_index Log Buffer日志缓冲区用来保存要写入到磁盘中的log日志数据redo log、undo log默认大小为16MB日志缓冲区的日志会定期刷新到磁盘中如果需要更新、插入或删除许多行的事务增加日志缓冲区的大小可以节省磁盘I/O。 参数 innodb_log_buffer_size: 缓冲区大小 innodb_flush_log_at_trx_commit: 日志刷新到磁盘时机。 1日志在每次事务提交时写入并刷新到磁盘。 0每秒将日志写入并刷新到磁盘一次。 2日志在每次事务提交后写入并每秒刷新到磁盘一次。 架构-磁盘结构 架构-后台线程 1.Master Thread 核心后台线程负责调度其他线程还负责将缓冲池中的数据异步刷新到磁盘中保持数据的一致性还包括脏页的刷新、合并插入缓存、undo页的回收。 2.IO Thread 在InnoDB存储引擎中大量使用了AIO来处理IO请求这样可以极大地提高数据库的性能而IOThread主要负责这些IO请求的回调。 3.Purge Thread 主要用于回收事务已经提交了的undo log在事务提交之后undo log可能不用就用它来回收。 4.Page Cleaner Thread 协助 Master Thread 刷新脏页到磁盘的线程它可以减轻Master Thread的工作压力减少阻塞。 事务原理 事务 是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失效。 特性 原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性Consistency事务完成时必须使所有的数据都保持一致状态。隔离性Isolation数据库系统提供的隔离机制保证事务不受外部并发操作影响的独立环境下运行。持久性Durability事务一旦提交或者回滚它对数据库中的数据的改变就是永久的 。 redo log   持久性 重做日志记录的是事务提交时数据页的物理修改是用来实现事务的持久性。 该日志文件由两部分组成重做日志缓冲redo log buffer以及重做日志文件redo log file)前者时在内存中后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中用于在刷新脏页到磁盘发生错误时进行数据恢复使用。 undo log  原子性 回滚日志用于记录数据被修改前的信息作用包含两个提供回滚和MVCC多版本并发控制。 undo log和redo log记录物理日志不一样它是逻辑日志。可以认为当delete一条记录时undo log中会记录一条对应的insert记录反之亦然当update一条记录时它记录一条对应相反的update记录。当执行rollback时就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。 Undo log销毁undo log在事务执行时产生事务提交时并不会立即删除undo log因为这些日志可能还用于MVCC。 Undo log存储undo log采用段的方式进行管理和记录存放在前面介绍的rollback segment回滚段中内部包含1024个undo log segment。 MVCC MVCC-基本概念 当前读 读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。对于我们日常的操作如select … lock in share mode(共享锁)select … for update、update、insert、delete(排它锁都是一种当前读。 快照读 简单的select不加锁就是快照读快照读读取的是记录数据的可见版本有可能是历史数据不加锁是非阻塞读。 · Read Committed每次select都生成一个快照读。 · Repeatable Read开启事务后第一个select语句才是快照读的地方。 · Serializable快照读会退化为当前读。 MVCC 全称 Multi-Version Concurrency Control多版本并发控制。指维护一个数据的多个版本使得读写操作没有冲突快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现还需要依赖于数据库记录中三个隐式字段、undo log日志、readView。 MVCC-实现原理