关于mysql:Mysql笔记2事务和索引

事务

• 事务就是要保障一组数据库操作，要么全副胜利，要么全副失败。
• 在 MySQL 中，事务反对是在引擎层实现的。
• ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

隔离性

事务并发问题：

脏读（dirty read）：一个事务读取了其它事务 update 后未提交的数据。

不可反复读（non-repeatable read）：一个事务读取了其它事务 update 或 delete 后已提交的数据。

幻读（phantom read）：一个事务读取了其它事务新 insert 曾经提交的数据（前后读取数量不统一）。

事务隔离级别

读未提交 RU（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。

读提交 RC（read committed）：一个事务提交之后，它做的变更才会被其余事务看到。

可反复读 RR（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是统一的。

串行化 Serial（serializable）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当呈现读写锁抵触的时候，后拜访的事务必须等前一个事务执行实现，能力继续执行。

InnoDB的 默认隔离级别 是可反复读(repeatable read)

实例

mysql> create table T(c int) engine=InnoDB;
mysql> insert into T(c) values(1);

读未提交：V1，V2，V3 值都是 2

读提交：V1=1，V2=2，V3=2

可反复读：V1=1，V2=1，V3=2（事务 A 在执行期间看到的数据前后必须是统一的）

串行化：V1=1，V2=1，V3=2（事务 B 执行时会被锁住，直到事务 A 提交后，才能够继续执行）

视图

在实现上，数据库外面会创立一个 MVCC 视图，拜访的时候以视图的逻辑后果为准。

读未提交 RU：间接返回记录上的最新值，没有视图概念。

查看级别：

读未提交：间接返回记录上的最新值，没有视图概念。

读提交 RC：在每个 SQL 语句开始执行的时候创立的，能够看到另外一个事务曾经提交的内容。

可反复读 RR：在事务启动时创立的，整个事务存在期间都用这个视图。

串行化 Serial：间接用加锁的形式来防止并行拜访，没有视图概念。

配置形式：将启动参数 transaction-isolation 的值设置成 READ-COMMITTED

查看级别：show variables like 'transaction_isolation'

注：Mysql5.7 前的版本为show variables like 'tx_isolation';

事务隔离的实现

MVCC：多版本并发管制，通过 undo log(回滚日志)版本链和 read-view（读视图）实现事务隔离

在 MySQL 中，每条记录在更新的时候除了记录变更记录，还会记录一条变更相同的回滚操作记录，前者记录在 redo log，后者记录在 undo log。

记录上的最新值，通过回滚操作，都能够失去前一个状态的值。

不同时刻启动的事务会有不同的 read-view（视图），在视图 A、B、C 外面，这一个记录的值别离是 1、2、4。

在可反复读隔离级别中，表中的数据其实曾经扭转，在后面的视图里，须要查找某条记录时，是通过取以后数据，再取视图对应的回滚段回滚到该视图的值。

回滚日志的删除

当没有事务再须要用到这些回滚日志时，回滚日志会被删除。就是当零碎里没有比这个回滚日志更早的 read-view 的时候。

长事务的问题

1. 长事务提交之前，数据库外面它可能用到的回滚记录都必须保留，会大量占用存储空间。

2. 在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即便长事务最终提交，回滚段被清理，文件也不会变小。

   • ibdata 文件是共享表空间数据文件。5.7 版本反对独自配置 undo log 的门路和表空间文件。
3. 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务的启动形式

倡议应用 set autocommit=1, 通过显式语句的形式来启动事务。

显式启动事务：

begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

1. START TRANSACTION 后，不论 autocommit 是 1 还是 0。只有当 commit 数据才会失效，ROLLBACK 后就会回滚。
2. 当 autocommit 为 0 时，不论有没有 START TRANSACTION。只有当 commit 数据才会失效，ROLLBACK 后就会回滚。
3. 如果 autocommit 为 1，并且没有 START TRANSACTION。会主动 commit。调用 ROLLBACK 是没有用的。即使设置了 SAVEPOINT。

隐式开启事务

set autocommit=0，这个命令会将这个线程的主动提交关掉，不论有没有 begin，start transaction 都须要 commit 来提交事务。这个事务继续存在直到你被动执行 commit 或 rollback 语句，或者断开连接。

commit work and chain

提交事务并主动启动下一个事务。

查问长事务

