快捷導(dǎo)航

Mysql多表關(guān)聯(lián)不走索引的原因及分析

更新時(shí)間：2022年12月12日 11:32:43 作者：Singinwind

這篇文章主要介紹了Mysql多表關(guān)聯(lián)不走索引的原因及分析，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

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

接下來(lái)，就研究一下如何對(duì)海量數(shù)據(jù)的查詢(xún)進(jìn)行優(yōu)化。

一、準(zhǔn)備過(guò)程

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

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

-- 創(chuàng)建存儲(chǔ)過(guò)程
 
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í)行存儲(chǔ)過(guò)程
CALL my_insert();
 
 
-- 數(shù)據(jù)插入成功后修改表模式InnoDB 時(shí)間稍微久點(diǎn)
 alter table `large_student_tb` engine=InnoDB;

鵝，確實(shí)很慢了。跑了1000s還沒(méi)有跑完

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

笑了，這么久跑完了?？蓱z的電腦~~

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

SELECT COUNT(*) FROM LARGE_STUDENT_TB

？？懵了，是100萬(wàn)條？？我少寫(xiě)了一個(gè)零。

為了科學(xué)的嚴(yán)謹(jǐn)。我還得再跑900萬(wàn)條。1萬(wàn)s？？

先記錄一下，100w條：

查所有：1.3s~1.5s。

查某條 username999999：0.6s

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

還是先查詢(xún)一下如何進(jìn)行表的遷移吧。因?yàn)轭A(yù)期想來(lái)，1000萬(wàn)的表，加入索引，會(huì)加快查詢(xún)速度和聚簇函數(shù)的計(jì)算速度。從而進(jìn)行優(yōu)化。但是我之前在辦公室試過(guò)，往一張1000萬(wàn)的表里面加索引，速度很慢很慢，第一個(gè)想法是先建立一個(gè)一樣的表，先加上索引，再進(jìn)行表的遷移。相關(guān)操作如下

1.表的遷移:

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

 
-- 創(chuàng)建存儲(chǔ)過(guò)程
 
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í)行存儲(chǔ)過(guò)程
CALL my_insert();
 
 
-- 數(shù)據(jù)插入成功后修改表模式InnoDB 時(shí)間稍微久點(diǎn)
 alter table `large_student_tb` engine=InnoDB;

二、比較

1.對(duì)增加了索引和沒(méi)有索引的效果。查詢(xún)速度是指數(shù)級(jí)別的增加，如下

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

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

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

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

1.查詢(xún)表的大小

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.查詢(xún)?cè)撍饕拇笮?/p>

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

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

3.多表連接查詢(xún)的比較

-- 兩表聯(lián)查

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

---這個(gè)查詢(xún)不出來(lái)，有索引也沒(méi)有用。待優(yōu)化

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

--這個(gè)可以查詢(xún)出來(lái)，用時(shí)間55s左右，需要優(yōu)化

3.1 多表查詢(xún)沒(méi)有用上索引的原因。如上3所顯示，有個(gè)多表查詢(xún)。我們需要用EXPLAIN關(guān)鍵字來(lái)排查原因。

1.單表可快速查詢(xún)EXPLAIN

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

2.兩表連接查詢(xún)ON?？梢圆槌鰜?lái)，但是速度很慢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中，沒(méi)有用上索引。是什么原因?qū)е滤鼪](méi)有用上索引。會(huì)不會(huì)用上了以后就變快了？

綜合比較，得出的結(jié)論是，左連接會(huì)做全盤(pán)掃描。類(lèi)型為ALL，自然就不能使用索引了。因?yàn)樽蟊韆要全部掃描一遍。

3.查詢(xún)不出來(lái)的語(yǔ)句。

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

三、千萬(wàn)級(jí)別的數(shù)據(jù)查詢(xún)個(gè)人優(yōu)化建議

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

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

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

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

1.笛卡爾積：CROSS JOIN

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

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

內(nèi)連接INNER JOIN是最常用的連接操作。從數(shù)學(xué)的角度講就是求兩個(gè)表的交集，從笛卡爾積的角度講就是從笛卡爾積中挑出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的含義就是求兩個(gè)表的交集外加左表剩下的數(shù)據(jù)。依舊從笛卡爾積的角度講，就是先從笛卡爾積中挑出ON子句條件成立的記錄，然后加上左表中剩余的記錄（見(jiàn)最后三條）。

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

左邊的表格t_blog會(huì)全部輸出來(lái)，右邊的表格，沒(méi)有的數(shù)據(jù)會(huì)為NULL

4.右連接RIGHT JOIN

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

5.外連接：OUTER JOIN

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