MySQL 索引事务

admin管理员组

文章数量:1570208

目录

• 1. 索引
• • 1.1 概念
  • 1.2 索引的使用
  • 1.2.1 查看索引
  • • 1.2.2 创建索引
    • 1.2.3 删除索引
    • 1.2.4 索引背后的数据结构
    • 1.2.5 索引的分类
  • 1.3 索引的优缺点
  • 1.4 SQL 性能分析
  • • SQL 执行频率
    • 慢查询日志
    • profile 详情
    • explain 执行计划
  • 1.5 索引的使用原则
  • • 最左前缀法则
    • 范围查询
    • 索引列运算
    • 字符串不加引号
    • 模糊查询
    • or连接
    • 数据分布影响
    • SQL 提示
    • 覆盖索引
    • 前缀索引
    • 单列索引和联合索引
  • 1.6 索引设计原则
• 2. 事务
• • 2.1 概念
  • 2.2 事务的特性
  • • 2.2.1 四大特性
    • 2.2.2 隔离性
    • • 脏读问题
      • 不可重复读
      • 幻读
    • 2.2.3 事务的隔离级别

1. 索引

1.1 概念

索引，是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。其实就像是文章目录一样，你想看哪个部分，你就可以通过目录（索引）很快找到你想要的部分。

1.2 索引的使用

1.2.1 查看索引

使用 show index from 表名，我们以 student 表为例：

show index from student;

圈起来的三块表示是 student 表中，这个 PRIMARY 是主键自动创建的索引，针对的是 id 这一列。

1.2.2 创建索引

我们使用 create index 索引名 on 表名(列名, …);

create index index_student_name on student(name);

此时就多出了一个索引。
我们这里再多说一句，创建索引一般是在创建表的时候就把索引设定好，如果说表里已经有很多数据了，那么索引最好就别动了，原因也很简单，比如你现在有一本很厚的书，没有目录，现在让你搞一个目录出来，那么就要花费你大量的时间（资源），可能得搞很久才能搞完。

1.2.3 删除索引

drop index 索引名 on 表名;

drop index index_student_name on student;

此时我们就完成了删除索引操作。

1.2.4 索引背后的数据结构

B+ 树，这个就是为数据库索引量身定做的数据结构。
我们先了解 B+ 树 的前身：B 树。
B 树 也叫做 B- 树，此处的 ‘-’ 不是 减号，是 杠，前晚不要念成 B 减树。
B 树可以认为是一个 N 叉 搜索树：

当节点的子树多了，节点保存的 key 多了，意味着在同样的 key 的个数的前提下，B树的高度就会比二叉搜索树低很多。树的高度越高，进行查询比较的时候，访问磁盘的次数就越多。之所以这样,主要有以下几点原因:

• 树形结构通常用来实现索引,树的节点会存储在磁盘上。
• 查询时需要从根节点开始遍历,访问各个节点。
• 节点存储在磁盘上,访问节点需要磁盘IO。
• 树越高,从根到叶子的路径越长,需要访问的节点数越多。
• 访问更多节点,意味着需要更多的磁盘IO操作。
• 磁盘IO是比较耗时的操作,访问次数越多,查询耗时越长。

B+ 树在 B 树 的基础上又作出改进，也是 N 叉搜索树：

此时我们可以发现， B+ 树的所有数据都是包含在叶子节点中的。
B+ 树的特点：

1. 一个节点，可以存储 N 个 key， N 个 key 划分出了 N 个区间（不是 N + 1 个）
2. 每个节点中的 key 的值，都会在子节点中存在（同时该 key 是子节点的最大值）
3. B+ 树的叶子节点是首尾相连的，类似于一个链表
4. 由于叶子节点是完整的数据集合，只在叶子节点这里存储数据表的每一行数据，而非叶子节点只存 key 本身即可。

这里的每个节点就是一个 Page（页），也就是最多存 16k 的数据。
由这些特点，我们来总结一下 B+ 树的优势：

• 当前一个节点保存更多的 key，最终树的高度是相对更矮的，查询的时候减少了 IO 次数（和 B 树是一样的）
• 所有的查询最后都会落在叶子节点上，换句话说，所有数据的查询的 IO 次数是一样的，所以查询也就比较稳定，就可以让程序员对于执行效率有一个更准确的评估。
• B+ 树的所有的叶子结点构成了一个链表，此时比较方便进行范围查询。
• 由于数据都在叶子节点上，非叶子结点只存储 key，导致非叶子结点占用空间是比较小的，那么这些非叶子结点就可能在内存中缓存（或者缓存一部分），这样就又进一步减少了 IO 的次数。

