电子商务网站建设的基本步骤,邢台建设企业网站费用,网页代码怎么查看,贵州建设厅施工员考试网站一.存储引擎
1.创建的存储引擎 MyISAM 不支持事务、也不支持外键#xff0c;索引采用非聚集索引#xff0c;其优势是访问的速度快#xff0c;对事务完整性没有要求#xff0c;以 SELECT 、 INSERT 为主的应用基本上都可以使用这个存储引擎来创建表。 MyISAM 的表在磁盘上…一.存储引擎
1.创建的存储引擎 MyISAM 不支持事务、也不支持外键索引采用非聚集索引其优势是访问的速度快对事务完整性没有要求以 SELECT 、 INSERT 为主的应用基本上都可以使用这个存储引擎来创建表。 MyISAM 的表在磁盘上存储成 3 个文件其文件名都和表名相同扩展名分别是 .frm存储表定义 .MYDMYData存储数据 .MYI MYIndex存储索引 InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全支持自动增长列外键等功能, 索引采用聚集索引索引和数据存储在同一个文件所以InnoDB 的表在磁盘上有两个文件其文件名都和表名相同扩展名分别是 .frm存储表的定义 .ibd存储数据和索引 MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表实际只对应一个磁盘文件格式是.frm 表结构定义。 MEMORY 类型的表访问非常快因为它的数据是放在内存中的并且 默认使 用 HASH 索引不适合做范围查询但是一旦服务关闭表中的数据就会丢失掉。 在mysql中可以使用查看存储引擎 show engines; 可以进入mysql的存储目录查看存储的文件 2.区别 种类 锁机 制 B- 树索 引 哈希索 引 外键 事务索引缓存数据缓存MyISAM表锁 支持 不支持 不支持 不支持 支持 不支持InnoDB 行锁 不支持 支持支持支持支持支持Memory表锁支持 支持 不支 持 不支 持 支持支持 锁机制表示数据库在并发请求访问的时候多个事务在操作时并发操作的粒度。 B-树索引和哈希索引主要是加速SQL的查询速度。 外键子表的字段依赖父表的主键设置两张表的依赖关系。 事务多个SQL语句保证它们共同执行的原子操作要么成功要么失败不能只成功一部分失败需要回滚事务。 索引缓存和数据缓存和MySQL Server的查询缓存相关在没有对数据和索引做修改之前重复查询可以不用进行磁盘I/O数据库的性能提升目的是为了减少磁盘I/O操作来提升数据库访问效率读取上一次内存中查询的缓存就可以了。 二.索引
1.简介 当表中的数据量到达几十万甚至上百万的时候 SQL 查询所花费的时间会很长导致业务超时出错此时就需要用索引来加速SQL 查询。 由于索引也是需要存储成索引文件的因此对索引的使用也会涉及磁盘 I/O 操作。如果索引创建过多使用不当会造成SQL 查询时进行大量无用的磁盘 I/O 操作降低了 SQL 的查询效率适得其反因此掌握良好的索引创建原则非常重要 索引的优点 提高查询效率 索引的缺点 索引并非越多越好过多的索引会导致 CPU 使用率居高不下由于数据的改变会造成索引文件的改动过多的磁盘I/O 造成 CPU 负荷太重 2.索引的分类 索引是创建在表上的是对数据库表中一列或者多列的值进行排序的一种结果。 索引的核心是提高查询 的速度 物理上 可以分为 聚集索引和非聚集索引 逻辑上可以分为以下几类
普通索引没有任何限制条件可以给任何类型的字段创建普通索引(创建新表已创建表数量是不限的一张表的一次sql查询只能用一个索引 where a1 and bM) 唯一性索引使用UNIQUE修饰的字段值不能够重复主键索引就隶属于唯一性索引 主键索引使用Primary Key修饰的字段会自动创建索引(MyISAM, InnoDB) 单列索引在一个字段上创建索引 多列索引在表的多个字段上创建索引 (uidcid多列索引必须使用到第一个列才能用到多列索引否则索引用不上) 全文索引使用FULLTEXT参数可以设置全文索引只支持CHARVARCHAR和TEXT类型的字段上常用于数据量较大的字符串类型上可以提高查询速度(线上项目支持专门的搜索功能给后台服务器增加专门的搜索引擎支持快速高效的搜索 elasticsearch 简称es C开源的搜索引擎 搜狗的workflow)
3.索引的创建和删除 创建表的时候指定索引字段 CREATE TABLE index1(id INT , name VARCHAR ( 20 ), sex ENUM ( male , female ), INDEX(id,name)); 在已经创建的表上添加索引 CREATE [UNIQUE] INDEX 索引名 ON 表名属性名 length [ ASC | DESC ]); 例如 create index nameidx on student(name(10)); 删除索引 : DROP INDEX 索引名 ON 表名 ; 结合实践讲出来
注意点:
经常作为where条件过滤的字段考虑添加索引 字符串列创建索引时尽量规定索引的长度而不能让索引值的长度key_len过长 索引字段涉及类型强转、mysql函数调用、表达式计算等索引就用不上了 4.索引的执行过程
1.explain查看执行计划
使用explain查看sql的执行计划分析索引的执行过程查看user表的索引 explain select * from student where uid1; explain select * from student where namezhangsan; 2.explain结果字段分析
select_type simple表示不需要union操作或者不包含子查询的简单select语句。有连接查询时外层的查询为simple且只有一个。primary一个需要union操作或者含有子查询的select位于最外层的单位查询的select_type即为primary且只有一个。 unionunion连接的两个select查询除了第一个表外第二个以后的表的select_type都是 union。 union result包含union的结果集在union和union all语句中因为它不需要参与查询所以id字段为null。 table 显示查询的表名如果不涉及对数据库操作这里显示null如果显示为尖括号就表示这是个临时表后边的N就是执行计划中的id表示结果来自于这个查询产生的如果是尖括号括起来union M,N也是一个临时表表示这个结果来自于union查询的id为MN的结果集 type const使用唯一索引或者主键返回记录一定是1行记录的等值where条件时通常type就是 const。ref常见于辅助索引的等值查找或者多列主键、唯一索引中使用第一个列之外的列作为等值查找会出现返回数据不唯一的等值查找也会出现。range索引范围扫描常见于使用、、is null、between、in、like等运算符的查询中。index索引全表扫描把索引从头到尾扫一遍常见于使用索引列就可以处理不需要读取数据文件的查询可以使用索引排序或者分组的查询。all全表扫描数据文件然后在server层进行过滤返回符合要求的记录。 ref 如果使用常数等值查询这里显示const 如果是连接查询被驱动表的执行计划这里会显示驱动表的关联字段 Extra using filesort排序时无法用到索引常见于order by和group by语句中。 using index查询时不需要回表查询直接通过索引就可以获取查询的数据。 5.索引的底层实现原理 数据库索引是存储在磁盘上的当数据量大时就不能把整个索引全部加载到内存了只 能逐一加载每一个磁盘块对应索引树的节点索引树越低越 “ 矮胖 ” 磁盘 IO 次数就 少 MySQL 支持两种索引一种的 B树索引一种是哈希索引大家知道B树和哈希表在数据查询时的效率是非常高的。 这里我们主要讨论一下 MySQL InnoDB存储引擎基于B树但实际上MySQL 采用的是 B 树结构的索引结构。 B树是一种m 阶(一般在200-300)平衡树叶子节点都在同一层由于每一个节点存储的数据量比较大索引整个 B- 树的层数是非常低的基本上不超过三层。 由于磁盘的读取也是按 block 块操作的内存是按 page页面操作的因此B树的节点大小一般设置为和磁盘块大小一致这样一个B树节点就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来所以当使用B- 树存储索引的时候磁盘 I/O 的操作次数是最少的 MySQL 的读写效率主要集中在磁盘I/O上。 1.B树 data存储的是数据本身还是磁盘上的地址?
不同的存储引擎对应的不同,对于MyISAM存储的是磁盘上的地址,因为存储数据的文件与存储索引的文件不同,而InnoDB存储的是数据本身,因为数据和索引存储在一个文件中
AVL树和B树的搜索时间是一样的,但是B树更加矮胖,因此它的磁盘IO的次数更少 从上图可以看到 B 树存在的缺点 每个节点中有key也有data但是每一个节点的存储空间是有限的如果data数据较大时会导致每个节点能存储的key的数据很小 当存储的数据量很大时同样会导致B树的高度较大磁盘IO次数花费增大效率降低
2.B树 那么 MySQL 最终为什么要采用 B 树存储索引结构呢那么看看 B- 树和 B 树在存储结构上有什么不同 B树的每一个节点存了关键字和对应的数据地址而B树的非叶子节点只存关键字不存数据地址。因此B树的每一个非叶子节点存储的关键字是远远多于B树的B树的叶子节点存放关键字和数据因此从树的高度上来说B树的高度要小于B树使用的磁盘I/O次数少因此查询会更快一些。 B树由于每个节点都存储关键字和数据因此离根节点近的数据查询的就快离根节点远的数据查询的就慢B树所有的数据都存在叶子节点上因此在B树上搜索关键字找到对应数据的时间是比较平均的没有快慢之分。 在B树上如果做区间查找遍历的节点是非常多的B树所有叶子节点被连接成了有序链表结构因此做整表遍历和区间查找是非常容易的。 索引的底层原理:对于一个SQL语句,mysql server先检查过滤的字段有没有索引,如果没有索引就做整表搜索,有索引的话操作系统kernel或从磁盘上的索引文件中先把索引数据加载到内存上,用B树构建,为什么用B树来构建呢?因为B树是一个平衡树,搜索的效率是非常好的,B树是每一个节点每一个节点构建,每一个节点对应的刚好是一个磁盘IO,非叶子节点存的都是key,这样相对于B树来说存储的key值较多,key和data都是存储在叶子节点的,所以用B树来构造叶子结点的话,会以花费最少的磁盘IO的次数,以二分搜索的时间复杂度,来找见索引的数据 6.InnoDB的主键和二级索引树
主键就是primary key,二级索引就是普通的索引和unique
1.主键索引树
场景一:uid是主键
data中存储的是表中的对应主键的数据
2.二级索引树
场景二:uid是主键,name创建普通索引(二级索引)
data中存储的是主键,所以如果查询了普通索引和主键的值,直接可以在二级索引树上查找到数据并且进行返回,如果查询到之外的数据,需要进行回表操作,也就是需要拿着查询到的主键索引,到主键索引树上查询到相应的数据进行返回 一次SQL查询只会查询一个索引树.但是如果我们SQL如果中涉及到两个字段,那么一个索引树可以索引命中呢?此时我们可以建立多列索引,比如对于以下的SQL select * from user where age20 order by name; 此时建立age和name的多列索引,二级索引树上key存储的就是age,name,data存储的是主键
注意:对于多列索引,查询中必须包含第一个字段(可以不包含其他的建立多列索引的字段),否则索引不命中.
如果对于含有多个字段的过滤,每个字段有单独的索引,因为mysql只会选择一个二级索引树进行搜索,所以之后选择其中一个,那么选择哪个呢?MySQL会看看到底是按哪个字段取的数据量少,就会选择哪个二级索引树.
如果想要强行使用某个索引,可以使用 select * from user force index(age) where namezhangsan and age10; 如果name和age都建立了二级索引,因为name的数据量少,所以会选用name索引树进行搜索,此时我们可以使用强制索引,强制使用age索引树搜索
7.MyISAM的主键和二级索引树
1.主键索引树
data存放的是数据的地址 2.二级索引树
此时二级索引树data存放的不是主键,二级相应的数据地址,不涉及回表的操作了. 8.聚簇索引和非聚簇索引
MyISAM存储引擎索引结构叶子节点存储关键字和数据地址也就是说索引关键字和数据没有在一起存放体现在磁盘上就是索引在一个文件存储数据在另一个文件存储例如一个user表会在磁盘上存储三个文件 user.frm表结构文件 user.MYD表的数据文件 user.MYI表的索引文件。 MyISAM 的索引方式也叫做非 聚簇 索引。 InnoDB的索引树叶节点包含了完整的数据记录这种索引叫做 聚簇 索引。因为 InnoDB 的数据文件本身要按主键聚集所以InnoDB 要求表必须有主键区别于 MyISAM 可以没有如果没有显式指定则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键如果不存在这种列则 MySQL 自动为InnoDB 表生成一个隐含字段作为主键这个字段长度为 6 个字节类型为长整形 9.哈希索引
哈希表中的数据没有任何数据可言,只能做等值查询
1.没办法处理磁盘上的数据加载到内存上构建高效的搜索数据结构因为它没有办法减少磁盘IO的次数 2.只适合做等值搜索其它的范围、排序、前缀搜索等不合适 10.InnoDB自适应哈希索引
lnnoDB存储引擎监测到同样的二级索引不断被使用那么它会根据这个二级索引在内存上根据二级索引树B树)上的二级索引值在内存上构建一个哈希索引来加速搜索 自适应哈希索引本身的数据维护也是要耗费性能的并不是说自适应哈希索引在任何情况下都会提升二级索引的查询性能!根据参数指标来具体分析是否打开或者关闭自适应哈希索引!! ! show engine innodb status\G 能看到两个比较重要的信息:
RW-latch等待的线程数量自适应哈希索引默认分配了8个分区)同一个分区等待的线程数量过多走自适应哈希索引搜索的频率和二级索引树搜索的频率
如果等待的线程数量多并且哈希索引搜索的频率低,最好关闭自适应哈希索引
三.索引总结
MySQL以主键的值构造成一颗树叶子节点存放着该主键对应的整行数据。此为聚簇索引。其他的索引为辅助索引叶子节点存放着索引字段的值及对应的主键值。一般情况下—次查询只能使用一条索引对查询where条件中区分度高的字段加索引联合索引叶子节点存储的顺序以创建时指定的顺序为准因此区分度高的放左边能被多个查询复用到的放左边只select需要用到的字段尽量避免select*如有必要可使用FORCE INDEX强制索引多表JOIN先按各表的查询条件比较哪个开销小从小表取出所有符合条件的到大表循环查找以下情况无法使用到索引like通配符在最左not in,, ,对列做函数运算隐式数据类型转换OR子句
面试题:如果优化SQL或索引
实际项目中是千万条的SQL语句,首先我们要能够查询到哪些SQL语句是慢的,然后通过explain进行具体情况进行分析
慢查询日志压测执行各种业务!!查看慢查询日志找出所有执行耗时的sql用explain分析这些耗时的sql举例子。。 show variables like %slow_query%; set global slow_query_logon; show variables like long%; set long_query_time 1; show profiles命令可有查看sql具体的运行时间全局变量的名字是profiling set global profilingon show profiles查看 四.MySQL事务
1.什么是事务 一个事务是由一条或者多条对数据库操作的 SQL 语句所组成的一个不可分割的单元只有当事务中的所有操作都正常执行完了整个事务才会被提交给数据库如果有部分事务处理失败那么事务就要回退到最初的状态因此事务要么全部执行成功要么全部失败。 所以记住事务的几个基本概念如下 事务是一组SQL语句的执行要么全部成功要么全部失败不能出现部分成功部分失败的结果。保证事务执行的原子操作。 事务的所有SQL语句全部执行成功才能提交commit事务把结果写回磁盘上。 事务执行过程中有的SQL出现错误那么事务必须要回滚rollback到最初的状态。
刚开始我们拿一个常见的操作来说明我们为什么需要事务.假如现在张三给李四转账100元,我们需要先把张三账户的钱减少100,然后再把李四账户的钱增加100.但是如果张三账户的钱减少100的时候突然发生网络异常,那么张三减少了100,但是李四却没有增加100,这个时候就会出现问题了. update accout set moneymoney-100 where name张三 update accout set moneymoney100 where name李四 为了避免这种问题的发生,因此出现了事务(transaction) MyISAM是不支持事务的,InnoDB是支持事务,行锁的 select autocommit; 默认是自动提交的,可以自行设置为0,不自动提交 set autocommit0; 2.事务的ACID特性 每一个事务必须满足下面的 4 个特性 事务的原子性 Atomic 事务是一个不可分割的整体事务必须具有原子特性及当数据修改时要么全执行要么全不执行即不允许事务部分的完成。 事务的一致性 Consistency 一个事务执行之前和执行之后数据库数据必须保持一致性状态。数据库的一致性状态必须由用户来负责由并发控制机制实现。就拿网上购物来说你只有让商品出库又让商品进入顾客的购物车才能构成一个完整的事务。 事务的隔离性 Isolation 当两个或者多个事务并发执行时为了保证数据的安全性将一个事物内部的操作与其它事务的操作隔离起来不被其它正在执行的事务所看到使得并发执行的各个事务之间不能互相影响。 事务的持久性 Durability 事务完成 (commit) 以后 DBMS 保证它对数据库中的数据的修改是永久性的即使数据库因为故障出错也应该能够恢复数据 ACD:是由mysql的redo log和undo log机制来保证的 l:隔离性是由my和ql事务的锁机制来实现保证的 3.事务并发存在的问题 事务处理不经隔离并发执行事务时通常会发生以下的问题 脏读 Dirty Read 一个事务读取了另一个事务未提交的数据。例如当事务 A 和事务 B 并发执行时当事务A 更新后事务 B 查询读取到 A 尚未提交的数据此时事务 A 回滚则事务 B 读到的数据就是无效的脏数据。事务B 读取了事务 A 尚未提交的数据 不可重复读 NonRepeatable Read 一个事务的操作导致另一个事务前后两次读取到不同的数据。 例如当事务A 和事务 B 并发执行时当事务 B 查询读取数据后事务 A 更新操作更改事务 B 查询到的数据此时事务B 再次去读该数据发现前后两次读的数据不一样。事务 B 读取了事务 A 已提交的数据 幻读 Phantom Read 幻读一个事务的操作导致另一个事务前后两次查询的结果数据量不同。例如 当事务A 和事务 B 并发执行时当事务 B 查询读取数据后事务 A 新增或者删除了一条满足事务 B 查询条件的记录此时事务B 再去查询发现查询到前一次不存在的记录或者前一次查询的一些记录不见了。 事务B 读取了事务 A 新增加的数据或者读不到事务 A 删除的数据 4.事务的隔离级别 MySQL 支持的四种隔离级别是 1 、 TRANSACTION_READ_UNCOMMITTED。未提交读。说明在提交前一个事务可以看到另一个事务的变化。这样读脏数据不可重复读和幻读都是被允许的。 2 、 TRANSACTION_READ_COMMITTED。已提交读。说明读取未提交的数据是不允许的。这个级别仍然允许不可重复读和幻读产生。 3 、 TRANSACTION_REPEATABLE_READ 。可重复读。说明事务保证能够再次读取相同的数据而不会失败但幻读仍然会出现。可以解决一部分的幻读问题 4 、 TRANSACTION_SERIALIZABLE。串行化。是最高的事务级别它防止读脏数据不可重复读和幻读。 具体的处理可以参考这篇博客: 数据库事务-CSDN博客 5.MySQL的事务处理命令 打开 MySQL 的 Command 命令行窗口测试以下命令 1 、 SELECT AUTOCOMMIT; 查看 MySQL 是否自动提交事务 0表示手动提交事务1表示自动提交事务设置事务提交方式为手动提交方式 set autocommit 0 ; BEGIN; 开启一个事务 COMMIT; 提交一个事务 ROLLBACK; 回滚一个事务到初始的位置 SAVEPOINT point1; 设置一个名字为 point1 的保存点 ROLLBACK TO point1; 事务回滚到保存点 point1 而不是回滚到初始状态 SET TX_ISOLATIONREPEATABLE-READ; 设置事务的隔离级别 SELECT TX_ISOLATION; 查询事务的隔离级别 五.MySQL的锁机制
1.表级锁行级锁 表级锁对整张表加锁。开销小加锁快不会出现死锁锁粒度大发生锁冲突的概率高并发度低。 行级锁对某行记录加锁。开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度高。 InnoDB的行锁是加在索引项上面的是给索引在加锁并不是给单纯的行记录在加锁;索引如果过滤条件没有索引的话使用的就是表锁而不是行锁!!! 所以说InnoDB既有行锁,也有表锁 2.排它锁和共享锁 排它锁 Exclusive 又称为 X 锁写锁。 共享锁 Shared 又称为 S 锁读锁。 X 和 S 锁之间有以下的关系 SS 可以兼容的 XS 、 SX 、 XX 之间是互斥的 一个事务对数据对象 O 加了 S 锁可以对 O 进行读取操作但不能进行更新操作。加锁期间其它事务能对O 加 S 锁但不能加 X 锁。 一个事务对数据对象 O 加了 X 锁就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。 显示加锁select ... lock in share mode强制获取共享锁select ... for update获取排它锁
3.InnoDB行级锁
recore lock:行锁
gap lock:间隙锁
next key lock:record lockgap lock;
1.行级锁 InnoDB 存储引擎支持事务处理表支持行级锁定并发能力更好。 InnoDB行锁是通过给索引上的索引项加锁来实现的而不是给表的行记录加锁实现的这就意味着只有通过索引条件检索数据InnoDB才使用行级锁否则InnoDB将使用表锁。 由于InnoDB的行锁实现是针对索引字段添加的锁不是针对行记录加的锁因此虽然访问的是InnoDB引擎下表的不同行但是如果使用相同的索引字段作为过滤条件依然会发生锁冲突只能串行进行不能并发进行。 即使SQL中使用了索引但是经过MySQL的优化器后如果认为全表扫描比使用索引效率更高此时会放弃使用索引因此也不会使用行锁而是使用表锁比如对一些很小的表MySQL就不会去使用索引。 通过普通索引加上的锁,实际上是根据普通索引映射到对应的主键,给对应的索引项加锁来实现的,因此通过不同的过滤条件,只要映射到了相同的索引项,也是被锁住的 2.间隙锁 当我们用范围条件而不是相等条件检索数据并请求共享或排他锁时 InnoDB 会给符合条件的已有数据记录的索引项加锁对于键值在条件范围内但并不存在的记录叫做“ 间隙 GAP)” InnoDB 也会对这个“ 间隙 ” 加锁这种锁机制就是所谓的间隙锁。举例来说 假如 user 表中只有 101 条记录 其userid 的值分别是 1,2,...,100,101 下面的 SQL select * from user where userid 100 for update ; 是一个范围条件的检索 InnoDB 不仅会对符合条件的 userid 值为 101 的记录加锁也会对 userid 大于 101 但是这些记录并不存在的 间隙 加锁防止其它事务在表的末尾增加数据。 InnoDB 使用间隙锁的目的为了防止幻读 以满足串行化隔离级别的要求对于上面的例子要是不使用间隙锁如果其他事务插入了 userid 大于 100 的任何记录那么本事务如果再次执行上述语句就会发生幻读。 间隙锁作用于普通索引. 使用select * from user where age20; 此时插入age20的用户数据是无法插入成功的,因为辅助索引是先按age进行排序存储到B树上的,然后按照主键的大小进行排序,此时间隙锁加到的(20,21],(21,25],(25,无穷]上的,而恰好age20,自增的主键是大于age20的主键12的,因此刚好加到间隙锁 此时进行范围查询加的是next key lock(也就是gap lockrecord lock)对大于20的数据加record lock,对之间的间隙加gap lock; 主键或者唯一键进行等值查询的时候,加的都是行锁(此时没有间隙锁gap lock),插入是不会受到影响的,不用担心间隙锁 当普通索引进行等值查询的时候,会对两边的间隙加间隙锁,对符合条件的数据加行锁 4.MVCC
先来了解一下undolog undo log 回滚日志保存了事务发生之前的数据的一个版本用于事务执行时的回滚操作同时也是实现多版本并发控制MVCC 下读操作的关键技术。 DB_TRX_ID:事务 ID DB_ROLL_PTR: 回滚指针 MVCC 是多版本并发控制 Multi-Version Concurrency Control 简称 MVCC 是 MySQL 中基于乐观锁理论实现隔离级别的方式用于实现已提交读和可重复读隔离级别的实现也经常称为多版本数据库。MVCC 机制会生成一个数据请求时间点的一致性数据快照 Snapshot) 并用这个快照来提供一定级别 语句级或事务级 的一致性读取。从用户的角度来看好象是数据库可以提供同一数据的多个版本系统版本号和事务版本号。 MVCC 多版本并发控制中读操作可以分为两类 1 、快照读 snapshot read 读的是记录的可见版本不用加锁。如select 2 、当前读 current read 读取的是记录的最新版本并且当前读返回的记录。如insertdeleteupdateselect...lock in share mode/for update MVCC 每一行记录实际上有多个版本每个版本的记录除了数据本身之外增加了其它字段 DB_TRX_ID 记录当前事务 ID DB_ROLL_PTR 指向 undo log 日志上数据的指针 已提交读 每次执行语句的时候都重新生成一次快照 Read View 每次 select 查询时。 为什么无法解决”不可重复读”? 因为每一次select都会重新产生一次数据快照其它事务更新后而且已提交的数据可以实时反馈到当前事务的select结果当中! 为什么无法解决”幻读”? 因为每一次select都会重新产生一次数据快照其它事务增加了和当前事务查询条件相同的新的数据并且已成功commit提交导致当前事务再次查询时数据多了 可重复读 同一个事务开始的时候生成一个当前事务全局性的快照 Read View 第一次 select 查询时。 为什么解决了”不可重复读”?为什么无法解决”幻读”? 第一次select产生数据快照其它事务虽然修改了最新的数据但是当前事务select时依然查看的是最初的快照数据 快照内容读取原则 版本未提交无法读取生成快照 版本已提交但是在快照创建后提交的无法读取 版本已提交但是在快照创建前提交的可以读取 当前事务内自己的更新可以读到 4.意向共享锁和意向排他锁
要获取一张表的共享锁S或者排它锁X最起码得确定这张表没有被其它事务获取过X锁!这张表(1千万)里面的数据没有被其它事务获取过行锁X锁!
因此当我们加表锁的时候,需要一行一行的扫描是否加过行锁,这样效率是十分低的,因此出现了意向锁 意向共享锁 IS 锁事务计划给记录加行共享锁事务在给一行记录加共享锁前必须先取得该表的 IS 锁。 意向排他锁 IX 锁事务计划给记录加行排他锁事务在给一行记录加排他锁前必须先取得该表的 IX 锁 意向锁是由InnoDB存储引擎获取行锁之前自己获取的 意向锁之间都是兼容的不会产生冲突 意向锁存在的意义是为了更高效的获取表锁表格中的X和S指的是表锁不是行锁 意向锁是表级锁协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行。
5.InnoDB表级锁 在绝大部分情况下都应该使用行锁因为事务和行锁往往是选择 InnoDB 的理由但个别情况下也使用表级锁 1 事务需要更新大部分或全部数据表又比较大如果使用默认的行锁不仅这个事务执行效率低而且可能造成其他事务长时间等待和锁冲突 2 事务涉及多个表比较复杂很可能引起死锁造成大量事务回滚。 如 LOCK TABLE user READ 读锁锁表 LOCK TABLE user WRITE; 写锁锁表 事务执行 ... COMMIT/ROLLBACK; 事务提交或者回滚 UNLOCK TABLES; 本身自带提交事务释放线程占用的所有表锁 6.死锁 MyISAM 表锁是 deadlock free 的 这是因为 MyISAM 总是一次获得所需的全部锁要么全部满足要么等待因此不会出现死锁。但在 InnoDB 中除单个 SQL 组成的事务外锁是逐步获得的即锁的粒度比较小这就决定了在 InnoDB 中发生死锁是可能的。 死锁问题一般都是我们自己的应用造成的和多线程编程的死锁情况相似大部分都是由于我们多个线程在获取多个锁资源的时候获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候不同的代码段应对这些表按相同的顺序进行更新操作以防止锁冲突导致死锁问题。 7.锁的优化建议
尽量使用较低的隔离级别 设计合理的索引并尽量使用索引访问数据使加锁更加准确减少锁冲突的机会提高并发能力 选择合理的事务大小小事务发生锁冲突的概率小 不同的程序访问一组表时应尽量约定以相同的顺序访问各表对一个表而言尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会 尽量用相等条件访问数据这样可以避免间隙锁对并发插入的影响 不要申请超过实际需要的锁级别 除非必须查询时不要显示加锁
