Mysql

Mysql补全计划(三)

前言

今天开始进阶篇的内容。

存储引擎

image-20250615193016039

上图是mysql数据库的全部架构，按照分层的思路，可以分为

  • 连接层
  • 服务层
  • 引擎层
  • 存储层

存储引擎介绍

在创建表时，可以指定使用的存储引擎，在默认情况下，mysql使用的是InnoDB存储引擎。

查看当前支持的存储引擎

可以使用show engines来查看当前支持的引擎

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.01 sec)

建表时指定引擎

之前的建表语句不变，发生变化的是在最后指定存储引擎的部分

1
2
3
4

create table xxx (
	...
    ...
) engine = innodb;

主流存储引擎介绍

这里主要包括InnoDB、MyISAM和Memory这三种引擎。

InnoDB存储引擎

在之前使用show engines命令中，有一些对于InnoDB存储引擎的介绍。

1

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

其中一项描述很关键

Supports transactions, row-level locking, and foreign keys

支持事务、行级锁和外键。

innodb引擎的每张表都会对应一个表空间文件。存储该表的表结构、数据和索引。但是这种文件并不是一定会生成的。

我们可以查询一下当前是否开启了。

1
2
3
4
5
6
7

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set, 1 warning (0.01 sec)

可以看到确实是开启了。

逻辑存储结构

image-20250615200513103

包括表空间、段、区、页、行。

  • 表空间：表空间是由多个段组成的，是存储引擎逻辑的最高层
  • 段：常见段有数据段、索引段、回滚段等，一个段包含多个区。
  • 区：区是表空间的单元结构，每个区的大小为1M。
  • 页：页是组成区的最小单元，页是InnoDB存储引擎磁盘管理的最小单元，每个页的默认大小是16kb
  • 行：数据存储是按照行来进行存储的。

MyISAM

MyISAM的特点是

  • 不支持事务
  • 不支持外键
  • 不支持行级锁
  • 支持表锁

对于启用了MyISAM存储引擎的表来说，其文件一般是以下面这几种格式结尾的

  • xxx.sdi，存储表的结构信息
  • xxx.myd， 存储数据
  • xxx.myi，存储索引

Memory

基于内存的，使用哈希索引作为默认索引的数据。

区别汇总

image-20250616094933296

如何选择存储引擎

一般的，直接默认使用InnoDB即可。而MyISAM一般可以用来读和插入操作比较多的常见。而如果不希望将表数据持久化，这个时候就可以考虑使用Memory.

索引

索引是一种特殊的数据结构，本质上是空间换时间的一种做法，对于数据库来说，索引一般是在引擎层实现的，不同的存储引擎也有着不同的支持策略。

image-20250616100259061

B-Tree

B树是一种树的结构，其目的就是去避免二叉搜索树的退化和红黑树复杂实现而采用的一种策略。

在B树中，叶子结点和非叶子结点都会存放数据。

B+ Tree

B+树是B树的改良版，B+树叶子结点才是存储的真正的数据，而非叶子结点只是起到索引的作用。

而且，为了方便叶子结点之间的查找，叶子之间又组成了一个链表结构。

这样设计的一个很大好处是，每一个非叶子结点只负责对数据进行索引，不负责存储，这样可以一级一级将整个树设计的非常大。

Mysql B+树索引

Mysql在B+树的基础上还将叶子结点的链表进行了优化设计。即将叶子结点之间使用双向链表进行相连，同时首尾链表也进行相连，这样在查找时更加高效。

Hash索引

Mysql还有一种索引是Hash索引，这种索引基本上是特指Memory引擎，不过InnoDB引擎也有自适应hash。

关于hash算法，相信大家已经特别熟悉了，就不再都说了。

索引分类

索引主要分为下面这几类

  • 主键索引,primary key
  • 唯一索引，unique修饰
  • 常规索引，可以通过create关键字来创建
  • 全文索引，fulltext关键字，不常用，不进行讨论。

InnoDB的索引

聚集索引

聚集索引的意思是，索引和存储数据放在了一块，索引的叶子结点保存了行数据。

这种索引是必须有的，而且只有一个。

聚集索引选取规则：

  • 一般的，当指定主键后，主键索引就是聚集索引。
  • 如果不存在主键，那么unique索引就是聚集索引
  • 如果上述都没有，那么InnoDB会自动生成一个rowid作为隐藏的聚集索引。

二级索引

索引与数据分开存储，索引的叶子结点存储的值是对应数据行的主键。二级索引可以存在多个。

按索引搜索的顺序

首先要看当前要搜索的字段是什么？假设这里有一个user表

1
2
3

id 主键
name 二级索引
age

可以看到，如果我们是按照id进行搜索的，那么搜索语句看起来是这样的

1

select * from user where id = ?;

因为主键是聚集索引，也就是说，这个索引的叶子结点就是对应的行数据，那么在进行搜索的时候，直接返回数据即可。

如果我们要根据姓名来进行查询，那么sql语句可能如下

1

select * from user where name = 'xxx';

