正文

mysql：索引事务(代码片段)

Icedzzz  Icedzzz  2022-12-13  305

关键词：

参考：
狂神说Mysql视频
高性能Mysql

文章目录

1.索引简介

(1) 索引的作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.

高性能Mysql中描述索引的优点：

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变成顺序IO
任何判断一个索引的建立是否适合某个查询：
一星：索引将相关记录放在一起
二星：如果索引中数据顺序和查找中的排序顺序一致
三星：如果索引中的列包含了查询中需要的全部列

(2) 索引分类

Primary Key 主键索引 (某一个属性组能唯一标识一条记录,确保数据的唯一性,仅只有一个)
Unique Key 唯一索引 (避免同一个表中某数据列中的值重复，唯一索引可以有多个)
Index 常规索引 (快速定位特定数据,应加在查询找条件的字段,不宜添加太多)
FullText Key 全文索引 (快速定位特定数据,只能用于CHAR、Text数据列类型)

(3) 创建索引

在创建表时创建索引

CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );

在已经存在的表上创建索引
CREATE:

  CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;

ALTER:

 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;

其他

#删除索引
DROP INDEX 索引名 ON 表名字;
#删除主键索引
 ALTER TABLE 表名 DROP PRIMARY KEY;

显示索引信息

SHOW INDEX FROM 表名字

(4) 索引引擎

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

(4) 索引数据结构

**hash类型的索引：**查询单条快，范围查询慢
btree类型的索引： b+树，层数越多，数据量指数级增长（innodb默认支持它）

2.EXPLAIN关键字

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.
如：

EXPLAIN SELECT * from user_info WHERE  id < 300;

EXPLAIN查询结果包括：

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

一百万条数据测试

创建app_user：

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

EXPLAIN查看无索引条件下：

EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'

在没有索引情况下，查询了993605行这条数据；

创建索引：

CREATE INDEX idx_app_user_name ON app_user(name);

仅查询一行即可查到该条数据

3. 索引原理

B-Tree

在MySQL中，索引是存储在引擎层而不是服务器层实现的。大多数Mysql索引均采用B-Tree数据结构作为索引，但不同存储引擎以不同的方式使用B-Tree索引，性能也各不相同。例如MyISAM采用前缀压缩技术使得索引更小，但InnoDB按照原始数据格式进行存储；MyISAM索引采用数据的物理位置引用被索引的行，InnoDB根据主键引用被索引的行。

B树（B-树）是一种多路搜索树（并不是二叉的），浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。叶子节点比较特殊，指向被索引的数据。

B-树的特性：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

B-树这样定义做有什么好处呢？

中间节点不保存数据，那么就可以保存更多的索引，减少数据库磁盘IO的次数。
因为中间节点不保存数据，所以每一次的查找都会命中到叶子节点，而叶子节点是处在同一层的，因此查询的性能更加的稳定。
所有的叶子节点按顺序链接成了链表，因此可以方便的进行范围查找。

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

IO次数取决于b-数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。 这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
B-树的适用范围：全键值、键值范围或键前缀(只适用于最左前缀)查找。
B-树索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引的列
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

B+Tree索引

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；B+树的非叶子结点仅仅存储着关键字信息和儿子的指针，**B+树中的数据都存储在叶子结点上，也就是其所有叶子结点的数据组合起来就是完整的数据.**因此每个磁盘块包含的关键字信息会更多。这样也就决定了加载一个磁盘块可以获取到更多的关键字，可以减少IO操作，
一次IO操作相当于成百上千次的内存比较.
在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构
B+的特性：

所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
更适合文件索引系统；
B-Tree与B+Tree 的区别

B-树的关键字和记录是放在一起的， B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。**因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。**尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。

为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

磁盘读取原理
索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。
盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。
当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。
由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

为什么要用B-Tree
由B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为(O(h)=O(log_dN))。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。
为什么Innodb选择B+树而不是B树？

B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

Hash索引

hash索引基于hash表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会所有的索引列计算一个Hash码，Hash是一个较小的值(一般用crc32,MD5为了避免hash碰撞所以通常过长)。Hash索引将所有的hash码存储在索引中，同时在hash表中保存指向每个数据行的指针。

hash索引数据并不是按照索引值排序存储的，所以无法用于排序
hash索引不支持部分索引列匹配查找，因为hash索引始终是使用索引列的全部内容来计算hash值。
hash索引只支持等值比较查询，不支持范围查询
访问hash索引的数据非常快，除非有很多hash冲突。当出现hash冲突时，则存储引擎必须遍历链表中所有的行指针，逐行比较，直到找到所有符合条件的行。如果hash冲突哼多的话，一些维护操作的代价也很高。
**InnoDB引擎支持一个特殊功能——自适应hash索引。**当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个Hash索引。

索引的方式

前缀索引

如果希望给一个很长的字符串上添加索引，那么可以考虑前缀索引。
前缀索引：在对一个比较长的字符串尽心索引时，可以仅索引开始的一部分字符，这样可以大大的节约索引空间，从而提高索引效率，但是这样会降低索引的选择性。

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。innodb的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。当表有聚簇索引时，数据行实际上是存储在索引的叶子页中。 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。InnoDB将通过主键聚集数据，如果没有主键则会选择一个唯一的非空索引代替。

