MySQL 存储引擎 MyISAM 与 InnoDB 区别

1. 事务支持： InnoDB 支持事务，提供了事务的 ACID（原子性、一致性、隔离性、持久性）特性，确保数据的完整性和一致性。而 MyISAM不支持事务。
2. 并发性能： InnoDB 采用行级锁定，提供更好的并发性能，允许同时进行读写操作。MyISAM采用表级锁定，在高并发环境下可能会有性能下降。
3. 数据完整性： InnoDB提供了更强大的数据完整性支持，包括外键约束、参照完整性等。MyISAM 对数据完整性的支持相对较弱。
4. 索引支持： MyISAM支持全文索引，对于全文搜索等操作更有效。InnoDB 主要使用 B+树索引。
5. 数据存储： InnoDB的数据存储方式更适合处理大型数据集，并且支持自动灾难恢复。MyISAM 更适合小型表和读取密集型操作。
6. 缓存效率： InnoDB 具有更高的缓存效率，对于经常访问的数据可以更好地利用缓存。
7. 表空间管理： InnoDB使用表空间来管理数据和索引，提供了更灵活的存储管理方式。MyISAM 则将数据和索引存储在不同的文件中。

MyISAM 索引与 InnoDB 索引的区别

• InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。
• InnoDB 的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数
  据。
• InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此
  查询时做到覆盖索引会非常高效。

B+树

MySQL中In与Exists的区别

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

一条sql语句最多会用多少索引

N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢
MySQL5.0之后是有索引合并这个概念的，所以第一个问题解决了，MySQL可以同时使用多个索引

索引有哪些优缺点？

索引的优点

• 加快查询速度：索引可以帮助数据库快速定位符合条件的数据行，避免全表扫描，从而显著提高查询的性能。
• 支持排序和分组：索引可以用于实现数据的排序和分组操作，提高这些操作的效率。
  提高连接操作的性能：在进行表连接时，索引可以帮助快速找到匹配的行，减少连接操作的时间。
• 支持范围查询：通过索引，数据库可以高效地处理范围查询，例如 WHERE column > value 或 WHERE column BETWEEN value1 AND value2。
• 改善数据检索的选择性：索引可以帮助数据库更好地选择合适的索引，提高查询的选择性，减少不必要的数据访问。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进
  行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执
  行效率；
• 空间方面：索引需要占物理空间。

索引有哪几种类型？

• 主键索引:
  • 数据列不允许重复，不允许为 NULL，一个表只能有一个主键。
• 唯一索引:
  • 数据列不允许重复，允许为 NULL 值，一个表允许多个列创建唯一索引。
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索
    引。
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创
    建唯一组合索引。
• 普通索引:
  • 基本的索引类型，没有唯一性的限制，允许为 NULL 值。 可以通过 ALTER TABLE table_name ADD INDEX index_name (column);创建
  • 普通索引可以通过 ALTER TABLE table_name ADD INDEX index_name(column1,
    column2, column3);创建组合索引。
• 全文索引：
  • 是目前搜索引擎使用的一种关键技术。 可以通过 ALTER TABLE table_name ADD FULLTEXT (column);创建全文索
    引。

索引会失效

1. 函数操作索引列：当对索引列进行函数操作时，索引可能会失效。例如，WHERE LOWER(column)、WHERE DATE(column) 等。

2. 不满足最左前缀原则：如果在多列索引中，查询没有从最左边的列开始，索引也可能会失效。例如，如果有一个 (a, b) 的索引，但查询中只用到了 b 列，索引会失效。

3. 使用!=或<>操作符：当使用 != 或 <> 操作符时，索引可能会失效。

4. 列类型不匹配：如果查询时用到的列和索引列的类型不匹配，索引可能会失效。例如，如果索引列是字符串类型，但在查询中将其作为数字使用，索引会失效。

5. 过滤条件中使用OR操作符：在过滤条件中使用 OR 操作符时，索引可能会失效。

