索引

创建索引选择的数据结构的依据是尽量减少磁盘IO的次数

使用索引可以带来多方面的好处，对于数据库性能和查询效率至关重要。以下是使用索引的一些主要原因：

1. 提高查询性能： 索引能够加速数据的检索，通过减少需要扫描的数据量，从而降低查询的时间复杂度。对于大型数据表和复杂查询，索引可以显著提升查询性能。

2. 加速排序： 索引可以使排序操作更加高效，因为数据库可以利用索引的有序性来避免全表扫描。

3. 支持快速查找： 索引允许数据库快速定位满足特定条件的数据行，使得等值查找、范围查找和多条件查询更加高效。

4. 提高连接性能： 在连接多个表时，索引可以加速连接操作，从而减少连接的成本。

5. 优化聚合操作： 聚合函数如 SUM、AVG、COUNT 等在索引的支持下可以更快地完成，减少计算所需的时间。

6. 保障数据完整性： 主键和唯一索引可以强制确保数据的唯一性，避免重复数据的插入。

7. 支持外键约束： 外键索引可以确保表之间的引用完整性，保证数据关系的正确性。

8. 加速全文搜索： 全文索引可以加速针对文本字段的搜索，支持高级的文本匹配算法。

   在InnoDB中，索引采用的是B+树

虽然索引在提高数据库查询性能方面起到了重要作用，但也存在一些缺点和考虑因素。以下是 MySQL 中索引的一些缺点：

1. 占用存储空间： 索引会占用额外的存储空间，特别是在大型表中创建多个索引可能会显著增加数据库的存储需求。

2. 影响写操作性能： 索引的存在会增加插入、更新和删除操作的成本，因为每次修改数据时都需要维护索引结构。

3. 索引维护成本： 随着数据的插入、更新和删除，索引结构需要不断地维护和调整，这可能会增加数据库的维护成本。

4. 降低写操作性能： 在高并发写操作的情况下，索引可能会导致锁定和竞争，降低写操作的性能。

5. 查询优化器可能选择不合适的索引： 查询优化器在选择索引时可能会出现错误的决策，导致查询性能下降。

6. 索引选择和设计复杂： 创建适当的索引需要根据实际查询需求进行权衡和设计，可能需要一些经验和调优。

7. 过多的索引可能会影响性能： 过多地创建索引可能会导致查询优化器混乱，影响整体性能，甚至可能导致性能下降。

8. 不适用于所有查询： 不是所有类型的查询都能从索引中获益，有些查询可能由于多个索引的存在而变得更加复杂和低效。

9. 索引统计数据更新成本： 数据库需要维护索引的统计信息，以便优化查询计划，这也会带来一定的成本。

因此，在使用索引时需要根据具体的应用场景和需求来进行权衡和设计。选择合适的索引、避免过多地创建索引以及定期进行性能监测和调优，可以最大程度地减少索引带来的负面影响。

应用场景

在 MySQL 中，索引可以用于加速多种数据库操作，包括：

1. 等值查询（=）： 使用索引可以快速定位满足等值条件的数据行，提高查询效率。

2. 范围查询（<、>、BETWEEN、IN）： 索引可以加速范围查询，帮助数据库定位满足指定范围条件的数据。

3. 排序（ORDER BY）： 使用索引可以加速排序操作，从而提高排序性能。

4. 分组（GROUP BY）： 索引可以帮助优化 GROUP BY 操作，加速数据分组计算。

5. 连接（JOIN）： 索引可以加速连接操作，特别是在连接列上创建索引，可以显著提高连接性能。

6. 唯一性约束： 主键索引和唯一索引可以确保数据的唯一性，避免插入重复数据。

7. 外键约束： 外键索引可以保证表之间的引用完整性，支持关联查询。

8. 全文搜索： 全文索引可以加速针对文本字段的全文搜索，支持高级的文本匹配。

9. 聚合函数（SUM、AVG、COUNT 等）： 索引可以加速聚合函数的计算，提高聚合操作性能。

10. 子查询： 索引可以在子查询中加速数据检索，提高子查询性能。

B+树

索引分类

在 MySQL 中，聚簇索引（Clustered Index）、非聚簇索引（Non-clustered Index）和联合索引（Composite Index）是数据库中不同类型的索引，用于优化查询性能和数据访问。

1. 聚簇索引（Clustered Index）：

   • 聚簇索引是数据行的物理排序顺序，表中的数据按照聚簇索引的顺序存储在磁盘上。每个表只能有一个聚簇索引。
   • 聚簇索引的叶子节点包含实际的数据行，也就是叶子节点存储实际的数据，因此聚簇索引的查询速度非常快。范围查询、排序和覆盖索引查询在聚簇索引上性能较好。
   • 通常将主键定义为聚簇索引，从小到大构成单向链表，如果没有主键，则选择一个唯一非空索引作为聚簇索引。如果表没有合适的候选键，则会生成一个隐藏的行标识符（Row ID）作为聚簇索引。

   缺点：

   1. 插入和更新代价较高： 聚簇索引的叶子节点包含实际的数据行，当插入新数据或更新数据时，可能需要调整索引的结构，导致额外的性能开销，一般都是定义自增的id作为主键，而不更新主键。

   2. 不适合频繁更新的表： 如果表经常进行插入、更新和删除操作，聚簇索引的维护代价会变得较高，可能影响性能。

   3. 碎片问题： 聚簇索引可能会导致碎片问题。删除或更新数据后，数据行可能不再连续存储，导致物理碎片，影响性能和存储效率。

   4. 无法覆盖索引查询： 聚簇索引的叶子节点包含实际数据，因此无法在聚簇索引上进行覆盖索引查询，可能需要额外的数据页访问。

   5. 不适合范围删除： 聚簇索引不太适合范围删除操作，因为需要移动数据行，可能导致性能下降。

   6. 主键约束限制： 聚簇索引通常与主键相关联，如果表中没有合适的主键，可能会影响聚簇索引的选择。

   7. 不适合非唯一索引： 如果一个表中需要多个非唯一索引，选择哪个索引作为聚簇索引可能需要权衡，不同的查询模式可能会导致性能差异。

   需要根据具体的应用需求和查询模式来评估是否使用聚簇索引。在一些情况下，非聚簇索引可能更适合，特别是在数据频繁更新、删除和大量范围查询的场景中。

