数据库、缓存复习

索引常见八股

数据库索引是一种优化数据库查询性能的技术，被广泛应用于各种数据库管理系统中。以下是关于数据库索引方面的几个常见问题：
索引是什么？索引是一种特殊的文件（InnoDB数据表是表空间文件里的一部分），它包含着对数据库表中一列或多列的引用。主要作用是加快数据的检索速度。
索引的类型有哪些？

• 单列索引：即一个索引只包含单个列。
• 组合索引：索引包含多个列。
• 唯一索引：索引列的值必须唯一，不允许重复。
• 主键索引：特殊的唯一索引，不允许有空值。
• 全文索引：主要用于查找文本中的关键字。
  位图索引：适用于列的取值不多的情况。
  哈希索引：基于哈希表的实现，对于等值查询性能优异。
  索引的原理是什么？大部分数据库使用B树（B-Tree）或者B+树（B+-Tree）作为索引结构，这些数据结构可以帮助数据库高效地读取数据。
  索引的优缺点是什么？优点：
  加快数据检索速度，提高查询效率。
  可以创建唯一性约束，保证数据库表中数据的唯一性。
  加速表和表之间的连接。
  索引列排序之后可以大大加快数据访问速度。
  缺点：
  创建和维护索引需要消耗时间，这个时间随着数据量的增加而增加。
  索引需要占用磁盘空间。
  当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样会降低数据的维护速度。
  什么时候不该使用索引？
  表中数据量小，或者查询表的大部分列。
  表经常进行大量的增加、删除、修改操作。
  索引列的数据重复率高，不具有唯一性。
  如何优化索引？
  选择合适的索引列，通常是查询中出现频率高、分布均匀的列。
  索引列宜独立：应避免对索引列进行函数操作和计算。
  使用索引扫描来代替全表扫描。
  控制索引的数量，避免过多索引导致的维护成本高昂问题。
  覆盖索引是什么？当一个索引包含所有需要查询的字段的值时，称之为覆盖索引。查询可以直接通过索引获取数据，而无需回表查询，这样可以显著提高查询效率。

说一下如何优化索引

当我们讨论优化索引时，通常主要指的是数据库中索引的优化。数据库索引的优化可以显著提高查询效率，降低数据检索的成本。以下是一些常见的索引优化策略：

1. 选择合适的索引列：选择那些经常出现在查询条件（WHERE语句）、排序（ORDER BY）和连接条件（JOIN）中的列作为索引。这样可以加快这些操作的速度。

2. 保持索引精简：仅对必要的列创建索引。过多的索引不仅会占用更多的存储空间，也会减慢写操作的速度，因为索引也需要维护。

3. 使用复合索引：当查询经常涉及多个列时，使用复合索引（一个索引包含多个列）可能更有效。但需要注意列的顺序，通常应将选择性最高的列放在前面。

4. 索引维护：随着数据变更，索引可能会出现碎片化。定期重建或重新组织索引，以确保其保持最优性能。

5. 适当使用前缀索引：如果列的字符串长度很长，使用前缀索引可以减少索引大小，节约存储空间，并可能加快搜索速度。

6. 索引选择性：选择性是指索引中不同值的比例。高选择性的索引意味着有更多唯一值，这类索引的效率更高。

7. 考虑使用包含索引：包含索引允许在非键索引列上存储额外的数据。通过这种方式，某些查询可以直接在索引上得到满足，而不需要通过索引再回表。

8. 监控和分析：定期监控索引的性能，分析索引使用情况和查询计划，找出那些未被充分利用的索引，或者确定是否需要添加新索引。

9. 避免过多的索引动态创建和删除：经常创建和删除索引可能会影响系统性能，并且可能导致查询计划不稳定。

通过这些策略，可以在不同的场景中对索引进行优化。不过，任何优化都需要根据具体情况进行，最好在测试环境中评估优化措施的效果，再在生产环境中实施。

索引什么时候失效

