Mysql小记

35 阅读8分钟

开启掘金成长之旅!这是我参与「掘金日新计划 · 2 月更文挑战」的第 2 天,点击查看活动详情

事务的ACID特性:

原子性(使用undo log回滚日志实现):

事务要么全部执行,要么全部不执行,以原子单位进行处理。

一致性(使用持久性、一致性、原子性):

事务执行的前后,数据的完整性保持一致。类似于银行转账,A向B转账,两个人总额是5000,转账完后总额还会是5000,中途不会不会有数据丢失。

隔离性(使用MVCC多版本控制或锁机制实现):

一个事务的执行不会被其他事务所干扰。

持续性(使用redo log重做日志实现):

如果一个事务成功提交了,那么它造成的数据改变将是永久的,类似于数据持久化。

事务的隔离级别:

读未提交(防止丢失更新,造成脏读):

一个事务可以读取到另一个事务已修改但未提交的数据修改,可以有效防止数据更新丢失。如果一个事务开始写数据,则另一个事务不允许同时进行写操作,但是可以读取数据。
脏读:事务A读取到事务B修改的数据,但是事务B却回滚了,读取到不存在的数据或错误数据。

读已提交(防止脏读,会造成不可重复读):

事务在执行过程中,允许访问其他事务已经提交的插入或修改操作的数据。但是如果一个事务正在进行写事务且未提交,则禁止其他事务访问该行。可以有效防止脏读。
不可重复读:同一个事务中读取一行数据两次,得到两次不一样的结果。在第一次读取后,另一个事务更改了数据并成功提交,就会导致两次读取的数据不同。

可重复读(MySQL的默认事务隔离级别!!防止不可重复读,会造成幻读)

一个事务在执行过程中,可以访问其他事务提交的新插入的数据,但是不能访问其他事务成功修改的数据。读事务时会禁止其他写事务(允许读事务),写事务则禁止其他所有事务。可以有效防止不可重复读和脏读。
幻读:在一个事务中查询数据两次,第一次查询后其他事务提交了新插入的数据,就会造成第二次查询时多出了一条数据,这就是幻读。(和可重复读的区别时,一个是会读到两次不同的数据,一个是会读出多的一些数据)

可串行化(所以问题都可避免,但是造成锁竞争和超时现象)

提供严格的事务隔离,要求事务序列化执行,不能并发操作,即一个事务结束后才会运行下一个事务。可以解决事务并发时出现的问题,脏读、不可重复读、幻读等问题。但是这样会导致大量的超时现象和锁竞争,在实际应用中很少使用。 事务隔离级别与问题

索引相关知识

什么是索引?

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构,用空间换时间。简单来说就是为了让存储引擎更快的查找获取数据。常见的索引有聚簇索引、主键索引、二级索引、唯一索引、hash索引、B+树索引。 Innodb的默认创建的主键索引为聚簇索引,是用B+树实现的,其他的属于二级索引或非聚簇索引。 image.png

B+Tree索引

  • 数据都存储在叶子节点,非叶子节点存放键值+指针。
  • 叶子节点中包含所有的索引。
  • 每个小节点都在大节点的范围中。
  • 叶子节点都是使用双指针链接,提高访问性能,比如条件是>或者<。
  • 如果B+树高度为2的话,根节点指针数*单个叶子节点记录行数 = 16kb/14 * 16 大约 1.8w+ 数据
  • 如果B+树高度为3的话,那么这棵B+树的存放总记录数为:根节点指针数*单个叶子节点记录行数 = 16kb/14 * 16kb/14 * 16 大约2kw+数据。

为什么使用B+树作为索引

一个是通过非叶子节点的结构,能够快速二分查找到数据,另一个是叶子节点通过双向链表连接,能够快速的进行区间范围查询。插入操作因为是通过主键自增的话,顺序插入很方便,并且删除时也只需要删除叶子节点就行。

Hash索引

  • Hash索引基于Hash表实现,只有查询条件精确匹配Hash索引中的所有列才会用到Hash索引。
  • 存储引擎会为Hash索引中的每一列都计算Hash码,Hash索引中存储的即hash码,所以读取时会进行两次查询。
  • Hash索引无法用于排序。
  • Hash索引不使用于区分度小的列上,如性别字段。
    B+树索引适合用来做范围查询,而HASH适合做等值查询、搜索复杂度为O(1)

二级索引

也是使用B+树存储,但是和聚簇索引不同的是,二级索引的叶子节点保存的是主键值。在使用二级索引列作为条件进行查询时,会先检索二级索引来获得主键值,然后再去查主键索引的B+树来获得数据,也就是回表。但是如果这时候需要获得的是主键值,就不需要再去查主键索引的B+树,而是直接获得主键值。

主键索引

建立在主键字段的索引,这一列必须是唯一的且不能为空

唯一索引

一列字段唯一的索引,但是可以为空

使用索引可能带来的问题

  1. 增加占用物理空间,数量越大,占用空间越大。
  2. 创建和维护索引都需要时间,当数据量越大时所花费的时间更多。
  3. 有可能让增删改变慢,因为每次进行增删改为了B+树的有序性,都需要维护索引。

适合使用索引的场景

  1. 字段唯一的,例如商品编码。
  2. 经常需要使用WHERE查询条件的字段,这样能够提高整个表的查询速度,如果查询条件是多个列时,使用联合索引。
  3. 经常用于GROUP BYOREDER BY的字段,这样在查询的时候就不需要再次排序,因为基于B+树的有序性,建立索引之后的记录都是排序的。

MySQL的引擎

InnoDB引擎

假设有my_test 的database有个t_order表,则在/var/lib/mysql/my_test目录下有 t_order.ibd,t_order 的表数据保存在这个文件
t_order.frm ,t_order 的表结构会保存在这个文件

表空间结构

表空间由段>区>页>行的包含关系存储,InnoDB 的数据是按为单位来读写的,每页的大小为16KB MySQL的默认引擎,提供了事务操作并使用了可重复读作为隔离级别,采用B+树作为默认索引类型,并且提供行锁和外键约束。
MySQL运行的时候,InnoDB会在内存中建立缓冲池,用于缓冲数据和索引。由于锁的粒度小,写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。

MyISAM引擎

Memory引擎

分库分表

  • 磁盘存储,单机MySQL当数据量较大时,当业务量较大时磁盘使用率大大降低
  • 支撑微服务提高并发操作,在高并发的情况会有大量连接Mysql,分库分表可以让不同的服务访问对应的模块数据库,用于分担读写压力。

垂直拆分

  • 分库:将原本的单库多表,拆分为多库单表,各个业务对应不同的数据库
  • 分表:将表的常用字段和不常用字段进行分开建表,保留基本常用信息字段,还能增快查询。

水平拆分

  • 分库:将user数据库分为user1和user2两个库,结构相同只是存储的数据集合不同
  • 分表:按照hans取模或者时间范围对数据进行分表,例如user1(前1000名用户)、user2(1001~2000用户)

分库分表导致的问题

  • 本地事务失效,需要使用分布式事务
  • 需要使用分布式ID,用UUID或者雪花算法生成ID
  • 跨库关联查询时,就需要分别查两次
  • 查询排序,需要查到各个库数据再进行合并
  • 分页查询同上