基础知识

1. char 和 varchar 的区别

char 是定长的，varchar 是变长的。char 插入的长度不足定义长度时，会使用空格填充，而 varchar 不会做填充。

拿 char(10) 和 varchar(10) 存储 "hello" 时，char 类型会占用 10 个字符（5个实际字符和5个填充空格），而 varchar 只会占用 5 个字符。

因此，char 要比 varchar 的存取速度快，方便存储和查找，但是 varchar 的占用空间小。

char 适用于存储的数据长度基本一致，不需要空格，eg: 手机号、UUID、密码加密后的密文；

varchar 适用于数据长度不一定，长度范围变化较大的场景。

char 和 varchar 的区别与使用场景

https://segmentfault.com/a/1190000024432795

2. varchar(50) 的含义，能存储多少个汉字

在早期的版本中，50 表示 50 个字节，现在表示的是 50 个字符。

无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放50个。

3. int(20) 的含义

定义的是显示宽度，int(20) 的 20 并不影响 int 的存储范围以及占用空间（32位）。

这个 20 在和 zerofill 搭配使用时起效果，会将达不到指定位数的整数前面用 0 填充。

4. delete、truncate、drop 的区别

delete：不删除表结构，会删除行数据，删除速度慢。可回滚；

truncate：不删除表结构，但是会把所有的表数据都删除掉，删除速度快。不可回滚；

drop：删除表结构、表数据、索引和权限，删除速度最快。不可回滚。

5. union 和 union all 的区别

union：对两个结果集进行并集操作，不包括重复行，同时按照默认规则排序；

union all：对两个结果集进行并集操作，包括重复行，不进行排序。

因此效率上，union all 会比 union 快。

6. in 和 exists 的区别

exists：对外表的逐条记录都会查看是否满足 exists 的条件语句，exists 每次判断都会查询一次表；

in：将子查询中的结果集查询出来放在缓存中，然后外表的逐条记录都与结果集进行比对，相当于将结果集与查询字段用 or 连接起来。

因此，很容易得出以下结论：

1. 如果查询的两个表大小相当，使用 in 和 exists 的差别不大；
2. 如果外表比子查询表大得多，那么使用 in 更合适；
3. 如果外表比子查询表小得多，那么使用 exists 更合适。

但是，《MySQL高性能（第三版）》的第 224 页指出，MySQL 会把 in 查询改为 exists 再去执行。

7. group by、having、where 的区别

了解三者的用法，就可以知道他们之间的区别。

where

用于增加查询约束，作用于原始数据表，在原始数据表中进行筛选，返回结果集之前起作用。

group by

是对查询出来的结果集按照某个字段进行分组，将数据集分成一组组，group by 一般搭配聚合函数使用。

使用 group by 时需要注意，select 的字段需要出现在 group by 语句的后面，可以是接在 group by 后面，也可以是接在 group by 后面的聚合函数中的字段。

having

是对 where 和 group by 查询出来的结果集进行过滤的，查出满足条件的分组结果。

使用 having 需要注意以下几点：

1. having 只能用于 group by 子句中；
2. where 用于在原始表中筛选查询，having 用于 where 和 group by 的结果集中筛选；
3. having 子句的每一个元素也必须出现在 select 列表中；
4. having 语句可以使用聚合函数，而 where 不使用。

三者区别

https://www.cnblogs.com/starliang/p/8377040.html

8. INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 的区别

INNER JOIN

内连接，在进行两张表连接查询时，只保留两张表完全匹配的结果集。

LEFT JOIN

左外连接，在进行两张表连接查询时，会返回左表所有的行，即使在右表中没有匹配的行。

RIGHT JOIN

右外连接，在进行两张表连接查询时，会返回右表所有的行，即使在左表中没有匹配的行。

FULL JOIN

全连接，在进行两张表连接查询时，会返回左表和右表所有没有匹配的行。

所以这四种连接查询的区别并不是太大，仅仅是查询出来的结果有所不同。

事务

9. MySQL 的事务特性

原子性（Atomicity）：事务是数据库的最小执行单位，里面的语句要么全部成功，要么全部失败；

一致性（Consistency）：事务开始和结束后，数据库的完整性没有遭到破坏；（数据库完整性由各种各样的完整性约束来保证）

隔离性（Isolation）：数据库允许多个事务同时对数据进行读写，隔离性可以防止多个事务之间互相干扰；

持久性（Durability）：事务处理结束后，对数据的修改是永久的，即使系统故障也不会丢失。

10. MySQL的事务隔离级别

READ_UNCOMMITED：未提交读。一个事务的修改还没提交就能被另一个事务知道；

READ_COMMITED：提交读。一个事务的修改只有提交了的数据才能被另一个事务知道；