索引不会发挥作用，或者说“失效”，通常是在以下情况下：

1. 全表扫描更高效：当查询结果集包含表中的大部分数据时，数据库优化器可能决定使用全表扫描而不是索引扫描。

2. 错误的或没有索引使用：查询中使用的列没有被索引，或者使用的索引对查询不起作用。

3. 索引列参与计算或函数操作：如果WHERE子句中对索引列进行了函数操作，例如 WHERE YEAR(column) = 2021，则索引可能不会被用到。

4. 不符合索引列顺序：在使用复合索引时，如果查询条件不是从索引的最左列开始，索引可能不会被充分利用。

5. 使用了NOT IN 或 <> 操作符：这些操作符往往使数据库优化器难以使用索引。

6. 使用了模糊查询的前置通配符：如 LIKE ‘%value’，这将阻止索引的使用。

7. 数据类型不一致：在进行列比较时，如果数据类型不匹配，可能导致索引失效。

8. 索引选择性不高：如果索引的列有许多重复值（低选择性），数据库优化器可能认为使用索引效率不足。

9. OR条件下不同列的索引：多个索引条件用OR连接，且参与条件的列分别在不同的索引中，可能导致无法有效使用索引。

10. 隐式转换：当查询条件中的数据类型与表中列的数据类型不一致时，数据库可能会进行隐式转换，这可能导致索引失效。

11. IS NULL 查询：某些数据库不会对 IS NULL 条件使用索引。

12. 排序规则和字符集问题：如果索引列的排序规则（collation）或字符集与查询中使用的不一致，也可能导致索引失效。

每种数据库系统的优化器行为略有不同，因此上述情况并非适用于所有数据库系统，具体情况需根据实际使用的数据库系统进行考虑。当索引失效时，可以通过查询优化、修改数据库模式或重新设计索引来解决这些问题。在实际管理数据库时，了解这些情况对于有效利用索引至关重要。

说一下mysql存储过程

MySQL 存储过程是一种在数据库中存储的预编译的 SQL 语句集合，它是一种可重用的数据库对象，可以包含一条或多条 SQL 语句。存储过程可以接受参数，并返回结果。它们可以执行复杂的业务逻辑，减少网络传输量，提高数据库的性能。

存储过程的创建语法如下：

CREATE PROCEDURE procedure_name([procedure_parameter[,...]])
[characteristic ...] routine_body

其中，procedure_name是存储过程的名称，procedure_parameter是传递给存储过程的参数，routine_body是存储过程的主体，包含了 SQL 语句。

存储过程的调用语法如下：

CALL procedure_name([parameter[,...]])

在存储过程中，可以使用BEGIN和END来定义一个代码块，代码块中可以包含条件判断、循环等控制结构。例如：

CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
   SELECT COUNT(*) INTO param1 FROM t;
END;

这个存储过程返回表 t 中的行数。

存储过程的优点包括：提高性能，减少网络流量，提高代码的可重用性，提高安全性。但是，存储过程也有一些缺点，如调试困难，跨数据库移植性差等。

存储过程在什么情况下会存在跨数据库移植性差的问题？

存储过程在以下情况下可能存在跨数据库移植性差的问题：

1. 数据库特定的语法和函数：不同的数据库系统，如MySQL、Oracle、SQL Server等，它们的SQL语法和内置函数可能会有所不同。如果存储过程中使用了某个数据库特有的语法或函数，那么在另一个数据库系统中可能无法直接使用，需要进行修改。
2. 数据类型的差异：不同的数据库系统对数据类型的支持也可能存在差异，例如，某些数据库系统可能支持特定的数据类型，而其他数据库系统则不支持。
3. 存储过程的编译和执行方式：不同的数据库系统可能有不同的存储过程编译和执行方式，这可能会影响到存储过程的性能和行为。
4. 事务处理：不同的数据库系统对事务的处理方式可能会有所不同，这可能会影响到存储过程中事务的行为。

