面试：mysql问题整理

青鱼入云

已于 2025-08-14 23:19:10 修改

阅读量310

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql Java面试文章标签： mysql

于 2020-02-19 19:55:46 首次发布

本文链接：https://blog.youkuaiyun.com/u011305680/article/details/79459671

Java面试同时被 2 个专栏收录

102 篇文章

订阅专栏

mysql

33 篇文章

订阅专栏

文章目录

Mysql基础

Mysql简介

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。
MySQL支持两种存储引擎：InnoDB、MyIsam。
MySQL支持索引、事务、集群、分库分表、主从复制、读写分离、持久化等能力

InnoDB与MyIsam区别与使用。

InnoDB：可以使用事务、外键、使用聚簇索引、不支持全文索引、不保存具体行数。
MyIsam：不支持事务、无外键、非聚簇索引、支持全文索引、用一个变量保存了整个表的行数。
具体详见我的另一篇博客：MyIsam与InnoDB的区别与使用选择

InnoDB索引的数据结构。

B+树，参考链接：B+树介绍
B+树查找过程，B+树的性质，参考链接：mysql索引原理

为什么选择B+树作为mysql的存储结构

首先看B+树的特点，和与其他二叉查找树、B树的区别，所以在大规模数据存储和实现索引查询的场景中，使用B+树有极大的优势。
1.B+树的数据都存储在叶子节点中，分支节点均为索引，方便扫库，只需要扫描一遍叶子节点即可。
2.所有叶子节点都在同一层，每个节点到叶子节点的高度都相同，查询时每个查询都是稳定的。
3.叶子节点可以做双向链表，从而提高范围查找的效率。
4. 红黑树、二叉树高度太高，导致查询效率低下；B+树可以拥有多个子节点，降低树的高度。
5. 磁盘预读原理：将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

SQL语句执行过程

在这里插入图片描述

①通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限
②Mysql8.0之前开看是否开启缓存，开启了 Query Cache 且命中完全相同的 SQL 语句，则将查询结果直接返回给客户端；MySQL在得到一个执行请求后，会首先去查询缓存中查找，是否执行过这条 SQL语句，之前执行过的语句以及结果会以 key-value 对的形式，被直接放在内存中。key 是查询语句，value 是查询的结果。
③由解析器进行语法语义解析，并生成解析树。如查询是select、表名tb_student、条件是id=‘1’
④查询优化器生成执行计划。根据索引看看是否可以优化
⑤查询执行引擎执行 SQL 语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。

MySQL 有关权限的表有哪几个呢？

MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 MySQL 数据库里，由 MySQL_install_db 脚本初始化。这些权限表分别 user，db，table_priv， columns_priv 和 host。

user 权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。 2、 db 权限表：记录各个帐号在各个数据库上的操作权限。
table_priv 权限表：记录数据表级的操作权限。
columns_priv 权限表：记录数据列级的操作权限。
host 权限表：配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和REVOKE语句的影响。

数据库三大范式是什么

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

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

char 和 varchar 的区别？

CHAR和VARCHAR的区别可以总结如下：

存储方式：CHAR是固定长度的字符类型，而VARCHAR是可变长度的字符类型。
占用空间：CHAR会以固定的长度存储数据，不论实际存储的字符数目，而VARCHAR则根据实际需要的空间动态分配存储。
尾随空格：CHAR在存储时会用空格填充到指定长度，并在检索时需要删除尾随空格，而VARCHAR没有这个问题。
长度限制：CHAR的长度范围为1到255个字符，而VARCHAR的长度范围也是255个字符，但可以根据需求设定更长的长度。
访问效率：由于CHAR是固定长度的，它在某些情况下可能会比VARCHAR稍微快一些。

综上所述，CHAR适合存储长度固定且固定大小的数据，而VARCHAR适用于长度可变的数据。

什么叫外链接？

外连接分为三种，分别是是左外连接(LEFT OUTER JOIN 或 LEFT JOIN 右外连接(RIGHT OUTER JOIN 或 RIGHT JOIN、全外连接(FULL OUTER JOIN 或 FULL JOIN)。
左外连接:又称为左连接，这种连接方式会显示左表不符合条件的数据行，右边不符合条件的数据行直接显示NULL。
右外连接:也被称为右连接，他与左连接相对，这种连接方式会显示右表不符合条件的数据行，左表不符合条件的数据行直接显示 NULL。

什么叫内链接？

INNER JOIN - 结合两个表中相同的字段，返回关联字段相符的记录就是内链接。

mysql 中in 和exists 的区别

当谈到 MySQL 中的 IN 和 EXISTS 时，它们都是用于查询和筛选数据的条件语句，但有一些重要的区别：

IN关键字：使用 IN 条件时，我们提供一个固定的值列表，然后将其与指定列的值进行比较。如果列中的值与列表中的任何一个值匹配，就会返回结果。IN 条件适合用于确定某个字段的值是否在给定的值列表中。
例如，SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);
如果 column_name 的值与 value1、value2 或 value3 中的任何一个相匹配，那么这条记录将会被返回。
EXISTS关键字：使用 EXISTS 条件时，我们需要指定一个子查询。查询的结果并不重要，而是判断子查询是否返回了至少一行结果。如果子查询返回了结果，EXISTS 条件就会被认为是满足的。EXISTS 条件适合用于判断某个条件是否至少存在于子查询的结果中。
例如，SELECT * FROM table_name WHERE EXISTS (SELECT * FROM another_table WHERE condition);
如果子查询(SELECT * FROM another_table WHERE condition)返回了至少一行结果，那么主查询中的记录将会被返回。

总结来说：

使用 IN 条件时，比较的是指定列的值是否在给定的值列表中。
使用 EXISTS 条件时，判断的是子查询是否返回了至少一行结果。

count(1)、count(*) 与 count(列名) 的区别？

在SQL查询中，COUNT(1)、COUNT(*)和COUNT(列名)是用于计算行数的聚合函数，但它们在实际应用时有一些区别。

COUNT(1)：这种写法中，1表示一个常量值，它会被用于计算查询结果集的行数。由于1是一个常量，在执行COUNT(1)时，数据库不会去访问或读取任何实际的数据，仅仅是对满足条件的行进行计数，因此执行速度相对较快。
COUNT(*)：这种写法中，表示选取所有列，它会对满足条件的行进行计数。与COUNT(1)不同的是，执行COUNT()时，数据库需要读取每一行的数据，然后进行计数操作，因此它可能会比COUNT(1)稍微慢一些。不过，在大多数数据库中，优化器会对COUNT(*)进行特殊处理，可以通过索引等方式进行优化，使得性能相对较好。
COUNT(列名)：这种写法中，列名表示具体的表列，它会对非空（NULL）值的行进行计数。相比于COUNT(1)和COUNT(*)，COUNT(列名)会跳过值为NULL的行，只统计非空值的行数。这在某些特定的情况下可能更符合实际需求，例如统计某个列的非空值个数。

总体来说，COUNT(1)和COUNT(*)的性能较好且结果一致，而COUNT(列名)则对非空值进行计数。在实际使用时，可以根据具体的查询需求和性能要求选择适当的写法。

UNION 与UNION ALL 的区别？

UNION和UNION ALL是在SQL中用于合并查询结果集的操作符，它们之间存在以下区别：

UNION：UNION用于合并两个或多个查询结果集，并去除重复的行。它将多个查询的结果合并为一个结果集，并自动去除重复的行。在执行UNION操作时，数据库会进行额外的去重操作，这可能会带来一定的性能开销。
UNION ALL：UNION ALL同样用于合并查询结果集，但不去除重复的行。它将多个查询的结果简单地合并在一起，包括重复的行。相比于UNION，UNION ALL不进行去重操作，因此执行效率更高。

总结来说：在使用时，可以根据具体的需求来选择合适的操作符。如果需要去除重复的行，可以使用UNION；如果不需要去重，或者对性能要求较高，可以使用UNION ALL。需要注意的是，使用UNION或UNION ALL时，要求被合并的查询结果的列数和列类型保持一致。

Mysql进阶

同样是查询复杂度O(logN)，为什么mysql选择用B+树，而redis选择用跳表

B+树是多叉平衡搜索树，扇出高，只需要3层左右就能存放2kw左右的数据，同样情况下跳表则需要24层左右，假设层高对应磁盘IO，那么B+树的读性能会比跳表要好，因此mysql选了B+树做索引。
redis的读写全在内存里进行操作，不涉及磁盘IO，同时跳表实现简单，相比B+树、AVL树、少了旋转树结构的开销，因此redis使用跳表来实现ZSET，而不是树结构。
存储引擎RocksDB内部使用了跳表，对比使用B+树的innodb，虽然写性能更好，但读性能属实差了些。在读多写少的场景下，B+树依旧YYDS。
参考：Mysql的索引为什么使用B+树而不使用跳表？
B站讲解视频：【mysql索引为什么选择B+树而不是跳表？】

MySQL 的 Binlog 有有几种录入格式？分别有什么区别？

有三种格式，statement，row和 mixed。

statement 模式下，每一条会修改数据的 sql 都会记录在 binlog 中。不需要记录每一行的变化，减少了binlog 日志量，节约了 IO，提高性能。由于 sql 的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row级别下，不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如 alter table)，因此这种模式的文件保存的信息太多，日志量太大。
mixed，一种折中的方案，普通操作使用 statement 记录，当无法使用 statement 的时候使用 row。