2. 非聚簇索引（Non-clustered Index）：

   1. 也被称为二级索引、辅助索引
   2. 非聚簇索引是基于表的某列的值构建的索引，但不会改变数据行的物理排序顺序。一个表可以有多个非聚簇索引。
   3. 非聚簇索引的叶子节点包含索引列的值以及指向对应数据行的指针（或行标识符）。因此，在覆盖索引查询时，需要额外的数据页访问。
   4. 非聚簇索引适用于查找特定值或范围的查询，但对于范围查询和排序等操作，性能可能相对聚簇索引较低。

3. 联合索引（Composite Index）：

   • 联合索引是基于多个列的值构建的索引，用于优化多列条件的查询。它可以包含多个列，按照从左到右的顺序逐级排序，也就是在存储时，数据域中存储了多个字段。
   • 联合索引可以加快多个列条件的查询和范围查询，但需要注意索引列的顺序，以匹配常见的查询模式。
   • 联合索引也可以用于覆盖索引查询，当索引列涵盖了查询所需的列时，可以避免额外的数据页访问。

在设计索引时，根据实际的查询需求和性能要求，可以选择适当的索引类型。聚簇索引适用于频繁进行范围查询和排序的情况，非聚簇索引适用于快速查找特定值或范围的查询，联合索引适用于多列条件的查询优化。

回表

回表（Lookup）是数据库查询中的一个概念，它指的是在使用索引进行查询时，当索引无法满足查询需求时，需要再次访问数据表的过程。具体来说，回表发生在非聚簇索引（或覆盖索引）查询时，当索引列无法完全覆盖查询所需的数据列时，数据库需要通过索引中的指针或行标识符，再次访问实际的数据页获取所需的数据行。

举例来说，假设有一个包含用户信息的表，其中有一个非聚簇索引建立在用户名（username）列上。当执行如下查询时：

SELECT user_id FROM users WHERE username = 'john';

如果只有用户名列建立了索引，那么数据库首先会使用索引查找到满足条件的行的位置，然后需要通过索引中的指针或行标识符再次访问数据表，获取该行中的 user_id 列的值。这个访问数据表的过程就称为回表操作。

回表操作可能会导致额外的磁盘 I/O 操作，从而影响查询性能。为了减少回表的发生，可以考虑创建覆盖索引，即索引包含了查询中所需的所有列，这样就可以避免回表操作，提高查询性能。

插入过程

1. 创建B+树索引的根节点： 当为某个表创建B+树索引时，会为这个索引创建一个根节点页面。在索引创建的最开始阶段，当表中还没有数据时，根节点页面既没有用户记录（数据行），也没有目录项记录（索引项）。此时，根节点页面会被用作索引的起始节点，也就是往根节点放数据。

2. 插入用户记录： 当向表中插入用户记录时，InnoDB会尝试将这些记录存储在根节点页面上，作为初始的数据存储。这是一个优化策略，旨在避免频繁的数据页分裂，因为刚开始插入的数据量可能不大。

3. 根节点页空间用尽和分裂： 随着插入操作的继续，根节点页的空间可能会耗尽。当发生这种情况时，会进行根节点页的分裂。具体过程如下：

   • 所有根节点中的记录会被复制到一个新的页（例如，页a）。
   • 对新页（页a）进行页分裂，生成另一个新页（例如，页b）。
   • 新插入的记录根据键值大小被分配到页a和页b中。

4. 目录项记录的插入： 随着根节点页升级成存储目录项记录的页，页a和页b对应的目录项记录会被插入到根节点页面中。目录项记录包含了指向实际数据页的指针，用于快速定位数据。

5. 根节点页号的固定性： 一旦B+树索引的根节点创建后，其页号就不会再变化。这是因为根节点页号被记录在某个固定的地方，以便在以后访问这个索引时可以快速找到根节点，从而遍历整个B+树索引。

InnoDB

InnoDB是MySQL数据库管理系统中的一种存储引擎，它有一套独特的数据存储结构，旨在提供高性能、可靠性和事务支持。以下是InnoDB存储结构的主要组成部分：

1. 表空间（Tablespaces）： InnoDB数据库存储在一个或多个表空间中。每个表空间对应一个物理文件，用于存储表和索引数据。InnoDB支持多表空间，可以将不同的表存储在不同的物理文件中，从而实现更好的管理和维护。

2. 页（Pages）： InnoDB的数据存储以页为基本单位，一个页的大小通常为16KB。表数据、索引数据和其他元数据都以页为单位进行存储和管理。InnoDB会自动管理页的分配和释放，以及内存中的页缓存。

3. 行记录（Row Records）： 表中的数据以行记录的形式存储在页中。每一行记录都包含了数据列的实际值，以及可能的版本信息（用于事务隔离级别）和其他控制信息。

4. 索引（Indexes）： InnoDB支持B+树索引结构，用于加速数据的查询和访问。每个索引都有自己的B+树，包括主键索引（聚簇索引）和辅助索引（非聚簇索引）。

5. 聚簇索引（Clustered Index）： InnoDB的表数据按照主键的顺序存储在聚簇索引中。聚簇索引决定了表数据在磁盘上的物理存储方式，因此表的主键设计对性能影响较大。

6. 非聚簇索引（Secondary Index）： 除了主键索引外，InnoDB还支持非聚簇索引，用于加速基于非主键列的查询。非聚簇索引的叶子节点存储了键值和指向对应行记录的引用。

7. 事务日志（Transaction Logs）： InnoDB通过事务日志（Redo Log和Undo Log）来实现事务的持久性和隔离性。Redo Log记录了数据修改操作，Undo Log用于事务的回滚和MVCC实现。

