MySQL(1)：存储引擎、索引

发表于 分类于
SQL语句执行流程、索引(覆盖索引、联合索引、索引下推)、索引优化、索引失效

1 MySQL基础架构

image.png|500
MySQL 可以简单分为 Server 层和存储引擎层这两层，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎。

  • Server 层负责建立连接、分析和执行SQL。所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
  • 引擎层负责数据的存储和读取

1.1 SQL语句执行流程

1.1.1 连接器（长连接、短连接）

- 与客户端进行 TCP 三次握手建立连接；
- 校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
- 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限

1.1.2 查询缓存

解析SQL语句的第一个字段，查看语句类型；如果SQL师查询语句，先查找缓存数据

1.1.3 解析SQL语句（解析器）

- 词法分析
- 语法分析

1.1.4 执行SQL

2.1.4.1 预处理器：

  • 检查 SQL 查询语句中的表或者字段是否存在；

  • 将 select * 中的 * 符号，扩展为表上的所有列；

2.1.4.2 优化器：

  • 优化器主要负责将 SQL 查询语句的执行方案确定下来，为SQL语句指定执行计划（选择某个索引，explain）

2.1.4.3 执行器：与存储引擎交互

  • 主键索引查询

  • 全表扫描

  • 索引下推：索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

执行一条 select 语句，期间发生了什么？ | 小林coding (xiaolincoding.com)

1.2 MySQL一行记录存储

image.png

1.2.1 NULL值列表：

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

1.2.2 记录头信息

  • delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。

  • next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

  • record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

1.2.3 记录的真实数据

  • row_id：有主键或者唯一约束列，没有 row_id 隐藏字段。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。

  • trx_id：事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。

  • roll_pointer：这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

[!Note] 行溢出
MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的==「溢出页」==中。当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

MySQL 的 NULL 值是怎么存放的？

MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。

NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

MySQL 怎么知道 varchar(n) 实际占用数据的大小？

MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。

2 MySQL存储引擎

2.1 MyISAM和InnoDB

  • 是否支持行级锁：MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

  • 事务支持： InnoDB支持事务，具有提交、回滚和崩溃恢复能力，可以保护用户数据。它使用了一种称为“写前日志”（write-ahead logging，WAL）的技术，先写日志，再写数据，保证了事务的完整性。 MyISAM不支持事务，这意味着在MyISAM中进行的更改无法回滚，且如果发生崩溃，数据可能会损坏。

  • 是否支持外键：MyISAM 不支持，而 InnoDB 支持。外键有助于维护数据的引用完整性和一致性，但是对性能有一定的损耗。

  • 是否支持数据库异常崩溃后的安全恢复：MyISAM 不支持，而 InnoDB 支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

  • 是否支持 MVCC：MyISAM 不支持，而 InnoDB 支持。

  • 数据存储索引实现不一样：都使用B+Tree作为索引结构。 InnoDB使用聚簇索引来组织数据，其数据文件本身就是索引文件,表数据和主键索引存储在一起。此外，InnoDB还支持全文索引和哈希索引。 MyISAM将数据和索引分开存储，数据文件（MYD）和索引文件（MYI）是分离的。MyISAM也支持全文索引，但不支持哈希索引。

  • 数据缓存策略和机制实现不同：InnoDB 使用缓冲池（Buffer Pool）缓存数据页和索引页，MyISAM 使用键缓存（Key Cache）仅缓存索引页而不缓存数据页。

  • 性能： InnoDB在处理大量读写操作时通常表现更好，特别是在高并发环境下。这得益于其行级锁和事务支持。 MyISAM在处理只读查询时可能更快，特别是当数据量很大且内存充足时。然而，在写操作频繁的场景下，MyISAM的性能可能会下降。

  • 存储空间： InnoDB的存储空间通常比MyISAM大，因为它需要额外的空间来存储事务日志、行级锁等信息。此外，InnoDB的数据文件大小是动态增长的。 MyISAM的存储空间相对较小，数据文件大小是固定的（可以通过配置修改）。但是，MyISAM不支持数据压缩功能。

MyISAM适合： 插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择， 没有事务。 InnoDB适合： 可靠性要求比较高，或者要求事务； 表更新和查询都相当的频繁， 大量的INSERT或UPDATE

