MySQL语句类型有哪些？

1. DDL（数据定义语言）：定义和管理数据库对象，如CREATE、ALTER、DROP等
2. DML（数据操作语言）：操作数据库中的数据，如INSERT、UPDATE、DELETE等
3. DQL（数据查询语言）：查询数据库数据，如SELECT等
4. DCL（数据控制语言）：控制数据库访问权限，如GRANT、REVOKE等
5. TCL（事务控制语言）：如COMMIT、ROLLBACK等

CHAR和VARCHAR的区别？

1. CHAR和VARCHAR类型在存储和检索方面有所不同
2. CHAR列长度固定为创建表时声明的长度，长度值范围是1到255

当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。

主键索引与唯一索引的区别

1. 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

2. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

3. 唯一性索引列允许空值，而主键列不允许为空值。

4. 主键列在创建时，已经默认为空值 ++ 唯一索引了。

5. 一个表最多只能创建一个主键，但可以创建多个唯一索引。

6. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

7. 主键可以被其他表引用为外键，而唯一索引不能。

MySQL中有哪些不同的表格？

• MyISAM

• Heap

• Merge

• INNODB

• ISAM

MyISAM和InnoDB的区别是什么

1. 事务支持：MyISAM不支持事务，InnoDB支持事务
2. 锁机制：MyISAM使用表级锁，InnoDB使用行级锁
3. 索引结构：MyISAM的索引和数据分离，InnoDB的主键索引是聚集索引
4. 缓存：MyISAM只缓存索引，InnoDB缓存索引和数据
5. 外键支持：MyISAM不支持外键，InnoDB支持外键
6. 崩溃恢复：MyISAM崩溃后恢复困难，InnoDB支持崩溃恢复
7. 读写性能：MyISAM对读数据支持非常好，写操作时会锁定整个表
8. 资源占用：MyISAM对系统资源占用小
9. 并发支持：MyISAM读写阻塞互斥，不支持多用户并发写操作

什么是辅助索引和聚集索引？

• 辅助索引：也称为非聚簇索引，叶子节点存储的是主键值而非数据行，查询时需要先找到主键值，再通过主键索引查找数据行
• 聚集索引：也称为主键索引，数据行实际存储在索引的叶子节点，InnoDB引擎特有，查询效率高

主从复制的原理

1. 主库将数据变更记录到二进制日志（binlog）中
2. 从库的IO线程连接到主库，读取主库的binlog日志并写入到从库的中继日志（relay log）中
3. 从库的SQL线程读取中继日志，解析出SQL语句并执行，从而实现数据同步

SQL的生命周期？

1. 连接器：应用服务器与数据库服务器建立一个连接，验证账号密码和主机权限
2. 查询缓存（8.0+取消）：检查SQL是否有缓存，有则直接返回数据没有进入下一步
3. 解析器：检测SQL语句是否正确，构建解析树，如SQL错误直接返回错误信息
4. 查询优化器：优化器决定使用哪个索引，生成一个最优的执行计划
5. 执行器：将优化后的SQL语句转换为可执行的操作序列，生成具体的执行计划树
6. 存储引擎：InnoDB缓冲池(Buffer Pool)或磁盘查找数据
7. 结果返回：格式化查询结果，通过网络协议返回给客户端
8. 日志阶段：
   1. 写入undo log：记录修改前的数据，用于回滚
   2. 写入redo log：确保事务持久性
   3. binlog记录（如果启用）：用于复制和时间点恢复
9. 资源清理：关掉连接，释放资源

数据备份的类型

1. 按备份方法分类：热备、冷备、温备
2. 按备份内容分类：物理备份、逻辑备份
3. 按备份策略分类：完全备份、增量备份、差异备份

MySQL数据库为什么使用B+树而不是B树

1. 更低的树高，减少磁盘I/O次数：B+树非叶节点仅存储索引键（不存数据指针），单节点可容纳更多键值相同数据量下树高比B树更低
2. 范围查询效率碾压B树：B+树叶节点通过双向链表串联，执行BETWEEN、>、<等范围查询时直接顺序遍历链表获取后续数据（无需回溯非叶节点），比B树快5-10倍（B树需反复访问非叶节点）
3. 查询性能稳定可控：B+树所有数据均在叶子节点，任何查询都需走到叶子层 时间复杂度稳定为O(log n)
4. 充分利用磁盘预读机制：磁盘每次读取1页（通常4KB/16KB），B+树叶节点数据物理连续存储，预读（Prefetching）可批量加载相邻节点 顺序I/O吞吐量比B树高30%+
5. 空间利用率更高：B+树非叶节点无数据指针（仅索引键），相同内存缓存更多索引层级缓存命中率提升，B树非叶节点存储数据指针20-30%空间浪费
6. 全表扫描更高效：只需遍历叶节点链表即可获取全部数据，B树需递归访问所有层级节点I/O路径复杂且冗余

NOTE

关键结论：B+树通过 "去数据化的非叶节点" + "链表化叶节点" 设计，在磁盘型存储系统中实现：