8. MVCC（Multi-Version Concurrency Control）： InnoDB通过MVCC机制支持不同的事务隔离级别。它在每个行记录上维护多个版本，从而允许并发事务的执行而不会产生冲突。

9. 缓冲池（Buffer Pool）： InnoDB使用缓冲池来管理磁盘和内存之间的数据交换。缓冲池中存放了页的副本，以提高热数据的访问速度。

10. 锁（Locking）： InnoDB支持行级锁，可以实现更细粒度的并发控制，以提高并发性能和事务隔离级别。

InnoDB的存储结构被设计为支持高性能的并发数据访问、事务处理和持久性。它是MySQL中最常用的存储引擎之一，广泛用于支持事务性应用和高并发场景。

页

在InnoDB存储引擎中，页（Page）是数据的最小存储单位，也是数据在磁盘和内存之间进行传输的基本单元。每个页的大小通常为16KB（可以根据配置进行调整），InnoDB使用页来组织表数据、索引、元数据以及其他管理信息。

在InnoDB存储引擎中，数据页和索引页之间采用的是双向链表来进行连接。这种双向链表通常被称为"Latched Doubly Linked List"（锁定的双向链表），用于维护数据页和索引页的逻辑顺序和连接关系。

具体来说，InnoDB中的数据页和索引页都按照其键值的顺序连接成一个双向链表。每个页都有指向前一个页和后一个页的指针，从而形成一个链表结构。这种链表连接的方式使得范围查询等操作更加高效，可以避免不必要的磁盘I/O操作。

表空间

一个表空间包含多个段，一个段包含多个区，一个区包含64个页

• 引入区的目的是让多个页能够在磁盘上连续，主要是利于顺序IO
• 引入段的目的是为了区分叶子结点和非叶子结点，将所有的叶子结点放到一个区中，非叶子结点也放到一个区

唯一索引和普通索引

普通索引和唯一索引在数据库中具有不同的特性和用途，同时也会影响查询效率。以下是普通索引和唯一索引的区别以及对查询效率的影响：

区别：

1. 唯一性： 唯一索引要求索引列中的值在整个表中是唯一的，而普通索引则没有这个限制，允许重复的值。

2. NULL值： 唯一索引可以包含一个NULL值，但只能包含一个，而普通索引可以包含多个NULL值。

3. 约束： 唯一索引不仅提供了索引的快速访问，还强制了数据的唯一性约束。普通索引只提供了快速访问，不强制数据的唯一性。

查询效率上的不同：

1. 查找和等值查询： 在执行等值查询时，唯一索引能够快速定位到特定的记录，因为它保证了值的唯一性。普通索引也可以加速等值查询，但可能需要在多个匹配的记录中进一步进行比较。

2. 范围查询和排序： 对于范围查询和排序操作，普通索引和唯一索引的性能类似。它们都可以加速范围查询和排序操作，但唯一索引可能因为数据的唯一性特性而稍微更快一些。

3. 插入、更新和删除： 在插入、更新和删除操作时，唯一索引的性能可能会略低于普通索引，因为唯一索引需要确保新插入或更新的数据不会违反唯一性约束。而普通索引只关注加速数据访问，不需要额外的唯一性验证。

综合考虑，唯一索引适用于需要确保数据唯一性的列，同时也可以提高等值查询的效率。普通索引适用于加速数据的查询、范围查询和排序操作，但不会强制数据唯一性。在选择索引类型时，需要根据具体的业务需求和数据操作来权衡各种因素。

行格式

InnoDB存储引擎支持多种行格式，行格式决定了存储在数据库表中的数据如何进行组织、压缩和存储。不同的行格式可以影响存储空间的利用效率、读取性能、存储结构和数据压缩。以下是InnoDB支持的主要行格式及其区别：

1. Compact行格式： 它以紧凑的方式存储数据，将空间占用最小化。Compact行格式的特点包括：

   • 使用可变长度的字段存储，减少了空间浪费。
   • 每个记录包含一个20字节的头信息，用于存储记录的长度和NULL值的标识。
   • 压缩存储列的值，如日期和整数。
   • 不保存旧版本的数据，适用于多版本并发控制（MVCC）环境。

2. Redundant行格式： Redundant行格式是InnoDB的旧版行格式，已经不再推荐使用。它的特点包括：

   • 每个记录包含一个6字节的头信息，比Compact行格式的头信息更大。
   • 保存了历史版本的数据，导致存储空间占用更多。
   • 不支持行数据压缩。

3. Dynamic行格式： 是InnoDB的默认行格式，Dynamic行格式在存储和处理数据时更加灵活，可以根据实际数据进行动态调整。其特点包括：

   • 每个记录包含一个可变长度的头信息，根据记录的字段数量和属性进行调整。
   • 支持行数据压缩，可以减小存储空间占用。
   • 不保存旧版本的数据，适用于MVCC环境。

每种行格式在不同场景下有不同的优势和适用性。选择适当的行格式可以根据表的使用情况、数据特点和性能要求来进行权衡。可以通过在创建表时使用ROW_FORMAT选项来指定所需的行格式，例如：

CREATE TABLE my_table (
    ...
) ENGINE=InnoDB ROW_FORMAT=Compact;

下表列出了InnoDB存储引擎支持的三种行格式（Compact、Redundant、Dynamic）的主要区别：

特性	Compact 行格式	Redundant 行格式	Dynamic 行格式
记录头信息大小	20 字节	6 字节	可变
空间占用	小	大	中等
空间浪费	少	多	适中
压缩存储	是	否	是
保存旧版本数据（MVCC）	否	是	否
动态头信息	否	否	是

分类

索引是数据库中用于加速数据检索操作的数据结构，可以提高查询性能。根据索引的不同特点和用途，可以将索引分为以下几种主要的分类：

1. 主键索引（Primary Key Index）： 主键索引是一种唯一性索引，用于标识表中的唯一记录。主键索引在数据库表中只能有一个，它可以加速对主键的精确查找。