什么是临时表，何时删除临时表？

什么是临时表?MySQL在执行 SQL语句的过程中通常会临时创建一些存储中间结果集的表，临时表只对当前连接可⻅，在连接关闭时，临时表会被删除并释放所有表空间。
临时表分为两种:一种是内存临时表，一种是磁盘临时表，什么区别呢?内存临时表使用的是 MEMORY存储引擎，而临时表采用的是 MylSAM 存储引擎。
MySQL会在下面这几种情况产生临时表。

使用 UNION查询:UNION有两种，一种是 UNION，一种是 UNION ALL，它们都用于联合查询;区别是使用UNION会去掉两个表中的重复数据，相当于对结果集做了一下去重(distinct)。使用 UNIONALL，则不会排重，返回所有的行。使用 UNION查询会产生临时表。
使用 TEMPTABLE算法或者是 UNION查询中的视图。TEMPTABLE算法是一种创建临时表的算法，它是将结果放置到临时表中，意味这要 MySQL要先创建好一个临时表，然后将结果放到临时表中去，然后再使用这个临时表进行相应的查询。
ORDER BY和 GROUP BY的子句不一样时也会产生临时表。
DISTINCT 查询并且加上 ORDER BY时;
SQL中用到 SQL_SMALL_RESULT选项时；如果查询结果比较小的时候，可以加上 SQL SMALL RESULT来优化，产生临时表
FROM中的子查询;
EXPLAIN 查看执行计划结果的 Extra 列中，如果使用 Using Temporary 就表示会用到临时表。

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

首先，我们要明白数据库连接池是一种管理和维护数据库连接的技术。它在应用程序和数据库之间建立了一个连接的缓冲池，用于存储和复用已经建立好的数据库连接。
那为什么需要数据库连接池呢？

提高性能：数据库连接的建立和断开是比较耗时的操作，频繁地创建和销毁连接会增加系统的负担。通过使用连接池，可以避免频繁地创建和关闭连接，减少了连接的开销，提高了系统的性能。
资源管理：数据库连接是有限的资源，如果每个请求都创建一个新的连接，可能导致连接过多而耗尽资源。连接池通过对连接的管理和复用，能够更有效地管理数据库连接，避免资源的浪费。
并发处理：在高并发的场景下，如果每个请求都去单独连接数据库，可能会导致数据库连接数量过多，从而限制了系统的扩展性。连接池允许多个请求共享连接，减少了数据库连接的数量，提高了并发处理能力。
连接可靠性：数据库连接可能会因为网络问题或服务器故障而中断，当发生这种情况时，连接池能够检测到连接的失效，并重新创建一个可用的连接，确保应用程序的可靠运行。

总结来说：数据库连接池是为了提高性能、更有效地管理资源、提高并发处理能力和增加连接的可靠性而存在的。它通过缓存已经建立好的连接，复用连接以及管理连接的生命周期，从而减少了连接的开销，提高了系统的性能和稳定性。

MySQL 记录货币用什么字段类型

在MySQL中记录货币金额，一般推荐使用DECIMAL字段类型。
DECIMAL字段类型用于存储精确的定点数值，可以指定总共的位数和小数点后的位数。这使得它非常适合用于存储货币金额，因为货币金额通常需要精确到小数点后几位。
以下是一个示例创建DECIMAL字段类型的语句：

CREATE TABLE my_table (
   amount DECIMAL(18, 2)
);

上述语句创建了一个名为amount的DECIMAL字段，总共有18位，其中小数点后有2位。
使用DECIMAL字段类型的好处包括：

精确性：DECIMAL字段类型可以确保货币金额的精确性，避免由于浮点数运算带来的精度问题。
可控性：通过指定总位数和小数位数，可以精确控制存储的金额范围和精度。
计算准确性：DECIMAL字段类型支持数值计算，如加法、减法和乘法等，保证计算结果的准确性。

需要注意的是，DECIMAL字段类型占用的存储空间相对较大，因此在设计表结构时需要考虑存储和性能需求，合理选择DECIMAL字段的位数。另外，应根据具体业务需求和国际化要求，考虑货币符号和货币转换等问题。

自增主键会遇到什么问题

使用数据库的自增主键，虽然在很多情况下都很方便，但也可能遇到一些问题：

插入性能问题：对于非常高并发的插入操作，自增主键可能会成为性能瓶颈。因为每次插入新记录时，都需要获取一个新的自增ID，这个操作是串行的，无法并发执行。
主键耗尽：如果表的记录非常多，可能会出现自增主键耗尽的情况。尤其是对于定义为整型的自增主键，如果插入的记录数超过了整型的最大值，就无法再插入新的记录。
分布式系统问题：在分布式系统中，如果多个数据库节点都需要生成自增主键，就需要保证生成的主键在全局是唯一的。这通常需要引入额外的机制或工具，比如分布式ID生成器。
数据迁移问题：以自增主键作为外键的表，在做数据扩容、数据迁移时，可能会面临新旧表id不一致问题。这种情况尽量以业务定义的唯一标识为外键。

