MySQL(补档)

数据库 MySQL
发布日期:   2022-12-04
更新日期:   2023-09-15
文章字数:   8.4k
阅读时长:   29 分
阅读次数:  

MySQL

什么是 MySQL

MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。

MySQL 的优点

MySQL 主要具有下面这些优点：

  1. 成熟稳定，功能完善。
  2. 开源免费。
  3. 文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。
  4. 开箱即用，操作简单，维护成本低。
  5. 兼容性好，支持常见的操作系统，支持多种开发语言。
  6. 社区活跃，生态完善。
  7. 事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且，InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。
  8. 支持分库分表、读写分离、高可用。

一条SQL语句在MySQL中如何执行的

MySQL逻辑架构图

MySQL分为Server层和存储引擎层两个部分，不同的存储引擎共用一个Server层。

Server层：大多数MySQL的核心服务功能都在这一层，包括连接处理、授权认证、查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层：存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

  1. 连接器 第一步，先连接到数据库上，当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证，认证基于用户名、原始主机信息和密码，一旦客户端连接成功，服务器会继续验证客户端是否具有执行某个特定查询的权限

  2. 查询缓存 第二步，查询缓存，每次MySQL执行过的语句及其结果会以key-value形式缓存在内存中，key是查询语句，value是查询结果。如果查询能够在缓存中找到key，那么这个value就会被直接返回客户端。
    注意：MySQL8.0版本直接将缓存的整个功能模块删掉了

  3. 分析器 第三步，分析器，如果没有命中缓存，就会执行SQL语句，首先让MySQL知道我们需要做什么，因此需要对SQL语句解析。解析器的工作：语法分析（生成句子），语义分析（确保这些句子讲得通），以及代码生成（为编译准备）

  4. 优化器 第四步，优化器，经过分析器MySQL知道我们需要什么了，在开始执行前，还要经过优化器进行处理，优化器是在表里面有多个索引时，决定使用哪个索引，或者在一个语句有多表关联（join）时，决定各个表的连接顺序。优化器会生成执行计划

  5. 执行器 第五步，执行器，MySQL通过分析器知道要做什么，通过优化器知道怎么做，开始执行前，要先判断一下是否有表TABLE查询权限，如果有打开表，根据表的引擎定义，去使用这个引擎提供的接口。根据执行计划，调用存储引擎API来查询数据

一条SQL的执行顺序是什么

1.FROM     ：对FROM左边的表和右边的表计算笛卡尔积，产生虚拟表t1
2.ON       ：对表t1进行ON筛选，只有符合条件的行才会记录在表t2中
3.JOIN     ：如果指定了OUTER JOIN（如：left join、right join）,那么未匹配到的行作为外部行添加到表t3中
4.WHERE    ：对表t3进行where条件过滤，只有符合条件的记录才会记录在表t4中
5.GROUP BY ：根据group by 子句中的列，对表t4记录进行分组操作，产生表t5
6.HAVING   ：对表t5进行having过滤，只有符合条件的行才会记录在表t6中
7.SELECT   ：执行select操作，选择指定的列，产生表t7
8.DISTINCT ：对表t7记录进行去重，产生表t8
9.ORDER BY ：对表t8记录进行排序，产生表t9
10.LIMIT   ：取出指定的行，产生表t10,并将结果进行展示

MySQL 的默认存储引擎

MySQL 5.5.5 之前：MyISAM 是 MySQL 的默认存储引擎。
5.5.5 版本之后：InnoDB 是 MySQL 的默认存储引擎。

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

MyISAM 和 InnoDB 的区别

  • InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
  • MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
  • MyISAM 不支持外键，而 InnoDB 支持。
  • MyISAM 不支持 MVCC，而 InnoDB 支持。
  • 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。（非聚簇和聚簇）
  • MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
  • InnoDB 的性能比 MyISAM 更强大。

MySQL用什么类型数据存储时间

  1. 不要用字符串存储日期，简单直白，但是占用的空间更大，计算和比较效率低
  2. DateTime 类型没有时区信息，Timestamp 和时区有关
  3. DateTime 类型耗费空间更大，Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间
  4. Timestamp 表示的时间范围更小。
  5. 用 int 或者 bigint 类型的数值也就是时间戳来表示时间，跨系统也很方便，排序以及对比效率高，但是数据的可读性太差

