Keeping T00
MySQL--从架构原理到索引及SQL优化
T00 Lv2
  2025-07-31 12:23:05   2025-08-09 15:59:09    12.1k Words  

体系结构/SQL执行过程

在实际开发中，我们写下的 SQL 并不会直接拿去执行，而是要经过 MySQL 内部多个组件的协作处理。这就像你发出一个“订单”，需要经过接单、分配、调度、执行等流程，最终才拿到结果。

了解这些执行流程，是我们掌握 SQL 优化、索引调优和事务隔离等高级特性的前提。

下图为MySQL的体系结构：

image

我们编写的每一条SQL语句都会经过这个流程：

  1. 建立连接：使用到MySQL中的连接器
    1. 在真正建立 MySQL 会话之前，操作系统底层要先通过 TCP 协议建立连接，经历三次TCP握手。（这一步与MySQL的连接器无关）；
    2. 校验用户名和密码；
    3. 校验权限；
    4. 初始化会话。
  2. 查询缓存（MySQL8.0已废弃，因为对于更新比较频繁的表，查询缓存命中率低）：key为SQL语句，value为查询结果。
  3. 分析器：分为词法分析和语法分析，对SQL语句拆分并做如下分析
    1. 词法分析：提取关键字；
    2. 语法分析：语法校验，构建SQL语法树，便于后续模块读取字段。
  4. 预处理：检验SQL语句中的表和字段是否存在，将select *中的*拓展为表上的所有列。
  5. 优化器：
    1. 表里有多个索引时，决定用哪个索引；
    2. SQL语句中有多表连接时，决定连接顺序。
  6. 执行器：调用相应引擎接口执行SQL语句。
  7. 存储引擎（默认为InnoDB)：真实读写数据。
  8. 返回结果。

TCP（Transmission Control Protocol） 是传输控制协议，它是一种面向连接、可靠传输、按顺序交付的数据传输协议。

TCP 在传输前需要先建立连接，这叫三次握手（Three-way Handshake）：

  1. 客户端 → 服务端：发送 SYN 请求（我要连你）
  2. 服务端 → 客户端：返回 SYN + ACK（我知道你要连）
  3. 客户端 → 服务端：发送 ACK 确认（我也知道你知道我要连）

接下来我们来介绍MySQL中的存储引擎。

字段执行顺序

  1. from：查询操作首先肯定要先知道是查的哪张表
  2. join：和from一起走
  3. where：知道查哪张表后，才能根据条件检索对应的数据，过滤原始行
  4. group by：按照字段分组
  5. having：和聚合函数一起用的话，就执行函数后再过滤数据
  6. select：到这数据库再决定返回什么数据
  7. order by：返回需要的数据后才能排序
  8. limit：最后才能截取，才知道你要哪几行

搭建数据基础：from，join

预处理：where，gruop by，聚合。having

字段选取和加工：select，distinct

最后结果整理：order by，limit

存储引擎

对于存储引擎，我们可能比较陌生，但是引擎这个概念倒是家喻户晓。

对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

  1. 建表时指定存储引擎：
1
2
3
4
5
 CREATE TABLE  表名(
字段1  字段1类型   [ COMMENT  字段1注释 ] ,
 ......
字段n  字段n类型   [COMMENT  字段n注释 ] 
) ENGINE = INNODB   [ COMMENT  表注释 ] ;
  1. 查询当前数据库支持的存储引擎
1
show engines;
image

里面比较重要的引擎有：InnoDB、MyISAM、Memory。

  1. 查询建表语句
1
show create table account;
image

我们之前建表的时候没有指定引擎，可见默认的存储引擎就是InnoDB。

上面我们介绍了什么是存储引擎，以及如何在建表时如何指定存储引擎，接下来我们就来介绍下来上面重点提到的三种存储引擎 InnoDB、MyISAM、Memory的特点。

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。

特点：

  1. 支持事务；
  2. 行级锁，提高并发访问性能；
  3. 支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

文件：

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。

参数：innodb_file_per_table

1
show variables like 'innodb_file_per_table';
image

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。 我们直接打开MySQL的 数据存放目录：

image

可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。

逻辑存储结构：

image

表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。

段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。

区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为 16K， 即一个区中一共有64个连续的页。

页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默 认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

MyISAM

MyISAM是MySQL早期的默认存储引擎。