REPEATABLE_READ：可重复读。对于字段的多次读取结果相同，除非被其他事务修改；

SERIALIZABLE：可串行化。最高级的隔离级别，所有的事务串行执行，不会产生并发问题。

不同隔离级别都解决了什么并发问题

• READ_COMMITED 解决了脏读的问题。

• REPEATABLE_READ 解决了不可重复读的问题。

• SERIALIZABLE 解决了幻读的问题。

11. MySQL的事务并发问题

丢失修改：事务A修改了数据之后，被事务B重新修改，事务A发现自己的修改丢失了；

脏读：事务A读了事务B还未提交的修改数据，事务B回滚之后，相当于事务A读了无效的数据；

不可重复读：事务A读了某个数据之后（此时事务B还未提交），数据被事务B修改了，事务A再来读取时发现不是之前的数据；

幻读：事务A按照某个条件查询数据条数之后，事务B插入某条数据，事务A再按照同样条件查询时，发现条数不一样。

关于幻读：

https://zhuanlan.zhihu.com/p/103580034?utm_source=wechat_session

索引

12. 什么是索引，哪些数据结构可以做索引，它们有什么区别

索引是对用户指定的任意字段的数据进行排序以提高搜索效率的数据结构。

能作为索引的数据结构有：哈希表，$B$ 树，$B^+$ 树，$AVL$ 树。

各种数据结构作为索引时的区别：

1. 哈希表： 使用哈希表能做到精确查询，查询时间复杂度为 $O(1)$。 但是不能进行范围查询和排序查询，所以并不是很合适。
2. $AVL$ 树 也叫完全平衡树，可以实现精确查询，范围查询。 但是因为是二叉树，所以在随着数据的增加，树的高度会增加，增加 $I/O$ 次数。 并且因为要保持平衡，经常要做左旋和右旋，比较耗时。
3. $B$ 树 每个节点包含了 key—value。 查询效率比 $AVL$ 树高，范围查询效率差。
4. $B^+$ 树 非叶子节点只包含 key，叶子节点包含 key-value，并且用一条双向链表将叶子节点串联起来。 高效支持范围查询。 $B^+$ 树相对于 $B$ 树，优点在于 $B^+$ 树将数据存放在叶子节点，并使用双向链表串起来，优化了范围搜索的效率。 缺点是有冗余数据，占用硬盘空间。

13. 为什么 $B^+$ 树比 $B$ 树更适合做索引

1. $B^+$ 树的磁盘读写代价更低。 $B^+$ 树非叶子节点不存储具体信息，因此一样大的硬盘空间，$B^+$ 树比 $B$ 树存储更多的节点。 在将关键字信息一次性读入内存时，$B^+$ 树也会比 $B$ 多一些数量。
2. $B^+$ 树的查询效率更加稳定。 查询任何一条具体信息，都需要经过从根节点到叶子节点的过程，导致每一条数据查询效率都相当。
3. $B^+$ 树方便全表扫描 $B^+$ 树在进行全表扫描时，只需要将叶子节点上的双向链表遍历一次即可。 而 $B$ 树则需要对整棵树做一次中序遍历。

或者说

$B$ 在提高 $I/O$ 性能的同时，并没有解决元素遍历效率低下的问题，所以出现了 $B^+$ 树，它只需要遍历一次叶子节点即可

14. 索引的分类（物理上和逻辑上）

物理上

1. 聚簇索引：这种索引的叶子节点包含了该行的全部数据。主键索引就是一个聚簇索引，一个表中只能有一个聚簇索引；
2. 非聚簇索引：这种索引的叶子节点存的是主键的值，拿到主键的值再去主键索引查找行数据；一个表中可以包含多个非聚簇索引。

逻辑上

1. 主键索引：使用主键列的值构成的搜索。
2. 唯一索引：键值不能重复的搜索，但允许有空值。
3. 覆盖索引：覆盖索引也叫组合索引，使用时要遵循“最左前缀”原则。
4. 复合索引：包含多个列的索引
5. 前缀索引：指定前面长度的内容为索引

15. 前缀索引

有时候索引很长的字符串，会使得索引变得又大又慢。

通常可以只索引字符串的开始部分，这样可以节省索引空间，提高所以效率。

前缀索引是一种可以使得索引更小更快的方法，但是另一方面，使用前缀索引时，MySQL 不能对该列使用 group by 或者 order by，也无法使用覆盖索引做扫描。

-- 添加前缀索引
alter table city_demo add key (city(6));

16. 组合索引与最左前缀匹配原则

用户可以在多个列上一起建立索引，这种方式成为组合索引。

创建组合索引时，相当于创建多个索引。

比如 idx(a, b)，相当于创建单列索引 idx(a) 和组合索引 idx(a,b)。