mysql删除数据或大字段数据更新减少之后，内存何时释放

mysql在删除数据记录后，并不会立即释放存储空间，mysql会将删除的记录放入回滚日志undolog，并将记录的存储空间标记为可重用，当此表有新数据写入的时候，mysql会利用这些已删除的空间再写入。这样做的目的是为了保证数据的一致性和可回滚性。
磁盘空间的释放是由Mysql的垃圾回收机制负责的，它会定期清理undolog，并释放删除记录占用的存储空间，回滚日志的清理过程是自动执行的，Mysql会根据一定的策略来决定何时进行回滚日志的清理。

可以使用 optimize table 命令来重新利用未使用的空间并整理数据文件的碎片
使用alter table tab_name ENGINE InnoDB;的方式，但是这个会先见一个临时表，要关注空间是否够用。
将表结构和数据导出，整个表删除，然后再重建。

Mysql索引

什么是索引？索引有哪些优缺点？

索引是数据库中用于提高数据检索性能的排好序的数据结构。它类似于书籍的目录，通过建立特定的数据结构将列或多个列的值与它们在数据表中对应的行关联起来，以加快查询速度。
索引的优点包括：

提高查询性能：索引可以加快数据库查找数据的速度，通过快速定位到符合查询条件的数据行，减少了数据库进行全表扫描的开销，从而显著提高查询效率。
唯一性约束：通过在索引上设置唯一性约束，可以确保数据的唯一性，防止重复数据的插入。

然而，索引也有一些缺点：

占用存储空间：索引通常需要占用一定的磁盘空间。过多的索引可能会增加存储成本。
索引维护的开销：当对数据表进行插入、更新或删除操作时，索引也需要进行相应的维护操作，这可能导致数据写入的性能下降，更新缓慢。

因此，在设计数据库时，需要根据具体的查询需求、数据特点和系统环境来决定是否以及如何建立索引，以平衡查询性能和维护成本。

索引的类型

索引分为聚簇索引与非聚簇索引：
聚簇索引：InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需求找主键索引的二级索引，先找到主键索引再通过主键索引找数据；
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（辅助索引）。
在这两大索引类型下，还可以分为四个小类：

普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。
唯一索引：索引的列值必须唯一，但允许为空值。
全文索引：全文索引（FULLTEXT）仅可以试用与MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。
组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

创建索引时需要注意什么？

在创建索引时，需要注意以下几点：

选择适当的列：根据实际查询需求和数据特点，选择合适的列作为索引列。通常情况下，选择经常被用于查询条件、排序或聚合操作的列作为索引列可以获得更好的性能。
避免过多的索引：过多的索引会增加存储空间的开销，并且在数据写入时需要维护多个索引结构，导致性能下降。因此，只创建必要的索引，避免不必要的冗余索引。
考虑联合索引：对于多个列的组合查询条件，可以考虑创建复合索引。复合索引可以覆盖多个列，减少查询时需要访问的索引数量，提高查询效率。
注意索引的顺序：对于多列索引，考虑索引的顺序。将最常用的列放在索引的前面，以提高查询性能。
使用合适的数据类型：选择合适的数据类型可以减小索引的大小，提高索引的效率。尽量避免使用过长或过大的数据类型作为索引列。

综上所述，创建索引需要根据具体的查询需求、数据特点和系统环境来进行权衡和决策。合理创建索引可以提升数据库的性能，但也需要注意索引的维护成本和更新操作的影响。

联合索引的顺序（最左匹配原则）

联合索引遵从最左匹配原则，比如abc三个字段的联合索引，查询条件组合ab、ac、acb等都可以匹配到索引，但没有a不行，例如bc。但是如果遇到b>1这类的范围索引，索引使用就只到a这一列。

联合索引的存储结构是怎样的，怎么实现的最左匹配原则

联合索引的存储结构：联合索引本质是多列值组合构建的B+树，与单列索引的核心差异在于：B+树的“排序键”是索引列的组合顺序（而非单个列）。
以联合索引 idx(a, b, c)（列顺序为a→b→c）为例，其B+树结构特点如下：

叶子节点：存储的是“(a值, b值, c值) + 主键ID”的组合数据，且所有叶子节点按 a值升序 → 同a值下b值升序 → 同a、b值下c值升序 的规则排序（完全遵循索引定义的列顺序）。
非叶子节点：仅存储“(a值, b值, c值)”的组合前缀（用于快速定位叶子节点），同样按上述组合顺序排序。
核心逻辑：联合索引的B+树“以索引列顺序为纲”，先按第一列排序，第一列相同再按第二列，以此类推——相当于把多列值“拼接”成一个“复合排序键”来组织数据。

最左匹配原则的本质是：联合索引的 B + 树仅能通过 “从左到右的连续列” 快速定位数据，跳过左侧列或不连续列会导致索引失效（无法利用 B + 树的有序性）。

Btree与Hash索引的区别与使用。

hash只用于对等操作，具体详见我的另一篇博客：数据库索引系列四：索引算法Hash与BTree的区别

大于小于是否可以走索引

有几种情况下可以走索引：
1.如果数据类型是数字。
2.如果查询sql使用了覆盖索引，即查询结果和查询条件字段一样。
3.如果整表数据量较小。
整体来说就是数据库引擎在判断这个sql会不会使用索引时，会判断如果耗费少，是可以使用索引的，如果耗费很大，比如字段是varchar类型，或者查询结果不是覆盖索引需要回表查，或者表数据太大，则会认为使用索引可能比直接全表查更慢或者没有使用索引的必要，就会不走索引，这个过程主要由查询优化器决定。

索引失效的情况

1. 隐式转换：如果索引字段是varchar类型，但传入的值是数字，没有引号；或者索引字段是int类型，传入的是“100”带引号的字符串；就会导致查询是发生隐式转换，会无法使用到索引。
2. 索引区分度不高：索引区分度不高会导致MySQL放弃使用索引，选择全表扫描，从而导致索引失效。比如“性别”字段上建索引。
3. 复合索引没有遵守最左前缀：复合索引没有遵守最左前缀的原因可能是因为在建立复合索引时，使用的顺序不正确，即最左前缀必须按照创建时设置的顺序出现。如果缺省了中间字段，比如login_name = ‘name’ AND mobile =‘mobile’,那么复合索引将不会被充分使用，只会使用 login_name 部分的索引，而不是完全失效。因此，在建立复合索引时，需要确保最左前缀的匹配顺序正确，以充分利用复合索引的潜力。
4. 不要再索引列上做任何操作：不要在索引列上做任何操作（计算，函数，自动or手动类型转换），会导致索引失效转换为全表扫描
5. 索引中使用了索引字段的范围查询：组合索引中其中某个条件使用了>、<等操作，会导致无法使用到这个字段后面的索引字段；单列索引中如果有范围查询，引擎会基于实际sql解析优化后查询的成本判断是否可以使用索引。
6. 使用了不等于：在使用(<>或!=或not in 或is null或is not null)时无法使用索引会导致全表扫描（新版mysql中有些可以用了）。
7. 通配符（%）使用错误：还是最左匹配原则，like以通配符(%)开头的索引会失效变成全表扫描。
8. or语句前后没有同时使用索引：如果or语句前后没有同时使用索引，那么当左右查询字段只有一个是索引时，该索引失效，只有当or左右查询字段均为索引时，才会生效。
9.优化器选错索引：在使用SQL查询，一些复杂sql会导致优化器误判索引列或者不使用索引，这个时候可以通过强制使用索引来解决问题。