特点：

  1. 不支持事务，不支持外键

  2. 支持表锁，不支持行锁

  3. 访问速度快

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

  1. 内存存放hash索引（默认），检索速度快，适合做缓存
  2. 表锁

三者区别及特点

特点 InnoDB MyISAM Memory
事务安全 支持 - -
锁机制 行锁 表锁 表锁
支持外键 支持 - -
  • 问：InnoDB引擎与MyISAM引擎的区别 ?
    1. InnoDB支持事务，外键，行级锁，而MyISAM不支持事务，外键，只有表锁，并发性能低。
    2. InnoDB支持数据库异常崩溃后的安全恢复，依赖于redo_log，而MyISAM不支持。
    3. InnoDB支持MVCC，而MyISAM不支持。

选择

InnoDB: 是Mysql的默认存储引擎，支持事务、外键，行级锁，可以保证事务的完整性和高并发性能，所以成为了MySQL的默认引擎。

MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。（MongoDB）

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。（Redis）

索引机制详解

**索引（index）**是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

概述

索引其实就可以理解成查字典时的目录，在没有目录前，查一个字就是一页页地去翻，这就是全表扫描，用目录就是用索引。

优势：提高数据检索的效率，降低数据库 的IO成本通过索引列对数据进行排序，降低 数据排序的成本，降低CPU的消耗。

劣势：索引列也是要占用空间的。 索引大大提高了查询效率，同时却也降低更新表的速度， 如对表进行INSERT、UPDATE、DELETE时，效率降低。

基本语法

创建索引

基本语法如下：

1
2
CREATE  [ UNIQUE | FULLTEXT ]  INDEX  index_name  ON  table_name  ( 
index_col_name,... ) ;

索引名成规范一般为idx_表名_字段名.

完成以下要求：

A. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

1
create index idx_user_name on tb_user(name);

B. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

1
create index idx_user_phone on tb_user(phone);

C. 为profession、age、status创建联合索引。 

1
create index idx_user_pro_age_sta on tb_user(profession,age,status);

D. 为email建立合适的索引来提升查询效率。 

1
create index idx_email on tb_user(email);

查看索引

1
SHOW  INDEX  FROM  table_name ;

下面查看一下我们刚刚创建的索引：

1
show index from tb_user;
image

删除索引

1
DROP  INDEX  index_name  ON  table_name ;

