MySQL学习

标题## 数据库三大范式

数据库三大范式是关系数据库设计中的一组规范，旨在提高数据结构的合理性、减少数据冗余和提高数据操作的效率。它们分别是：
● 第一范式（1NF）：确保每个数据列都是不可再分的原子值，即每个单元格中只包含一个值。这可以通过将表拆分为更小的表来实现，每个表都包含一个实体的属性。
● 第二范式（2NF）：建立在第一范式的基础上，要求表中的每个非主键列完全依赖于主键列，而不是依赖于其他非主键列。换句话说，每个表应该只描述一个实体的信息。如果有部分信息依赖于表中的一部分主键，那么需要将这些信息拆分到另一个表中。
● 第三范式（3NF）：在第二范式的基础上，要求表中的每个非主键列之间不应该存在传递依赖关系。也就是说，非主键列之间不应该相互依赖，而是直接依赖于主键列。如果存在传递依赖，需要将其转化为直接依赖关系。
这三个范式是逐步规范化设计数据库的步骤，目的是提高数据的一致性、完整性和减少冗余，从而提高数据库的性能和可维护性。但要注意，范式化设计并不是一成不变的，根据具体的业务需求和应用场景，有时也需要对范式进行适度的调整和冗余处理。

MySQL为什么默认引擎是InnoDB

MySQL 默认的存储引擎是 InnoDB，这是因为 InnoDB 在性能、事务支持和容错能力等方面具有较好的特性，适合大多数应用场景。下面是一些原因：
1. 支持事务：InnoDB 是一个支持事务的存储引擎。事务是一组数据库操作的原子性执行，可以保证操作的一致性和完整性。
2. 并发控制：InnoDB 支持行级锁定，在高并发环境下可以最大程度地减少锁冲突，提高并发性能。相比之下，MySQL 的另一个存储引擎 MyISAM 只支持表级锁定，并发性能较低。
3. 外键约束：InnoDB 支持外键约束，可以保证数据的完整性。外键用于建立表与表之间的连接，通过外键约束可以实现数据之间的关联和参照完整性。
4. 崩溃恢复：InnoDB 具有自动崩溃恢复的能力。即使在发生意外故障或系统崩溃时，InnoDB 引擎也能够自动进行崩溃恢复，保障数据的一致性。
5. 支持热备份：InnoDB 支持在线热备份，可以在不停止数据库服务的情况下进行备份操作。这对于需要实时运行且对数据可用性要求高的应用程序非常重要。
需要注意的是，虽然 InnoDB 是 MySQL 默认的存储引擎，但在某些场景下，可以根据实际需求选择其他存储引擎，如 MyISAM、Memory 等。不同的存储引擎适用于不同的应用场景和需求。

索引优缺点

索引是数据库中用于提高数据检索性能的排好序的数据结构。它类似于书籍的目录，通过建立特定的数据结构将列或多个列的值与它们在数据表中对应的行关联起来，以加快查询速度。
索引的优点包括：
1. 提高查询性能：索引可以加快数据库查找数据的速度，通过快速定位到符合查询条件的数据行，减少了数据库进行全表扫描的开销，从而显著提高查询效率。
2. 唯一性约束：通过在索引上设置唯一性约束，可以确保数据的唯一性，防止重复数据的插入。
然而，索引也有一些缺点：
1. 占用存储空间：索引通常需要占用一定的磁盘空间。过多的索引可能会增加存储成本。
2. 索引维护的开销：当对数据表进行插入、更新或删除操作时，索引也需要进行相应的维护操作，这可能导致数据写入的性能下降，更新缓慢。
因此，在设计数据库时，需要根据具体的查询需求、数据特点和系统环境来决定是否以及如何建立索引，以平衡查询性能和维护成本。

MySQL 索引分类