2. 唯一索引（Unique Index）： 唯一索引保证了被索引列的值在表中是唯一的，但允许有空值。它可以加速对唯一性约束字段的查找，类似于主键索引，但可以存在多个唯一索引。

3. 普通索引（Non-Unique Index）： 普通索引是最常见的索引类型，用于加速对列的查找操作。它允许相同的值出现多次，可以在不违反唯一性约束的情况下提高查询性能。

4. 全文索引（Full-Text Index）： 全文索引用于对文本字段中的内容进行全文搜索，而不仅仅是精确匹配。它支持高效地进行全文搜索和模糊匹配。

5. 空间索引（Spatial Index）： 空间索引用于存储和查询空间数据，如地理位置坐标。它支持地理位置相关的查询操作，如范围查询、距离查询等。

6. 复合索引（Composite Index）： 复合索引是由多个列组合而成的索引，用于加速多列条件的查询。复合索引可以覆盖多个列的查询需求，但需要权衡查询性能和索引大小。

7. 哈希索引（Hash Index）： 哈希索引通过对索引列进行哈希计算来加速查找操作。它适用于精确匹配的查询，但不支持范围查询和排序操作。

不同类型的索引适用于不同的场景和查询需求。选择合适的索引类型需要根据具体的数据库结构、数据特点和查询模式进行权衡。使用索引可以提高查询性能，但也需要注意索引的维护成本和对数据写操作的影响。

查看索引

show index from 表名

创建索引

在创建表时：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    [UNIQUE | FULLTEXT] INDEX index_name (column_name1, column_name2, ...),
    PRIMARY KEY (column_name)
);

手动创建：

CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [asc|desc], column2 [asc|desc], ...) [invisible];

• 默认是升序索引，8.0支持降序索引，不指定默认是升序索引
• invisible代表隐藏索引，可以理解为软删除，也就是查询时不使用索引，经常用来评估索引是否有效

FULLTEXT索引：全文索引不支持所有的数据类型，仅适用于字符型字段

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT (title, content)
) ENGINE=InnoDB;

也可以指定建立索引的字符数量

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT (title(20), content)
) ENGINE=InnoDB;

删除索引

drop index 索引名 on 表名;

适合创建索引的情况

建议单张表的索引个数不超过6个

在MySQL中，适合创建索引的情况通常包括以下几种：

1. 主键字段： 用于唯一标识表中的每一行数据，必须是唯一的。主键默认会创建一个聚簇索引，提高查询性能。

2. **具有唯一性限制的字段：**有的字段具有唯一性的特点，但没有加索引。业务上具有唯一特性的字段，即使是组合字段，也必须加唯一索引。

3. 外键字段： 用于关联其他表的数据，提高连接操作的性能。

4. 经常出现在WHERE子句中的列： 在经常进行过滤条件的列上创建索引，加速查询操作，包括UPDATE、DELETE后的WHERE。

5. **DISTINCT标注的字段 **

6. 使用频繁的列放在联合索引的左侧（最左前缀）：

7. 经常用于连接操作的列： 在用于JOIN操作的列上创建索引，提高连接查询的性能。

8. 经常用于排序的列： 在用于ORDER BY子句的列上创建索引，提高排序操作的性能。

9. 经常用于分组、聚合、排序操作的列： 在用于GROUP BY和聚合函数的列上创建索引，加速分组和聚合操作。

10. 复合索引： 对于多个列组合的查询，可以创建复合索引，提高多条件查询的性能。

11. 覆盖索引： 在查询中包含所有需要的字段时，可以创建覆盖索引，减少回表操作。

12. 全文搜索列： 对于包含文本内容的列，可以使用全文索引来进行高效的文本搜索。

13. 区分度高的列： select count(distinct 字段) / count(*) from 表计算区分度，一般大于33%就算比较好的，越接近1越好

14. 对字符串前缀建立索引：对字符串进行建立索引时，必须要指明长度

15. **多个字段都需要索引的情况下，联合索引优于单值索引：**联合索引是同时包含多个列的索引，它可以优化多列条件查询、排序、连接和分组等操作。通过将多个字段组合成一个联合索引，可以减少索引的数量，从而降低存储开销，并且可以在单个索引中同时满足多个查询需求

需要注意的是，虽然索引可以提高查询性能，但过多或不恰当的索引会导致性能下降和存储空间浪费。在设计索引时，需要根据具体的查询需求和数据模式进行权衡和优化。对于某些列，可能需要通过测试和分析来确定是否创建索引以及选择何种类型的索引。

最左前缀

是的，将使用频繁的列放在联合索引的左侧（最左前缀）是一种常见的优化策略，可以提高查询性能。这是因为在MySQL中，联合索引的最左前缀可以有效地支持查询和排序操作。

在一个表上创建联合索引时，查询条件和排序操作通常只会使用索引的左边一部分，即最左前缀。如果将最常用于过滤、连接和排序的列放在联合索引的左侧，可以减少索引的深度，从而加速查询。

例如，考虑以下查询：

SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2023-01-01';

如果你在orders表上创建了一个联合索引 (customer_id, order_date)，并将customer_id放在左侧，那么这个索引可以高效地支持上述查询，因为customer_id在查询条件中是最左前缀。

不适合创建索引的情况

1. 没有在where出现过的列
2. 数据量小的表： 对于小表，创建索引可能没有太大的性能提升，反而会增加存储开销。一般1000行以下不适合创建索引
3. 大量重复数据的列不适合索引，例如性别，重复度高于10%的不适合索引
4. 经常更新的表不适合大量索引，经常更新字段不适合索引
5. 无序的值不适合索引
6. 不要定义冗余和重复索引

优化

MySQL慢查询日志是用于记录执行时间超过一定阈值的查询语句的日志。通过分析慢查询日志，可以帮助定位和优化数据库性能问题。以下是设置和查看MySQL慢查询日志的方法：

查看慢查询的设置：

show status like '%query%';
show variables like '%slow%';
show global status like '%Slow%';

设置慢查询日志：

1. 打开MySQL配置文件（通常是my.cnf或my.ini）。