你能够在 information_schema 库的 innodb_trx 这个表中查问长事务。

// 查问超过 60 秒的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

小结

如何防止长事务对业务的影响？

开发端

1. 确认是否应用了 set autocommit=0
2. 去掉不必要的只读事务

3. 设置 MAX_EXECUTION_TIME 来管制每个语句执行的最长工夫，防止单个语句意外执行太长时间

   1. 全局设置：SET GLOBAL MAX_EXECUTION_TIME=1000;
   2. 对某个 session 设置：SET SESSION MAX_EXECUTION_TIME=1000;
   3. 对某个语句设置：SELECT max_execution_time=1000 SLEEP(10), a.* from test a;

数据端

1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
2. Percona 的 pt-kill 这个工具不错，举荐应用；
3. 在业务功能测试阶段要求输入所有的 general_log，剖析日志行为提前发现问题；
4. 如果应用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的呈现大事务导致回滚段过大，这样设置后清理起来更不便。

备注：innodb_undo_tablespaces 是管制 undo 是否开启独立的表空间的参数 为 0 示意：undo 应用零碎表空间，即 ibdata1 不为 0 示意：应用独立的表空间，个别名称为 undo001 undo002，寄存地址的配置项为：innodb_undo_directory 个别 innodb_undo_tablespaces 默认配置为 0，innodb_undo_directory 默认配置为以后数据目录

查问日志开启

办法一：

mysql>set global general_log_file='/tmp/general.lg'; #设置门路   
mysql>set global general_log=on; # 开启 general log 模式   
mysql>set global general_log=off; # 敞开 general log 模式 

mysql>set global general_log=on; # 开启 general log 模式

mysql>set global general_log=off; # 敞开 general log 模式

命令行设置即可, 无需重启 在 general log 模式开启过程中，所有对数据库的操作都将被记录 general.log 文件

办法二：

将日志记录在表中 set global log_output=’table’ 运行后，能够在 mysql 数据库下查找 general_log 表 二、查问日志敞开 查看是否是开启状态：

mysql> show global variables like '%general%'; 

敞开

mysql> set global general_log = off; // 敞开查问日志

索引

索引是帮忙 MySQL 高效获取数据的 排好序的数据结构，索引存储在文件里。

索引的常见模型

哈希表

MySQL 的 Memory 存储引擎反对 Hash 存储

1. 哈希表是一种以键 – 值（key-value）存储数据的构造，咱们只有输出待查找的键即 key，就能够找到其对应的值即 Value。
2. 把值放在数组里，用一个哈希函数把 key 换算成一个确定的地位，而后把 value 放在数组的这个地位。
3. 多个 key 值通过哈希函数的换算，会呈现同一个值的状况。解决这种状况的一种办法是，拉出一个链表。
4. 雷同哈希值的解决步骤：首先，通过哈希函数算出 key；而后，按程序遍历，找到后果。
5. 哈希索引做区间查问的速度是很慢的，这种构造实用于只有等值查问的场景。

有序数组

有序数组在等值查问和范畴查问场景中的性能十分优良。

• 有序数组可能解决 hash 函数不能满足反对疾速范畴查问。
• 二分查找时间复杂度是 O（log n），一般查找是 O（n），数组已排序时用二分法查问最快。
• 如果仅仅看查问效率，有序数组就是最好的数据结构了。
• 缺点是针对插入和删除场景，须要移动前面的整个记录，代价太高。
• 有序数组实用于动态搜索引擎。

搜寻树

mysql 索引存储构造和特点：https://blog.csdn.net/bible_r…

B 树和 B + 树：https://blog.csdn.net/u014453…

动静计算二叉树：https://www.cs.usfca.edu/~gal…

二叉树

第一个父节点开始跟指标元素值比拟，如果相等则返回以后节点，如果指标元素值小于以后节点，则挪动到左侧子节点进行比拟，大于的状况则挪动到右侧子节点进行比拟，重复进行操作最终挪动到指标元素节点地位。

毛病：自增整形字段作为建设索引时，新增索引会总是增加到右侧，导致查找时和没加索引一样。

均衡二叉树（红黑树）

红黑树会左旋、右旋对构造进行调整，始终保障左子节点值 < 父节点值 <= 右子节点值的规定。

毛病：每个父节点只能存在两个子节点，在数据量大的时候，深度也很大。

B-Tree