因此，如果你打算将存储过程从一个数据库系统迁移到另一个数据库系统，可能需要对存储过程进行一些修改，以适应新的数据库系统。

说一下mysql索引

MySQL索引是一种特殊的数据结构，它可以帮助数据库系统更快地查找数据。索引可以极大地提高数据查询的速度，就像一本书的目录可以帮助你快速找到你需要的信息一样。

在MySQL中，常见的索引类型有以下几种：

1. 主键索引（PRIMARY KEY）：主键索引是唯一的，它要求主键的值必须是唯一的，不能有重复。同时，每个表只能有一个主键索引。
2. 唯一索引（UNIQUE KEY）：唯一索引要求索引的值必须是唯一的，但允许有空值。每个表可以有多个唯一索引。
3. 普通索引（INDEX 或 KEY）：普通索引是最基本的索引，它没有任何限制。
4. 全文索引（FULLTEXT）：全文索引用于全文搜索。
5. 组合索引：组合索引是由两个或更多的列组成的索引，其效果等于同时对多个列创建索引。

创建索引可以使用CREATE INDEX语句，例如：

CREATE INDEX index_name ON table_name(column_name);

删除索引可以使用DROP INDEX语句，例如：

DROP INDEX index_name ON table_name;

虽然索引可以提高查询速度，但是它也会占用磁盘空间，同时也会在插入、删除和更新操作时降低性能，因为在执行这些操作时，MySQL需要更新索引。因此，应该根据实际情况合理地创建索引。

主键索引和唯一索引有什么区别？如何选择合适的索引类型？索引对数据库性能有什么影响？

主键索引和唯一索引的主要区别在于：

1. 主键索引要求主键的值必须是唯一的，并且不能为NULL。每个表只能有一个主键索引。
2. 唯一索引也要求索引的值必须是唯一的，但是可以有NULL值。每个表可以有多个唯一索引。

选择合适的索引类型主要取决于你的数据和查询需求：

1. 如果某个列的值总是唯一的，例如用户ID，那么可以为这个列创建主键索引。
2. 如果某个列的值大部分是唯一的，但可能有NULL值，例如电子邮件地址，那么可以为这个列创建唯一索引。
3. 如果某个列的值有很多重复，例如性别，那么可以为这个列创建普通索引。
4. 如果需要对文本进行搜索，那么可以为这个列创建全文索引。

索引对数据库性能的影响主要有两方面：

1. 正面影响：索引可以极大地提高数据查询的速度。当执行SELECT查询时，如果能够使用索引，那么MySQL可以直接定位到匹配的行，而不需要扫描整个表。
2. 负面影响：索引会占用磁盘空间。同时，当插入、删除和更新数据时，MySQL需要更新索引，这会降低这些操作的性能。

因此，创建索引是一种权衡。如果一个表主要用于读取数据，那么创建更多的索引可能是有利的。但是，如果一个表主要用于写入数据，那么应该谨慎地创建索引。

索引是如何实现的？

MySQL的索引实现主要依赖于数据结构，其中最常用的数据结构是B-Tree（Balanced Tree，平衡树）和B+Tree。

1. B-Tree：B-Tree是一种自平衡的多路搜索树，它的特点是任何一个节点的所有子节点中的关键字都分布在该节点的关键字两侧。也就是说，如果某个节点有k个子节点，那么该节点必须有k个关键字，并且关键字i小于等于关键字i+1。B-Tree的优点是能够保持数据的有序性，查找效率较高。
2. B+Tree：B+Tree是B-Tree的一种变体，它的特点是非叶子节点不直接存储数据，只存储关键字和子节点的指针，只有叶子节点存储数据。这样做的好处是每个非叶子节点可以存储更多的关键字，从而降低了树的高度，提高了查找效率。

在MySQL中，InnoDB存储引擎默认使用B+Tree作为索引结构。当你创建一个索引时，MySQL会在B+Tree中插入对应的关键字，并将关键字与数据行关联起来。当你查询数据时，MySQL会使用B+Tree进行快速查找，从而提高查询速度。