组合索引的多字段是有序的，排序是先按照第一个字段，然后按照第二个字段......

如果跳过第一个字段，直接查询第二个字段，那么第二个字段肯定是无序的，组合索引无法生效。

因此组合索引的使用需要注意最左前缀匹配原则。

最左前缀匹配原则有个非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

17. 索引使用注意事项

创建的原则

1. 频繁作为查询条件的列才建立索引，而频繁更新的列不适合创建索引；
2. where 子句中的查询列才考虑使用索引；
3. 区分度（选择性）低的列不建立索引（比如性别这种）；
4. 使用短索引，如果对长字符串进行索引，应该指定一个前缀长度，这样能节省大量的时间；
5. 索引并不是无代价的，过多的索引会占用额外的磁盘空间，而且会导致写操作性能降低。

索引失效的情况

1. 复合索引没有遵循最左前缀匹配原则；
2. 在索引列上使用以下关键字：int、not in、null、is not null、<>；
3. 如果使用 or 连接条件，那么当有一个字段没有索引时，会导致全表扫描；
4. 在索引列使用函数、运算，比如 id/2 = #{id} ；
5. 索引列使用 like 查询时，表达式使用 % 开头，比如 "%abc"；
6. MySQL 的优化器觉得全表扫描更快时，不会使用索引。

索引失效原理

https://cloud.tencent.com/developer/article/1704743

存储引擎

18. MySQL 的架构

MySQL 的架构分为四层：应用层、逻辑层、存储引擎层、物理层。

应用层负责和客户端接受、响应请求，建立链接返回数据。

逻辑层包括解析器、优化器、SQK 接口、Cache 和 Buffer。

存储引擎层包括 MyISAM、InnoDB 存储引擎等等。

物理层负责文件存储，日志等等。

19. SQL 语句执行的过程

1. 客户端通过连接器进行身份验证和权限验证；
2. 如果是查询请求，会先查询缓存；（MySQL 8.0 后将这一步骤移除）
3. 如果缓存查询不到，SQL 语句就会经过解析器，分析语句，包括语法检查等；
4. 通过优化器，MySQL 会按照它所认为的最优方案去执行；
5. 执行语句，并从存储引擎中返回数据。

20. 说出至少三个存储引擎

MySQL 的存储引擎包括但不限于 MyISAM、InnoDB、Memory 等等。

MyISAM 原本是 MySQL 的默认存储引擎，不支持事务、行级锁和外键，适合读多写少的场景。

InnoDB 提供了数据库对 ACID 事务的支持，支持行级锁和外键约束，设计的目的是用来处理大数据容量的系统。

Memory 所有的数据都在内存中，处理速度非常快但是不安全。

其中 MyISAM 和 InnoDB 用的比较多，二者的区别如下表。

	MyISAM	InnoDB
顺序	按记录的插入顺序保存	按主键的大小有序保存
外键	不支持外键	支持外键
事务	不支持事务	支持事务
锁支持	只支持表级锁	支持行级锁、表级锁
存储空间	表可被压缩，存储空间较小	需要更多的内存和存储空间， 会在主内存建立其专用的缓冲池
可移植、备份及恢复	MyISAM 的数据都是以文件的形式存在， 数据转移比较方便，可以单独针对某个表进行备份和恢复	拷贝数据文件、备份 binlog、使用 mysqldump ， 但是数据量达到几十 G 就会比较痛苦
文件格式	数据文件是 .MYD 索引文件时 .MYI	数据和索引集中存储 .ibd
SELECT	MyISAM 更优
INSERT、UPDATE、DELETE		InnoDB 更优
select count(*)	MyISAM 更优，其内部维护了一个计数器，可以直接调取
哈希索引	不支持	支持
全文索引	支持	不支持

5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文。

21. InnoDB引擎中 $B^+$ 算法可以存储多少行数据

大约可以存储 2 千万行记录。

结果的计算

（假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节）

https://www.cnblogs.com/leefreeman/p/8315844.html

锁

22. MySQL 的锁有几种

按照锁的粒度由小到大可以分为行级锁、页级锁、表级锁。

行级锁

• 会对当前操作的行记录加锁。

• 能大大降低数据库操作冲突，锁的粒度小，并发能力高，但是加锁的开销也大。

• 支持共享锁和排他锁。

表级锁

• 会对当前操作的表进行加9锁。

• 加锁粒度大，实现简单，开销少，大多数存储引擎都支持，但是并发能力低。

• 支持共享锁和排他锁。

页级锁

• 粒度介于行级锁和表级锁之间的一种锁。

表级锁速度快冲突多，行级锁速度慢冲突少，页级锁折中，一次只锁定相邻的一组记录。

按照锁的类别还可以分为共享锁和排他锁

