SQL慢查询优化

问题复现

select a.*
from scenic_spot_audio a
         LEFT JOIN scenic_spot_audio_type b on a.category_id = b.id
where a.scenic_id = 11253
  and a.type = 1
  and b.foreign_name = '中文'

建表语句：

create table scenic_spot_audio
(
    id          bigint auto_increment
        primary key,
    category_id varchar(100) null,
    type        varchar(100) null,
    scenic_id   varchar(255) null,
    audio       varchar(255) null,
    is_default  varchar(10)  null,
    audio_name  varchar(255) null,
    is_tencent  varchar(2)   null,
    language    varchar(20)  null,
    sound_sex   varchar(20)  null,
    season      varchar(20)  null,
    special     varchar(20)  null,
    duration    varchar(20)  null,
    introduce   text         null
);

create table scenic_spot_audio_type
(
    id           bigint auto_increment
        primary key,
    name         varchar(100) null,
    pic          varchar(255) null,
    foreign_name varchar(100) null
);

可以看到除了主键索引之外没有其他索引

先查询一次大概多少数据：

select count(*) as audio_count
from scenic_spot_audio;
select count(*) as audio_type_count
from scenic_spot_audio_type;

结果是：

audio_count=591090
audio_type_count=14

再来看这条SQL语句的执行时间,开启查询时间，然后执行SQL

SHOW VARIABLES LIKE 'profiling';
SET profiling = 1;
SHOW PROFILES;

0.68286025,"/* ApplicationName=IntelliJ IDEA 2023.3.6 */ SELECT a.*
FROM scenic_spot_audio a
         LEFT JOIN scenic_spot_audio_type b ON a.category_id = b.id
WHERE a.scenic_id = 11253
  AND a.type = 1
  AND b.foreign_name = '中文'"

或者navicat中也可以看查询时间，可以看到大概在0.7-0.8s左右

优化过程

使用explain查看执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b	null	ALL	PRIMARY	null	null	null	14	10	Using where
1	SIMPLE	a	null	ALL	null	null	null	null	624928	0.1	Using where; Using join buffer (Block Nested Loop)

Using where:

• MySQL 在服务器端过滤行，而不仅仅依赖于索引条件。即使有索引可用，但还需要进一步筛选符合 WHERE 子句条件的行。

Using join buffer (Block Nested Loop):

• 表示 MySQL 使用了连接缓冲区。这通常发生在没有足够的索引来高效地执行连接操作时，MySQL 会将一个表的结果存储在一个缓冲区中，然后用该缓冲区与另一个表进行连接。这通常表明连接操作效率较低，可以通过添加适当的索引来优化。

可以看到两条执行计划都是走的全表扫描：

优化方式：

首先针对scenic_spot_audio_type表，即便数据很少，我们也尽量走索引：

create index scenic_spot_audio_type_foreign_name_index
    on scenic_spot_audio_type (foreign_name);

此时的执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b	null	ref	PRIMARY,scenic_spot_audio_type_foreign_name_index	scenic_spot_audio_type_foreign_name_index	403	const	1	100	Using index
1	SIMPLE	a	null	ALL	null	null	null	null	624928	0.1	Using where; Using join buffer (Block Nested Loop)

可以看到现在第二张表走索引了type: ref （使用索引）Using index 表示这个查询是覆盖索引查询，即查询可以直接从索引中获取所需的数据，而不需要访问表的行数据。

针对第一张表，我们考虑联合索引：在 scenic_id 和 type 和category_id字段上创建复合索引，以加速过滤条件的匹配

CREATE INDEX idx_scenic_spot_audio_full ON scenic_spot_audio(scenic_id, type, category_id);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b	null	ref	PRIMARY,scenic_spot_audio_type_foreign_name_index	scenic_spot_audio_type_foreign_name_index	403	const	1	100	Using index
1	SIMPLE	a	null	ALL	idx_scenic_spot_audio_scenic_id_type	null	null	null	624928	0.1	Using where; Using join buffer (Block Nested Loop)

可以看到依然走全表。

仔细观察，发现建表语句的时候使用的全是varchar类型，而我查询的时候用的是数字类型，

在字段类型与查询条件不一致的情况下，数据库需要对数据进行类型转换，这会导致全表扫描而不是使用索引

我们此时将查询的SQL语句全用字符串而不是数字：

EXPLAIN
SELECT a.*
FROM scenic_spot_audio a
         INNER JOIN scenic_spot_audio_type b ON a.category_id = b.id
WHERE a.scenic_id = '11253'
  AND a.type = '1'
  AND b.foreign_name = '中文';

观察执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	b	null	ref	PRIMARY,scenic_spot_audio_type_foreign_name_index	scenic_spot_audio_type_foreign_name_index	403	const	1	100	Using index
1	SIMPLE	a	null	ref	idx_scenic_spot_audio_full	idx_scenic_spot_audio_full	1426	const,const	3	10	Using index condition

此时我们可以看到两张表都走索引了，执行SQL语句

可以看到时间从0.8s降低到0.034s，性能提高了20倍左右。

考虑到表中还有一个introduce字段，这个字段是对景点信息的介绍，是个大文本，因此也不太适合放同一张表，最好去做一个分表操作，防止数据量继续加大的时候影响性能。此处不在考虑这个问题。

总结

优化数据库查询的主要思路是看执行计划走不走索引，不走索引全盘扫会慢很多

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，执行计划的结果发现这条语句会走全表扫描。