侧边栏壁纸
博主头像
一定会去到彩虹海的麦当

说什么呢?约定好的事就一定要做到啊!

  • 累计撰写 63 篇文章
  • 累计创建 16 个标签
  • 累计收到 3 条评论

目 录CONTENT

文章目录

[面试题]-数据库

一定会去到彩虹海的麦当
2022-05-18 / 0 评论 / 3 点赞 / 106 阅读 / 22,264 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-05-18,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

数据库基础知识

什么是 MySQL?

MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL 是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。在 Java 企业级开发中非常常用,因为 MySQL 是开源免费的,并且方便扩展。

数据库三大范式是什么

第一范式:每个列都不可以再拆分。

第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。

第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。

在设计数据库表结构的时候,要尽量遵守三大范式,如果不遵守,必须有足够的理由。比如性能,事实上我们经常会为了性能而妥协数据库的设计。

基础架构

image.png

SQL 的几种连接查询方式(内连接、外连接、全连接、联合查询)

准备表

drop table if exists test_a;
CREATE TABLE `test_a` (
 `id` varchar(10) NOT NULL,
 `username` varchar(10) NOT NULL,
 `password` varchar(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table if exists test_a_description;
CREATE TABLE `test_a_description` (
 `id` varchar(10) NOT NULL,
 `age` varchar(10) ,
 `address` varchar(50) ,
 `parent_id` varchar(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

准备数据

insert into test_a values('1','小明','11');
insert into test_a values('2','宁宁','22');
insert into test_a values('3','敏敏','33');
insert into test_a values('6','生生','66');

insert into test_a_description values('1','10','aaa','1');
insert into test_a_description values('2','20','bbb','2');
insert into test_a_description values('3','30','ccc','3');
insert into test_a_description values('4','40','ddd','4');

一、内连接(inner join)

典型的联接运算,使用像 = 或 <> 之类的比较运算符)。包括相等联接和自然联接。

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索 students 和 courses 表中学生标识号相同的所有行。

查出的是两张表的交集,两张表都有的才查出来

SQL:

select * from 表 A inner join 表 B on 判断条件;

select * from 表 A, 表 B where 判断条件;

select a.*, ad.* from test_a as a inner join test_a_description as ad on a.id=ad.parent_id;

select a.*, ad.* from test_a as a, test_a_description as ad where a.id=ad.parent_id;

结果:

image-20220518161920078

二、外连接

1、左外连接(left join)以左表为主表(查询全部), 右表为辅表(没有的显示 null)

SQL:select * from 表 A left join 表 B on 判断条件;

select a.*, ad.* from test_a as a left join test_a_description as ad on a.id=ad.parent_id;

结果:

image-20220518161914487

2、右外连接(right join)

以右表为主表(查询全部), 左表为辅表(没有的显示 null)

SQL:select * from 表 A right join 表 B on 判断条件;

select a.*, ad.* from test_a as a right join test_a_description as ad on a.id=ad.parent_id;

结果:

image-20220518161908646

三、全连接(full join)

两个表的所有数据都展示出来

SQL:select * from 表 A full join 表 B on 判断条件;

select a.*, ad.* from test_a as a full join test_a_description as ad on a.id=ad.parent_id;

结果:

image-20220518161902025

四、联合 (合并) 查询(union)

MySQL 不识别 FULL join,所以可以通过 union 来实现

SQL:

select a.*, ad.* from test_a as a left join test_a_description as ad on a.id=ad.parent_id
union
select a.*, ad.* from test_a as a right join test_a_description as ad on a.id=ad.parent_id;

结果:

image-20220518161855667

五、区别分析

外连接:外连不但返回符合连接和查询条件的数据行,还返回不符合条件的一些行。外连接分三类:左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)

三者的共同点是都返回符合连接条件和查询条件(即:内连接)的数据行。不同点如下:

左外连接:还返回左表中不符合连接条件单符合查询条件的数据行。

右外连接:还返回右表中不符合连接条件单符合查询条件的数据行。

全外连接:还返回左表中不符合连接条件单符合查询条件的数据行,并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集(去掉重复),即 “全外 = 左外 UNION 右外”。

说明:左表就是在 “(LEFT OUTER JOIN)” 关键字左边的表。右表当然就是右边的了。在三种类型的外连接中,OUTER 关键字是可省略的。

六、SQL 查询的基本原理

单表查询:根据 WHERE 条件过滤表中的记录,形成中间表(这个中间表对用户是不可见的);然后根据 SELECT 的选择列选择相应的列进行返回最终结果。

两表连接查询:对两表求积(笛卡尔积)并用 ON 条件和连接连接类型进行过滤形成中间表;然后根据 WHERE 条件过滤中间表的记录,并根据 SELECT 指定的列返回查询结果。

多表连接查询:先对第一个和第二个表按照两表连接做查询,然后用查询结果和第三个表做连接查询,以此类推,直到所有的表都连接上为止,最终形成一个中间的结果表,然后根据 WHERE 条件过滤中间表的记录,并根据 SELECT 指定的列返回查询结果。理解 SQL 查询的过程是进行 SQL 优化的理论依据。

引擎

MySQL 存储引擎 MyISAM 与 InnoDB 区别

存储引擎 Storage engine:MySQL 中的数据、索引以及其他对象是如何存储的,是一套文件系统的实现。

常用的存储引擎有以下:

  • Innodb 引擎:Innodb 引擎提供了对数据库 ACID 事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
  • MyISAM 引擎(原本 MySQL 的默认引擎):不提供事务的支持,也不支持行级锁和外键。
  • MEMORY 引擎:所有的数据都在内存中,数据的处理速度快,但是安全性不高。

MyISAM 与 InnoDB 区别

Innodb MyISAM
存储结构 每张表都保存在同一个数据文件中 每张表被存放在三个文件:表定义文件、数据文件、索引文件
数据和索引存储方式 数据和索引是集中存储的,查询时做到覆盖索引会非常高效 数据和索引是分开存储的,索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据
记录存储顺序 按主键大小有序插入 按记录插入顺序保存
索引 聚簇索引 非聚簇索引
索引的实现方式 B+树索引,Innodb 是索引组织表 B+树索引,myisam 是堆表
全文索引 不支持 支持
哈希索引 支持 不支持
外键 支持 不支持
事务 支持 不支持
锁粒度(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的) 行级锁定、表级锁定,锁定力度越小并发能力越高 表级锁定
SELECT

| MyISAM更优 |
| select count(*) |

| myisam更快,因为myisam内部维护了一个计数器,可以直接调取。 |
| INSERT、UPDATE、DELETE | InnoDB更优 |

|

存储引擎选择

MyISAM:适用于管理非事务表,它提供高速存储和检索, 以及全文搜索能力的场景。比如博客系统、新闻门户网站。

InnoDB:适用于更新操作频繁,或者要保证数据的完整性,并发量高,支持事务和外键的场景。比如 OA 自动化办公系统。

如果没有特别的需求,使用默认的Innodb即可。

索引

什么是索引?

索引是一种数据结构,是数据库管理系统中一个排序的数据结构,以协助快速查询数据库表中数据。索引的实现通常使用 B + 树或 hash 表。

更通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。

索引有哪些优缺点?

索引的优点

  • 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
  • 通过使用索引,可以在查询的过程中,使用优化器,提高系统的性能。

索引的缺点

  • 时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增 / 删 / 改的执行效率;
  • 空间方面:索引需要占物理空间。

索引有哪几种类型?

主键索引:数据列不允许重复,不允许为 NULL,一个表只能有一个主键。

唯一索引:数据列不允许重复,允许为 NULL 值,一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引:基本的索引类型,没有唯一性的限制,允许为 NULL 值,一个表允许多个列创建普通索引。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引:是目前搜索引擎使用的一种关键技术,MyISAM 存储引擎才有全文索引。

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

索引的数据结构(B + 树,Hash)

在 MySQL 中使用较多的索引有Hash 索引B + 树索引等,索引的数据结构和具体存储引擎的实现有关,而我们经常使用的 InnoDB 存储引擎,默认索引实现为:B + 树索引。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录等值查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择 B + 树索引。

1)B + 树索引

MySQL 通过存储引擎存取数据,基本上 90% 的人用的就是 InnoDB 了,按照实现方式分,InnoDB 的索引类型目前只有两种:BTREE(B 树)索引和 HASH 索引。B 树索引是 MySQL 数据库中使用最频繁的索引类型,基本所有存储引擎都支持 BTree 索引。通常我们说的索引不出意外指的就是(B 树)索引(实际是用 B + 树实现的,因为在查看表索引时,mysql 一律打印 BTREE,所以简称为 B 树索引)

B + 树数据结构

image-20220518161847352

众所周知,一颗传统的 M 阶 B + 树需要满足以下几个要求:

  • 从根节点到叶节点的所有路径都具有相同的长度
  • 所有数据信息都存储在叶子节点,非叶子节点仅作为叶节点的索引存在
  • 叶子节点通过指针连在一起
  • 根节点至少拥有两个子树
  • 每个树节点最多拥有 M 个子树
  • 每个树节点 (除了根节点) 拥有至少 M/2 个子树

B + 树是为了磁盘及其他存储辅助设备而设计的一种平衡查找树 (不是二叉树),在 B + 树中,所有记录的节点按大小顺序存放在同一层的叶节点中,各叶子节点用指针进行连接,而 B + 树索引本质上就是 B + 树在数据库中的实现,与纯粹的 B + 树数据结构还是有点区别。

B + 树与 B + 树索引的区别如下:

B+树 B+树索引
存储位置 内存 磁盘
扇出率
并发控制 可以不考虑 需考虑
分裂方向 不需要考虑 向左、向右

B + 树的一些特性:

1、B + 树中的 B 不是代表的二叉(Binary) ,而是代表平衡(Balance),因为 B + 树是从最早的平衡二叉树演化而来,但是 B + 树不是一个二叉树。

2、B + 树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树,在 B + 树中,所有的记录节点都是按照键值大小顺序存在同一层的叶子节点,由叶子节点指针进行相连。

3、B + 树在数据库中的特点就是高扇出,因此在数据库中 B + 树的高度一般都在 2~4 层,这也就是说查找一个键值记录时,最多只需要 2 到 4 次 IO,当前的机械硬盘每秒至少可以有 100 次 IO,2~4 次 IO 意味着查询时间只需要 0.02~0.04 秒。

4、B + 树索引并不能找到一个给定键值的具体行,B + 树索引能找到的只是被查找的键值所在行的页,然后数据库把页读到内存,再内存中进行查找,最后找到要查找的数据。

5、数据库中 B + 树索引可以分为,聚簇索引和非聚簇索引,但是不管是聚簇索引还是非聚簇索引,其内部都是 B + 树实现的,即高度是平衡的,叶子节点存放着所有的数据,聚簇索引和非聚簇索引不同的是,叶子节点是否存储的是一整行信息。每张表只能有一个聚簇索引。

6、B + 树的每个数据页(叶子节点)是通过一个双向链表进行链接,数据页上的数据的顺序是按照主键顺序存储的。

2)哈希索引