索引分类

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构 描述
B+Tree索引 最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索 引） 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类 型，通常使用较少。
Full-text(全文 索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES

注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

为什么InnoDb使用B+树索引结构

从索引的介绍来看，不难知道，索引就是用来提升检索效率的，那可以实现快速搜索的数据结构有：哈希表，二叉树，红黑树，B树，B+树，接下来我们就一个个分析：

  1. hash表：用唯一的key来查对应的value，虽然很快，但是它没法做范围查询，也没法排序，如下图image如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可 以通过链表来解决，image
  2. 二叉排序树

左节点小于右节点，右节点大于根，中序遍历是有序的，可以排序也可以范围查询，理想状态下如下图：

image

但是一旦按照主键顺序插入，它就会退化为链表，性能很差如下图：

image

这时你可能会想到二叉平衡树

  1. 二叉平衡树：在插入节点的时候，使用旋转使二叉树保证平衡，左右子树高度差不大于1，这样虽然能避免退化为链表，但是由于它会追求绝对平衡，会频繁旋转，在插入数据时，就会带来大量磁盘IO，降低性能。
  2. 红黑树：红黑树也是一种自平衡的二叉排序树，通过插入删除数据时，进行变色和旋转来保持平衡，它不追求绝对平衡，所以大大降低了旋转操作，但是它的本质是二叉树，一个节点只有两个孩子，存储大量数据时，树高很高，性能自然就没那么好。
  3. B树：它是一种多路平衡排序树，相对于二叉树，B树每个节点可以有多个分支，即多叉，下面展示一个最大度数（max-degree）为5(5阶)的b-tree（每个节点最多存储4个key，5 个指针）：image

可以看见它的节点又存索引又存数据，所以一个节点存不了多少索引，数据量很大的时候，树还是会比较高；而且做范围查询时，需要回溯，效率很低。

  1. B+树：它也是多路平衡排序树，而且它的非叶子节点上只存索引值，不存数据，那么单个节点能存的索引值就很多了，树高更低，性能更好；而且B+树会把所有数据存放在最下层叶子节点中，并且由双向链表连接，范围查询只需遍历链表，不用回溯。
image
  • 那B+树中具体是咋存储数据的呢？

聚集索引 vs 二级索引

其实MySQL索引可以就分为聚集索引和二级索引（也可称为非聚集索引）。

  • 聚集索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有,而且只有一个（主键索引，由于主键在B+树中，决定了主键就是自增插入，可以避免页分裂带来的性能问题）。

聚集索引选取规则: 如果存在主键，主键索引就是聚集索引。 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引.

  • 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个，在查找的时候先查出当前对应的主键id再回表查出对应的行数据，所以叫二级节点。

二者如下图：

image

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

1
select * from user where name = 'Arm';
  1. 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查 找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
  2. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最 终找到10对应的行row。
  3. 最终拿到这一行的数据，直接返回即可。
  • 思考：InnoDB主键索引的B+tree高度为多高呢?

前面我们可以知道InnoDB的结构如下：

image

然后一个节点占用一页，如下图：

image

假设: 一行数据大小为1k，那么一页中可以存储16行这样的数据；InnoDB的指针占用6个字节的空间，主键如果为bigint，占用字节数为8。

高度为2的时候： n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为1170，总共就是1170*16=18720 也就是说，如果树的高度为2，则可以存储 18000 多条记录。

高度为3： 1171 * 1171 * 16 = 21939856也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

这样变相的证明了B+树做为索引结构的优越性

SQL性能分析

前面说了这么多关于索引的理论知识，接下来，我们来介绍关于如何得知SQL性能好坏，以便于我们更好的使用索引。

慢日志查询

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log，判断是否开启

1
show variables like 'slow_query_log';
image

可以临时开启慢查询日志：

1
set global slow_query_log='ON';

也可以更改配置文件增加以下两条配置文件开启：

1
2
3
4
# 开启MySQL慢日志查询开关
slow_query_log=1
 # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置过后一定要重启MySQL服务。

可以使用以下代码查看慢日志存储在哪

1
show variables like 'slow_query_log_file';
image image

我们提前准备了一个千万数据表tb_sku，来试着执行一条SQL

1
select count(*) from tb_sku;
image

可以看见日志里面就记录了这条sql语句。

proile的使用

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_profiling 参数，能够看到当前MySQL是否支持profile操作

1
select @@have_profiling;
image

如果没有开启，可以通过以下指令开启：

1
SET profiling = 1;

开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去 了。

我们直接执行如下的SQL语句：

1
2
3
4
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:

1
show profiles; -- 查看每一条SQL的耗时基本情况
image 
1
show profile for query query_id;-- 查看指定query_id的SQL语句各个阶段的耗时情况

可以看见具体的执行耗时，最长的就是执行：

image 
1
show profile cpu for query query_id;  -- 查看指定query_id的SQL语句CPU的使用情况

explain的使用

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行 过程中表如何连接和连接的顺

1
2
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；

准备三个表，我们来逐步演示每个字段的含义：

image

他们之间的关系如下：

image

id

1
2
-- 查询每个学生的选课情况
explain select s.*,c.* from student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid;

id如果是相同的，从上而下的执行，如图就是先查s，后查sc，最后c

image 
1
explain select * from student s where s.id in (select studentid from student_course sc where sc.courseid = (select C.id from course c where c.name = 'MySQL'));

id不同大的先执行

image

select_type

表示 SELECT 的类型，参考意义不大

type

表示连接类型，性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all

一般来说到const就是最好的了;

null一般就是你给它什么，它查出什么，比如：

image

system就是访问系统表；

const就是访问主键或者索引：

image

ref是非唯一性possible_key索引：

image

possible_key

顾名思义就是可能用到的索引。

key

实际使用的索引，如果为NULL，则没有使用索引。

key_len

表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。

rows

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比， filtered的值越大越好。

Explain 执行计划中比较重要字段的含义:

字段 含义
type 表示连接类型
possible_key 显示可能应用在这张表上的索引，一个或多个。
key 实际使用的索引，如果为NULL，则没有使用索引。
key_len 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。

索引实战

接下俩我们开始实际探讨索引的使用及其一些特性。

验证索引效率

我们查询我们之前创建的千万行表的一条没有索引的数据，可以看见尽管只有一条数据，它也查询了15s之久。

image

现在我们给它创建一个索引

1
create index idx_sku_sn on tb_sku(sn);

可以看见有了索引之后，它几乎是瞬间查询出了数据：

image

最左前缀原则

如果索引了多列（联合索引），要遵守最左前缀法则。

最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

我们上面创建了一个联合索引idx_user_pro_age_sta

image

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。

而且中间不能跳过某一列，否则该列后面的字段索引将失效；接下来，我们来演示几组案例，看一下具体的执行计划：

1
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

可以看见成功使用索引了，而且索引长度为54。

image 
1
2
-- 我们删除最后一个字段
explain select * from tb_user where profession = '软件工程' and age = 31;

也使用了索引，并且长度为49，status索引长度应该为5。

image 
1
2
-- 我们再删除一个字段
explain select * from tb_user where profession = '软件工程';

依旧使用了索引，长度为47，age索引长度为2，profession索引长度为47.

image 
1
2
-- 我们只是用age和status查询
explain select * from tb_user where age = 31 and status = '0';

因为没有最左边的字段，所以就没有使用索引，索引失效。

image 
1
2
-- 那如果我们使用profession和status呢?
explain select * from tb_user where profession = '软件工程' and status = '0';

可以看见也使用了索引，索引长度为profession的长度47，并没有status的索引，只是部分走了索引。

image 
1
2
-- 再执行以下代码，是否会走索引呢？
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'；

可以看见是走索引了，而且所有索引都走了，由此可见与条件的排列顺序其实是没有关系的。

image

索引失效场景汇总

索引不是在任何时候都会发生作用的，以下我们就会介绍很多种索引失效的场景。

最左前缀法则

上面已经讲的很清楚了，这里不过多介绍。

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

接下来演示一下：

1
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

走了索引，为profession和age的索引，因为status的在范围查询的右边，所以失效了。

image
  • 有没有规避的方法呢？

有的，就是在业务允许的情况下，只使用类似于 >= 或 <= 这类。

我们在把>改成>=，可以发现所有索引都被使用了。

image

索引列运算/函数处理

当根据phone字段进行等值匹配查询时, 索引生效。

1
explain select * from tb_user where phone = '17799990015';
image

当根据phone字段进行函数运算操作之后，索引失效。

1
explain select * from tb_user where substring(phone,10,2) = '15';
image

字段类型不匹配

字符串类型字段使用时，不加引号，索引将失效。

1
explain select * from tb_user where phone = '17799990015';
image 
1
explain select * from tb_user where phone = 17799990015;
image

经过上面两组示例，我们会明显的发现，如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

由于下面查询语句中，都是根据profession字段查询，符合最左前缀法则，联合索引是可以生效的， 我们主要看一下，模糊查询时，%加在关键字之前，和加在关键字之后的影响。

1
explain select * from tb_user where profession like '软件%';
image 
1
explain select * from tb_user where profession like '%工程';
image 
1
explain select * from tb_user where profession like '%工%';
image

经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效。

or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

下面的age没有索引，phone有索引。

1
explain select * from tb_user where id = 10 or age = 23;
image 
1
explain select * from tb_user where phone = '17799990017' or age = 23;

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

image

然后，我们可以对age字段建立索引，重新执行：

image image

最终，我们发现，当or连接的条件，左右两侧字段都有索引时，索引才会生效。

数据分布

如果MySQL评估使用索引比全表更慢，则不使用索引。

tb_user表单数据如下：

image
  • 首先查询基本整个表的数据
1
explain select * from tb_user where phone >= '17799990005';

可以看见走到是全表查询，这是因为MySQL自己判断，认为走全表查询比走索引更快。

image
  • 然后我们只查询后面几行数据
1
explain select * from tb_user where phone >= '17799990015';

这时可以看见它走的就是索引了。

image

经过测试我们发现，相同的SQL语句，只是传入的字段值不同，最终的执行计划也完全不一样，这是为什么呢？

就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。

因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

SQL提示

把上述的 idx_user_age, idx_email 这两个之前测试使用过的索引直接删除。

然后我们创建profession的单列索引：

1
create index idx_user_pro on  tb_user(profession);

执行SQL :

1
explain select * from tb_user where profession = '软件工程';
image

发现走的联合索引，我们可以看到，possible_keys中idx_user_pro_age_sta,idx_user_pro 这两个 索引都可能用到，最终MySQL选择了idx_user_pro_age_sta索引。这是MySQL自动选择的结果。

那么，我们能不能在查询的时候，自己来指定使用哪个索引呢？ 答案是肯定的，此时就可以借助于 MySQL的SQL提示来完成。 接下来，介绍一下SQL提示。

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

  1. use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进 行评估）
  2. ignore index ： 忽略指定的索引
  3. force index ： 强制使用索引

示例

1
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
image 
1
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
image 
1
explain select * from tb_user force index(idx_user_pro_age_sta) where profession = '软件工程';
image

覆盖索引与回表

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

1
explain select id,profession,age, status from tb_user  where profession = '软件工程' and age = 31 and status = '0';

Using where; Using Index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

image 
1
explain select id,profession,age, status, name from tb_user  where profession = '软件工程' and age = 31 and status = '0';

Using index condition：查找使用了索引，但是需要回表查询数据

image

Using where; Using index 比Using index condition的性能高

因为，在tb_user表中有一个联合索引idx_user_pro_age_sta，该索引关联了三个字段 profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。

所以当我们查询返回的数据在id、profession、age、status 之中，则直接走二级索引直接返回数据了。

如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据 了，这个过程就是回表。

而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。

  1. 根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
image
  1. 执行SQL：selet id,name from tb_user where name = ‘Arm’;
image
  1. 执行SQL：selet id,name,gender from tb_user where name = ‘Arm’
image

由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

  • 一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对 以下SQL语句进行优化, 该如何进行才是最优方案:
1
select id,username,password from tb_user where username = 'itcast';
  • 答案: 针对于username, password建立联合索引, sql为:
1
create index  idx_user_name_pass on tb_user(username,password);
  • 这样可以避免上述的SQL语句出现回表查询。

尽量使用覆盖索引，减少select *。

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率；此时可以只将字符串的一部分前缀，建 立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

1
create index idx_xxxx on table_name(column(n)) ;
  • 前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值， 索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

1
select count(distinct email)/count(*) from tb_user;
image

我们使用substring函数，不断减少索引的长度，尽量让它又短，同时选择性又尽量大，最后发现截取到前五位是最好的。

1
select count(distinct substring(email,1,5))/count(*) from tb_user;
image

这时我们就可以创建索引：

1
create index idx_email_5 on tb_user(email(5));
image

前缀索引的查询流程：

image

step1：先在当前前缀索引中查询到这行数据的id

step2：回表查询到一整行的数据

step3：在这一行数据的email的值与条件对比，然后返回这个值

最后在前缀索引当中看下一个值是否对应的上，如果可以重复上述操作，直至没有相同的前缀为止

其实前缀索引就是一个时间换空间的过程。

单列&联合索引

单列索引：即一个索引只包含单个列。 联合索引：即一个索引包含了多个列。

我们先来看看tb_user表中目前的索引情况:

image

我们针对于name和phone进行查询：

1
explain select name,phone from tb_user where name ='吕布' and phone ='17799990000';

可以看见实际使用的只有一个索引，此时是会回表查询的

image

紧接着，我们再来创建一个phone和name字段的联合索引来查询一下执行计划。

1
create unique index idx_user_phone_name on tb_user(phone,name);

可以看见就没有回表查询了

image

多个单列索引的组合在混合条件下查询是会进行回表的，这种时候我们既可以使用联合索引来优化。

索引下推（ICP)