介绍一下join

  • A inner join B ：取交集。
  • A left join B ：取 A 全部，B 没有对应的值为 null。
  • A right join B ：取 B 全部 A 没有对应的值为 null。
  • A full outer join B ：取并集，彼此没有对应的值为 null。

对应条件在 on 后面填写

在使用 join 时，on 和 where 条件的区别如下：

  •  1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
  •  2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

MySQL的redo log，undo log，bin log都是干什么的

1、redolog记录修改内容（哪一页发生了什么变化），写于事务开始前，用于数据未落磁盘，但数据库挂了后的数据恢复
2、binlog记录修改SQL，写于事务提交时，可用于读写分离 ，主从复制
3、undolog记录修改前记录，用于回滚和多版本并发控制

事务

事务的基本特性

  1. 原⼦性：⼀个事务中的操作要么全部成功，要么全部失败。

  2. ⼀致性：数据库总是从⼀个⼀致性的状态转换到另外⼀个⼀致性的状态。

  3. 隔离性：⼀个事务的修改在最终提交前，对其他事务是不可⻅的。

  4. 持久性：⼀旦事务提交，所做的修改就会永久保存到数据库中。

并发事务带来的问题

脏读（Dirty read）

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据。

丢失修改（Lost to modify）

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

不可重复读（Unrepeatable read）

在一个事务内两次读到的数据是不一样的情况。在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

事务的隔离级别

  1. read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。

  2. read commit 读已提交，两次读取结果不⼀致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。

  3. repeatable read 可重复复读，就是每次读取结果都⼀样，但是有可能产⽣幻读。

  4. serializable 串⾏，给每⼀⾏读取的数据加锁，会导致⼤量超时和锁竞争的问题。

并发事务的控制方式

锁 和 MVCC

锁可以看作是悲观控制的模式

多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式

ACID靠什么保证

A原⼦性：由undo log⽇志保证，它记录了需要回滚的⽇志信息，事务回滚时撤销已经执⾏成功的sql

C⼀致性：由其他三⼤特性保证、程序代码要保证业务上的⼀致性

I隔离性：由锁和MVCC来保证

D持久性：由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

锁

Mysql锁有哪些

按锁粒度分类：

  1. ⾏锁：锁某⾏数据，锁粒度最⼩，并发度⾼

  2. 表锁：锁整张表，锁粒度最⼤，并发度低

  3. 间隙锁：锁的是⼀个区间

还可以分为：

  1. 共享锁：也就是读锁，⼀个事务给某⾏数据加了读锁，其他事务也可以读，但是不能写

  2. 排它锁：也就是写锁，⼀个事务给某⾏数据加了写锁，其他事务不能读，也不能写

共享锁和排他锁是表级锁和行级锁

  1. 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。

  2. 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是表级锁

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

InnoDB 锁有哪些

  • 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
  • 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
  • 临键锁（Next-Key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要是为了解决幻读问题。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock

当前读和快照读的区别

快照读（一致性非锁定读）就是单纯的 SELECT 语句，但不包括下面这两类 SELECT 语句：

SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

  • 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
  • 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

MVCC

什么是MVCC

多版本并发控制：读取数据时通过⼀种类似快照的⽅式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到⾃⼰特定版本的数据，版本链

MVCC只在**READ COMMITTED(读已提交)和REPEATABLE READ(可重复读)**两个隔离级别下⼯作。

其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据⾏, ⽽不是符合当前事务版本的数据⾏。⽽ SERIALIZABLE 则会对所有读取的⾏都加锁。

对于InnoDB存储引擎，每一行记录都有隐藏列：

trx_id：⽤来存储每次对某条记录进⾏修改的时候的事务id。
roll_pointer：记录有修改的时候，都会把⽼版本写⼊undo⽇志中。roll_pointer就是存了⼀个指针，它指向这条记录的上⼀个版本的位置，通过它来获得上⼀个版本的记录信息。
row_id：表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列。

已提交读和可重复读的区别就在于它们⽣成ReadView的策略不同。

开始事务时创建readview，readView维护当前活动的事务id，即未提交的事务id，排序⽣成⼀个数组
，获取数据中的事务id（获取的是事务id最⼤的记录），对⽐readview：

  • 如果在readview的左边（⽐readview都⼩），可以访问（在左边意味着该事务已经提交）
  • 如果在readview的右边（⽐readview都⼤）或者就在readview中，不可以访问，获取roll_pointer，取上⼀版本重新对⽐（在右边意味着，该事务在readview⽣成之后出现，在readview中意味着该事务还未提交）