• 更低树高（减少随机I/O）
• 更强顺序访问（优化范围查询）
• 更稳时间复杂度（保障OLTP性能

数据库三大范式是什么

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

怎么优化SQL查询语句

1. 索引优化：对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2. 避免 SELECT \*：SELECT子句中避免使用*号，只返回必要的字段
3. 用 LIMIT分页：限制结果集大小（SELECT ... LIMIT 100）
4. 模糊查询慎用 %开头：LIKE '%apple%'无法走索引 → LIKE 'apple%'可触发索引
5. 隐式类型转换：字符串列用数字查询（WHERE id = '100'）会导致索引失效
6. 用 JOIN替代子查询：嵌套子查询 → 改用 INNER JOIN（减少临时表生成）
7. EXISTS替代 IN：EXISTS在匹配到结果后立即终止扫描，效率更高
8. 减少排序分组：避免非必要的 ORDER BY；若必须排序，确保排序字段有索引
9. 使用 EXPLAIN诊断：关注type字段避免 ALL（全表扫描），至少达到 range（范围扫描），Extra字段警惕 Using filesort（文件排序）、Using temporary（临时表）
10. 启用慢查询日志：记录执行超阈值（如 1 秒）的 SQL

什么是覆盖索引、回表

• 覆盖索引： 查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

• 回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

MySQL数据库cpu飙升的话，要怎么处理呢？

排查过程

1. 使用top 命令观察，确定是MySQLd导致还是其他原因。
2. 如果是MySQLd导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。
3. 找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理

1. kill 掉这些线程(同时观察 cpu 使用率是否下降)，

2. 进行相应的调整(比如说加索引、改 sql、改内存参数)

3. 重新跑这些 SQL。

其他情况

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

Innodb的事务与日志的实现方式

日志的存放形式

1. redo log：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。
2. Undo log：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata之外。

事务是如何通过日志来实现的

1. 因为事务在修改页时，要先记 undo log，在记 undo log之前要记 undo log 的 redo log， 然后修改数据页，再记数据页修改的 redo log。 Redo log（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。
2. 当事务需要回滚时，因为有 undo log，可以把数据页回滚到前镜像的 状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo log把该事务的修改回滚到事务开始之前。
3. 如果有 commit 记录，就用 redo log 前滚到该事务完成时并提交掉。

非聚簇索引一定会回表查询吗？

不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（哈哈，覆盖索引就是这么回事）。

举个简单的例子，假设我们在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理：

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

1. hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
2. 因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
3. hash索引不支持使用索引进行排序，原理同上。
4. hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
5. hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
6. hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
7. 因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

select for update有什么含义，会锁表还是锁行还是其他。

select for update 含义

select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁呢，而且它是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是用了索引/主键啦。

没用索引/主键的话就是表锁，否则就是是行锁。

select for update 加锁验证

表结构：

//id 为主键，name为唯一索引 CREATE TABLE account ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, balance int(11) DEFAULT NULL, PRIMARY KEY (id), KEY idx_name (name) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8

id为主键，select for update 1270070这条记录时，再开一个事务对该记录更新，发现更新阻塞啦，其实是加锁了。如下图：

我们再开一个事务对另外一条记录1270071更新，发现更新成功，因此，如果查询条件用了索引/主键，会加行锁~

我们继续一路向北吧，换普通字段balance吧，发现又阻塞了。因此，没用索引/主键的话，select for update加的就是表锁

索引的数据结构（b树，hash）

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

B树索引

MySQL通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。B树索引是MySQL数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，MySQL一律打印BTREE，所以简称为B树索引）

查询方式：

1. 主键索引区:PI(关联保存的时数据的地址)按主键查询,
2. 普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度最快

B+tree性质：

1. n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
2. 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3. 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
4. B+ 树中，数据对象的插入和删除仅在叶节点上进行。
5. B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

哈希索引

简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在MySQL中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

最左匹配原则？

在创建联合索引时候，一般需要遵循最左匹配原则。即联合索引中的属性识别度最高的放在查询语句的最前面。

对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

1.索引的目的是什么？

• 快速访问数据表中的特定信息，提高检索速度

• 创建唯一性索引，保证数据库表中每一行数据的唯一性。

• 加速表和表之间的连接

• 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

2.索引对数据库系统的负面影响是什么？

创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

3.为数据表建立索引的原则有哪些？

• 在最频繁使用的、用以缩小查询范围的字段上建立索引。

• 在频繁使用的、需要排序的字段上建立索引

4.什么情况下不宜建立索引？

• 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

• 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。。

myisamchk是用来做什么的？

它用来压缩MyISAM表，这减少了磁盘或内存使用。

说说分库与分表的设计

分库分表方案，分库分表中间件，分库分表可能遇到的问题

分库分表方案:

1. 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
2. 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中
3. 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中
4. 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

常用的分库分表中间件：

1. sharding-jdbc（当当）
2. Mycat
3. TDDL（淘宝）
4. Oceanus(58同城数据库中间件)
5. vitess（谷歌开发的数据库中间件）
6. Atlas(Qihoo 360)

