MySQL

MySQL

开源关系型数据库

1.内连接 外连接 交叉连接 笛卡尔积

MySQL 中的连接是通过两个或多个表之间的列进行关联
1）内连接 inner join：连接两表连接字段匹配行 ，一旦一方有一方无该行不会出现在查询讯结果中

SELECT Employees.Name,Departments.DeptName
FROM Employees
INNER JOIN Departments ON Employees.ID  = Departments.DeptID;

这个查询将返回所有员工及其所在部门的信息，但仅限于那些在 Departments 表中有对应部门的员工。
2）外连接 outer join：不仅返回两个表中匹配的行，还返回左表或者右表或者两者中未匹配的行

SELECT Employees.Name,Departments.DeptName
FROM Employees
LEFT OUTER JOIN Departments ON Employees.ID  = Departments.DeptID;

left join 返回左表（FROM 子句中指定的表）的所有记录，以及右表中匹配记录的记录。左表全部保留 返回的的是所有员工的名字和他们部门的名字，某些员工没有分配到部门也进行保留，右表中的部分会以NULL填充
right join 刚好与左联相反，返回右表（FROM 子句中指定的表）的所有记录，以及左表中匹配记录的记录。如果左表中没有匹配的记录，则结果中左表的部分会以 NULL 填充。
全连接（full join）：返回两张表匹配的记录，以及左右两表中各自多余的记录
3）交叉连接：返回第一个表中的每一行与第二个表中的每一行的组合，这种类型的连接通常用于生成笛卡尔积。

SELECT Employees.Name, Departments.DeptName
FROM Employees
CROSS JOIN Departments;

这个查询将为 Employees 表中的每个员工与 Departments 表中的每个部门生成一个组合。
4）笛卡尔积 ：数学中的一个概念，例如集合 A={a,b}，集合 B={0,1,2}，那么 A✖️B={<a,0>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

2.数据库的三大范式

三大范式的作用是为了减少数据冗余，提高数据完整性
1）第一范式（1NF）：确保表的每一列都是不可分割的基本数据单元，比如说用户地址，应该拆分成省、市、区、详细信息等 4 个字段。
2）第二范式（2NF）：在 1NF 的基础上，要求数据库表中的每一列都和主键直接相关，而不能只与主键的某一部分相关（主要针对联合主键）。

3）第三范式（3NF）：在 2NF 的基础上，消除非主键列对主键的传递依赖，即非主键列只依赖于主键列，不依赖于其他非主键列。

2.DDL 、DML 、DQL 、DCL

1. DDL（Data Definition Language） DDL语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。

CREATE TABLE 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,...)

ALTER TABLE 表名;
eg：ALTER TABLE 表名 ADD 列名 数据类型;（添加一个列）
    ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;（修改列名）
    ALTER TABLE 表名 DROP 列名;

DROP TABLE 表名;
DROP DATABASE 数据库名;

1. DML 语句 DML（Data Manipulation Language）语句: 数据操纵语言，主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。
2. DQL 语句 DQL（Data Query Language）语句：数据查询语言，主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等。
3. DCL 语句 DCL（Data Control Language）语句： 数据控制语言，主要是用来设置/更改数据库用户权限。常用关键字有 GRANT、REVOKE 等

GRANT （授权）

REVOKE （取消权限）

4.区别

1）varchar与char

• char固定字符串长度（最多能存放的字符个数为 255字节），插入数据的长度小于 char 的固定长度时，则用空格填充；因为长度固定，所以存取速度要比 varchar 快很多，会占据多余空间，空间换时间
• varchar 可变长字符串（最多能存放的字符个数为 65532字节），插入的数据是多长，就按照多长来存储；varchar 它存取慢时间换空间

但mysql5字段定义时，是定义的【字符】数。比如varchar(10)，你仅能存入10个英文字母或者汉字，尽管一个字符可能占多个字节。
一个字符可能占用多个字节，这由编码和存放的字符决定。比如UTF8（一种变长的unicode编码）中，一般一个汉字占据3个字节，一个英文字母占据一个字节。
2）blob和text

• blob(Binary Large Object（二进制大对象) 用于存储二进制数据，而 text 用于存储大字符串。
• blob 没有字符集，text 有一个字符集，并且根据字符集的校对规则对值进行排序和比较

Blob 对象就是一个包含有只读原始数据的类文件对象​。File 接口基于 Blob，继承了 Blob 的功能。通过 Blob.slice 方法，可以实现大文件的切片上传 ；通过 url 下载文件，window.URL 对象可以为 Blob 对象生成一个网络地址 ，结合 a 标签的 download 属性，可以实现点击 url 下载文件；通过 url 显示图片，把图片转化为 Blob 对象，生成URL（URL.createObjectURL(blob)）来显示图片。
Bolg其作用也仅仅是一个容器，而真正的业务功能则需要通过FileReader、URL、Canvas等对象实现。

3）DATETIME 和TIMESTAMP
—相同点：

• 两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
• 两个数据类型都包含「日期」和「时间」部分。
• 两个数据类型都可以存储微秒的小数秒（秒后 6 位小数秒）

—区别

• 日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC
• 存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
• 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
• 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

4）in与exists

• MySQL 中的 in 语句是把外表和内表作 hash 连接，而 exists 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询。
• 如果查询的两个表大小相当，那么用 in 和 exists 差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in。
• not in 和 not exists：如果查询语句使用了 not in，那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用 not exists 都比 not in 要快。

# 2759174行数据
SELECT COUNT(*) FROM tb_data t1;

# 7262行数据
SELECT COUNT(*) FROM tb_task t2;