在MySQL中，索引按照索引列的类型可以分为以下几种：
● 主键索引：用于唯一标识每一条记录，主键索引的值不允许重复且不能为空，并且一个表只能有一个主键索引。
● 唯一索引：用于保证索引列的值唯一，允许为空值，但是一个表可以有多个唯一索引。
● 普通索引：没有唯一性限制，允许重复值和空值，是最基本的索引类型。
● 组合索引：在多个字段上创建的索引，可以包含多个列。组合索引可以提高多列查询的性能，但查询条件必须符合最左前缀原则，即查询从左到右使用组合索引中的列。
以上就是MySQL常见的四种索引，这些不同类型的索引在数据库中起到了加速数据检索操作的作用，可以根据具体的需求和使用场景选择适当的索引类型。同时，需要注意索引的创建对写操作（如插入、更新、删除）可能会产生额外的开销，因此需要权衡索引的使用与数据操作的平衡。

MyISAM索引与InnoDB索引的区别

MyISAM和InnoDB是MySQL中两种常见的存储引擎，它们在索引实现上存在以下区别：
1. 存储方式：MyISAM使用非聚簇索引，索引文件和数据文件是分开的；而InnoDB使用聚簇索引，将索引和数据一起存储在同一个文件中。
2. 锁机制：MyISAM采用表级锁定，意味着当对表进行写操作时，整个表都会被锁定，因此可能导致并发写操作的性能较差。而InnoDB采用行级锁定，只锁定需要修改的行，可以提供更好的并发性能和多用户写入的支持。
3. 事务支持：MyISAM不支持事务处理，而InnoDB支持事务和ACID特性（原子性、一致性、隔离性和持久性），可以进行事务管理、回滚和恢复操作。
4. 引用完整性：MyISAM不支持外键约束，而InnoDB支持外键约束，可以设置关联关系来保证数据的完整性。
5. 性能特点：MyISAM在读取频繁、插入和更新较少的场景下性能较好，特别适合于读密集型应用；而InnoDB在并发写入和更新较多的情况下性能较好，适合于写入密集型应用或需要事务支持的场景。
以上就是MyISAM索引与InnoDB索引的五点区别，我们在实际使用时需要根据具体的应用需求和场景来选择适合的存储引擎和索引类型。

创建索引时注意点

在创建索引时，需要注意以下几点：
1. 选择适当的列：根据实际查询需求和数据特点，选择合适的列作为索引列。通常情况下，选择经常被用于查询条件、排序或聚合操作的列作为索引列可以获得更好的性能。
2. 避免过多的索引：过多的索引会增加存储空间的开销，并且在数据写入时需要维护多个索引结构，导致性能下降。因此，只创建必要的索引，避免不必要的冗余索引。
3. 考虑联合索引：对于多个列的组合查询条件，可以考虑创建复合索引。复合索引可以覆盖多个列，减少查询时需要访问的索引数量，提高查询效率。
4. 注意索引的顺序：对于多列索引，考虑索引的顺序。将最常用的列放在索引的前面，以提高查询性能。
5. 使用合适的数据类型：选择合适的数据类型可以减小索引的大小，提高索引的效率。尽量避免使用过长或过大的数据类型作为索引列。
综上所述，创建索引需要根据具体的查询需求、数据特点和系统环境来进行权衡和决策。合理创建索引可以提升数据库的性能，但也需要注意索引的维护成本和更新操作的影响。

使用索引的场景

在大多数情况下，使用索引可以提高数据库查询的性能和效率。但也有一些情况下，不使用索引可能更合适：
● 小型表：对于非常小的表，可能不需要使用索引。因为索引的维护需要额外的存储空间和计算资源，而对于小型表来说，全表扫描的开销可能相对较小。
● 插入、更新和删除频繁的表：如果表的插入、更新和删除操作非常频繁，而且对查询性能的要求相对较低，则可以考虑不使用索引。因为索引的维护会增加写操作的开销，可能导致性能下降。
● 查询条件不经常使用：如果某个字段很少用作查询条件，而且表的数据量较小，那么为该字段创建索引可能没有太大的性能提升。
● 需要频繁更新索引的表：对于频繁进行大量写操作（插入、更新、删除）的表，索引的维护成本可能会超过索引带来的查询性能提升，此时可以考虑不使用	索引。
需要注意的是，实际情况还需结合具体的业务需求、数据规模和性能要求来综合考虑。在设计数据库时，综合权衡索引的使用与否，并进行性能测试和优化是非常重要的。