共享锁又叫读锁，用于要读取数据时，对数据加上共享锁，共享锁可以同时加上多个。

排他锁又叫写锁，用于要写入数据时，对数据加上排他锁，排他锁只能加一个，与共享锁相斥。

23. MySQL中InnoDB引擎的行锁是怎么实现的

InnoDB 是基于索引实现行级锁的。

select * from table_name where id = 5 for update;

for update 可以根据条件对行进行锁定。并且 id 是主键，否则将会变成表级锁。

24. InnoDB锁算法

Record Lock：单个行记录上的锁。

Gap Lock：间隙锁，锁定一个范围，不包括记录本身。

Next-key Lock：record + gap 锁定一个范围，包括记录本身。

1. InnoDB 对于行记录的查询使用 Next-key 算法；
2. Next-key 是为了解决幻读问题；
3. 当查询的索引包含唯一属性时，将 Next-key 降级为 Record key；
4. Gap 的目的是阻止多个事务对同一个范围插入记录，这会导致幻读问题；
5. 有两种方式的显式关闭 Gap 锁
   1. 将事务隔离级别调整为 READ_COMMITED
   2. 将参数 innodb_locks_unsafe_for_binlog 设置为1

数据库优化

25. 慢查询日志

开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，优化系统。

涉及的参数有以下三个，

slow_query_log：慢查询开启状态（ON 和 OFF）；

slow_query_log_file：慢查询日志放置的位置（这个位置可以是 MySQL 存放数据的位置）；

long_query_time：查询超过多少秒才记录。

启动的方式有两种，

1. 不用重启 MySQL

登录 MySQL 进行全局变量设置

mysql> set slow_query_log='ON';
mysql> set slow_query_log_file='/data/mysql/slow_query.log';
-- 设置查询时间后，如果发现 long_query_time 时长显示未变化，重新登录即可
mysql> set long_query_time=3;

1. 需要重启 MySQL

修改配置文件 /etc/my.cnf，在 [mysqld] 下的下方加入

[mysqld]
slow_query_log=ON
slow_query_log_file='/data/mysql/slow_query.log'
long_query_time=3

配置文件保存后重启 MySQL 才可以生效。

26. 查看 SQL 语句的执行计划

使用 explain 关键字可以模拟优化器如何执行 SQL 语句，从而分析你的查询语句和表结构的性能瓶颈。

使用 explain，我们可以看到

1. 表的读取顺序（id）；
2. 数据读取操作的操作类型（select_type）；
3. 哪些索引可以使用（possible_keys）；
4. 哪些索引实际被使用（key）；
5. 表之间的引用（ref）；
6. 每张表有多少行被优化器查询（rows）；

27. explain 各个字段详解

1. id

   表示读取表的顺序，id 大的先读取，id相同的按顺序读取。

2. select_type

   表读取的操作类型。

   SELECT：不包含任何查询条件的简单查询；

   PRIMARY：查询中若包含复杂的子部分，最外层的查询被标记为PRIMARY；

   SUBQUERY：在 select 或者 where 列表中包含了子查询；

   DERIVED：衍生表，在 from 列表中包含的子查询会被标记为 DERIVED；

   UNION：若第二个 select 出现在 UNION 之后，则会被标记为 UNION；

   UNION RESULT：从 UNION 表获取的结果。

3. table

   查询的表名，如果是衍生表，则是 <derived#id> 这种，表示是由哪个 id 衍生出来的。

   比如 <derived2> 表示该查询的表是由 id 为 2 的查询衍生出来的。

4. type

   访问的类型，好坏程度如下，至少要达到 range，最好达到 ref

   system > const > eq_ref > ref > range > index > all

   system、const

   当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于 where 列表中，MySQL 能将查询转换为一个常量，system 是 const 的特例，当查询表中只有一条数据时，使用 system。

   eq_ref

   唯一性索引扫描，表中只有一行记录能与之匹配，常见于主键和唯一索引扫描。

   ref

   非唯一性索引扫描，返回匹配某个单独值的所有行。

   本质上也是一种索引访问，它返回所有匹配某个值的所有行，属于查找和扫描的混合体。

   range

   只检索给定范围的行，使用一个索引来选择行。

   比全表扫描好一点，因为它开始于索引的某一点，而结束于另一点，不用扫描整个表。

   index

   遍历整个索引以找到匹配的行。

   all

   全表扫描，MySQL 遍历全表以找到匹配的行。

5. possible_keys

   显示可能应用到这个表的索引，一个或者多个。

   将可能涉及到的索引列出，但不一定真的会被使用。

6. key

   实际使用的索引，如果为 NULL，则表示没有使用索引。

7. key_len

   表示索引中使用的字节数，在不损失精度的情况下，长度越短越好。

8. ref

   列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

