项目问题，一个需求需要对两张表进行合并查询，分开单表查很快，但两张表合起来会耗时很久，百万级耗时达到分钟级，这是无法忍受的
合并结果集，用到
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
此时应该可以满足需求了，单表也可借用以上优化方法
欢迎大家提供自己的见解，谢谢！