# 执行时间为44.88s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE t1.task_id IN(SELECT t2.id FROM tb_task t2);

# 执行时间为28.93s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE EXISTS(SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);

exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃
n查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询：

select * from user where user_id in(1,2,3);
等效于
select * from user where user_id =1or user_id =2or user_id =3;

in查询的子条件返回结果必须只有一个字段，例如

select * from user where user_id in(select id from B);

不能是
select * from user where user_id in(select id, age from B);

而exists就没有这个限制。
5）记录货币 Decimal 、Numeric
salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而 2(scale)代表将被用于存储小数点后的位数。存储在 salary 列中的值的范围是从-9999999.99 到 9999999.99。
作为字符串存储 若用float或者double作为二进制存储 有误差

6）MySQL 的 utf8 字符集仅支持最多 3 个字节的 UTF-8 字符，但是 emoji 表情（😊）是 4 个字节的 UTF-8 字符，所以在 MySQL 中存储 emoji 表情时，需要使用 utf8mb4 字符集。
MySQL 8.0 已经默认支持 utf8mb4 字符集

7）drop delete与truncate（英文翻译截断）

8)UNION 与UNION ALL
如果使用 UNION，会在表链接后筛选掉重复的记录行
如果使用 UNION ALL，不会合并重复的记录行
从效率上说，UNION ALL 要比 UNION 快很多，如果合并没有刻意要删除重复行，那么就使用 UNION All

9）count（1）、count（*）与count（列名）
COUNT 函数用于计算查询结果集中的行数。

• count(1)包括了忽略所有列，COUNT(1) 会将 1 作为一个非空的常量值，并对每一行进行计数，在统计结果的时候，不会忽略列值为 NULL，适用于计算总行数，与 COUNT(*) 无区别
• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为 NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者 0，而是表示 null）的计数，即某个字段值为 NULL 时，不统计。

5. SQL查询语句执行顺序

CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合

CREATE DATABASE database_name;
DROP DATABASE database_name;
USE database_name;

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,...);
DROP TABLE table_name;
SHOW TABLES;
DESCRIBE table_name;
ALTER TABLE 表名 ADD 列名 数据类型;（添加一个列）
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;（修改列名）
ALTER TABLE 表名 DROP 列名;

INSERT INTO table_name(column1, column2,...)VALUES(value1, value2,...);
SELECT column_names FROM table_name WHERE condition;
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
DELETE FROM table_name WHERE condition;

CREATE INDEX index_name ON table_name(column_name);
ALTER TABLE table_name ADD PRIMARY KEY(column_name);
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY(column_name) REFERENCES parent_table(parent_column_name);

创建用户:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
授予权限:
GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host';
撤销权限:
REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host';
DROP USER 'username'@'host';

START TRANSACTION;
COMMIT;
ROLLBACK;

6. MySQL bin目录下的可执行文件

mysql：客户端程序，用于连接 MySQL 服务器
mysqldump：一个非常实用的 MySQL 数据库备份工具，用于创建一个或多个 MySQL 数据库级别的 SQL 转储文件，包括数据库的表结构和数据。对数据备份、迁移或恢复非常重要。
mysqladmin：mysql 后面加上 admin 就表明这是一个 MySQL 的管理工具，它可以用来执行一些管理操作，比如说创建数据库、删除数据库、查看 MySQL 服务器的状态等。
mysqlcheck：mysqlcheck 是 MySQL 提供的一个命令行工具，用于检查、修复、分析和优化数据库表，对数据库的维护和性能优化非常有用。
mysqlimport：用于从文本文件中导入数据到数据库表中，非常适合用于批量导入数据。
mysqlshow：用于显示 MySQL 数据库服务器中的数据库、表、列等信息。
mysqlbinlog：用于查看 MySQL 二进制日志文件的内容，可以用于恢复数据、查看数据变更等

在 MySQL 中，要查询第 3 到第 10 条记录，可以使用 limit 语句，结合偏移量 offset 和行数 row_count 来实现。
limit 语句用于限制查询结果的数量，偏移量表示从哪条记录开始，行数表示返回的记录数量。

SELECT * FROM table_name LIMIT 2, 8;

2：偏移量，表示跳过前两条记录，从第三条记录开始。
8：行数，表示从偏移量开始，返回 8 条记录。
偏移量是从 0 开始的，即第一条记录的偏移量是 0；如果想从第 3 条记录开始，偏移量就应该是 2。

7.MySQL函数

字符串函数

• CONCAT(): 连接两个或多个字符串。
• LENGTH(): 返回字符串的长度。
• SUBSTRING(): 从字符串中提取子字符串。
• REPLACE(): 替换字符串中的某部分。
• LOWER() 和 UPPER(): 分别将字符串转换为小写或大写。
• TRIM(): 去除字符串两侧的空格或其他指定字符。

-- 连接字符串
SELECT CONCAT('沉默',' ','王二') AS concatenated_string;-- 获取字符串长度
SELECT LENGTH('沉默 王二') AS string_length;-- 提取子字符串
SELECT SUBSTRING('沉默 王二',1,5) AS substring;-- 替换字符串内容
SELECT REPLACE('沉默 王二','王二','MySQL') AS replaced_string;-- 字符串转小写
SELECT LOWER('HELLO WORLD') AS lower_case;-- 字符串转大写
SELECT UPPER('hello world') AS upper_case;-- 去除字符串两侧的空格
SELECT TRIM('  沉默 王二  ') AS trimmed_string;

数值函数

