在MySQL中，索引和数据都存储在磁盘中。当我们通过索引进行查询时，首先需要进行磁盘I/O，将索引页加载到内存中。通过内存中的索引定位到数据所在的物理位置后，还需要再次进行磁盘I/O来读取相应的数据行。由于磁盘I/O相对于内存操作是非常耗时的，因此查询过程中发生的I/O次数是决定查询性能的关键。因此数据库优化的一个核心目标就是减少查询时的磁盘I/O次数。
这里说的”磁盘I/O“指的是计算机的CPU和内存，与磁盘之间进行数据读取和写入的过程。
索引的目的
加速查询：索引允许数据库快速定位到特定的数据行，显著提高了查询的效率，避免全表扫描。
影响更新操作：索引需要在数据插入、更新、删除时进行维护，这可能会影响写操作的性能。
索引的优势和劣势
优势：
快速定位数据。
优化排序和分组操作，因为B+树索引是有序的。
提高大型表的查询效率。
劣势：
维护成本：影响数据的插入、更新、删除性能。
存储开销：索引需要额外的存储空间。
索引的适用场景
- 频繁作为查询条件的字段，特别是当查询返回的结果集为表中较小的子集时。
MySQL索引的数据结构：
B+Tree（B+ 树）索引：
MySQL主要的索引类型。用于 InnoDB、MyISAM、Memory 等存储引擎。
支持全值匹配、范围查询和前缀查找。
主键索引是聚簇索引，数据直接存储在索引的叶子节点上。
Hash（哈希）索引：
基于哈希表，适用于等值查询。
适用于 Memory 存储引擎。当某个索引值被频繁访问时，InnoDB会在B-Tree索引上再创建一个Hash 索引，这使得某些查找操作更加高效。
查找速度非常快，但缺点是数据插入和删除的速度较慢，且占用更多的内存。
SQL语句可能不使用索引的情况
使用函数或者算术运算
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
查询优化器评估全表扫描比索引扫描更高效
表的数据量小，全表扫描会更快
like 查询中，使用前置通配符，即like在前。（like ‘%value’）
- 当%在前时：由于通配符在前，查询需要检查所有的记录来查找匹配项，因为索引是基于字段值的顺序构建的，所以当查询的开始部分是不确定的，数据库无法利用索引快速定位数据，只能执行全表扫描来查找匹配的行，导致索引失效。
- 当%在后时：通配符在后，查询只需要匹配以关键字开头的任意字符，这种情况下，数据库可以利用索引快速定位到关键词开始的第一条记录，然后顺序扫描，直到不再匹配为止。
- 数据类型不匹配：
- 如
WHERE column = '123'而不是WHERE column = 123，这可能导致索引不被使用。
- NULL 值查询：
- 查询涉及
IS NULL或IS NOT NULL时，索引的使用可能会受到限制，尤其是在某些数据库配置下。
下面是对不同 COUNT 用法的性能分析和排序：
COUNT(*)：
COUNT(*)统计表中的行数，不忽略任何行（包括含有 NULL 值的行）。它不关注表中的任何特定列，只是简单地计数所有行。- 在多数数据库系统中，
COUNT(*)被特别优化，因为它只需要遍历索引或数据行来计算总行数，不需要查看列值。 - 统计所有行，包括包含 NULL 的行。
COUNT(1)：
COUNT(1)实质上与COUNT(*)功能相同，因为1是一个常量表达式，表示“对每行计数”，与COUNT(*)一样，它不涉及任何列的内容。- 在大多数现代数据库中，
COUNT(1)和COUNT(*)的性能非常接近，因为优化器识别这种计数模式并执行相同的操作。 - 统计所有行，包括包含 NULL 的行。
COUNT(主键字段)：
COUNT(主键字段)统计主键字段非 NULL 的行数。由于主键字段不允许有 NULL 值，因此COUNT(主键字段)实际上与COUNT(*)一样，都是统计所有行。- 这种方式的性能通常与
COUNT(*)接近，但如果数据库优化器不充分识别这一点，可能略微有额外开销，因为它需要检查主键字段。 - 由于主键字段不能为 NULL，所以其结果和
COUNT(*)相同，即统计所有行。
COUNT(字段)：
COUNT(字段)统计指定字段非 NULL 的行数。这需要访问具体的列数据，并检查每个列值是否为 NULL。- 如果该字段不是索引的一部分，这可能导致较慢的性能，因为数据库需要加载每一行的实际数据来检查该字段。
只统计那些指定字段不为 NULL 的行。
MySQL的索引结构为什么要选B+树？
二分查找树是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大/最小的元素，就会导致二分查找树退化成一个链表，此时查询复杂度就会从O(logn)降低为O(n)。
自平衡二叉树保证了查询操作的时间复杂度就会一直维持在O(logn)。但是它本质上还是一个二叉树，每个节点只能有2个子节点，随着元素的增多，树的高度会越来越高。
树的高度通常决定了查询时潜在的磁盘I/O操作次数，因为树结构是存储在磁盘上的，并且访问树中的每个节点都可能对应一次磁盘I/O操作，尤其是当节点数据不在内存中时。因此，树的高度越高，每次查询可能涉及的磁盘I/O次数越多，从而可能影响查询的性能。然而，使用内存缓冲等技术可以减少这种影响。
而B树和B+树都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。
但是MySQL默认的存储引擎InnoDB采用的是B+树作为索引的数据结构，原因有：
- B+树的非叶子节点不存放实际的记录数据，仅存放索引，因此在数据量相同的情况下，相比存储即存索引又存记录的B树，B+树的非叶子节点可以存放更多的索引，因此B+树可以比B树更[矮胖]，查询底层节点的磁盘I/O次数会更少。
- B+树有大量的冗余节点（所有非叶子节点可以存放更多的索引），这些冗余索引让B+树在输入、删除的效率都更高，比如删除根节点的时候，不会像B树那样发生复杂的树的变化。
- B+树叶子节点之间用链表连接了起来，有利于范围查询，而B树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及到多个节点的磁盘I/O操作，范围查询效率不如B+树。
聚簇索引和非聚簇索引的区别
聚簇索引 VS 非聚簇索引
- 聚簇索引
- 将数据行直接存储在索引的叶子节点上。
- 一个表只能有一个聚簇索引，通常是主键。
- 非聚簇索引
- 索引和数据行分开存储，索引项包含指向数据行的指针。
- 一个表可以有多个非聚簇索引。
- 查询可能会导致回表操作，即通过索引找到数据行的实际位置。
MySQL的全表扫描
全表扫描是指数据库执行查询操作时，不通过索引，逐一读取表中的每一行来确定哪些行符合查询条件。
何时会触发全表扫描：
表中没有建立索引或者查询的时候没有使用到索引；
使用了函数或表达式；
查询优化器觉得全表扫描更高效。
全表扫描的优势：
简单：不需要考虑索引维护和构建的复杂性。
适用于小表：对于非常小的表，全表扫描很可能比建立和维护索引更快。
避免索引的开销：没有索引，就没有更新索引的开销。
全表扫描的劣势：
对于大表来讲，效率较低
I/O开销大，全表扫描会导致大量的磁盘I/O。
MySQL 回表
场景：当通过非聚簇索引查询数据时，从返回的数据里面，无法获取所需的全部数据（即查询的列不是索引的一部分），系统就需要再次通过主键索引（聚簇索引）去查询剩下这部分数据。这个过程被称为”回表“。回表是一个附加的步骤，可能会导致额外的I/O操作。
性能影响：回表操作涉及至少两次磁盘访问，第一次是通过非聚簇索引查询数据时，第二次是通过聚簇索引（即主键索引）查询数据时。在数据行分散或索引不是完全覆盖查询需求的情况下，回表操作可能成为性能瓶颈，尤其在数据量大或查询复杂时。
发生回表的场景：
当查询的列不在索引中时，即查询的列超出了索引所覆盖的范围。
使用
select *查询时，因为表示所有列，而非聚簇索引只包含索引列和主键列，所有需要回表获取其他列的数据。当查询条件（WHERE子句）使用了非聚簇索引进行筛选，但返回的结果集（SELECT子句）中包含了非索引列。
避免和解决回表问题
- 优化索引设计，使用覆盖索引：覆盖索引是非聚簇索引，它包含了查询所有列。当查询只涉及索引中的列时，就可以直接从索引中获取数据，无需回表。例如，如果你经常查询name和age，你可以创建一个包含name、age和id(主键)的索引。
在使用覆盖索引时，列的顺序很重要，它影响了索引的使用方式和效率。如果你为 age 和 name 建立了一个复合索引，索引的创建顺序会影响查询的性能。这里是几个关键点：
索引的顺序：如果索引是按
(age, name)的顺序建立的，那么在查询时首先使用age作为过滤条件的查询将能高效地利用这个索引。例如，SELECT age, name FROM table WHERE age = 30能够充分利用该索引。但如果查询主要基于name而非age，如SELECT age, name FROM table WHERE name = 'John'，那么这个索引可能不会被优化器选择，因为name是索引的第二部分。最左前缀原则：在使用复合索引时，数据库遵循最左前缀原则，这意味着查询条件需要从索引的最左边开始匹配。如果索引是
(age, name)，那么只有当age部分在 WHERE 子句中被使用时，索引才能被有效地使用。如果查询直接使用name而忽略age，则这个索引的效用会大大降低。（又或者age和name同时使用时，age必须在第一个where条件中，否者同样不会生效）多条件查询：如果查询涉及多个条件且这些条件对应于索引中的列，那么索引的效率取决于这些条件是如何使用索引列的。例如，
SELECT age, name FROM table WHERE age = 30 AND name = 'John'可以完美地利用(age, name)索引。
限制查询的列：避免使用 select * ，而是明确指定所需的列，并且如果这些列都包含在索引中，就可以避免回表。
使用聚簇索引查询：如果可能，直接使用主键（聚簇索引）进行查询，这样可以直接获取所有列的数据，无需回表。
注：在 InnoDB 存储引擎中，每个表都有一个聚簇索引，通常是主键。如果你没有明确指定一个主键，InnoDB 会选择一个唯一的非空索引作为聚簇索引。如果表中没有这样的索引，InnoDB会为表生成一个隐藏的、包含6字节长度的聚簇索引。
MySQL 页
在 MySQL 中，“页” 通常是指数据库存储引擎中数据存储的基本单位。在 InnoDB 存储引擎中，页的默认大小是 16KB。数据页用于存储表中的行数据，索引页用于存储索引数据。
- 数据页和事务日志：
- InnoDB 存储引擎将数据存储在数据页中。每当数据被修改（如通过 insert、update或delete操作）时，InnoDB 不仅会更改磁盘上的数据页，而且还会在事务日志（称为redo log）中记录这些更改。这是为了确保在系统崩溃后能够恢复数据。
- 回滚段（Undo Logs）：
- 当事务进行修改时，原始数据会存储在所谓的回滚段中。这些回滚段实际上也存储在数据页中。它们允许数据库在事务失败或明确地回滚时恢复到原始状态。同时，回滚段支持MVCC，使得不同的事务可以看到数据的不同历史版本。
- 事务的一致性视图：
- InnoDB 通过在每个事务开始时创建一个一致性视图来实现 MVCC。这意味着每个事务可以看到在其启动时刻之前已经提交的数据的快照。这个机制依赖于回滚段中的数据来提供旧版本的数据，允许对同一数据的并发读写操作。
- 锁定和并发控制：
- 页面级的锁定（虽然 InnoDB 主要使用行级锁）可以用于管理对共享数据的并发访问。这有助于处理死锁和提高并发事务的性能。
MySQL 的 InnoDB 和 MyISAM 的区别
| 区别点 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | 支持ACID事务，有提交和崩溃回滚功能 | 不支持 |
| 锁定机制 | 采用行级锁，进行数据修改时只锁定所涉及的行 | 采用表级锁，进行写操作时会锁定整个表 |
| 外键 | 支持外键 | 不支持 |
| 崩溃恢复 | 由于其事务日志、双写缓冲区和自动崩溃恢复功能，InnoDB在系统崩溃后可以恢复到一致的状态 | 没有事务日志，因此在崩溃后可能需要修复或重建表 |
| 性能 | 通常在需要高并发写入的应用中表现更好，因为它使用行级锁定 | 在只读或大量读的应用中可能会更快 |
| MVCC (多版本并发控制) | 支持 | 不支持 |
总结：因为MyISAM相对简单所以在效率上要优于InnoDB，如果系统读多写少，同时表数据量不是很大，并对原⼦性要求低，那么MyISAM最好的选择。且MyISAM恢复速度快。可直接⽤备份覆盖恢复。如果系统读少，写多的时候，尤其是并发写⼊⾼的时候。InnoDB就是⾸选了。
MySQL 的主备同步（主从复制）
主从复制是一种常用的数据备份和读取扩展策略。它允许数据从一个 MySQL 数据库服务器（主服务器）复制到一个或多个MySQL 数据库服务器（从服务器）。
MySQL 主备复制实现分成三个步骤：
master 将所有数据更改（如 INSERT、UPDATE、DELETE 命令）都会记录在二进制日志中（binary log）中
slave 将 master 的binary log，简称binlog，拷贝到它的中继日志（relay log）
salve 重做中继日志的事件，将改变反映它自己的数据
SQL 语句执⾏顺序
- FROM 子句
- 首先执行FROM子句，确定查询的数据源，可能是一张表、多张表的连接或子查询。
- ON 子句
- 如果涉及JOIN操作，接下来将执行ON子句以确定如何连接表。
- JOIN 子句
- 执行JOIN操作，根据ON子句的条件确定如何连接表。
- WHERE 子句
- 接着执行WHERE子句，对FROM子句确定的数据源进行行级过滤。
- GROUP BY 子句
- 执行GROUP BY子句，将数据分组以供聚合函数使用。
- HAVING 子句
- 在分组后执行HAVING子句，对分组后的数据进行过滤，HAVING子句通常用于聚合函数的条件过滤。
- SELECT 子句
- 然后执行SELECT子句，选取特定的列。如果使用了聚合函数，此时将计算聚合值。
- DISTINCT 子句
- 如果使用了DISTINCT关键字，将在此阶段对结果进行去重。
- ORDER BY 子句
- 执行ORDER BY子句，对结果集进行排序。
- LIMIT / OFFSET 子句
- 最后执行LIMIT/OFFSET子句，限制返回的结果数量或跳过一定数量的行。
SELECT DISTINCT column1
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE column2 > 10
GROUP BY column1
HAVING COUNT(*) > 1
ORDER BY column1
LIMIT 10
执行顺序为：
104. FROM table1
105. JOIN table2 ON table1.id = table2.id
106. WHERE column2 > 10
107. GROUP BY column1
108. HAVING COUNT(*) > 1
109. SELECT DISTINCT column1
110. ORDER BY column1
111. LIMIT 10
SQL优化方法
避免使用 select * ，只选择需要查询的列
避免在列上使用函数或者算术运算
正确使用索引
- 为经常用于查询条件的列创建索引
- 定期检查并优化索引，删除不再需要的索引
- 避免过度索引，因为索引会增加写操作的开销
- 限制查询的数目以及返回的结果条数
