Java八股汇总【MySQL】
1. 基础
1.1. MySQL概述
| 概念 | 说明 |
|---|---|
| 关系型数据库 | 建立在关系模型基础上的数据库，支持事务的四大特性ACID |
| SQL | 结构化查询语言，提供与数据库交互的方式，构建数据库表、增删改查数据、构建视图、函数、存储过程 |
| MySQL | 一个开源的关系型数据库，优点有功能完善、开源免费、文档丰富、社会活跃、兼容性较好、支持分库分表、读写分离、高可用，事务支持优秀，经历过大型企业的实际运用 |
1.2. MySQL连接
| 连接 | 说明 |
|---|---|
| 内连接 | 取两个数据集的交集，只有某一方没有匹配的记录，记录都不会出现在结果集 |
| 左连接 | 返回左表的所有记录，以及右表中匹配的记录，如果左表没有匹配的记录，右表会以NULL填充 |
| 右连接 | 返回右表的所有记录，以及左表中匹配的记录，如果左表没有匹配的记录，左表会以NULL填充 |
1.3. MySQL三大范式
三大范式的作用是为了减少数据的冗余，提高数据的完整性
| 范式 | 说明 |
|---|---|
| 第一范式 | 确保表的每一列都是不可再分割的基础单元，每个字段不能包含多个值 |
| 第二范式 | 要求表中的每一列都依赖于主键，保证数据不冗余，比如订单表中出现了商品名称、价格等，理论上应只存在商品编号，在第一范式的基础上消除了部分依赖 |
| 第三范式 | 要求表中的每一列都只能依赖于主键，而不依赖于其他非主键列，在第二范式的基础上消除了传递依赖 |
1.4. MySQL字段类型
| 字段类型 | 具体类型 | 额外说明 |
|---|---|---|
| 数值类型 | 1. tinyint 2. smallint 3. mediumint 4. int 5. bigint 6. float 7. double 8. decimal | 1. 通常用tinyint表示布尔值 2. 可以使用unsigned来表示整数类型，可以拥有更大的上限范围，更适合从0开始递增的序列 3. 通常使用decimal定点数保存货币金额，或将金额转化为分值再使用整型存储 |
| 字符串类型 | 1. char 2. varchar 3. tinytext 4. text 5. mediumtext 6. longtext 7. tinyblob 8. blob 9. mediumblob 10. longblob | 1. char是定长字符串，varchar是变长字符串，char存储时会额外向右填充空格，检索时会去掉，varchar会额外利用1到2个字节记录字符串长度 2. text用于存储大字符串，blog可以用于存储二进制数据 |
| 日期类型 | 1. year 2. time 3. date 4. datetime 5. timestamp | 1. datetime的存储空间为8字节，与时区无关，timestamp存储空间为4字节，与时区有关，因此timestamp时间范围更小，表现形式都是YYYY-MM-DD HH:MM:SS |
1.5. MySQL语句
1.5.1. DDL、DML、DCL
| 语句 | 说明 |
|---|---|
| CREATE DATABASE database_name; | DDL，创建库 |
| DROP DATABASE database_name; | DDL，删除库 |
| USE database_name; | DDL，选择数据库 |
| CREATE TABLE table_name ( column1 datatype, column2 datatype, … ); | DDL，创建表 |
| DROP TABLE table_name; | DDL，删除表，删除表速度最快 |
| TRUNCATE TABLE table_name; | DDL，截断表，删除表数据，保留表结构 |
| SHOW TABLES; | DDL，查看所有表 |
| DESCRIBE table_name; | DDL，查看表结构 |
| ALTER TABLE table_name ADD column_name datatype; | DDL，增加列 |
| INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …); | DML，插入数据 |
| SELECT column_names FROM table_name WHERE condition; | DML，查询数据 |
| UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; | DML，更新数据 |
| DELETE FROM table_name WHERE condition; | DML，删除数据 |
| CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’; | DCL，创建用户 |
| GRANT ALL PRIVILEGES ON database_name.table_name TO ‘username’@‘host’; | DCL，授予权限 |
| REVOKE ALL PRIVILEGES ON database_name.table_name FROM ‘username’@‘host’; | DCL，撤销权限 |
| DROP USER ‘username’@‘host’; | DCL，删除用户 |
| START TRANSACTION; | 开始事务 |
| COMMIT; | 提交事务 |
| ROLLBACK; | 回滚事务 |
1.5.2. SQL语句使用注意
| 注意点 | 说明 |
|---|---|
| in和exist | 1. in可以用于特定的集合列表和子查询，exists只能用于子查询 2. 在内外表大小相当时，in和exists差别不大，但是子查询表大的用exists，子查询表小的用in 3. 如果使用not in，会内外表都全表扫描，不使用索引，而使用not exists子查询仍然可以用到标上的索引，所以not exists比not in更快 |
| union和union all | 1. union，会在表连接后去除重复的记录行，使用union all不会去除 2. union all的效率比union更快 |
| count | 1. count(字段)，会忽略NULL值，只统计非空值，count(*)，会统计所有记录数 2. 如果存在二级索引，count(*),count(1),count(主键)都会选择先遍历二级索引，因为二级索引更小，I/O成本比遍历主键索引更小 3. 如果count(字段)，并且字段不是索引列，会采用全表扫描计数，效率最差 4. count(*) = count(1) > count(主键) > count(字段) |
| NULL和空字符串 | 1. 空字符串用于用于比较运算符，NULL只能使用IS NULL和IS NOT NULL来判断 2. 空字符串长度是0，不占用空间，NULL值占用空间 3. 聚合函数会忽略NULL值 |
1.6. MySQL函数
| 函数 | 说明 |
|---|---|
| concat | 拼接多个字符串 |
| length | 返回字符串长度 |
| substring | 提取字符串的子串 |
| replace | 替换字符串 |
| lower | 转化为小写 |
| upper | 转化为大写 |
| trim | 去除字符串两侧空格 |
| format | 将数字转化为格式化的字符串，如1,234,567.89 |
| abs | 返回绝对值 |
| ceiling | 返回大于或等于该数值的最小整数 |
| floor | 返回小于或等于该数值的最小整数 |
| round | 四舍五入到指定的小数位数 |
| mod | 返回余数 |
| sum | 求列的总和 |
| avg | 求列的平均数 |
| count | 求列的行数 |
| max | 返回列的最大值 |
| min | 返回列的最小值 |
| group_concat | 将列的值拼接为一个字符串 |
| if | 根据条件真假返回值 |
| case | 根据条件返回值 |
| coalesce | 返回参数列表第一个非NULL值 |
| now | 返回当前日期和时间 |
| curdate | 返回当前日期 |
| curtime | 返回当前时间 |
| day | 日 |
| month | 月 |
| year | 年 |
| datediff | 返回日期之间的天数 |
| date_add | 日期加上固定的天数 |
| date_sub | 日期减上固定的天数 |
| cast | 转化为指定数据类型 |
| convert | 与cast相似，类型转换 |
1.7. SQL查询语句执行顺序
| 执行顺序 | 说明 |
|---|---|
| from | 对from左表和右表执行笛卡尔积，形成虚拟表VT1 |
| on | 对虚拟表VT1应用on筛选，符合on条件的行被插入VT2 |
| join | 这一步如果是外连接(outer join，outer关键词通常省略)，还需要在VT2的基础上添加被过滤条件筛掉的空行数据，赋予NULL值，形成VT3 |
| where | 对VT3应用where过滤条件，符合where条件的记录插入到VT4 |
| group by | 对VT4中的记录进行分组操作，生成VT5 |
| cube 、 roll up | 对VT5进行cube、roll up操作，生成VT6 |
| having | 对VT6进行having过滤，符合having条件的记录插入到VT7 |
| select | 从VT7中选择指定的列，生成VT8 |
| distinct | 从VT8中去除重复数据，生成VT9 |
| order by | 将VT9记录按照order by条件进行排序，生成VT10 |
| limit | 从VT10取出指定行的记录，产生VT11 |
2. 数据库架构
2.1. Server层
2.1.1. 连接器
连接器的主要功能，包括连接管理，验证身份认证以及权限校验等
// 登录mysql，并进行密码验证
mysql -u root -p
// 查看mysql被哪些客户端连接
show processlist
// 查看mysql的最大连接数
show variables like 'max_connections'
2.1.2. 查询缓存
| 版本 | 说明 |
|---|---|
| MySQL8.0版本以前 | 如果SQL是select查询语句，会先去查询缓存，如果命中会直接返回结果。但是表更新后，对应的查询缓存就会被清空，因此对于频繁更新的表来说并无多大提升 |
| MySQL8.0版本以后 | 功能欠缺实用性，查询缓存被移除 |
2.1.3. 解析器
解析器主要过程可以分为词法分析、语法分析，构建语法树三个步骤
| 解析器 | 含义 |
|---|---|
| 作用 | 确保SQL语法规则正确，引用的数据库、表和列都存在 |
| 词法分析 | 将一条SQL拆分成单个TOKEN组成的序列，并识别出其中的关键 |
| 语法分析 | 1. 根据词法分析结果，根据语法规则判断SQL是否合法，合法就会构建出SQL语法树 2. 根节点，通常代表SQL语句的主要操作，例如SELECT、INSERT、UPDATE、DELETE等 3. 内部节点，表示语句中的关键词、连接操作等，如WHERE，JOIN等 4. 叶子节点，表示标识符、常量、列名等，例如列age，常量18 |
SELECT id, name FROM users WHERE age > 18;
-- 分解成Token：
-- SELECT (关键字)
-- id (标识符)
-- , (分隔符)
-- name (标识符)
-- FROM (关键字)
-- users (标识符)
-- WHERE (关键字)
-- age (标识符)
-- > (操作符)
-- 18 (数值)
-- ; (分隔符)
-- 语法树结构示例
SELECT
├── Fields（查询列）
│ ├── Column(id)
│ └── Column(name)
├── From（查询数据源）
│ └── Table(users)
└── Where（查询条件）
└── Expr(>)
├── Column(age)
└── Constant(18)
2.1.4. 优化器
| 优化器 | 说明 |
|---|---|
| 作用 | 优化器会生成多个可能的执行计划，并选择其中代价最低的一个 |
| 索引选择 | 优化器会选择更低成本的索引，例如count(*)，优化器会自动选择采用key_len最小的二级索引进行扫描，相较于扫描主键索引效率更高 |
| 连接优化 | 优化器会决定表之间的连接顺序，以最小化数据处理量 |
2.1.5. 执行器
执行器会执行优化器决定的执行计划，从存储引擎中读取记录，返回给客户端
2.2. 存储引擎层
// 查看MySQL版本
SELECT VERSION();
// 查看MYSQL支持的引存储擎
SHOW ENGINES;
// 查看MySQL默认使用的存储引擎
SHOW VARIABLES LIKE '%storage_engine%';
// 切换MySQL的数据引擎
ALTER TABLE your_table_name ENGINE=InnoDB;
// 查看MySQL使用的字符集，UTF-8字符集最多3个字节，而utf8mb4字符集是4个字节的UTF-8字符
// 而emoji表情是4个字节的UTF-8字符，需要使用utf8mb4字符集
SHOW VARIABLES LIKE 'character_set_server'
2.2.1. MyISAM和InnoDB的区别
| 功能\引擎 | MyISAM | InnoDB |
|---|---|---|
| 版本 | 5.5以前的默认存储引擎 | 5.5以后的默认存储引擎 |
| 存储结构 | 索引和数据分开存储 | 索引和数据共同存储 |
| 最小锁颗粒度 | 表级锁 | 行级锁 |
| 事务支持 | 不支持 | 支持，并实现MVCC |
| 索引类型 | 非聚簇索引 | 聚簇索引 |
| 外键支持 | 不支持 | 支持 |
| 主键要求 | 可以没有主键 | 必须有主键 |
| 安全恢复 | 不支持 | 支持崩溃后的安全恢复，redo log |
| 缓存 | Key Cache，仅缓存索引页 | Buffer Pool，缓存数据页和索引页 |
| 性能 | 更适合读多写少的场景 | 读写并行的场景 |
2.2.2. InnoDB的Buffer Pool
| Buffer Pool | 说明 |
|---|---|
| 概念 | 1. Buffer Pool是InnoDB存储引擎中的一个内存缓存区，如果执行器读取数据时命中缓存，会直接返回数据，否则才会去磁盘中读取数据页，提高了数据库的读写性能 2. Buffer Pool默认大小是【128MB】，可以通过调整【innodb_buffer_pool_size】设置大小，一般可以设置为可用物理内存的60%-80% |
| 存储内容 | 1. Buffer Pool缓存了索引页、数据页、锁信息等 2. Buffer Pool还需要额外空间来创建控制块，专门存储缓存页的一些基础信息 3. Buffer Pool页面其中还可以分为三种类型，【空闲页】，未被使用的Buffer Pool页面空间。【干净页】，从磁盘中读取了数据并填充到Buffer Pool内的页面。【脏页】，该页被使用，并且被修改 |
| 预读策略和防污染策略 | 1. 预读，当MySQL加载数据页时，会将相邻的数据页一起加载进来 2. 预读失效，预读进来的数据页并没有被访问，并且还被放到了前排，导致热点页被淘汰了 3. old区，专门用于存储预读的数据区域 4. young区，专门用于存储热点访问数据区域 5. Buffer Pool的LRU链表划分为young和old两个区域，young在前面，old在后面，可以通过【innodb_old_blocks_pct】设置比例，默认是【63：37】，old区和young区的划分可以有效解决预读失效问题，将预读的页尽可能往后面放 6. Buffer Pool 污染，加载了大量的页面，导致热点页面都被淘汰了，导致热点数据访问时会缓存未命中，从而产生大量磁盘IO，性能会急剧下降 7. 防止热点数据被替换，需要提高进入young区域的门槛，【innodb_old_blocks_time】设置了在old区域停留时间的时间间隔，默认【1000ms】，当old区域中的页面，只有被访问并且超过设置的时间间隔才会移动到young区域 |
| 修改策略 | 修改数据时，会直接修改缓存页，并将其设置为脏页，并不会直接写入磁盘，如果MySQL宕机，没有及时刷入磁盘，还能通过redo log日志恢复数据 |
| 脏页刷新时机 | 1. 脏页被LRU算法淘汰时，会将修改写入磁盘 2. redo log日志满了，会主动触发脏页刷新 3. MySQL后台线程会定期将适量脏页刷入磁盘 4. MySQL正常关闭时 |
| 缓存替换策略 | 使用LRU算法，将最近最少使用的数据移出缓存 |
2.3. 数据存储形式
2.3.1. 文件格式
| 文件格式 | 说明 |
|---|---|
| db_user.opt | 用于存储当前数据库的默认字符集和字符校验规则 |
| tbl_user.frm | 用于保存每个表的结构信息 |
| tbl_user.ibd | 用于保存每个表的数据信息 |
2.3.2. 文件结构
| 文件结构 | 说明 |
|---|---|
| 段 | 表空间由多个段组成，如数据段、索引段、回滚段等 1. 索引段，存放B+树的非叶子节点的区 2. 数据段，存放B+树的叶子节点的区 3. 回滚段，存放的是事务执行中用于数据回滚的旧数据的区 |
| 区 | 段由多个区构成，一个区由64个连续的页构成，也就是1MB，64个页之间的物理位置相邻，可以提升磁盘I/O效率 |
| 页 | 页是InnoDB存储和读取数据的基本单元，默认大小是16KB的连续存储空间，从磁盘至少读取16KB的内容到内存中，内存中的16KB内容刷新到磁盘中 |
| 行 | 数据库表中的记录都是采用行存储方式，每行记录根据不同的行格式，有不同的存储结构，包括REDUNDANT、COMPACT、DYNAMIC等 |
2.3.3. COMPACT行格式
一行记录最多存储最多65535字节的数据，会包含【变长字段长度列表、NULL值列表占用字节、所有的列】，不包括【记录头信息、row_id、trx_id、roll_pointer】，所有字段的长度+变长字段长度列表占用字节数+NULL值列表所占用字节数<=65535字节，如果是ASCII字符集，设置varchar(65532)，字段占用长度65532字节，变长字段长度列表占用16位=2字节，NULL值列表1字节，如果是UTF-8字符集，一个字符最多需要3个字节，则需要65532/3 = 21844，最大取值varchar(21844)
| 字段 | 含义 |
|---|---|
| 变长字段长度列表 | 1. 变长字段长度列表，用于保存变长字段（varchar、text、blob）的真实数据占用的字节数 2. 变长字段的真实数据占用字节数会按照列的顺序从低位存放 3. 如果数据表没有变长字段时，不会有变长字段长度列表 |
| NULL值列表 | 1. NULL值列表，专门用于记录某条记录为空的字段 2. 允许存在NULL值的列，每个列对应一个二进制位，从低位存放，1为NULL，0不为NULL 3. 如果数据表的字段都是NOT NULL，就不会存在NULL值列表 4. 如果存在NULL值，那么NULL值列表至少8位=1字节，如果有9个字段是NULL，那么就会产生16位=2字节 |
| 记录头信息 | 1. 删除标记，delete_mask，用于标记此条记录是否删除 2. 下一条记录信息，next_record，指向下一条记录的位置，指向记录头信息和真实数据之间的位置，方便向左读记录头信息，向右读记录的真实数据 3. 当前记录的类型，record_type，0表示普通记录，1表示B+非叶子节点记录，2表示最小记录，3表示最大记录 |
| row_id | 如果一张表既没有主键也没有唯一约束，那么就会添加row_id隐藏字段，非必须，占用6字节 |
| trx_id | 事务id，表示这条数据记录是由哪个事务生成的，必须，占用6字节 |
| roll_pointer | 这条记录上一个版本的指针，必须，占用7字节 |
2.3.4. MySQL行溢出
| 行溢出 | 含义 |
|---|---|
| 概念 | MySQL中磁盘和内存交互的基本单位是页，一个页大小是16KB，即16384字节，而一行数据最多存储65535字节的数据，因此一个页无法存储记录时，就会发生行溢出，多的数据存储到另外的溢出页中 |
| 行溢出处理 | 1. Compact格式，记录的真实数据处只保存一部分数据，保留20字节的存储空间指向溢出页的地址 2. Dynamic、Compressed格式，记录的真实数据处采用完全的行溢出方式，不保留数据，只用20个字节存储空间指向溢出页的地址 |
3. 索引
3.1. 索引简介
| 索引 | 含义 |
|---|---|
| 概念 | 是一种数据库中用于快速查询和检索数据的数据结构，本质上用空间换时间，MySQL中MyISAM和InnoDB都使用了B+Tree作为索引结构 |
| 优点 | 1. 可以加速数据检索速度，减少磁盘I/O次数 2. 通过创建唯一性索引，可以保证数据的唯一性 |
| 缺点 | 1. 创建索引和维护索引需要耗费时间，数据进行更改时，索引也需要动态修改 2. 索引需要额外的物理空间存储 |
3.2. 索引分类
3.2.1. 按数据结构分类
按照数据结构分类，MySQL索引有B+Tree索引、Hash索引
3.2.1.1. B+Tree索引
| B+Tree索引 | 说明 |
|---|---|
| 概念 | 1. B+Tree是一种多路查找树，叶子节点存储数据，非叶子节点只存放索引 2. 叶子节点中的记录按照主键顺序存放，叶子节点间是双向链表，可以顺序遍历 3. 当数据量较小时，底层优化器可能跳过索引，直接遍历叶子节点 |
| 存储结构 | 1. InnoDB中，数据读写单位是数据页，一个数据页的默认大小是16KB，B+Tree的每一个节点都与数据页大小对齐 2. 查询数据时，每次从磁盘加载树的一个节点到内存中，树高就意味着磁盘的I/O次数，对于千万级别的数据只需要3-4层高度，因此最多只需要3-4次磁盘I/O 3. B+树存储2000w数据大概是3层树高，如果再多就可以到4层，磁盘I/O次数就会增加，所以单表建议最好不要超过2000w |
| 优点 | 1. 相较于BTree而言，B+Tree的非叶子节点不存储具体数据，因此在相同磁盘I/O次数下，单个节点能存储更多的索引，并且B+Tree叶子节点会有链表连接，方便范围查找 2. 相较于二叉树而言，B+Tree的树高度低于二叉树，因此磁盘I/O次数更小，速度更快 |
3.2.1.2. Hash索引
| Hash索引 | 说明 |
|---|---|
| 存储结构 | 存储结构类似HashMap，以作为索引的列为键，通过散列函数存储在哈希表中，哈希冲突时通过拉链法解决 |
| 优点 | 与B+Tree相比，更适合用于等值查询，但是无法做到范围查询 |
| 缺点 | InnoDB并不提供直接创建哈希索引的选项，因为B+树可以很好的满足需要 |
3.2.2. 按物理存储分类
3.2.2.1. 聚簇索引与非聚簇索引
| 索引结构 | 说明 |
|---|---|
| 聚簇索引 | 索引结构和数据一起存放的索引，主键索引属于聚簇索引 |
| 非聚簇索引 | 索引结构和数据分开存储的索引，二级索引属于非聚簇索引，MyISAM引擎使用的都是非聚簇索引 |
3.2.2.2. 主键索引与二级索引
| 索引结构 | 说明 |
|---|---|
| 主键索引 | 主键索引的叶子节点存放实际数据，即一行所有列的完整数据 |
| 二级索引 | 二级叶子节点仅存放索引列和主键列，如果需要其他列就需要回表 |
| 回表 | 如果查询的二级索引树的叶子节点中没有包含需要的列，就需要根据主键值回到主键索引重新查询 |
| 覆盖索引 | 如果查询列都被索引覆盖，可以直接从二级索引树的叶子节点中找到 |
| 联合索引 | 将多个列合成一个索引 1. 构建索引时，会按照字段顺序进行排序后排列，例如(a,b,c)，索引就会先按a排序，如果a相等再按b排序，如果b相等在按c排序 2. 最左匹配原则，符合【where a = 1; where a = 1 and b= 2 and c = 3; where a = 1 and b=2】，不符合【where b=2; where c=3; where b=2 and c=3; where a>1 and b=2】 3. 字段区分度，count(distinct(column)) / count(*)，建立联合索引时，区分度越大的字段排在前面，越有可能被更多的SQL使用 |
3.2.3. 按字段功能分类
按照字段功能分类，索引可以分为主键索引、唯一索引、普通索引、前缀索引、全文索引
| 索引类型 | 说明 |
|---|---|
| 主键索引 | 1. 主键索引就是建立在主键字段上的索引，一张表最多只有一个主键索引，索引列的值不允许有空值 2. MySQL会为主键列自动创建一个聚簇索引 |
| 唯一索引 | 一张表可以有多个唯一索引，保证索引列的值唯一，允许有空 |
| 普通索引 | 普通的索引类型，用于加速查询 |
| 前缀索引 | 1. 在普通索引的基础上，额外添加了前缀长度的限制，而不是整个字段上建立的索引 2. 使用前缀索引，可以减少索引占用的存储空间，提升查询效率 |
| 全文索引 | 特定作用于文本数据的索引，用于提高文本搜索的效率 |
3.3. 索引下推
| 索引下推 | 说明 |
|---|---|
| 概念 | 本质上是将服务层的操作下推到存储引擎层，允许在访问索引时进行数据过滤，减少回表次数 |
| 场景 | 例如这种情况，建立了联合索引(a,b)，并使用了where a>1 and b = 10，只有a会用到索引 1. MySQL5.6以前，会在存储引擎层筛选出a>1的所有数据，并回表，最后在服务层在筛选b=10 2. MySQL5.6以后，会在存储引擎层筛选出a>1和b=10的数据，回表后返回给服务层 |
| 标志 | 如果执行计划中出现了Using index condition，则说明使用了索引下推 |
3.4. 索引失效
| 失效场景 | 说明 |
|---|---|
| 使用计算、函数、类型转化操作 | 在索引列上使用计算、函数、类型转化操作，如where year(create_time) = 2000 |
| 最左前缀原则 | 建立的联合索引列不符合最左前缀原则，会导致索引失效 |
| 使用LIKE | 在索引列上使用LIKE，并且通配符在前，如like ‘%x’ 或 like ‘%x%’ |
| 使用OR操作符 | 使用OR，如果OR的前后条件有一个列没有索引，都会失效 |
| 使用NOT操作符或者<>操作符 | 索引列使用NOT操作符或者<>操作符，会导致索引失效，走全表扫描 |
| 使用IN操作符 | 索引列使用IN的取值范围较大，会导致索引失效，走全表扫描 |
| 隐式转换 | 当隐式转换时，会将左右两边转换为浮点数进行比较，因此可能导致索引失效，因此最好保证左右两边的字段类型是一致的 1. 比如varchar类型的字段，where a = ‘1000’ 会生效，where a = 1000会失效，因为’1000xxx’，'001000’都会转化为浮点数1000，此时索引失效 2. 但是对于int类型的字段，where a = ‘1000’ 也会生效，因为两边转化为浮点数1000不影响索引使用 |
3.5. 索引优化
| 情况 | 说明 |
|---|---|
| 索引构建注意点 | 1. 选择合适的列作为索引，经常被where、order by、group by、连接的列 2. 避免过多的索引，每个索引占用额外的磁盘空间，并且修改需要额外的时间更新索引 3. 如果字段区分度不高，就没必要创建索引，优化器会根据字段区分度的高低忽略索引选择全表扫描 4. 表数据量太少时，不需要创建索引 5. 经常更新的字段不要创建索引 6. 建立索引的字段最好设置为NOT NULL，因为NULL的列会使值比较更复杂 7. 如果百万数据的大表需要删除数据，可以先删除索引，删除无用数据后，再重新创建索引 |
| 索引优化 | 1. 保证主键索引是自增的，尾部追加操作速度远大于中间插入操作速度，并且中间插入会导致页分裂【比如一个页存储了1、2、4、5，4条数据，此时要插入3，就会分裂成1 2 3 ，4 5两页，4，5两条记录需要移动到另外的页中】，如果是尾部追加，则不需要移动1、2、4、5数据，可以直接开辟新页写入数据 2. 覆盖索引优化，如果查询的字段可以通过联合索引创建，从而避免回表 3. 利用前缀索引，对于字符串类型的列选择前缀索引，可以减少索引大小 4. 创建联合索引时，需要考虑排列的顺序，将常用的列放在前面，遵守最左前缀原则 |
4. 事务
4.1. 事务四大特性
事务具有四个基本特性，也就是ACID，分别对应原子性，一致性，隔离性和持久性
| 事务特性 | 描述 | 实现 |
|---|---|---|
| 原子性 | 一个事务中的所有操作，要么全部成功，要么全部失败，整个操作是不可分割的，如果任何一个操作发生错误，就会回滚到事务开始前的状态，就像这个事务没有执行过一样 | MySQL中事务的原子性是通过回滚日志undo log实现的 |
| 一致性 | 确保事务从一个一致的状态转换到另一个一致的状态，保证数据库的完整性约束 | MySQL中事务的一致性是由原子性+隔离性+持久性共同保证的 |
| 隔离性 | 确保一个事务的执行不会被其他事务干扰，防止多个事务在并发执行出现问题 | MySQL中事务的隔离性是通过多版本并发控制MVCC实现的 |
| 持久性 | 事务一旦提交，它对数据库的更改是永久性的，就算系统崩溃也不影响 | MySQL中事务的持久性是通过恢复日志redo log实现的 |
4.2. 事务并发执行问题
MySQL事务并行执行问题，主要有三类，分别是脏读、不可重复读、幻读问题
| 并发执行问题 | 说明 |
|---|---|
| 脏读 | 一个事务读取了另一个事务未提交的修改数据 |
| 不可重复读 | 一个事务执行过程中，前后两次读取操作得到的数据是不同的，数据不一致 |
| 幻读 | 一个事务执行过程中，前后两次读取操作得到的记录条数是不同的，记录条数不一致 |
4.3. 事务隔离级别
MySQL事务隔离级别主要分为四种，从低到高分别是读未提交、读已提交、可重复读和串行化，MySQL InnoDB引擎默认隔离级别是可重复读，但是很大程度上避免了幻读现象
| 隔离级别 | 说明 | 脏读问题 | 不可重复读问题 | 幻读问题 | 实现方式 |
|---|---|---|---|---|---|
| 读未提交 | 一个事务可以读取其他事务未提交的数据 | 存在 | 存在 | 存在 | 直接读取最新数据 |
| 读已提交 | 一个事务可以读取其他事务已提交的数据，存在不可重复读、幻读问题 | 不存在 | 存在 | 存在 | 每次读取数据前生成一个Read View，保证每次读取数据都是最新的数据 |
| 可重复读 | 确保在同一事务中多次读取相同记录的结果是一致的，存在幻读问题 | 不存在 | 不存在 | 不存在 | 事务启动时，生成一个Read View，然后整个事务期间都重复使用这个Read View，保证读取数据一致 |
| 可串行化 | 强制各个事务串行执行来避免并发问题，可以解决事务并发执行问题，但是效率低下 | 不存在 | 不存在 | 不存在 | 通过表级读写锁实现，读锁是表级共享锁，写锁是表级排他锁 |
4.4. MVCC
| MVCC | 说明 |
|---|---|
| 定义 | 多版本并发控制，主要用来解决数据的并发问题，当多个用户同时访问数据时，每个用户都可以无阻塞地执行查询和修改操作，互不干扰 |
| 特定 | 1. MVCC允许读操作时访问数据的一个旧版本快照，写操作时创建一个新的版本快照 2. MySQL中的InnoDB引擎使用的MVCC通过版本链和Read View机制实现 |
4.4.1. 版本链
| 版本链 | 说明 |
|---|---|
| 定义 | 每条记录的ROLL_POINTER字段保存了上一条undo record的指针，从而串起来的链，叫做版本链，可以用于追溯历史信息 |
| 结构 | 1. TRX_ID，创建该行数据的事务ID 2. ROLL_POINTER，指向上一条undo record的指针，可以访问到该行数据的历史版本 |
4.4.2. Read View
| Read View | 说明 |
|---|---|
| 定义 | Read View，读视图，在InnoDB中是一种数据结构，用于确定特定事务中哪些版本的行记录是可见的，并实现一致性读，如处理隔离级别为读已提交和可重复读的情况下保证读取的数据始终是一致的 |
| 结构 | 1. creator_trx_id，创建该ReadView的事务ID 2. m_ids，所有活跃事务【指那些已经开始但还尚未提交的事务】的ID列表 3. min_trx_id，所有活跃事务中的最小的事务ID 4. max_trx_id，下一个将要生成的全局事务ID，即当前事务ID的最大值+1 |
4.4.2.1. Read View原理
当一个事务读取数据时，会创建一个ReadView，并根据ReadView中的信息条件来判断哪个数据版本是可见，根据数据记录中的trx_id与Read View中的四个字段的条件匹配过程
| 条件匹配 | 可见性 | 含义 |
|---|---|---|
| trx_id == creator_trx_id | 可见 | 记录属于当前事务，具有实时性 |
| trx_id < min_trx_id | 可见 | 记录属于已经提交的事务 |
| trx_id > max_trx_id | 不可见 | 该记录所在事务是在Read View生成后开始的 |
| min_trx_id <= trx_id <= max_trx_id && trx_id in m_ids | 不可见 | 该记录所在事务属于活跃事务，不可见 |
| min_trx_id <= trx_id <= max_trx_id && trx_id not in m_ids | 可见 | 该记录所在事务已经提交，可见 |
4.4.2.2. 读已提交RC隔离级别
Read Committed，在事务每一次执行快照读时生成ReadView
4.4.2.3. 可重复读RR隔离级别
Read Repeatable，在事务第一次执行快照读时生成ReadView，后续快照读都使用第一个Read View，因此可以实现重复读，但在快照读和当前读的交替情况下仍会存在幻读问题，但很大程度上避免了幻读现象发生
| MySQL RR级别的读类型 | 解决幻读的方式 |
|---|---|
| 快照读(普通select语句) | 通过MVCC方式解决了幻读，因为每次读取的都是第一次快照读得到的Read View |
| 当前读(select … for update、update、insert、delete语句) | 通过next-key lock(记录锁+间隙锁的组合)方式解决了幻读，当执行语句执行时都会查询最新版本的数据并上锁，然后再做进一步的操作。如果有其他事务在锁范围内进行写操作，就会被阻塞 |
5. SQL优化
5.1. 执行计划explain
执行计划explain的基本字段及相关说明
| 字段 | 含义 | 内部类型 |
|---|---|---|
| id | 查询的标识符 | |
| select_type | 查询的类型 | 1. SIMPLE，简单查询，不包含子查询和UNION查询 2. PRIMARY，查询中如果包含子查询，最外层查询被标记为PRIMARY 3. SUBQUERY，子查询 4. DERIVED，派生表的查询 |
| table | 查询的表名 | |
| partitions | 表的分区 | |
| type | 表示MySQL在表中找到所需行的方式，性能从最优到最差分别为，system > const > eq_ref > ref > range > index > all | 1. system，表只有一行，一般是系统表 2. const，结果只有一条的主键或唯一索引扫描。通过使用主键索引或唯一索引精确匹配查询 3. eq_ref，唯一索引扫描，通常用于多表联查，使用主键索引或唯一索引产生，比如两张表的name都是唯一索引 4. ref，非唯一索引扫描，使用非唯一索引或前缀索引查询的情况，返回符合条件的多行记录，通常用于普通索引或联合索引查询 5. range，索引范围扫描，只检索给定范围的行，使用索引来检索，使用范围查询操作符 6. index，全索引扫描，扫描整个索引不访问数据行 7. all，全表扫描 |
| possible_keys | 可能会用到的索引 | |
| key | 表示实际用的索引 | |
| key_len | 表示索引的长度 | |
| rows | 估算查到结果集，需要扫描的数据行数 | |
| filtered | 结果集过滤后留下的比例 | |
| extra | 附加信息 | 1. Using index，使用了覆盖索引，避免回表操作 2. Using index condition， 使用了索引下推 3. Using where，只使用了where过滤 4. Using temporary，使用了临时表保存中间结果 5. Using filesort，无法使用索引进行排序操作，使用外部排序算法，效率较低 |
5.2. MySQL性能优化
5.2.1. 慢SQL定位
// 配置慢sql日志
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 1 # 记录执行时间超过1秒的查询
// 查看当前正在执行的SQL语句，找出执行时间较长的SQL
show processlist;
5.2.2. SQL优化
| SQL优化方法 | 说明 |
|---|---|
| 查询列优化 | 1. 避免使用select * |
| 分页优化 | 1. 延迟关联，先筛选出所需数据行的主键，再通过延迟关联回表查询数据，可以大大减少回表次数 2. 书签，记录上一次的查询返回的最后一行的某个值，避免扫描大量不需要的行 |
| 索引优化 | 1. 利用覆盖索引，可以直接从索引取值，从而减少回表 2. 避免引起索引失效的操作，如使用!=和<>，函数等 3. 适当使用前缀索引，可以降低索引的空间占用 4. 合理使用联合索引 |
| 连接优化 | 1. 小表驱动大表，使用记录行数较少的表驱动行数较大的表，可以减少建立连接的次数 2. 适当增加冗余字段，减少连表情况 3. 减少使用连接，拆分成多个简单查询，在jvm层做合并 |
| 排序优化 | 1. 排序字段要有索引优化，否则需要外部排序较慢 |
| union优化 | 1. 条件下推，将筛选条件下推到union的各个子查询中 |
5.2.3. MySQL cpu飙升场景
| 步骤 | 说明 |
|---|---|
| 查看服务器cpu占用排名，查看是否是mysqld导致 | 1. top 命令 2. ps -aux --sort=-%cpu |
| 如果mysqld cpu负载高，查看SQL具体占用情况 | show processlist，查看session情况，确定是否有高消耗资源sql在运行 |
| 定位消耗高的SQL，并排查原因 | 使用explain查看执行计划，索引是否缺失，扫描数据量是否过大 |
| 做出相应处理 | 1. kill掉高占用cpu的线程 2. 添加索引、修改sql语句 3. 重新执行SQL查看占用资源是否降低 |
6. MySQL锁
6.1. 锁的类别
MySQL的锁可以按照不同的方式划分，包括锁粒度，加锁机制，兼容性
6.1.1. 按锁的兼容性划分
按锁的兼容性，可以分为共享锁、排他锁
| 兼容性 | 含义 |
|---|---|
| 共享锁，Share Lock，读锁，S锁 | 读读共享，读写互斥，一个事务对记录添加了S锁，其余事务也可以添加S锁，但是不可以加X锁 |
| 排他锁，Exclusive Lock，写锁，X锁 | 读写互斥，写写互斥，一个事务对记录添加了X锁，其他事务可以添加S锁，也不可以加X锁 |
6.1.2. 按锁的粒度划分
按锁的粒度划分，MySQL可以分为行级锁、表级锁、全局锁
| 锁粒度 | 类别 | 额外说明 |
|---|---|---|
| 行级锁 | 1. Record Lock，记录锁，锁定一条记录 2. Gap Lock，间隙锁，锁定一个左开右开的范围，不包含记录本身，【特殊的是S型和X型间隙锁在多个事务间相互兼容，但间隙锁和插入意向锁冲突】 3. Next-Key Lock，临键锁，记录锁+间隙锁，锁定一个左开右闭范围，包含记录本身 4. insert Intention Lock，插入意向锁，并不是意向锁，是一种特殊的间隙锁，当插入一条记录时，就需要生成一个插入意向锁，如果范围内已经被其他事务上锁，需要阻塞直至其他事务释放锁 | 1. 快照读不会上锁，当前读语句除了select … lock in share mode是上s锁，其余当前读语句都是上x锁 2. 在InnoDB事务中，行锁的基本单位是临键锁，在某些条件下会退化为间隙锁和记录锁 3. select * from tbl_user for update像这种不带筛选条件包含主键、索引列或是不走索引时，会导致全表扫描，会给每条记录的索引添加临键锁，从而可能导致锁住整个表 |
| 表级锁 | 1. 表锁，又可以分为表级共享锁和表级排他锁 2. Intention Lock，意向锁，在对某些记录上行锁时，会对表级添加意向锁，从而快速判断表中是否有记录被加锁，而不需要遍历全表 3. 自增锁，AUTO_INCREMENT修饰的列，当插入数据时，需要先获取自增锁，拿不到就会被阻塞 | 1. 表级共享锁，LOCK TABLES tbl_user READ 2. 表级排他锁，LOCK TABLES tbl_user WRITE 3. 对某些记录加上共享锁前，会先在表级加上一个意向共享锁，对某些记录加上排他锁前，会先在表级加上一个意向排他锁 4. 意向共享锁和意向排他锁，只会跟表级共享锁和表级排他锁冲突 |
| 全局锁 | flush tables with read lock，使用后，整个数据库处于只读状态，保证数据不会变化，一般用于备份数据库 |
6.1.3. 按锁的机制划分
按锁的机制划分，MySQL可以分为乐观锁、悲观锁
| 锁机制 | 含义 | 具体实现 |
|---|---|---|
| 乐观锁 | 基于冲突在系统中出现的概率较低的假设，只在提交更新时才检查是否有其他事务已经修改了数据 | 可以根据版本号或时间戳来实现，更新时检查版本号或时间戳是否发生变化决定是否执行更新 |
| 悲观锁 | 基于冲突在系统中是常见的假设，因此在数据处理中，会主动锁定数据，防止其他事务更改 | 使用数据库的表锁或者行锁，来锁定被访问的数据，直至事务结束后释放 |
6.2. 死锁
死锁，两个事务相互等待对方释放锁资源，而无法继续推进的现象
6.2.1. 死锁发生
6.2.2. 死锁避免
只要死锁发生，就必然存在互斥、占有并等待、不可剥夺、循环等待的条件，只要破坏其中一个条件就不会发生死锁
| 死锁发生的必要条件 | 含义 | 如何破坏条件 |
|---|---|---|
| 互斥 | 资源互斥，资源只能由一个事务占用 | 一般不破坏互斥条件 |
| 占有并等待 | 事务会占用资源，并等待其他未获得的资源 | 1. 设置事务等待锁超时时间，超时就对事物进行回滚，将锁资源释放，可以通过innodb_lock_wait_timeout来设置超时时间，默认50秒 2. 开启主动死锁检索，发生死锁时主动回滚死锁链中的一个事务，将innodb_deadlock_detect字段设置为on，默认开启 |
| 不可剥夺 | 事务之间不能抢占其他事务占用的资源，只能通过获得资源的事务释放 | 一般不抢占 |
| 循环等待 | 发生死锁时，存在一个资源的环形链 | 调整事务的锁资源访问顺序 |
7. MySQL日志
7.1. MySQL日志种类
| MySQL日志种类 | 说明 |
|---|---|
| 错误日志，error log | 记录MySQL服务器启动、运行、停止时的问题 |
| 慢查询日志，slow query log | 记录执行时间超过long_query_time的所有SQL语句 |
| 一般查询日志，general query log | 记录所有MySQL服务器的连接信息及所有SQL语句 |
| 二进制日志，归档日志，binary log，binlog | 记录了所有修改数据库状态的SQL语句，包括insert，update，delete等，不包括查询语句，用于数据备份和主从复制 |
| 重做日志，redo log | 记录了每个物理级别的写操作，不是SQL级别的，主要用于崩溃恢复，实现事务中的持久性 |
| 回滚日志，undo log | 记录了数据被修改前的值，用于事务的回滚和MVCC，实现事务中的原子性 |
7.2. undolog
当InnoDB引擎对一条记录进行操作如增删改时，会生成一条具有相反信息的Undo Record，主要记录了此次事务修改前的数据状态，这些信息都保存在undo log中，undo log的作用主要有两点，分别是实现事务回滚、构建MVCC的版本链
| undo log的作用 | 说明 |
|---|---|
| 实现事务回滚，保证事务原子性 | 事务处理过程中，如果出现错误，或者用户执行了ROLLBACK语句，MySQL就会根据Undo Log中记录的Undo Record将数据恢复到事务开始之前的状态 1. 在插入一条记录时，会生成undo insert 【id = x】，记录主键，回滚时可以直接删掉 2. 在更新一条记录时，会生成undo update 【id = x，name = ‘1’】，如果更新不是主键列，则会记录原先记录的旧值，如果更新的是主键列，会先删除该行，再插入新的一行，回滚时可以更新回原旧值 3. 在删除一条记录时，就会生成undo delete，为原先记录打上删除标记，最终删除由purge线程完成 |
| 构建MVCC中的版本链 | MVCC通过Read View和版本链实现，版本链则是根据Undo Record串联起来的，可以匹配某条可见性的历史记录 |
7.3. redolog
7.3.1. redo log的作用
当InnoDB引擎对一条记录进行操作如增删改后时，会先更新内存中的缓存页，并标记为脏页，再将对此页的修改以redo log的形式记录下来，后续再由后台线程将脏页刷新到磁盘中实现持久化，整个过程利用了WAL（Write-Ahead Logging）技术，指MySQL的写操作不会立即写到磁盘上，而是先写日志，后续在写磁盘。redo log的作用主要是实现事务的持久性，让MySQL拥有崩溃恢复的能力
7.3.2. redo log的刷盘时机
| 刷盘时机 | 说明 |
|---|---|
| redo log buffer空间不足 | 当redo log buffer中的记录写入量大于总容量的一半，会触发刷盘 |
| 触发checkpoint机制 | 当write pos跟checkpoint相遇，代表redo log日志写满了，需要触发刷盘 |
| 事务提交时 | 事件提交时，会根据innodb_flush_log_at_trx_commit参数做出不同的策略，默认为1 1. 参数为0，只将redo log刷新到redo log buffer中，MySQL崩溃会导致上一秒事务数据丢失 2. 参数为1，将redo log buffer中的redo log直接持久化到磁盘，可以保证发生异常数据不丢失 3. 参数为2，将redo log buffer里的redo log写到redo log文件，会先缓存在OS层面的page cache中，等待后续刷盘，只有系统断电、操作系统崩溃的情况下，才会丢失数据 |
| 后台线程提交 | 后台线程每秒会将redo log buffer持久化到磁盘 |
| 正常关闭服务器 |
7.4. binlog
7.4.1. binlog的作用
等待事务提交时，再将binlog写入到binlog文件中，binlog记录了所有数据库表结构更改和表数据更改的SQL全量日志，主要的作用是用于数据备份、主从复制
| binlog和redo log的不同点 | 说明 |
|---|---|
| 适用对象 | 1. undo log和redo log是只由InnoDB存储引擎生成的 2. 而binlog是由server层生成的，因此所有存储引擎都可以使用binlog |
| 文件格式 | 1. binlog记录的是一个事务中包含的逻辑SQL语句，无论事务有多大，保证事务的原子性 2. redo log记录的是物理数据页的修改操作 |
| 写入方式不同 | 1. binlog的写入方式是追加写，写满了就继续创建一个新的文件 2. redo log的写入方式是循环写，如果写满了就需要执行checkpoint规则腾出可写空间后在写，内部本质上只需要保留脏页数据 |
| 用处不同 | 1. binlog的作用主要用于数据备份、主从复制 2. redo log的作用主要用于数据库的崩溃恢复 |
7.4.2. binlog的刷盘时机
| 刷盘步骤 | 说明 |
|---|---|
| 写入binlog cache | 事务执行过程中，MySQL为每个线程分配一块内存binlog cache用于缓冲binlog |
| 写入page cache | 事务提交时，执行器会把binlog cache中的事务完整通过调用write方法写入到binlog文件中，并清空binlog cache，此时数据还缓存在OS文件系统的page cache中 |
| 写入磁盘 | 根据一定的刷新频率，将page cache中的数据调用fsync刷新到磁盘上，这个刷新频率由sync_binlog控制，默认为0，如果发生宕机，未持久化到磁盘的数据就会丢失 1. sync_binlog = 0，每次事务提交只write，不fsync，后续交由OS决定何时进行数据持久化 2. sync_binlog = N，每次事务提交都write，积累N个事务后，调用fsync刷新数据到磁盘，风险是断电时会导致丢掉N个事务的binlog日志 |
7.4.3. MySQL主从复制
7.4.3.1. MySQL主从复制原理
MySQL主从复制是一种数据同步机制，通过binlog的数据从主数据库复制到多个从数据库中
| 主从复制步骤 | 说明 |
|---|---|
| 记录binlog | 主库在提交事务时，写入binlog，并更新主库数据 |
| 发送binlog | log dump线程将主库的binlog日志发送给所有从库，每个从数据库通过I/O线程保存到自己relay log中 |
| 回放binlog | 从数据库通过SQL线程读取relay log中的binlog日志并执行，更新从库的数据，最终实现主从数据一致性 |
7.4.3.2. MySQL主从复制模型
| 主从复制模型 | 含义 |
|---|---|
| 同步复制 | MySQL主库提交事务要等待所有从库的复制成功响应，才返回客户端响应，性能太差，有任一过程发生错误，就会影响整个过程 |
| 异步复制 | MySQL主库提交事务不等待从库的响应，就返回客户端响应，但是一旦主库宕机，数据就会丢失 |
| 半同步复制 | MySQL主库提交事务只要等待某一个从库的复制成功响应，就返回客户端响应，兼顾同步和异步优点，即时主库宕机，至少存在一个从库有最新数据，不存在数据丢失问题 |
7.4.3.3. MySQL主从同步延迟
主从同步延迟，从服务器里面读取binlog的log dump线程只有一个，当主库某一条的SQL执行时间较长或锁表，就会导致主数据库SQL大量积压，未能及时同步到从库中
| 解决方案 | 说明 |
|---|---|
| 写请求后的读请求强制发送给主库 | 比如用户注册后，登录时读取账号的读操作也发给主库，这种会跟业务强绑定，侵入较大 |
| 读从库失败后读一次主库 | 二次读取和业务无绑定，实现代价小，但是会增加主库的读压力 |
| 关键读写业务指向主库，非关键业务采用读写分离 | 需要低时延同步的关键业务全部访问主库，其余可以允许时延的非关键业务可以访问从库 |
7.5. 两阶段提交
7.5.1. 两阶段提交
两阶段提交在事务提交之后，redo log和binlog都需要持久化到磁盘，否则会形成单阶段提交导致数据库的状态不一致的情况。因此为了避免两份日志的不一致，MySQL内部会开启一个XA事务，分两阶段来完成XA事务的提交。本质上是分布式事务一致性协议，保证分布式事务的原子性
| 单阶段提交的两种情况 | 会导致的问题 |
|---|---|
| 先写入redo log，在写入binlog | redo log刷入到磁盘，此时宕机，binlog没有持久化到磁盘，当系统恢复时，主库的数据恢复正常，由于binlog缺失，会导致数据备份缺失，从库与主库数据不一致 |
| 先写入binlog，在写入redo log | binlog输入到磁盘，此时宕机，redo log没有持久化到磁盘，当系统恢复时，由于redo log缺失，主库的数据恢复缺失，binlog完整，从库恢复数据后与主库数据不一致 |
| 两阶段提交 | 含义 |
|---|---|
| prepare | 将XID写入到redo log，同时将redo log对应的事务状态设置为prepare，然后写入redo log |
| commit | 将XID写入到binlog，然后写入binlog，成功后调用InnoDB引擎的提交事务接口，将redo log的状态设置为commit。两阶段提交，本质上还是以binlog写成功作为事务提交成功的标志。 |
7.5.2. 记录执行过程
8. MySQL高可用、性能
8.1. 分库分表
8.1.1. 分库分表策略
| 策略 | 说明 |
|---|---|
| 垂直分库 | 按照业务模块将不同的表分到不同的库中 |
| 水平分库 | 将一个表中的数据拆分到多个库中 |
| 垂直分表 | 按照业务模块将表的字段在分配到多张表 |
| 水平分表 | 将表中的记录拆分到多张表中 |
8.1.2. 分库分表的问题
| 分库分表问题 | 说明 | 解决方案 |
|---|---|---|
| 事务问题 | 使用关系型数据库，可以保证事务的完整性，而分库后单机事务无法保证，需要分布式事务 | 使用seata分布式中间件 |
| 跨库连接问题 | 跨库之后无法使用join | 1. 需要在业务代码进行跨库查询，最终关联得到结果 2. 适当冗余字段，减少关联操作 3. 通过binlog同步方式，把需要跨库join的数据异构到别的数据库中进行查询 |
| 分表的汇总问题 | 由于分表，因此聚合操作可能需要由业务代码或中间件来进行汇总、排序、分页 | 使用Sharding-Sphere或者Mycat |
| 数据迁移、容量规划、扩容问题 | 因为一旦设定好表的数量，因为分表路由的问题，后期扩容会很不方便，会需要重新数据导入 | 估计表未来的数据量增长量，进行合理的容量规划，和规定合理的分表数量，选择合理的分表路由策略 |
| 主键ID问题 | 采用分表后，不能在依赖数据库自身的主键自增机制，还需要实现全局主键唯一 | 1. 使用UUID，但是不连续的主键插入会导致严重的页分裂 2. 使用分布式ID，如雪花算法 3. 根据表的数量设置自增步长 |
8.1.3. 水平分表的路由策略
在表达到千万级别时，会导致磁盘I/O查询次数增加，为了解决单表数据量过大导致查询过慢的瓶颈，就需要实现水平分表，关键在于分片键的设计，应该满足高区分度、查询频率高、写入频率高，保证数据可以均匀分布，防止数据倾斜
| 策略 | 说明 |
|---|---|
| 范围路由 | 根据分片键的值范围进行分表，比如[1,1000w]在第一张表，[1000w，2000w]在第二张表，适用于分片键具有顺序性和连续性的场景 |
| Hash路由 | 根据分片键的哈希值进行取模来确定具体存储在哪张表，优点是数据可以均匀分布，避免数据倾斜 |
| 配置路由 | 根据路由配置表来确定数据存储的表，适用于分片键不规律的场景，例如可以指定某个ID存储在哪张表中 |
8.2. 数据库读写分离
| 读写分离 | 说明 |
|---|---|
| 概念 | 搭建MySQL集群，一主多从，主库负责写操作，从库负责读操作，分担读读压力 |
| 配置 | 主库通过binlog日志将数据同步到从库，每个数据库都存储了所有业务数据 |
| 实现 | 将写操作分配给主库，将读操作分配给从库 1. 通过程序代码封装，在业务代码中抽象一个数据访问层，实现读写操作分离和数据库服务器连接管理 2. 通过中间件封装，独立一套中间件系统出来，专门用于实现读写操作分离和数据库服务器连接管理，对于客户端来说访问中间件和数据库没有区别 |
8.3. 数据冷热分离
| 冷热分离 | 说明 |
|---|---|
| 冷数据 | 指不经常访问，但需要长期保存的数据 |
| 热数据 | 指经常要被访问且修改需要快速访问的数据 |
| 冷热数据的区分指标 | 1. 时间维度，根据时间单位将数据划分，如1年内的订单数据作为热数据，1年前的作为冷数据 2. 访问频率，将高频访问的数据视为热数据，将低频访问的数据作为冷数据 |
| 冷热分离 | 将冷热数据剥离，将冷数据存储在低成本、低性能的存储介质中，将热数据存储在高性能存储介质中 1. 优点，热数据的查询性能得到优化，冷数据的剥离可以腾出更多空间来存储热数据 2. 缺点，提高了系统的不稳定性，如数据统计不一致，错误率增加 |
| 冷热数据迁移方案 | 1. 定时任务调度，利用任务调度定时去扫描数据库，找出符合条件的冷数据，批量地迁移到冷库中 2. 监听数据库变更日志binlog，如使用canal和mq监听数据库binlog，然后符合条件的复制到冷库，并从热库中删除 3. 业务层代码实现，数据写的时候判断是冷数据还是热数据，写入到不同的库，代码侵入性较强 |
8.4. 不停机扩容
| 实现步骤 | 说明 |
|---|---|
| 在线双写、查询走老库 | 拷贝新库旧库的库表结构，新数据同时写入旧库和新库，并将旧库的数据迁移到新库，对齐旧库和新库，查询依旧走老库 1. 在线双写可以使用canal监听binlog，并用mq发送到新库做复制 2. 停机，进行离线数据迁移 |
| 在线双写、查询走新库 | 新数据同时写入旧库和新库，查询切换至新库 |
| 旧库下线 | 旧库不在写入新数据，确保旧库无请求，下线老库 |