6. 隐式类型转换：当查询中的数据类型与索引列的数据类型不匹配，MySQL 可能会进行隐式类型转换，这会导致索引失效。

7. 大范围查询：如果查询结果需要返回大部分数据，MySQL 可能会选择不使用索引。

8. 数据分布不均匀：如果索引列的数据分布不均匀，可能会导致索引失效。

9. 表中行数太少：对于行数太少的表，MySQL 可能会选择不使用索引。

mysql innodb，叶子节点在磁盘上分布是顺序的吗

在 InnoDB 存储引擎中，数据在磁盘上的存储方式是有序的，但并不是严格意义上的连续存储，而是通过 B+ 树的结构进行组织的。
虽然数据在 InnoDB 存储引擎中是以有序的方式存储的，但是在磁盘上的存储位置并不是严格连续的，而是受到多种因素的影响。

叶子节通过什么关联

一个叶子节点中的多条数据通常是通过主键顺序来关联的
通过链表结构来关联的。这个链表是一个双向链表，每个数据记录都包含指向前一个和后一个数据记录的指针

MySQL 中有哪几种锁

1. 共享锁（Shared Locks）：也称为读锁，允许并发读取数据，但阻止其他事务对数据进行修改。
2. 排他锁（Exclusive Locks）：也称为写锁，用于独占访问数据，阻止其他事务读取或修改数据。
3. 意向共享锁（Intention Shared Locks）：表示一个事务有意获取共享锁的意向。
4. 意向排他锁（Intention Exclusive Locks）：表示一个事务有意获取排他锁的意向。
5. 记录锁（Record Locks）：用于锁定单个数据行。
6. 间隙锁（Gap Locks）：用于锁定数据行之间的间隙，防止插入或修改范围内的数据。
7. 临键锁（Next-Key Locks）：是记录锁和间隙锁的组合，锁定一个数据行及其前面的间隙。
8. 表锁（Table Locks）：用于锁定整个表，阻止其他事务对表进行操作。

MySQL 中 InnoDB 支持的四种事务隔离级别

默认隔离级别是可重复读
查看数据库隔离级别

show variables like 'transaction_isolation';

• 读未提交（Read Uncommitted）：最低的隔离级别，事务可以读取其他未提交事务修改的数据，可能导致脏读、不可重复读和幻读问题。
• 读已提交（Read Committed）：默认的隔离级别，事务只能读取其他已提交事务修改的数据，可以避免脏读，但仍然可能出现不可重复读和幻读。
• 可重复读（Repeatable Read）：通过使用行级锁定和多版本并发控制（MVCC），确保在同一个事务中多次读取相同的数据时，结果是一致的，避免了不可重复读，但仍可能出现幻读。
• 串行化（Serializable）：最高的隔离级别，事务串行执行，避免了脏读、不可重复读和幻读问题，但会导致较高的并发性能下降。

对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

• 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
• 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

MySQL 的 redo log、undo log 及 binlog

• undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
  • 事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
  • MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
• redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
  • redo log 是物理日志，记录了某个数据页做了什么修改，对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条物理日志。
  • 在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态
• binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

redo log 和 undo log 区别在哪？

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之「后」的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之「前」的值；
  事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。
磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

产生的 redo log 是直接写入磁盘的吗？

不是的。
实际上， 执行一个事务，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。
所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘
redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能

redo log 什么时候刷盘？

缓存在 redo log buffe 里的 redo log 还是在内存中，它什么时候刷新到磁盘？
主要有下面几个时机：

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘

为什么需要两阶段提交？

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。

将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

• prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
• commit 阶段：把 XID 写入到 binlog，然后将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件，所以 commit 状态也是会刷盘的）；

char 和 varchar 的区别

