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

事务A	事务B
启动事务A，查询得到值1	启动事务B
	查询得到值1
	将1改为2
查询得到值V1
	提交事务B
查询得到值V2
提交事务A
查询得到值V3

读未提交：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。

记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

当前值	4	read-view C
回滚段	将4改成3
回滚段	将4改成2	read-view B
回滚段	将2改成1	read-view A

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

在可重复读隔离级别中，表中的数据其实已经改变，在前面的视图里，需要查找某条记录时，是通过取当前数据，再取视图对应的回滚段回滚到该视图的值。

回滚日志的删除

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

长事务的问题

长事务提交之前，数据库里面它可能用到的回滚记录都必须保留，会大量占用存储空间。
在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。
- ibdata文件是共享表空间数据文件。 5.7版本支持单独配置undo log的路径和表空间文件。
除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式

建议使用 set autocommit=1, 通过显式语句的方式来启动事务。

显式启动事务：

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

START TRANSACTION 后，不管autocommit 是1还是0 。只有当commit数据才会生效，ROLLBACK后就会回滚。
当autocommit 为 0 时，不管有没有START TRANSACTION。只有当commit数据才会生效，ROLLBACK后就会回滚。
如果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

小结

如何避免长事务对业务的影响？

开发端

确认是否使用了 set autocommit=0
去掉不必要的只读事务
设置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;

数据端

监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
Percona 的 pt-kill 这个工具不错，推荐使用；
在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
如果使用的是 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存储

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。
把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。
多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。
相同哈希值的处理步骤：首先，通过哈希函数算出 key；然后，按顺序遍历，找到结果。
哈希索引做区间查询的速度是很慢的，这种结构适用于只有等值查询的场景。

有序数组

有序数组在等值查询和范围查询场景中的性能非常优秀。

有序数组能够解决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+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

在 InnoDB 中，每一张表其实就是多个 B+ 树，即一个主键索引树和多个非主键索引树。
执行查询的效率，使用主键索引 > 使用非主键索引 > 不使用索引。
如果不使用索引进行查询，则从主索引 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：

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

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

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

问题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）。

执行流程：

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

覆盖索引

索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。覆盖索引是一个常用的性能优化手段。

select id from T where k between 3 and 5

ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。
查询读到5之后，还需要再读取下一个的值，判断是否满足条件，如果不满足，才真正结束循环，共读取了3条记录
MySQL 扫描行数是 2

联合索引

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

联合索引是每个树节点中包含多个索引值，单列索引其实也可以看做联合索引。

在通过索引查找记录时，会先将联合索引中第一个索引列与节点中第一个索引值进行匹配
匹配成功接着匹配第二个索引列和索引值，直到联合索引的所有索引列都匹配完
如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况，则无需再匹配节点中剩余索引列，前往下一个节点。

联合索引的技巧

覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据
最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

最左前缀原则

索引项是按照索引定义里面出现的字段顺序排序的。

问题：在建立联合索引的时候，如何安排索引内的字段顺序？

需要优先考虑采用可以少维护索引的顺序。如建(a,b)索引后，不再需要建(a)索引。
如果需要(a),(b)单独查询，且(b)字段较大，则优先将空间大的建为联合索引(b,a)，小的单独索引(a)

mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

索引下推(Mysql5.6引入)

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

示例：索引(name,age)

id	Name	Age
1	李一	10
2	王二	20
3	张三	5
4	张四	10
5	张五	10
6	张六	40

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 需要保留。

解析：

主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。
索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键，这个跟索引 c 的数据是一模一样的。
索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键