sql 百万级数据量查询优化

• 项目问题，一个需求需要对两张表进行合并查询，分开单表查很快，但两张表合起来会耗时很久，百万级耗时达到分钟级，这是无法忍受的

• 合并结果集，用到 UNION all,这也是加上会耗时很久的罪魁祸首

• 创建表

create table `test1` (
    `id` bigint(10) primary key NOT NULL DEFAULT '0',
    `name` varchar(20) NOT NULL DEFAULT '' comment '用户名',
    `card_no` bigint(18) NOT NULL DEFAULT '0' comment '学号',
    `age` int(2) NOT NULL DEFAULT '0' comment '年龄',
    `sex` TINYINT(1) NOT NULL DEFAULT '0' comment '性别',
    `amount` decimal(18,4) NOT NULL DEFAULT '0.0000',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
)

create table `test2` (
    `id` bigint(10) primary key NOT NULL DEFAULT '0',
    `name` varchar(20) NOT NULL DEFAULT '' comment '用户名',
    `card_no` bigint(18) NOT NULL DEFAULT '0' comment '学号',
    `year` int(2) NOT NULL DEFAULT '0' comment '年龄',
    `sex` TINYINT(1) NOT NULL DEFAULT '0' comment '性别',
    `money` decimal(18,4) NOT NULL DEFAULT '0.0000',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
)

• 由于页面存在分页，所以对union用法不可避免，（哪位大啦有高见，请指教谢谢）

• sql 实现

// 将满足条件的两张表数据一起显示出来
SELECT 
    a.name as name,
    a.age as age,
    a.amount as money
    FROM test1 a
    WHERE
        a.card_no in (xxx)
        and
        a.sex=1
        AND
        a.age>18
UNION all
SELECT 
    b.name as name,
    b.card_no as idCard,
    b.year as age,
    b.money as money
    FROM test2 b
    WHERE
        b.card_no in (xxx)
        and
        b.sex=1
        AND
        b.year>18

• 通常sql就是以上写法，小数据量还没什么，大数据量由于union all的存在会耗时很久,请求超时，令人崩溃

---

优化sql开始

• 避免select *的使用，减少数据库的解析时间
• where 条件放到每张表后面，避免全表扫描
• 添加查询条件索引

以上三点几十万的数据已经能接受了

此时100万的数据耗时4s

重点

• 添加索引

// eg.添加单列索引
alter table test1  add INDEX idx_card_no(`card_no`) COMMENT '学号'

// 这里使用多列索引
// 注意索引的创建顺序需要与查询条件顺序一致
// 表1
alter table test1  add INDEX idx_card_no_sex_age(`card_no`,`sex`,`age`) COMMENT '学号-性别-年龄'
// 表2
alter table test2  add INDEX idx_card_no_sex_year(`card_no`,`sex`,`year`) COMMENT '学号-性别-年龄'

此时100万的数据耗时2.5s

• 查看索引 show index from table_name
• 删除索引

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

• 创建索引提高查询效率，当然也会降低查询效率（亲测），由于一张表可能会有多个业务，索引也会增加

• 查看以上加索引之后的性能 EXPLAIN(自行百度用法)
  

  SQL执行计划

划红线的列显示会用到其他索引，这中间有个索引匹配过程，因此会耗时

那如果我指定单独的索引是不是会减少匹配的消耗呢？（可以）

• 分别在两条查询后指定索引 USE INDEX(index_name)

SELECT 
    a.name as name,
    a.age as age,
    a.amount as money
    FROM test1 a
    
    USE INDEX(idx_card_no_sex_age)
    
    WHERE
        a.card_no in (xxx)
        and
        a.sex=1
        AND
        a.age>18
UNION all
SELECT 
    b.name as name,
    b.card_no as idCard,
    b.year as age,
    b.money as money
    FROM test2 b
    
    USE INDEX(idx_card_no_sex_year)
    
    WHERE
        b.card_no in (xxx)
        and
        b.sex=1
        AND
        b.year>18

此时100万的数据耗时不到1s

---

此时应该可以满足需求了，单表也可借用以上优化方法

欢迎大家提供自己的见解，谢谢！