由于姓名是二级索引，二级索引的索引与数据是分开存放的，所以在进行搜索时，最后查找到叶子结点的值是对应行数据的id。

由于拿到的是主键值，所以，还需要再次去表中查找，这个过程就叫做回表。

索引总结

InnoDB根据存储结构的不同，将索引分为聚集索引和二级索引。

聚集索引每张表只有一个，当指定primary key或unique时主键时会自动创建。注意，此索引的叶子结点就是对应的行数据。所以查询这个索引可以直接找到对应的数据，不用回表再次查询。

而二级索引获取的是一个主键，需要根据拿到的主键值再次进行回表操作，效率比聚集索引差。

创建索引

下面来看看怎么创建索引

给user表的age字段创建一个索引

1

create index idx_user_age on user(age);

除此之外，还可以创建联合索引

1

 CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

查看表的索引 

1

show index from user;

删除索引 

1

drop index index_name on table_name;

SQL性能分析

分析sql的执行频率

一般的，对于sql来说，无非就是一些读写操作。我们可以看看当前数据库哪些sql执行频率比较高

1

show [session | global] status like 'Com______'; -- 下划线的个数是6个

如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql> show session status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 2     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+

这个有什么用呢？

实际上，索引并不是一劳永逸的，他也是需要代价的！任何的效率高的查找本质上都是时间换空间。

而且，请考虑一种场景，对于频繁的插入、删除、更新数据，也就意味着频繁的调整B+树，此时，索引甚至可能成为累赘。

通过查询当前sql语句的执行频率，如果是读特别多的数据，那么建立索引就非常的合适。如果是写数据特别多，那么就要只给合适的字段添加索引！

慢查询日志

慢日志是所有sql语句耗时超过指定时间(long_query_time)的一个记录日志。

查看是否开启慢查询日志 

1
2
3
4
5
6
7

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到，并没有开启慢日志。

查看慢日志存放的位置 

1
2
3
4
5
6
7

mysql> show variables like 'slow_query%';
+---------------------+---------------------------------------------+
| Variable_name       | Value                                       |
+---------------------+---------------------------------------------+
| slow_query_log      | OFF                                         |
| slow_query_log_file | D:\mysql_file\data\localhost.log |
+---------------------+---------------------------------------------+

设置慢日志查询

  • 通过修改全局参数的方式

    可以修改全局参数来开启慢日志查询

    1

    set global slow_query_log='ON';

    设置操作超时时间

    1

    set global long_query_time=1;

  • 通过修改配置文件

    一般是etc/my.cnf

    需要添加一下内容

    1
2
3
4
5

    # 慢日志开关
slow_query_log=1

# 设置慢日志超时时间
long_query_time=2;

    然后重启服务器

    1

    systemctl restart mysqld

可以执行一个耗时操作看一下

1

select sleep(3);

看一眼慢日志

1
2
3
4
5
6
7

Time                 Id Command    Argument
# Time: 2025-06-16T09:45:16.135675Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 3.000487  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
use test1;
SET timestamp=1750067113;
select sleep(3);

可以看到，在test1数据库下的select操作耗时越为3s，是一个慢日志。

profile详情

show profiles可以帮助我们了解事件都耗费到哪里去了，通过have_profiling参数可以查看当前Mysql是否支持profile优化

查看是否支持profile优化 

1
2
3
4
5
6

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

查看是否开启

1
2
3
4
5
6
7

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

开启profile 

1

set session profiling = 1;

查看sql的耗时

在开启profile后，可以使用show profiles来查看当前执行过的所有sql的情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查看每一条`sql`耗时的基本情况
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00029800 | select @@profiling              |
|        2 | 0.00042275 | select * from user              |
|        3 | 0.00045525 | select * from user where id = 1 |
+----------+------------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)

查看具体的耗时时间 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 查看指定`id`的各个阶段的耗时情况
mysql> show profile for query 3;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000131 |
| Executing hook on transaction  | 0.000008 |
| starting                       | 0.000009 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000053 |
| init                           | 0.000005 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000074 |
| preparing                      | 0.000016 |
| executing                      | 0.000012 |
| end                            | 0.000003 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000011 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000080 |
| cleaning up                    | 0.000014 |
+--------------------------------+----------+

查看指定id的sql的cpu的使用情况 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000139 | 0.000000 |   0.000000 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |
| Opening tables       | 0.000019 | 0.000000 |   0.000000 |
| init                 | 0.000006 | 0.000000 |   0.000000 |
| optimizing           | 0.000014 | 0.000000 |   0.000000 |
| executing            | 0.000015 | 0.000000 |   0.000000 |
| end                  | 0.000003 | 0.000000 |   0.000000 |
| query end            | 0.000009 | 0.000000 |   0.000000 |
| closing tables       | 0.000003 | 0.000000 |   0.000000 |
| freeing items        | 0.000072 | 0.000000 |   0.000000 |
| cleaning up          | 0.000012 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
11 rows in set, 1 warning (0.00 sec)

explain

explain也是分析sql执行的一个好工具，我们只需要在对应的查询语句前加上explain或者desc即可。