简要说下,类似于数据结构中简单实现的 HASH 表(散列表)一样,当我们在 MySQL 中用哈希索引时,主要就是通过 Hash 算法(常见的 Hash 算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的 Hash 值,与这条数据的行指针一并存入 Hash 表的对应位置;如果发生 Hash 碰撞(两个不同关键字的 Hash 值相同),则在对应 Hash 键下以链表形式存储。当然这只是简略模拟图。

image-20220518161840662

数据库为什么使用 B + 树而不是 B 树

  • B + 树的叶子节点存储了所有的数据,非叶子节点中存储的是比较关键字。而 B 树所有的节点都会存储数据。B + 树的叶子节点之间存在一个指针连接,B 树不存在指针连接。B + 树这种设计结构能带来什么好处呢?B + 树所有的数据都存储在叶子节点,那么顺着叶子节点从左往右即可完成对数据的遍历,极大了简化了排序操作。这也是 mysql 设计索引是采用 B + 树的原因,不仅仅能方便查找,而且有助于排序,在 mysql 的索引中叶子节点之间数双向链表可正反遍历,更加灵活;
  • B 树只适合随机检索,而 B + 树同时支持随机检索和顺序检索
  • B + 树空间利用率更高,可减少 I/O 次数,磁盘读写代价更低。一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘 I/O 消耗。B + 树的内部结点并没有指向关键字具体信息的指针,只是作为索引使用,其内部结点比 B 树小,盘块能容纳的结点中关键字数量更多,一次性读入内存中可以查找的关键字也就越多,相对的,IO 读写次数也就降低了。而 IO 读写次数是影响索引检索效率的最大因素;
  • B + 树的查询效率更加稳定。B 树搜索有可能会在非叶子结点结束,越靠近根节点的记录查找时间越短,只要找到关键字即可确定记录的存在,其性能等价于在关键字全集内做一次二分查找。而在 B + 树中,顺序检索比较明显,随机检索时,任何关键字的查找都必须走一条从根节点到叶节点的路,所有关键字的查找路径长度相同,导致每一个关键字的查询效率相当。
  • B - 树在提高了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题。B + 树的叶子节点使用指针顺序连接在一起,只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而 B 树不支持这样的操作。
  • 增删文件(节点)时,效率更高。因为 B + 树的叶子节点包含所有关键字,并以有序的链表结构存储,这样可很好提高增删效率。

索引算法有哪些?

索引算法有 BTree 算法和 Hash 算法

BTree 算法

BTree 是最常用的 mysql 数据库索引算法,也是 mysql 默认的算法。因为它不仅可以被用在 =,>,>=,<,<= 和 between 这些比较操作符上,而且还可以用于 like 操作符,只要它的查询条件是一个不以通配符开头的常量, 例如:

-- 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like 'jack%';
-- 如果一通配符开头,或者没有使用常量,则不会使用索引,例如:
select * from user where name like '%jack';

Hash 算法

Hash 算法只能用于对等比较,例如 =,<=>(相当于 =)操作符。由于是一次定位数据,不像 BTree 索引需要从根节点到枝节点,最后才能访问到叶子节点这样多次 IO 访问,所以检索效率远高于 BTree 索引。

创建索引的原则?索引设计的原则?

索引虽好,但也不是无限制的使用,最好符合以下几个原则

  1. 为常作为查询条件的字段建立索引,where 子句中的列,或者连接子句中指定的列
  2. 为经常需要排序、分组操作的字段建立索引
  3. 更新频繁字段不适合创建索引
  4. 不能有效区分数据的列不适合做索引列 (如性别,男女未知,最多也就三种,区分度实在太低)
  5. 对于定义为 text、image 和 bit 的数据类型的列不要建立索引
  6. 最左前缀原则,就是最左边的优先。指的是联合索引中,优先走最左边列的索引。对于多个字段的联合索引,如 index(a,b,c) 联合索引,则相当于创建了 a 单列索引,(a,b)联合索引,和 (a,b,c) 联合索引(但并不是建立了多个索引树)。mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配,比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引,d 是用不到索引的,如果建立 (a,b,d,c) 的索引则都可以用到,a,b,d 的顺序可以任意调整。使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间
  7. 非空字段:应该指定列为 NOT NULL,除非你想存储 NULL。在 mysql 中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值
  8. 不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长

什么情况使用了索引,查询还是慢

  • 索引全表扫描
  • 索引过滤性不好
  • 频繁回表的开销

MySQL 使用自增主键的好处

  1. 自增主键按顺序存放,增删数据速度快,对于检索非常有利;
  2. 数字型,占用空间小,易排序;
  3. 使用整形才可以使用 AUTO_INCREAMENT,不用担心主键重复问题。

什么是聚簇索引?何时使用聚簇索引与非聚簇索引

image-20220518161832239

  • 聚簇索引:将数据与索引放到了一块,索引结构的叶子节点存储了行数据,找到索引也就找到了数据
  • 非聚簇索引:将数据与索引分开存储,索引结构的叶子节点存储的是行数据的地址

聚簇索引的优点

  • 数据访问更快。聚族索引将索引和数据保存在同一个 B + 树中,因此从聚族索引中获取数据通常比非聚族索引中查找更快。
  • 当你需要取出一定范围内的数据时,用聚簇索引也比用非聚簇索引好。
  • 使用覆盖索引扫描的查询可以直接使用节点中的主键值。

聚簇索引的缺点

  • 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列作为主键。
  • 更新主键的代价很高,因为将会导致被更新的行移动。因此,对于 InnoDB 表,我们一般定义主键为不可更新。
  • 通过辅助索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

几个概念

  • 对于普通索引,如 name 字段,则需要根据 name 字段的索引树(非聚簇索引)找到叶子节点对应的主键,然后再通过主键去主键索引树查询一遍,才可以得到要找的记录,这就叫回表查询。先定位主键值,再定位行记录,它的性能较扫描一遍索引树的效率更低
  • InnoDB 的行锁是建立在索引的基础之上的,行锁锁的是索引,不是数据,所以提高并发写的能力要在查询字段添加索引
  • 主索引和辅助索引:主索引就是主键索引,辅助索引就是根据业务需要,自己设置的普通的非主键的索引。这个在 Myisam 里面区别不大,但是在 Innodb 的时候差别很大
  • 聚簇索引:Innodb 的主索引采用的是聚簇索引,一个表只能有 1 个聚簇索引,因为表数据存储的物理位置是唯一的。聚簇索引的 value 存的就是真实的数据,不是数据的地址。主索引树里面包含了真实的数据。key 是主键值,value 值就是 data,key 值按照 B + 树的规则分散排布的叶子节点。
  • 非聚簇索引:Myisam 的主索引和辅助索引都采用的是非聚簇索引,索引和表数据是分离的,索引的 value 值存储的是行数据的地址。
  • Innodb 的索引:主索引采用聚簇索引,叶子节点的 value 值,直接存储的真实的数据。辅助索引是非聚簇索引,value 值指向主索引的位置。所以在 Innodb 中,根据辅助索引查询值需要遍历 2 次 B + 树,同时主键的长度越短越好,越短辅助索引的 value 值就越小。Innodb 中根据主键进行范围查询,会特别快。
  • Myisam 的索引:主索引和辅助索引都是非聚簇索引
  • B + 树:不管是什么索引,在 mysql 中的数据结构都是 B + 树的结构,可以充分利用数据块,来减少 IO 查询的次数,提升查询的效率。一个数据块 data 里面,存储了很多个相邻 key 的 value 值,所有的非叶子节点都不存储数据,都是指针。
  • mysql 采用 B + 树的优点:IO 读取次数少(每次都是页读取),范围查找更快捷(相邻页之间有指针)

联合索引是什么?组合索引是什么?

MySQL 可以使用多个字段组合建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。

联合索引数据结构和实现原理,使用联合索引是怎么进行查询的

假设,我们对 (a,b) 字段建立索引,那么入下图所示

image-20220518161822002

如上图所示他们是按照 a 来进行排序,在 a 相等的情况下,才按 b 来排序。

因此,我们可以看到 a 是有序的 1,1,2,2,3,3。而 b 是一种全局无序,局部相对有序状态!什么意思呢?

从全局来看,b 的值为 1,2,1,4,1,2,是无序的,因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看,当 a 的值确定的时候,b 是有序的。例如 a = 1 时,b 值为 1,2 是有序的状态。当 a=2 时候,b 的值为 1,4 也是有序状态。因此,你执行a = 1 and b = 2是 a,b 字段能用到索引的。而你执行a > 1 and b = 2时,a 字段能用到索引,b 字段用不到索引。因为 a 的值此时是一个范围,不是固定的,在这个范围内 b 值不是有序的,因此 b 字段用不上索引。

综上所示,最左匹配原则,在遇到范围查询的时候,就会停止匹配。

什么是最左前缀原则?什么是最左匹配原则?为什么需要注意联合索引中的顺序?

  • 最左前缀原则,就是最左边的优先。指的是联合索引中,优先走最左边列的索引。对于多个字段的联合索引,如 index(a,b,c) 联合索引,则相当于创建了 a 单列索引,(a,b)联合索引,和 (a,b,c) 联合索引(但并不是建立了多个索引树)。mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配,比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引,d 是用不到索引的,如果建立 (a,b,d,c) 的索引则都可以用到,a,b,d 的顺序可以任意调整。
  • = 和 in 可以乱序,比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序,mysql 的查询优化器会帮你优化成索引可以识别的形式。
  • 如果建立的索引顺序是 (a,b) 那么直接采用 where b = 5 这种查询条件是无法利用到索引的,这一条最能体现最左匹配的特性。

为什么不推荐使用外键与级联?

  1. 增加了复杂性: a. 每次做DELETE 或者UPDATE都必须考虑外键约束,会导致开发的时候很痛苦, 测试数据极为不方便; b. 外键的主从关系是定的,假如那天需求有变化,数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。
  2. 增加了额外工作: 数据库需要增加维护外键的工作,比如当我们做一些涉及外键字段的增,删,更新操作之后,需要触发相关操作去检查,保证数据的的一致性和正确性,这样会不得不消耗资源;(个人觉得这个不是不用外键的原因,因为即使你不使用外键,你在应用层面也还是要保证的。所以,我觉得这个影响可以忽略不计。)
  3. 对分库分表不友好 :因为分库分表下外键是无法生效的。

如果系统不涉及分库分表,并发量不是很高的情况还是可以考虑使用外键的。

事务

什么是数据库事务?

事务是逻辑上的一组操作,要么都执行,要么都不执行。

事务的四大特性 (ACID) 介绍一下?

关系性数据库需要遵循 ACID 规则,具体内容如下:

image-20220518161814366

特性 说明
原子性 Atomic 事务是最小的执行单位,不允许分割。事务包含的所有操作要么全部成功,要么全部失败回滚。
一致性 Consistency 事务执行之前和执行之后都必须处于一致性状态。举例:拿转账来说,假设用户 A 和用户 B 两者的钱加起来一共是 5000,那么不管 A 和 B 之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是 5000,这就是事务的一致性。
隔离性 Isolation 隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间是相互隔离的。数据库规定了多种事务隔离级别,不同的隔离级别对应不同的干扰程度。隔离级别越高,数据一致性越好,但并发性越差。
持久性 Durability 持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下,也不会丢失提交事务的操作。

什么是脏读?不可重复读?幻读?

image-20220518161809002

  • 脏读 (Dirty Read):一个事务读取到另外一个事务未提交的数据。举例:一个事务 1 读取了被另一个事务 2 修改但还未提交的数据。由于某种异常事务 2 回滚,则事务 1 读取的是无效数据。

image-20220518161802093

  • 不可重复读 (Non-repeatable read):一个事务读取同一条记录 2 次,得到的结果不一致。这可能是两次查询过程中间,另一个事务更新了这条记录。

image-20220518161755752

  • 幻读 (Phantom Read):幻读发生在两个完全相同的查询,得到的结果不一致。这可能是两次查询过程中间,另一个事务增加或者减少了行记录。

不可重复度和幻读区别

不可重复读的重点是修改,幻读的重点在于新增或者删除

什么是事务的隔离级别?MySQL 的默认的隔离级别是什么?

为了达到事务的四大特性,数据库定义了 4 种不同的事务隔离级别,由低到高依次为 Read uncommitted、Read committed、Repeatable read、Serializable,后三个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED
READ-COMMITTED ×
REPEATABLE-READ × ×
SERIALIZABLE × × ×

SQL 标准定义了四个隔离级别

  • READ-UNCOMMITTED(读未提交):最低的隔离级别,一个事务可以读取另一个事务更新但未提交的数据。可能会导致脏读、不可重复读或幻读
  • READ-COMMITTED(读已提交):一个事务提交后才能被其他事务读取到,可以阻止脏读,但是不可重复读或幻读仍有可能发生
  • REPEATABLE-READ(可重复读):对同一记录的多次读取结果都是一致的,除非数据是被本身事务所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生
  • SERIALIZABLE(可串行化):最高的隔离级别,完全服从 ACID 的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读

这里需要注意的是:MySQL 默认采用的 REPEATABLE_READ 隔离级别,Oracle 默认采用的 READ_COMMITTED 隔离级别

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是 MVVC(多版本并发控制),通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取已提交),但是你要知道的是 InnoDB 存储引擎默认使用 **REPEATABLE-READ(可重复读)**并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别。

