MySQL查询优化实战篇

关注“Java后端技术全栈”

回复“面试”获取最新资料

回复“加群”邀您进技术交流群

如今的各种应用中，大多使用关系型数据库用于数据存储（非关系型数据库，多用于缓存）。当数据达到一定的数量级，并发请求增多时，SQL 效率（一般是查询语句）对于系统性能就显得尤为重要。而在关系型数据库中，MySQL 由于体积小、速度快、成本低等优点，尤其是开放源码这一特点，很多企业都使用其作数据存储。

同时在很多人面试的时候也是很容被问到，

• 你有做过sql优化吗？

• 是怎么优化的？
  

• 为什么这么优化？
  

• 还有更好的方案吗？
  

• .....
  

本文将通过多个实例，介绍 MySQL 数据库查询语句的优化方式，如筛选条件写法不同对查询性能的影响、拆分子查询、关键字（如 distinct、group by 等）的使用对性能的影响、分析执行计划，适当添加索引等。最后，讨论几种分布式数据存储方式。读者可根据业务场景不同，选择合适的分布式数据存储，以提升系统性能。

本文将从以下几个角度讨论查询 SQL 的优化：

• 筛选条件写法不同（决定查询是否使用索引）与关键字的使用（导致全表扫描）对查询性能的影响

• 分析执行计划与拆分子查询

• 根据数据访问特点适当添加索引

• 讨论大数据量时，几种分布式数据存储方式的优劣与解决方法

本文所有例子，都以 CRM（客户管理系统）业务为基础，主要涉及下述三张表。其中客户表记录客户基本信息，客户通过某些方式进入客户管理系统，然后被分配给销售（客户表中 owner_id 字段），销售通过客户手机号（客户表中 phone_number 字段）给客户打电话（客户通话记录表），跟进客户。跟进记录表记录销售跟进客户的信息，如修改客户参加活动进度（即修改客户状态，跟进记录表中 prev_state 上一状态与 next_state 下一状态字段），同时可添加注释。

三张表的表结构如下所示。

客户表（170w）：

 CREATE TABLE `table_customer` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