分库分表可能遇到的问题

1. 事务问题：需要用分布式事务啦
2. 跨节点Join的问题：解决这一问题可以分两次查询实现
3. 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
4. 数据迁移，容量规划，扩容等问题
5. ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID
6. 跨分片的排序分页问题（后台加大pagesize处理？）

什么情况下设置了索引但无法使用

1. 以“%”开头的LIKE语句，模糊匹配
2. OR语句前后没有同时使用索引
3. 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

如何删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name; alter table user_index drop KEY id_card; alter table user_index drop KEY information;

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）

需要取消自增长再行删除：

alter table user_index -- 重新定义字段 MODIFY id int, drop PRIMARY KEY

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

什么是数据库连接池?为什么需要数据库连接池呢?

连接池基本原理：

数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。

应用程序和数据库建立连接的过程：

1. 通过TCP协议的三次握手和数据库服务器建立连接
2. 发送数据库用户账号密码，等待数据库验证用户身份
3. 完成身份验证后，系统可以提交SQL语句到数据库执行
4. 把连接关闭，TCP四次挥手告别。

数据库连接池好处：

1. 资源重用 (连接复用)
2. 更快的系统响应速度
3. 新的资源分配手段
4. 统一的连接管理，避免数据库连接泄漏

列对比运算符是什么？

在SELECT语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR或LIKE运算符。

按照锁的粒度分，数据库锁有哪些呢？锁机制与InnoDB锁算法

• 按锁粒度分有：表锁，页锁，行锁

• 按锁机制分有：乐观锁，悲观锁

LIKE声明中的％和_是什么意思？

• ％对应于0个或更多字符，_只是LIKE语句中的一个字符

• 如何在Unix和MySQL时间戳之间进行转换

• UNIX_TIMESTAMP是从MySQL时间戳转换为Unix时间戳的命令

• FROM_UNIXTIME是从Unix时间戳转换为MySQL时间戳的命令

如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

执行计划包含的信息 id 有一组数字组成。表示一个查询中各个子查询的执行顺序;

1、 id相同执行顺序由上至下。

2、 id不同，id值越大优先级越高，越先被执行。

3、 id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type 每个子查询的查询类型，一些常见的查询类型。

table 查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id partitions 表分区、表创建的时候可以指定通过那个列进行表分区。 举个例子：

create table tmp ( id int unsigned not null AUTO_INCREMENT, name varchar(255), PRIMARY KEY (id) ) engine = innodb partition by key (id) partitions 5;

type(非常重要，可以看到有没有走索引) 访问类型

1、 ALL 扫描全表数据

2、 index 遍历索引

3、 range 索引范围查找

4、 index_subquery 在子查询中使用 ref

5、 unique_subquery 在子查询中使用 eq_ref

6、 ref_or_null 对Null进行索引的优化的 ref

7、 fulltext 使用全文索引

8、 ref 使用非唯一索引查找数据

9、 eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

10、 possible_keys 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

11、 key 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

12、 TIPS:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

13、 key_length 索引长度

14、 ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

15、 rows 返回估算的结果集数目，并不是一个准确的值。

16、 extra 的信息非常丰富，常见的有：

17、 Using index 使用覆盖索引

18、 Using where 使用了用where子句来过滤结果集

19、 Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。

20、 Using temporary 使用了临时表 sql优化的目标可以参考阿里开发手册

推荐

SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好

说明：

1、 consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

2、 ref 指的是使用普通的索引（normal index）。

3、 range 对索引进行范围检索。

反例：

explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

UNION与UNION ALL的区别？

1. Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
2. Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
3. UNION的效率高于 UNION ALL

索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理很简单，就是把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

• 事务A持有id=1锁，等待id=2锁

• 事务B持有id=2锁，等待id=1锁

解决死锁思路，一般就是切断环路，尽量避免并发形成环路。

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
4. 如果业务处理不好可以用分布式事务锁或者使用乐观锁
5. 死锁与索引密不可分，解决索引问题，需要合理优化你的索引，

SQL 约束有哪几种呢？

1. NOT NULL: 约束字段的内容一定不能为NULL。

2. UNIQUE: 约束字段唯一性，一个表允许有多个 Unique 约束。

3. PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。

4. FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键。

5. CHECK: 用于控制字段的值范围。

创建索引的三种方式

在执行CREATE TABLE时创建索引

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用ALTER TABLE命令添加索引

ALTER TABLE table_name ADD INDEX index_name (column);

使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column);

为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

说一下 MySQL 执行一条查询语句的内部执行过程？

答：MySQL 执行一条查询的流程如下：

1. 客户端先通过连接器连接到 MySQL 服务器；
2. 连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器；
3. 分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器；
4. 优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好；
5. 优化器执行完就进入执行器，执行器则开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

MySQL 查询缓存有什么优缺点？

答：MySQL 查询缓存功能是在连接器之后发生的，它的优点是效率高，如果已经有缓存则会直接返回结果。 查询缓存的缺点是失效太频繁导致缓存命中率比较低，任何更新表操作都会清空查询缓存，因此导致查询缓存非常容易失效。