9. rows

   根据表统计信息和索引选用情况，大致估算出找到所需记录需要读取的行数。

10. extra

    展示不是和在其他列中显示出来的，但又比较重要的额外信息。

    using filesort

    说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读。

    using temporary

    说明使用了临时表存储结果，常见于 order by 和 group by。

    using index

    表示相应的 select 操作中使用了覆盖索引，效率不错。如果同时出现 using where ，表明索引用来查找键值，如果没有同时出现 using where，表明索引用来读取数据而非执行查找动作。

    distinct

    优化 distinct 操作。

    using join buffer

    使用了连接查询缓存。

    impossible where

    where 子句的值总是 false，不能用来获取任何元组。

    select tables optimized away

    在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再计算，查询执行计划生成的阶段即完成优化。

28. MySQL 超大分页如何处理

在分页查询中，通常使用 limit offset N 的方式，但是这种方式是先获取 offset + N 行记录，然后返回前抛弃前 offset 行，当 offset 很大的时候，效率就非常低下。

对于这种情况，要么控制返回页的总数，比如查询条件里加个时间限制，或者针对特定阈值的页数进行 SQL 改写。

SQL 改写的思路是先定位到需要获取的 id 段，然后再关联查询

SELECT a.* 
FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b 
where a.id=b.id

29. 什么情况下考虑分库分表

能不切分尽量不切分，不到万不得已要避免使用分库分表这个大招。

分库分表前，应该尝试升级硬件、升级网络、读写分离、优化索引、缓存、限定查询条件等等。

当数据的量达到瓶颈时，在考虑分库分表。

30. MySQL 分库分表

当数据库的数据多到不能通过扩容，索引优化等手段提升速度时，才需要考虑分库分表。

分库分表主要有两种方式：垂直分表和水平分表。

垂直分表

即按照列来分表，将相关性强的列归在一个表中，将比较多列的表拆分成多个小表。

各表之间通过主键关联。

优点

• 行数据变小，在查询时减少读取的 Block 数，减少 $I/O$ 次数；同时简化表结构，易于维护。

缺点是

• 主键需要冗余，而且经常要使用 join 操作，将完整数据查出；垂直分表会让事务变得复杂。

使用场景

• 一些列经常用到，另一些列不怎么用到；可以使数据行变小，减少 $I/O$ 次数。

水平分表

保持表结构不变，通过某种策略将数据分片，这样数据分散在不同的数据表和数据库中，达到分布式的目的。

优点

• 可以支撑非常大的数据量。

缺点

• 分片事务难以解决，增加逻辑、部署、运维的复杂度。

使用场景

• 表中的数据有明显的独立性，比如记录各个地区的数据或者不同时期的数据。

水平分表的常见方案

• 客户端代理：分片逻辑在应用端，封装在 jar 包中，通过修改和封装 JDBC 的实现。比如 Sharding-JDBC、阿里的 TDDL 。
• 中间件代理：在应用和数据库之间增加代理层。分片逻辑统一在中间件中完成。比如 Mycat、Atlas、DDB 。

31. 分库分表后面临的问题

1. 分布式事务：分库分表后，就形成了分布式事务。如果依赖数据库本身的分布式事务，性能又会比较差。
2. 跨度 join：只要是进行切分，就避免不了跨界点 join 操作。
3. 跨节点 group by、order by 以及使用聚合函数等。
4. ID 问题：需要在应用层产生全局唯一的 ID。
5. 数据迁移，容量规划、扩容等问题。

32. 有哪些优化建议

1. 合适的数据类型

   选择合适的数据类型来存储数据，避免使用过大或者过小的数据类型，以减少存储和内存的开销。

2. 索引优化

   1. 选择适当的索引来加速查询，但不过度索引，避免索引更新带来的开销
   2. 使用复合索引来满足多个查询条件；
   3. 定期重新构建或者优化索引以保持其性能。

3. 查询优化

   1. 避免使用 select * ，而是选择需要的列；
   2. 使用 Explain 查看执行计划，以便分析优化 SQL 语句；
   3. 优化子查询，尽量避免子查询的使用。

4. 适当使用缓存策略

   使用 Memcached 或者 Redis 来减少数据库负载，减少数据库访问

5. 数据库参数调整

   调整数据库的配置参数，如缓冲池大小、连接数大小和查询缓存大小，以适应应用程序的需求。

   max_connections=3000 MySql的最大连接数

   innodb_buffer_pool_size=1024M(1G) 用于存储页面缓存数据

   query_cache_size=40M 缓存MySQL中的ResultSet

6. 分区分表

   对于大型数据表，可以采用分区分表的方式，减少数据查询压力。分区可以采用按日期、范围或者列值的策略方案。

7. 硬件升级

   在合理的情况下，可以升级硬件，如 CPU、内存和磁盘，以提高数据库性能。