MySQL 数据库可重复读隔离级别是怎么实现的,MVCC 并发版本控制原理

MySQL 事务与 MVCC 原理

MVCC 的实现依赖于:隐藏字段、Read View、undo log。在内部实现中,InnoDB 通过数据行的 DB_TRX_ID(事务 ID) 和 Read View 来判断数据的可见性,如不可见,则通过数据行的 DB_ROLL_PTR(回滚指针) 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的,在同一个事务中,用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

1、隐藏字段

InnDB 中每个事务都有一个唯一的事务 ID,记为 transaction_id。它在事务开始时向 InnDB 申请,按照时间先后严格递增。

而每行数据其实都有多个版本,这就依赖 undo log 来实现了。每次事务更新数据就会生成一个新的数据版本,并把 transaction_id 记为 row trx_id。同时旧的数据版本会保留在 undo log 中,而且新的版本会记录旧版本的回滚指针,通过它直接拿到上一个版本。
所以,InnDB 中的 MVCC 其实是通过在每行记录后面保存两个隐藏的列来实现的。一列是事务 ID:trx_id;另一列是回滚指针:roll_pt。

2、read-view

read view 中主要包含当前系统中还有哪些活跃的读写事务,在实现上 InnDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正活跃(还未提交)的事务

前面说了事务 ID 随时间严格递增的,把系统中已提交的事务 ID 的最大值记为数组的低水位,已创建过的事务 ID + 1 记为高水位