索引下推就是把 WHERE 条件尽量“推”到索引扫描阶段，省掉无用回表：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，然后再去做回表，从而减少了回表次数，提升了性能。

假设索引为 (name, age)，查询语句如下：

1
SELECT * FROM user WHERE name LIKE '王%' AND age = 30;

联合索引先按name排序，name一样再按age排序，如果是 name=”张三” and age > 18，这个就能使用联合索引的所有列. 不需要索引下推。

  • 无ICP

    • 这里走联合索引先筛选出姓名以王开头的用户
    • 由于这里是模糊匹配，不是等值匹配，故获取所有以王开头的用户后, 他们的age不一定有序的
    • 所以无法继续使用联合索引的特性来筛选age, 只能拿到以王开头的用户的id, 去回表, 然后再筛选出age=30的人.

  • 有ICP

    • 走联合索引先筛选出姓名以王开头且同时age=30的用户
    • 回表只需要根据他的id查即可

可以看到使用了索引下推后, 大大减少了回表操作。

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的列建立索引。

  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

  3. 尽量选择区分度高的列作为索引，例如身份证号建议建立索引，但是性别不建议。

  4. 字符串字段较长，可以建立前缀索引。

  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，避免回表，提高查询效率。

  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

SQL优化

上面我们了解了索引的机制，以及使用原则，其实不难看出，索引就是为了优化select查询语句的，那剩下的SQL如何优化呢？下面就开始探究其他SQL的优化方法。

