mysql

mysql

CHAR 和 VARCHAR 的区别是什么

CHAR 和 VARCHAR 是最常用到的字符串类型，
两者的主要区别在于：

• CHAR 是定长字符串，VARCHAR 是可变长字符串。CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；

• VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

• CHAR (M) 和 VARCHAR (M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

VARCHAR (100) 和 VARCHAR (10) 的区别是什么？

• VARCHAR (100) 和 VARCHAR (10) 都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR (10) 存储超过 10 个字符时，就需要修改表结构才可以。虽说 VARCHAR (100) 和 VARCHAR (10) 能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。不过，VARCHAR (100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR (100) 是按照 100 这个长度来进行的，也就会消耗更多内存。

MySQL 主要由下面几部分构成？

• 连接器： 身份认证和权限相关 (登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

MyISAM 和 InnoDB 有什么区别？

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

Mysql 事务有哪些？

ACID

• 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

脏读（Dirty read）

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

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19, 事务 1 回滚导致对 A 的修改并未提交到数据库， A 的值还是 20。

不可重复读（Unrepeatable read）

• 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

幻读（Phantom read）

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

例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。

并发事务的控制方式有哪些？

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁 / 独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁 (table-level locking) 和行级锁 (row-level locking) 。
InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。
不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

• undo log : undo log 用于记录某行数据的多个版本的数据。
• read view 和 隐藏字段：用来判断当前版本数据的可见性。

SQL 标准定义了哪些事务隔离级别？

SQL 标准定义了四个隔离级别：
READ-UNCOMMITTED (读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED (读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ (可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE (可串行化)：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

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

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

InnoDB 有哪几类行锁？

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

• 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
• 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

MYSQL 中哪几种情况会锁表

如何分析 SQL 的性能？

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

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

EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE 语句，我们一般分析 SELECT 查询较多。

我们这里简单来演示一下 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 |Usingfilesort|
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

mysql 索引数据结构？

B 树也称 B - 树，全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B + 树中的 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树 B + 树两者有何异同呢？

• B 树的所有节点既存放键 (key) 也存放数据 (data)，而 B + 树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的；B + 树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B + 树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B + 树的范围查询，只需要对链表进行遍历即可

综上，B + 树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

Mysql 索引类型有哪些？

按照数据结构维度划分：

• BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
• 哈希索引：类似键值对的形式，一次即可定位。
• RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

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

• 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
• 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引 (辅助索引) 就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。注：二级索引（Secondary Index）又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。

按照应用维度划分：

• 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
• 普通索引：仅加速查询。
• 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
• 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

聚簇索引的优缺点
优点：

• 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B + 树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
• 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

• 依赖于有序的数据：因为 B + 树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引介绍

非聚簇索引 (Non-Clustered Index) 即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引 (辅助索引) 就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：

• 更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

缺点：

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

覆盖索引

• 索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引，我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键 + 列值。最终还是要 “回表”，也就是要通过主键再查找一次，这样就会比较慢。而覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

正确使用索引的一些建议？

• 使用合适的字段（不为 NULL 的字段，被频繁查询的字段，被作为条件查询的字段，频繁需要排序的字段，被经常频繁用于连接的字段）
• 被频繁更新的字段应该慎重建立索引
• 限制每张表上的索引数量，建议单张表索引不超过 5 个（影响更新，插入速度）
• 尽可能的考虑建立联合索引而不是单列索引
• 注意避免冗余索引
• 避免索引失效
• 删除长期未使用的索引

- mysql 索引失效的集中情况？

• 字段类型不匹配
• 查询条件中包含 or
• like 通配符 % 错误使用（like a% 走索引）
• 联合索引最左匹配原则
• 索引列使用 MySQL 函数或计算，索引失效
• 使用（!= 或者 < >，not in），导致索引失效
• 使用 is null， is not null，导致索引失效
• 左连接、右连接关联字段编码不一致，索引失效
• order by 使用不当

mysql 使用建议

• 数据库和表的字符集统一使用 UTF8
• 所有表必须使用 InnoDB 存储引擎
• 所有表和字段都需要添加注释
• 尽量控制单表数据量的大小，建议控制在 500 万以内
• 谨慎使用 MySQL 分区表 （分区表在物理上表现为多个文件，在逻辑上表现为一个表；谨慎选择分区键，跨分区查询效率可能更低；建议采用物理分表的方式管理大数据。）
• 经常一起使用的列放到一个表中
• 禁止在数据库中存储文件（比如图片）这类大的二进制数据
• 尽可能把所有列定义为 NOT NULL
• 每个 InnoDB 表必须有个主键
• 避免数据类型的隐式转换
• 避免使用子查询，可以把子查询优化为 join 操作
• 避免使用 JOIN 关联太多的表
• 减少同数据库的交互次数
• 在明显不会有重复值时使用 UNION ALL 而不是 UNION
  UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
  UNION ALL 不会再对结果集进行去重操作

mysql 日志类别

• binlog： 二进制日志，记录了数据库对数据的修改记录，例如表的创建，数据更新等。但并不包括 select 这些查询语句。binlog 日志是属于逻辑语句的记录，可用于主从数据库的同步。
• relay log： 中继日志，用于主从备份恢复使用的。有主服务器的 binlog 逻辑操作语句，以及当前的恢复位置。
• 慢查询日志： 记录在 mysql 里执行时间超过预期值的耗时语句
• redo log： redo log 是对加载到内存数据页的修改结果的记录，和 binlog 不同的是，binlog 记录的是逻辑操作语句，偏向于过程记录。而 redo log 是一个数据页的修改日志，偏向于结果的记录。redo log 在写 binlog 日志前会先记录 redo log，记录完后标记为 prepare 状态。当 binlog 也写入完成后，才将 redo log 标记为 commit 状态。只有当 redo log 是 commit 状态时，事务才能真正的 commit。这样能防止主从节点根据 binlog 同步有可能事务不一致的情况。
• undo log： 回滚日志主要用于回滚数据，和 redo log 不一样的是，undo log 是逻辑日志，是一种相反操作的记录，比如在回滚时，如果是 insert 操作时，则会逆向为 delete，delete 操作时，逆向为 insert 操作，更新则恢复到当时的版本数据。

mysql 的主从复制

整体上来说，复制有 3 个步骤：

1. master log dump 线程，主从复制的基础是主库记录数据库的所有变更记录到 bin log (bin log 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件), 主节点 log dump 线程，当 bin log 有变动时，log dump 线程读取其内容并发送给从节点
2. slave I/O 线程，从节点 I/O 线程接收 binlog 内容，将将其写入到它的中继日志 (relay log)；
3. slave SQL 线程，从节点的 SQL 线程读取 relay log 文件内容，对数据更改进行重做

mysql 从库同步复制方式:

• 全同步复制
• 当向主库写数据时，只有等所有的 slave 节点将同步的 bin log 日志写入 relay log，并且响应 ack 确认后，此次的事务才会提交，然后返回客户端。数据完整性高，但性能低
• 半同步复制
• 当向主库写数据时，只要有一个 salve 节点响应 ack 后就可以认为同步成功，但细分为了两种，一种是 AFTER_COMMIT：先在主库提交事务，然后同步从库，等待从库的 ack 确认才告诉客户端是否 Ok。另一种是 AFTER_SYNC：主库先不提交事务，只有从库有 replay log , 回复了 ack 后才进行提交事务。后面一种数据一致性较高
• 异步复制 (默认)
• 当向主库写数据时，立刻返回客户端，即一旦有需要复制的就通知 slave, 但不会等待确认成功才进行后续操作。

分库分表有哪些？有什么优缺点

• 分库：从业务角度进行切分 降低单库并发及压力
• 分表：将数据根据一定的规则落在多张表上。比如按时间范围来切分，或者通过对 ID 进行 Hash 来路由到对应的表上。提高单表 sql 速度
• 分库分表后使得数据不再集中到一张表上，但也带来了维护以及其他处理问题。比如原来的事务变为分布式事务；原来的 join 操作将要变为在应用程序做过滤；还有数据的后续迁移、扩容规划等。

MySQL 主从同步延时问题

• 分库，将⼀个主库拆分为多个主库，每个主库的写并发就减少了⼏倍，此时主从延迟可以忽略不计。
• 打开 MySQL ⽀持的并⾏复制，多个库并⾏复制。如果说某个库的写⼊并发就是特别⾼，单库写并发达到了 2000/s，并⾏复制还是没意义。
• 重写代码，写代码的同学，要慎重，插⼊数据时⽴⻢查询可能查不到。
• 如果确实是存在必须先插⼊，⽴⻢要求就查询到，然后⽴⻢就要反过来执⾏⼀些操作，对这个查询设置直连主库。不推荐这种⽅法，你要是这么搞，读写分离的意义就丧失了。

慢查询该如何优化？

• 检查是否走了索引，如果没有则优化 SQL 利用索引
• 检查所利用的索引，是否是最优索引
• 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
• 检查表中数据是否过多，是否应该要进行分库分表了
• 检查数据库实例所在的机器的性能配置，是否太低，是否增加资源

分表如何处理 id?

本作品采用《CC 协议》，转载必须注明作者和本文链接