除了B-Tree和B+Tree，MySQL还支持其他类型的索引，例如哈希索引、全文索引等，但是这些索引在使用时需要考虑其适用场景和限制。

索引结构选择时，B-Tree和B+Tree有何区别？MySQL索引创建时会自动选择适合的数据结构吗？除了B-Tree和B+Tree，MySQL还支持哪些其他类型的索引？

B-Tree和B+Tree都是树形数据结构，用于提高数据库查询性能，但它们之间有一些区别：

1. B-Tree：在B-Tree中，每个节点都包含键和数据。这意味着所有的键都分布在整个树中，因此搜索可能在非叶子节点结束。
2. B+Tree：在B+Tree中，数据只存储在叶子节点，非叶子节点仅存储键信息。这导致了B+Tree的所有数据都位于叶子节点，这对范围查询非常有利。此外，B+Tree的所有叶子节点都通过指针连接，这使得对整个范围的数据扫描变得更容易。

在MySQL中，InnoDB存储引擎默认使用B+Tree作为索引结构，因为它对大部分场景都有良好的性能。在创建索引时，MySQL不会自动选择数据结构，而是根据索引类型使用预定义的数据结构。例如，主键索引和非主键索引都使用B+Tree，全文索引使用FULLTEXT，哈希索引使用HASH。

除了B-Tree和B+Tree，MySQL还支持以下类型的索引：

1. 哈希索引：哈希索引基于哈希表实现，提供了非常高的数据查询性能。然而，哈希索引只能满足”=”、”<=>”这类精确查找查询，不能用于范围查找和排序，而且不支持部分键查找。
2. 全文索引：全文索引用于实现全文搜索。与其他索引类型不同，全文索引并不是在某个列上创建，而是在文本集合上创建。
3. 空间索引：空间索引用于地理数据存储，能够处理所有类型的空间数据查询，包括对点、线、多边形等的查询。

MySQL索引创建时是否会根据数据类型自动选择适合的数据结构？MySQL中的哈希索引用于哪些类型的查询？B+Tree相对于B-Tree的优势是什么？

在MySQL中，索引的数据结构并不是根据数据类型自动选择的，而是根据索引类型预定义的。例如，InnoDB存储引擎的主键索引和非主键索引都使用B+Tree数据结构，全文索引使用FULLTEXT数据结构，哈希索引使用HASH数据结构。

哈希索引非常适合于等值查询，也就是说，它非常适合于那些使用”=”或”<=>”运算符的查询。哈希索引可以快速找到给定键值的记录，但它不支持范围查询，也不支持按键值排序，而且在处理大数据量的等值查询时，哈希索引的性能也不一定就比B-Tree索引好。

B+Tree相对于B-Tree的主要优势在于：

1. B+Tree的所有叶子节点都是通过指针连接的，这使得对整个范围的数据扫描变得更容易。这对于需要执行范围查询的数据库操作非常有利。
2. 在B+Tree中，数据只存储在叶子节点，非叶子节点仅存储键信息。这意味着非叶子节点可以存储更多的键，因此B+Tree的树高通常比B-Tree更低，查询效率更高。
3. B+Tree的结构更加稳定。在B-Tree中，插入和删除节点可能会导致树的结构发生较大的变化，而在B+Tree中，插入和删除节点只会影响到叶子节点，不会影响到整个树的结构。

MySQL中的全文索引使用什么样的数据结构？B+Tree索引相对于B-Tree索引的查询效率如何？在MySQL中，哪些存储引擎支持哈希索引？

在MySQL中，全文索引使用的是倒排索引（Inverted Index）数据结构。倒排索引是一种将单词映射到它们出现过的文档的索引，它主要用于全文搜索。在倒排索引中，每个唯一的单词都有一个相关的文档列表。