什么时候不要使用索引？

在大多数情况下，使用索引可以提高数据库查询的性能和效率。但也有一些情况下，不使用索引可能更合适：

小型表：对于非常小的表，可能不需要使用索引。因为索引的维护需要额外的存储空间和计算资源，而对于小型表来说，全表扫描的开销可能相对较小。
插入、更新和删除频繁的表：如果表的插入、更新和删除操作非常频繁，而且对查询性能的要求相对较低，则可以考虑不使用索引。因为索引的维护会增加写操作的开销，可能导致性能下降。
查询条件不经常使用：如果某个字段很少用作查询条件，而且表的数据量较小，那么为该字段创建索引可能没有太大的性能提升。
需要频繁更新索引的表：对于频繁进行大量写操作（插入、更新、删除）的表，索引的维护成本可能会超过索引带来的查询性能提升，此时可以考虑不使用索引。

需要注意的是，实际情况还需结合具体的业务需求、数据规模和性能要求来综合考虑。在设计数据库时，综合权衡索引的使用与否，并进行性能测试和优化是非常重要的。

B 树和B+树的区别？

B树和B+树是常用的数据结构，用于在数据库中进行索引操作。它们之间的区别主要有以下几个方面：

数据存储方式：在B树中，每个节点都包含键和对应的值，叶子节点存储了实际的数据记录；而B+树中，只有叶子节点存储了实际的数据记录，非叶子节点只包含键信息和子节点的指针。
数据检索方式：在B树中，由于非叶子节点也存储了数据，所以查询时可以直接在非叶子节点找到对应的数据，具有更短的查询路径；而B+树的所有数据都存储在叶子节点上，只有通过叶子节点才能获取到完整的数据。
范围查询效率：由于B+树的所有数据都存储在叶子节点上，并且叶子节点之间使用链表连接，所以范围查询的效率较高。而在B树中，范围查询需要通过遍历多个层级的节点，效率相对较低。
适用场景：B树适合进行随机读写操作，因为每个节点都包含了数据；而B+树适合进行范围查询和顺序访问，因为数据都存储在叶子节点上，并且叶子节点之间使用链表连接，有利于顺序遍历。

总结来说： B树和B+树在数据存储方式、数据检索方式、范围查询效率以及适用场景方面存在区别。B树适合随机读写操作，而B+树适合范围查询和顺序访问。在实际应用中，根据不同的场景和需求选择合适的树结构可以带来更高效的数据处理和索引操作。

什么是回表

我们可以通过二级索引找到B+树中的叶子结点，但是二级索引的叶子节点的内容并不全，只有索引列的值和主键值。我们需要拿着主键值再去聚簇索引（主键索引）的叶子节点中去拿到完整的用户记录，这个过程叫做回表。

什么是覆盖索引？

覆盖索引是指一个索引包含了查询所需的所有列，而无需访问表的实际数据页（即回表）。
当数据库系统执行查询时，通常需要从磁盘中读取数据页到内存中才能进行处理。而如果使用了覆盖索引，由于索引已经包含了查询所需的所有列的值，数据库系统可以直接通过索引来获取这些值，而不需要额外地读取数据页。这样可以减少磁盘 I/O 的次数和数据在内存中的占用，提高查询的效率。
覆盖索引通常适用于以下场景：

查询语句只需要返回索引列中的数据，而不需要访问其他列的值。
查询语句中的条件过滤、排序或分组的列都在同一个索引上。

总结来说：使用覆盖索引可以减少数据库系统的工作量，提高查询的性能。它可以避免不必要的数据读取操作，减少磁盘 I/O，并且在内存中更高效地处理查询操作。因此，在设计数据库索引时，可以考虑创建覆盖索引来优化相关的查询。

什么是索引下推？

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，它利用了数据库引擎中的索引和过滤条件，将部分过滤工作下推到存储引擎层面进行处理，从而减少不必要的数据读取和传输。
在传统的查询执行过程中，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些行读取到内存中，然后再进行进一步的过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中。
具体实现方式可以是通过存储引擎提供的接口或者钩子函数，让存储引擎在读取索引页时就进行额外的过滤操作。
通过索引下推，数据库系统可以在存储引擎层面根据索引和过滤条件提前过滤掉不符合条件的数据，减少了需要传递给查询引擎的数据量和内存消耗。这样可以大大减少磁盘 I/O 和数据传输的开销，提升查询性能和整体系统效率。
需要注意的是，索引下推并不是对所有类型的查询都适用，它更适用于复杂查询条件、多列条件的查询中，能够有效地减少不必要的数据读取和传输。例如：

-- 在name和phone上建立联合索引，理论上phone使用了前置“%”不可能使用到索引，正常会通过name字段使用索引将查询到内存中再基于phone过滤，这样就会读取很多不需要的数据到内存中。
-- 但是ICP可以将phone的过滤放到存储引擎层，在索引查询时就过滤掉phone不符合条件的记录，这样只需要读取符合记录的数据到内存并返回给用户即可。
SELECT * FROM user_innodb WHERE name = "张三" AND phone LIKE "%6606";

全文索引的实现原理

全文索引：是将存储在数据库中的大段文本中的任意内容信息查找出来的技术。一般是基于倒排索引实现的
倒排索引：在辅助表中存储了单词与单词自身在一个或多个文档中所在的位置之间的映射，它的表现形式如下：{单词，（单词所在文档的ID，再具体文档中的位置）}，其中（documentid， position）视为一个“ilist”。
参考：《全文索引的原理及定义》

or什么情况会用索引

只有or两边的条件都有独立索引、主键索引、或联合索引的第一列，才会走索引，并且不能再and其他多余的字段条件，否则也不能走索引。如果两个字段是在同一个联合索引里面的前面两列，并且select结果里只有这两列，走了覆盖索引，才会走联合索引，否则也不会使用索引

Mysql分库分表

分库分表

框架：Sharding-jdbc、Mycat

Sharding-jdbc：这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是各个系统都需要耦合 Sharding-jdbc 的依赖，升级比较麻烦
Mycat：这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了

拆分方案：水平拆分、垂直拆分