2. 找到并编辑[mysqld]部分，添加以下配置项：

   slow_query_log = 1
   slow_query_log_file = /path/to/slow-query.log
   long_query_time = 2   # 查询执行时间超过2秒的被记录到日志（可根据需要调整）
   

3. 保存文件并重启MySQL服务器，使配置生效。

查看慢查询日志：

1. 登录到MySQL服务器。

2. 运行以下命令查看慢查询日志的路径：

   SHOW VARIABLES LIKE 'slow_query_log_file';
   

3. 使用文本编辑器打开慢查询日志文件，可以查看其中记录的慢查询语句和执行时间。

另外，还可以使用mysqldumpslow工具来分析和汇总慢查询日志，以便更好地理解和优化慢查询问题。例如，可以运行以下命令查看最耗时的慢查询语句：

mysqldumpslow -s t /path/to/slow-query.log

需要注意的是，慢查询日志会记录执行时间超过阈值的查询语句，因此可能会涵盖一些本身不一定是问题的查询。在使用慢查询日志时，需要结合业务需求和性能分析，逐步优化数据库性能。

另外，定期清理慢查询日志，避免日志文件过大影响磁盘空间。

EXPLAIN

EXPLAIN 是 MySQL 中一个非常有用的命令，用于分析查询语句的执行计划，帮助你理解查询的执行方式、使用的索引以及可能的性能问题。通过查看 EXPLAIN 的输出，你可以深入了解 MySQL 是如何处理你的查询，从而进行性能优化。

EXPLAIN 命令的语法如下：

EXPLAIN SELECT columns FROM table WHERE conditions;

其中，SELECT columns FROM table WHERE conditions; 是你要分析的查询语句。

EXPLAIN 的输出结果包含了一些重要的列，以下是其中一些常见的列及其含义：

• id: 查询的唯一标识符，对于复杂查询，多个查询步骤会有不同的标识符。

  • id数量越少越好
  • 如果id相同，代表为一组

• select_type: 查询的类型，例如 SIMPLE、PRIMARY、SUBQUERY 等。

  • SIMPLE: 简单的 SELECT 查询，不包含子查询或联合查询。
  • PRIMARY: 主查询，通常是复杂查询的最外层。
  • SUBQUERY: 子查询，在其他查询中作为子查询执行。
  • DERIVED: 派生表，通过从子查询派生出的临时表执行。
  • UNION: UNION 查询的第二个或后续查询。
  • UNION RESULT: UNION 查询的结果。
  • DEPENDENT SUBQUERY: 依赖的子查询，子查询的结果会影响外部查询。
  • DEPENDENT UNION: 依赖的联合查询，联合查询的结果会影响外部查询。
  • UNCACHEABLE SUBQUERY: 不可缓存的子查询，结果不会被缓存。
  • UNCACHEABLE UNION: 不可缓存的联合查询，结果不会被缓存。
  • MATERIALIZED: 使用临时表来存储子查询的结果。
  • PRIMARY SUBQUERY: 在联合查询中，作为主查询的子查询。
  • DEPENDENT PRIMARY SUBQUERY: 依赖的主查询子查询。
  • DEPENDENT UNION SUBQUERY: 依赖的联合查询子查询。

• table: 查询涉及的表。

• type: 访问表的方式，包括 ALL、index、range 等，代表从表中扫描的方式，sql优化的目标至少哟啊达到range级别，从好到坏：

  • const: 使用唯一索引或主键查找一行数据，常用于在 WHERE 子句中使用常量进行等值匹配。

    • select ... from xxx where id = 常数;
      

  • eq_ref: 使用唯一索引或主键进行连接

    • select ... from r join u on r.user_id = u.user_id;
      

  • ref: 使用非唯一索引(普通索引)进行查找，可能返回多个匹配的行。

  • fulltext: 使用全文索引进行全文搜索。

  • ref_or_null: 类似于 ref，但还包括对 NULL 值的检查。

    • select ... from y where name = 'adddd' or name is null
      

  • index_merge: 对多个索引进行合并扫描，然后使用 OR 连接结果。

    • # 此时name和username都添加了索引
      select ... from y where name = 'adddd' or username = '2' ;
      

  • unique_subquery: 在子查询中使用唯一索引或主键。

  • index_subquery: 在子查询中使用非唯一索引。

  • range: 使用索引范围进行查找，例如使用 BETWEEN、IN 等操作。

    • select ... from a where id > 10 and id < 999;
      select ... from a where id = 10 or id = 999;
      select ... from a where id in (10, 99);
      

  • index: 对索引进行全表扫描，通常对小表或需要返回大部分数据的查询使用。

    • # a和b是联合索引
      select a, b from c where b = 'aaaa';
      

  • all: 全表扫描，对于大表或没有使用索引的查询使用。

• possible_keys: 可能使用的索引。

• key: 实际使用的索引。

• key_len: 使用的索引长度，自己和自己比越大越好，针对联合索引比较有参考意义。

• ref: 使用索引进行查找的引用列。

• rows: 估计的查询返回的行数，越小越好。

• filtered：代表某个表经过搜索条件过滤后剩余条数的百分比

• Extra: 额外的信息，可能包括 Using filesort、Using temporary 等。

  • Using index: 查询使用了** **，即查询的所有需要的列都包含在了索引中，避免了回表操作。
  • Using where: 查询使用了 WHERE 子句进行过滤。
  • Using temporary: 查询需要创建临时表来处理一些操作，可能涉及排序、分组等。
  • Using filesort: 查询需要进行文件排序操作，可能会导致性能问题。
  • Using join buffer: 查询使用了连接缓冲区。
  • Distinct: 查询使用了 DISTINCT 关键字去重。
  • Range checked for each record: 查询对每条记录都进行了范围检查。
  • Using index condition: 查询使用了索引条件来过滤数据。
  • Using where; Using index: 同时使用了 WHERE 子句进行过滤和覆盖索引。
  • Using index; Using join buffer: 同时使用了索引和连接缓冲区。
  • Impossible WHERE: 查询的 WHERE 子句总是返回 false，因此不会返回任何结果。