这个视图数组和高水位就组成了当前事务的一致性视图(read view)

个数组画个图,长这样:

image.png

规则如下:

  • 1 如果 trx_id 在灰色区域,表明被访问版本的 trx_id 小于数组中低水位的 id 值,也即生成该版本的事务在生成 read view 前已经提交,所以该版本可见,可以被当前事务访问。
  • 2 如果 trx_id 在橙色区域,表明被访问版本的 trx_id 大于数组中高水位的 id 值,也即生成该版本的事务在生成 read view 后才生成,所以该版本不可见,不能被当前事务访问。
  • 3 如果在绿色区域,就会有两种情况:
    • a) trx_id 在数组中,证明这个版本是由还未提交的事务生成的,不可见
    • b) trx_id 不在数组中,证明这个版本是由已提交的事务生成的,可见

3、undo-log

undo log 主要有两个作用:

  • 当事务回滚时用于将数据恢复到修改前的样子
  • 另一个作用是 MVCC ,当读取记录时,若该记录被其他事务占用或当前版本对该事务不可见,则可以通过 undo log 读取之前的版本数据,以此实现非锁定读

在 InnoDB 存储引擎中 undo log 分为两种: insert undo log 和 update undo log:

  1. insert undo log :指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见,对其他事务不可见,故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
  2. update undo log :update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制,因此不能在事务提交时就进行删除。提交时放入 undo log 链表,等待 purge线程 进行最后的删除