适当地减少每个树节点能存储的数据个数

• 度(Degree)- 节点的数据存储个数，每个树节点中数据个数大于 15/16*Degree（未验证）时会主动决裂，调整结构
• 叶节点具备雷同的深度，左子树跟右子树的深度统一
• 叶节点的指针为空
• 节点中的数据 key 从左到右递增排列

长处：解决数据量过大时整棵树的深度过长的问题。雷同数量的数据只须要更少的层，雷同深度的树能够存储更多的数据，查找的效率天然会更高。

毛病：范畴查的话，BTree 构造每次都要从根节点查问一遍，效率会有所升高。

B+Tree

B+ 树结构没有在所有的节点里存储记录数据，而是只在最上层的叶子节点存储，下层的所有非叶子节点只寄存索引信。

• 操作系统贮存数据的最小单位是页（page），一页假如是 4K 大小（由操作系统决定），对内存和磁盘读取数据是按一页的整数倍读取的。
• 节点数据总量越大，须要执行的 IO 操作越多，破费的工夫也越长，因而为了进步性能，数据库会倡议咱们一个大节点只存储一页 4K 大小的数据
• Degree = 内存页大小（4K）/ 单个索引值字节大小；

B+ 树中，每个节点为都是一个页，每次新建节点的时候，就会申请一个页空间。

B+ 树每个叶子节点都指向下一个叶子节点：

如果咱们进行范畴查找 where id > 4 的记录，咱们只须要先找到 id = 4 的记录后天然就能通过叶子节点间的双向指针不便地查问出大于 4 的所有记录。

其余

AVL 树(高度均衡树)：左右子树的高度之差的绝对值（均衡因子）最多为 1 的一种二叉树。

InnoDB 的索引模型

InnoDB 应用 B+ 树索引模型，数据都是存储在 B+ 树中。每张表的每个索引对应一棵 B+ 树。

B+ 树可能很好地配合磁盘的读写个性，缩小单次查问的磁盘拜访次数。

1. 在 InnoDB 中，每一张表其实就是多个 B+ 树，即一个主键索引树和多个非主键索引树。
2. 执行查问的效率，应用主键索引 > 应用非主键索引 > 不应用索引。
3. 如果不应用索引进行查问，则从主索引 B+ 树的叶子节点进行遍历。

示例：

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

依据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引：叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引：叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和一般索引的查问有什么区别？

• 主键索引 是惟一且有序的，非主键索引，是须要找到后果 ID，回表，获取最初的后果的。
• select * from T where k=5，即一般索引查问形式，则须要先搜寻 k 索引树，失去 ID 的值为 500，再到 ID 索引树搜寻一次。这个过程称为回表。
• 也就是说，基于非主键索引的查问须要多扫描一棵索引树。因而，咱们在利用中应该尽量应用主键查问。

索引保护

应用自增主键，就能够保障新的 ID 肯定是在叶子节点最左边，不会影响后面的数据。

页决裂

B+ 树为了保护索引有序性，在插入新值的时候须要做必要的保护。

如果插入行 ID 自增则只需在前面插入一个新记录，否则须要辑上移动前面的数据，空出地位。

如果所在的数据页曾经满了，依据 B+ 树的算法，这时候须要申请一个新的数据页，而后移动局部数据过来。这个过程称为页决裂。

影响：1. 性能天然会受影响。2. 整体空间利用率升高

页合并

当相邻两个页因为删除了数据，利用率很低之后，会将数据页做合并。

采纳自增主键避免页决裂，逻辑删除并非物理删除避免页合并。

自增主键

自增主键是指自增列上定义的主键：NOT NULL PRIMARY KEY AUTO_INCREMENT。

主键长度越小，一般索引的叶子节点就越小，一般索引占用的空间也就越小。

问题 1：

假如你的表中的确有一个惟一字段，比方字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

答案：不必业务字段做主键：

1. 业务字段不肯定是递增的，有可能会造成主键索引的页决裂，导致性能不稳固。
2. 二级索引存储的值是主键，如果应用业务字段占用大小不好管制，如果业务字段过长可能会导致二级索引占用空间过大，利用率不高。

问题 2：

有没有什么场景适宜用业务字段间接做主键的呢？

答案：只有一个索引，且该索引必须是惟一索引。因为没有其余索引，所以也就不必思考其余索引的叶子节点大小的问题。