• ABS(): 返回一个数的绝对值。
• CEILING(): 返回大于或等于给定数值的最小整数。
• FLOOR(): 返回小于或等于给定数值的最大整数。
• ROUND(): 四舍五入到指定的小数位数。
• MOD(): 返回除法操作的余数。

-- 返回绝对值
SELECT ABS(-123) AS absolute_value;-- 向上取整
SELECT CEILING(123.45) AS ceiling_value;-- 向下取整
SELECT FLOOR(123.45) AS floor_value;-- 四舍五入
SELECT ROUND(123.4567,2) AS rounded_value;-- 余数
SELECT MOD(10,3) AS modulus;

时间和日期函数

• NOW(): 返回当前的日期和时间。
• CURDATE(): 返回当前的日期。
• CURTIME(): 返回当前的时间。
• DATE_ADD() 和 DATE_SUB(): 在日期上加上或减去指定的时间间隔。
• DATEDIFF(): 返回两个日期之间的天数。
• DAY(), MONTH(), YEAR(): 分别返回日期的日、月、年部分。

汇总函数

• SUM(): 计算数值列的总和
• AVG(): 计算数值列的平均值。
• COUNT(): 计算某列的行数。
• MAX() 和 MIN(): 分别返回列中的最大值和最小值
• GROUP_CONCAT(): 将多个行值连接为一个字符串。

逻辑函数

• IF(): 如果条件为真，则返回一个值；否则返回另一个值。
• CASE: 根据一系列条件返回值。
• COALESCE(): 返回参数列表中的第一个非 NULL 值。（英文翻译合并）

-- IF函数
SELECT IF(1>0,'True','False') AS simple_if;-- CASE表达式
SELECT CASE WHEN 1>0 THEN 'True' ELSE 'False' END AS case_expression;-- COALESCE函数
SELECT COALESCE(NULL,NULL,'First Non-Null Value','Second Non-Null Value') AS first_non_null;

格式化函数

• FORMAT(): 格式化数字为格式化的字符串，通常用于货币显示。
• CAST(): 将一个值转换为指定的数据类型。
• CONVERT(): 类似于CAST()，用于类型转换。

-- 格式化数字
SELECT FORMAT(1234567.8945,2) AS formatted_number;-- CAST函数
SELECT CAST('2024-01-01' AS DATE) AS casted_date;-- CONVERT函数
SELECT CONVERT('123', SIGNED INTEGER) AS converted_number;

SQL隐式数据类型转换
当一个整数和一个浮点数相加时，整数会被转换为浮点数，然后再进行相加。
当一个字符串和一个整数相加时，字符串会被转换为整数，然后再进行相加。
可以通过显式转换来规避这种情况。

SELECT CAST('1' AS SIGNED INTEGER) + 1; -- 结果为 2

8.MYSQL基础架构

一条SQL查询语句在MYSQL中的执行流程

9. MySQL数据存储形式

MySQL 是以表的形式存储数据的，而表空间的结构则由段、区、页、行组成。

10. MySQL存储引擎

1. 存储引擎 MySQL 5.5 之前，默认存储引擎是 MyISAM，5.5 之后是 InnoDB。 InnoDB 支持的哈希索引是自适应的，不能人为干预。 可以通过 alter table 语句来切换 MySQL 的数据引擎。ALTER TABLE your_table_name ENGINE=InnoDB;
2. 存储引擎的选择 大多数情况下，使用默认的 InnoDB 就对了，InnoDB 可以提供事务、行级锁等能力。 MyISAM 适合读更多的场景。 MEMORY 适合临时表，数据量不大的情况。由于数据都存放在内存，所以速度非常快
3. InnoDB与MyISAM其区别 下面这七个方面的区别

10.MYSQL日志

1. 日志及其作用 redo log在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。在redo log满了到擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求，所以有可能会导致MySQL卡顿。

11.更新语句的执行过程

1） MySQL 保证数据不会丢的能力主要体现在两方面：

• 能够恢复到任何时间点的状态；
• 能够保证MySQL在任何时间段突然奔溃，重启后之前提交的记录都不会丢失；

对于第一点将MySQL恢复到任何时间点的状态，相信很多人都知道，只要保留有足够的binlog，就能通过重跑binlog来实现。

对于第二点的能力，也就是本文标题所讲的crash-safe。即在 InnoDB 存储引擎中，事务提交过程中任何阶段，MySQL突然奔溃，重启后都能保证事务的完整性，已提交的数据不会丢失，未提交完整的数据会自动进行回滚。这个能力依赖的就是redo log和unod log两个日志。

2） 我们可以假设不采用两阶段提交的方式，而是采用“单阶段”进行提交，即要么先写入 redo log（WAL典型应用），后写入 binlog；要么先写入 binlog，后写入 redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。

3） 针对通过在两阶段提交中加锁控制事务提交顺序这种实现方式遇到的性能瓶颈问题，有没有更好的解决方案呢？

在MySQL 5.6 就引入了binlog组提交，即BLGC（Binary Log Group Commit）。binlog组提交的基本思想是，引入队列机制保证InnoDB commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现组提交目的。

4）奔溃重启后会检查redo log中是完整并且处于prepare状态的事务，然后根据XID（事务ID），从binlog中找到对应的事务，如果找不到，则回滚；找到并且事务完整则重新commit redo log，完成事务的提交。
5）redo log如何刷入磁盘
redo log 的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。

• log buffer 空间不足时 log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
• 事务提交时 在事务提交时，为了保证持久性，会把 log buffer 中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。
• 后台线程输入 有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
• 正常关闭服务器时
• 触发 checkpoint 规则 write pos是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。当write_pos追上checkpoint时，表示 redo log 日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。 所谓的checkpoint 规则，就是 checkpoint 触发后，将 buffer 中日志页都刷到磁盘。