已提交读隔离级别下的事务在每次查询的开始都会⽣成⼀个独⽴的ReadView,⽽可重复读隔离级别则在第⼀次读的时候⽣成⼀个ReadView，之后的读都复⽤之前的ReadView。

这就是Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView⽣成策略的不同实现不同的隔离级别。

基于MVCC查询一条记录，是怎样的流程

  1. 获取事务自己的版本号，即事务ID
  2. 获取Read View
  3. 查询得到的数据，然后与Read View中的事务版本号进行比较。
  4. 如果不符合Read View的可见性规则， 即就需要Undo log中查找可见的历史快照;
  5. 最后返回符合规则的数据

InnoDB 实现MVCC，是通过 Read View+ Undo Log 实现的，Undo Log 保存了历史快照，Read View可见性规则帮助判断当前版本的数据是否可见。

索引

什么是索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的作用就相当于书的目录。

优点 ：

  • 使用索引可以大大加快 数据的检索速度, 这也是创建索引的最主要的原因。

缺点 ：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

索引的底层数据结构有哪些

Hash 表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

哈希算法有Hash 冲突 问题，哈希索引不支持顺序和范围查询。

B+树

B+树是⼀个平衡的多叉树，从根节点到每个叶⼦节点的⾼度差值不超过1，⽽且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶⼦节点的搜索效率基本相当，不会出现⼤幅波动，⽽且基于索引的顺序扫描时，也可以利⽤双向指针快速左右移动，效率⾮常⾼。因此，B+树索引被⼴泛应⽤于数据库、⽂件系统等场景。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构

为什么用B+树而不用B-树

  1. B+树索引节点没有有Data域，内存占用小，索引范围大。

  2. B+树节点小，磁盘IO次数少

  3. B+树叶子节点用指针串连，可以进行区间访问

B 树& B+树两者有何异同

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。

  • B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

为什么用B+树而不用红黑树或者AVL树

红黑树和AVL树的高度太大：AVL 树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。

B+树一个node只需一次I/O：数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

索引有哪些类型

按照底层存储方式角度划分

  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分

  • 主键索引：是⼀种特殊的唯⼀索引，在⼀张表中只能定义⼀个主键索引，主键⽤于唯⼀标识⼀条记录，使⽤关键字 PRIMARY KEY 来创建。（不可以有 NULL）
  • 普通索引：允许被索引的数据列包含重复的值。（可以有 NULL）
  • 唯一索引：可以保证数据记录的唯⼀性。（可以有 NULL）
  • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
  • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  • 全文索引：通过建⽴倒排索引 ,可以极⼤的提升检索效率,解决判断字段是否包含的问题。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

聚簇索引与非聚簇索引的区别是什么

  • 聚簇索引：即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。
  • 对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
  • 非聚簇索引：即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。
  • 非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

聚簇索引的优缺点：

优点 ：

  • 查询速度非常快 ：相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
  • 对排序查找和范围查找优化 ：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点 ：

  • 依赖于有序的数据 ：如果索引的数据不是有序的，那么就需要在插入时排序，字符串或 UUID 这种又长又难比较的数据，插入或查找的速度比较慢。
  • 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改

非聚簇索引的优缺点：

优点 ：

  • 更新代价比聚簇索引要小 。非聚簇索引的叶子节点是不存放数据的

缺点 ：

  • 依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
  • 可能会二次查询(回表) ：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

索引覆盖是什么

索引覆盖就是⼀个SQL在执⾏时，可以利⽤索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL⾛完索引后不⽤回表了，所需要的字段都在当前索引的叶⼦节点上存在，可以直接作为结果返回了

非聚簇索引一定回表查询吗(覆盖索引)

非聚簇索引不一定回表查询。

用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

SELECT name FROM table WHERE name='aiaa';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

索引下推是什么

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

最左前缀原则是什么

当⼀个SQL想要利⽤索引，就⼀定要提供该索引所对应的字段中最左边的字段，也就是排在最前⾯的字段，⽐如针对a,b,c三个字段建⽴了⼀个联合索引，那么在写⼀个sql时就⼀定要提供a字段的条件，这样才能⽤到联合索引，这是由于在建⽴a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去⽐较⼤⼩进⾏排序的，所以如果想要利⽤B+树进⾏快速查找也得符合这个规则