InnoDB存储引擎：B+树索引的叶子结点保存数据本身
MyISAM存储引擎：B+树索引的叶子结点保存数据的物理地址

3 乐观锁和悲观锁

悲观锁，先获取锁，再进行业务操作，一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。 当数据库执行SELECT … FOR UPDATE时会获取被select中的数据行的行锁，select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

乐观锁，先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过。Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。

4 MVCC（多版本并发控制）

通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

多版本并发控制（MVCC，Multiversion Concurrency Control），是行级锁的变种，在很多情况下避免了加锁操作，因此开销更低。实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

  • undo log : undo log 用于记录某行数据的多个版本的数据。

  • read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

InnoDB存储引擎对MVCC的实现 | JavaGuide

5 索引

索引是存储引擎用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构，提高查询效率。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；

  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；

  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

5.1 底层数据结构

5.1.1 AVL树

在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。 磁盘 IO 是一项耗时的操作

5.1.2 B树和B+树

  • B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

  • 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

5.1.3 为什么选择B+树作为索引

文件索引和数据库索引存放在硬盘上，性能瓶颈在IO上。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

  • B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

  • B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

  • B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

5.2 索引类型

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。

  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。

  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

  • 按「字段个数」分类：单列索引、联合索引。

5.2.1 数据结构

B-Tree索引

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据

  • MyISAM索引通过数据的物理位置引用被索引的行，B+Tree 叶节点的 data 域存放的是数据记录的地址（非聚簇索引），索引文件和数据文件分离

  • InnoDB则根据主键引用被索引的行。其数据文件本身就是索引文件。 InnoDB 表数据文件本身就是主索引，辅助索引的 data 域存储相应记录主键的值而不是地址。

可以使用B-Tree索引的查询类型

  • 全值匹配：全值匹配指的是和索引中的所有列进行匹配

  • 匹配最左前缀

  • 匹配列前缀：也可以只匹配某一列的值的开头部分

  • 匹配范围值

  • 精确匹配某一列并范围匹配另外一列

哈希索引

不支持顺序和范围查询

全文索引

对文本的内容进行分词，进行搜索，适用于字符串列

5.2.2 物理存储

5.2.2.1 主键索引与二级索引

主键索引：聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点二级索引：叶子节点存放的是主键值，而不是实际数据。

在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引(索引包含（或者说覆盖）所有需要查询的字段的值)。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

5.2.2.2 聚簇(集)索引与非聚簇(集)索引

  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。在聚簇索引中，数据行按照索引键值的顺序存储，也就是说，索引的叶子节点包含了实际的数据行。

  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。

因为写入是乱序的，innodb 不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，影响性能。 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。

聚簇索引优缺点：

  • 二级索引访问需要两次索引查找，而不是一次。二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。InnoDB在移动行或者数据页分裂时无须更新二级索引中的这个“指针”。

image.png|475

5.2.3 字段特性

唯一索引

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
  UNIQUE KEY(index_column_1,index_column_2,...) 
);


CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

  • 使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

5.2.4 字段个数

单列索引

联合索引

建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