12、SQL优化

• 慢SQL定位 慢 SQL 也就是执行时间较长的 SQL 语句，MySQL 中 long_query_time 默认值是 10 秒，也就是执行时间超过 10 秒的 SQL 语句会被记录到慢查询日志中。 SQL 执行过程中，优化器通过成本计算预估出执行效率最高的方式： 影响 SQL 执行效率的因素有： ①、IO 成本 数据量：数据量越大，IO 成本越高。所以要避免 select *；尽量分页查询。数据从哪读取：尽量通过索引加快查询。 ②、CPU 成本 尽量避免复杂的查询条件，如有必要，考虑对子查询结果进行过滤。 尽量缩减计算成本，比如说为排序字段加上索引，提高排序效率；比如说使用 union all 替代 union，减少去重处理。 查找手段：慢查询日志和服务监控 找到对应的慢 SQL 后，使用 EXPLAIN 命令查看 MySQL 是如何执行 SQL 语句的，再根据执行计划对 SQL 进行优化。 慢 SQL 日志的开启方式有多种，比如说直接编辑 MySQL 的配置文件 my.cnf 或 my.ini，设置 slow_query_log 参数为 1，设置 slow_query_log_file 参数为慢查询日志的路径，设置 long_query_time 参数为慢查询的时间阈值。然后重启 MySQL 服务就好了
• 优化SQL的方法
• 尽量避免使用 select *，只查询需要的列，减少数据传输量。
• 延迟关联（Late Row Lookups）延迟关联适用于需要从多个表中获取数据且主表行数较多的情况。它首先从索引表中检索出需要的行 ID，然后再根据这些 ID 去关联其他的表获取详细信息。
• 书签（Seek Method）书签方法通过记住上一次查询返回的最后一行的某个值，然后下一次查询从这个值开始，避免了扫描大量不需要的行。

正确地使用索引可以显著减少 SQL 的查询时间，通常可以从索引覆盖、避免使用 != 或者 <> 操作符、适当使用前缀索引、避免列上函数运算、正确使用联合索引等方面进行优化。

• 利用覆盖索引 使用非主键索引查询数据时需要回表，但如果索引的叶节点中已经包含要查询的字段，那就不会再回表查询了，这就叫覆盖索引。 它指的是一种索引能够“覆盖”查询中所涉及的所有列，换句话说，查询所需的数据全部都可以从索引中直接获取，而无需访问数据表的行数据（也就是无需回表）。

 ALTER TABLE mytable ADD INDEX name_city_age(username(10),city,age);

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
[ usernname,city,age] 、[ usernname,city]、[ usernname]
最左前缀
为什么没有 city，age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。
简单的理解就是只从最左面的开始组合。组合索引的第一个字段必须出现在查询组句中，并且不能跳跃，这个索引才会被用到，因此并不是只要包含这三列的查询都会用到该组合索引
两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。

• 避免使用 != 或者 <> 操作符
• 适当使用前缀索引，适当使用前缀索引可以降低索引的空间占用，提高索引的查询效率。邮箱的后缀一般都是固定的@xxx.com，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引：alter table test add index index2(email(6));，MySQL 无法利用前缀索引做 order by 和 group by 操作。
• 避免列上使用函数，在 where 子句中直接对列使用函数会导致索引失效，因为数据库需要对每行的列应用函数后再进行比较，无法直接利用索引。
• 正确使用联合索引，正确地使用联合索引可以极大地提高查询性能，联合索引的创建应遵循最左前缀原则，即索引的顺序应根据列在查询中的使用频率和重要性来安排。
• 优化子查询，子查询，特别是在 select 列表和 where 子句中的子查询，往往会导致性能问题，因为它们可能会为每一行外层查询执行一次子查询。select name from A where id in (select id from B);替换为select A.name from A join B on A.id=B.id;
• 小表驱动大表，在执行 JOIN 操作时，应尽量让行数较少的表（小表）驱动行数较多的表（大表），这样可以减少查询过程中需要处理的数据量。比如 left join，左表是驱动表，所以 A 表应小于 B 表，这样建立连接的次数就少，查询速度就快了。
• 适当增加冗余字段，在某些情况下，通过在表中适当增加冗余字段来避免 JOIN 操作，可以提高查询效率，尤其是在高频查询的场景下。
• 避免使用 JOIN 关联太多的表，最多不要超过 3 张表。因为 join 太多表会降低查询的速度，返回的数据量也会变得非常大，不利于后续的处理。
• MySQL 生成有序结果的方式有两种：一种是对结果集进行排序操作，另外一种是按照索引顺序扫描得出的自然有序结果。

UNION 操作用于合并两个或者多个 SELECT 语句的结果集。

• 条件下推，条件下推是指将 where、limit 等子句下推到 union 的各个子查询中，以便优化器可以充分利用这些条件进行优化。 explain 是 MySQL 提供的一个用于查看查询执行计划的工具，可以帮助我们分析查询语句的性能瓶颈，找出慢 SQL 的原因。使用方式也非常简单，在 select 语句前加上 explain 关键字就可以了。

13. 索引

1. 索引分类 主键索引一定是聚簇索引

CREATE TABLE users(
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255));

唯一索引: 保证数据列中每行数据的唯一性，但允许有空值。

CREATE UNIQUE INDEX idx_username ON users(username);

普通索引: 基本的索引类型，用于加速查询。

CREATE INDEX idx_email ON users(email);

