伤心肠粉
伤心肠粉

关于 MySQL 的一些八股

MelonCholi 收录于  数据库
    约 10168 字   预计阅读 21 分钟 

其他八股

事务的四大特性 ACID

事务特性 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

  • 原子性是指事务是最小的单位，不可以再分割；同一事务中的 SQL 语句，必须保证同时完成
  • 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如 a 与 b 账户共有 1000 块，两人之间转账之后无论成功还是失败，它们的账户总和还是 1000。
  • 隔离性。跟隔离级别相关，如 read committed，一个事务只能读到已经提交的修改。
  • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

数据库的三大范式

第一范式 1NF

确保数据库表字段的原子性。

比如字段 userInfo: 广东省 10086 ，依照第一范式必须拆分成 userInfo: 广东省 userTel:10086两个字段。

第二范式 2NF

首先要满足第一范式，另外包含两部分内容：

  • 表必须有一个主键；
  • 非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子。假定选课关系表为 student_course(student_no, student_name, age, course_name, grade, credit)，主键为 (student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选 n 门课，姓名年龄有 n 条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。

应该拆分成三个表：学生：student (stuent_no, student_name, 年龄)；课程：course (course_name, credit)；选课关系：student_course_relation (student_no, course_name, grade)。

第三范式 3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。

即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

假定学生关系表为 Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院 id 依赖于学号，而学院地点和学院电话依赖于学院 id，存在传递依赖，不符合第三范式。

可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。

2NF 和 3NF的区别？

  • 2NF 依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
  • 3NF 依据是非主键列是否直接依赖于主键，即有无传递依赖的情况发生

事务隔离级别

事务读写会出现的问题

先了解下几个概念：脏读、不可重复读、幻读。

脏读

在一个事务处理过程里读取了另一个未提交的事务中的数据。

脏读在读未提交中发生

不可重复读

在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。

不可重复读在读已提交中发生。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

幻读

某个事务 A 在读取某个范围内的记录时，另外一个事务 B 又在该范围内插入了新的记录，但是当事务 A 再次查询时却感受不到这种变化

对幻读的正确理解是一个事务范围内的读取操作的结论不能支撑之后业务的执行。（因为有并发写的情况出现）

  • 假设事务要新增一条记录，主键为 id，在新增之前执行了 select，没有发现 id 为 xxx 的记录，但插入时出现主键冲突，这就属于幻读
  • 读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。

⭐ 不可重复度和幻读的区别是：

  1. 在不可重复读中，发现数据不一致主要是数据被更新了。
  2. 在幻读中，发现数据不一致主要是数据增多或者减少了

事务的四个隔离级别

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL 数据库为我们提供的四种隔离级别：

  • Read uncommitted (读未提交)

    • 所有事务都可以看到其他未提交事务的执行结果。

  • Read committed (读已提交)

    • 一个事务只能看见已经提交事务所做的改变。
    • 解决了脏读的问题。

  • Repeatable read (可重复读)

    • 在同一个事务中多次读取到的数据是一致的。
    • MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
    • 事务 A 只能在事务 B 修改过数据并提交后，自己也提交事务后，才能读取到事务 B 修改的数据。
    • 解决了不可重复读的问题。
    • 可以通过间隙锁来解决幻读问题

  • Serializable (串行化)

    • 通过强制事务串行执行，使之不可能相互冲突
    • 通过加锁实现（读锁和写锁）
    • 解决了幻读的问题。

查看隔离级别：

1

select @@transaction_isolation;

设置隔离级别：

1

set session transaction isolation level read uncommitted;

事务的隔离机制主要是依靠锁机制和 MVCC (多版本并发控制)实现的，提交读和可重复读可以通过 MVCC 实现，串行化可以通过锁机制实现。

生产环境数据库一般用的什么隔离级别

生产环境大多使用 RC。为什么不是 RR 呢？

可重复读 (Repeatable Read)，简称为 RR

读已提交 (Read Commited)，简称为 RC

缘由一：在 RR 隔离级别下，存在间隙锁，导致出现死锁的几率比 RC 大的多

缘由二：在 RR 隔离级别下，条件列未命中索引会锁表！而在 RC 隔离级别下，只锁行!

也就是说，RC 的并发性高于 RR。

并且大部分场景下，不可重复读问题是可以接受的。毕竟数据都已经提交了，读出来本身就没有太大问题！

互联网项目中 mysql 应该选什么事务隔离级别

可重复读隔离下为什么会产生幻读

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。

什么是快照读，什么是当前读？

快照读读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，比如这样：

1

SELECT * FROM player WHERE ...

当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读。

这有点像是 Java 中的 volatile 关键字，被 volatile 修饰的变量，进行修改时，JVM 会强制将其写回内存，而不是放在 CPU 缓存中，进行读取时，JVM 会强制从内存读取，而不是放在 CPU 缓存中。

这样就能保证其可见行，保证每次读取到的都是最新的值。

继续来看，如下的操作都会进行当前读。

1
2
3
4
5

SELECT * FROM player LOCK IN SHARE MODE;
SELECT * FROM player FOR UPDATE;
INSERT INTO player values ...
DELETE FROM player WHERE ...
UPDATE player SET ...

说白了，快照读就是普通的读操作，而当前读包括了加锁的读取和 DML（DML，Data Manipulation Language，数据操纵语言，只是对表内部的数据操作，不涉及表的定义，结构的修改。主要包括 insert、update、delete）

比如在可重复读的隔离条件下，我开启了两个事务，在事务 B 中进行了插入操作，事务 A 如果使用当前读是可以读到事务 B 插入的最新数据的。

MySQL 中如何实现可重复读（涉及 MVCC 的通俗解释）

当隔离级别为可重复读的时候，事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View。也就是说：对于 A 事务而言，不管其他事务怎么修改数据，对于 A 事务而言，它能看到的数据永远都是第一次 SELECT 时看到的数据。这显然不合理，如果其它事务插入了数据，A 事务却只能看到过去的数据，读取不了当前的数据。

既然都说到 Read View 了，就不得不说 MVCC (多版本并发控制) 机制了。MVCC 其实字面意思还比较好理解，为了防止数据产生冲突，我们可以使用时间戳之类的来进行标识，不同的时间戳对应着不同的版本。比如你现在有 1000 元，你借给了张三 500 元， 之后李四给了你 500 元，虽然你的钱的总额都是 1000 元，但是其实已经和最开始的 1000 元不一样了，为了判断中途是否有修改，我们就可以采用版本号来区分你的钱的变动。

如下，在数据库的数据表中，id，name，type 这三个字段是我自己建立的，但是除了这些字段，其实还有些隐藏字段是 MySQL 偷偷为我们添加的，我们通常是看不到这样的隐藏字段的。

我们重点关注这两个隐藏的字段：

  • db_trx_id：操作这行数据的上一个事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。我们每开启一个事务，都会从数据库中获得一个事务 ID（也就是事务版本号），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

  • db_roll_ptr：回滚指针，指向这个记录的 Undo Log 信息。什么是 Undo Log 呢？可以这么理解，当我们需要修改某条记录时，MySQL 担心以后可能会撤销该修改，回退到之前的状态，所以在修改之前，先把当前的数据存个档，然后再进行修改，Undo Log 就可以理解为是这个存档文件。这就像是我们打游戏一样，打到某个关卡先存个档，然后继续往下一关挑战，如果下一关挑战失败，就回到之前的存档点，不至于从头开始。

在 MVCC（多版本并发控制） 机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo Log 里。如下图所示，当前行记录的回滚指针指向的是它的上一个状态，它的上一个状态的回滚指针又指向了上上一个状态。这样，理论上我们通过遍历回滚指针，就能找到该行数据的任意一个状态。

我们没有想到，我们看到的或许只是一条数据，但是 MySQL 却在背后为该条数据存储多个版本，为这条数据存了非常多的档。那问题来了，当我们开启事务时，我们在事务中想要查询某条数据，但是每一条数据，都对应了非常多的版本，这时，我们需要读取哪个版本的行记录呢？

这时就需要用到 Read View 机制了，它帮我们解决了行的可见性问题。因此，mysql 查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出 1000+10 条数据，并抛弃前 1000 条，这部分耗时更大。

在 Read View 中有几个重要的属性：

  • trx_ids，系统当前正在活跃的事务 ID 集合
  • low_limit_id，活跃的事务中最大的事务 ID
  • up_limit_id，活跃的事务中最小的事务 ID
  • creator_trx_id，创建这个 Read View 的事务 ID

在前面我们说过了，在每一行记录中有一个隐藏字段 db_trx_id，表示操作这行数据的事务 ID ，而且事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

当我们开启事务以后，准备查询某条记录，发现该条记录的 db_trx_id < up_limit_id，这说明什么呢？说明该条记录一定是在本次事务开启之前就已经提交的，对于当前事务而言，这属于历史数据，可见，因此，我们通过 select 一定能查出这一条记录。

但是如果发现，要查询的这条记录的 db_trx_id > up_limit_id。这说明什么呢，说明我在开启事务的时候，这条记录肯定是还没有的，是在之后这条记录才被创建的，不应该被当前事务看见，这时候我们就可以通过 回滚指针 + Undo Log 去找一下该记录的历史版本，返回给当前事务。

举一个例子：A 事务开启时，数据库中还没有（30, 30, 30）这条记录。A 事务开启以后，B 事务往数据库中插入了（30, 30, 30）这条记录，这时候，A 事务使用不加锁的 select 进行快照读时是查询不出这条新插入的记录的，这符合我们的预期。对于 A 事务而言，(30, 30, 30) 这条记录的 db_trx_id 一定大于 A 事务开启时的 up_limit_id，所以这条记录不应该被 A 事务看见。

如果需要查询的这条记录的 trx_id 满足 up_limit_id < trx_id < low_limit_id 这个条件，说明该行记录所在的事务 trx_id 在目前 creator_trx_id 这个事务创建的时候，可能还处于活跃的状态，因此我们需要在 trx_ids 集合中进行遍历，如果 trx_id 存在于 trx_ids 集合中，证明这个事务 trx_id 还处于活跃状态，不可见，如果该记录有 Undo Log，我们可以通过回滚指针进行遍历，查询该记录的历史版本数据。如果 trx_id 不存在于 trx_ids 集合中，证明事务 trx_id 已经提交了，该行记录可见。

回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。

最后，再来强调一遍：事务只在第一次 SELECT 的时候会获取一次 Read View

因此，在可重复读的隔离条件下，在该事务中不管进行多少次以相同条件的查询，最终结果得到都是一样的，尽管可能会有其它事务对这个结果集进行了更改。

查询语句执行流程

查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查询语句如下：

1

select * from user where id > 1 and name = '大彬';
  1. 首先检查权限，没有权限则返回错误；
  2. MySQL 8.0 以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
  3. 词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
  4. 两种执行方案，先查 id > 1 还是 name = '大彬'，优化器根据自己的优化算法选择执行效率最好的方案；
  5. 再次校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

更新语句执行过程

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare 状态）、bin log、redo log（commit 状态）

举个例子，更新语句如下：

1

update user set name = '大彬' where id = 1;
  1. 先查询到 id 为 1 的记录，有缓存会使用缓存。
  2. 拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录 redo log，此时 redo log进入 prepare 状态。
  3. 执行器收到通知后记录 bin log，然后调用引擎接口，提交 redo log 为 commit 状态。
  4. 更新完成。

为什么记录完 redo log，不直接提交，而是先进入 prepare 状态？

假设先写 redo log 直接提交，然后写 bin log，写完redo log后，机器挂了，bin log 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bin log 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

exists 和 in 的区别

exists 用于对外表记录做筛选。exists 会遍历外表，将外查询表的每一行，代入内查询进行判断。

  • 当 exists 里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。
  • 反之如果 exists 里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。
1

select a.* from A where exists (select 1 from B b where a.id=b.id)

in 是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

1

select * from A where id in (select id from B)

子查询的表比较大的时候，使用 exists 可以有效减少总的循环次数来提升速度；当外查询的表比较大的时候，使用 in 可以有效减少对外查询表循环遍历来提升速度。

总结：exists 遍历外表，in 遍历子表

MySQL 中 int(10) 和 char(10) 的区别

int(10) 中的 10 表示的是显示数据的长度，而 char(10) 表示的是存储数据的长度

truncate、delete 与 drop 区别？

相同点：

  1. truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据。

不同点：

  1. truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；
  2. drop、truncate 都是 DDL 语句（数据定义语言），执行后会自动提交
  3. delete 是 DML 语句
  4. 一般来说，执行速度: drop > truncate > delete。

having 和 where 区别？

  • 二者作用的对象不同，where 子句作用于表和视图，having 作用于组。
  • where 在数据分组前进行过滤，having 在数据分组后进行过滤。

查询 limit 1000,10 和 limit 10 速度一样快吗？

太长不看：limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出 1000+10 条数据，并抛弃前 1000 条，这部分耗时更大。

两种查询方式。对应 limit offset, size 和 limit size 两种方式。

而其实 limit size ，相当于 limit 0, size。也就是从 0 开始取 size 条数据。

也就是说，两种方式的区别在于 offset 是否为 0。

先来看下 limit sql 的内部执行逻辑。

MySQL 内部分为 server 层和存储引擎层。一般情况下存储引擎都用 innodb。

server 层有很多模块，其中需要关注的是执行器是用于跟存储引擎打交道的组件。

执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他 where 条件），则会放到结果集中，最后返回给调用 mysql 的客户端。

以主键索引的 limit 执行过程为例：

  • 执行 select * from xxx order by id limit 0, 10;，select 后面带的是星号，也就是要求获得行数据的所有字段信息。
    • server 层会调用 innodb 的接口，在 innodb 里的主键索引中获取到第 0 到 10 条完整行数据，依次返回给 server 层，并放到 server 层的结果集中，返回给客户端。
  • 把 offset 搞大点，比如执行的是：select * from xxx order by id limit 500000, 10;
    • server 层会调用 innodb 的接口，由于这次的 offset=500000，会在 innodb 里的主键索引中获取到第 0 到（500000 + 10）条完整行数据，返回给 server 层之后根据 offset 的值挨个抛弃，最后只留下最后面的 size 条，也就是 10 条数据，放到 server 层的结果集中，返回给客户端。

可以看出，当 offset 非 0 时，server 层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的。

因此，mysql 查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出 1000+10 条数据，并抛弃前 1000 条，这部分耗时更大。

好笨

深分页怎么优化？

还是以上面的 SQL 为空：select * from xxx order by id limit 500000, 10;

方法一：

从上面的分析可以看出，当 offset 非常大时，server 层会从引擎层获取到很多无用的数据，而当 select 后面是 * 号时，就需要拷贝完整的行信息，拷贝完整数据相比只拷贝行数据里的其中一两个列字段更耗费时间。

因为前面的 offset 条数据最后都是不要的，没有必要拷贝完整字段，所以可以将 sql 语句修改成：

1

select * from xxx  where id >= (select id from xxx order by id limit 500000, 1) order by id limit 10;

先执行子查询 select id from xxx by id limit 500000, 1, 这个操作，其实也是将在 innodb 中的主键索引中获取到 500000+1 条数据，然后 server 层会抛弃前 500000 条，只保留最后一条数据的 id。

但不同的地方在于，在返回 server 层的过程中，只会拷贝数据行内的 id 这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

在拿到了上面的 id 之后，假设这个 id 正好等于 500000，那 sql 就变成了

1

select * from xxx  where id >=500000 order by id limit 10;

这样 innodb 再走一次主键索引，通过 B+ 树快速定位到 id=500000 的行数据，时间复杂度是 lg(n)，然后向后取 10 条数据。

方法二：

将所有的数据根据 id 主键进行排序，然后分批次取，将当前批次的最大 id 作为下次筛选的条件进行查询。

1

select * from xxx where id > start_id order by id limit 10;

通过主键索引，每次定位到 start_id 的位置，然后往后遍历 10 个数据，这样不管数据多大，查询性能都较为稳定。

高度为 3 的 B+ 树，可以存放多少数据

InnoDB 存储引擎有自己的最小储存单元——页（Page）。

查询 InnoDB 页大小的命令如下：

1
2
3
4
5
6

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

可以看出 innodb 默认的一页大小为 16384B = 16384/1024 = 16kb。

在 MySQL 中，B+ 树一个节点的大小设为一页或页的倍数最为合适。因为如果一个节点的大小 < 1 页，那么读取这个节点的时候其实读取的还是一页，这样就造成了资源的浪费。

B+ 树中非叶子节点存的是 key + 指针；叶子节点存的是数据行。

对于叶子节点，如果一行数据大小为 1k，那么一页就能存 16 条数据。

对于非叶子节点，如果 key 使用的是 bigint，则为 8 字节，指针在 MySQL 中为 6 字节，一共是 14 字节，则 16k 能存放 16 * 1024 / 14 = 1170 个索引指针。

于是可以算出，对于一颗高度为 2 的 B+ 树，根节点存储索引指针节点，那么它有 1170 个叶子节点存储数据，每个叶子节点可以存储 16 条数据，一共 1170 x 16 = 18720 条数据。而对于高度为 3 的 B+ 树，就可以存放 1170 x 1170 x 16 = 21902400 条数据（两千多万条数据），也就是对于两千多万条的数据，我们只需要高度为 3 的 B+ 树就可以完成，通过主键查询只需要 3 次 IO 操作就能查到对应数据。

所以在 InnoDB 中 B+ 树高度一般为 3 层时，就能满足千万级的数据存储。

单表多大进行分库分表

目前主流的有两种说法：

  1. MySQL 单表数据量大于 2000 万行，性能会明显下降，考虑进行分库分表。
  2. 阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

事实上，这个数值和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为 MySQL 为了提高性能，会将表的索引装载到内存中。在 InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。

因此，对于分库分表，需要结合实际需求，不宜过度设计，在项目一开始不采用分库与分表设计，而是随着业务的增长，在无法继续优化的情况下，再考虑分库与分表提高系统的性能。对此，阿里巴巴《Java 开发手册》补充到：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

至于 MySQL 单表多大进行分库分表，应当根据机器资源进行评估。

大表查询慢怎么优化

某个表有近千万数据，查询比较慢，如何优化？

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

  • 建立索引。在合适的字段上建立索引，例如在 WHERE 和 ORDER BY 命令上涉及的列建立索引，可根据 EXPLAIN 来查看是否用了索引还是全表扫描
  • 建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能
  • 利用缓存。利用 Redis 等缓存热点数据，提高查询效率
  • 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内
  • 读写分离。经典的数据库拆分方案，主库负责写，从库负责读
  • 分库分表，主要有垂直拆分和水平拆分
  • ES

什么是临时表

MySQL 在执行 SQL 语句的时候会临时创建一些存储中间结果集的表，这种表被称为临时表，临时表只对当前连接可见，在连接关闭后，临时表会被删除并释放空间。

临时表主要分为内存临时表和磁盘临时表两种

  • 内存临时表使用的是 MEMORY 存储引擎
  • 磁盘临时表使用的是 MyISAM 存储引擎。

一般在以下几种情况中会使用到临时表：

  • FROM 中的子查询
  • DISTINCT 查询并加上 ORDER BY
  • ORDER BY 和 GROUP BY 的子句不一样时会产生临时表
  • 使用 UNION 查询会产生临时表

主键一般用自增 ID 还是 UUID

使用自增 ID 的好处：

  • 字段长度较 uuid 会小很多。

  • 数据库自动编号，按顺序存放，利于检索

  • 无需担心主键重复问题

使用自增 ID 的缺点：

  • 因为是自增，在某些业务场景下，容易被其他人查到业务量。

  • 发生数据迁移时，或者表合并时会非常麻烦

  • 在高并发的场景下，竞争自增锁会降低数据库的吞吐能力

UUID：通用唯一标识码，UUID 是基于当前时间、计数器和硬件标识等数据计算生成的。

使用 UUID 的优点：

  • 唯一标识，不会考虑重复问题，在数据拆分、合并时也能达到全局的唯一性。

  • 可以在应用层生成，提高数据库的吞吐能力。

  • 无需担心业务量泄露的问题。

使用 UUID 的缺点：

  • 因为 UUID 是随机生成的，所以会发生随机 IO，影响插入速度，并且会造成硬盘的使用率较低。

  • UUID 占用空间较大，建立的索引越多，造成的影响越大。

  • UUID 之间比较大小较自增 ID 慢不少，影响查询速度。

最后说下结论，一般情况 MySQL 推荐使用自增 ID。因为在 MySQL 的 InnoDB 存储引擎中，主键索引是一种聚簇索引，主键索引的 B+ 树的叶子节点按照顺序存储了主键值及数据

  • 如果主键索引是自增 ID，只需要按顺序往后排列即可，

  • 如果是 UUID，ID 是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。

字段为什么要设置成 not null

首先说一点，NULL 和空值是不一样的，空值是不占用空间的，而 NULL 是占用空间的，所以字段设为 NOT NULL 后仍然可以插入空值。

字段设置成 not null 主要有以下几点原因：

  1. NULL 值会影响一些函数的统计，如 count，遇到 NULL 值，这条记录不会统计在内。

    • B 树不存储 NULL，所以索引用不到 NULL，会造成统计不到的问题。

  2. NOT IN 子查询在有 NULL 值的情况下返回的结果都是空值。

    • 例如 user 表如下

    • idusername
      0zhangsan
      1lisi
      2null
      1

      select * from `user` where username NOT IN (select username from `user` where id != 0)

      这条查询语句应该查到 zhangsan 这条数据，但是结果显示为 null

    MySQL 在进行比较的时候，NULL 会参与字段的比较，因为 NULL 是一种比较特殊的数据类型，数据库在处理时需要进行特殊处理，增加了数据库处理记录的复杂性。

如何优化 WHERE 子句

  • 不要在 where 子句中使用 != 和 <> 进行不等于判断，这样会导致放弃索引进行全表扫描。

  • 不要在 where 子句中使用 null 或空值判断，尽量设置字段为 not null

  • 尽量使用 union all 代替 or

  • 在 where 和 order by 涉及的列建立索引

  • 尽量减少使用 in 或者 not in，会进行全表扫描

  • 在 where 子句中使用参数会导致全表扫描

  • 避免在 where 子句中对字段及进行表达式或者函数操作会导致存储引擎放弃索引进而全表扫描

SQL 语句的执行顺序

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT DISTINCT 
	select_list 
FROM 
	left_table 
LEFT JOIN 
	right_table ON join_condition 
WHERE 
	where_condition 
GROUP BY 
	group_by_list 
HAVING 
	having_condition 
ORDER BY 
	order_by_condition

https://markdown-1303167219.cos.ap-shanghai.myqcloud.com/11d8f5cf351d7387c01a2759b65f5609.png

  • FROM：对 SQL 语句执行查询时，首先对关键字两边的表以笛卡尔积的形式执行连接，并产生一个虚表 V1。虚表就是视图，数据会来自多张表的执行结果。

  • ON：对 FROM 连接的结果进行 ON 过滤,并创建虚表 V2

  • JOIN：将 ON 过滤后的左表添加进来，并创建新的虚拟表 V3

  • WHERE：对虚拟表 V3 进行 WHERE 筛选，创建虚拟表 V4

  • GROUP BY：对 V4 中的记录进行分组操作，创建虚拟表 V5

  • HAVING：对 V5 进行过滤，创建虚拟表 V6

  • SELECT：将 V6 中的结果按照 SELECT 进行筛选，创建虚拟表 V7

  • DISTINCT：对 V7 表中的结果进行去重操作，创建虚拟表 V8

    • 如果使用了 GROUP BY 子句则无需使用 DISTINCT，因为分组的时候是将列中唯一的值分成一组，并且每组只返回一行记录，所以所有的记录都是不同的。

  • ORDER BY：对 V8 表中的结果进行排序。

分库分表后，ID 键如何处理

分库分表后不能每个表的 ID 都是从 1 开始，所以需要一个全局 ID

设置全局 ID 主要有以下几种方法：

  • UUID

    • 优点：本地生成 ID，不需要远程调用；全局唯一不重复
    • 缺点：占用空间大；不适合作为索引。

  • 数据库自增 ID：在分库分表表后使用数据库自增 ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的 ID，利用这个 ID 去分库分表中写数据。

    • 优点：简单易实现。
    • 缺点：在高并发下存在瓶颈。

https://markdown-1303167219.cos.ap-shanghai.myqcloud.com/3ce2984ba84782dd28e80c70bb008871.png

  • Redis 生成 ID

    • 优点：不依赖数据库，性能比较好。
    • 缺点：引入新的组件会使得系统复杂度增加

  • Twitter 的 snowflake 算法：是一个 64 位的 long 型的 ID，其中有 1bit 是不用的，41bit 作为毫秒数，10bit 作为工作机器 ID，12bit 作为序列号。

    • 1bit：第一个 bit 默认为 0，因为二进制中第一个 bit 为 1 的话为负数，但是 ID 不能为负数.

    • 41bit：表示的是时间戳，单位是毫秒。

    • 10bit：记录工作机器 ID，其中 5 个 bit 表示机房 ID，5 个 bit 表示机器 ID。

    • 12bit：用来记录同一毫秒内产生的不同 ID。

  • 美团的 Leaf 分布式 ID 生成系统，美团点评分布式 ID 生成系统

更新于 2024-04-27
CC BY-NC 4.0
 数据库, MySQL
返回 | 主页