索引哪些情况会失效

  • 查询条件包含or，会导致索引失效。
  • 隐式类型转换，会导致索引失效，例如age字段类型是int，where age = “1”，这样就会触发隐式类型转换。
  • like通配符会导致索引失效。注意：”ABC%“会走range索引，”%ABC”索引才会失效。
  • 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
  • 对索引字段进行函数运算。
  • 对索引列运算（如，+、-、*、/），索引失效。
  • 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
  • 索引字段上使用is null， is not null，可能导致索引失效。
  • 相join的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
  • mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景

  • 数据量少的不适合加索引
  • 更新比较频繁的也不适合加索引
  • 离散性低的字段不适合加索引（如性别）

选择合适的字段创建索引

  • 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。
  • 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

如何分析语句是否走索引查询

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

MySQL的explain执行计划的type属性值表示的是什么

常见取值及其含义：

  1. const（常量）：表示通过索引一次查找就可以定位到唯一的行。
  2. ref（索引查找）：表示通过非唯一索引进行查找并返回匹配条件的行。
  3. range（范围查找）：表示通过索引的范围查找，返回匹配指定条件范围的行。
  4. index（索引扫描）：表示全索引扫描，遍历索引树上的每个节点。
  5. all（全表扫描）：表示对整个表进行完全扫描，没有使用索引。

分库分表

什么是分库分表

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。
分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

分库分表的拆分方法有哪些

  • 水平分库：以库为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
  • 水平分表：以表为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
  • 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
  • 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中

什么情况下需要分库分表

  • 单表的数据达到千万级别以上，数据库读写速度比较缓慢。
  • 数据库中的数据占用的空间越来越大，备份时间越来越长。
  • 应用的并发量太大。

常用的分库分表中间件

  • Sharding-Sphere
  • Mycat

Sharding-Sphere 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二
次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个
系统都需要耦合 Sharding-Sphere 的依赖；

mycat 这种 proxy 层方案的缺点在于需要部署，自己及运维一套中间件，运维成本高，
但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了

分库分表会带的问题

  • join 操作 ： 同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装。
  • 事务问题 ：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。
  • 分布式 id ：分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。需要为系统引入分布式 id 。

如何解决id唯一性问题

  • UUID：结合机器的网卡、当地时间、一个随机数来生成UUID。
  • 为每个分片指定一个 ID 范围
  • 数据库自增，多个Master库就设置不同的自增步长
  • 雪花算法自增：64位long(1符号位，41时间戳，10机器id，12流水号：意味着每毫秒可以产生4096个id)
  • 借助redis自增(incr 和 increby原子指令)

分库分表如何将老库的数据迁移到新库（实现方案）

停机迁移：维护升级预计 1 小时，写一个脚本将老库的数据都同步到新库中。

双写方案：双写方案是针对那种不能停机迁移的场景，动态切换

  • 对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。 这样就能保证，咱们新库里的数据是最新的。
  • 在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。
  • 重复上一步的操作，直到老库和新库的数据一致为止。

读写分离

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。

读写分离依赖于从主复制。

主从复制的原理是什么（过程怎么样的）

MySQL binlog(binary log 即二进制日志文件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)。因此，我们根据主库的 MySQL binlog 日志就能够将主库的数据同步到从库中。

  1. 主库将数据库中数据的变化写入到 binlog
  2. 从库连接主库
  3. 从库会创建一个 I/O 线程向主库请求更新的 binlog
  4. 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
  5. 从库的 I/O 线程将接收的 binlog 写入到中转日志 relay log 中。
  6. 从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）。

binlog 还能帮助我们实现数据恢复。

主从同步延迟是什么？怎么产生的？如何解决？

主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的 主从同步延迟 。

怎么产生的

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。造成的原因可能有：

  • 从库所在机器的性能要比主库所在的机器性能差。
  • 从库的压力太大：从库访问量高于主库，从库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。
  • 主库上执行了大事务。一次性修改了大量数据

参考的解决办法：

  1. 强制将读请求路由到主库处理。Sharding-JDBC 就是采用的这种方案。对于这种方案，可以将那些必须获取最新数据的读请求都交给主库处理。
  2. 延迟读取。但是影响性能
  3. 增加从服务器，降低服务器负载。
文章作者: Aiaa
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Aiaa !
数据库 MySQL
  目录