当然，我们上面画的图是默认 id 是表的主键了。但是如果这个表有多个索引，针对 id 有主键索引，针对 name 又有一个索引的话，表的数据还是按照 id 为主键，构建出 B+ 树，通过叶子结点组织所有的数据行。其次针对 name 这一列，会构建出另外一个 B+ 树，但是这个 B+ 树的叶子结点就不在存储这一行的完整数据，而是存储主键 id。此时如果根据 name 来查询，查到的叶子结点只是主键 id，还需要通过主键 id，去主键的 B+ 树立再查一次才能得到数据（这个过程称为“回表”，都是 MySQL 自动完成的，用户感知不到）。

1.2.5 索引的分类

• 主键索引
• 唯一索引
• 常规索引
• 全文索引

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为一下两种：

• 聚簇索引（聚集索引）：将数据存储与索引放到了一起，索引结构的叶子结点保存行数据，这个索引必须有，并且只能有一个。
• 二级索引：将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键，可以存在多个。

聚簇索引的生成规则：
如果存在主键，主键索引就是聚簇索引。如果不存在主键，将使用第一个唯一索引作为聚簇索引。如果唯一索引也没有的话，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

1.3 索引的优缺点

优点：

• 可以提高查询效率，减少 IO 成本
• 避免全表扫描，减少查询时间
• 节省存储空间，因为索引占据的空间比实际数据要少很多

缺点：

• 维护索引需要额外的存储空间
• 虽然加快了查询速度，但是插入、删除等操作的效率却降低了

1.4 SQL 性能分析

因为我们需要做 SQL 优化，所以我们要进行 SQL 性能分析

SQL 执行频率

由于实际的业务逻辑主要以查询为主，所以往往优化也是以优化查询为主，我们就需要知道SQL语句的执行频率来判断该数据什么操作频率最高，从而针对性的进行优化。
我们可以通过如下命令来查看当前数据库的 增删改查 的访问频次：
show global status like ‘Com_’;
这里 有几个 _(占位符) 代表有几个字符
演示一下：

由此可见，我们增删改一次都没有，但是查询了 48 次，所以说该数据库主要以查询操作为主。

慢查询日志

当我们确定了当前数据的 查询操作占据了绝大部分时，我们要想针对当前的查询操作进行优化，那么其实就是要优化慢查询，所以我们就需要借助 慢查询日志 来定位到那些 SQL 语句执行较慢。
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认为 10s）的所有 SQL语句的日志。
MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件 （/etc/myf, linux 环境）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为 2s，也就是说SQL语句执行时间超过2s就会被视为慢查询，被记录
long_query_time=2

此外，我们还可以使用 一下命令来开启：

set global slow_query_log='ON';

profile 详情

虽然慢查询日志能够记录慢查询语句，比如我们设置超过两秒算慢查询，但是有些语句执行时间可能不到2s但是接近两秒，尤其是一些简单的查询语句，此时这些其实也是需要优化的，但是慢查询日志查不到，我们可以利用 profile 详情来处理。
show profiles 指令能帮助我们了解每一条 SQL 语句耗时多少，而且耗时到底耗费在哪里。
我们可以通过 have_profiling 参数来查看当前 MySQL 是否支持 profile操作：

select @@have_profiling;

此时 YES 说明我这里是支持的，如果不支持，可通过如下指令开启：

set profiling=1;

explain 执行计划

以上三种方式都是从执行时间的角度来判断是否要进行优化，但是这种判断其实只是粗略的判定，不够准确。我们还需要借助 explain 来判断。
explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接以及连接的顺序。
语法：直接在 select 语句之前加上关键字 explain/desc：
explain select 字段列表 from 表名 where 条件;

explain select * from userinfo where id = 1;

下面我们来介绍一下查询结果中每一列的含义：

• id：select 是查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同则执行顺序从上往下；id不同则值越大，越先执行）。此处由于我们只是单表查询，只有一条记录，所以体现不出来，下面我们重新演示一下：
  
  此时我们再查看该语句的执行计划：
  
  此时我们发现 三条记录 id 都是1，所以执行顺序是从上到下，也就是先执行 s（storage）表，再执行 g（goods表）最后执行 gt（goodstype表）
• select_type：表示当前查询的类型，没什么价值
• type：表示连接类型，性能由好到差的连接类型为：NULL, system,const,eq_ref, ref, range, index, all。当我们进行性能优化的时候，尽量往 null 这边靠。
• possible_key：可能应用在这张表上的索引，一个或多个。
• key：实际用到的索引，没用到索引则为 null
• Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短越好
• filtered：表示返回结果的行数占需读取行数的百分比，值越大越好
• extra：在 extra 字段前的信息没有展示出来的，在 extra 中展示

