剛?cè)肼毜谝惶?，有個大佬寫了一個統(tǒng)計函數(shù)count(*)需要對兩張表a,b做統(tǒng)計。咋一看挺簡單的，可是表a有1000萬條數(shù)據(jù)，表b有300萬條數(shù)據(jù)。使用LEFT JOIN進行查詢。結(jié)果，一直查詢不出來，可能時間就很久了。然后，這個鍋就甩給第一天入職的我(我？？？)。

接下來，就研究一下如何對海量數(shù)據(jù)的查詢進行優(yōu)化。

一、準備過程

1.創(chuàng)建兩張表，表A large_student_tb(幼兒園大班學生哈哈):1000萬條。表B samll_student_tb(小班學生orzzzzzzz):300萬條。不建立索引的情況。

a,建立存儲過程:插入1000萬條數(shù)據(jù)。n=10000000+1//為結(jié)束判斷條件

-- 創(chuàng)建存儲過程
 
DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
   DECLARE n int DEFAULT 1;
        loopname:LOOP
            INSERT INTO `large_student_tb`(`id`,`username`,`password`) VALUES ( n,CONCAT('myname',n),CONCAT('password',n));
            SET n=n+1;
        IF n=1000000+1 THEN
            LEAVE loopname;
        END IF;
        END LOOP loopname;
END;
 
 
-- 執(zhí)行存儲過程
CALL my_insert();
 
 
-- 數(shù)據(jù)插入成功后修改表模式InnoDB 時間稍微久點
 alter table `large_student_tb` engine=InnoDB;

鵝，確實很慢了。跑了1000s還沒有跑完

繼續(xù)讓它跑一下吧。

笑了，這么久跑完了。可憐的電腦~~

b.查詢一下條數(shù)

SELECT COUNT(*) FROM LARGE_STUDENT_TB

？？懵了，是100萬條？？我少寫了一個零。

為了科學的嚴謹。我還得再跑900萬條。1萬s？？

 先記錄一下，100w條：

查所有：1.3s~1.5s。

查某條 username999999：0.6s

繼續(xù)插入表剩下的900萬條。。來把英雄聯(lián)盟吧哈哈哈哈

還是先查詢一下如何進行表的遷移吧。因為預期想來，1000萬的表，加入索引，會加快查詢速度和聚簇函數(shù)的計算速度。從而進行優(yōu)化。但是我之前在辦公室試過，往一張1000萬的表里面加索引，速度很慢很慢，第一個想法是先建立一個一樣的表，先加上索引，再進行表的遷移。相關操作如下

1.表的遷移:

insert into db1.table1 select * from db2.table2 ?#完全復制

 
-- 創(chuàng)建存儲過程
 
DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
   DECLARE n int DEFAULT 1000000+1;
        loopname:LOOP
            INSERT INTO `large_student_tb`(`id`,`username`,`password`) VALUES ( n,CONCAT('myname',n),CONCAT('password',n));
            SET n=n+1;
        IF n=10000000+1 THEN
            LEAVE loopname;
        END IF;
        END LOOP loopname;
END;
 
 
-- 執(zhí)行存儲過程
CALL my_insert();
 
 
-- 數(shù)據(jù)插入成功后修改表模式InnoDB 時間稍微久點
 alter table `large_student_tb` engine=InnoDB;

二、比較

1.對增加了索引和沒有索引的效果。查詢速度是指數(shù)級別的增加，如下

SELECT * FROM LARGE_STUDENT_TB a where a.username = 'myname1002554' ?

-- index before 5.532s ?--index after 0.037s

我查詢 username。沒有對username增加索引的時候，需要5s才能從千萬數(shù)據(jù)級別中查出某一條數(shù)據(jù)，增加了username字段為索引，秒查詢。

2.索引增加后所占據(jù)的空間大小，以及表本身的空間大小

1.查詢表的大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data ?from TABLES where table_schema='simonsdb' and table_name='large_student_tb';

550.00MB