插入数据

insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

1
2
3
4
insert  into  tb_test  values(1,'tom');
insert  into  tb_test  values(2,'cat');
insert  into  tb_test  values(3,'jerry');
....

批量插入

1
Insert  into  tb_test  values(1,'Tom'),(2,'Cat'),(3,'Jerry')

如果数据量很大一条sql插入的数据最好不要超过1000条；往往来说500-1000比较合理。

手动控制事务

由于mysql中的事务提交方式是自动提交的，也就意味着执行一条sql语句后，它就会自动提交事务，频繁开启事务也会影响性能。

这时我们就可以手动管理事务的开启关闭，在多条sql执行之后再提交事务。

1
2
3
4
start  transaction;
insert  into  tb_test  values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert  into  tb_test  values(4,'Tom'),(5,'Cat'),(6,'Jerry');
commit

主键顺序插入

主键顺序插入，性能要高于乱序插入（详细在后面主键优化）。 

1
2
主键乱序插入 : 8  1  9  21  88  2  4  15  89  5  7  3   
主键顺序插入 : 1  2  3  4  5  7  8  9  15  21  88  89

load

在批量插入大量数据的时候，insert就不那么好用了，这个时候，我们就要使用load指令来批量插入：

1
load data local infile '/.../..' into table tb_user fields terminated  by  ','  lines terminated by '\n' ;