使用索引遵循的规则

覆盖索引

覆盖索引（Covering Index），也称为索引覆盖，是一种特殊类型的索引设计，它可以显著提高查询性能。当一个查询所需的数据可以从索引本身获取，而不需要去主表中读取实际的数据行，就称为覆盖索引。这可以减少磁盘I/O操作，从而提高查询效率。

覆盖索引的关键在于，索引不仅包含索引列的值，还包含了查询所需的其他列的值。这样，在满足查询条件的情况下，查询可以直接从索引中获取所需的数据，而不需要访问主表中的实际数据行。

以下是覆盖索引的一些特点和优势：

1. 减少磁盘I/O操作： 通过避免从主表中读取数据行，覆盖索引可以显著减少磁盘I/O操作，从而提高查询性能。

2. 减少内存消耗： 查询所需的数据可以直接从索引中获取，这可以减少内存中缓存数据的需求。

3. 提高查询性能： 由于减少了磁盘和内存的访问，覆盖索引通常可以显著提高查询性能，特别是在大表上的查询。

4. 注意事项： 覆盖索引适用于查询涉及少量的列，而不是需要检索整个表的所有列。创建过多的覆盖索引可能会导致维护开销增加和额外的存储空间消耗。

覆盖索引在优化查询性能时非常有用，特别是对于需要从大表中检索特定列的查询。当设计索引时，可以考虑包含查询所需的列，以使索引成为覆盖索引，从而提高查询效率。

最左前缀法则

最左前缀法则是指在联合索引（复合索引）中，索引的列顺序非常重要。它表示如果一个联合索引包含多个列，那么查询条件中使用的列必须遵循索引列的顺序，从左到右逐个使用。这样的查询才能充分利用联合索引，否则索引可能无法被有效使用。

最左前缀法则的核心思想是，只有在索引的最左边的列开始匹配，才能利用索引加速查询。一旦某一列不再匹配，查询就不能继续使用索引，而需要扫描更多的数据行。这意味着如果查询条件涉及到索引的后续列，那么索引的效果将会减弱。

以下是最左前缀法则的一些要点：

1. 联合索引顺序： 联合索引中列的顺序非常重要，要根据最常见的查询需求来选择列的顺序。

2. 优化查询顺序： 查询条件中的列应该按照联合索引的顺序提供，这样查询可以充分利用索引。

3. 左边列先匹配： 查询的列从索引的最左边开始匹配，只有匹配到的列会被用来过滤数据。

4. 避免冗余索引： 如果已经有一个联合索引，不需要再为其子集创建单独的索引。因为联合索引的最左前缀法则已经可以满足子集的查询需求。

5. 注意性能权衡： 虽然遵循最左前缀法则可以加速一些查询，但也可能导致索引变得较长，增加存储开销。

最左前缀法则的理解可以帮助你更有效地设计索引，提高查询性能。根据实际的查询需求，选择合适的索引列顺序是一个需要权衡的过程。

范围比较

将需要进行范围查询的字段，在建立索引时放到最后

假设有以下联合索引：

create index idx_t on t(age, salary, name);

以下查询语句：

select id, age, salary, name from t where age = 30 and salary > 4000 and name = '小明'

此时只有age、salary可以用上索引，name用不上索引，失效的原因：因为根据范围查找筛选后的数据，无法保证范围查找后面的字段是有序的

如果联合索引为：

create index idx_t on t(age, name, salary);

此时都能使用索引

索引失效

索引失效是指在某些情况下，查询优化器无法使用索引来加速查询，从而导致查询性能下降。以下是一些导致索引失效的情况、原因以及相应的SQL语句：

1. 模糊查询的模式以通配符开头：

   • 原因：在模糊查询中，如果通配符在查询模式的开头，索引无法有效使用。

   • 示例：

     SELECT * FROM customers WHERE last_name LIKE '%Smith';
     

2. 进行运算：

   • explain select id from ysa_resources where id + 1 = 75;
     

3. 使用函数或表达式：

   • 原因：在查询中使用函数或表达式，会导致索引失效，因为函数可能改变了索引列的值。

   • 示例：

     SELECT * FROM orders WHERE YEAR(order_date) = 2023;
     

4. 使用 NOT 和 != 运算符：

   • 原因：使用 NOT 或者!=运算符会导致普通索引失效，某些情况下等同于全表扫描。

   • 示例：

     SELECT * FROM products WHERE NOT in_stock = 0;
     

5. is null可以使用索引，is not null不能使用索引：

   • 因为这个原因，所以最好将所有的字段都设置为not null，并且给一个默认值

6. 范围查询中的左侧不等式：

   • 原因：在范围查询中，如果不等式在索引列的左侧，索引可能无法被使用。

   • 示例：

     SELECT * FROM sales WHERE sale_date > '2023-01-01';
     

7. 使用 OR 运算符前后存在非索引的列：

   • 原因：在 OR 运算符连接多个条件时，只要出现没有建立索引的列，都会进行全表扫描。

   • 示例：

     SELECT * FROM customers WHERE country = 'USA' OR state = 'CA';
     

8. 数据类型不匹配：

   • 原因：如果索引列的数据类型与查询条件不匹配，索引可能无法使用。

   • 示例：

     SELECT * FROM employees WHERE emp_id = '123';
     

9. 表太小：

   • 原因：对于非常小的表，使用索引可能不会带来性能提升，直接扫描整个表可能更快。
   • 示例：对于一个仅有几条记录的表进行查询。

10. 隐式数据类型转换：

    • 原因：如果查询中存在隐式数据类型转换，索引可能无法使用。

    • 示例：

      SELECT * FROM products WHERE product_id = '123';
      

避免索引失效的关键在于理解索引的工作原理，设计合适的查询条件和索引，以及优化查询语句。使用 EXPLAIN 命令可以帮助你分析查询执行计划，从而了解是否出现了索引失效的情况。

JOIN