MySQL 的常用引擎都有哪些？

答：MySQL 的常用引擎有 InnoDB、MyISAM、Memory 等，从 MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。

什么叫回表查询？

答：普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了？

答：不是，如果把主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的rowid 作为主键。

一张自增表中有三条数据，删除两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几？

答：无论是MySQL 8还是MySQL 5.7，在删除数据后重启数据库再新增数据时，新数据的ID不会被删除的记录影响，而是继续按照自增序列规则生成。

什么是独立表空间和共享表空间？它们的区别是什么？

答：共享表空间指的是数据库的所有表数据，索引文件全部放在一个文件中，默认 这个共享表空间的文件路径在 data 目录下。

独立表空间：每一个表都将会生成以独立的文件方式来进行存储。共享表空间和独立表空间最大的区别是如果把表放再共享表空间，即使表删除了空间也不会删除，因此表依然很大，而独立表空间如果删除表就会清除空间。

清空表的所有数据性能最好的语句是？

A：delete from t

B：delete t

C：drop table t

D：truncate table t 答：D

题目解析：truncate 清除表数据不会写日志，delete 要写日志，因此 truncate 的 效率要高于 delete。

唯一索引和普通索引哪个性能更好？

答：唯一索引和普通索引的性能对比分为以下两种情况：

• 对于查询来说两者都是从索引树进行查询，性能几乎没有任何区别；
• 对于更新操作来说，因为唯一索引需要先将数据读取到内存，然后需要判断是否有冲突，因此比唯一索引要多了判断操作，从而性能就比普通索引性能要低。

left join 和 right join 的区别是什么？

• left join 和 right join 的区别如下：

• left join（左联结），返回左表全部记录和右表联结字段相等的记录；

• right join（右联结），返回右表全部记录和左表联结字段相等的记录。

什么是最左匹配原则？它的生效原则有哪些？

答：最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，指的是索 引以最左边为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、 between、like）就会停止匹配。 生效原则来看以下示例，比如表中有一个联合索 引字段 index(a,b,c)：

• where a=1 只使用了索引 a；
• where a=1 and b=2 只使用了索引 a,b；
• where a=1 and b=2 and c=3 使用 a,b,c；
• where b=1 or where c=1 不使用索引；
• where a=1 and c=3 只使用了索引 a；
• where a=3 and b like ‘xx%’ and c=3 只使用了索引 a,b。

以下 or 查询有什么问题吗？该如何优化？

select * from t where num=10 or num=20;

答：如果使用 or 查询会使 MySQL 放弃索引而全表扫描，可以改为：

select * from t where num=10
union
select * from t where num=20;

事务是什么？它有什么特性？

答：事务是一系列的数据库操作，是数据库应用的基本单位。

在 MySQL 中只有 InnoDB 引擎支持事务，它的四个特性如下：

• 原子性（Atomic），要么全部执行，要么全部不执行；

• 一致性（Consistency），事务的执行使得数据库从一种正确状态转化为另一种正确状态；

• 隔离性（Isolation），在事务正确提交之前，不允许把该事务对数据的任何改变提供给其他事务；

• 持久性（Durability），事务提交后，其结果永久保存在数据库中。

MySQL 中有几种事务隔离级别？分别是什么？

答：MySQL 中有四种事务隔离级别，分别是：

• read uncommited，未提交读，读到未提交数据；

• read committed，读已提交，也叫不可重复读，两次读取到的数据不一致；

• repetable read，可重复读；

• serializable，串行化，读写数据都会锁住整张表，数据操作不会出错，但并发性能极低，开发中很少用到。

MySQL 默认使用 repetable read 的事务隔离级别。

如何设置 MySQL 的事务隔离级别？

答：MySQL 事务隔离级别 mysql.cnf 文件里设置的（默认目录 /etc/my.cnf）， 在文件的文末添加配置：

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLEREAD、SERIALIZABLE。

MySQL 出现了中文乱码该如何解决？

答：解决 MySQL 中文乱码的问题，可以设置全局编码或设置某个数据库或表的编码为 utf8。 设置全局编码：

set character_set_client='utf8';
set character_set_connection='utf8';
set character_set_results='utf8';

设置数据库的编码：

alter database db character set utf8;

设置表的编码：

alter table t character set utf8;

InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？

答：因为 B 树、Hash、红黑树或二叉树存在以下问题。

• B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。
• Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高。
• 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高。
• 红黑树：树的高度随着数据量增加而增加，IO 代价高。

MySQL 是如何处理死锁？

答：MySQL 对待死锁常见的两种策略：

• 通过 innodb_lock_wait_timeout 来设置超时时间，一直等待直到超时；

• 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其他事务继续执行。

什么是全局锁？它的应用场景有哪些？

答：全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全量逻辑备份，这个时候整个库会处于完全的只读状态。

使用全局锁会导致什么问题？