1.5 索引的使用原则

最左前缀法则

当使用联合索引的时候，就要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃了某一列，索引将部分失效（后面的字段索引失效），需要注意的是，只要最左边的列出现了，不管在那个位置，都会走索引，因为引擎会自动优化。MySQL 8.0 后新增索引跳跃扫描，可以不遵循这个法则。

范围查询

联合索引中当出现范围查询时（>, <）, 范围查询右侧的列索引就会失效，我们可以使用 >=, <= 来避免。

索引列运算

不要再索引列上进行运算操作，索引将失效。

字符串不加引号

当使用字符串类型字段时，如果不加引号，索引会失效

模糊查询

如果头部进行模糊匹配，索引就会失效。如果仅仅是尾部模不匹配，那么索引不会失效。

or连接

用 or 分割开的条件，必须 在 or 的左右两侧列都有索引才会走索引。

数据分布影响

如果 MySQL 评估使用索引不如全表扫描来的快，则不会使用索引。（比如 is null 和 is not null）

SQL 提示

覆盖索引

尽量使用覆盖索引（查询使用了索引，病情需要返回的列，在该索引中已经全部能找得到），减少使用 select *。
之前我们研究的是 where 条件中的索引应该怎么使用，这个覆盖索引讲的是 select 之后的索引应该怎么使用。

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只讲字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：
create index 索引名 on 表名(列名(n));
这里的 n，就表示截取前 n 个字符创建索引

单列索引和联合索引

单列索引：一个索引只包含一个列
联合索引：一个索引包含了多个列

1.6 索引设计原则

• 针对数据量大，查询频率比较频繁的表建索引
• 针对常作为查询条件（where）、排序（order by）以及分组（group by）操作的字段建立索引。
• 尽量对区分度较高的字段建立索引，比如说身份证之类的，每个人都不一样。对于性别或者状态这样可能很多数据都是一样的这种，建立索引也提高不了多少效率。
• 如果是字符串类型的字段，字段长度较长的时候，可以针对字段的特点简历前缀索引
• 尽量使用联合索引，减少单列索引。因为查询时，联合索引很多时候可以覆盖索引，劫神存储时间，避免回表，提高查询效率。
• 索引不是越多越好，因为索引也需要维护，会影响增删改的效率
• 如果索引列不能存 null 值，那么最好在建表时使用 not null 来约束。这样的话当优化器知道每列是否包含 null 时就可以更好的确定哪个索引最有效的用于查询。

2. 事务

2.1 概念

我们先设想一个场景：假设有一张 account（账户）表：account(id, balance)，现在有 id = 1 的用户，balance 为 1000,；id = 2 的用户，balance 为 0。现在 1 给 2 转账 500，此时要完成这个操作需要完成两步：
1）update account set balance = balance - 500 where id = 1;
2）update account set balance = balance + 500 where id = 2;
此时考虑一个极端场景，假设在执行转账的过程中，执行完 1） 之后，数据库崩溃了/主机宕机了，此时 2）操作就没有执行，也就是说 1 的钱扣了，但是 2 的钱没到账，显然这是很不科学的。而事务就是为了解决这个问题的。
事务的本质就是把多条 SQL 语句给打包成一个整体，要么全部执行成功，要么就一个都不执行，不会出现只执行一部分这种状态。这句话的全部执行成功好理解，但是一个都不执行不是真的一个都不执行，而是看起来就像没执行一样，意思就是执行了，但是执行一半出错了，然后选择恢复成未执行的状态（恢复现场，或者说按了一个 ctrl + z），这恢复数据的操作称为“回滚rollback”。进行回滚的时候，为了知道回滚到什么样的状态，需要额外的操作来记录书屋中的操作步骤，数据库中有专门的用来记录事务的日志。
此时我们就可以将上面转账的 1）2）操作作为一个事务，就可以在出现异常的时候把数据还原回来。
我们使用 start transaction 来开启事务，通过 commit 来提交事务，到 commit 这一步就相当于事务就执行完了：

start transaction;
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
commit;

2.2 事务的特性

2.2.1 四大特性