MySQL 中的锁

在MySQL中，常见的锁包括以下几种：
1. 表级锁（Table-level Locking）：在事务操作中对整个表进行加锁。当一个事务对表进行写入操作时，其他事务无法对该表进行任何读写操作。表级锁通常是针对特定的DDL操作或备份操作。
2. 共享锁（Shared Lock）：也称为读锁（Read Lock），用于允许多个事务同时读取同一资源，但禁止并发写入操作。其他事务可以获取共享锁，但无法获取排他锁。
3. 排他锁（Exclusive Lock）：也称为写锁（Write Lock），用于独占地锁定资源，阻止其他事务的读写操作。其他事务无法获取共享锁或排他锁，直到持有排他锁的事务释放锁。
4. 行级锁（Row-level Locking）：也称为记录锁（Record Locking），在事务操作中对数据行进行加锁。行级锁可以控制并发读写操作，不同事务之间可以并发地访问不同行的数据。MySQL的InnoDB存储引擎默认使用行级锁。
5. 记录锁（Record Lock）：用于行级锁的一种形式，锁定数据库中的一个记录（行）以保证事务的隔离性和完整性。
6. 间隙锁（Gap Lock）：用于行级锁的一种形式，锁定两个记录之间的间隙。它可以防止其他事务在该间隙中插入新记录，从而保证数据的一致性。
7. 临键锁（Next-Key Locks）： 临键锁是记录锁和间隙锁的结合，锁定的是一个范围，并且包括记录本身。
需要注意的是，MySQL的不同存储引擎对锁的支持和实现方式可能有所不同。例如，MyISAM存储引擎使用表级锁来控制并发访问，而InnoDB存储引擎则支持更细粒度的行级锁，提供更好的并发性能和数据一致性。

深入精讲MySQL的锁

1.MySQL中的锁分类

● 按粒度分表锁、行锁和页锁三种，
● 按类型分读锁和写锁(都属于悲观锁)两种。
● 按性能分乐观锁、悲观锁和意向锁。
其中，MyISAM和MEMORY存储引擎采用的是表级锁，而InnoDB存储引擎支持行级锁和表级锁。

2.表锁、行锁、页锁

表锁：

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

CREATE TABLE locker (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  value INT
);

INSERT INTO locker (
	id, name, value
) VALUES
(1, '张三', 2),	(2, '张三', 3),(3, '张三', 4);

BEGIN;
SELECT * FROM locker WHERE value = 2 FOR UPDATE;

因value字段没有索引，所以这个 SQL 语句会对 locker 表进行加锁，也就是表锁。在事务提交之前，该表数据将无法被其他事务修改。

行锁：

对表中一行或多行记录进行加锁控制的方式。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。在 MySQL 中，行锁是基于索引加载的，即行锁是要加在索引响应的行上。索引失效时会升级为表锁。

#给locker表中value列添加索引
ALTER TABLE locker ADD index idx_value (value);

BEGIN;
SELECT * FROM locker WHERE value = 2 FOR UPDATE;

value字段添加索引后，这条SQL只会针对value值为2的记录进行加锁，也就是行锁。在事务提交之前，这些行数据将无法被其他事务修改。

页锁：

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

3.读锁、写锁、意向锁

读锁（共享锁，S锁（shared））：

多个读操作对同一个资源共享同一个锁，多个读操作可以同时进行而不会互相影响。因为读操作并不会改变数据内容，所以多个事务可以共享同一个锁，并行地读取同一个资源，这样可以提高并发效率。

写锁（排它锁，X锁（exclude））：

当两个事务同时对表中某行数据进行更新操作时，若一个事务先到达并给该行加上排它锁，那么另一个事务就不能在该行加任意类型的锁，直到第一个事务释放了该行的锁。因此，排它锁可以确保在同一时间只有一个事务可以对被加锁的资源进行修改操作，从而避免出现数据竞争和不一致问题。

意向锁（I锁（Intent Lock））：

