# 背景:person_info 表存放用戶信息中有1千萬數據,ID為主鍵,addr存放省份ID;addr_table表存放省份數據,有34條數據,ID為主鍵;
# 統計所有地區的人員人數
select ad.name, count(pio.id) as pid from addr_table ad inner join person_info pio on ad.id = pio.addr group by ad.name order by pid;
# 查詢時長:5s
# SQL優化:
# 添加索引
alter table person_info add index pi_addr_index(addr);
# 添加索引后,查詢時長:2s
# 添加外鍵
# 語法:ALTER TABLE child_table ADD FOREIGN KEY (column1) REFERENCES parent_table(parent_column)
# CASCADE:當父表中的記錄被刪除或更新時,子表中相應的記錄也會被級聯刪除或更新。
# SET NULL:當父表中的記錄被刪除時,子表中相應的外鍵列將被設置為NULL。
# NO ACTION 或 RESTRICT:當父表中的記錄被刪除或更新時,如果子表中存在依賴的記錄,則操作將被拒絕。
# SET DEFAULT:將外鍵列設置為默認值。
alter table person_info
add foreign key (addr) references addr_table(id);
# 添加外鍵時報錯,原因:兩邊的字段的數據類型不一致
# 添加外鍵需要注意的事項:
# 父表必須存在,父列必須為主鍵或唯一鍵
# 數據類型必須一致,子表中的數據都必須滿足外鍵約束
# 修改表字段類型
alter table person_info modify addr int;
# 添加索引后,查詢時長變為了2秒,看能否繼續優化
# 為 group by 的字段添加索引可以優化查詢速度
# 由于ID為主鍵自動添加了索引,所以可以將 語句中的 group by ad.name 修改為 group by ad.id
select ad.name, count(pio.id) as pid from addr_table ad inner join person_info pio on ad.id = pio.addr group by ad.id order by pid;
# 優化后查詢時長:1s