在我最近的项目中，经常会有给大表加字段的需求，这个过程非常耗时。

可以看到，900 万数据量的一张表，加一个字段就需要 3 个小时左右。

我们知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。假设表数据量比较大，加字段的过程将会非常耗时。

不过我最关心的是，在加字段的过程中，会不会对业务的增删改查造成影响？在询问 DBA 后，他给出的答复是不会造成影响。这不禁让我思考这背后的实现原理。

下面，我们就来一探究竟。

在我们的生产环境中有一张表：courier_consume_fail_message，是存放消息消费失败的数据的，设计之初，这张表的数据量评估在万级别以下，因此没有建立索引。

但目前发现，该表的数据量已经达到百万级别，原因产生了大量的重试消费，这导致了该表的慢查询。

因此需要清理该表数据。而实际上，使用 DELETE 命令删除数据后，我们发现查询速度并没有显著提高，甚至可能会降低。为什么？

因为 DELETE 命令只是标记该行数据为“已删除”状态，并不会立即释放该行数据在磁盘中所占用的存储空间，这样就会导致数据文件中存在大量的碎片，从而影响查询性能。所以，除了删除表记录外，还需要清理磁盘碎片。

在表碎片清理前，我们关注以下四个指标。

• 指标一：表的状态：SHOW TABLE STATUS LIKE 'courier_consume_fail_message';
• 指标二：表的实际行数：SELECT count(*) FROM courier_consume_fail_message;
• 指标三：要清理的行数：SELECT count(*) FROM courier_consume_fail_message where created_at < '2023-04-19 00:00:00';
• 指标四：表查询的执行计划：EXPLAIN SELECT * FROM courier_consume_fail_message WHERE service='courier-transfer-mq';

1
2

-- 清理磁盘碎片
OPTIMIZE TABLE courier_consume_fail_message;

以下是清理前后的指标对比。

这里记录的是学习分享内容，文章维护在 Github：studeyang/leanrning-share。

优化查询语句的性能是 MySQL 数据库管理中的一个重要方面。在优化查询性能时，选择正确的索引对于减少查询的响应时间和提高系统性能至关重要。但是，如何确定 MySQL 的索引选择策略？MySQL 的优化器是如何选择索引的？

在这篇《索引失效了？看看这几个常见的情况！》文章中，我们介绍了索引区分度不高可能会导致索引失效，而这里的“不高”并没有具体量化，实际上 MySQL 会对执行计划进行成本估算，选择成本最低的方案来执行。具体我们还是通过一个案例来说明。

案例

还是以人物表为例，我们来看一下优化器是怎么选择索引的。

建表语句如下：

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `score` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name_score` (`name`,`score`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

然后插入 10 万条数据：

1
2
3
4
5
6
7
8
9
10

create PROCEDURE `insert_person`()
begin
    declare c_id integer default 3;
    while c_id <= 100000 do
	    insert into person values(c_id, concat('name',c_id), c_id + 100, c_id + 10, date_sub(NOW(), interval c_id second));
	    -- 需要注意，因为使用的是now()，所以对于后续的例子，使用文中的SQL你需要自己调整条件，否则可能看不到文中的效果
	    set c_id = c_id + 1;
    end while;
end;
CALL insert_person();

索引是 MySQL 数据库中优化查询性能的重要工具，通过对查询条件和表数据的索引，MySQL可以快速定位数据，提高查询效率。但是，在实际的数据库开发和维护中，我们经常会遇到一些情况，导致索引失效，从而使得查询变得非常缓慢，甚至无法使用索引来优化查询，这会严重影响系统的性能。那么，是什么原因导致了索引失效呢？

常见的情况有：

• 索引中断
• 数据类型不匹配
• 查询条件使用函数操作
• 前模糊查询
• OR 查询
• 建立索引时使用函数
• 索引区分度不高

下面我通过实际的例子来具体说说。假设现在我们有一张人物表，建表语句如下：

1
2
3
4
5
6
7
8

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `score` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

日常工作中，有些同学一遇到查询性能问题，就盲目要求 DBA 给表字段创建索引。今天，我们就来具体看看这背后的细节。

谈到索引失效，大家可能都能列举出几个场景，比如：后模糊查询、条件中带函数、索引中断等等。今天我想和你分享另一个场景：索引成本分析。

我先用一个具体的例子来描述一下这个场景。

提到事务，你肯定不陌生。在 MySQL中，InnoDB 是支持事务的，事务有4大特性，即 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

今天我们就来说说隔离性。