这是基本语法格式：这串命令的含义是把本地/.../..路径中的文件，每一个元素按照,分隔，每一行又换行分隔，最后插入到tb_user中。

其中的形式不定，可以根据实际情况改变，例子中的格式为：

image

命令行中

不管是cmd还是linux要使用load指令，首先必须要开启local_infile：

  1. 直接用local_infile=1登录
1
mysql --local-infile=1 -u root -p
  1. 登录时附加local_infile，然后将其设置为1
1
mysql --local-infile -u root -p
1
set global local_infile = 1;
image

可以用以下指令查看是否修改成功：

1
select @@local_infile;

datagrip

右键连接，选择属性(properties)，在右边的高级（advanced），将allowLoadLoaclInfile设置为true。

image

演示

最后插入一百万条数据用时14s，如果用insert要用十分钟左右，大大提升了性能。

image

主键优化

在上一小节，我们提到，主键顺序插入的性能是要高于乱序插入的。 这一小节，就来介绍一下具体的 原因，然后再分析一下主键又该如何设计。

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

image

前面我们知道InnoDB的逻辑结构，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。

那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

主键顺序插入

  1. 从磁盘中申请页， 主键顺序插入
image
  1. 第一个页没有满，继续往第一页插入
image
  1. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
image

后续反复。

乱序插入导致页分裂

  1. 加入1#,2#页都已经写满了，存放了如图所示的数据
image

  1. 此时再插入id为50的记录，我们来看看会发生什么现象

    1. 由于索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后
    image
    1. 但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。
    image
    1. 但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。
    image

  2. 移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个 页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针

image

上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

页合并

目前表中已有数据的索引结构(叶子节点)如下：

image
  1. 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间 变得允许被其他记录声明使用。
image
  1. 当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用
image image
  1. 删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页
image

这个里面所发生的合并页的这个现象，就称之为 “页合并”。

  • **MERGE_THRESHOLD：**合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键优化的原则

  1. 满足业务需求的情况下，尽量降低主键的长度。
  2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  4. 业务操作时，避免对主键的修改

order by优化

实践

MySQL的排序，有两种方式：

  • Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort 排序。

  • Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

我们首先只保留以下索引：

image
  1. 只对age和phone升序排列
1
explain select id,age,phone from tb_user order by age;
image 
1
explain select id,age,phone from tb_user order by age，phone;
image

因为他们都没有索引，所以都是Using filesort，性能不好。

  1. 我们给他们创建联合索引
1
create index idx_user_age_phone on tb_user(age,phone); #默认两个升序

再次执行：

image image

发现两次排序都是Using index，性能好。

  1. 如果我们对age，phone均降序排列
1
explain select id,age,phone from tb_user order by age desc,phone desc;
image

也是Using index，使用了反向扫描索引。

  1. 如果我们先对phone升序排列，相同时再对age升序排列
1
explain select id,age,phone from tb_user order by phone,age;
image