image.png

不同事务或者相同事务的对同一记录行的修改,会使该记录行的 undo log 成为一条链表,链首就是最新的记录,链尾就是最早的旧记录。

RC 和 RR 隔离级别下 MVCC 的差异

对于「读提交」和「可重复读」隔离级别的事务来说,它们是通过 **Read View **来实现的,它们的区别在于创建 Read View 的时机不同:

  • 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View,也意味着,事务期间的多次读取同一条数据,前后两次读的数据可能会出现不一致,因为可能这期间另外一个事务修改了该记录,并提交了事务。
  • 「可重复读」隔离级别是启动事务时生成一个 Read View,然后整个事务期间都在用这个 Read View,这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对,来控制并发事务访问同一个记录时的行为,这就叫 MVCC(多版本并发控制)

MVCC➕Next-key-Lock 防止幻读

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题:
1、执行普通 select,此时会以 MVCC 快照读的方式读取数据
在快照读的情况下,RR 隔离级别只会在事务开启后的第一次查询生成 Read View ,并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见,实现了可重复读和防止快照读下的 “幻读”
2、执行 select…for update/lock in share mode、insert、update、delete 等当前读
在当前读下,读取的都是最新的数据,如果其它事务有插入新的记录,并且刚好在当前事务查询范围内,就会产生幻读!InnoDB 使用 Next-key Lockopen in new window 来防止这种情况。当执行当前读时,会锁定读取到的记录的同时,锁定它们的间隙,防止其它事务在查询范围内插入数据。只要我不让你插入,就不会发生幻读