问题 3：

重建一般索引和主键索引适合吗？

alter table T drop index k;
alter table T add index(k);

alter table T drop primary key;
alter table T add primary key(id);

答案：如果删除，新建主键索引，会同时去批改一般索引对应的主键索引，性能耗费比拟大。
删除重建一般索引影响不大，不过要留神在业务低谷期操作，防止影响业务。

回表

回到主键索引树搜寻的过程，咱们称为回表

问题：一下 SQL 执行了几次回表？

select * from T where k between 3 and 5

答案：这个查问过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

执行流程：

1. 在 k 索引树上找到 k=3 的记录，获得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，获得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环完结。

笼罩索引

索引 k 曾经“笼罩了”咱们的查问需要，咱们称为笼罩索引。笼罩索引是一个罕用的性能优化伎俩。

select id from T where k between 3 and 5

1. ID 的值曾经在 k 索引树上了，因而能够间接提供查问后果，不须要回表。
2. 查问读到 5 之后，还须要再读取下一个的值，判断是否满足条件，如果不满足，才真正完结循环，共读取了 3 条记录
3. MySQL 扫描行数是 2

联结索引

mysql 联结索引详解：https://cloud.tencent.com/dev…

联结索引是每个树节点中蕴含多个索引值，单列索引其实也能够看做联结索引。

1. 在通过索引查找记录时，会先将联结索引中第一个索引列与节点中第一个索引值进行匹配
2. 匹配胜利接着匹配第二个索引列和索引值，直到联结索引的所有索引列都匹配完
3. 如果过程中呈现某一个索引列与节点相应地位的索引值不匹配的状况，则无需再匹配节点中残余索引列，返回下一个节点。

联结索引的技巧

1. 笼罩索引：如果查问条件应用的是一般索引（或是联结索引的最左准则字段），查问后果是联结索引的字段或是主键，不必回表操作，间接返回后果，缩小 IO 磁盘读写读取正行数据
2. 最左前缀：联结索引的最左 N 个字段，也能够是字符串索引的最左 M 个字符
3. 联结索引：依据创立联结索引的程序，以最左准则进行 where 检索，比方（age，name）以 age=1 或 age= 1 and name=‘张三’能够应用索引，单以 name=‘张三’不会应用索引，思考到存储空间的问题，还请依据业务需要，将查找频繁的数据进行靠左创立索引。
4. 索引下推：like ‘hello%’and age >10 检索，MySQL5.6 版本之前，会对匹配的数据进行回表查问。5.6 版本后，会先过滤掉 age<10 的数据，再进行回表查问，缩小回表率，晋升检索速度

最左前缀准则

索引项是依照索引定义外面呈现的字段程序排序的。

问题：在建设联结索引的时候，如何安顿索引内的字段程序？

1. 须要优先思考采纳能够少保护索引的程序。如建 (a,b) 索引后，不再须要建 (a) 索引。
2. 如果须要 (a),(b) 独自查问，且 (b) 字段较大，则优先将空间大的建为联结索引(b,a)，小的独自索引(a)

mysql 会始终向右匹配直到遇到范畴查问（>、<、between、like）就进行匹配。范畴列能够用到索引，然而范畴列前面的列无奈用到索引。即，索引最多用于一个范畴列，因而如果查问条件中有两个范畴列则无奈全用到索引。

索引下推(Mysql5.6 引入)

能够在索引遍历过程中，对索引中蕴含的字段先做判断，间接过滤掉不满足条件的记录，缩小回表次数。

示例：索引(name,age)

select * from tuser where name like '张 %' and age=10;

Mysql5.5

只能匹配到 name like ‘ 张 %’ 的数据 ID，一条条回表查问，共回表 4 次。

Mysql5.6+

索引外部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过，只须要对 ID4、ID5 这两条记录回表取数据判断，就只须要回表 2 次。

小结

在满足语句需要的状况下，尽量少地拜访资源是数据库设计的重要准则之一。

问题：

CREATE TABLE `geek` (`a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

查问语句

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

请问“ca”“cb”这两个索引是否必须？

答案：ca 能够去掉，cb 须要保留。

解析：

1. 主键 a，b 的聚簇索引组织程序相当于 order by a,b，也就是先按 a 排序，再按 b 排序，c 无序。
2. 索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键，这个跟索引 c 的数据是截然不同的。
3. 索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键