[!note] 联合索引底层存储结构(和其他种类的索引的存储结构有什么区别?

联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配

联合索引是数据库中一种常见的索引类型，它允许在多个列上创建索引，以提高查询性能。与单列索引相比，联合索引的底层存储结构有一些区别，主要体现在如何组织和存储索引数据的方式上。

  1. 存储数据的组织方式：

  2. 索引维护的复杂性：

    • 单列索引：单列索引的维护相对简单，因为它只需要维护单个列的值和指针。
    • 联合索引：联合索引的维护相对复杂一些，因为它需要考虑多个列值的组合。当表中的数据发生变化时，数据库需要确保联合索引中的多个列值的组合保持有序，这可能需要更多的资源和时间。

联合索引在适当的情况下可以提供更好的查询性能，特别是对于涉及到联合索引中列值的组合的查询。然而，需要注意的是，联合索引的创建和维护可能会带来一些额外的开销，并且需要根据具体的查询需求和数据模式来合理选择索引策略。

[!question]
select * from t_table where a > 1 and b = 2
只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

[!question]
select * from t_table where a >= 1 and b = 2
从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描，Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询

[!question]
SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2
 MySQL 的 BETWEEN 包含 value1 和 value2 边界值，所以类似于 Q2 查询语句，因此 Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

[!question]
SELECT * FROM t_user WHERE name like ‘j%’ and age = 22
二级索引记录的 name 字段值为 ‘j’ 时，可以通过 age = 22 条件减少需要扫描的二级索引记录范围（age 字段可以利用联合索引进行索引查询的意思）。也就是说，从符合 name = 'j' and age = 22 条件的第一条记录时开始扫描，而不需要从第一个 name 为 j 的记录开始扫描 。

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，==对于 >=、<=、BETWEEN、like 前缀匹配的范围查询,并不会停止匹配。

5.2.5 冗余索引和重复索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引

5.3 索引的优点（三星评价）

索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得“三星”。

5.4 最左匹配

查询只能使用索引的最左前缀，直到遇到第一个范围条件列。最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止，范围查询的字段可以用到联合索引。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配

当MySQL使用联合索引进行查询时，会从索引的最左边开始匹配查询条件，如果查询条件中包含了索引左边的列，那么MySQL才可能会使用这个索引来优化查询。如果查询条件没有包含左边的列或者左边的列出现一些范围查询（>、<、between等），即使其他列都在索引中，MySQL也可能不会使用这个索引。

5.5 索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。将Server层筛选数据的工作，下推到引擎层处理。

在没有索引下推的情况下，MySQL会先从索引中获取所有可能满足条件的数据行的主键，然后根据这些主键检索完整的数据行(即回表)，最后再应用WHERE条件进行过滤。而有了索引下推，MySQL可以在扫描索引的同时，直接根据索引中的值来判断是否满足WHERE条件，从而避免不必要的回表操作。

对于联合索引(a,b)，在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引。在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后：

  • 不使用索引下推：从ID=2（主键值）回表，在主键索引中找出数据行，在对比b字段值

  • 索引下推：可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。（Using index condition）

对于联合索引，在遇到范围查询时，后面的索引无法被用到。不使用索引下推，则根据主键值回表查询。但是可以根据索引包含的字段先做判断

5.6 什么时候需要 / 不需要创建索引？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

  • 需要占用物理空间，数量越大，占用空间越大；

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；

  • 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

5.6.1 什么时候适用索引？

  • 字段有唯一性限制的，比如商品编码；

  • 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。

  • 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

  • 字段非空

  • 字段区分度高

5.6.2 什么时候不需要创建索引？

  • WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。

  • 字段不具有区分度，字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

  • 表数据太少的时候，不需要创建索引；

  • 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

5.7 索引优化

  • 前缀索引优化：使用字符串的前几个字符建立索引，减少索引字段大小

  • 覆盖索引：要查询的字段，在叶子节点上都能找到对应的索引

  • 主键索引最好是自增的：每次插入一条新记录，都是追加操作，不需要重新移动数据

  • 索引最好设置为NOT NULL

  • 防止索引失效，最左前缀匹配

  • 选择合适的列进行索引，选择经常用于查询条件的列，避免对经常修改的列建立索引

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

5.7.1 NULL和’'的区别

  • NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。

  • ''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。

  • NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。

  • 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

5.8 索引失效

  • 使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

  • 在查询条件中对索引使用函数，即对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；

  • 对索引进行表达式计算

  • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

  • 对索引隐式类型转换（索引字段是字符串类型，但是在条件查询中，输入的参数是整型）

  • 在 where 子句中使用 != 或 not in 或 <> 操作符

  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

//对索引进行表达式计算
EXPLAIN select * from department where dept_id+1=10;
EXPLAIN select * from department where dept_id=11;

//对索引使用函数
EXPLAIN select * from department where LENGTH(dept_name)=5;
ALTER TABLE department add key idx_name_length ((LENGTH(dept_name)));

5.8.1 隐式类型转换造成索引失效

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

  • 当操作符左右两边的数据类型不一致时，会发生隐式转换。

  • 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。

  • 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。

  • 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

//date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换
select * from news where date_str = 201701

5.9 回表查询

回表查询是指在使用非聚集索引进行查询时，需要通过索引中的指针回到主键索引或者聚集索引中获取完整的数据行的过程。回表查询通常发生在以下场景中：

  1. 需要查询的字段不在非聚集索引中：当查询的字段不在非聚集索引中时，数据库引擎无法直接从索引中获取完整的数据行，而是需要通过回表操作到主键索引或聚集索引中获取完整的数据行。

  2. 需要返回的数据超过了非聚集索引的覆盖索引能力：覆盖索引是指索引中包含了查询所需的所有字段，可以直接从索引中获取查询结果，而无需回表操作。但是，如果需要返回的数据超过了非聚集索引的覆盖索引能力，仍然需要进行回表查询。

  3. 使用了索引优化的查询：有些查询语句可能会使用到索引优化，例如使用了索引的覆盖扫描、索引合并等技术，这些优化可能会导致回表查询的发生。

回表查询会增加额外的IO操作，因为需要通过指针再次访问主键索引或聚集索引。在一些对查询性能要求较高的场景中，可以考虑使用覆盖索引或者调整查询语句的优化方式，以减少回表查询的次数。

回表查询并非一定是性能问题，有时候回表查询是必要的，特别是在需要返回完整数据行的情况下。在设计数据库表和索引时，需要根据具体的业务需求和查询场景来选择合适的索引策略，以达到最佳的查询性能。

5.10 Count

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再来，就是不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。

如果参数是 (COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。

6 执行计划（Explain)

  • possible_keys 字段表示可能用到的索引；

  • key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；

  • key_len 表示索引的长度；

  • rows 表示扫描的数据行数。

  • type 表示数据扫描类型，我们需要重点看这个。

6.1 type

  • All（全表扫描）；

  • index（全索引扫描）；

  • range（索引范围扫描）；

  • ref（非唯一索引扫描）；

  • eq_ref（唯一索引扫描）；

  • const（结果只有一条的主键或唯一索引扫描）。

6.2 extra

  • Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。

  • Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。

  • Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

7 慢查询

  • 慢查询日志

  • 分析查询语句：使用EXPLAIN命令分析SQL执行计划，找出慢查询的原因，比如是否使用了全表扫描，是否存在索引未被利用的情况等，并根据相应情况对索引进行适当修改。 (索引失效、覆盖索引)

  • 创建或优化索引：根据查询条件创建合适的索引，特别是经常用于WHERE子句的字段、Orderby 排序的字段、Join 连表查询的字典、 group by的字段，并且如果查询中经常涉及多个字段，考虑创建联合索引，使用联合索引要符合最左匹配原则，不然会索引失效

  • 避免索引失效：比如不要用左模糊匹配、函数计算、表达式计算等等。 查询优化：避免使用SELECT *，只查询真正需要的列；使用覆盖索引，即索引包含所有查询的字段；联表查询最好要以小表驱动大表，并且被驱动表的字段要有索引，当然最好通过冗余字段的设计，避免联表查询。

  • 分页优化：针对 limit n,y 深分页的查询优化，可以把Limit查询转换成某个位置的查询：select * from tb_sku where id>20000 limit 10，该方案适用于主键自增的表，

  • 优化数据库表：如果单表的数据超过了千万级别，考虑是否需要将大表拆分为小表，减轻单个表的查询压力。也可以将字段多的表分解成多个表，有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。 （分库分表）

  • 使用缓存技术：引入缓存层，如Redis，存储热点数据和频繁查询的结果，但是要考虑缓存一致性的问题，对于读请求会选择旁路缓存策略，对于写请求会选择先更新 db，再删除缓存的策略。

7.1 深分页优化

limit偏移量太大，回表查询导致查询效率低

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

  1. 通过普通二级索引树idx_update_time，过滤update_time条件，找到满足条件的记录ID。

  2. 通过ID，回到主键索引树，找到满足记录的行，然后取出展示的列（回表）

  3. 扫描满足条件的100010行，然后扔掉前100000行，返回。

  • 子查询优化：查询满足条件的数据行，再走主键索引

  • 索引优化：覆盖索引

  • INNER JOIN延迟关联：是把条件转移到主键索引树，然后减少回表

  • 标签记录法：记录上次查询的最后一条记录

实战！聊聊如何解决MySQL深分页问题-腾讯云开发者社区-腾讯云 (tencent.com)

8 参考

image.png