SQL 优化

如何定位及优化 SQL 语句的性能问题?创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

对于低性能的 SQL 语句的定位,最重要也是最有效的方法就是使用执行计划,MySQL 提供了 explain 命令来查看语句的执行计划。我们知道,不管是哪种数据库,或者是哪种数据库引擎,在对一条 SQL 语句进行执行的过程中都会做很多相关的优化,对于查询语句,最重要的优化方式就是使用索引。而执行计划,就是显示数据库引擎对于 SQL 语句执行的详细情况,其中包含了是否使用索引,使用什么索引,使用的索引的相关信息等

image-20220518161728330

执行计划包含的信息

id 由一组数字组成。表示一个查询中各个子查询的执行顺序;

  • id 相同执行顺序由上至下。
  • id 不同,id 值越大优先级越高,越先被执行。
  • id 为 null 时,表示一个合并结果集的操作的执行 id 为 null,常出现在包含 union 等查询语句中。

select_type 每个子查询的查询类型,一些常见的查询类型。

id select_type description
1 SIMPLE 不包含任何子查询或 union 查询
2 PRIMARY 包含子查询时最外层查询就显示为 PRIMARY
3 SUBQUERY 在 select 或 where 子句中出现的子查询
4 DERIVED from 字句中出现的子查询
5 UNION union 连接的两个 select 查询,第一个查询是 dervied 派生表,除了第一个表外,第二个以后的表 select_type 都是 union。
6 UNION RESULT 包含 union 的结果集,在 union 和 union all 语句中,因为它不需要参与查询,所以 id 字段为 null
7 dependent subquery 与 dependent union 类似,表示这个 subquery 的查询要受到外部表查询的影响。
8 dependent union 与 union 一样,出现在 union 或 union all 语句中,但是这个查询要受到外部查询的影响

table 显示的查询表名,如果查询使用了别名,那么这里显示的是别名。

type访问类型 (非常重要,可以看到有没有走索引)

依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL。

除了 all 之外,其他的 type 都可以使用到索引,除了 index_merge 之外,其他的 type 只可以用到一个索引。

类型 描述
system 表中只有一行数据或者是空表,且只能用于 myisam 和 memory 表。如果是 Innodb 引擎表,type 列在这个情况通常都是 all 或者 index。
const 使用唯一索引或者主键,返回记录是 1 行记录的等值 where 条件时,通常 type 是 const。其他数据库也叫做唯一索引扫描。
eq_ref 出现在要连接多个表的查询计划中,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为 not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现 eq_ref。
ref 像 eq_ref 那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见于普通索引的等值查找。或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现。
fulltext 全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql 不管代价,优先选择使用全文索引。
ref_or_null 与 ref 方法类似,只是增加了 null 值的比较。实际用的不多。
unique_subquery 用于 where 中的 in 形式子查询,子查询返回不重复值唯一值。
index_subquery 用于 in 形式子查询使用到了辅助索引或者 in 常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
range 索引范围扫描,常见于使用 >,<,is null,between ,in ,like 等运算符的查询中。
index_merge 表示查询使用了两个以上的索引,最后取交集或者并集。常见 and ,or 的条件使用了不同的索引,官方排序这个在 ref_or_null 之后,但是实际上由于要读取多个索引,性能可能都不如 range。
index 索引全表扫描。把索引从头到尾扫一遍,常见于使用索引列就可以处理,不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
ALL 全表扫描数据文件

possible_keys 可能使用的索引,注意不一定会使用。查询涉及到的字段上若存在索引,则该索引将被列出来。当该列为 NULL 时就要考虑当前的 SQL 是否需要优化了。

key 显示 MySQL 在查询中实际使用的索引,若没有使用索引,显示为 NULL。

key_length 索引长度

ref 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

rows 这里是执行计划中估算的扫描行数,不是精确值。

extra 的信息非常丰富,常见的有:

  1. Using index 使用覆盖索引
  2. Using where 使用了 where 子句来过滤结果集
  3. Using filesort 使用文件排序,使用非索引列进行排序时出现,非常消耗性能,尽量优化。
  4. Using temporary 使用了临时表

SQL 优化的目标可以参考阿里开发手册

【推荐】SQL性能优化的目标:至少要达到 range 级别,要求是ref级别,如果可以是consts最好。  
说明:  
1) consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。  
2) ref 指的是使用普通的索引(normal index)。  
3) range 对索引进行范围检索。  
反例:explain表的结果,type=index,索引物理文件全扫描,速度非常慢,这个index级别比较range还低,与全表扫描是小巫见大巫。

SQL 的生命周期?一条 SQL 查询语句是如何执行的?MySQL 总体架构 —>SQL 执行流程 —> 语句执行顺序

一条 SQL 语句在 MySQL 中如何被执行的? | JavaGuide

常用 SQL 查询语句优化方法

  • 不要使用 select * from t,用具体的字段列表代替 “*”,使用星号会降低查询效率,如果数据库字段改变,可能出现不可预知隐患。
  • 应尽量避免在 where 子句中使用!= 或 <> 操作符,避免在 where 子句中字段进行 null 值判断,存储引擎将放弃使用索引而进行全表扫描。
  • 避免使用左模糊,左模糊查询将导致全表扫描。
  • IN 语句查询时包含的值不应过多,否则将导致全表扫描。
  • 为经常作为查询条件的字段,经常需要排序、分组操作的字段建立索引。
  • 在使用联合索引字段作为条件时,应遵循最左前缀原则。
  • OR 前后两个条件都要有索引,整个 SQL 才会使用索引,只要有一个条件没索引整个 SQL 就不会使用索引。
  • 尽量用 union all 代替 union,union 需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的 CPU 运算,加大资源消耗及延迟。

数据库优化

数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表