答：使用全局锁会使整个系统不能执行更新操作，所有的更新业务会出于等待状态；如果你是在从库进行备份，则会导致主从同步严重延迟。

InnoDB 存储引擎有几种锁算法？

答：InnoDB 的锁算法包括以下三种：

• Record Lock — 单个行记录上的锁；

• Gap Lock — 间隙锁，锁定一个范围，不包括记录本身；

• Next-Key Lock — 锁定一个范围，包括记录本身。

InnoDB 如何实现行锁？

答：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则 InnoDB 将使用表锁。使用 for update 来实现行锁，具体脚本如下：

select * from t where id=1 for update

其中 id 字段必须有索引。

MySQL 性能指标都有哪些？如何得到这些指标？

答：MySQL 最重要的性能指标有以下两个：

• QPS（Queries Per Second），每秒查询数，一台数据库每秒能够处理的查询次数；

• TPS（Transactions Per Second），每秒处理事务数。

这些性能指标可以通过 show status 来查询当前数据库状态的结果信息中估算出 来，show status 会有 300 多条状态信息记录，其中以下这些信息 QPS 和 TPS 有关系：

• Uptime，服务器已经运行的时间，单位秒；

• Questions，已经发送给数据库查询数；

• Com_select，查询次数，实际查询次数；

• Com_insert，插入次数；

• Com_delete，删除次数；

• Com_update，更新次数；

• Com_commit，事务次数；

• Com_rollback，回滚次数。

MySQL 中的重要日志分为哪几个？

① 错误日志：用来记录 MySQL 服务器运行过程中的错误信息，比如，无法加载 MySQL 数据库的数据文件，或权限不正确等都会被记录在此，还有复制环境下， 从服务器进程的信息也会被记录进错误日志。默认情况下，错误日志是开启的，且 无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中，名称为 hostname.err，其中 hostname 为服务器主机名。在 MySQL 5.5.7 之前，数据库 管理员可以删除很长时间之前的错误日志，以节省服务器上的硬盘空间， MySQL 5.5.7 之后，服务器将关闭此项功能，只能使用重命名原来的错误日志文件，手动 冲洗日志创建一个新的，命令为：

mv hostname.err hostname.err.old
mysqladmin flush-logs

② 查询日志：查询日志在 MySQL 中被称为 general log（通用日志），查询日 志里的内容不要被“查询日志”误导，认为里面只存储 select 语句，其实不然，查 询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体 原因如下:

• insert 查询为了避免数据冲突，如果此前插入过数据，则当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；

• update 时也会查询因为更新的时候很可能会更新某一块数据；

• delete 查询，只删除符合条件的数据；

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都 记录下来会导致 IO 非常大，影响 MySQL 性能。因此如果不是在调试环境下， 是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句 对应的数据是否能够被缓存，同时也可以帮助我们分析问题，因此，可以根据自己 的实际情况来决定是否开启查询日志。

查询日志模式是关闭的，可以通过以下命令开启查询日志：

set global general_log=1
set global log_output='table';

general_log=1 为开启查询日志，0 为关闭查询日志，这个设置命令即时生效，不 用重启 MySQL 服务器。

③ 慢日志：慢查询会导致 CPU、IOPS、内存消耗过高，当数据库遇到性能瓶颈 时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让 MySQL 记录 下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库 性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录 到慢查询日志中。使用如下命令记录当前数据库的慢查询语句：

set global slow_query_log='ON';

使用 set global slow_query_log=‘ON’ 开启慢查询日志，只是对当前数据库有效， 如果 MySQL 数据库重启后就会失效。因此如果要永久生效，就要修改配置文件 my.cnf，设置 slow_query_log=1 并重启 MySQL 服务器。

④ redo log（重做日志）：为了最大程度的避免数据写入时，因为 IO 瓶颈造成 的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把 内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故 障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。

⑤ undo log（回滚日志）：用于存储日志被修改前的值，从而保证如果修改出现 异常，可以使用 undo log 日志来实现回滚操作。

undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以 从 undo log 中的逻辑记录读取到相应的内容并进行回滚。undo log 默认存放在 共享表空间中，在 ySQL 5.6 中，undo log 的存放位置还可以通过变量 innodb_undo_directory 来自定义存放目录，默认值为“.”表示 datadir 目录。

⑥ bin log（二进制日志）：是一个二进制文件，主要记录所有数据库表结构变 更，比如，CREATE、ALTER TABLE 等，以及表数据修改，比如，INSERT、 UPDATE、DELETE 的所有操作，bin log 中记录了对 MySQL 数据库执行更改的所 有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是它 不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。 binlog 的作用如下：

• 恢复（recovery）：某些数据的恢复需要二进制日志。比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复；

• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或者 standby）与一台 MySQL数据库（一般称为 master 或者 primary）进行实时同步；

• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

