MySQL多条件搜索查询优化及索引、性能提升方案咨询

背景说明 #

使用MySQL 8.0.35，数据库包含users和clients两张表，支持通过username、firstname、lastname、email、phone、document或id搜索用户。clients表用于追踪商家客户状态：

• 无记录：从未是客户
• status=1：待处理客户
• status=2：当前客户
• status=0：已流失客户

需根据输入特征自动判断搜索类型（含@识别邮箱、纯数字识别电话/证件等）。

表结构 #

`Db`.`users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NULL,
  `username` VARCHAR(50) NULL,
  `firstname` VARCHAR(30) NULL,
  `lastname` VARCHAR(60) NULL,
  `document` CHAR(11) NULL,
  `phone` VARCHAR(15) NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE,
  UNIQUE INDEX `document_UNIQUE` (`document` ASC) VISIBLE)
ENGINE = InnoDB;

`Db`.`clients` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `status` TINYINT(1) UNSIGNED NOT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL,
  `userId` INT UNSIGNED NOT NULL,
  `businessId` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `userId`, `businessId`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  INDEX `fk_clients_users1_idx` (`userId` ASC) VISIBLE,
  INDEX `fk_clients_business1_idx` (`businessId` ASC) VISIBLE,
  CONSTRAINT `fk_clients_users1`
    FOREIGN KEY (`userId`)
    REFERENCES `Db`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_clients_business1`
    FOREIGN KEY (`businessId`)
    REFERENCES `Db`.`business` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

现有查询场景 #

无输入时的查询 #

SELECT u.id, u.firstname, u.lastname, u.phone 
FROM clients c 
INNER JOIN users u ON c.userId = u.id 
WHERE c.businessId = 1 AND c.status = 2 
ORDER BY c.id DESC

数字输入时的查询 #

SELECT u.id, u.firstname, u.lastname, u.phone 
FROM clients c 
INNER JOIN users u ON c.userId = u.id 
WHERE c.businessId = 1 AND c.status = 2 AND (u.id = $search OR u.document = $search OR u.phone = $search) 
ORDER BY c.id DESC

邮箱输入时的查询 #

SELECT u.id, u.firstname, u.lastname, u.phone 
FROM clients c 
INNER JOIN users u ON c.userId = u.id 
WHERE c.businessId = 1 AND c.status = 2 AND u.email = '$search' 
ORDER BY c.id DESC

字母数字输入时的查询 #

SELECT u.id, u.firstname, u.lastname, u.phone 
FROM clients c 
INNER JOIN users u ON c.userId = u.id 
WHERE c.businessId = 1 AND c.status = 2 AND (u.username LIKE '$search%' OR CONCAT(u.firstname, ' ', u.lastname) LIKE '$search%' OR u.lastname LIKE '$search%')
ORDER BY c.id DESC

问题解答 #

1. 索引优化策略，是否需要为status单独建索引？ #

核心索引建议：

• clients表创建复合索引：CREATE INDEX idx_clients_bus_status_user_id ON clients(businessId, status, userId, id);
  所有查询都以businessId和status为过滤条件，复合索引可以直接覆盖过滤逻辑，同时包含userId用于关联users表，id用于排序，完全覆盖无输入时的查询（无需回表）。
• users表新增phone索引：CREATE INDEX idx_users_phone ON users(phone);
  数字输入场景会用到phone匹配，现有表未给该字段建索引，新增后可加速该条件的查询。
• users表新增lastname索引：CREATE INDEX idx_users_lastname ON users(lastname);
  字母数字输入场景的lastname前缀匹配可以用到该索引。

关于status单独索引：

不需要单独为status建索引。因为多数查询都是同时结合businessId和status过滤，单独的status索引区分度低（只有3种状态），远不如包含businessId的复合索引高效。

2. LIKE前缀匹配是否需切换为全文搜索？是否需新增fullName列？ #

前缀匹配 vs 全文搜索：

• 如果仅需要严格的前缀匹配（如输入Joh匹配John、Johanna），当前的LIKE '$search%'已经能利用索引（前缀匹配可命中字段索引），性能足够的话无需切换全文搜索。
• 如果需要支持中间匹配、分词搜索（如输入Doe匹配John Doe、Jane Doe），则推荐使用MySQL全文搜索，MySQL 8.0支持中文分词（需配置合适的分词器），匹配逻辑更贴合自然语言搜索需求。

关于fullName列：

当前CONCAT(u.firstname, ' ', u.lastname) LIKE '$search%'无法利用索引，因为函数包裹了字段。建议：

• 新增fullname字段（如VARCHAR(90)），在业务代码或触发器中同步维护该字段值（拼接firstname + ' ' + lastname）。
• 为fullname创建索引：CREATE INDEX idx_users_fullname ON users(fullname);
  之后用fullname LIKE '$search%'替代CONCAT写法，可直接命中索引，大幅提升查询效率。

3. OR运算符是否需改用UNION拆分查询？ #

是的，OR运算符在多索引字段匹配时会限制优化器的索引使用效率，改用UNION拆分查询能让每个子查询单独命中对应的索引，提升性能。

数字输入场景的UNION改写示例：

SELECT u.id, u.firstname, u.lastname, u.phone 
FROM clients c 
INNER JOIN users u ON c.userId = u.id 
WHERE c.businessId = 1 AND c.status = 2 AND u.id = $search
UNION
SELECT u.id, u.firstname, u.lastname, u.phone 
FROM clients c 
INNER JOIN users u ON c.userId = u.id 
WHERE c.businessId = 1 AND c.status = 2 AND u.document = $search
UNION
SELECT u.id, u.firstname, u.lastname, u.phone 
FROM clients c 
INNER JOIN users u ON c.userId = u.id 
WHERE c.businessId = 1 AND c.status = 2 AND u.phone = $search
ORDER BY id DESC

• 使用UNION而非UNION ALL，避免重复结果（如某用户的phone和document值相同的情况）。
• 字母数字输入场景的OR条件（username/lastname/fullname），若数据量较大，也可按此方式拆分，进一步提升查询效率。

内容的提问来源于stack exchange，提问作者Igor