• 存储方式：char 是固定长度的字符类型，每个 char 字段都会占用指定的字符数空间，即使实际存储的字符数少于指定长度，也会填充空白字符以满足长度要求。而 varchar 是可变长度的字符类型，它只存储实际输入的字符，不填充空白字符。
• 存储空间：由于 char 是固定长度的，所以在存储较短的字符时可能会浪费存储空间。而 varchar 则更节省空间，尤其是对于长度不确定或较短的字符数据。
• 性能：在某些情况下，char 的性能可能会稍微优于 varchar，因为在检索和比较时不需要进行额外的长度计算。然而，对于大多数情况，性能差异通常可以忽略不计。
• 限制：char 通常有长度限制，例如在某些数据库中可能限制为 255 个字符。而 varchar 的长度可以根据需要进行设置，但通常也有最大长度限制。

创建索引时需要注意什么

1. 选择合适的列：索引应该建立在经常用于查询、连接或排序的列上。选择具有高选择性的列，即具有较少重复值的列，可以提高索引的效果。
2. 避免过多的索引：虽然索引可以提高查询性能，但过多的索引会增加数据插入、更新和删除的开销。只在必要的列上创建索引。
3. 考虑数据分布：如果数据的分布不均匀，例如某些值出现的频率非常高，可能会导致索引的效果不佳。在这种情况下，可以考虑使用其他数据结构或索引技巧。
4. 索引的顺序：在多列索引中，列的顺序会影响索引的效率。将最选择性高的列放在前面通常会更有帮助。
5. 维护索引：随着数据的变化，索引可能需要维护。例如，在大量数据插入或删除后，可能需要重建或优化索引。

使用索引查询一定能提高查询的性能吗？为什么

• 不合适的索引选择：如果选择了不正确或不相关的列作为索引，可能不会对查询性能产生显著的改进。索引应该基于经常用于查询条件、连接或排序的列。
• 数据分布不均匀：如果数据的分布非常不均匀，某些值在索引列中出现的频率极高，可能会导致索引的效果不佳。这可能导致大量的索引扫描而不是有效的过滤。
• 低选择性：如果索引列中的值具有较高的重复性，即选择性较低，索引可能无法提供很大的帮助。例如，在一个包含大量相同值的列上创建索引可能不太有效。
• 查询的复杂性：如果查询本身非常复杂，涉及多个表的连接、子查询或复杂的逻辑，索引可能只能在一定程度上提高性能。其他因素，如查询计划的优化、数据量和硬件资源等，也会对性能产生影响。
• 数据更新频繁：如果表中的数据经常进行大量的插入、更新和删除操作，索引需要不断维护，这可能会带来一定的性能开销。
• 索引碎片：随着时间的推移，索引可能会出现碎片，导致索引效率下降。定期重建或优化索引可以改善这种情况。

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

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所
以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操
作需要消耗额外的 IO,会降低增/改/删的执行效率。所以，在我们删除数据库
百万级别数据的时候，删除数据的速度和创建的索引数量是成正比的。

• 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多
  钟）
• 然后删除其中无用数据（此过程需要不到两分钟）
• 删除完成后重新创建索引(此时数据较少了)创建索引也非常快
• 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回
  滚。那更是坑了。

什么是最左前缀原则？

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where 子句
中使用最频繁的一列放在最左边。
最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查
询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。
=和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。

MySQL 查询缓存

MySQL 在得到一个执行请求后，会首先去查询缓存 中查找，是否执行过这条SQL 语句，之前执行过的语句以及结果会以 key-value 对的形式，被直接放在内存中。key 是查询语句，value 是查询的结果。
如果通过 key 能够查找到这条 SQL 语句，就直接妾返回 SQL 的执行结果。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果就会被放入查询缓存中。可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，效率会很高。

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

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

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

SQL 优化的经验