除了上面介绍的几个作用外，binlog 对于事务存储引擎的崩溃恢复也有非常重要 的作用，在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL 将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内 部的状态可能为 prepared（准备状态）和 commit（提交状态）两种，对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog，如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在， 那么将其回滚，这样就保证了数据在主库和从库之间的一致性。 binlog 默认是关闭状态，可以在 MySQL 配置文件（my.cnf）中通过配置参数 log-bin = [base-name] 开启记录 binlog 日志，如果不指定 base-name，则默认 二进制日志文件名为主机名，并以自增的数字作为后缀，比如：mysqlbin.000001，所在目录为数据库所在目录（datadir）。 通过以下命令来查询 binlog 是否开启：

show variables like 'log_%';
binlog 格式分为 STATEMENT、ROW 和 MIXED 三种。

• STATEMENT 格式的 binlog 记录的是数据库上执行的原生 SQL 语句。这种格式的优点是简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，因此相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间，并且通过 mysqlbinlog 工具容易读懂其中的内容。缺点就是同一条 SQL 在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL，比如，使用 INSERT INTO TB1VALUE(CUURENT_DATE()) 这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化，存储过程和触发器在使用基于语句的复制模式时也可能存在问题；另外一个问题就是基于语句的复制必须是串行化的，比如，InnoDB 的 next-key 锁等，并不是所有的存储引擎都支持基于语句的复制。

• ROW 格式是从 MySQL 5.1 开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据，一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的 SQL 构造、触发器、存储过程等都能正确执行；它的缺点就是二进制日志可能会很大，而且不直观，因此，你不能使用mysqlbinlog 来查看二进制日志，也无法通过看二进制日志判断当前执行到那一条 SQL 语句。现在对于 ROW 格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点，并且由于 ROW 格式记录行数据，因此可以基于这种模式做一些 DBA 工具，比如数据恢复，不同数据库之间数据同步等。

• MIXED 也是 MySQL 默认使用的二进制日志记录方式，但 MIXED 格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到 UUID()、USER()、CURRENT_USER()、ROW_COUNT() 等无法确定的函数。

redo log 和 binlog 有什么区别？

redo log（重做日志）：

1. 特定于InnoDB存储引擎，它是InnoDB内部的一种日志机制，用来保证事务的持久性和数据库的崩溃恢复。
2. redo log 记录的是对数据库页的物理更改，即在事务提交前，将对数据库所做的更改以“redo record”的形式先行记录到redo log buffer中，随后按照一定策略将其刷新到磁盘上的redo log文件。
3. redo log采用WAL（Write-Ahead Logging）机制，即先写日志再写磁盘，这样即使数据库发生异常重启，也可以通过重做（redo）这些日志来恢复未完成的事务，确保事务的ACID中的D（Durability持久性）特性。

binlog（二进制日志）：

1. binlog是MySQL Server层实现的，不仅适用于InnoDB引擎，对于其他支持的存储引擎（如MyISAM以前版本）也是通用的。
2. binlog记录的是逻辑层面的数据库更改，即每条对数据库有修改作用的SQL语句的原始文本或其事件表示形式。
3. binlog主要用于数据备份与恢复（如基于binlog的逻辑备份工具如mysqlbinlog和主从复制），以及审计、数据迁移等场景。在主从复制中，从服务器通过读取并执行主服务器的binlog来保持数据一致性。

总结来说，redo log主要服务于存储引擎层面的崩溃恢复，保证事务的持久性；而binlog则更多地应用在跨服务器间的数据同步和数据恢复场景，记录的是更高级别的逻辑操作。两者相辅相成，共同保证了MySQL数据库的数据完整性和一致性。

慢查询日志的获取方式有哪些？

答：慢查询日志的常见获取方式如下。

• 使用 MySQL 自带功能，开启慢查询日志，在 MySQL 的安装目录下找到my.cnf 文件设置 slow-query-log=On 开启慢查询，慢查询默认时长为10s，默认存储文件名为 host_name-slow.log。
• 使用三方开源方案 zabbix，zabbix 是一个基于 Web 界面的提供分布式系统监视以及网络监视功能的企业级的开源解决方案，能监视各种网络参数，保证服务器系统的安全运营；并提供灵活的通知机制以让系统管理员快速定位/解决存在的各种问题。

如何定位慢查询？

答：使用 MySQL 中的 explain 分析执行语句，比如：

explain select * from t where id=5;

• id — 选择标识符，id 越大优先级越高，越先被执行

• select_type — 表示查询的类型。

• table — 输出结果集的表

• partitions — 匹配的分区

• type — 表示表的连接类型

• possible_keys — 表示查询时，可能使用的索引

• key — 表示实际使用的索引

• key_len — 索引字段的长度

• ref— 列与索引的比较

• rows — 大概估算的行数

• filtered — 按表条件过滤的行百分比

• Extra — 执行情况的描述和说明

其中最重要的就是 type 字段，type 值类型如下：

• all — 扫描全表数据
• index — 遍历索引
• range — 索引范围查找
• index_subquery — 在子查询中使用 ref
• unique_subquery — 在子查询中使用 eq_ref
• ref_or_null — 对 null 进行索引的优化的 ref
• fulltext — 使用全文索引
• ref — 使用非唯一索引查找数据
• eq_ref — 在 join 查询中使用主键或唯一索引关联
• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点