分布式集群

33. MySQL 的日志

1. redo log

   redo log 是 InnoDB 引擎特有的。

   了解 redo log 前要先了解 WAL（Write Ahead Logging）技术，先写日志，再写磁盘。

   数据库的更新操作（增删改）都需要先记录在 redo log，同时 InnoDB 会在合适的时候，把数据写入磁盘。

   redo log 是为了确保事务的持久性，在事务提交后，如果还来不及将数据写入磁盘程序就崩溃了，可以在重启的时候，通过 redo log 来进行数据恢复以保证事务的持久性。

   redo log 记录的是物理数据页面的修改信息，其实际是由多个文件组成一个循环，然后顺序写入。

   write pos 是当前写入位置，一边写入一边往后移动；checkpoint 是要擦除的位置，一边记录日志一边往后移动。

   

2. bin log

   bin log 是 MySQL 的 Server 层实现的，每个 MySQL 都有。

   bin log 本身就是二进制格式的日志文件，网络传输无需进行协议转换。

   bin log 主要用于复制，在主从复制中，从数据库开启 $I/O$ 线程去读取 bin log 的变化，然后记录到本机，以此实现主从同步。用于数据库基于时间点的还原。

   bin log 主要有三种记录模式：Statement（基于SQL语句的复制）、Row（基于行的记录）以及 Mixed（混合模式）。

3. undo log

   undo log 用来回滚行记录到某个版本，undo log 一般是逻辑日志，根据每行记录进行记录。

   undo log 用于记录事务开始前的状态，用于事务失败时的回滚操作，而 redo log 记录事务成功后的状态。

4. relay log

   也叫中继日志。

   relay log 存在于主从复制的过程中，主从复制时，从数据库的 $I/O$ 线程读取到数据后，不是直接写入磁盘，而是先写入 relay log，然后从数据库的 SQL 线程再去读取 relay log 执行 SQL 语句。

   relay log 可以看作是一个缓冲区。

关于日志

https://learnku.com/articles/49614

34. MySQL 主从复制原理和流程

主从复制的过程涉及到三个线程

1. 主节点上的 log dump 线程；
2. 从节点上的 $I/O$ 线程；
3. 从节点上的 SQL 线程。

原理

主节点开启 bin log 之后，当从节点连接到主节点时，主节点就会为该从节点单独开启一个 log dump 线程。

log dump 线程用于发送和读取 bin log，在读取 bin log 的时候，log dump 线程会对 bin log 进行加锁直到数据发送给从节点后才释放锁。

当从节点开启 start slave 命令之后，从节点开启一个 $I/O$ 线程，专门请求主库更新的 bin log。

$I/O$ 线程读取到 log dump 线程发来的更新数据后，保存在本地的 relay log（中继日志）中。

从节点中还有一个 SQL 线程，负责读取 relay log 的内容，并转化为具体可执行的操作，保证数据的一致性。

$I/O$ 线程什么时候关闭？

关于主从复制

https://www.cnblogs.com/rickiyang/p/13856388.html

35. MySQL 主从复制的架构模式

1. 一主多从

   一主多从是常见的主从架构模式，但是当 slave 的数量增加到一定程度时，会对 master 的负载和网络带宽造成影响。

2. 多主一从

   MySQL 5.7 开始支持多主一从架构，将多个库的数据备份到一个库中存储。

3. 双主复制

   类似于主从，两个 master 互为对方的 slave，适用于写压力比较大的业务场景，或者方便主从切换。

4. 级联复制

   当 slave 节点特别多的时候，如果都连在 master 上面，肯定会对 master 造成不小的压力。

   所以可以让部分 slave 连接到上一级的 slave 节点，这样就可以缓解 master 的压力了。

   级联复制解决了多个 slave 对 master 造成压力，缺点就是数据同步的延迟比较大。

36. MySQL 主从复制的模式

1. 异步模式

   是 MySQL 默认的主从复制模式。

   这种模式下，master 在处理完客户端的写操作后立即将结果返回给客户端，不管 slave 节点是否接受到数据并处理。

   弊端是如果 master 突然挂了，但是已提交的事务没能够传到 slave 节点，如果这时强行将 slave 节点提升为主节点，就会造成数据不完整。

2. 同步模式

   当 master 执行完一个事务，然后所有的从数据库都复制了该事务并成功执行才会返回成功给客户端。

   因为要等所有的 slave 节点执行完事务，所以性能必定会受到严重的影响。

3. 半同步模式

   介于异步复制和同步复制之间，master 执行完并提交事务后，不是立刻返回给客户端，而是等待至少有一个从库接收到并写入到 relay log 中才会返回成功给客户端。

   否则需要等待到超时时间然后切换成异步模式再提交。

