• 項目問題，一個需求需要對兩張表進行合并查詢，分開單表查很快，但兩張表合起來會耗時很久，百萬級耗時達到分鐘級，這是無法忍受的

• 合并結(jié)果集，用到 UNION all,這也是加上會耗時很久的罪魁禍首

• 創(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 '學號',
    `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 實現(xiàn)

// 將滿足條件的兩張表數(shù)據(jù)一起顯示出來
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就是以上寫法，小數(shù)據(jù)量還沒什么，大數(shù)據(jù)量由于union all的存在會耗時很久,請求超時，令人崩潰

優(yōu)化sql開始

• 避免select *的使用，減少數(shù)據(jù)庫的解析時間
• where 條件放到每張表后面，避免全表掃描
• 添加查詢條件索引

以上三點幾十萬的數(shù)據(jù)已經(jīng)能接受了

此時100萬的數(shù)據(jù)耗時4s

重點

• 添加索引

// eg.添加單列索引
alter table test1  add INDEX idx_card_no(`card_no`) COMMENT '學號'

// 這里使用多列索引
// 注意索引的創(chuàng)建順序需要與查詢條件順序一致
// 表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萬的數(shù)據(jù)耗時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)建索引提高查詢效率，當然也會降低查詢效率（親測），由于一張表可能會有多個業(yè)務(wù)，索引也會增加

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

  SQL執(zhí)行計劃

劃紅線的列顯示會用到其他索引，這中間有個索引匹配過程，因此會耗時

那如果我指定單獨的索引是不是會減少匹配的消耗呢？（可以）

• 分別在兩條查詢后指定索引 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萬的數(shù)據(jù)耗時不到1s

此時應(yīng)該可以滿足需求了，單表也可借用以上優(yōu)化方法

歡迎大家提供自己的見解，謝謝！