MySQL 中常见的读写分离方案有哪些？

答：MySQL 中常见的读写分离方案通常为以下两种：

• 使用 MySQL 官方提供的数据库代理产品 MySql ProxySQL 搭建自动分配的数据库读写分离环境；

• 在程序层面配置多数据源使用代码实现读写分离。

怎样保证主备数据库无延迟？

答：通常保证主备数据库无延迟有以下三种方法。

• 每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求，seconds_behind_master 参数是用来衡量主备延迟时间的长短。

• 对比位点确保主备无延迟。Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点，Relay_Master_Log_File 和Exec_Master_Log_Pos，表示的是备库执行的最新位点。

• 对比 GTID 集合确保主备无延迟。Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。

什么是 MySQL 多实例，如何配置 MySQL 多实例？

答：MySQL 多实例就是在同一台服务器上启用多个 MySQL 服务，它们监听不同的端口，运行多个服务进程，它们相互独立，互不影响的对外提供服务，便于节约服务器资源与后期架构扩展。 多实例的配置方法有两种：

• 一个实例一个配置文件，不同端口；
• 同一配置文件（my.cnf）下配置不同实例，基于 MySQL 的 d_multi 工 具。

表的优化策略有哪些？

「参考答案」常见的大表优化策略如下。

• 读写分离，主库负责写，从库负责读。
• 垂直分区，根据数据属性单独拆表甚至单独拆库。
• 水平分区，保持表结构不变，根据策略存储数据分片，这样每一片数据被分散到不同的表或者库中。水平拆分只是解决了单一表数据过大的问题，表数据还在同一台机器上，对于并发能力没有什么意义，因此水平拆分最好分库。另外分片事务难以解决，跨节点 join 性能较差。

数据库分片方案有哪些？

「参考答案」数据库分片方案有哪些？

答：数据库创建的分片方案有两种方式：

客户端代理方式和中间件代理方式。

• 客户端代理 — 分片逻辑在应用端，封装在 jar 包中，通过修改或者封装JDBC 层来实现，比如 Sharding-JDBC、阿里 TDDL 等。
• 中间件代理 — 在应用层和数据层中间加了一个代理层。分片逻辑统一维护在中间件服务中，比如 MyCat、网易的 DDB 都是中间件代理的典型代表。

查询语句的优化方案有哪些？

「参考答案」常见优化方案如下：

• 不做列运算，把计算都放入各个业务系统实现；

• 查询语句尽可能简单，大语句拆小语句，减少锁时间；

• 不使用 select * 查询；

• or 查询改写成 in 查询；

• 不用函数和触发器；

• 避免 %xx 查询；

• 少用 join 查询；

• 使用同类型比较，比如 ‘123’ 和 ‘123’、123 和 123；

• 尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描；

• 列表数据使用分页查询，每页数据量不要太大。

MySQL 毫无规律的异常重启，可能产生的原因是什么？该如何解决？

「参考答案」可能是积累的长连接导致内存占用太多，被系统强行杀掉导致的异常重启，因为在 MySQL 中长连接在执行过程中使用的临时内存对象，只有在连接断开的时候才会释放，这就会导致内存不断飙升，解决方案如下：

• 定期断开空闲的长连接；

• 如果是用的是 MySQL 5.7 以上的版本，可以定期执行

mysql_reset_connection 重新初始化连接资源，这个过程会释放之前使用的内存资源，恢复到连接刚初始化的状态。

MySQL主从复制延迟时间大，都有哪些可能的原因及解决办法？

1. 主库的从库太多，导致复制延迟 解决：一般一个主库五个从库最多，不可再添加从库
2. 从库的硬件条件比主库差，导致复制延迟 解决：提升从库硬件条件
3. 慢SQL语句太多 解决：开启从库的慢查询日志，会记录执行时间超过指定时间的SQL语句，告诉开发，让开发去更新优化语句
4. 主从复制的设计问题，从库的SQL线程为单线程，只能一条一条处理并发过来的SQL语句，所以会有延迟 解决：更新版本，mysql5.6版本之后从库的SQL线程为多线程，大大降低了延迟
5. 主从库之间的网络延迟 解决：优化网络
6. 主库读写压力大，导致复制的时候就产生了延迟 解决：优化主库性能，如读写分离
7. MySQL主从复制延迟时间查看参数：show slave status显示参数Seconds_Behind_Master不为0
8. 输入命令临时跳过一条sql线程的解析的命令：set global sql_slave_skip_counter=1;

MySQL 5.7 与 8.0 的核心区别

查询功能增强

• 窗口函数：8.0 支持 ROW_NUMBER()、RANK()、LAG() 等，简化复杂分析查询；5.7 需嵌套子查询实现。
• CTE（公共表表达式）：8.0 支持递归和非递归 CTE，提升查询可读性；5.7 不支持。
• JSON 支持：8.0 增强 JSON_TABLE()、->> 操作符及部分更新；5.7 仅基础 JSON 操作。