37. MySQL 主从复制的作用

1. 如果主数据库出现了问题，可以切换到从数据库；
2. 可以在数据库层面实现读写分离；
3. 可以在从数据库上进行日常备份；

还解决了如下的问题

1. 数据分布：随意开始和停止复制，并在多个地区位置分布数据备份；
2. 负载均衡：降低单个服务器的压力；
3. 提高可用性和故障切换：帮助应用程序避免单点失败；
4. 升级测试：在从库上尝试更高版本的数据库。

38. 新一代的主从复制模式- GTID 复制模式

在传统的主从复制中，发生了故障要主从切换时，需要找到 bin log 和位点信息，恢复完数据后再将主节点指向新的主节点。

在 MySQL 5.6 里面，提供了一种新的思路，只要知道主节点的 IP 和 端口，就能进行 GTID 寻找同步点进行复制。

GTID（Global Transaction Identifier）即全局事务ID，一个事务对应一个GTID，保证每个主库上提交的事务在集群内是唯一的。

原理

在基于 GTID 的复制中，从库会告知主库已经执行的事务的 GTID 的值，然后主库会把所有从库未执行的 GTID 列表返回给从库，这样可以保证每个事务只在指定的从库里执行一次。

从库在执行事务前，会先判断该事务是否已执行过，如果执行过则会忽略。

过程

基于 GTID 复制的过程如下：

1. master 更新数据时，会在事务前产生 GTID，一同记录到 bin log 日志中。
2. slave 的 $I/O$ 线程将变更的 bin log 写入到 relay log 中。从服务器读取 GTID，并将其设定为自己的 GTID。
3. SQL 线程从 relay log 中获取 GTID，然后对比从服务端的 bin log 是否有记录。
4. 如果有记录，说明该 GTID 的事务已经执行过了，从服务器会忽略。
5. 如果没有记录，从服务器就会从 relay log 中执行该 GTID 的事务，并记录到 bin log 中。

GTID 如何产生

首先 GTID 是由两部分组成的，GTID = source_id:transaction_id。

source_id 是产生 GTID 的服务器，是一个uuid，在第一次启动时生成，并保存在 DATADIR/auto.cnf 文件里。

transaction_id 是序列号，在每台 MySQL 服务器上开始从 1 字增长的顺序号。

GTID 的产生受到 GTID_NEXT 控制。

在主服务器上，GTID_NEXT 默认值是 AUTOMATIC，即在每次事务提交时自动生成 GTID，同时在实际更新记录之前，把 GTID 写入到 bin log 中。

GTID 自动生成是从当前已执行过的事务（gitd_executed）中，找到一个大于 0 的未使用的最小值作为下个事务 GTID。

GTID_NEXT 有三个值可以选择

AUTOMATIC: 使用自动生成下一个 GTID。
ANONYMOUS: 事务没有 GTID，只使用 file and position 作为标识
UUID:NUMBER : GITD in UUID:NUMBER format

基于GTID复制实现的工作原理

http://mysql.taobao.org/monthly/2020/05/09/

39. 读写分离有哪些解决方案

1. 使用 mysql-proxy

   可以直接实现读写分离和负载均衡，不用修改代码，但是官方不建议在实际生产中使用。

   使用 mysql-proxy 会降低性能，不支持事务。

2. 使用 AbstractRoutingDataSource + aop + annotation 在 dao 层实现

   在 dao 层将 read-only 的事务交给读库，其他有的 读写 事务交给写库。

3. 使用 AbstractRoutingDataSource + aop + annotation 在 service 层实现，选择数据源。

40. MySQL 备份

MySQL 备份分为逻辑备份和物理备份。

逻辑备份

依赖 MySQL 服务进行数据备份，只要 MySQL 能正常进行 select 操作，就可以得到一份可以依赖的备份。

优点

• 这种备份方式比较简单，甚至可以远程备份，备份出来的文件内容是普通文本，可以通过 sed、grep 等工具进行信息提取；

• 这种方式也是与引擎无关的，因为数据是从 MySQL 里面提取出来的，恢复时直接导入数据库即可。

缺点

• 因为是使用 MySQL 服务进行操作的，所以备份的时候会占用更多 CPU，且备份的时间相对长一些；
• 恢复时间长，因为在导入数据的时候，MySQL需要分配资源去进行锁分配、索引构建、冲突检验、日志刷新等。

常用的方法有 mysqldump、select into file、mydumper，其中 mysqldump 是 MySQL 自带的工具。

物理备份

直接将物理文件备份起来，复制有多快，备份就有多快。

优点

• 备份速度快，恢复速度快，只需要将文件复制到数据库目录就可以完成备份，不需要检查锁、索引构建；
• 恢复简单，对于 MyISAM 引擎的表，不需要停库；对于 InnoDB，如果是一个表一个表空间，也可以不需要停库。

