MySQL 常见问题(三)

  • 磁盘文件不同:

MyISAM引擎的表会生成三个磁盘文件:

table_name.frm 该文件中存储表的结构信息。table_name.MYD 该文件中存储表的行数据。table_name.MYI 该文件中存储表的索引数据。

而InnoDB引擎的表只会生成两个磁盘文件:

table_name.frm 该文件中存储表的结构信息。table_name.ibd 该文件中存储表的行数据和索引数据。

  • InnoDB 支持聚簇索引,而 MyISAM 只支持非聚簇索引,因为 MyISAM 索引数据和表数据是分开存储的。
  • InnoDB 基于 Undo-log 日志实现了事务机制,但 MyISAM 没有,所以 MyISAM 不支持事务。
  • InnoDB 基于 Redo-log 日志实现了故障恢复机制,但 MyISAM 则只能依靠 Bin-log,因此会有丢失数据的风险。
  • InnoDB 可以基于聚簇索引实现行锁,同时还兼容表锁,但 MyISAM 仅支持表锁。
  • InnoDB 因为支持行锁以及 MVCC 机制,所以并发场景下的性能会远超 MyISAM 引擎。
  • InnoDB 由于设计了 BufferPool 缓冲池,所以内存利用度会远超 MyISAM 引擎。

主要是粒度不同,表锁是指对一整张表加锁,当加锁后,其他来访问该表的事务都会被阻塞,而行锁的粒度则小很多,是指针对于一条/多条数据加锁,并不会阻塞操作同一表的事务,而仅仅 只会阻塞操作相同行数据的事务

共享锁允许多个事务一起持有,而排他锁在同一时间内只能允许一个事务持有,也就是但凡出现排他锁的场景,其他事务都需要阻塞等待。

表锁有元数据锁、意向锁、自增锁、全局锁这四种,行锁有记录锁、间隙锁、临键锁、插入意向锁这四类,行锁在 MySQL 中是 InnoDB 引擎独有的,并且 InnoDB 的行锁和表锁之间,是相互兼容的。

记录锁是指对一条数据上锁、当我们针对主键或者唯一索引加锁的时候, MySQL 默认会对查询的这一行数据加行锁,避免其他事务对这一行数据进行修改。

间隙锁,就是锁定一个索引区间。在普通索引或者唯一索引列上,由于索引是基于 B+ 树的结构存储,所以默认会存在一个索引区间。而间隙锁,就是某个事务对索引列加锁的时候,默认锁定对应索引的 左右开区间范围。在基于索引列的范围查询,无论是否是唯一索引,都会自动触发间隙锁。比如基于between的范围查询,就会产生一个左右开区间的间隙锁。

临键锁,它相当于行锁+间隙锁的组合,也就是它的锁定范围既包含了索引记录,也包含了索引区间,它会锁定一个 左开右闭区间的数据范围。比如我们使用非唯一索引列进行查询的时候,默认会加一个临键锁,锁定一个左开右闭区间的范围。

总的来说,记录锁、临键锁、间隙锁只是表示锁定数据的范围,最终目的是为了解决幻读 的问题。而临键锁相当于行锁+间隙锁,因此当我们使用非唯一索引进行精准匹配的时候,会默认加临键锁,因为它需要锁定匹配的这一行数据,还需要锁定这一行数据对应的左开右闭区间。因此在实际应用中,尽可能使用唯一索引或者主键索引进行查询,避免大面积的锁定造成性能影响。

几乎所有释放锁的工作都是 MySQL 自动完成的,但不同事务隔离级别中,释放锁的时机也不同,如果目前是读未提交级别,MySQL 执行完一条语句后就会立马释放锁。如果是其他级别中,基本上都需要等待持有锁的事务结束(commit/rollback)后才会释放。

因为索引树的结构会发生变更,比如一个无序数据插入时,就会导致树节点的分裂,这时需要挪动树中的一些节点位置,为了防止其他事务再次破坏树结构,或从索引树中读到不对的数据,所以会对整棵树上锁,这个问题被称为SMO问题,共享排他锁主要就是用来解决SMO问题。

死锁,简单来说就是两个或者两个以上的线程在执行的过程中,争夺同一个共享资源造成的相互等待的现象。

如果没有外部干预,线程会一直阻塞无法往下执行,这些一直处于相互等待资源的线程就称为死锁线程。

导致死锁的条件有四个,也就是这四个条件同时满足就会产生死锁👇

  • 互斥条件,共享资源 X 和 Y 只能被一个线程占用
  • 请求和保持条件,线程 T1 已经取得共享资源 X,在等待共享资源 Y 的时候,不释放共享资源 X
  • 不可抢占条件,其他线程不能强行抢占线程 T1 占有的资源
  • 循环等待条件,线程 T1 等待线程 T2 占有的资源,线程 T2 等待线程 T1 占有的资源,就是循环等待

导致死锁之后,只能通过人工干预来解决,比如重启服务,或者杀掉某个线程。所以,只能在写代码的时候,去规避可能出现的死锁问题。

