[MYSQL[快问快答系列]](https://learnku.com/articles/75347)

4 核 16GB 内存 1TB 固态硬盘，请给我 mysql8.0 的最优配置 my.cnf
[mysqld]

基本设置

port = 3306
user = mysql
default_storage_engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

日志文件设置

log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

缓存设置

thread_cache_size = 128
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache_instances = 64
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 4M

InnoDB存储引擎设置

innodb_buffer_pool_size = 12G //缓冲池大小
innodb_log_file_size = 2G //事务日志文件大小
innodb_flush_log_at_trx_commit = 1 //2为异步,有风险;1为同步,性能较差
innodb_file_per_table = 1 //每个InnoDB表创建单独的数据文件。
innodb_flush_method = O_DIRECT //指定InnoDB使用的磁盘刷新方法。O_DIRECT参数表示数据被直接写入磁盘而不是被缓存到操作系统缓存中。
innodb_io_capacity = 2000 //指定InnoDB磁盘IO容量的预估值。
innodb_io_capacity_max = 4000 //指定InnoDB磁盘IO容量的上限值。

线程和连接设置

max_connections = 500 //允许的最大客户端连接数
max_user_connections = 1000 //每个mysql用户允许的最大连接数。如果没有单独设置，则使用全局max_connections值。
thread_stack = 512K //线程分配的堆栈空间
thread_cache_size = 128 //MySQL线程缓存的大小。
wait_timeout = 600 //连接在没有活动操作时被关闭之前的等待时间。
interactive_timeout = 600
connect_timeout = 60

其他设置

character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
执行一条 select 语句，期间发生了什么？
Mysql 客户端与 Mysql 服务端通过三次握手建立 TCP 连接
验证账号密码
查询缓存，如果命中直接返回 (MySQL 8.0 版本直接将查询缓存删掉了)
解析器阶段，进行词法分析，生成语法树
预处理阶段，检查 SQL 语句中的表或者字段是否存在，将 select 中的 符号扩展为表上的所有列
优化器阶段，基于查询成本的考虑，决定使用什么索引
执行器阶段，开始与存储引擎交互，从存储引擎读取记录，返回给客户端
如何查看 MySQL 服务被多少个客户端连接了？
show processlist;
空闲连接会一直占用着吗？
MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880 秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

MySQL 的连接数有限制吗？
最大连接数由 max_connections 参数控制，超过这个值，系统就会拒绝接下来的连接请求，并报错提示 “Too many connections”。

怎么解决长连接占用内存的问题？
定期断开长连接
客户端主动重置连接
explain 命令
possible_keys 字段表示可能用到的索引
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度，根据 key_len 长度来分析联合索引使用了几个
rows 表示扫描的数据行数。
type 表示数据扫描类型
extra 额外信息
type 执行效率从低到高的顺序为:

All 全表扫描
index 全索引扫描 比全表扫描好一些，好处是不用再排序
range 索引范围扫描 尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式
ref 非唯一索引扫描
eq_ref 主键或唯一索引扫描，通常使用在多表联查中
const 结果只有一条的主键或唯一索引扫描
extra:

覆盖索引 Extra:Using index 效率不错
索引下推 Extra:Using index condition
文件排序 Extra:Using filesort 常见于 group by 操作 效率低
临时表 Extra:Using temporary 常见于 group by/order by 操作 效率低
查询一次数据最多需要多少次 IO
B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4 次

什么是回表
先检二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫回表，也就是说要查两个 B+Tree 才能查到数据。

什么是覆盖索引
在二级索引的 B+Tree 就能查询到结果的过程就叫作覆盖索引，不需要回表。

什么是索引下推
MySQL 5.6 引入的索引下推优化， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

字符集 utf8,utf8mb4,ascii 一个字符分别占用多少字节？
utf8 3 个
utf8mb4 4 个 允许存储 emoji 表情
ascii 1 个
怎么提高这条 SQL 效率
select * from order where status = 1 order by create_time asc
建立 status,create_time 联合索引，避免文件排序

什么时候使用索引
字段有唯一性限制的
经常用于 WHERE 查询条件的字段
经常用于 GROUP BY 和 ORDER BY 的字段
什么时候不使用索引
WHERE 条件，GROUP BY，ORDER BY 里用不到的字段
字段中存在大量重复数据，不需要创建索引
表数据太少的时候，不需要创建索引
经常更新的字段不用创建索引
索引列为什么最好设置为 Not Null
索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化
NULL 值是一个没意义的值，但是它会占用物理空间，至少每行都会用 1 字节空间存储 NULL 值列表
索引什么时候失效
like % xx 或者 like % xx%
对索引列做了计算、函数、类型转换操作
没有按照最左优先的方式进行索引的匹配
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列
Innodb 为什么选择 B+tree
Hash
Hash 在做等值查询的时候效率贼快，搜索复杂度为 O (1)。
但是 Hash 表不适合做范围查询，它更适合做等值的查询
二叉树
数据量越大，二叉树高越高，磁盘 IO 越多，B+Tree 再千万级别的数据量下只用 3-4 层
B Tree
相同数据量时，B+Tree 树高比 BTree 低，磁盘 IO 次数更少
B+Tree 叶子节点用双向链表串起来，适合范围查询，BTree 无法做到这点
如何添加测试数据
CREATE TABLE person(

id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键',
person_id tinyint not null comment '用户id',
person_name VARCHAR(200) comment '用户名称',
gmt_create datetime comment '创建时间',
gmt_modified datetime comment '修改时间'

) comment '人员信息表';
添加一条数据

insert into person values(1, 1,'user_1', NOW(), now());
以下 sql 执行 20 次 约等于 100 万条数据执行 24 次 1600 万条数据

INSERT INTO person ( person_id, person_name, gmt_create, gmt_modified ) SELECT LEFT
( rand()* 10, 10 ) AS person_id,
concat( 'user_', LEFT ( rand()* 10, 10 ) % 2048 ),
date_add( gmt_create, INTERVAL + @i cast( rand() 100 AS signed ) SECOND ),
date_add(

date_add( gmt_modified, INTERVAL + @i * cast( rand()* 100 AS signed ) SECOND ),
INTERVAL + cast( rand()* 1000000 AS signed ) SECOND 

)
FROM

person;

MySQL 单表为什么最好不要超过 2000W
超过了这个值可能会导致 B + 树层级更高，影响查询性能。

MySQL 使用 like “% x“，索引一定会失效吗？
不一定，如果扫描了二级索引就能得到结果，就不用回表，直接返回数据，这个时候 explain 的 type 是 index

count (*) 和 count (1) 有什么区别？哪个性能最好？
count() 执行过程跟 count (1) 执行过程基本一样的，性能没有什么差异。而且 MySQL 会对 count () 和 count (1) 有个优化，如果有多个二级索引的时候，优化器会使用 key_len 最小的二级索引进行扫描。只有当没有二级索引的时候，才会采用主键索引来进行统计。

如何优化 count (*)？
近似值
explain select count(*) from t_order
会返回一个近似值 rows
额外表保存计数值
插入一条记录的同时，将计数表字段 + 1, 新增和删除的时候需要额外维护这个计数器，存在 redis 也行
事务有哪些特性？
原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成
一致性（Consistency）：事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
隔离性（Isolation）：每个事务都有一个完整的数据空间，对其他并发事务是隔离的。
持久性（Durability）：事务处理结束后，对数据的修改就是永久的
InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？
持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志） 来保证的；
隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
一致性则是通过持久性 + 原子性 + 隔离性来保证；
事务的隔离级别有哪些？
读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；
MySQL InnoDB 怎么避免幻读现象？
针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁 + 间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
快照读和当前读有什么区别？
MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据。
另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？
在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

MySQL 死锁了，怎么办？
设置事务等待锁的超时时间，当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。
分析产生死锁的业务逻辑，考虑怎么去掉 select …for update 导致产生的死锁
为什么需要 undo log？
实现事务回滚，保障事务的原子性。。
实现 MVCC（多版本并发控制）关键因素之一。
为什么需要 缓冲池 Buffer Pool？
当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘
Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。
查询一条记录，就只需要缓冲一条记录吗？
不对，当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中

为什么需要 redo log ？
为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来
当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

redo log 和 undo log 区别在哪？
redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；
事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务，如下图：
redo log 什么时候刷盘？
MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）。
为什么需要 binlog ？
binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

redo log 和 binlog 有什么区别？
binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用，用于备份恢复、主从复制；
redo log 是 Innodb 存储引擎实现的日志，用于掉电等故障恢复。
如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？
不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

主从复制是怎么实现？
写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。
从库是不是越多越好？
不是的。
因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。
所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

为什么需要两阶段提交？
MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务

MySQL 磁盘 I/O 很高，有什么优化的方法？
设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。这个方法是基于 “额外的故意等待” 来实现的，因此可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早被写入到 page cache 了，只要系统没有宕机，缓存在 page cache 里的 binlog 就会被持久化到磁盘。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「 redo log 文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。
你知道 mysql 主从延迟吗？有些时候延迟的时间还会很长。遇到这种情况咋么办？
多线程复制
确保使用了多线程复制，并且已经按照手册查看了如何调整各种选项以从中获得最佳效率。
调整 binlog 格式
调整为 statement 减小 binlog 容量
临时降低持久化要求
可以临时设置
sync_binlog=0,
innodb_flush_log_at_trx_commit=0
以提高复制速度。
怎么定位运行中的 MySQL 使用哪个配置文件

which mysqld
/usr/sbin/mysqld
/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
为什么不建议字段格式为 null
如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。

储存财务数据，BIGINT 还是 DECIMAL
假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万然后将结果存储在 BIGINT 里，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题。

VARCHAR 和 CHAR 的区别
VARCHAR
VARCHAR 需要额外使用 1 或 2 字节在每行数据前记录字符串的长度：如果最大长度 <=255 字节，则只使用 1 字节表示，否则使用 2 字节。VARCHAR 节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致 InnoDB 需要分割页面来容纳行，InnoDB 将过长的 VARCHAR 值存储为 BLOB

CHAR
当存储 CHAR 值时，MySQL 删除所有尾随空格。如果需要进行比较，值会用空格填充。CHAR 适合存储用户密码的 MD5 值。对于经常修改的数据，CHAR 也比 VARCHAR 更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR 也比 VARCHAR 更高效；设计为只保存 Y 和 N 的值的 CHAR（1）在单字节字符集 [1] 中只使用 1 字节，但 VARCHAR（1）需要 2 字节

如何查询各个数据表储存大小

DATETIME 还是 TIMESTAMP
DATETIME
可以保存大范围的数值，从 1000 年到 9999 年，精度为 1 微秒。它以 YYYYMMDDHHMMSS 格式存储压缩成整数的日期和时间，且与时区无关。这需要 8 字节的存储空间。
TIMESTAMP
存储自 1970 年 1 月 1 日格林尼治标准时间（GMT）午夜以来经过的秒数，只使用 4 字节的存储空间，只能表示从 1970 年到 2038 年 1 月 19 日，MySQL 提供 FROM_UNIXTIME () 函数来将 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP () 函数将日期转换为 UNIX 时间戳。
结论
使用带符号的 32 位 INT，可以表达直到 2038 年的时间。使用无符号的 32 位 INT，可以表达直到 2106 年的时间。如果使用 64 位，还可以超出这些范围。这一做法越来越流行。
可以对保存 md5 (),sha1 (),uuid () 的列做索引吗？
这些函数生成的新值会任意分布在很大的空间内，这会减慢
INSERT 和某些类型的 SELECT 查询的速度

因为插入的值会写到索引的随机位置，所以会使得 INSERT 查询变慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
SELECT 查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内存中。
IPV4 地址用什么数据类型？
用过函数转成 int 然后用 unsigned int 存储

MySQL schema 设计中的有什么骚操作 (错误示范)?
太多的列
MySQL 的存储引擎 API 通过在服务器和存储引擎之间以行缓冲区格式复制行来工作，InnoDB 的行格式总是需要转换的。这种转换的成本取决于列数。
太多的联接
MySQL 限制每个联接有 61 个表
全能的枚举

如何不停机不锁表地更新表结构？
pt-online-schema-change: 原理是使用触发器，对已经使用触发器的表不太兼容
gh-ost: 原理是使用 binlog, 对使用外键的表不太兼容
怎么选择
如果你正在运行许多已存在外键的旧数据库，并且删除外键是很困难的，那么会发现 pt-online-schema-change 已经尝试对外键提供更广泛的支持，但你必须承担为数据完整性和正常运行时间选择最安全选项的认知负担。此外，gh-ost 利用 binlog 来完成其工作，因此如果由于某种原因无法访问这些日志，pt-online-schema-change 仍然是一个可行的选项。
INNODB 自适应哈希索引特性？
当 InnoDB 发现某些索引值被非常频繁地被访问时，它会在原有的 B-tree 索引之上，在内存中再构建一个哈希索引。这就可以实现非常快速的哈希查找。这个过程是完全自动化的，用户可以通过参数关闭这个特性。

如何减少索引和数据的碎片
通过执行 OPTIMIZE TABLE 或者导出再导入的方式来重新整理数
据。

联接查询怎么优化？
确保 ON 或者 USING 子句中的列上有索引。
确保任何 GROUP BY 和 ORDER BY 中的表达式只涉及一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程。
怎么优化大表 limit offset

错误示范

select id,description from film order by title limit 50, 5

正确示范

select id,description from film inner join (select id from film order by title limit 50,5) as lim using (id)
复制如何工作
主库把数据更改记录到 binlog
从库启动线程 1 把主库的 binlog 复制到自己的中继日志
从库启动线程 2 重放中继日志
binlog_format 有多少种日志格式？
STATEMENT
表示在 binlog 文件中只记录 SQL 语句。这是默认值，优点是简单紧凑容量小，其最大的弊端则在于会遇到某些具有 “不确定性” 的 SQL 语句问题
ROW
表示在 binlog 文件中只记录受影响的行。在这种格式下，MySQL 会将对数据的更改记录为行级别的操作。这种格式可以更准确地记录数据更改，但会导致 binlog 文件更大。
MIXED
表示在 binlog 文件中既记录 SQL 语句，又记录受影响的行。MySQL 会自动选择适当的格式来记录数据更改。这是最常用的格式，因为它可以平衡可读性和性能。
结论
我们 (高性能 MYSQL 第四版) 建议坚持使用基于行的复制 ROW，除非某些场景下明确需要临时使用基于语句的复制。基于行的复制提供了最安全的数据复制方法。
怎么保证崩溃后的复制安全

保证每个undo log都被同步地写到磁盘,增加磁盘写入操作频次,确保数据持久性

innodb_flush_log_at_trx_commit=1

每次事务执行的时候都会把二进制日志同步写入磁盘。增加磁盘写入操作频次,防止丢失事务

sync_binlog=1

使用 InnoDB 存储引擎存储中继日志信息,提供更好的可靠性和性能。

relay_log_info_repository=TABLE

在复制过程中发生错误时自动尝试使用中继日志进行恢复

relay_log_recovery=ON
延迟复制有什么用
想象一下这样的场景，你正在处理大量数据，突然意外地做了一些变更：删除了一个表。从备份中恢复可能需要几个小时。如果使用了延迟复制的副本，则可以找到 DROP TABLE 语句对应的 GTID，使副本服务器的复制运行到表被删除之前的时间点，这会大大减少修复时间。

什么是多线程复制？
最新的 MySQL 版本则提供了多线程复制能力，可以在副本端运行多个 SQL 线程，从而加快本地中继日志的应用。

什么是半同步复制？
在半同步复制中，主服务器会等待至少一个从服务器将更新成功复制后，才会返回客户端。确保至少一个从服务器成功复制了更新，降低故障恢复的成本，缺点是会影响主服务器的性能