对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

增加中间表

对于需要经常联合查询的表,可以建立中间表以提高查询效率。

通过建立中间表,将需要通过联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询。

增加冗余字段

设计数据表时应尽量遵循范式理论的规约,尽可能的减少冗余字段,让数据库设计看起来精致、优雅。但是,合理的加入冗余字段可以提高查询速度。

表的规范化程度越高,表和表之间的关系越多,需要连接查询的情况也就越多,性能也就越差。

注意:

冗余字段的值在一个表中修改了,就要想办法在其他表中更新,否则就会导致数据不一致的问题。

大表怎么优化?某个表有近千万数据,CRUD 比较慢,如何优化?分库分表是怎么做的?分表分库了有什么问题?有用到中间件么?他们的原理知道么?

当 MySQL 单表记录数过大时,数据库的 CRUD 性能会明显下降,一些常见的优化措施如下:

  1. 限定数据的查询范围: 务必禁止不带任何限制数据范围条件的查询语句。比如:当用户在查询订单历史的时候,我们可以控制在一个月的范围内;
  2. 读 / 写分离: 经典的数据库拆分方案,主库负责写,从库负责读;
  3. 缓存: 使用 MySQL 的缓存,另外对重量级、更新少的数据可以考虑使用应用级别的缓存;
  4. 分库分表

分库分表主要有垂直分表和水平分表

  1. 垂直分表:
    垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表。例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。如下图所示,这样来说大家应该就更容易理解了。
    image-20220518161717083
    适用场景:如果一个表中某些列常用,另外一些列不常用
    垂直拆分的优点:可以使得行数据变小,在查询时减少读取的 Block 数,减少 I/O 次数。此外,垂直分区可以简化表的结构,易于维护。
    垂直拆分的缺点:主键会出现冗余,需要管理冗余列,并会引起 Join 操作,可以通过在应用层进行 Join 来解决。对于应用层来说,逻辑算法增加开发成本。此外,垂直分区会让事务变得更加复杂;
  2. 水平分表:
    保持数据表结构不变,通过某种策略进行存储数据分片。这样每一片数据分散到不同的表或者库中,达到了分布式的目的。水平拆分可以支撑非常大的数据量。
    水平拆分是指数据表行的拆分,表的行数超过 200 万行时,就会变慢,这时可以把一张表的数据拆成多张表来存放。举个例子:我们可以将用户信息表拆分成多个用户信息表,这样就可以避免单一表数据量过大对性能造成影响。
    image-20220518161709644
    水平拆分可以支持非常大的数据量。需要注意的一点是:水平分表仅仅是解决了单一表数据过大的问题,但由于表的数据还是在同一台机器上,其实对于提升 MySQL 并发能力没有什么意义,所以 水平拆分最好分库
    适用场景:支持非常大的数据量存储
    水平拆分优点:支持非常大的数据量存储
    水平拆分缺点:给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需 UNION 操作;分片事务难以解决 ,跨库 join 性能较差,逻辑复杂。

《Java 工程师修炼之道》的作者推荐 尽量不要对数据进行分片,因为拆分会带来逻辑、部署、运维的各种复杂度 ,一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择客户端分片架构,这样可以减少一次和中间件的网络 I/O。

下面补充一下数据库分片的两种常见方案

  • 客户端代理: 分片逻辑在应用端,封装在 jar 包中,通过修改或者封装 JDBC 层来实现。 当当网的 Sharding-JDBC 、阿里的 TDDL 是两种比较常用的实现。
  • 中间件代理: 在应用和数据库中间加了一个代理层。分片逻辑统一维护在中间件服务中。 Mycat 、360 的 Atlas、网易的 DDB 等等都是这种架构的实现。

MySQL 的主从复制原理以及流程

主从复制:将主数据库中的 DDL 和 DML 操作通过二进制日志(BINLOG)传输到从数据库上,然后将这些日志重新执行,从而使得从数据库的数据与主数据库保持一致。

主从复制的作用

  1. 高可用和故障切换:主数据库出现问题,可以切换到从数据库。
  2. 负载均衡:可以进行数据库层面的读写分离。
  3. 数据备份:可以在从数据库上进行日常备份。

复制过程

image-20220518161702026

Binary log:主数据库的二进制日志

Relay log:从数据库的中继日志

第一步:master 在每个事务更新数据完成之前,将该操作记录串行地写入到 binlog 文件中。

第二步:salve 开启一个 I/O Thread,该线程在 master 打开一个普通连接,将这些事件写入到中继日志中。如果读取的进度已经跟上了 master,就进入睡眠状态并等待 master 产生新的事件。

第三步:SQL Thread 会读取中继日志,并顺序执行该日志中的 SQL 事件,从而与主数据库中的数据保持一致。

读写分离有哪些解决方案?

读写分离是依赖于主从复制,而主从复制又是为读写分离服务的。主从复制要求slave不能写只能读

方案一

利用中间件来做代理,使用 mysql-proxy 代理,负责对数据库的请求识别出读还是写,并分发到不同的数据库中。

优点:直接实现读写分离和负载均衡,不用修改代码,数据库和应用程序弱耦合,master 和 slave 用一样的帐号,mysql 官方不建议实际生产中使用

缺点:降低性能, 不支持事务,代理存在性能瓶颈和可靠性风险增加。

方案二

使用 AbstractRoutingDataSource+aop+annotation 在 dao 层决定数据源。

如果采用了 mybatis, 可以将读写分离放在 ORM 层,比如 mybatis 可以通过 mybatis plugin 拦截 sql 语句,所有的 insert/update/delete 都访问 master 库,所有的 select 都访问 salve 库,这样对于 dao 层都是透明。plugin 实现时可以通过注解或者分析语句是读写方法来选定主从库。

