項(xiàng)目問(wèn)題椎镣，一個(gè)需求需要對(duì)兩張表進(jìn)行合并查詢艺晴，分開(kāi)單表查很快冗美，但兩張表合起來(lái)會(huì)耗時(shí)很久歌憨，百萬(wàn)級(jí)耗時(shí)達(dá)到分鐘級(jí)，這是無(wú)法忍受的
合并結(jié)果集墩衙，用到
UNION all,這也是加上會(huì)耗時(shí)很久的罪魁禍?zhǔn)?/strong>創(chuàng)建表
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 '學(xué)號(hào)',
`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 '學(xué)號(hào)',
`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
)
由于頁(yè)面存在分頁(yè)务嫡，所以對(duì)
union用法不可避免，（哪位大啦有高見(jiàn)漆改，請(qǐng)指教謝謝）sql 實(shí)現(xiàn)
// 將滿足條件的兩張表數(shù)據(jù)一起顯示出來(lái)
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就是以上寫(xiě)法心铃，小數(shù)據(jù)量還沒(méi)什么，大數(shù)據(jù)量由于
union all的存在會(huì)耗時(shí)很久,請(qǐng)求超時(shí)挫剑，令人崩潰
優(yōu)化sql開(kāi)始
- 避免
select *的使用去扣，減少數(shù)據(jù)庫(kù)的解析時(shí)間 -
where條件放到每張表后面，避免全表掃描 - 添加查詢條件索引
以上三點(diǎn)幾十萬(wàn)的數(shù)據(jù)已經(jīng)能接受了
此時(shí)100萬(wàn)的數(shù)據(jù)耗時(shí)4s
重點(diǎn)
- 添加索引
// eg.添加單列索引
alter table test1 add INDEX idx_card_no(`card_no`) COMMENT '學(xué)號(hào)'
// 這里使用多列索引
// 注意索引的創(chuàng)建順序需要與查詢條件順序一致
// 表1
alter table test1 add INDEX idx_card_no_sex_age(`card_no`,`sex`,`age`) COMMENT '學(xué)號(hào)-性別-年齡'
// 表2
alter table test2 add INDEX idx_card_no_sex_year(`card_no`,`sex`,`year`) COMMENT '學(xué)號(hào)-性別-年齡'
此時(shí)100萬(wàn)的數(shù)據(jù)耗時(shí)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 ;
創(chuàng)建索引提高查詢效率樊破，當(dāng)然也會(huì)降低查詢效率（親測(cè)）愉棱，由于一張表可能會(huì)有多個(gè)業(yè)務(wù)，索引也會(huì)增加
-
查看以上加索引之后的性能
EXPLAIN(自行百度用法)
SQL執(zhí)行計(jì)劃
劃紅線的列顯示會(huì)用到其他索引哲戚，這中間有個(gè)索引匹配過(guò)程奔滑，因此會(huì)耗時(shí)
那如果我指定單獨(dú)的索引是不是會(huì)減少匹配的消耗呢？（可以）
- 分別在兩條查詢后指定索引
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
此時(shí)100萬(wàn)的數(shù)據(jù)耗時(shí)不到1s
此時(shí)應(yīng)該可以滿足需求了顺少，單表也可借用以上優(yōu)化方法
歡迎大家提供自己的見(jiàn)解朋其，謝謝！