全文索引：特定于文本数据的索引，用于提高文本搜索的效率。=

CREATE FULLTEXT INDEX idx_article_content ON articles(content);

• B+树 Hash索引
• B+树索引是InnoDB 存储引擎的默认索引类型。B+ 树中的非叶子节点都不存储数据，只存储索引。从小到大的有序双向链表，使得在完成一次树的遍历定位到范围查询的起点后，可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。

创建 B+ 树的时候不需要指定索引类型。

CREATE TABLE example_btree(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    INDEX name_index(name)) ENGINE=InnoDB;

• Hash 索引：基于哈希表的索引，查询效率可以达到 O(1)，但是只适合 = 和 in 查询，不适合范围查询。当发生哈希冲突的时候也是通过拉链法来解决。 MEMORY 存储引擎

CREATE TABLE example_hash(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    UNIQUE HASH(name)) ENGINE=MEMORY;

• InnoDB 存储引擎内部使用了一种名为“自适应哈希索引”（Adaptive Hash Index, AHI）的技术。 自适应哈希索引并不是由用户显式创建的，而是 InnoDB 根据数据访问的模式自动建立和管理的。当 InnoDB 发现某个索引被频繁访问时，会在内存中创建一个哈希索引，以加速对这个索引的访问。

1. 聚簇索引与非聚簇索引 聚簇索引：聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行。 非聚簇索引：它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。 InnoDB 存储引擎的主键使用的是聚簇索引，MyISAM 存储引擎不管是主键索引，还是二级索引使用的都是非聚簇索引。
2. 创建索引注意的点
3. 索引失效的情况1）在索引列上使用函数或表达式：如果在查询中对索引列使用了函数或表达式，那么索引可能无法使用，因为数据库无法预先计算出函数或表达式的结果。例如：SELECT * FROM table WHERE YEAR(date_column) = 2021。 2）使用不等于（<>）或者 NOT 操作符：这些操作符通常会使索引失效，因为它们会扫描全表。 3）使用 LIKE 操作符，但是通配符在最前面：如果 LIKE 的模式串是以“%”或者“_”开头的，那么索引也无法使用。例如：SELECT * FROM table WHERE column LIKE ‘%abc’。 4）OR 操作符：如果查询条件中使用了 OR，并且 OR 两边的条件分别涉及不同的索引，那么这些索引可能都无法使用。 5）联合索引不满足最左前缀原则时，索引会失效。 区分度 = 字段的唯一值数量 / 字段的总记录数； 索引不是键的越多越好，索引会占据磁盘空间；索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL 不仅要保存数据，还有保存或者更新对应的索引文件。

内存和磁盘在进行 IO 读写的时候，有一个最小的逻辑单元，叫做页（Page），页的大小一般是 4KB。那为了提高读写效率，从磁盘往内存中读数据的时候，一次会读取至少一页的数据，比如说读取 2KB 的数据，实际上会读取 4KB 的数据；
B 树的节点大小通常与页的大小对齐，这样每次从磁盘加载一个节点时，可以正好是一个页的大小。
B 树的一个节点通常包括三个部分：
键值：即表中的主键
指针：存储子节点的信息
数据：表记录中除主键外的数据
因为 B 树的每个节点上都存了数据，就导致每个节点能存储的键值和指针变少了，因为每一页的大小是固定的，对吧？
于是 B+树就来了，B+树的非叶子节点只存储键值，不存储数据，而叶子节点存储了所有的数据，并且构成了一个有序链表。

MySQL 的默认存储引擎是 InnoDB，它采用的是 B+树索引，B+树是一种自平衡的多路查找树，和红黑树、二叉平衡树不同，B+树的每个节点可以有 m 个子节点，而红黑树和二叉平衡树都只有 2 个。
和 B 树不同，B+树的非叶子节点只存储键值，不存储数据，而叶子节点存储了所有的数据，并且构成了一个有序链表。
这样做的好处是，非叶子节点上由于没有存储数据，就可以存储更多的键值对，再加上叶子节点构成了一个有序链表，范围查询时就可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。查询的效率会更高。
B+树的叶子节点是通过双向链表连接的，这样可以方便范围查询和反向遍历。如果需要在 B+树中从大值向小值进行检索，可以按以下步骤操作：定位到最右侧节点：首先，找到包含最大值的叶子节点。这通常通过从根节点开始向右遍历树的方式实现。
反向遍历：一旦定位到了最右侧的叶子节点，可以利用叶节点间的双向链表向左遍历。
6. MongoDB索引用B树，MySQL用B+树的原因
MySQL 属于关系型数据库，所以范围查询会比较多，所以采用了 B+树；但 MongoDB 属于非关系型数据库，在大多数情况下，只需要查询单条数据，所以 MongoDB 选择了 B 树。
那么在查找单条数据时，B 树的查询效率可能会更高，因为每个节点都存储数据，所以最好情况就是 O(1)。

hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.
hash索引不支持模糊查询以及多列索引的最左前缀匹配,因为hash函数的不可预测,eg:AAAA和AAAAB的索引没有相关性.
hash索引不支持使用索引进行排序,因为hash函数的不可预测.
7. 聚簇索引和非聚簇索引的区别
每个表只能有一个聚簇索引。表可以有多个非聚簇索引。
InnoDB 采用的是聚簇索引，如果没有显式定义主键，InnoDB 会选择一个唯一的非空列作为隐式的聚簇索引；如果这样的列也不存在，InnoDB 会自动生成一个隐藏的行 ID 作为聚簇索引。这意味着数据与主键是紧密绑定的，行数据直接存储在索引的叶子节点上。
MyISAM 采用的是非聚簇索引，表数据存储在一个地方，而索引存储在另一个地方，索引指向数据行的物理位置。