1. 索引优化：合理地创建和使用索引可以显著提高查询性能。选择经常用于查询条件、连接和排序的列创建索引，并确保索引的有效性和合理性。
2. 查询语句优化：避免使用复杂的查询，尽量简化和优化查询语句。避免使用 SELECT *，明确指定需要的列。使用合适的连接方式（如 Inner Join、Left Join 等），并注意连接条件的顺序。
3. 避免全表扫描：通过使用索引、条件过滤等方式尽量减少全表扫描的发生，提高查询效率。
4. 参数化查询：避免在查询中直接拼接参数，使用参数化查询可以防止 SQL 注入攻击，并提高查询计划的重用性。
5. 数据分页：当返回大量数据时，采用分页机制，每次只返回需要的一部分数据，避免一次性返回过多数据。
6. 避免不必要的计算和函数调用：尽量在数据库层面完成必要的计算和操作，避免在查询中进行过多的客户端计算。
7. 定期维护和优化：包括定期分析和优化表结构、索引、统计信息等，以及清理过期或无用的数据。
8. 监控和性能分析：使用数据库的监控工具和性能分析工具，了解查询的执行计划、开销和瓶颈，针对性地进行优化。
9. 考虑数据分布和数据量：根据数据的特点和分布情况，选择合适的数据存储方式和索引策略。
10. 测试和验证：在实际环境中进行测试和验证，比较不同优化方案的效果，选择最适合的方案。

为什么Mongodb索引用B树，而Mysql用B+树?

(1)B树的树内存储数据，因此查询单条数据的时候，B树的查询效率不固定，最好的情况是O(1)。我们可以认为在做单一数据查询的时候，使用B树平均性能更好。但是，由于B树中各节点之间没有指针相邻，因此B树不适合做一些数据遍历操作。

(2)B+树的数据只出现在叶子节点上，因此在查询单条数据的时候，查询速度非常稳定。因此，在做单一数据的查询上，其平均性能并不如B树。但是，B+树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得B+树非常适合做范围查询。

因此，我们可以做一个推论:没准是Mysql中数据遍历操作比较多，所以用B+树作为索引结构。而Mongodb是做单一查询比较多，数据遍历操作比较少，所以用B树作为索引结构。

慢查询怎么办

处理思路：
所有查询都很慢

• 查看数据库情况，监控，资源配置，可能服务器资源不足，大量并发，锁竞争等

定位慢查询sql语句慢查询日志
通过explain 分析sql语句执行 explain的字段含义参考
possible_keys：可能用到的索引
key: 实际使用的索引
Extra：额外信息，如Using index、Using where等

参考慢查询
查询开启慢查询日志

show variables like '%slow_query_log%';

set global slow_query_log='ON'; 

设置慢查询时间 单位：秒，默认10秒

set global long_query_time=1; 

查看当前Mysql所有的进程

show processlist;

查看Mysql的最大缓存

show global variables like "global max_allowed_packet"

查看当前正在进行的事务

select * from information_schema.INNODB_TRX

查看当前Mysql的连接数

show status like 'thread%'

总结可能原因
索引问题： 如果数据库表没有正确的索引，或者索引失效，数据库查询将会变得很慢。这可能是由于索引损坏、删除或者数据库统计信息不准确导致的。

查询语句问题：查询语句的性能也可能会导致查询变慢。例如，复杂的查询、未优化的查询、大量的JOIN操作或者子查询等。

数据量增加：当数据库中的数据量增加时，查询可能会变慢。这可能是由于增加的数据量导致索引效率降低，或者查询需要处理更多的数据导致的。

硬件资源问题：数据库运行所在的服务器硬件资源不足，例如CPU、内存、磁盘I/O等，都可能导致查询变慢。

锁和并发问题：如果数据库中有大量的并发查询或者事务，可能会导致锁竞争，进而影响查询的性能。

数据库配置问题：数据库配置不当也可能导致查询变慢。例如，内存配置不足、缓冲池设置不当、日志设置不当等。

网络问题：如果数据库连接存在网络延迟或者带宽限制，也会导致查询变慢。

MySQL WAL （Write-Ahead Logging）技术

指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。