聚簇索引的优点：

可以把相关数据保存在一起
数据访问更快(聚集索引将索引和数据保存在同一个b-tree中)
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点：

聚簇数据提高了IO性能，如果数据全部放在内存中，则访问的顺序就没那么重要了
插入速度严重依赖插入顺序。按主键的顺序插入是速度最快的。但如果不是按照主键顺序加载数据，则需在加载完成后最好使用optimize table重新组织一下表
更新聚簇索引列的代价很高。因为会强制Innodb将每个被更新的行移动到新的位置
基于聚簇索引的表在插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂的问题。页分裂会导致表占用更多的磁盘空间。 当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。而分裂会导致表占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或由于页分裂导致数据存储不连续的时
非聚集索引比想象的更大，因为二级索引的叶子节点包含了引用行的主键列
非聚集索引(二级索引)访问需要两次索引查找(非聚集索引中叶子节点保存的行指针指向的是行的主键值)，对于innodb自适应哈希索引可以减少这样的重复工作
二次索引需要两次索引查找，而不是一次

重要：InnoDB二级索引中保存的 ”行指针“的实质不是指向行的物理位置的指针，而是行的主键值。

InnoDB中聚簇索引就是表，而MyISAM则是独立的行存储。这样的策略减少了当出现移动或数据页分裂时二级索引的维护工作，使用主键作为指针会让二级索引占用更多的空间，但换来的好处是，InnoDB在移动行时，无需更新二级索引的指针。

**注意，**如果使用乱序的值作为主键(如UUID)，不仅花费的时间更长，索引占用的空间也更大，因为新行的主键值不一定比之前插入的大，索引无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置并分配空间。（会增加额外的工作，和数据分布不够优化）

写入的目标页可能已经刷到磁盘上并从缓冲中删除，或还没被加载到缓存中，innodb在插入之前不得不先找到然后从磁盘中读取目标页到内存中。会导致大量的随机IO
因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致大量移动数据，一次插入需要修改多个页(最少三页)而不是一个页
由于频繁的页分裂，页会变得稀疏并不被规则填充，所以数据会有碎片

为什么innodb这么执着的需要搞一个聚簇索引呢：因为一个数据表中的数据总得有且只有一种排序方式来存储在磁盘上,因此这是必须的。这也是innodb推荐我们使用自增主键的原因，因为自增主键自增且连续，在插入的时候只需要不断的在数据后面追加即可。

覆盖索引
当一个索引包含（或者说是覆盖）需要查询的所有字段的值时，我们称之为覆盖索引。

查询的数据列只用从索引中就能够得到，不必从数据表中读取
查询列要被所使用的索引覆盖
索引是高效找到行的一个方式，当能通过检索索引就可以读取到想要的数据，那就不需要再到数据表中读取行了。
如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫覆盖索引
是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）

4. 存储引擎对比

InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

表级锁和⾏级锁对⽐：

表级锁： MySQL中锁定 粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低，MyISAM和 InnoDB引擎都⽀持表级锁。
⾏级锁： MySQL中锁定 粒度最⼩ 的⼀种锁，只针对当前操作的⾏进⾏加锁。⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种:
Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。
相关知识点：

innodb对于⾏的查询使⽤next-key lock
Next-locking keying为了解决Phantom Problem幻读问题
当查询的索引含有唯⼀属性时，将next-key lock降级为record key
Gap锁设计的⽬的是为了阻⽌多个事务将记录插⼊到同⼀范围内，⽽这会导致幻读问题的产⽣
有两种⽅式显式关闭gap锁：（除了外键约束和唯⼀性检查外，其余情况仅使⽤record lock）
A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

MyISAM不支持外键，InnoDB支持
MyISAM只缓存索引，不缓存真实数据，InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

5.事务

事务是逻辑上的⼀组操作，要么都执⾏，要么都不执⾏。
事务的四大特性(ACID):

原⼦性（Atomicity）： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么完全不起作⽤；
⼀致性（Consistency）： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
隔离性（Isolation）： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
持久性（Durability）： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

并发事务带来的问题：脏读、丢失修改、不可重复读和幻读
(1) 脏读（Dirty read） : 当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
(2) 丢失修改（Lost to modify） : 指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失
(3) 不可重复读（Unrepeatableread） : 指在⼀个事务内多次读同⼀数据。在这个事务还没有结束时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。
(4) 幻读（Phantom read） : 幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。
事务的隔离级别：
(1) READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
(2) READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据， 可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
(3) REPEATABLE-READ(可重复读)： 对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
(4) SERIALIZABLE(可串⾏化)： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读。
InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使⽤的是Next-Key Lock 锁算法，因此可以避免幻读的产⽣，这与其他数据库系统(如SQL Server) 是不同的。所以说InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串⾏化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都是 READCOMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使⽤ REPEAaTABLE-READ（可重读）并不会有任何性能损失。InnoDB 存储引擎在分布式事务的情况下⼀般会⽤到 SERIALIZABLE(可串⾏化) 隔离级别。

锁机制