1
2
3
4
5
6
7

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

image-20250616204520752

索引使用

最左前缀原则

在联合索引时，例如user表里面有

  • name
  • age
  • status

这三个字段，我们可以给这三个字段创建联合索引

1

create index idx_user_name_age_status on user (name, age, status);

索引生效的情况

下面这三种情况都是索引生效的

1

select * from user where name = 'xxx' and age = xxx and status = xxx;

这种情况也是生效的

1

select * from user where name = 'xxx' and age = xxx;

这种情况也是生效的

1

select * from user where name = 'xxx' and status = xxx;

但是这种情况是索引部分生效，也就是说，在查找数据时，跳过了age索引

上面这几种情况都是索引生效的，即，我们将a,b,c三个字段作为联合索引

1
2
3
4

a, b, c
a, c
a, b
a

这都是索引生效的情况。

索引失效情况

如果查询条件缺乏最左侧那个字段，就会造成索引失效，实际上，下面这段也是索引生效的

1

b, c, a

只要存在最左侧那一列，就满足最左前缀原则，与字段先后顺序无关。

范围查询导致索引失效

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

1

select * from user where name = 'xxx' and age > 20 and status = 1;

age > 20这个范围查询会使status失效。

而使用>=或<=这类的范围查询不会使得范围查询右侧的字段索引失效，所以应该尽量使用>=或者<=来进行范围查询，避免索引失效。

索引失效的情况

索引列运算

如果对索引的字段进行运算操作，那么就会导致索引失败，例如，这里有一个substring函数，假设我们的name是一个单索引。

  • 精确匹配不会导致索引失效

    1

    select * from user where name = 'zxp1';

  • 对name字段进行再操作就会导致失效

    1

    select * from user where name = substring(name, 10, 2) = 'zx';

字符串不加引号

对于一个字符串类型的字段，在查询的时候可以不加引号，看下面的一个例子。

1

select * from user where phone_number = 1234567890;

和

1

select * from user where phone_number = '1234567890';

实际上，mysql会做隐式转换，但是，转换后索引并不会起作用，所以查询字符串类型时加引号

模糊查询

在使用like进行模糊查询时，如果左侧没有通配符，那么索引还是可以生效的。

1

select * from user where name = 'zxp%';

上面这个语句是可以走索引进行查询的。

下面这两个都是索引失效的

1

select * from user where name = '%xp'

和

1

select * from user where name = '%xp%';

or连接条件

如果or左右两侧需要查询的字段有一个没有索引的话，就会造成索引失效。

1

select * from user where name = 'zxp1' or age = 18;

如果name字段有索引而age字段没有的话，那么name和age都不会去走索引，这就要求两边字段都要有索引。

数据分布影响

索引在大量数据时才会体现出其优势，如果数据量很小，有些时候直接全表扫描也会很快。所以，在mysql执行的时候，其内部就会进行评价。

如果它认为走索引不如全表扫描，那么索引就会失效。

SQL提示

SQL语句中可以加入一些人为提示来进行操作优化。

  • use index

    建议mysql使用哪个索引来完成查询，仅仅是建议，不一定使用，内部还会进行评估。

    1

    select * from user use index(idx_user_name) where name = 'zxp';

  • ignore index

    查询时忽略某个索引

    1

    select * from user ignore index (idx_user_name) where name = 'zxp';

  • force index

    查询是强制使用索引

    1

    select * from user force index (idx_user_name) where name = 'zxp';

覆盖索引

覆盖索引指的是需要查询的字段全部都是索引值，不需要回表。

对于其它字段来说，因为聚集索引只能有一个(多余会造成数据冗余)，所以其它都是二级索引。

二级索引中，叶子结点全部都存储的是索引字段值+主键id。

例如，对于，对于name字段来说，其叶子结点就是(name，id)。

前缀索引

当需要创建的字段值是字符串或者text类型的时候，全部把字段值作为索引来使用会导致占用大量的空间，此时就可以使用前缀索引来进行空间优化。

前缀索引的意思是，使用一部分前缀来作为索引值

例如，我们要使用email的前7个字符作为索引

1

create index idx_email_7 on user(email(7));

单列索引和联合索引

单列索引即索引只包含了单个列，联合索引是指索引包含了多个列。

需要注意的是，如果查询的字段都是单列索引，那么只会选择其中的一个进行查询。

相反，如果两个字段是联合索引的话，就可以全部查询到，从而避免二次回表查询。

索引设计原则

  • 数据量大，读多写少的操作建立索引
  • 经常作为查询条件的字段应该建立索引
  • 字符串类型的字段，可以考虑建立前缀索引
  • 尽量使用联合索引，可以避免回表查询
  • 索引不是越多越好！
  • 设计表时，尽量给每个字段添加一定的约束，像unique,not null等

总结

这部分是mysql进阶的第一部分！

Licensed under CC BY-NC-SA 4.0
© 2024 - 2025 Xiao Peng
花有重开日，人无再少年
使用 Hugo 构建
主题 Stack 由 Jimmy 设计