水平拆分：一个表放到多个库，分担高并发，加快查询速度
• id保证业务在关联多张表时可以在同一库上操作
• range方便扩容和数据统计
• hash可以使得数据更加平均
垂直拆分：一个表拆成多个表，可以将一些冷数据拆分到冗余库中
• 分库数据间的数据无法再通过数据库直接查询了。会产生深分页的问题
• 分库越多，出现问题的可能性越大，维护成本也变得更高。
• 分库后无法保障跨库间事务，只能借助其他中间件实现最终一致性。

选择合适的分布式主键ID方案

中心服务器生成、事先生成、uuid、自增步长设定
• 使用redis可以
• 并发不高可以单独起一个服务，生成自增id
• 设置数据库step自增步长可以支撑水平伸缩
• UUID适合文件名、编号，但是不适合做主键
• snowflake雪花算法，综合了41时间（ms）、10机器、12序列号（ms内自增），其中机器预留的10bit可以根据自己的业务场景配置

分库与分表带来的分布式困境与应对之策

数据一致性：由于数据被分布到不同的数据库和表中，分库分表涉及跨节点的事务，需要确保数据的一致性。可以采用两阶段提交（2PC）协议、最终一致性方案或者基于分布式事务的工具来管理分布式事务，确保数据的一致性。
跨分片查询：当业务需要跨多个分片进行查询时，可能会面临性能问题和复杂的查询逻辑。可以使用分布式查询引擎、数据聚合、缓存和分布式计算框架等技术来处理跨分片查询需求，提高查询效率和简化查询逻辑。
全局唯一性约束：在分库分表环境下，全局唯一性约束可能受到挑战。可以采用分布式唯一ID生成器（如Snowflake算法）来生成全局唯一ID，避免冲突。

分表后非分片键的查询、排序怎么处理

在使用分表后，非分片键的查询和排序可以通过以下方式进行处理：

联合查询：如果需要执行涉及多个分表的查询，可以使用联合查询（UNION）或者连接查询（JOIN）来合并结果。将查询条件应用到对应的分表，然后将结果联合起来返回给用户。
扫描所有分表：如果查询涉及到的数据无法通过分片键进行过滤，那么可能需要扫描所有的分表来获取满足条件的数据。这样的查询会比较慢，因为需要访问所有的分表并合并结果，但在某些场景下是必要的。
手动分页和排序：如果需要对分表后的数据进行排序和分页，可以在每个分表上执行独立的排序和分页操作，然后将结果合并或者按需返回给用户。这需要在应用程序中进行额外的逻辑处理。
使用全局索引：如果有一些特定的查询需要在非分片键上执行，并且这些查询非常频繁，可以考虑创建全局索引。全局索引不关心分片键，而是在整个集群上创建索引，可以加速这类查询的执行速度。

跨分片的排序分⻚问题

一般来讲，分⻚时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如下图所示：

如何将老数据做迁移

历史数据同步：通过binlog做同步，如果新旧表结构不一致，比如分库分表、系统重构等，则可通过代码读老数据写新库，写的时候注意updatetime。

新数据：通过binlog实时同步双写，线上系统里所有写库的地方，增删改操作，除了对老库增删改，都加上对新库的增删改。
离线对账：可以通过异步对账、离线数据备份对账等方式进行数据对比，直到两边数据完全一致。

几个注意事项：

先开启新数据双写，然后基于开启updateTime时间，同步小于此updateTime的历史数据，遇到新表中已经存在的情况就跳过这条数据，这样保证历史数据的完整性。
要注意应用代码中是否会有连表查询、非分库分表键的其他查询SQL，如果有，需要改造，可以使用别的数据库（ES、离线库、Redis）等方式进行查询，分库分表中只允许通过非分库分表键查。
因为binlog同步的延时问题，新老切换过程中，先切对binlog延时不敏感的读服务，再切读写一致性要求高的服务。

讲讲主从复制原理与延迟

MySQL 的主从复制是由三个线程完成的，原理如下：

首先，主库将变更写入 binlog 日志，当binlog有变动时，binlog dump线程读取其内容，并发给从节点。
从库连接到主库后，有一个 IO 线程负责将主库的 binlog 日志复制到自己本地，并写入到中继日志（relay log）文件中。
然后，从库中有一个 SQL 线程会从中继日志读取 binlog，并执行其中的 SQL 内容，即在从库上再次执行一遍。

以上就是主从复制的原理。那么主从延迟的原因有哪些呢？

主库的从库太多，主库需要将 binlog 日志传输给多个从库，导致复制延迟。
在从库执行的 SQL 中存在慢查询语句，会导致整体复制进程的延迟。
如果主库的读写压力过大，会导致主库处理 binlog 的速度减慢，进而影响复制延迟。

为了优化主从复制的延迟，我们可以采取以下措施：

减少从库的数量，降低主库的负载，减少复制延迟。
优化慢查询语句，减少从库执行SQL 的延迟。
对主库进行性能优化，减少主库的读写压力，提高 binlog 写入速度。

针对延迟导致的数据不一致问题，我们可以采取以下措施解决：

增加缓存层，更新数据后，将数据记录放入缓存保存一段时间，读任务先读缓存，读不到再读数据库。同样会引入数据库和redis不一致问题。
增加缓存层，但缓存层只存储数据主键和版本号，读任务先读缓存，读不到或者跟从库版本号不一致则读主库。
服务降级，在业务上允许读不到数据或短暂读取到历史数据，做好兼容。

通过以上措施可以帮助降低主从复制的延迟，提高复制的效率和一致性。

Mysql数据库锁

数据库锁

基于锁的属性分类：共享锁，排他锁
基于锁的粒度分类：行级锁（InnoDB），表级锁（InnoDB、MyIsam），页级锁（BDB引擎），记录锁，间隙锁（锁一个范围），临键锁（Next-Key Locks）
基于锁的状态分类：意向共享锁，意向排他锁
何时加锁：冲突时等待
锁的使用方式：悲观锁，乐观锁
锁的超时等待时间：innodb_lock_wait_timeout：50秒

行锁是怎么实现的

InnoDB行锁是通过在索引上加锁来实现行锁的。行锁一般是先加Next-Key Lock，也就是临键锁，当扫描发现SQL操作包含唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock（记录锁），仅锁住索引本身而不是范围。这里包括共享锁和排他锁都是这么实现的，仅仅只是读的话是不加锁的，通过MVCC实现并发读。

如果是主键索引，那么会在主键索引上加一个锁。
如果是非主键索引，如果是唯一键加锁，那么会在非主键索引上加一个锁并在对应的主键索引上也加一个锁。
如果索引只是普通索引，那么除了会对查找到的索引记录加锁，还在索引前后和中间会加gap锁，也就形成里Next-Key Lock，同样索引关联的聚蔟索引中的行也会加锁。
如果是无索引的加锁，那么表里的所有行和间隙都会加锁，也就是当没有索引时，会导致全表锁定，也就是行锁升级为表锁。

MySQL 遇到的死锁问题

形成死锁的主要原因是加锁顺序问题