B+Tree索引相对于B-Tree索引的查询效率通常更高。因为在B+Tree中，非叶子节点仅存储键信息，这意味着非叶子节点可以存储更多的键，因此B+Tree的树高通常比B-Tree更低，查询效率更高。此外，B+Tree的所有叶子节点都是通过指针连接的，这使得对整个范围的数据扫描变得更容易，这对于需要执行范围查询的数据库操作非常有利。

在MySQL中，只有Memory存储引擎默认支持哈希索引，InnoDB存储引擎不直接支持哈希索引。但是，InnoDB存储引擎有一个叫做”自适应哈希索引”的特性，当某个索引被频繁使用时，InnoDB会自动创建一个哈希索引来提高查询速度。但这个特性默认是关闭的，需要在配置文件中开启。

MySQL中的倒排索引是用来做什么的？B+Tree索引为什么查询效率更高？InnoDB存储引擎中的自适应哈希索引是什么意思？

在MySQL中，倒排索引主要用于全文搜索。全文搜索是一种在所有文档中查找包含指定词或短语的文档的技术。倒排索引是一种将单词映射到它们出现过的文档的索引，每个唯一的单词都有一个相关的文档列表。当执行全文搜索时，MySQL可以直接查找倒排索引，找到包含指定词或短语的所有文档，而不需要扫描所有的文档。

B+Tree索引的查询效率更高主要有两个原因：

1. 在B+Tree中，非叶子节点仅存储键信息，这意味着非叶子节点可以存储更多的键，因此B+Tree的树高通常比B-Tree更低，查询效率更高。
2. B+Tree的所有叶子节点都是通过指针连接的，这使得对整个范围的数据扫描变得更容易，这对于需要执行范围查询的数据库操作非常有利。

在InnoDB存储引擎中，自适应哈希索引是一种性能优化的特性。当InnoDB发现某个B+Tree索引被频繁使用时，它会自动创建一个哈希索引来提高查询速度。这个哈希索引是基于B+Tree索引的，它的键是B+Tree索引的键，它的值是指向B+Tree索引的指针。这样，当查询这个索引时，InnoDB可以直接查找哈希索引，而不需要查找B+Tree索引，从而提高查询速度。但这个特性默认是关闭的，需要在配置文件中开启。

倒排索引在MySQL中是如何提高全文搜索效率的？为什么B+Tree索引相对于B-Tree索引查询效率更高？InnoDB存储引擎中的自适应哈希索引是如何自动优化查询速度的？

倒排索引在MySQL中主要通过以下方式提高全文搜索效率：

1. 倒排索引将每个唯一的单词映射到它出现过的文档列表。当执行全文搜索时，MySQL可以直接查找倒排索引，找到包含指定词或短语的所有文档，而不需要扫描所有的文档。这大大减少了搜索的时间。
2. 倒排索引通常会存储每个单词在文档中的位置信息，这可以帮助MySQL更精确地执行全文搜索。例如，当搜索包含多个单词的短语时，MySQL可以使用位置信息来确定这些单词是否在同一个文档中并且彼此相邻。

B+Tree索引相对于B-Tree索引查询效率更高的主要原因是：

1. 在B+Tree中，非叶子节点仅存储键信息，这意味着非叶子节点可以存储更多的键，因此B+Tree的树高通常比B-Tree更低，查询效率更高。
2. B+Tree的所有叶子节点都是通过指针连接的，这使得对整个范围的数据扫描变得更容易，这对于需要执行范围查询的数据库操作非常有利。

InnoDB存储引擎中的自适应哈希索引是通过以下方式自动优化查询速度的：

当InnoDB发现某个B+Tree索引被频繁使用时，它会自动创建一个哈希索引来提高查询速度。这个哈希索引是基于B+Tree索引的，它的键是B+Tree索引的键，它的值是指向B+Tree索引的指针。这样，当查询这个索引时，InnoDB可以直接查找哈希索引，而不需要查找B+Tree索引，从而提高查询速度。