14.MySQL中的锁

• 按锁粒度划分的话，MySQL 的锁有： 表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低；不会出现死锁。 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

START TRANSACTION;-- 加排他锁，锁定某一行
SELECT * FROM your_table WHERE id =1 FOR UPDATE;-- 对该行进行操作
UPDATE your_table SET column1 ='new_value' WHERE id =1;

COMMIT;

START TRANSACTION;-- 加共享锁，锁定某一行
SELECT * FROM your_table WHERE id =1 LOCK IN SHARE MODE;-- 只能读取该行，不能修改

COMMIT;

select。。。for update 注意事项：如果查询条件使用了索引（特别是主键索引或唯一索引），SELECT FOR UPDATE 会锁定特定的行，即行级锁，这样锁的粒度较小，不会影响未涉及的行或其他并发操作。

但如果查询条件未使用索引，SELECT FOR UPDATE 可能锁定整个表或大量的行，因为查询需要执行全表扫描。

页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

• 按兼容性划分的话，MySQL 的锁有： 共享锁（S Lock），也叫读锁（read lock），相互不阻塞。 排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据
• 按加锁机制划分 ①、乐观锁 乐观锁基于这样的假设：冲突在系统中出现的频率较低，因此在数据库事务执行过程中，不会频繁地去锁定资源。相反，它在提交更新的时候才检查是否有其他事务已经修改了数据。 可以通过在数据表中使用版本号（Version）或时间戳（Timestamp）来实现，每次读取记录时，同时获取版本号或时间戳，更新时检查版本号或时间戳是否发生变化。 如果没有变化，则执行更新并增加版本号或更新时间戳；如果检测到冲突（即版本号或时间戳与之前读取的不同），则拒绝更新。不同于悲观锁，乐观锁通常是由开发者实现的。 ②、悲观锁 悲观锁假设冲突是常见的，因此在数据处理过程中，它会主动锁定数据，防止其他事务进行修改。 可以直接使用数据库的锁机制，如行锁或表锁，来锁定被访问的数据。常见的实现是 SELECT FOR UPDATE 语句，它在读取数据时就加上了锁，直到当前事务提交或回滚后才释放。数据库中的行锁，表锁，读锁，写锁均为悲观锁。 库存超卖 乐观锁：UPDATE inventory SET count = count - 1, version = version + 1 WHERE product_id = 1 AND version = current_version; 悲观锁：

START TRANSACTION;
SELECT * FROM inventory WHERE product_id =1 FOR UPDATE;
UPDATE inventory SET count = count -1 WHERE product_id =1;
COMMIT;

• 全局锁就是对整个数据库实例进行加锁，在 MySQL 中，可以使用 FLUSH TABLES WITH READ LOCK 命令来获取全局读锁。全局锁的作用是保证在备份数据库时，数据不会发生变化，当我们需要备份数据库时，可以先获取全局读锁，然后再执行备份操作。
• 表锁（Table Lock）就是锁住整个表。在 MySQL 中，可以使用 LOCK TABLES 命令来锁定表。表锁可以分为读锁（共享锁）和写锁（排他锁）。

LOCK TABLES your_table READ;-- 执行读操作
UNLOCK TABLES;
读锁允许多个事务同时读取被锁定的表，但不允许任何事务进行写操作
LOCK TABLES your_table WRITE;-- 执行写操作
UNLOCK TABLES;
写锁允许一个事务对表进行读写操作，其他事务不能对该表进行任何操作（读或写）。

在进行大规模的数据导入、导出或删除操作时，为了防止其他事务对数据进行并发操作，可以使用表锁。
或者在进行表结构变更（如添加列、修改列类型）时，为了确保变更期间没有其他事务访问或修改该表，可以使用表锁。

• 记录锁Record Lock 记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。例如select * from t where id =6 for update;就会将id=6的记录锁定。
• 间隙锁 Gap Lock 间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间。间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就会将(1,6)区间锁定。
• 临键锁 临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。 Mysql默认的行锁类型是临时锁，当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的，在已提交读（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)都会失效！
• 插入意向锁 一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁 ，如果有的话，插入操作需要等待，直到拥有 gap 锁 的那个事务提交。但是事务在等待的时候也需要在内存中生成一个 锁结构 ，表明有事务想在某个 间隙 中插入新记录，但是现在在等待。这种类型的锁命名为 Insert Intention Locks ，也就是插入意向锁 。在同一位置插入的多个插入意向锁不会互斥
• 意向锁 意向锁是一个表级锁，不要和插入意向锁搞混。 意向锁的出现是为了支持 InnoDB 的多粒度锁，它解决的是表锁和行锁共存的问题。当我们需要给一个表加表锁的时候，我们需要根据去判断表中有没有数据行被锁定，以确定是否能加成功。假如没有意向锁，那么我们就得遍历表中所有数据行来判断有没有行锁；有了意向锁这个表级锁之后，则我们直接判断一次就知道表中是否有数据行被锁定了。有了意向锁之后，要执行的事务 A 在申请行锁（写锁）之前，数据库会自动先给事务 A 申请表的意向排他锁。当事务 B 去申请表的互斥锁时就会失败，因为表上有意向排他锁之后事务 B 申请表的互斥锁时会被阻塞。

15.MySQL遇到死锁问题如何解决

排查死锁的一般步骤是这样的：

（1）查看死锁日志 show engine innodb status;

（2）找出死锁 sql

（3）分析 sql 加锁情况