不过这样依然有一个问题, 也就是不支持事务, 所以我们还需要重写一下 DataSourceTransactionManager, 将 read-only 的事务扔进读库, 其余的有读有写的扔进写库。

方案三

使用 AbstractRoutingDataSource+aop+annotation 在 service 层决定数据源,可以支持事务

缺点:类内部方法通过 this.xx() 方式相互调用时,aop 不会进行拦截,需进行特殊处理。

大数据量处理

大批量数据删除,怎么一次删除 100 万条数据

方法一:

在 MySQL 数据库使用中,有的表存储数据量比较大,达到每天三百万条记录左右,此表中建立了三个索引,这些索引都是必须的,其他程序要使用。由于要求此表中的数据只保留当天的数据,所以每当在凌晨的某一时刻当其他程序处理万其中的数据后要删除该表中昨天以及以前的数据,使用 delete 删除表中的上百万条记录时,MySQL 删除速度非常缓慢每一万条记录需要大概 4 分钟左右,这样删除所有无用数据要达到八个小时以上,这是难以接受的。

查询 MySQL 官方手册得知删除数据的速度和创建的索引数量是成正比的,于是删除掉其中的两个索引后测试,发现此时删除速度相当快,一百万条记录在一分钟多一些,可是这两个索引其他模块在每天一次的数据整理中还要使用,于是想到了一个折中的办法:

在删除数据之前删除这两个索引,此时需要三分钟多一些,然后删除其中无用数据,此过程需要不到两分钟,删除完成后重新创建索引,因为此时数据库中的数据相对较少,约三四十万条记录 (此表中的数据每小时会增加约十万条),创建索引也非常快,约十分钟左右。这样整个删除过程只需要约 15 分钟。对比之前的八个小时,大大节省了时间。

删除大表的部分数据,通常采用以下步骤:

删除大表的多行数据时,会超出 innod block table size 的限制,最小化的减少锁表时间的方案是:

1、选择不需要删除的数据,并把它们存在一张相同结构的空表里

2、重命名原始表,并给新表命名为原始表名

3、删掉原始表

方法二:

分批删除,如果你要删除一个表里面的前 10000 行数据,有以下三种方法可以做到:

  • 第一种,直接执行 delete from T limit 10000;
  • 第二种,在一个连接中循环执行 20 次 delete from T limit 500;
  • 第三种,在 20 个连接中同时执行 delete from T limit 500。

方案一,单个语句占用锁的时间较长,会导致其他客户端等待资源时间较长。

方案二,串行化执行,将相对长的事务分成多次相对短的事务,则每次事务占用锁的时间相对较短,其他客户端在等待相应资源的时间也较短。这样的操作,同时也意味着将资源分片使用(每次执行使用不同片段的资源),可以提高并发性。

方案三,人为制造锁竞争,加剧并发。

方案二相对比较好,具体还要结合实际业务场景。

MySQL 大数据量分页查询方法及其优化

limit 偏移量不变,随着查询记录量越来越大,所花费的时间也会越来越多。

limit 查询记录数不变,随着查询偏移的增大,尤其查询偏移大于 10 万以后,查询时间急剧增加。

原因分析

select * from user where sex = 1 limit 100,10

由于 sex 列是索引列,MySQL 会走 sex 这棵索引树,命中 sex=1 的数据。

然后又由于非聚簇索引中存储的是主键 id 的值,且查询语句要求查询所有列,所以这里会发生一个回表的情况,在命中 sex 索引树中值为 1 的数据后,拿着它叶子节点上的值也就是主键 id 的值去主键索引树上查询这一行其他列(name、sex)的值,最后返回到结果集中,这样第一行数据就查询成功了。

最后这句 SQL 要求 limit 100, 10,也就是查询第 101 到 110 个数据,但是 MySQL 会查询前 110 行,然后将前 100 行抛弃,最后结果集中就只剩下了第 101 到 110 行,执行结束。

小结一下,在上述的执行过程中,造成 limit 大偏移量执行时间变久的原因有:

  • limit a, b 会查询前 a+b 条数据,然后丢弃前 a 条数据

MySQL 数据库的查询优化器是采用了基于代价的方式,而查询代价的估算是基于CPU 代价IO 代价。如果 MySQL 在查询代价估算中,认为全表扫描方式比走索引扫描的方式效率更高的话,就会放弃索引,直接全表扫描。

优化方式

t5 表有 200 万数据,id 为主键,text 为普通索引

使用覆盖索引

如果一条 SQL 语句,通过索引可以直接获取查询的结果,不再需要回表查询,就称这个索引为覆盖索引。

在 MySQL 数据库中使用 explain 关键字查看执行计划,如果 extra 这一列显示 Using index,就表示这条 SQL 语句使用了覆盖索引。

让我们来对比一下使用了覆盖索引,性能会提升多少吧。

没有使用覆盖索引

select * from t5 order by text limit 1000000, 10;

这次查询花了 3.690 秒,让我们看一下使用了覆盖索引优化会提升多少性能吧。

使用了覆盖索引

select id, `text` from t5 order by text limit 1000000, 10;

从上面的对比中,超大分页查询中,使用了覆盖索引之后,花了 0.201 秒,而没有使用覆盖索引花了 3.690 秒,提高了 18 倍多,这在实际开发中,就是一个大的性能优化了。

子查询优化

因为实际开发中,用 SELECT 查询一两列操作是非常少的,因此上述的覆盖索引的适用范围就比较有限。

所以我们可以通过把分页的 SQL 语句改写成子查询的方法获得性能上的提升。

select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;

其实使用这种方法,提升的效率和上面使用了覆盖索引基本一致。

但是这种优化方法也有局限性:

  • 这种写法要求主键 ID 必须是连续的
  • Where 子句不允许再添加其他条件
延迟关联

和上述的子查询做法类似,我们可以使用 JOIN,先在索引列上完成分页操作,然后再回表获取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;
3

评论区