Mysql多表關聯不走索引的原因及分析

2022-12-15 14:04:09

剛入職第一天，有個大佬寫了一個統計函數count(*)需要對兩張表a,b做統計。咋一看挺簡單的，可是表a有1000萬條資料，表b有300萬條資料。使用LEFT JOIN進行查詢。結果，一直查詢不出來，可能時間就很久了。然後，這個鍋就甩給第一天入職的我(我？？？)。

接下來，就研究一下如何對海量資料的查詢進行優化。

一、準備過程

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

a,建立儲存過程:插入1000萬條資料。n=10000000+1//為結束判斷條件

-- 建立儲存過程
 
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;
 
 
-- 執行儲存過程
CALL my_insert();
 
 
-- 資料插入成功後修改表模式InnoDB 時間稍微久點
 alter table `large_student_tb` engine=InnoDB;

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

繼續讓它跑一下吧。

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

b.查詢一下條數

SELECT COUNT(*) FROM LARGE_STUDENT_TB

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

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

先記錄一下，100w條：

查所有：1.3s~1.5s。

查某條 username999999：0.6s

繼續插入表剩下的900萬條。。來把英雄聯盟吧哈哈哈哈

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

1.表的遷移:

insert into db1.table1 select * from db2.table2  #完全複製

 
-- 建立儲存過程
 
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;
 
 
-- 執行儲存過程
CALL my_insert();
 
 
-- 資料插入成功後修改表模式InnoDB 時間稍微久點
 alter table `large_student_tb` engine=InnoDB;

二、比較

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

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

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

我查詢 username。沒有對username增加索引的時候，需要5s才能從千萬資料級別中查出某一條資料，增加了username欄位為索引，秒查詢。

2.索引增加後所佔據的空間大小，以及表本身的空間大小

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

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

3.多表連線查詢的比較

-- 兩表聯查

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

---這個查詢不出來，有索引也沒有用。待優化

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

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

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中，沒有用上索引。是什麼原因導致它沒有用上索引。會不會用上了以後就變快了？

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

3.查詢不出來的語句。

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

三、千萬級別的資料查詢個人優化建議

1.加索引。千萬級別資料查詢需要增加索引，索引在資料越多的情況下，效率越加明顯

2.單獨查表。兩張千萬級別的表查詢，不建議用聯表查。查一張結果，輸出一個資料。去查詢另外一張。

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

四、MYSQL多表查詢的區別

1.笛卡爾積：CROSS JOIN

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

2.內連線INNER JOIN

內連線INNER JOIN是最常用的連線操作。從數學的角度講就是求兩個表的交集，從笛卡爾積的角度講就是從笛卡爾積中挑出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的含義就是求兩個表的交集外加左表剩下的資料。依舊從笛卡爾積的角度講，就是先從笛卡爾積中挑出ON子句條件成立的記錄，然後加上左表中剩餘的記錄（見最後三條）。

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

左邊的表格t_blog會全部輸出來，右邊的表格，沒有的資料會為NULL

4.右連線RIGHT JOIN

同理右連線RIGHT JOIN就是求兩個表的交集外加右表剩下的資料。

5.外連線：OUTER JOIN

外連線就是求兩個集合的並集。從笛卡爾積的角度講就是從笛卡爾積中挑出ON子句條件成立的記錄，然後加上左表中剩餘的記錄，最後加上右表中剩餘的記錄。另外MySQL不支援OUTER JOIN，但是我們可以對左連線和右連線的結果做UNION操作來實現。

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

Mysql多表關聯不走索引的原因及分析

目錄

一、準備過程

二、比較

三、千萬級別的資料查詢個人優化建議

四、MYSQL多表查詢的區別

總結

熱門文章