连接的字段如果有索引，两个字段的类型要一致，否则索引索引会失效

在数据库中，“驱动表”（Driving Table）和"被驱动表"（Driven Table）是在连接操作（如 JOIN）中的概念。它们描述了连接操作中参与连接的不同表之间的角色。

• 驱动表（Driving Table）： 驱动表是连接操作中起主导作用的表，它是连接操作的起点。在连接操作中，数据库引擎会首先选择驱动表中的每一行，然后根据连接条件去匹配被驱动表中的相应行。驱动表的每一行都会参与连接，以确定需要获取的数据。

• 被驱动表（Driven Table）： 被驱动表是连接操作中由驱动表的每一行匹配出来的数据来源。被驱动表中的数据将根据连接条件与驱动表中的数据进行匹配，以获取最终查询结果中需要的数据。如果驱动表和被驱动表连接的字段只能加在一个表上的话，被驱动表的连接字段加索引更好。

在执行连接操作时，驱动表通常是那个较小的表，因为通过将小表作为驱动表，可以减少连接操作的数据量，从而提高查询性能。被驱动表则可能是那些包含较多数据的表。

需要注意的是，在执行连接操作时，数据库引擎会根据查询条件、索引、数据分布等因素来选择驱动表和被驱动表，以最优化地处理连接操作。驱动表和被驱动表的选择会影响查询的执行计划和性能。

假设有两个表：orders 表和 customers 表，分别存储订单信息和客户信息。我们要查询某个客户的所有订单信息。以下是一个示例 SQL 查询，并结合这个查询解释驱动表和被驱动表：

SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE customers.customer_id = 123;

在这个查询中，customers 表和 orders 表进行了连接，查询客户ID为 123 的客户的订单信息。

• 驱动表： 在这个查询中，customers 表是驱动表。这是因为查询的目的是获取客户的订单信息，而我们首先需要确定客户的信息，然后根据客户的信息去匹配订单信息。所以，customers 表是起主导作用的表，它是连接操作的起点。

• 被驱动表： 在这个查询中，orders 表是被驱动表。一旦确定了客户ID为 123，数据库引擎将使用连接条件 customers.customer_id = orders.customer_id 来匹配 orders 表中相应客户ID的订单信息，获取所有匹配的订单数据。

总结：在这个查询中，customers 表是驱动表，它是连接操作的起点，而 orders 表是被驱动表，它是根据驱动表中的客户信息匹配出来的订单数据来源。这种连接方式通常可以避免处理大量的冗余数据，从而提高查询效率。

小表驱动大表

“小表驱动大表” 可以减少连接操作的数据量，是因为在连接操作中，驱动表的数据量相对较小，只需要对较少的数据进行连接匹配，从而减少了需要处理的数据量。这有助于提高查询的性能和效率。

让我们通过一个简单的示例来解释这个概念：

假设有两个表：students 表和 scores 表，分别存储学生信息和考试成绩。我们想要查询每个学生的考试成绩。以下是一个示例 SQL 查询：

SELECT students.name, scores.score
FROM students
JOIN scores ON students.id = scores.student_id;

在这个查询中，students 表和 scores 表进行了连接，查询每个学生的考试成绩。

• 如果我们将 students 表作为驱动表，它可能只有几十个学生的信息，而 scores 表可能有数千条记录的考试成绩数据。在这种情况下，连接操作将以 students 表的记录为基础，根据每个学生的ID匹配相应的考试成绩数据。由于 students 表的数据量相对较小，连接操作的数据量也较小，性能会更好。

• 如果我们将 scores 表作为驱动表，那么连接操作会以考试成绩为基础，根据每个考试成绩的学生ID匹配学生信息。这可能会导致连接操作需要处理更多的数据，从而影响性能。

因此，将小表作为驱动表可以在连接操作中减少要处理的数据量，从而提高查询性能。这个优化原则适用于大多数情况，但在具体场景中仍需根据数据分布、查询条件等综合考虑来选择驱动表。

优化器会对连接的两个表进行优化，从而确定驱动表和被驱动表

JOIN算法

简单嵌套循环连接、索引嵌套循环连接、块嵌套循环连接以及 Hash Join 都是数据库中用于处理连接操作的算法。它们在不同的情况下具有不同的优势和适用性。下面对这些连接算法进行对比：

1. 简单嵌套循环连接（Nested Loop Join）：

   • 适用场景：当一个表非常小，而另一个表非常大时，可以选择简单嵌套循环连接。小表作为驱动表，大表作为被驱动表。
   • 执行过程：对于驱动表中的每一行，在被驱动表中寻找匹配的行。因为需要多次对被驱动表进行访问，性能可能较低。
   • 优点：适用于小表驱动大表的情况，不需要额外的索引。
   • 缺点：性能受到大表的影响，可能导致性能瓶颈。

2. 索引嵌套循环连接（Index Nested Loop Join）：

   • 适用场景：当连接字段在一个表上有索引时，可以选择索引嵌套循环连接。一般用于驱动表是小表，被驱动表是大表的情况。
   • 执行过程：类似于简单嵌套循环连接，但在被驱动表上使用索引来查找匹配行，从而提高性能。
   • 优点：适用于连接字段有索引的情况，可以提高性能。
   • 缺点：性能仍受到大表的影响。

3. 块嵌套循环连接（Block Nested Loop Join）：

   • 适用场景：在内存中可以容纳块大小的情况下，可以选择块嵌套循环连接。适用于中等大小的表连接。
   • 执行过程：将大表划分成多个块，每次将一个块加载到内存中，然后在内存中进行连接操作。
   • 优点：相较于简单嵌套循环连接，可以减少磁盘I/O，适用于中等大小的表。
   • 缺点：仍然可能受到大表的影响。

4. Hash Join：

   • 适用场景：适用于大表连接，适用于内存中无法容纳整个表的情况。
   • 执行过程：将驱动表和被驱动表中的连接字段值映射到一个哈希表中，然后根据哈希表中的值进行连接。
   • 优点：适用于大表连接，可以减少磁盘I/O，适用于内存有限的情况。
   • 缺点：需要构建哈希表，可能需要大量内存，对内存的消耗较大。