：mysql索引和事务(代码片段)

文章目录一：索引（1）索引基本概念（2）使用场景（3）MySQL索引使用A：查看索引B：创建索引C：删除索引（4）MySQL索引背后的数据结构A：B树B：B+树二：事务（查看详情

mysql入门篇(代码片段)

MySql入门篇（二）索引定义：排好序的快速查找的数据结构索引分类索引SQL在创建表时创建索引在以创建的表上添加索引a.通过create语法创建b.通过alter语法创建删除索引索引的执行过程事务事务的定义事务的特征(ACID)... 查看详情

mysql数据库——事务和索引(代码片段)

...事务隔离级别有哪些？MySQL的默认隔离级别：二、索引：索引的作用：索引的分类：索引准则:索引的数据结构：一、事务：事务是逻辑上的一组操作，要么查看详情

mysql索引事务及存储引擎(代码片段)

MySQL索引、事务及存储引擎MySQL索引索引的概念索引的作用创建索引的原则依据索引的分类和创建普通索引（最基本的索引）唯一索引主键索引组合索引全文索引删除索引MySQL事务概念事务的ACID特点事务之间的相互影响MySQ... 查看详情

不要停！进一步了解mysql索引事务与存储引擎！(代码片段)

mysql索引事务与存储引擎一.索引1.索引的简介2.索引的作用3.创建索引的原则依据4.索引的分类与创建（1）普通索引（2）唯一索引（3）主键索引（4）组合索引（5）全文索引5.查看索引6.索引... 查看详情

mysql索引事务(代码片段)

索引概念索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(类似于c语言的链表通过指针指向数据记录的内存地址)。使用索引后可以不用扫描全表来定位某行的数据，而是先通... 查看详情

mysql索引事务与存储引擎(代码片段)

MySQL索引、事务与存储引擎索引的概念数据库索引●是一个排序的列表，存储着索引值和这个值所对应的物理地址●无须对整个表进行扫描，通过物理地址就可以找到所需数据●是表中一列或者若干列值排序的方法●需要... 查看详情

mysql面试题积累(代码片段)

...SETAUTOCOMMIT=0禁止自动提交SETAUTOCOMMIT=1开启自动提交数据库索引种类？索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度MySQL索引... 查看详情

面向校招mysql知识点整理(代码片段)

目录基础数据库三范式InnoDB与MyISAMMySQL执行查询过程索引索引模型哈希模型有序数组模型搜索树模型BST和AVL等二叉树模型B树模型B+树主键索引和非主键索引回表索引维护(页分裂与页合并)覆盖索引最左前缀原则前缀索引索引下... 查看详情

面向校招mysql知识点整理(代码片段)

mysql的索引与事务(代码片段)

作者：敲代码の流川枫博客主页：流川枫的博客专栏：和我一起学java语录：Stayhungrystayfoolish给大家推荐一款好用的神器Apifox=Postman+Swagger+Mock+JMeter。集接口文档工具、接口Mock工具、接口自动化测试工具... 查看详情

mysql数据库之索引事务存储引擎(代码片段)

一、索引的概念①索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。②使用索引后可以不用扫描全表来定位某行... 查看详情

mysql-约束存储引擎事务索引视图(代码片段)

MySQL—约束、存储引擎、事务1、约束1.1唯一性约束（unique）案例：给某一列添加unique案例：给两个列或者多个列添加unique1.2主键约束案例：如何给一张表添加主键约束：1.2.1主键的特点1.2.2主键相关的术语1.... 查看详情

mysql索引与事务详解(代码片段)

MySQL索引与事务详解一、前言?上一章我们讲解了MySQL的手工编译安装流程以及相关的数据库操作命令（sql语句），本文将要详细介绍MySQL索引与事务的概念及原理，并初步了解MySQL数据库视图概念，简述数据库的存储过程。二、索... 查看详情

mysql入门指南5（约束，索引，事务）(代码片段)

... 3.UNIQUE 4.FOREIGN KEY 5.CHECK二、索引三、事务 1.事务介绍 2.事务隔离级别 3.事务ACID四、最后的话一、约束约束用于确保数据库的数据满足特定的商业规则。在MySQL... 查看详情

mysql系列你不知道的视图触发器存储过程函数事务索引语句(代码片段)

...，pymysql怎么用？函数、增/删/改/查/return值内置函数事务索引！一、视图视图是查询命令结果构成的一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【查看详情

mysql学习10：视图&事务&索引(代码片段)

视图View视图的作用视图可以看作是一个虚拟的表，可将一些表的关系结合起来进行查看，并实时更新（表的数据变化，视图也会随之变化，因此视图并非表的备份）。视图用来方便查看，因此只具备查... 查看详情

mysql数据库mysql的索引和事务（扫盲必备）(代码片段)

文章目录1.索引1.1概念1.2作用1.3索引的原理1.3.1减少磁盘的访问次数是构建索引的核心思想1.3.2B+树适用实现索引的底层1.4适用场景1.5使用语句1.5.1查看索引1.5.2创建索引1.5.3删除索引2.事务2.1概念2.2为什么使用事务2.3四大属性2.3.... 查看详情