1. 索引优化
   • 降序索引：8.0 直接支持 ORDER BY ... DESC 的索引优化；5.7 需文件排序。
   • 隐藏索引：8.0 可临时禁用索引（ALTER INDEX ... INVISIBLE），避免删除重建；5.7 不支持。
   • 函数索引：8.0 支持基于表达式创建索引（如 UPPER(c2)）；5.7 不支持。
2. 安全机制
   • 身份验证：8.0 默认使用 caching_sha2_password 插件；5.7 使用 mysql_native_password，旧客户端需适配。
   • 角色管理：8.0 支持角色权限分配（CREATE ROLE）；5.7 需手动授权。
   • 密码策略：8.0 强制密码复杂度检查、过期策略及历史记录；5.7 功能较弱。
3. 数据字典与存储
   • 原子 DDL：8.0 确保 DDL 操作完全成功或回滚；5.7 可能部分失败。
   • 自增列持久化：8.0 重启后自增值不重置；5.7 可能重置导致主键冲突。
   • 元数据存储：8.0 将系统表移至 InnoDB（mysql.ibd）；5.7 使用 MyISAM 和 .frm 文件。
4. 字符集与性能
   • 默认字符集：8.0 改用 utf8mb4（支持表情符号）；5.7 默认 latin1。
   • 并行查询：8.0 支持全表扫描并行化（配置 innodb_parallel_read_threads）；5.7 不支持。
   • 资源组：8.0 可限制 CPU 和线程优先级（CREATE RESOURCE GROUP）；5.7 无此功能。
5. 事务与高可用
   • 锁控制：8.0 支持 SELECT ... FOR UPDATE NOWAIT/SKIP LOCKED 跳过锁等待；5.7 仅等待超时。
   • 备份锁：8.0 引入 LOCK INSTANCE FOR BACKUP，减少备份阻塞；5.7 无此功能。
   • Group Replication：8.0 优化多主模式性能；5.7 并行复制较弱。

⚠️ 版本支持：MySQL 5.7 已于 2023 年 10 月结束标准支持，8.0 为当前推荐版本。 🔧 升级需测试客户端兼容性、SQL 保留字冲突及执行计划变化。

MySQL性能优化

服务器硬件优化

• 选择适当的硬件：根据业务需求选择合适的CPU、内存、硬盘和网络设备。
• 使用SSD：固态硬盘能显著提高I/O性能。
• 足够的RAM：确保服务器有足够的内存来缓存数据和索引。

操作系统优化

• 调整内核参数：如vm.swappiness、innodb_buffer_pool_instances等，以减少磁盘I/O。
• 文件系统选择：使用适合数据库应用的文件系统，如XFS或EXT4。
• 禁用不必要的服务：减少系统负载。

配置参数优化

• 内存相关参数：如query_cache_size（查询缓存）、innodb_buffer_pool_size（InnoDB缓冲池大小）、key_buffer_size（MyISAM键缓存大小）。
• 并发相关参数：如max_connections（最大连接数）、innodb_thread_concurrency（InnoDB并发线程数）。
• 日志和临时表参数：如innodb_log_file_size（InnoDB日志文件大小）、tmp_table_size和max_heap_table_size（临时表大小）。

数据库架构优化

• 合理设计表结构：避免过度的范式化，合理使用索引。
• 分区表：对于大表，可以使用分区来提高查询性能。
• 分库分表：水平拆分或垂直拆分，以减轻单点的压力。

SQL查询优化

• 避免全表扫描：使用索引来优化查询。
• 合理使用JOIN：避免复杂的JOIN操作，尤其是多表JOIN。
• 优化子查询：尽量使用关联查询代替子查询。
• 使用EXPLAIN分析查询：分析查询执行计划，找出性能瓶颈。

索引优化

• 创建合适的索引：根据查询模式创建索引，考虑索引选择性。
• 索引维护：定期分析和优化索引，避免索引碎片。
• 覆盖索引：使用覆盖索引来减少数据访问。

锁和事务优化

• 减少锁等待：优化事务逻辑，减少长时间持有锁的情况。
• 使用乐观锁：在适当的情况下使用乐观锁代替悲观锁。
• 事务隔离级别：根据业务需求选择合适的事务隔离级别。

二进制日志和复制优化

• 合理设置二进制日志：根据备份和恢复的需求设置二进制日志的大小和格式。
• 主从复制优化：优化复制延迟，合理设置复制过滤规则。

监控和分析工具

• 使用监控工具：如MySQL Enterprise Monitor、Percona Monitoring and Management等，实时监控数据库性能。
• 慢查询日志：开启慢查询日志，定期分析慢查询并进行优化。
• 第三方监控工具：
  • 监控服务：如Newrelic, Datadog等提供的MySQL监控插件。
  • 开源工具：如Prometheus+Grafana进行MySQL性能监控，pt-query-digest处理慢日志。

应用程序级别的优化

• 连接池：使用连接池来复用数据库连接。
• 批处理：批量插入、更新和删除操作，减少网络往返次数。
• 缓存机制：合理使用缓存来减少数据库访问。