（4）模拟死锁案发

（5）分析死锁日志

（6）分析死锁结果

16.MySQL事务

事务是一个或多个 SQL 语句组成的一个执行单元，这些 SQL 语句要么全部执行成功，要么全部不执行，不会出现部分执行的情况。

• 事务的主要作用是保证数据库操作的一致性 事务具有四个基本特性，也就是通常所说的 ACID 特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。 1）原子性子性意味着事务中的所有操作要么全部完成，要么全部不完成，它是不可分割的单位。 2）一致性确保事务从一个一致的状态转换到另一个一致的状态。比如在银行转账事务中，无论发生什么，转账前后两个账户的总金额应保持不变。假如 A 账户（100 块）给 B 账户（10 块）转了 10 块钱，不管成功与否，A 和 B 的总金额都是 110 块。 3）隔离性意味着并发执行的事务是彼此隔离的，一个事务的执行不会被其他事务干扰。就是事务之间是井水不犯河水的。隔离性主要是为了解决事务并发执行时可能出现的问题，如脏读、不可重复读、幻读等。数据库系统通过事务隔离级别（如读未提交、读已提交、可重复读、串行化）来实现事务的隔离性。 4）持久性确保事务一旦提交，它对数据库所做的更改就是永久性的，即使发生系统崩溃，数据库也能恢复到最近一次提交的状态。通常，持久性是通过数据库的恢复和日志机制来实现的，确保提交的事务更改不会丢失。
• ACID靠什么保证 MySQL 通过事务、undo log、redo log 来确保 ACID。 1）MySQL 通过 undo log 来确保原子性（Atomicity）。 当事务开始时，MySQL 会在undo log中记录事务开始前的旧值。如果事务执行失败，MySQL 会使用undo log中的旧值来回滚事务开始前的状态；如果事务执行成功，MySQL 会在某个时间节点将undo log删除。 2）如果其他三个特性都得到了保证，那么一致性（Consistency）就自然而然得到保证了。 3）如何保证隔离性？ MySQL 定义了多种隔离级别，通过 MVCC 来确保每个事务都有专属自己的数据版本，从而实现隔离性（Isolation）。 在 MVCC 中，每行记录都有一个版本号，当事务尝试读取记录时，会根据事务的隔离级别和记录的版本号来决定是否可以读取 4）redo log 是一种物理日志，当执行写操作时，MySQL 会先将更改记录到 redo log 中。当 redo log 填满时，MySQL 再将这些更改写入数据文件中。 如果 MySQL 在写入数据文件时发生崩溃，可以通过 redo log 来恢复数据文件，从而确保持久性（Durability）。

mvcc，也就是多版本并发控制，是一种并发控制的方法，一般用在数据库管理系统中，实现对数据库的并发访问，比如在MySQL InnoDB中主要是为了提高数据库并发性能，不用加锁，非阻塞并发读。
数据库并发有以下几种场景：
读-读：不存在任何问题。
读-写：有线程安全问题，可能出现脏读、幻读、不可重复读。
写-写：有线程安全问题，可能存在更新丢失等。
mvcc解决的就是读写时的线程安全问题，线程不用去争抢读写锁。
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题
mvcc所提到的读是快照读，也就是普通的select语句。快照读在读写时不用加锁，不过可能会读到历史数据。
还有一种读取数据的方式是当前读，是一种悲观锁的操作。它会对当前读取的数据进行加锁，所以读到的数据都是最新的。主要包括以下几种操作：
selectlockin share mode（共享锁）
selectfor update（排他锁）
update（排他锁）
insert（排他锁）
delete（排他锁）

MVCC的实现主要依赖于记录中的三个隐藏字段、undolog，read view来实现的。
1、隐藏字段
每行记录，除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段：
DB_ROW_ID：6字节，隐藏的主键，如果数据表没有主键，那么innodb会自动生成一个6字节的row_id
​DB_TRX_ID：6字节，最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id
DB_ROLL_PTR：7字节，回滚指针，用于配合undo日志，指向上一个旧版本

Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，系统会生成一个此刻的快照，记录并维护系统此刻活跃事务的id，用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取到的是当前行记录的undolog中某个版本的数据

1. 事务隔离级别 1） 读未提交是最低的隔离级别，在这个级别，当前事务可以读取未被其他事务提交的数据，以至于会出现“脏读”、“不可重复读”和“幻读”的问题 不提供任何锁机制来保护读取的数据，允许读取未提交的数据（即脏读）

读已提交和可重复读通过 MVCC 机制中的 ReadView 来实现。

2） 读已提交：当前事务只能读取已经被其他事务提交的数据，可以避免“脏读”现象。但不可重复读和幻读问题仍然存在

READ COMMITTED：每次读取数据前都生成一个 ReadView，保证每次读操作都是最新的数据

。
3）可重复读：确保在同一事务中多次读取相同记录的结果是一致的，即使其他事务对这条记录进行了修改，也不会影响到当前事务。可重复读是 MySQL 默认的隔离级别，避免了“脏读”和“不可重复读”，但可能会出现幻读

REPEATABLE READ：只在第一次读操作时生成一个 ReadView，后续读操作都使用这个

4）串行化： 最高的隔离级别，通过强制事务串行执行来避免并发问题，可以解决“脏读”、“不可重复读”和“幻读”问题。但会导致大量的超时和锁竞争问题

事务在读操作时，必须先加表级共享锁，直到事务结束才释放；事务在写操作时，必须先加表级排他锁，直到事务结束才释放。