缺点

• InnoDB 的数据文件通常比逻辑备份的大，因为 InnoDB 的表空间往往包含未使用的表空间，删除的数据也不会释放空间，所以即使数据量不大，文件还是比较大；
• 物理备份的文件往往与操作系统、MySQL 版本息息相关，如果环境不一致，可能会出现问题；
• 没有官方的工具支持。

常见的备份方法：xtrabackup、直接复制文件，xtrabackup 是最常用的物理备份工具，有 percona 开源。

还有一种方式是通过 bin log 备份，可以恢复到某段时间内的任意一个时间点，主要看 bin log 的保留时间。

另外，主从复制并不算是备份的一种方式，因为主库的删除操作也会传递给从库，并不能在出现意外的时候进行数据恢复，但是可以在从库进行数据备份，以减少主库的压力。

最后，特别重要的是，备份出来的文件要定时检查其有效性。

MySQL 备份

https://segmentfault.com/a/1190000019955399

41. MySQL 导入数据

1. 在 shell 直接执行命令

mysql -u username -p passwd dbname < file.sql

2. 通过 source 命令，需要先登入数据库

mysql -u username -p passwd
use dbname
source file.sql;

导入大文件之前可以先取消事务自动提交来加快导入速度。

mysql -u username -p passwd
set autocommit=0;
use dbname;
source file.sql

模拟场景

42. 随机取出10条数据

生成一个合法的随机ID，取比这个ID大的数据即可。

SELECT 
	* 
FROM table_a 
WHERE ID >= 
(( SELECT Max( ID ) FROM table_a ) - ( SELECT MIN( ID ) FROM table_a )) * RAND() + ( SELECT MIN( ID ) FROM table_a ) 
LIMIT 10;

不推荐使用 order by rand()

order by rand()会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

43. MySQL数据库cpu飙升到500%的话怎么处理？

首先使用 top 命令，看 MySQL 所在服务器占用 CPU 最多的进程是否是 mysqld 进程。

若是 mysqld 造成，则在数据库内使用 show processlist 命令查看当前所有 session 的情况，包括并发线程数、查询耗时、是否锁等等。

普通用户只能看到自己的线程，除非赋予普通用户 PROCESS 权限。

查看 show processlist 的结果

1. 如果有执行时间将长的语句，可以拿出来看看执行计划以对应的优化。

2. 如果没有发现特别占用资源的 sql，可能是同一时间执行的 session 太多，需要配合应用层代码分析。

关于 show processlist

https://seealso.cn/post/use-show-processlist-to-troubleshoot-mysql-latencies/

44. 数据去重并留下主键id最小的行记录

首先需要查出所有重复的数据作为一个临时表 b，然后在临时表中找到最小的 id。

之后原表和临时表做关联查询，根据重复标准，再加一个条件就是原表中要删除的数据的 id 要大于临时表中的最小 id。

还是直接看 SQL 比较清楚。

DELETE a
FROM
	VIIS_ILLEGAL_VEHICLE_HANDLE_INFO a,
	( 
    SELECT
      Min( TID ) AS TID,
      PLATE_INFO,
      count( PLATE_INFO ) 
    FROM
      VIIS_ILLEGAL_VEHICLE_HANDLE_INFO 
    GROUP BY
      PLATE_INFO 
    HAVING
      count( PLATE_INFO ) > 1 
  ) b 
WHERE
	a.PLATE_INFO = b.PLATE_INFO 
	AND a.TID > b.TID;

参考博客

https://www.pianshen.com/article/47001409041/

45. 查询某个表格的占用空间大小，用 MB 表示

information_schema 记录了数据库表的基本信息，比如数据库名、使用的引擎、版本、row_format、占用的磁盘大小等等。

table_shema 字段表示属于哪个数据库，table_name 表示数据表的名字。

SELECT
	concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS DATA 
FROM
	information_schema.`TABLES` 
WHERE
	table_schema = #{dbName}
	AND table_name = #{tableName};

46. 整数达不到指定位数时填充0

借助 zerofill 关键字

ALTER TABLE table_name ADD test1 INT(6) ZEROFILL DEFAULT 0;

47. 如何计算索引列的选择性

索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，范围从1/#T到1之间。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

-- 计算主键的选择性，因为主键是唯一的，所以结果会是 1
SELECT count(DISTINCT ( TID ))/ count( TID ) FROM table_test;
-- 查询结果
+---------------------------------------+
| count(DISTINCT ( TID ))/ count( TID ) |
+---------------------------------------+
| 1.0000                                |
+---------------------------------------+
1 row in set (0.67 sec)

参考文章

• https://blog.csdn.net/mayday_su1/article/details/94659468