数据库中的一种表级锁，在行级锁的基础上引入的一种概念。意向锁是用于解决多粒度锁定而设计的，可以避免行级锁和表级锁之间的冲突。
意向锁分为两种类型：意向共享锁（Intention Shared Lock, IS）和意向排他锁（Intention Exclusive Lock, IX）。
● 意向共享锁（IS）：在一个事务需要对表中某些行加共享锁（S锁）时，事务首先需要获得表的意向共享锁（IS锁）
● 意向排他锁（IX）：指的是在一个事务需要对表中某些行加排它锁（X锁）时，事务首先需要获得表的意向排它锁（IX锁）。
意向锁简单来说就是添加行锁时，给表添加一个标识表明该表已经存在共享锁或者是排它锁，其他事务需要加锁直接读取该标识判断是否已经存在锁。

4.间隙锁（Gap-Lock）

间隙锁就是两个值之间的空隙加锁，是Innodb在可重复读隔离级别下为了解决幻读问题而引入的一种锁机制。需注意间隙锁只会在可重复读隔离级别（REPEATABLE-READ）下才会生效。	

drop TABLE orders;
CREATE TABLE orders (
    order_id INT NOT NULL PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    quantity INT NOT NULL
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO orders (
	order_id,
	product_name,
	quantity
)
VALUES
	(1, 'iPhone', 2),
	(2, 'iPad', 1),
	(3, 'MacBook', 3),
	(7, 'AirPods', 7),
	(10, 'Apple Watch', 10);

此时orders表存在（3,7），（7,10），（10,正无穷）。

-- 事务A
set tx_isolation = 'REPEATABLE-READ';
BEGIN;
SELECT * FROM orders WHERE order_id = 8 FOR UPDATE;
#commit;

其他事务无法在这个（7,10）区间插入任何数据。

-- 事务A
set tx_isolation = 'REPEATABLE-READ';
BEGIN;
SELECT * FROM orders WHERE order_id = 15 FOR UPDATE;
#commit;

其他事务无法在这个（10,正无穷）区间插入任何数据。
操作步骤如下：
开启A事务修改id为8的数据，但是不提交事务，同时开启事务B进行插入id为8的数据。

#事务A
set tx_isolation = 'REPEATABLE-READ';
BEGIN;
SELECT * FROM orders WHERE order_id = 8 FOR UPDATE;
COMMIT;

#事务B
set tx_isolation = 'REPEATABLE-READ';
BEGIN;
INSERT INTO orders (
	order_id,
	product_name,
	quantity
)
VALUES
	(9, 'AirPods Pro', 2);
COMMIt;

#事务C
BEGIN;
SELECT * FROM orders WHERE order_id = 10 FOR UPDATE;
COMMIt;

此时事务B阻塞无法插入成功；再开启事务C进行数据修改，可以修改成功；将事务A进行提交，事务B随即插入成功。
间隙锁可以锁定一个范围内的所有记录，包括不存在的记录，从而防止其他事务在该范围内插入或修改数据。

5.临键锁（Next-key Locks）

临键锁（Next-key Locks）是MySQL InnoDB存储引擎实现的一种数据行级别的锁机制，它是行级锁与间隙锁的组合，即位于索引记录与索引区间之间的一种排它锁。
临键锁主要目的是为了解决幻读问题，能够封锁该条记录相邻两个键之间的空白区域，防止其他事务在这个区域内插入、修改、删除数据。临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁.

#事务A
set tx_isolation = 'REPEATABLE-READ';
BEGIN;
SELECT * FROM orders WHERE order_id >= 3 and  order_id < 7 FOR UPDATE;
COMMIt;

事务A SQL锁定了[3,7）区间，其他事务无法修改order_id为3的记录，并且无法添加为order_id为4的记录。

#事务B无法修改成功
set tx_isolation = 'REPEATABLE-READ';
BEGIN;
update orders set quantity = 333 where order_id = 3;
COMMIt;

#事务C无法插入成功
set tx_isolation = 'REPEATABLE-READ';
BEGIN;
INSERT INTO orders (
	order_id,
	product_name,
	quantity
)
VALUES
	(4, 'iPhone999', 999);
COMMIt;

B 树和B+树的区别

B树和B+树是常用的数据结构，用于在数据库中进行索引操作。它们之间的区别主要有以下几个方面：
1. 数据存储方式：在B树中，每个节点都包含键和对应的值，叶子节点存储了实际的数据记录；而B+树中，只有叶子节点存储了实际的数据记录，非叶子节点只包含键信息和子节点的指针。
2. 数据检索方式：在B树中，由于非叶子节点也存储了数据，所以查询时可以直接在非叶子节点找到对应的数据，具有更短的查询路径；而B+树的所有数据都存储在叶子节点上，只有通过叶子节点才能获取到完整的数据。
3. 范围查询效率：由于B+树的所有数据都存储在叶子节点上，并且叶子节点之间使用链表连接，所以范围查询的效率较高。而在B树中，范围查询需要通过遍历多个层级的节点，效率相对较低。
4. 适用场景：B树适合进行随机读写操作，因为每个节点都包含了数据；而B+树适合进行范围查询和顺序访问，因为数据都存储在叶子节点上，并且叶子节点之间使用链表连接，有利于顺序遍历。
总结来说： B树和B+树在数据存储方式、数据检索方式、范围查询效率以及适用场景方面存在区别。B树适合随机读写操作，而B+树适合范围查询和顺序访问。在实际应用中，根据不同的场景和需求选择合适的树结构可以带来更高效的数据处理和索引操作。

深入讲解

MySQL的索引机制中，有一点可谓是路人皆知，既默认使用B+树作为底层的数据结构。有人会说树结构是以二分法查找数据，所以会在很大程度上提升检索性能，这点确实没错，但树结构有那么多，但为什么要选择B+树呢？而不选择二叉树、红黑树或B树呢？下面一起聊一聊这个话题。

索引为何不选择二叉树

二叉搜索树是遵守二分搜索法实现的一种数据结构，它具有下面特点：
● 任意节点的左节点不为空时，左节点值小于根节点值；
● 右节点不为空时，右节点值大于根节点值；
依次存入数据，如果数据是递增的，则原二叉树退化为链表结构。

从动画中可以明显看到，需要经过5次查询才能找到目标数据，由于树结构在磁盘中存储的位置也不连续，所以最终需要发生5次磁盘IO才能找到目标数据。
二叉树不适合作为索引结构的原因：
● ①如果索引的字段值是按顺序增长的，二叉树会转变为链表结构，因此检索的过程和全表扫描无异。
● ②每个节点中只存储一个数据，节点之间还是不连续的，每次磁盘IO只能读取一个数据。

索引为何不选择红黑树

相比于二叉树，红黑树则进一步做了优化，它是一种自适应的平衡树，会根据插入的节点数量以及节点信息，自动调整树结构来维持平衡。

由于树变矮了，其效果也很明显，在红黑树中只需要经过3次IO就可以找到目标数据，似乎看起来还不错对嘛？但MySQL为啥不用这颗名声远扬的红黑树呢？
红黑树不适合作为索引结构的原因：
● ①虽然对比二叉树来说，树高有所降低，但数据量一大时，依旧会有很大的高度。
● ②每个节点中只存储一个数据，节点之间还是不连续的，每次磁盘IO只能读取一个数据。

索引为何不选择B-Tree

B树和红黑树相比，其单节点可容纳多个数据，就能在很大程度上改善其性能，使B树的树高远远小于红黑树的高度。

对比红黑树可以发现，每个节点上可以存储更多的数据，且树高固定，数据插入之后横向扩展。观察动画只需要2次IO就可以找到目标数据，搜索效率大大提高了。并且每个节点的元素我们可以自己空值。
那么为什么MySQL没有采用B树结构了？
我们仔细观察可以知道B的叶子节点直接是没有指针的，但是日常查询中包含了大量的范围查找，所以当出现范围查找的时候，会出现多次的IO查找。
B树不适合作为索引结构的原因：
● ①虽然对比之前的红黑树更矮，检索数据更快，但对于大范围查询的需求，依旧需要通过多次磁盘IO来检索数据。

索引为何要选择B+Tree

B+树是在B树的基础进一步优化，一方面节点分为了叶节点和叶子节点两类，另一方面叶子节点之间存在单向链表指针。

B+树相比于B树叶子节点之间多了个单项指针，当需要做范围查询时，只需要定位第一个节点，然后就可以直接根据各节点之间的指针，获取到对应范围之内的所有节点，也就是只需要发生一次IO，就能够确定所查范围之内的所有数据位置。
其实MySQL底层真正的索引结构还对叶子节点之间的指针进行了优化，B+树叶子节点的单向指针无法友好支持的倒叙查询，因此MySQL针对单向指针优化成了双向指针，也就是双向链表结构。即可以快速按正序进行范围查询，而可以快速按倒序进行范围操作，在某些业务场景下又能进一步提升整体性能！
节点分为了叶节点和叶子节点。为什么？
因为B+树的叶节点不存储数据，仅存储指向叶子节点的指针，这样在相同树高时，能存储更多的数据，需要注意的是叶节点数据与叶子结点数据是冗余的。
现在对于MySQL索引为何要选择B+树（变种）的原因大家应该懂了吧。

MySQL中in和exists的区别

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

MySQL多表查询时的连接方式

当进行多表查询时，在 MySQL 中常用的连接方式有以下几种：
1. 内连接（INNER JOIN）：返回同时满足连接条件的行。它通过比较连接列的值，将两个或多个表中匹配的行组合在一起。
2. 左外连接（LEFT JOIN）：返回左表中的所有行，以及与左表匹配的右表的行。如果右表中没有匹配的行，对应的列将填充为 NULL。
3. 右外连接（RIGHT JOIN）：返回右表中的所有行，以及与右表匹配的左表的行。如果左表中没有匹配的行，对应的列将填充为 NULL。
4. 全外连接（FULL JOIN）：返回左右两个表中的所有行。如果某个表中没有匹配的行，对应的列将填充为 NULL。需要注意 MySQL 不支持 FULL JOIN 可以使用UNION ALL 模拟。
5. 自连接（Self JOIN）：将单个表视为两个独立的表，使用别名来引用同一个表。这种连接适用于在同一个表中根据某些条件关联不同的行。
6. 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积，即所有可能的组合。它将第一个表的每一行与第二个表的每一行进行组合。
在实际使用中我们可以根据具体的需求选择合适的方式来联合多个表，获取所需的数据。

最左前缀原则

最左前缀原则（Leftmost Prefix Rule）是索引在数据库查询中的一种使用规则。它指的是在使用复合索引时，索引的最左边的连续几个列会被用于查询过滤条件的匹配。
具体来说，如果在表中创建了一个复合索引，包含多个列 A、B、C，那么最左前缀原则要求查询中的过滤条件必须从索引的最左边开始，并且不能跳过中间的列。只有当查询中的过滤条件与索引的最左前缀完全匹配时，索引才能被充分利用。
例如，考虑以下复合索引 (A, B, C) 和查询语句：
SELECT * FROM my_table WHERE A = 'value1' AND C = 'value2';
在这种情况下，最左前缀原则要求查询条件中必须包含列 A，而不能直接使用列 C 进行过滤。因为只有满足最左前缀条件，索引 (A, B, C) 才能被有效地使用。
遵循最左前缀原则的好处包括：
1. 提高查询性能：通过使用索引的最左前缀，可以最大限度地减少索引扫描的数据量，提高查询的效率和响应时间。
2. 减少索引占用空间：在某些情况下，使用最左前缀原则可以减少创建多个索引的需求，节省磁盘空间和索引维护的开销。
需要注意的是，最左前缀原则并不适用于所有的情况。有时候，根据具体的查询需求和数据模式，可能需要创建额外的索引来支持更复杂的查询条件。因此，在设计和优化数据库索引时，需要综合考虑查询的特点和性能要求，灵活选择适当的索引策略。

覆盖索引

覆盖索引是指一个索引包含了查询所需的所有列，而无需访问表的实际数据页。
当数据库系统执行查询时，通常需要从磁盘中读取数据页到内存中才能进行处理。而如果使用了覆盖索引，由于索引已经包含了查询所需的所有列的值，数据库系统可以直接通过索引来获取这些值，而不需要额外地读取数据页。这样可以减少磁盘 I/O 的次数和数据在内存中的占用，提高查询的效率。
覆盖索引通常适用于以下场景：
1. 查询语句只需要返回索引列中的数据，而不需要访问其他列的值。
2. 查询语句中的条件过滤、排序或分组的列都在同一个索引上。
总结来说：使用覆盖索引可以减少数据库系统的工作量，提高查询的性能。它可以避免不必要的数据读取操作，减少磁盘 I/O，并且在内存中更高效地处理查询操作。因此，在设计数据库索引时，可以考虑创建覆盖索引来优化相关的查询。

索引下推

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

深入精讲

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。

● MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。
● MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。
● MySQL 索引扫描：根据指定索引过滤条件，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。
● MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

delete from user1;
drop table user1;

CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) NOT NULL,
	`address` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user1` (`name`, `age`, `address`) VALUES
('Alice', 40, 'address1'),
('Amy', 23, 'address2'),
('Tom', 18, 'address3'),
('Mike', 22, 'address4');

explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 23;

# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

不使用索引下推实现

Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;

使用索引下推实现

Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;

索引下推的使用条件
● ICP目标是减少全行记录读取，从而减少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说。
● 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
● where 条件中是用 and 而非 or 的时候。
● ICP适用于分区表。
● ICP不支持基于虚拟列上建立的索引，比如说函数索引
● ICP不支持引用子查询作为条件。
● ICP不支持存储函数作为条件，因为存储引擎无法调用存储函数

数据库连接池

首先，我们要明白数据库连接池是一种管理和维护数据库连接的技术。它在应用程序和数据库之间建立了一个连接的缓冲池，用于存储和复用已经建立好的数据库连接。
那为什么需要数据库连接池呢？
1. 提高性能：数据库连接的建立和断开是比较耗时的操作，频繁地创建和销毁连接会增加系统的负担。通过使用连接池，可以避免频繁地创建和关闭连接，减少了连接的开销，提高了系统的性能。
2. 资源管理：数据库连接是有限的资源，如果每个请求都创建一个新的连接，可能导致连接过多而耗尽资源。连接池通过对连接的管理和复用，能够更有效地管理数据库连接，避免资源的浪费。
3. 并发处理：在高并发的场景下，如果每个请求都去单独连接数据库，可能会导致数据库连接数量过多，从而限制了系统的扩展性。连接池允许多个请求共享连接，减少了数据库连接的数量，提高了并发处理能力。
4. 连接可靠性：数据库连接可能会因为网络问题或服务器故障而中断，当发生这种情况时，连接池能够检测到连接的失效，并重新创建一个可用的连接，确保应用程序的可靠运行。
总结来说： 数据库连接池是为了提高性能、更有效地管理资源、提高并发处理能力和增加连接的可靠性而存在的。它通过缓存已经建立好的连接，复用连接以及管理连接的生命周期，从而减少了连接的开销，提高了系统的性能和稳定性。

并发事务带来的问题

并发事务可以带来以下几个问题：
1. 脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据。假设事务A修改了一条数据但未提交，事务B却读取了这个未提交的数据，导致事务B基于不准确的数据做出了错误的决策。
2. 不可重复读（Non-repeatable Read）：一个事务在多次读取同一数据时，得到了不同的结果。假设事务A读取了一条数据，事务B修改或删除了该数据并提交，然后事务A再次读取同一数据，发现与之前的读取结果不一致，造成数据的不一致性。
3. 幻读（Phantom Read）：一个事务在多次查询同一范围的数据时，得到了不同数量的结果。假设事务A根据某个条件查询了一组数据，事务B插入了符合该条件的新数据并提交，然后事务A再次查询同一条件下的数据，发现结果集发生了变化，产生了幻觉般的新增数据。
4. 丢失修改（Lost Update）：两个或多个事务同时修改同一数据，并且最终只有一个事务的修改被保留，其他事务的修改被覆盖或丢失。这种情况可能会导致数据的部分更新丢失，造成数据的不一致性。
不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

CHAR 和 VARCHAR 的区别

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