违背最左前缀法则，没有使用索引。(索引是age在前，是age先排，这里先给phone排压根找不到索引，就是最左前缀法则)

  1. 如果我们先对age升序排列，再对phone降序排列
1
explain select id,age,phone from tb_user order by age asc ,phone desc;
image

使用了索引，但是没有完全使用，性能没有提升。

为什么呢？

查看索引，可以看见他们两个字段的值都是A，表示是升序索引。

image

不难想到，创建索引的时候age和phone是绑定在一起的，先通过升序排列确定了age的顺序，但是phone要降序排序就会重新搜索排序，自然就没有使用到索引来提升性能了。

那我们是否可以通过优化就让它可以降序排列呢？

是可以的，我们可以指定对应字段是升序还是降序。

1
create index idx_user_age_pho_ad on tb_user(age asc,phone desc );
image

可以看见phone就成了降序索引。

再次执行可以看见使用索引成功提升到了性能。

image

附：索引的结构

image image

order by优化原则

  1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  2. 尽量使用覆盖索引。
  3. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
  4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
image

gruop by优化

实践

  • 先删除掉所有索引。
1
explain select profession, count(*) from tb_user group by profession;
image

可以看见性能是不好的，没有使用到索引

然后，我们在针对于 profession ， age， status 创建一个联合索引。

1
create index idx_user_pro_age_sta on tb_user(profession,age,status);
image

加上联合索引后，增强了性能。

1
2
-- 只使用age组合
explain select age, count(*) from tb_user group by age;
image

使用索引失败，因为违反了最左前缀法则。

1
2
-- 不使用status
explain select profession,age, count(*) from tb_user group by profession,age;
image

也使用了索引，与最左前缀法则一致。

1
2
-- 先指定profession，再对age组合
explain select age, count(*) from tb_user where profession = '软件工程' group by age;
image

这样也使用了索引，因为age在profession后面，profession相同的情况下，age就是有序的。

总结

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

  1. 在分组操作时，可以通过索引来提高效率。
  2. 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

实践

image

通过测试我们会看到，确实越往后，分页查询效率越低，这就是分页查询的问题所在

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

  1. 我们先用order by做为踏板使用主键索引查询id
1
select id from tb_sku order by id limit 9000000,10;
image

我们知道limit是在order by后执行的，先用order by对索引的检索，快速找到9000000，然后再去使用limit。

  1. 嵌合select *
1
select * from tb_sku where id in (select id from tb_sku order by id limit 9000000,10);
image

不支持，那只有使用连表查询了。

1
select a.* from tb_sku a, (select id from tb_sku order by id limit 9000000,10) b where a.id = b.id;
image

提升了近50%的效率，还是很可观了。

总结

  1. 通过覆盖索引查询主键id
  2. 再通过连表查询的方式回表查询行数据

count优化

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

  • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个 数，效率很高； 但是如果是带条件的count，MyISAM也慢。

  • InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出 来，然后累积计数。

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数 据库进行,但是如果是带条件的count又比较麻烦了)。

count用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法：count（*）、count（主键）、count（字段）、count（数字）

用法 含义
count(主 键) InnoDB 引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。 服务层拿到主键后，直接按行进行累加(主键不可能为null)。
count(字 段) 1.没有not null约束 : InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。2.有not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返 回给服务层，直接按行进行累加。
count(数 字) InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。
count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽 量使用 count()。

总结

尽量使用count(*)。

update优化

InnoDB是行级锁，是对索引加的锁，不是针对记录加的锁： 如果where条件是不带索引的字段,，那么就会是表锁.；如果where条件是带索引的字段, 那么是行锁. 并且该索引不能失效, 否则会从行锁升级为表锁。表锁的并发性能低。

实践

先看一下我们的course表结构：

image

然后针对于这表创建两个会话：

image
  1. 两会话均是where有索引的更新
1
2
3
4
5
6
-- 会话1
begin;

update course set name = 'JavaEE' where id =1;

commit;
image 
1
2
3
4
5
6
-- 会话2
begin;

update course set name = 'MQ' where id = 4;

commit
image

他们是可以同时更新数据的，互不影响，并发性能好。

  1. 针对于没有索引的name字段更新
1
2
3
4
5
6
-- 会话1
begin;

update course set name = 'SpringBoot' where name = 'PHP';

commit;
image 
1
2
3
4
5
6
-- 会话2
begin;