在实际场景中，选择适合的连接算法要根据数据分布、表的大小、索引情况以及系统资源等综合考虑。不同的情况可能需要选择不同的连接算法来达到最佳的性能。

下面是简单嵌套循环连接、索引嵌套循环连接、块嵌套循环连接和 Hash Join 这四种连接算法的表格对比：

算法	适用场景	执行过程	优点	缺点
简单嵌套循环连接	小表驱动大表，表尺寸差异较大	对驱动表中的每一行，在被驱动表中查找匹配的行	适用于小表驱动大表，不需要额外索引	受大表影响，性能可能低
索引嵌套循环连接	驱动表有索引，小表驱动大表，表尺寸差异较大	对驱动表中的每一行，在被驱动表上使用索引查找匹配行	适用于连接字段有索引，性能较好	受大表影响，性能可能低
块嵌套循环连接	中等大小的表连接，内存可以容纳表块	将大表划分成块，每次加载块到内存，进行连接操作	减少磁盘I/O，适用于中等大小表连接	仍然可能受大表影响
Hash Join	大表连接，内存有限	构建哈希表，将连接字段映射到哈希表中，根据哈希表连接	适用于大表连接，减少磁盘I/O，适用于内存有限	需要构建哈希表，内存消耗较大，性能可能高

选择合适的连接算法要考虑表的大小、索引情况、内存资源以及数据分布等因素。不同的情况下，不同的连接算法可能会表现出更好的性能。

子查询

子查询的效率不高主要是由于以下几个原因：

1. 多次查询操作： 子查询通常会被执行多次，每次执行都会生成一个查询计划，执行优化和查询操作，这会导致额外的开销和性能下降。

2. 无法使用索引： 子查询的内部查询通常不能使用索引，子查询的结果集是存放到临时表中的，临时表不存在索引。

3. 依赖外部查询： 子查询的每次执行都需要等待外部查询的结果，导致查询的嵌套和级联执行，影响了性能。

4. 优化器限制： 在某些情况下，优化器难以有效地优化子查询，导致执行计划的选择可能不是最优的，从而影响性能。

5. 数据传递和处理： 子查询需要将数据传递给外部查询，这涉及数据传输和处理，增加了额外的开销。

可以使用join代替子查询

优化建议：

• 在order by和where使用索引，避免全表扫描，避免在order by中出现filesort
• 尽量使用索引字段完成排序
• 无法使用索引时，需要对file sort调优

索引下推

索引下推（Index Condition Pushdown，简称ICP）是 MySQL 中的一项优化技术，它可以在查询过程中使用索引来过滤掉不满足条件的行，从而减少查询需要处理的数据量，提高查询性能。

通常情况下，MySQL 在执行查询时会先根据查询条件从索引中找到满足条件的行的主键值，然后再根据主键值去访问表中的数据。索引下推则允许 MySQL 在索引查找的过程中，对非索引字段的查询条件进行评估，从而在索引扫描的时候就能够排除掉不满足条件的行，减少了访问表中数据的次数。

索引下推的主要优点有：

1. 减少数据访问次数： 通过在索引扫描过程中排除不符合条件的行，可以减少实际访问表中数据的次数，从而提高查询性能。

2. 减少磁盘IO和内存开销： 减少不必要的数据访问，可以降低磁盘IO和内存开销，从而提高数据库的整体性能。

3. 减少锁定的行数： 对于 InnoDB 存储引擎，索引下推可以减少锁定的行数，从而降低了并发访问时的锁竞争，提高了并发性能。

也就是在联合索引中，到某个字段时索引失效了，但仍然也用到了索引，例如有下表：

create table user (
    id int primary key,
    group_id int not null,
    username varchar(200) not null,
    nickname varchar(200) not null,
    key idx_user(group_id, username, nickname)
)

根据索引失效的情况可以得知，以下sql中索引会失效，因为like后%开头会导致索引失效：

select * from user where group_id = 1 and username like '%a%;'

但是经过查询执行计划后，可以看到Extra字段的值为Using index condition，这就代表使用到了索引下推

流程是：

• 能够匹配到idx_user索引
• 经过第一个条件，将group_id = 1的索引筛选出来
• 从筛选出来的索引中，过滤username
• 也就避免了回表之后再对比

自增id

自增（Auto Increment）ID 是一种常见的主键生成方式，它在每次插入新数据时自动递增，用于保证每条记录的唯一性。虽然自增ID在很多情况下是一个方便的选择，但也存在一些问题需要考虑：

1. 不连续的ID： 当执行数据删除操作时，自增ID可能会出现不连续的情况，即某些ID被删除后，数据库中可能会出现空缺的ID值。这可能影响一些特定的业务逻辑，例如要求连续ID的需求。

2. 主键重置问题： 在某些情况下，可能需要重置自增ID的起始值，比如在测试环境中需要重新从某个值开始插入数据。然而，不是所有数据库都支持简单地修改自增ID的起始值，可能需要进行一些复杂的操作。

3. 分库分表问题： 在分库分表的情况下，使用自增ID可能会导致不同的分库分表产生相同的ID值，从而造成数据冲突。

4. 性能问题： 自增ID可能会导致热点问题，因为新数据总是插入到ID的末尾，可能会导致某些页被频繁写入，从而影响性能。

5. 跨库查询问题： 如果多个数据库之间需要进行跨库查询，自增ID的值可能在不同的数据库中重复，导致查询结果不准确。

6. 冗余问题： 有时候，应用程序可能会因为需要引用数据的多个属性，而在多个表中使用了自增ID，这可能导致冗余的情况。

虽然自增ID存在上述问题，但在大多数情况下，它仍然是一种方便和高效的主键生成方式。要根据具体的业务需求和数据库设计来决定是否使用自增ID，以及如何充分考虑和解决可能出现的问题。

推荐方式：时间 + 去重字段 + 用户id后几位

Q.E.D.