MySQL--索引、事务_mysql 修改列事务

索引

索引的概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。

事物的实现原理

事务是基于重做日志文件（redo log）和回滚日志（undo log）实现的。
每提交一个事物必须先将事务的所有日志写入到重做日志文件中进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性

每当有修改事务时，还会产生undo log，如果需要回滚，则根据undo log的反向语句进行逻辑操作，比如insert一条记录就会delete一条记录，undo log主要实现数据库的一致性

索引的作用

就一句话，加快查询数据的速度。

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
• 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
• 索引对于提高数据库的性能有很大的帮助。
  

索引使用场景

可以使用explain来进行查看SQL的执行：
要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间，所以磁盘空间需要充足。(存放在磁盘的.ibd文件里）

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

索引的使用场景

• 针对经常用在条件中的字段上
• 查找的次数远远高于修改的次数
• 表中的行数足够大

索引的使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

• 查看索引

show index from 表名;

• 创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

或者 alert table 表名 add fulltext index ‘索引名’(‘列名’);

• 删除索引

drop index 索引名 on 表名;

索引保存的数据结构主要为B+树，及hash的方式.

索引的分类

从使用者的角度分类：

主键索引（也有语义上的约束）：某一个属性组能唯一标识一条记录

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

唯一键索引（也有语义上的约束）
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别

• 主键索引只能有一个
• 唯一索引可能有多个

普通索引（没有语义上的约束，纯粹是为了提升查询速度而使用）

• 作用 : 快速定位特定数据
• 注意 :index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

全文索引（FULLTEXT）

• 百度搜索：全文索引
• 作用 : 快速定位特定数据
• 注意 :只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

聚集索引（索引之间有关系）和非聚簇索引

• 主键索引：叶子结点存储了整行的数据
• 非聚簇索引：叶子结点存储的时主键的值（通过主键再去找到真正的数据），也称为二级索引
• 聚簇索引和非聚簇索引的区别在于非聚簇索引的叶子结点存储的不是表中的数据，而存储的是该列对应的主键（行号）
  通常情况下，主键索引（聚簇索引查询只会查一次），而非聚簇索引需要会表查询多次
  注意：InnoDB引擎的主键索引是聚簇索引，二级索引是非聚簇索引，但是MyISAM引擎无论是主键索引还是二级索引都是非聚簇索引
  
  联合索引（key是由多个字段放在一起组成的）

覆盖索引：只需要在一张表里获取到sql所需要的所有列数据，无需回表，速度更快
如何实现覆盖索引：将被查询的字段建立道联合索引里面去
创建一个user表，并为name字段创建索引

create table user (

id int primary key,

name varchar(20),

sex varchar(5),

index(name)

)engine=innodb;
1
2
3
4
5
6
7
8
9
10
11

• 第一个SQL语句：

select id,name from user where name='shenjian';
1

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

• 第二个SQL语句：

select id,name,sex* from user where name='shenjian';
1

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

改进:如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user (

id int primary key,

name varchar(20),

sex varchar(5),

index(name, sex)

)engine=innodb;
1
2
3
4
5
6
7
8
9
10
11

可以看到：都能够命中索引覆盖，无需回表。

select id,name from user where name='shenjian';
1

select id,name,sex from user where name='shenjian';
1

回表查询

什么是回表查询

• 对于InnoDB来说，用非聚簇索引查找数据时，第一次得到的value可能是主键（聚簇索引），再根据这个主键去查找真正的数据的过程我们称为回表

非聚簇索引一定会回表查询吗？

• 不一定，如果查询语句所要求的的字段全部命中了索引，那么就不必在进行回表查询了。（一个索引包含所有需要查询字段的值，称之为"覆盖索引"）
  举个简单的例子，假设我们在员工表的年龄上建立了索引，那么进行selete score from student where score > 90 的查询时，在索引的叶子节点上，已经包含了score信心，不会再次进行回表查询

索引的注意事项

• 索引的创建是非常耗时的也会耗费大量的空间，数据量越大索引的创建时间也就越长，所以生产环境下慎用
• 索引它适用场景时读取比较多的情况，如果操作（新增、删除）比较多的不建议使用（B+树删除会重新构建，比较费时）
• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

面试题：什么情况下索引会不生效？

• 使用like查询有可能不生效，like’%xxx%'不走索引，而like’x%'生效（必须以x开头，不能以%开头）
• 尽量避免使用or查询，有可能会导致查询不生效
• 尽量不使用is not in查询语句，也会导致索引不生效
• ！=、<>导致索引不生效，尽量不要使用
• 对于组合索引来说一定要遵守最左匹配原则，否则索引也不生效。

事务

为什么要使用事务

准备测试表：

==drop table if exists accout; create table accout(
id int primary key auto_increment,
name varchar(20) comment ‘账户名称’,
money decimal(11,2) comment ‘金额’
);
insert into accout(name, money) values (‘阿里巴巴’, 5000),
(‘四十大盗’, 1000);

比如说，四十大盗把从阿里巴巴的账户上偷盗了2000元

– 阿里巴巴账户减少2000
update accout set money=money-2000 where name = ‘阿里巴巴’;
– 四十大盗账户增加2000
update accout set money=money+2000 where name = ‘四十大盗’;

假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，阿里巴巴的账户会减少2000，但是 四十大盗的账户上就没有了增加的金额。那么钱去哪里了呢，不翼而飞了？这时候我们就需要使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

事物的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。 在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

事务的四大特征（ACID）

• 原子性：要么全部成功，要么全部失败（补偿机制）
• 持久性：事务执行的结果一定要永久的保存下来
• 一致性：事务执行之前和执行之后，数据要保证修改的正确性
• 隔离性：多个事务在执行的事务要相互隔离

MySQL事务执行时的三个问题

• 脏读：事务A在执行的时候，读取到了正在执行的事务B的数据，事务B进行了回滚数据
• 不可重复读：事务A使用同一个查询条件，读取到的内容不一致（因为在这个过程中事务B修改了数据）
• 幻读：事务A使用相同的查询添加，两次查询的结果不一样，因为事务B进行了添加或删除了数据

经典面试题：不可重复度和幻读的区别？
二者的侧重点不同，不可重复读的侧重点是修改数据，而幻读的侧重点是添加或者删除数据

MySQL的隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

• 读未提交

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• 读已提交

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读，因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

• 可重入读

是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，）机制解决了该问题。

• 串行化

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

事务的使用

事务三板斧

• （1）开启事务：start transaction;
• （2）执行多条SQL语句
• （3）回滚或提交：rollback/commit;

说明：rollback即是全部失败，commit即是全部成功。
举例

start transaction;
– 阿里巴巴账户减少2000
update accout set money=money-2000 where name = ‘阿里巴巴’;
– 四十大盗账户增加2000
update accout set money=money+2000 where name = ‘四十大盗’;
commit;

MVCC

什么是MVCC

MVCC即多版本并发控制。
MVCC的实现是通过保存数据在某个时间点的快照来实现的，根据事务开始的时间不同，每个事物对同一张表同一时刻看到的数据可能是不一样的

MVCC的实现原理

对于InnoDB来讲，聚簇索引记录中包含三个隐藏的列：

• ROW ID：隐藏的自增ID，如果表没有主键，InnoDB会自动按ROW ID产生一个聚集索引树
• 事务ID：记录最后一次修改该记录的事务ID
• 回滚指针：记录这条记录的上一个版本

如图：首先insert语句向表t1中插入了一条数据，a字段为1，b字段为1，ROW ID也为1，事务ID假设也为1，回滚指针假设为null。当执行update t1 set b = 666 where a = 1时，大致步骤如下：

• 数据库会先满足a = 1 的行加排他锁
• 然后将原纪录复制到 undo 表空间当中
• 修改b字段的值为666，修改事务ID为2
• 并通过隐藏的回滚指针指向undo log中的历史记录
• 事务提交，释放前面满足a = 1的行所加的排他锁
  因此可以总结出MVCC实现的原理大致是：
  InnoDB每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个版本存放在undo log中，如果要执行更新操作，会将原记录放入undo log中，并通过隐藏的回滚指针指向undo log中的原纪录。其他事务此时需要查询时，就是查询undo log中这行数据的最后一个历史版本

MVCC最大的好处就是读不加锁，读写不冲突，极大增加了MySQL的并发性，通过MVCC，保证了事务ACID中的I（隔离性的特性）

锁

为什么要加锁？

多用户环境下保证数据库完整性和一致性，当多个用户并发的存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据的一致性

数据库的锁的分类（按照锁的粒度分）

在关系行数据库中，可以按照锁的粒度将数据库锁分为行级锁（InnoDB引擎）、表级锁（MYISAM引擎）和页级锁（BDB引擎）

行级锁：

• 概念：行级锁是MySQL中粒度最细的一种锁，表示只针对当期操作的行进行加锁，行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但是加锁的开销最大。
• 分类：行级锁分为共享锁和排他锁
• 优点：锁的粒度最小，发生锁冲突的概率最低，并发度也最高
• 缺点：开销大、加锁慢

页级锁：

• 概念：页级锁是MySQL中锁粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级锁冲突少但是速度慢。所以有了折中的页级锁，一次锁定相邻的一组数据，BDB支持页级锁
• 特点：开销和加锁时间中规中矩，介于表级锁和行级锁之间，也会出现死锁，并且并发度一般

表级锁：

• 概念：表级锁是MySQL中锁粒度最大的一种锁，表示对当前操作的整表进行加锁，它实现简单，资源消耗比较少，被大部分MySQL引擎支持，最长使用的InnoDB和MYISAM引擎都支持表级锁
• 分类：表级锁分为共享锁（表共享锁）和排他锁（表独占锁）
• 优点：开销小，加锁快，不会出现死锁
• 缺点：发生锁冲突的概率最高，并发度最低

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁
• Inno采用行级锁和表级锁，默认为行级锁

数据库的锁的分类（按照锁的类别分）

从锁的类别上来讲，有共享锁和排他锁

• 共享锁：又叫读锁。当用户读取数据时，对数据加上共享锁。共享锁可以同时加上多个
• 排他锁：又叫写锁。当用户写入数据时，对数据加上排他锁。排他锁只可以加一个，他和其他的锁排他锁、共享锁都相斥

数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务时确保在多个事务同时存取数据库中统一数据时不破坏事务的隔离性行和统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段
悲观锁

• 概念：假定会发生冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，知道提交事务，
• 实现方式：使用数据库的锁机制（行锁、表锁）
• 使用场景：适用于写比较多，读比较少的情况

乐观锁

• 概念：假设并不会发生冲突，只在提交操作操作的时候检查是否违反了数据完整性，在修改数据的时候把事务锁起来，通过version的方式来进行锁定
• 实现方式：一般会使用版本号机制或CAS算法实现
• 使用场景：适用于读比较多，写比较少的场景，即使真正发生冲突时，也可以省去锁的开销，加大系统的吞吐量

InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁的

例如：select * from tab where id = 1 for update；
for update 可以根据条件来完成行锁锁定，并且id是有索引键的列，如果id不是索引建那么InnoDB将完成表锁，并发将无从谈起

MySQL中什么是死锁？怎么解决？

死锁的概念

• 死锁是指两个或两个以上的事务在统一资源上相互占用，并请求锁定对方的资源，从而导致死循环的现象

解决死锁的方法：

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁的机会
• 在同一个事务当中，尽可能做到一次锁定所需要的所有资源，减少死锁产生的概率
• 对于非常容易产生死锁的业务部分，尝试升级索的力度，通过使用表级锁来减少死锁产生的概率

隔离级别与锁的关系

• 读未提交级别：读数据不需要加共享锁
• 读已提交级别：读操作需要加共享锁，但是在语句执行完以后释放共享锁
• 可重复读隔级别：读操作需要加共享锁，但是在事务执行完毕之后才可以释放共享锁

串行化隔离级别：该隔离级别锁定整个范围的建，并一直持有锁，直到事务完成

锁优化

• 使用较低的隔离级别
• 设计索引，尽量使用索引去访问数据，使加锁更加精确，从而减少锁冲突
• 使用合适的锁级别去加锁
• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 对于特定的事物，可以使用表锁来提高处理速度并减少死锁的可能

分库分表

为什么要分库分表？

分表

• 假如单表的数据量太大达到几千万条数据，会极大影响你sql执行的性能，分表就是把一个表的数据放到多个表中，然后操作的时候只操作一个表就可以了，这样可以控制每个表的数据量在可控范围内（比如每个表固定在200万以内）
  分库
• 分库也和分表是类似的。可以将一个库的数据拆分到多个库中，访问的时候访问一个库就行了

B树

B树它是一种多叉平衡搜索树，称为B树(有些地方写的是B-树，注意不要误读成"B减树")。

B树的命名和构造

B树的性质：

• 可以有多个孩子
• 所有的叶子节点都在同一层
• B树的插入是向叶子插入(第一次插入需要新建节点）
• 所有节点中的key，最多有x个（）
• B树的高度是在插入时根分裂才会生长
• B树的生长过程是向上的（与我们之前学过的二叉树不同）

B树的插入

B+树

B+树是B-树的变形，也是一种多路搜索树，把所有的key - value 全部保存在叶子上，保存起来。为所有叶子节点增加一个链指针组成了一个链表。

B+树的搜索与B-树基本相同，查找都一样快。缺点：B+树空间使用的更多了，优点：遍历所有的key-value
变得容易了（进行链表遍历）

为什么索引默认结构使用B+Tree，而不是B-Tree，hash，二叉树红黑树呢？

和B-Tree比较：

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B-树更小，如果把所有同一内部节点的关键字存放在同一块磁盘上，那么磁盘容纳的数量更多，一次性读入内存需要查找的关键字也就越多，相对IO读写次数就降低了
• 由于B+树的数据都存储在叶子节点，分支节点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支节点同样存储着数据，我们要想找到具体的数据，需要进行一次遍历才行，所以B+树更适合在区间查询的情况，更适合用作数据库索引

与hash对比：

• hash虽然可以快速定位，但是是没有顺序的，IO复杂度高
• 如果有大量重复键值对的情况下，hash效率就会很低（存在hash碰撞）

与红黑树对比：树的高度随着数据量的增加而增加，IO代价高