update course set name = 'Kafka' where id = 4;

commit;
image image

可以看见会话1执行过后，由于没有索引，所以开启了表锁，会话2直接无法更新，被卡住了进程。

  1. 创建name索引重新执行
1
create index idx_course_name on course(name);
image image

是没有问题的。

总结

执行更新操作时，where条件尽量用有索引的字段。

where优化

同前面索引失效的情况，总结如下：

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使 用索引而进行全表扫描。

  1. 体系结构/SQL执行过程
    1. 字段执行顺序
  2. 存储引擎
    1. InnoDB
    2. MyISAM
    3. Memory
    4. 三者区别及特点
    5. 选择
  3. 索引机制详解
    1. 概述
    2. 基本语法
      1. 创建索引
      2. 查看索引
      3. 删除索引
    3. 索引分类
    4. 为什么InnoDb使用B+树索引结构
    5. 聚集索引 vs 二级索引
    6. SQL性能分析
      1. 慢日志查询
      2. proile的使用
      3. explain的使用
        1. id
        2. select_type
        3. type
        4. possible_key
        5. key
        6. key_len
        7. rows
        8. filtered
        9. Explain 执行计划中比较重要字段的含义:
    7. 索引实战
      1. 验证索引效率
      2. 最左前缀原则
    8. 索引失效场景汇总
      1. 最左前缀法则
      2. 范围查询
      3. 索引列运算/函数处理
      4. 字段类型不匹配
      5. 模糊查询
      6. or连接条件
      7. 数据分布
    9. SQL提示
    10. 覆盖索引与回表
    11. 前缀索引
    12. 单列&联合索引
    13. 索引下推（ICP)
    14. 索引设计原则
  4. SQL优化
    1. 插入数据
      1. insert
        1. 批量插入
        2. 手动控制事务
        3. 主键顺序插入
      2. load
        1. 命令行中
        2. datagrip
        3. 演示
    2. 主键优化
      1. 主键顺序插入
      2. 乱序插入导致页分裂
      3. 页合并
      4. 主键优化的原则
    3. order by优化
      1. 实践
      2. 附：索引的结构
      3. order by优化原则
    4. gruop by优化
      1. 实践
      2. 总结
    5. limit优化
      1. 实践
      2. 总结
    6. count优化
      1. count用法
      2. 总结
    7. update优化
      1. 实践
      2. 总结
    8. where优化
© 2025    T00
Powered by Hexo & Theme Keep
Total words 55.8k Unique Visitor Page View
  1. 体系结构/SQL执行过程
    1. 字段执行顺序
  2. 存储引擎
    1. InnoDB
    2. MyISAM
    3. Memory
    4. 三者区别及特点
    5. 选择
  3. 索引机制详解
    1. 概述
    2. 基本语法
      1. 创建索引
      2. 查看索引
      3. 删除索引
    3. 索引分类
    4. 为什么InnoDb使用B+树索引结构
    5. 聚集索引 vs 二级索引
    6. SQL性能分析
      1. 慢日志查询
      2. proile的使用
      3. explain的使用
        1. id
        2. select_type
        3. type
        4. possible_key
        5. key
        6. key_len
        7. rows
        8. filtered
        9. Explain 执行计划中比较重要字段的含义:
    7. 索引实战
      1. 验证索引效率
      2. 最左前缀原则
    8. 索引失效场景汇总
      1. 最左前缀法则
      2. 范围查询
      3. 索引列运算/函数处理
      4. 字段类型不匹配
      5. 模糊查询
      6. or连接条件
      7. 数据分布
    9. SQL提示
    10. 覆盖索引与回表
    11. 前缀索引
    12. 单列&联合索引
    13. 索引下推（ICP)
    14. 索引设计原则
  4. SQL优化
    1. 插入数据
      1. insert
        1. 批量插入
        2. 手动控制事务
        3. 主键顺序插入
      2. load
        1. 命令行中
        2. datagrip
        3. 演示
    2. 主键优化
      1. 主键顺序插入
      2. 乱序插入导致页分裂
      3. 页合并
      4. 主键优化的原则
    3. order by优化
      1. 实践
      2. 附：索引的结构
      3. order by优化原则
    4. gruop by优化
      1. 实践
      2. 总结
    5. limit优化
      1. 实践
      2. 总结
    6. count优化
      1. count用法
      2. 总结
    7. update优化
      1. 实践
      2. 总结
    8. where优化