按照死锁发生的四个条件,只需要破坏其中的任何一个,就可以解决,但是,互斥条件是没办法破坏的,因为这是互斥锁的基本约束,其他三方条件都有办法来破坏:

  • 对于“请求和保持”这个条件,可以一次性申请所有的资源,这样就不存在等待了。
  • 对于“不可抢占”这个条件,占用部分资源的线程进一步申请其他资源时,如果申请不到,可以主动释放它占有的资源,这样不可抢占这个条件就破坏掉了。
  • 对于“循环等待”这个条件,可以靠按序申请资源来预防。所谓按序申请,是指资源是有线性顺序的,申请的时候可以先申请资源序号小的,再申请资源序号大的,这样线性化后自然就不存在循环了。

聚簇索引,在聚簇索引中,索引数据和表数据在磁盘中的位置是一起的。聚簇索引存的是主键和当前行的数据,一张表中只能存在一个聚簇索引,一般都会选用主键作为聚簇索引。一般聚簇索引要求索引必须是 非空唯一索引才行。

唯一索引,索引中的索引节点值不允许重复,一般配合唯一约束使用。

主键索引,是一种特殊的唯一索引,和普通唯一索引的区别在于不允许有空值

普通索引,通过KEYINDEX关键字创建的索引就是这个类型,没什么限制,就是单纯的可以让查询快一点。

全文索引 在 5.7 版本之前,只有 MyISAM 引擎支持。全文索引只能创建在CHARVARCHARTEXT等这些文本类型字段上,而且使用全文索引查询时,条件字符数量必须 大于 3 才生效。如果想要创建出的全文索引支持中文,需要在最后指定解析器with parser ngram

覆盖索引

比如有个联合索引为user_name、user_sex、password,那么 SQL:

1
2
3
4
5
6
EXPLAIN SELECT 
    `user_name`,`user_sex`
FROM 
    `zz_users`
WHERE 
    `password` = "1234" AND `user_sex` = "男";

虽然不符合联合索引的最左前缀匹配原则的,但是也可以用到索引,这就是索引覆盖。也就是:查询的列,在使用的索引中已经包含,被所使用的索引覆盖,这种情况称之为索引覆盖。

索引下推

1
2
3
4
5
6
7
{
    ["熊猫","女","6666"] : 1,
    ["竹子","男","1234"] : 2,
    ["子竹","男","4321"] : 3,
    ["1111","男","4321"] : 4,
    ["竹竹","女","8888"] : 5
}

比如 zz_users 表中有数据☝️,索引user_name、user_sex、password,查询 SQL:

1
SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";

由于使用了模糊查询,但%在结尾,因此可以使用这个字作为条件在联合索引中查询,整个查询过程如下:

  • ① 利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
  • ② 返回索引节点存储的值「2、5」给Server层,然后去逐一做回表扫描。
  • ③ 在Server层中根据user_sex="男"这个条件逐条判断,最终筛选到「竹子」这条数据。

那么为什么user_sex="男"这个条件不在联合索引中处理呢?因为前面是模糊查询,所以拼接起来是这样的竹X男,由于这个X是未知的,因此无法根据最左前缀原则去匹配数据,最终这里只能使用联合索引中user_name字段的一部分,后续的user_sex="男"还需要回到Server层处理。

所谓的索引下推,就是将Server层筛选数据的工作,下推到引擎层处理。

MySQL5.6 后加入索引下推机制后,其执行过程就是下面的顺序:

  • ① 利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
  • ② 根据user_sex="男"这个条件在索引节点中逐个判断,从而得到「竹子」这个节点。
  • ③ 最终将「竹子」这个节点对应的「2」返回给Server层,然后聚簇索引中回表拿数据。

相较于没有索引下推之前,原本需要做「2、5」两次回表查询,但在拥有索引下推之后,仅需做「2」一次回表查询。

脏读:指一个事务读到了其他事务还未提交的数据,也就是当前事务读到的数据,由于还未提交,因此有可能会回滚。

幻读:另外一个事务在第一个事务要处理的目标数据范围之内新增了数据,然后先于第一个事务提交造成的问题。

幻读是对自己来说的,比如,事务 A 在对表中多行数据进行修改,将性别「男、女」改为「0、1」,此时事务 B 又插入了一条性别为男的数据,当事务 A 提交后,再次查询表时,会发现表中依旧存在一条性别为男的数据。

不可重复读:指在一个事务中,多次读取同一数据,先后读取到的数据不一致。

事务 A 执行下单业务时,因为添加物流信息的时候出错了,导致整个事务回滚,事务回滚完成后,事务 A 就结束了。但事务 B 却并未结束,在事务 B 中,在事务 A 执行时读取了一次剩余库存,然后在事务 A 回滚后又读取了一次剩余库存,仔细想想:B 事务第一次读到的剩余库存是扣减之后的,第二次读到的剩余库存则是扣减之前的(因为 A 事务回滚又加回去了),导致两次读取的数据不一致,这就是不可重复读。

多个事务一起操作同一条数据,例如两个事务同时向表中添加一条ID=88的数据,此时就会造成数据覆盖,或者主键冲突的问题,这个问题也被称之为更新丢失问题。