2.查詢該索引的大小

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES ?WHERE table_schema = 'simonsdb'
and table_name='large_student_tb'

235.94MB

如上，索引的增加會帶來存儲空間的增加。但是速度卻是很快。以犧牲空間換取這么大倍數(shù)的時間效率，值得。

3.多表連接查詢的比較

-- 兩表聯(lián)查

EXPLAIN SELECT * FROM small_student_tb a left join ?large_student_tb ?b on a.username = 'myname1002554'

---這個查詢不出來，有索引也沒有用。待優(yōu)化

SELECT * FROM small_student_tb a left join ?small_student_tb ?b on a.username = b.username;

--這個可以查詢出來，用時間55s左右，需要優(yōu)化

3.1 多表查詢沒有用上索引的原因。 如上3所顯示，有個多表查詢。我們需要用EXPLAIN關鍵字來排查原因。

1.單表可快速查詢EXPLAIN

EXPLAIN SELECT * FROM LARGE_STUDENT_TB a where a.username = 'myname1002554'?

2.兩表連接查詢ON。可以查出來，但是速度很慢55s。EXPLAIN一下

EXPLAIN SELECT * FROM small_student_tb a left join ?small_student_tb ?b on a.username = b.username

我們可以看到表a 也就是 small_student_tb在possible_keys中，沒有用上索引。是什么原因?qū)е滤鼪]有用上索引。會不會用上了以后就變快了？

綜合比較，得出的結(jié)論是，左連接會做全盤掃描。類型為ALL，自然就不能使用索引了。因為左表a要全部掃描一遍。

3.查詢不出來的語句。

EXPLAIN SELECT * FROM small_student_tb a left join ?large_student_tb ?b on a.username = 'myname1002554'

三、千萬級別的數(shù)據(jù)查詢個人優(yōu)化建議

1.加索引。千萬級別數(shù)據(jù)查詢需要增加索引，索引在數(shù)據(jù)越多的情況下，效率越加明顯

2.單獨查表。兩張千萬級別的表查詢，不建議用聯(lián)表查。查一張結(jié)果，輸出一個數(shù)據(jù)。去查詢另外一張。

3.實在需要多表聯(lián)查，應該注意兩張表的字符編碼級別是否相同。

四、MYSQL多表查詢的區(qū)別

1.笛卡爾積：CROSS JOIN

笛卡爾積就是將A表的每一條記錄與B表的每一條記錄強行拼在一起。所以，如果A表有n條記錄，B表有m條記錄，笛卡爾積產(chǎn)生的結(jié)果就會產(chǎn)生n*m條記錄。下面的例子，t_blog有10條記錄，t_type有5條記錄，所有他們倆的笛卡爾積有50條記

2.內(nèi)連接INNER JOIN

內(nèi)連接INNER JOIN是最常用的連接操作。從數(shù)學的角度講就是求兩個表的交集，從笛卡爾積的角度講就是從笛卡爾積中挑出ON子句條件成立的記錄。有INNER JOIN，WHERE（等值連接）         

SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;

SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;

3.左連接LEFT JOIN

左連接LEFT JOIN的含義就是求兩個表的交集外加左表剩下的數(shù)據(jù)。依舊從笛卡爾積的角度講，就是先從笛卡爾積中挑出ON子句條件成立的記錄，然后加上左表中剩余的記錄（見最后三條）。

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;

左邊的表格t_blog會全部輸出來，右邊的表格，沒有的數(shù)據(jù)會為NULL 

4.右連接RIGHT JOIN

同理右連接RIGHT JOIN就是求兩個表的交集外加右表剩下的數(shù)據(jù)。

5.外連接：OUTER JOIN

外連接就是求兩個集合的并集。從笛卡爾積的角度講就是從笛卡爾積中挑出ON子句條件成立的記錄，然后加上左表中剩余的記錄，最后加上右表中剩余的記錄。另外MySQL不支持OUTER JOIN，但是我們可以對左連接和右連接的結(jié)果做UNION操作來實現(xiàn)。

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論