场景一：一般的情况，两个session分别通过一个sql持有一把锁，然后互相访问对方加锁的数据产生死锁。两个线程AB，A修改数据a、b，B修改数据b、a，当A队a加锁且B对b加锁后，会因为A想对b加锁，且B想对a加锁时无法获取到锁，导致死锁。解决方案：调整AB更新a、b的顺序，尽量一致。
场景二：根据字段查询，如果不存在，则插入新数据，否则更新。当对存在的行进行锁的时候(主键)，mysql就只有行锁。当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（间隙锁），如果同时插入两条数据就会死锁。解决方案：mysql特有的语法来解决此问题。因为insert语句对于主键来说，插入的行不管有没有存在，都会只有行锁
insert into t3(xx,xx) on duplicate key update xx=‘XX’;

如何解决MySQL死锁问题

MySQL在并发环境下可能会出现死锁问题。死锁是指两个或多个事务互相等待对方释放资源，导致无法继续执行的情况。
解决死锁问题的方法通常有以下几种：

调整事务隔离级别：通过将事务隔离级别降低为读未提交（或读已提交，可以减少死锁的发生概率。但是要注意隔离级别的降低可能引发脏读、不可重复读等数据一致性问题，在选择时需要权衡利弊。
优化查询和事务逻辑：分析造成死锁的原因，优化查询语句和事务逻辑，尽量缩短事务持有锁的时间，减少死锁的可能性。比如按照相同的顺序获取锁，避免跨事务的循环依赖等。
使用行级锁：行级锁可以较小地限制锁的范围，从而减少死锁的可能性。将表的锁粒度调整为行级别，可以减少事务之间的冲突。
设置合理的超时时间和重试机制：当发生死锁时，可以设置适当的超时时间，在一定时间内尝试解锁操作。如果超过设定的时间仍未成功，则进行死锁处理，如终止较早请求的事务或进行回滚等。

需要根据具体情况分析和实施相应的解决方案，并进行测试和验证，以确保解决死锁问题，并提高数据库的并发性能。

了解MySQL锁升级吗

MySQL锁升级是指在并发事务执行过程中，当某个事务无法满足当前所需的锁级别时，系统会自动将该事务的锁级别升级为更高级别的锁。
通常情况下，MySQL 会根据事务的需求和操作的对象自动选择合适的锁级别。然而，在某些情况下，事务无法获取到所需的锁级别，就会触发锁升级。
例如以下两种常见的锁升级场景：

当某个事务需要获取的是行级锁，但由于并发冲突或其他原因无法满足事务的要求时，系统会将该事务的锁级别升级为表级锁，从而保证数据的一致性和并发性能。
当某个事务操作的对象不符合行锁加锁规则，比如没有走索引或非唯一索引记录数达到一定数量，系统也会将该事务的锁级别升级为表级锁，以确保数据的完整性和一致性。

所以在实际应用时，合理的索引设计、适当的事务隔离级别设置可以帮助减少锁升级的发生，从而提高并发性能和系统吞吐量。

高并发场景下，如何安全修改同一行数据

在高并发情况下，多事务安全修改同一行数据可以采用以下方法：

乐观锁：在数据表中添加一个版本号（或者时间戳）字段，每次更新数据时都会检查该字段的值。当多个并发的请求同时修改同一行数据时，只有一个请求能够成功执行更新操作，其他请求需要重新检查数据是否被修改过。如果数据没有被修改，那么它们可以重新尝试更新；如果数据已经被修改，则这些请求需要触发重试等相应的冲突处理逻辑。
悲观锁：在读取数据之前，使用数据库提供的锁机制，如SELECT … FOR UPDATE语句，将要修改的行数据进行加锁。这样，其他并发的请求在读取相同行数据时会被阻塞，直到锁释放。这种方法能够确保同一时间只有一个请求在修改数据，但是会影响系统的并发性能。
分布式锁：通过使用分布式锁服务，如Redis的SETNX命令或ZooKeeper的临时节点，来实现对行级数据的互斥访问。在修改数据前先尝试获取锁，获取成功后执行数据修改操作，修改完成后释放锁。其他请求在获取锁失败时可以等待或执行相应的冲突处理逻辑。
事务：将对同一行数据的修改操作封装在数据库事务中。在事务中，数据库会自动处理并发修改的冲突，通过锁定相应的数据行来确保数据的一致性和完整性。并发的请求会被串行化执行，保证每个请求都能正确读取并修改数据。

需要注意的是，在进行高并发的数据操作时，要考虑到锁的粒度、性能开销以及可能出现的死锁问题，做好并发控制和冲突处理的容错设计。

添加索引会锁表吗？

可能会
在MySQL5.7中，表结构修改和索引添加通常不会锁定整个表。但是，在某些情况下，MySQL可能需要锁定整个表。此外，如果使用ALTER TABLE语句添加索引，则会锁定表。相反，如果使用CREATE INDEX语句添加索引，则不会锁定表。

Mysql事务

事务的特性和四个隔离级别。每个隔离级别的表现如何。

