Mysql - 查詢之關聯查詢

標籤：效能測試   mem   order   報錯   關注   table   aes   時間   create   

 

    ? 查詢這塊是重中之重, 關係到系統反應時間. 項目做到後期, 都是要做效能測試和效能最佳化的, 最佳化的時候, 資料庫這塊是一個大頭.

sql格式: select 列名/* from 表名 where 條件 group by 列 having 條件 order by 列 asc/desc;

這裡牽涉到一個查詢執行順序的問題. 

單表查詢執行順序: 

select sex, count(Sex) as count from tch_teacher where id > 15 group by Sex having count > 5 order by Sex asc limit 1;

1-> from 表 : 首先拿到表tch_teacher

2-> where 條件 : 根據where後面的條件式篩選一遍資料集合A

3-> group by 分組 : 對篩選出的資料A, 根據group by後面的列進行分組, 得到資料集B

4-> having 篩選 : 對資料集B進行進一步篩選, 得到資料集C

5-> select 資料 : 這裡有四步

                         第一步 : 根據select後面的列名, 去資料集C中取資料. 得到資料集D

                         第二步 : 對資料集D中的資料進行去重操作(這一步是建立在 sql中有distinct 情況下), 得到資料集E

                         第三步 : 對資料集E進行排序操作, 得到資料集F

                         第四步 : 對資料集F進行截取資料操作, 得到最終的資料集(執行 limit 10 操作)

 在多表的時候, 只是第1步有了變化, 多了一個連表操作, 別的沒啥變化.

 

一、連表查詢

1. 交叉串連 -- 笛卡爾乘積  cross join

select * from tch_teacher cross join tch_contact

這種串連方式, 沒見人用過.  如果tch_teacher,tch_contact表各有10條資料, 那麼串連的結果, 就是 10 x 10 = 100 條資料. 

在mysql 中, cross join 後面是可以跟 on 和 where 的, 加上之後, 其實跟 inner join 是一樣的

 

2. 內串連 -- inner join 

內串連在不加on的情況下, 也是去求笛卡爾乘積. 不加on的用法並不推薦使用, 容易造成記憶體溢出的情況. 加on的時候, 在連表的時候, 就會對資料進行篩選, 以此來縮減有效資料範圍

select * from tch_teacher inner join tch_contact 

 

從上面的sql和圖片來看, inner join 的時候, 可以不加on, 也能得到一個結果, 而且這個結果和交叉串連的結果是一樣的. 

 這裡還有另外兩種寫法:

select * from tch_teacher,tch_contactselect * from tch_teacher join tch_contact

得到的結果是一樣的. 

 

3. 外串連 -- left/right join on 

這裡我特意加了一個on在上面, 因為不加on是會報錯的.

left join 稱之為左串連, 串連以左側表資料為準, 當右表沒有資料與之匹配的時候, 則會用null填補

right join 稱之為右串連, 與 left join 相反, 這個是以右表為準

先看下效果吧

select * from tch_teacher left join tch_contact on tch_teacher.Id = tch_contact.TId;

select * from tch_teacher right join tch_contact on tch_teacher.Id = tch_contact.TId;

其實這裡還有一個full join , 不過mysql裡面的full join, 著實有些讓人無語, 反正我沒這麼用過, 略過不表了.

這裡我做了一個小測試, 這裡的資料, tch_contact的tid值, 我取的是0~100000之間的隨機數

select * from tch_teacher inner join tch_contact on tch_teacher.Id = tch_contact.TId ;select * from tch_teacher LEFT join tch_contact on tch_teacher.Id = tch_contact.TId ;

tch_teacher	tch_contact	inner join(s)	結果	left join(s)	結果
十萬	十萬	0.499	99999	0.526	137017
十萬+5000	十萬	0.345	99999	0.565	142017
十萬-5000	十萬	0.472	94949	0.534	130124

這裡面的值, 是我反覆運行之後, 在一些離散值裡面, 取得比較靠近中心點的值.

 

4. 自己連自己

mysql裡面, 有一個比較好用的功能, 就是自己連自己. 我再tch_teacher表裡面加入一列, CreateBy,  存放的是這個表的Id值

select a.*, b.Name as CreateByName from tch_teacher aleft join tch_teacher b on a.createby = b.id

能得到以下結果:

 

 二、union查詢

除了把幾個表通過內部關係拼成一個表結果, 還可以, 把多個表的查詢表結果拼成一個表結果. 所使用的方法就是union.

這裡要注意的是, 列的順序. 如果害怕列的順序不一致不好排查, 可以把表結果的列名都重新命名為相同的.

select Id, Sex, BId, `No`, Name, CreateDate from tch_teacherunionselect 0 as Id, Sex, BId, `No`, Name, CreateDate from tch_teacher_temp

union是會對最後的表結果進行去重操作的, 如果我不想去重, 只想快速得到拼接的結果, 可以使用 union all 來拼接. 

 

        長按二維碼識別關注，您的支援是我們最大的動力。       

        公眾號：測試夢工廠

        QQ一群：300897805

　　

 

Mysql - 查詢之關聯查詢