`name` varchar(30) NOT NULL DEFAULT '' COMMENT '昵称',
`sex` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别 0:未知 1:男 2:女',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`phone_number` varchar(50) DEFAULT NULL COMMENT '手机号码',
`state_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '状态id',
`state_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '状态更新时间',
`owner_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '销售id',
`owner_updated_at` datetime DEFAULT NULL COMMENT '销售更新时间',
`is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否被删除 0:未被删 1:已删除',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_phone_number` (`phone_number`),
KEY `idx_user_id` (`user_id`),
KEY `idx_state_id` (`state_id`),
KEY `idx_owner_id_state_id` (`owner_id`,`state_id`),
KEY `idx_state_updated_at` (`state_updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户表';

客户跟进记录表（1800w）：

CREATE TABLE `table_customer_follow_record` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`customer_id` int(11) unsigned NOT NULL COMMENT '客户id',
`prev_state` int(11) NOT NULL DEFAULT '0' COMMENT '上一状态',
`next_state` int(11) NOT NULL DEFAULT '0' COMMENT '下一状态',
`content` varchar(255) NOT NULL COMMENT '记录内容',
`operator_id` int(11) unsigned NOT NULL COMMENT '操作人id',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_customer_id` (`customer_id`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户跟进记录表';

客户通话记录表（680w）：

CREATE TABLE `table_customer_call_record` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`customer_phone_number` varchar(50) NOT NULL DEFAULT '' COMMENT '客户电话',
`seat_number` int(11) NOT NULL DEFAULT '0' COMMENT '座席号',
`system_user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '系统用户id 如销售id',
`start_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '开始呼叫时间，时间戳',
`begin_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '客户接听时间，时间戳',
`end_time` int(11) NOT NULL COMMENT '通话结束时间，时间戳',
`total_duration` int(11) NOT NULL DEFAULT '0' COMMENT '总时长(秒)',
`answer_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '通话状态 1:接听 2:未接听',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_customer_phone_number` (`customer_phone_number`),
KEY `idx_seat_number` (`seat_number`),
KEY `idx_created_at` (`created_at`),
KEY `idx_system_user_id` (`system_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户外呼记录表';

索引的使用场景

索引的一个主要目的是，加快检索表中数据，提高系统性能。因此如果我们想要提高数据库性能，首先需要考虑索引的使用（前提是在常用查询列上已建立索引）。

索引列运算（尤其是日期）

例 1：查询当日所有客户的跟进记录（由于业务需求，created_at 创建时间字段已添加索引）

一种写法：

select * from `table_customer_follow_record` where DATE_FORMAT(`created_at`, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d');

执行时间 13s，再来看一下执行计划：

可以看到全表扫描了 table_customer_follow_record，扫描了 670w 行。

另一种写法（把日期处理全部移到右边，不对 created_at 字段做表达式处理）：

select * from `table_customer_follow_record` where `created_at` between DATE_FORMAT(now(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(now(), '%Y-%m-%d 23:59:59');

执行时间 7ms，看一下执行计划：

可以看到走了 idx_created_at 索引，效率有了质的提升。

like 的使用

对于 like 关键字的使用，若使用 like 的字段有索引，则下述两种写法也会对查询效率有影响。

例 2：查询手机号以“130”开头的客户

select * from `table_customer` where `phone_number` like '130%';

select * from `table_customer` where `phone_number` like '%130%';

执行计划与例一类似。like 后直接跟 % 号的匹配查询不使用索引。

in 与 or 的使用

使用 not in 与 or 不会使用索引，in 会使用索引（若筛选列已添加索引）。

例 3：查询参加过活动（客户的 state_id = 1）或者销售跟进过的客户信息

select *
from `table_customer`
where `state_id` = 1
or `id` in (
select `customer_id` from `table_customer_follow_record`
);

查看执行计划：

可以看到，虽然客户表的 state_id 字段有索引，但是由于使用了 or，导致并没有使用 idx_state_id 索引，从而全表扫描，如果拆分成两个 SQL 单独查询，就会使用 idx_state_id 和 idx_customer_id 索引。

distinct 关键字的使用

distinct 关键字会导致全表扫描，使用是否合适会影响 SQL 性能。

例 4：查询参加过活动（即跟进记录表中出现过下一状态为 1——参加活动，而并非当前状态为 1）的客户信息

select `cu`.*
from `table_customer` `cu`
left join (
  select distinct `customer_id`
  from `table_customer_follow_record`
  where `next_state` = 1
) `fo` on `cu`.`id` = `fo`.`customer_id`
where `cu`.`is_deleted` = 0

在子查询中使用 distinct 的原因是，客户可能重复多次参加活动，使用 distinct 可以保证不会出现重复客户。

上述 SQL 的执行非常慢（30s+），可以看一下执行计划：

可以看到，虽然使用了跟进记录表的 idx_customer_id 索引，但是由于使用的是子查询，表关联时并不会使用索引。我们尝试在关联后的结果集中排除重复数据。

select distinct `cu`.*
from `table_customer` `cu`
left join `table_customer_follow_record` `fo` on `cu`.`id` = `fo`.`customer_id` and `fo`.`next_state` = 1
where `cu`.`is_deleted` = 0;

上述 SQL 的执行时间是 400ms，相对于之前有很大提升。下面是执行计划：

对比之前的执行计划，少了处理跟进记录表的一千万数据，因此性能有了极大提升。

拆分子查询

由于拆分子查询有很多点需要考虑，这里会用一个例子，从多个维度考虑，多次优化以达到良好的查询效率。

例 5：查询当日拨打（是否接通都算）次数超过 5 次的客户信息

select `cu`.*
from `table_customer` `cu`
left join (
select `id`, `customer_phone_number` from `table_customer_call_record` where DATE_FORMAT(`created_at` , '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d')
) `call` on `cu`.`phone_number` = `call`.`customer_phone_number`
where `cu`.`is_deleted` = 0
group by `cu`.`id`
having sum(if(`call`.`id` is null, 0, 1)) > 5;

上述 SQL 的执行时间是 53s。下面看一下执行计划。

拆分子查询

可以看到上述执行计划中，子查询的 table_customer_call_record 全表扫描，并且数据量很大。尝试拆分子查询，直接关联。

select `cu`.*
from `table_customer` `cu`
left join `table_customer_call_record` `call` on `cu`.`phone_number` = `call`.`customer_phone_number`
  and DATE_FORMAT(`call`.`created_at` , '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d')
where `cu`.`is_deleted` = 0
group by `cu`.`id`
having sum(if(`call`.`id` is null, 0, 1)) > 5;

上述 SQL 的执行时间是 38s。相比之前子查询的方式，效率有所提升，但是还是很慢，再来看一下执行计划。

区分左连接、右连接与内连接

根据执行计划来看，目前关联是使用了索引（idx_customer_phone_number），但由于数据量问题，目前效率仍未达到预期，因此需要从其他方面考虑。

要求是查通话次数大于 5 次的，因此可以考虑将左连接（left join）改成内连接（inner join），减少关联之后的数据量。

注：如果要求是查通话次数小于 5 次的，就不能使用内连接（inner join），因为会过滤掉未通话过的客户，但未通话过的客户，也符合要求。

同时，由于通话表的创建时间有加索引，并且作为 created_at 作为通话表的筛选条件，从上面的执行计划来看，并未使用到通话表的 idx_created_at 索引，因此根据上面“不要在索引列上运算”的原理，将时间处理都移到右侧。

select `cu`.*
from `table_customer` `cu`
inner join `table_customer_call_record` `call` on `cu`.`phone_number` = `call`.`customer_phone_number`
and `call`.`created_at` between DATE_FORMAT(now(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(now(), '%Y-%m-%d 23:59:59')
where `cu`.`is_deleted` = 0
group by `cu`.`id`
having count(`call`.`id`) > 5;

上述 SQL 的实行时间是 5s，相比前面的 SQL，效率有很大提升，看一下执行计划。

可以看到，改为内连接，和修改通话表日期筛选的写法之后，虽然通话表的索引由之前的主键索引改为了 idx_created_at 索引，并且数据量由之前的 173w 降低到 5w，因而提高了查询效率。

从目前 SQL 来看，好像没有多少优化空间，因此需要从其他方面考虑。

• 业务逻辑：查询当天拨打次数超过 5 次的客户信息，意义不大。若过于频繁给客户拨打电话会被投诉，因此很少会当日拨打超过 5 次。

• 缓存数据：统计需求，可以做数据缓存。如每天半夜（数据库空闲时）统计前一日的数据。

• 清除过期数据：通话记录表和跟进记录表，数据量分别是 680w 和 1800w，其实很大一部分是很久以前的数据，基本不会访问。类似这种过期数据，可以定期迁移到历史记录表中。

注：上述的通话记录表中，定期会做历史数据迁移，但是会有类似于 Oracle 的高水位处理，需要定期手动处理。运行上述例子时，未处理通话记录表中的高水位问题，若处理的话，运行时间会有所减少。

根据数据访问特点适当添加索引

组合索引

实际使用场景中，经常需要根据销售查询不同状态客户信息（客户需由销售负责，因此客户会有 owner_id 字段标识客户所属销售），因此创建了索引 KEY idx_owner_id_state_id(owner_id,state_id)，即为组合索引。使用组合索引做 SQL 查询时，尽量按照字段顺序使用。如仅根据 state_id 筛选，不会使用 idx_owner_id_state_id 索引。由于客户表有 idx_state_id 索引，因此会使用 idx_state_id 索引，但若 state_id 无索引，则不会使用索引。

分布式数据存储的优劣

分区表

MySQL 的分区表支持水平分区，不支持垂直分区。（关于 MySQL 的水平分区，可自行了解，此处不做详述）

对于上述三张表，数据量多在通话表和跟进记录表，因为每个客户会有多条通话记录和跟进记录，因此可对通话表和跟进记录表，创建时间按月分区。

使用分区表，数据物理存储在不同文件，对于应用程序来说仍是一张表，应用程序代码无需修改。

但是使用分区表也有一些缺点，如查询时，不走分区键会导致锁全表（所有分区），并且若需要对分区表进行关联查询，数据量会非常大。

分表

由于分区表的一些缺点，相对于分区表，另一种选择是在应用程序层控制分表。

与分区表不同的是，分表分为水平分表和垂直分表。

• 水平分表：数据表行的拆分，将数据拆成多张表来存放。与分区表相类似的是，分表只解决了数据量大的问题。查询时，需要多个分表的结果进行合并。且需要进行关联查询时，情况会变得很复杂。因此使用水平分表时，需确定合适的分表规则，尽量保证每个分表存放的数据独立，避免同时访问多个分表数据。

• 垂直分表：数据表列的拆分，一般按照大字段或者访问频率高低来拆分。垂直分表适用于数据量不多，字段多的表，拆分后的表与原表数据一一对应。

垂直分表可以简化表结构，减少 I/O，但是相应的，可能会增加表关联。之前从一张表可以取到的字段，现在可能需要关联多张表才能获取。

相比分区表，无论是选择水平分表还是垂直分表，其中遇到的问题都需要在应用层解决，因为分表之后的多个表，对于应用层来讲是多个表，并非如分区表一样当作一张表来使用。并且若后续分表策略改变，应用层代码也需做相应变动。读者可根据优劣选择使用何种数据存储。

小结

本文先通过一些例子演示了常用的索引使用场景。然后用一个实例，经过多次思考优化处理，使查询效率大幅提升。最后，完备 SQL 写法之余，考虑从数据存储层面，选择不同的数据存储方式，以助提高系统性能。

由于篇幅原因，本文对于相关的一些内容，未能详尽的介绍，如：

• 导致不使用索引的所有情况。

• 修改数据时，通过主键或索引更新，否则会导致锁全表，影响系统性能。

• 区分左右连接的使用（曾经优化过一个 SQL，最后通过右连接的方式，使 SQL 性能达到实时响应要求）。

• 对于查询请求很多的系统做数据库的读写分离（即写主库，读从库），数据同步更新或异步更新（一般为异步更新），对于写后读的情况，数据同步不及时可能导致一些其他问题。

• 在线事务处理应用中，SQL 的写法会受到事务并发控制的影响（即需要考虑并发问题，其他事物修改时，本事务同时在读取数据）。

• 分布式事务存储，除了上述的分区表与分表之外，还有一些其他的存储策略，如将数据分库存到不同的数据节点，处理数据时，按照某种规则映射到数据实际存在的节点去处理。但这会出现其他问题，如节点数量改变时，映射规则也会改变，如何能在不大批量迁移数据的情况下实现数据存储节点的增减等，都是需要考虑的。

本文的主要目的，并非意图通过几个例子教会读者 SQL 优化的技巧，而旨在通过 SQL 优化，理清思路充分理解需求、考虑实时性要求，再拓展到数据存储等，从多方面考虑，最终目的都是提高系统并发性能。

推荐阅读

一个月薪 12000 的北京程序员的真实生活

你必须要知道的锁原理、锁优化、CAS、AQS

MySQL 的这个 BUG，坑了多少人？

目前10000+ 人已关注我们