。
2. 脏读 幻读 不可重复读
1）脏读指的是一个事务能够读取另一个事务尚未提交的数据。如果读到的数据在之后被回滚了，那么第一个事务读取到的就是无效的数据

-- 事务 A
START TRANSACTION;
UPDATE employees SET salary =5000 WHERE id =1;-- 事务 B
START TRANSACTION;
SELECT salary FROM employees WHERE id =1;-- 读取到 salary =5000(脏读)
ROLLBACK;

2）不可重复读指的是在同一事务中执行相同的查询时，返回的结果集不同。这是由于在事务过程中，另一个事务修改了数据并提交。
比如说事务 A 在第一次读取某个值后，事务 B 修改了这个值并提交，事务 A 再次读取时，发现值已经改变。

-- 事务 A
START TRANSACTION;
SELECT salary FROM employees WHERE id =1;-- 读取到 salary =3000-- 事务 B
START TRANSACTION;
UPDATE employees SET salary =5000 WHERE id =1;
COMMIT;-- 事务 A 再次读取
SELECT salary FROM employees WHERE id =1;-- 读取到 salary =5000(不可重复读)
COMMIT;

3） 幻读指的是在同一事务中执行相同的查询时，返回的结果集中出现了之前没有的数据行。这是因为在事务过程中，另一个事务插入了新的数据并提交。

16. 分库分表

1. 带来的问题 1） 从分库的角度来讲： 事务的问题 使用关系型数据库，有很大一点在于它保证事务完整性。而分库之后单机事务就用不上了，必须使用分布式事务来解决。 跨库 JOIN 问题 在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库了之后就无法使用 JOIN 了。此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码来关联得到最终的结果。还有可以适当的冗余一些字段，这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。还有一种方式就是数据异构，通过 binlog 同步等方式，把需要跨库 join 的数据异构到 ES 等存储结构中，通过 ES 进行查询。 2）从分表的角度来看： 跨节点的 count,order by,group by 以及聚合函数问题 只能由业务代码来实现或者用中间件将各表中的数据汇总、排序、分页然后返回。 数据迁移，容量规划，扩容等问题 数据的迁移，容量如何规划，未来是否可能再次需要扩容，等等，都是需要考虑的问题。 ID 问题 数据库表被切分后，不能再依赖数据库自身的主键生成机制，所以需要一些手段来保证全局主键唯一。 还是自增，只不过自增步长设置一下。比如现在有三张表，步长设置为 3，三张表 ID 初始值分别是 1、2、3。这样第一张表的 ID 增长是 1、4、7。第二张表是 2、5、8。第三张表是 3、6、9，这样就不会重复了。 UUID，这种最简单，但是不连续的主键插入会导致严重的页分裂，性能比较差。 分布式 ID，比较出名的就是 Twitter 开源的 sonwflake 雪花算法
2. 百万级别的数据删除 我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的 IO,会降低增/改/删的执行效率。删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引，然后删除其中无用数据，删除完成后重新创建索引也非常快
3. 百万千万级大表如何添加字段？ 大表添加字段，通常有这些做法： 1） 通过中间表转换过去 创建一个临时的新表，把旧表的结构完全复制过去，添加字段，再把旧表数据复制过去，删除旧表，新表命名为旧表的名称，这种方式可能回丢掉一些数据。 2）用 pt-online-schema-change pt-online-schema-change是 percona 公司开发的一个工具，它可以在线修改表结构，它的原理也是通过中间表。 3）先在从库添加 再进行主从切换 如果一张表数据量大且是热表（读写特别频繁），则可以考虑先在从库添加，再进行主从切换，切换后再将其他几个节点上添加字段。
4. MySQL 数据库 cpu 飙升 排查过程： （1）使用 top 命令观察，确定是 mysqld 导致还是其他原因。 （2）如果是 mysqld 导致的，show processlist，查看 session 情况，确定是不是有消耗资源的 sql 在运行。 （3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。 处理： （1）kill 掉这些线程 (同时观察 cpu 使用率是否下降)， （2）进行相应的调整 (比如说加索引、改 sql、改内存参数) （3）重新跑这些 SQL。
5. 先插入数据再创建索引：在没有索引的情况下插入数据，数据库不需要在每次插入时更新索引，这会减少插入操作的开销。之后一次性创建索引通常比逐条记录更新索引更快。 先创建索引再插入数据：这种情况下，数据库需要在每次插入新记录时维护索引结构，随着数据量的增加，索引的维护可能会导致额外的性能开销。
6. 数据库是先建立索引还是先插入数据？ 在 InnoDB 中，如果表定义了主键，那么主键索引就是聚簇索引。如果没有明确指定主键，InnoDB 会自动选择一个唯一索引作为聚簇索引。如果表没有任何唯一索引，InnoDB 将自动生成一个隐藏的行 ID 作为聚簇索引。 这意味着当插入新数据时，InnoDB 首先将数据插入到聚簇索引中。这一步骤实质上是创建索引的一部分，因为数据存放在索引结构中。 对于非主键的其他索引（次级索引），在插入数据到聚簇索引后，InnoDB 还需要更新表的所有次级索引。这些索引中的每一个都包含指向聚簇索引记录的指针。 所以在 InnoDB 中，数据插入和索引创建（更新）是密不可分的。从数据库的视角看，插入操作包括向聚簇索引添加记录和更新所有相关的次级索引。这些步骤在一个事务中原子地执行，以确保数据的一致性和完整性。 深分页指的是在分页查询中，请求的页数非常大，例如请求第 100 万页的数据。在这种情况下，数据库需要跳过前 999999 页的数据，会消耗大量的 CPU 和 I/O 资源，导致查询性能下降。