事务的四个特性ACID：原子性(Automicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
四个隔离级别：读未提交、读已提交、可重复读、串行化。
前面三个隔离级别分别对应三种隔离性问题：脏读、不可重复读、幻读。
具体见我另一篇博客：事务系列（一）：事务四大特性与隔离级别

事务的实现原理？ACID靠什么保证

原子性：通过undolog实现。
持久性：通过redolog实现。
隔离性：通过读写锁、MVCC实现。
一致性：通过原子性、持久性、隔离性共同实现。
参考：图解mysql事务实现原理

事务的执行过程（回滚及持久化）

在这里插入图片描述

MVCC

多版本并发控制器，是为了数据库在读取数据时不加锁来提高读取效率和并发性的一种手段。顾名思义，是一种通过多个版本号的方式实现并发读写的手段，通过为每个事务创建数据的不同版本，避免了锁竞争问题。通过undolog + readview + 版本链实现。
它的工作原理如下：

● 每条数据行都有一个隐藏的版本号或时间戳，记录该行的创建或最后修改时间。
● 当事务开始，它会获取一个唯一的事务ID，作为其开始时间戳。
● 在读取数据时，事务只能访问在其开始时间戳之前已提交的数据。这个版本的数据在事务开始前就已存在。
● 当事务更新数据，会创建新版本的数据，将更新后的数据写入新的数据行，并将事务ID与新版本关联。
● 其他事务可以继续访问旧版本的数据，不受正在进行的更新事务影响。这种机制被称为快照读。
● 当事务提交，其所有修改才对其他事务可见。此时，新版本的数据成为其他事务读取的数据。

在读已提交和可重复读隔离级别下的快照读，都是基于MVCC实现的：
（1）RC的隔离级别下，每个快照读都会生成并获取最新的readview。
（2）RR的隔离级别下，只有在同一个事务的第一个快照读才会创建readview，之后的每次快照读都使用的同一个readview，所以每次的查询结果都是一样的。通过mvcc，RR的隔离级别快照读的场景解决了幻读问题，因为每次使用的都是同一个readview。但是当前读的场景（由select … for update实现）会读到最新的提交的数据，这种情况只能通间隙锁或者串行化隔离级别来实现可重复读了。
具体原理请见：
参考：MVCC详解，深入浅出简单易懂

undolog、redolog、binlog的作用

背景：mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用，然后使用后台线程去做缓冲池和磁盘之间的同步。
undo log 叫做回滚日志，用于记录数据被修改前的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来。然后在发生错误事务提交失败的情况下，要回滚数据，可以使用undo log日志里面的数据恢复Buff Pool里面的缓存数据。用于保障事务的原子性。
redo log 叫做重做日志，日志记录方式：物理日志，用来实现事务的持久性。其中redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，是物理日志。如果事务提交成功，Buff Pool里面的数据还没来得及写入硬盘，此时系统宕机了，可以使用redo log日志恢复磁盘ibd文件里面的数据。
binlog 也叫归档日志，日志记录方式：逻辑日志。mysql的二进制日志是事务安全型的，记录了所有DDL与DML语句，主要用于恢复数据库磁盘里面的数据；多用于数据恢复、主从同步、业务监听等场景。
redolog和binlog什么时候写？
mysql修改数据会同时在内存和redolog记录这次操作，在事务提交时、redolog buffer满了或定时刷盘任务，都会将redolog从缓冲区写入磁盘，所以redolog事务提交不成功也可能会写入磁盘，但事务提交成功后会给redolog一个commit状态标识用于区分。
事务执行过程中,会先把binlog日志写到binlog cache中去,事务提交时,才把binglog cache写到binlog文件中去(刷盘)，刷盘时机由sync_binlog参数控制，取值范围0~N，0代表有系统决定何时同步，1表示每次都同步，N表示N个事务同步一次。
物理日志和逻辑日志的区别
逻辑日志：可以简单理解为sql语句，如update ** 、insert ** 、delete **。
物理日志：mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更，简单来说就是：在哪个数据页做了什么修改，如字段值1->2.
binlog和undolog都是逻辑日志，记录的是sql语句；而redolog是物理日志，记录的是数据页的变更。

Undo log是如何回滚事务的

在数据库中，Undo Log通常用于实现事务的回滚操作。当事务执行更新操作时，数据库会将相应的旧数据记录在Undo Log中，用于回滚事务时还原到事务开始前的状态。以下是Undo Log回滚事务的一般步骤：

首先，获取事务的回滚指针或Undo Log的起始位置。
从Undo Log的末尾开始逆向扫描，按照事务操作的逆序依次处理每个日志记录。
然后，针对 INSERT 操作，执行 DELETE 操作来撤销插入的数据。对于 UPDATE 操作，使用Undo Log 中记录的旧值将数据还原到之前的状态。

在回滚过程中，对于已经提交的其他事务所做的修改需要跳过，只处理属于当前回滚事务的 Undo Log 记录。
按照逆序依次处理所有的日志记录，直到达到回滚指针位置或 Undo Log 的起始位置。
回滚完成后，清除或标记已回滚的 Undo Log 记录。
总体而言，事务回滚是通过执行 Undo Log 中记录的反向操作，将事务的修改操作撤销，恢复到事务开始前的状态。

Mysql性能优化

explain的使用。

主要关注type，key，rows，Extra。详见我的另一篇博客：数据库索引系列三：EXPLAIN使用详解

mysql查询中的filesort是指什么

在 MySQL 中，filesort 是一种排序机制，当查询需要对结果进行排序（如使用 ORDER BY 或 GROUP BY），且无法利用索引完成排序时，MySQL 会通过内存或磁盘对结果集进行排序，这个过程就称为 filesort。
参考《mysql查询中的filesort是指什么》

慢SQL你是怎么优化的

针对SQL慢查询，可以考虑以下一些优化措施：

使用explain查看SQL执行计划：使用explain查看sql执行计划，看是否使用索引、临时表、全表扫描等，针对具体情况进行SQL优化。
优化查询语句结构：检查是否存在冗余的操作、重复的子查询、不必要的排序、大量的JOIN操作等。优化查询语句的结构和逻辑，减少不必要的数据读取和计算。
添加合适的索引：确保查询中涉及的列都有适当的索引，并且查询条件能够充分利用索引。通过使用适当的索引，提高查询的性能。索引尽量避开区分度小的字段、金额类经常变动的字段、长文本型字段，避免索引字段里有null值，查询条件避免使用函数，避免全表扫描，避免文件排序。但是要避免过多的索引，因为过多的索引会增加写入操作的开销。
使用覆盖索引：如果查询只需要使用索引列的数据而不需要访问表的其他列，可以考虑使用覆盖索引。覆盖索引避免了访问表的额外IO操作，提高查询性能。
避免全表扫描：尽量避免全表扫描的情况，通过合适的索引或筛选条件来限制查询范围，减少数据读取量。
合理分页查询：对于大数据量的分页查询，可以通过使用LIMIT分页、使用游标、定期同步缓存等方式来提高性能。

以上是一些常见的SQL慢查询优化措施，具体的优化方法还因根据具体问题和应用场景进行调整。

limit 20000加载很慢怎么解决（深分页问题）

limit在查询分页的时候，如果是limit 0,100，则很快，如果limit 100000,100，则会很慢，因为她会查询100100条数据然后丢弃前面100000条数据。

解决方案：
1. 子查询+覆盖索引：子查询可以使用覆盖索引，避免回表查询，如下例子，相当于只需要一次id索引的排序+一次基于id查询数据，而不是查询出id、title、content数据记录到内存之后再排序。

SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
-- 或者
SELECT * FROM users WHERE uid >=  (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10;

2. 反向查找：当偏移量超过一半时，使用反向排序。如一个1628775条记录的表查找第30000页（每页40个）

正向：SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40；
反向：SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

3. 记住上次的位置：如果你的应用程序可以记住上次查询的最后一个 ID，那么你可以使用 >last_id 来避免扫描大量数据。
4. 使用分页插件：有些数据库框架提供了分页插件，可以自动优化分页查询。
5. 避免深分页：在设计应用程序时，尽量避免深分页。例如，你可以提供搜索功能，让用户快速找到他们需要的数据，而不是浏览大量的页面。

为什么大家说mysql数据库单表最大两千万？依据是啥？

B+树叶子和非叶子结点的数据页都是16k，且数据结构一致，区别在于叶子节点放的是真实的行数据，而非叶子结点放的是主键和下一个页的地址。
B+树一般有两到三层，由于其高扇出，三层就能支持2kw以上的数据，且一次查询最多1~3次磁盘IO，性能也还行。
存储同样量级的数据，B树比B+树层级更高，因此磁盘IO也更多，所以B+树更适合成为mysql索引。
索引结构不会影响单表最大行数，2kw也只是推荐值，超过了这个值可能会导致B+树层级更高，影响查询性能。
单表最大值还受主键大小和磁盘大小限制。主键大的话非叶子节点上能存放的索引就少，扇出就小，总体能存放的数据行数就少；相反主键小的话能存放的数据行数就多。所以如果主键大，可能几百万就不能放了，主键小，放一个亿数据进去也不会影响B+树的行数。
参考：《为什么大家说mysql数据库单表最大两千万？依据是啥？》
B站视频讲解：【为什么说mysql数据库单表最大两千万？依据是啥？】

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的 IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询 MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

谈谈 SQL 优化的经验

查询语句无论是使用哪种判断条件等于、小于、大于，WHERE 左侧的条件查询字段不要使用函数或者表达式
使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explainsql 来分析这条 sql 语句，这样方便我们分析，进行优化。
当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1。不要直接使用SELECT*，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT"使用的是全表扫描，也就是 type =all 。
为每一张表设置一个 ID属性。
避免在 MHERE 字句中对字段进行 NULL
判断避免在 WHERE中使用!或>操作符
使用 BETWEEN AND 替代 IN
为搜索字段创建索引
选择正确的存储引擎，InnoDB、MyISAM、MEMORY等
使用 LIKE%abc%不会走索引，而使用 LIKE abc%会走索引。
对于枚举类型的字段(即有固定罗列值的字段)，建议使用 ENUM 而不是 VARCHAR，如性别、星期、类型、类别等。
拆分大的 DELETE或 INSERT 语句
选择合适的字段类型，选择标准是尽可能小、尽可能定⻓、尽可能使用整数。
字段设计尽可能使用 NOT NULL
进行水平切割或者垂直分割

如何快速定位慢SQL

要查询慢SQL产生的原因，可以采取以下4个步骤：

启用慢查询日志：在MySQL配置中启用慢查询日志，这样可以记录执行时间超过阈值的查询语句。通过分析慢查询日志，可以找到执行时间较长的SQL语句。
使用EXPLAIN分析执行计划：对于慢查询的SQL语句，使用EXPLAIN命令来查看其执行计划。通过分析执行计划，确定查询是否有效利用了索引以及是否存在性能瓶颈。
检查索引使用情况：确保查询中涉及的列都有适当的索引，并且查询条件能够充分利用索引。可以使用SHOW INDEX命令或查询表的索引信息来检查索引情况。
分析查询语句：仔细分析查询语句本身，检查是否存在冗余的操作、重复的子查询、不必要的排序、大量的JOIN操作等。

通过这些步骤的分析，找出慢查询产生的原因，并针对性地进行优化和调整，来提升查询性能。

MySQL 有哪些高可用方案

MySQL的高可用方案主要有以下几种：

主从复制：这是最常见的高可用方案。主库负责处理写操作，并将数据变更记录到binlog日志。从库将主库的binlog复制到自己的中继日志，然后执行中继日志中的事件，以达到与主库数据一致的目的。当主库出现故障时，可以将从库提升为新的主库，实现服务的高可用。
集群：MySQL集群是一个高可用、高性能的数据库集群解决方案。它使用了共享无关的架构，可以在节点之间自动分割和复制数据，实现了数据的高可用和高性能。

Mysql高可用方案有哪些?

Mysql高可用方案包括:

主从复制方案
这是MySQL自身提供的一种高可用解决方案，数据同步方法采用的是MySQL replication技术。MySQL replication就是从服务器到主服务器拉取二进制日志文件，然后再将日志文件解析成相应的SQL在从服务器上重新执行一遍主服务器的操作，通过这种方式保证数据的一致性。为了达到更高的可用性，在实际的应用环境中，一般都是采用MySQL replication技术配合高可用集群软件keepalived来实现自动failover，这种方式可以实现95.000%的SLA。
MMM/MHA高可用方案
MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中，典型的应用是双主多从架构，通过MySQL replication技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现MySQL的高可用。
Heartbeat/SAN高可用方案
在这个方案中，处理failover的方式是高可用集群软件Heartbeat，它监控和管理各个节点间连接的网络，并监控集群服务，当节点出现故障或者服务不可用时，自动在其他节点启动集群服务。在数据共享方面，通过SAN（Storage Area Network）存储来共享数据，这种方案可以实现99.990%的SLA。
Heartbeat/DRBD高可用方案
这个方案处理failover的方式上依旧采用Heartbeat，不同的是，在数据共享方面，采用了基于块级别的数据同步软件DRBD来实现。DRBD是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和SAN网络不同，它并不共享存储，而是通过服务器之间的网络复制数据。
NDB CLUSTER高可用方案
国内用NDB集群的公司非常少，貌似有些银行有用。NDB集群不需要依赖第三方组件，全部都使用官方组件，能保证数据的一致性，某个数据节点挂掉，其他数据节点依然可以提供服务，管理节点需要做冗余以防挂掉。缺点是：
管理和配置都很复杂，而且某些SQL语句例如join语句需要避免。

数据库 cpu 飙升，你会如何处理

当MySQL数据库的CPU使用率飙升时，可能是由于以下几个原因导致的：

查询性能问题：某些查询可能没有被正确地优化，导致查询执行时间过长，从而占用大量的CPU资源。可以通过查看慢查询日志和执行计划来分析问题查询，并进行索引优化、重写查询语句或调整数据库配置等方式来改善查询性能。
数据库连接问题：如果存在大量的数据库连接并发访问，可能会造成CPU负载过高。可以检查应用程序连接池的配置情况、数据库连接数限制以及是否有闲置的连接未关闭等问题，并进行相应调整。
锁和死锁问题：并发事务之间的锁竞争或死锁可能导致CPU飙升。可以通过查看数据库的锁状态、死锁日志以及事务并发控制的设置来解决锁相关的问题。
配置问题：不合理的数据库配置可能导致CPU资源浪费和效率低下。可以检查MySQL的配置参数，如缓冲区大小、并发连接数、线程池大小等是否合理设置，并进行相应调整。
资源竞争：如果服务器的物理资源（如内存、磁盘I/O）不足或受限，可能会导致CPU过度使用。可以通过监控系统资源使用情况，调整或增加资源配置，以满足数据库的需求。

此外，定期进行数据库性能优化和监控也是重要的措施，可以通过数据库性能分析工具、慢查询日志、系统监控工具等来识别和解决性能问题。

如何进行Mysql数据库的备份和恢复

备份：

以命令行的方式备份：MySQL提供了mysqldump命令来实现数据的备份，具体语法格式如下。mysqldump -uusername -ppassword dbname>path:filename.sql在以上语法格式中，-u后的参数username表示用户名，-p后的参数password表示登录密码，dbname表示需要备份的数据库名称，path表示备份文件存放的路径，filename.sql代表备份文件的名称。
以图形化的方式备份：MySQL客户端工具——SQLyog的使用，用户同样可以使用SQLyog来做数据库备份，也就是以图形化的方式备份，这样使用起来更加便捷。

还原：

以命令行的方式：还原MySQL提供了mysql命令来实现数据的还原，具体语法格式如下。mysql -uusername -ppassword dbname<path:filename.sql在以上语法格式中，-u后的参数username表示用户名，-p后的参数password表示登录密码，dbname表示需要还原的数据库名称，path代表备份文件存放的路径，filename.sql代表备份文件的名称。
以图形化的方式还原：数据还原也可以通过图形化的方式实现，也就是使用SQLyog还原数据。