1. 原子性：务中的操作要么全部执行成功,要么全部失败回滚,该事务中涉及的对数据库的更新必须视为一个整体,不能部分执行成功或部分失败。
2. 一致性：事务必须使数据库从一个一致性状态转换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须保证数据库中数据的完整性约束。比如上述的转账操作，1 转账 500 后还有 500，2 收到转账后有 500，此时没问题；但是如果 1 转账后是 500,2 收到转账后是 5000，这就不对了。
3. 持久性：事务修改的内容是写到硬盘上的，持久存在的，重启也不会丢失。
4. 隔离性：这个隔离性是为了解决并发执行事务而引起的问题。

2.2.2 隔离性

接下来我们就来说说并发处理事务，可能有哪些问题，以及这些问题数据库的隔离性是怎样解决的。

脏读问题

一个事务A正在对数据进行修改的过程中，还没提交之前，另外一个事务B，也对这个数据进行了读取，此时 B 的读取操作就称为 脏读，读到的数据也称为 脏数据，这里的 脏 是无效的意思。举个例子，假设正在考试，学生1 在做一道选择题，在 C 和 D 选项中徘徊不定，虽然写下了 C 但是还没确定，此时学生 2 偷瞄了同学1 的答案，于是写了 C，但是同学1 思考中确定了 D 是正确答案，又是改成了 D，那么此时 同学2 的偷瞄就相当于是脏读，读到的 C 就相当于是 脏数据。
为了解决脏读问题，MySQL 引入了“写操作加锁” 这样的机制，就相当于同学1 与 同学2 约好了，同学1 在没确定答案前，先用手挡住，确定了答案在把手拿开，同学2 才能查看。此时当同学1 琢磨答案（写的时候）同学2 没发读，也就是 同学1 的 写操作 与 同学2 的读操作没发并发了。这个给写加锁操作，就降低了并发程度（降低了效率），但是提高了隔离性（提高了数据的准确性）。

不可重复读

事务1 已经提交了数据，事务2 开始去读取数据。在读取过程中，事务3 又提交了新的数据，此时就意味着同一个事务 2 之内，多次读取数据，读出来的结果是不相同的（预期是一个事务中多次读取的结果是一样的）。
举个例子：
假设一个电影院的网络订票系统,显示某场电影还剩10张票。小王查看了票数后准备订票,此时另一个用户小李先订走了2张票。小王完成选座后提交订单时,系统显示该场电影仅剩8张票。
这个例子中:

• 小王查询票数10张,是一个读操作。
• 小李订票减少剩余票数,是一个更新操作。
• 小王再次读取剩余票数变为8张,与第一次读到的10张不一致。

这就造成了不可重复读现象。同一个查询在事务执行过程中返回了不同结果。我们可以在小王的事务开始时,直接对需要查询的电影订票数字段加上行锁。这可以阻止其他事务对订票数进行更新,直到小王的事务完成。从而避免了小王多次读取订票数据不一致的情况。这就相当于对读操作进行加锁。
通过这个读加锁进一步的降低了事务的并发能力，提高了事务的隔离性。

幻读

当前我们已经约定了写加锁和读加锁，解决了脏读和不可重复读问题，此时还存在幻读问题。
所谓的幻读就是在读加锁和写加锁的前提下，一个事务两次读取同一个数据，发现读取的数据值是一样的，但是结果集不一样。
举个例子，由于约定了读加锁，1先生在 2先生读的时候，不能修改代码，但是 1先生不想闲着，于是就要想办法做点事情，他发现 2先生读的是 Student.java ，那么他就创建一个 Teacher.java，然后接编写这里面的代码。这样的话就可能导致 2先生或者其他正在读 Student.java 的时候，虽然每次读取数据内容一样，但是突然就多出一个 Teacher.java。
为了解决幻读问题，数据库采用 串行化 的方式，彻底放弃并发处理事务，一个接一个的串行的处理事务。这样做并发程度最低（效率最低），隔离性最高（数据准确性最高）

2.2.3 事务的隔离级别

对应上述的脏读、不可重复读、幻读问题，MySQL 提供了 4 种隔离级别：

1. 读未提交（read uncommitted）：此时没有任何所限制。并发程度最高（效率最高），隔离性最低（准确性最低）
2. 读已提交（read committed）：给写加锁了，并发程度降低了，隔离性提高了
3. 可重复读（repeatable read）:给读和写都加锁，并发程度又降低了，隔离性又提高了
4. 串行化（serializable）：串行化，并发程度最低，隔离性最高

这四个级别属于 MySQL 内置的机制，可以通过修改 MySQL 的配置文件，来设置当前 MySQL 的工作在那种状态下，默认是 可重复读。这几个隔离级别的选择需要我们根据实际需要，在准确性和效率之间进行权衡。

本文标签： 索引事务mysql