欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間：2022年12月12日 11:32:43   作者：Singinwind  
這篇文章主要介紹了Mysql多表關聯不走索引的原因及分析，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

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

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

一、準備過程

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

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

-- 創(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();
 
 
-- 數據插入成功后修改表模式InnoDB 時間稍微久點
 alter table `large_student_tb` engine=InnoDB;

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

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

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

 

b.查詢一下條數

SELECT COUNT(*) FROM LARGE_STUDENT_TB

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

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

 先記錄一下，100w條：

查所有：1.3s~1.5s。

查某條 username999999：0.6s

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

還是先查詢一下如何進行表的遷移吧。因為預期想來，1000萬的表，加入索引，會加快查詢速度和聚簇函數的計算速度。從而進行優(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();
 
 
-- 數據插入成功后修改表模式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'

---這個查詢不出來，有索引也沒有用。待優(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中，沒有用上索引。是什么原因導致它沒有用上索引。會不會用上了以后就變快了？

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

3.查詢不出來的語句。

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

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

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

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

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

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

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操作來實現。

總結

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

相關文章

  • mysql 復制原理與實踐應用詳解

    mysql 復制原理與實踐應用詳解

    這篇文章主要介紹了mysql 復制原理與實踐應用,結合實例形式詳細分析了MySQL數據庫復制功能的原理、操作技巧與相關注意事項,需要的朋友可以參考下
    2020-02-02
  • SQL Server 數據庫的備份詳細介紹及注意事項

    SQL Server 數據庫的備份詳細介紹及注意事項

    這篇文章主要介紹了SQL Server 備份詳細介紹及注意事項的相關資料,需要的朋友可以參考下
    2016-12-12
  • mysql查詢表里的重復數據方法

    mysql查詢表里的重復數據方法

    這篇文章主要介紹了mysql查詢表里的重復數據方法,需要的朋友可以參考下
    2017-05-05
  • MySQL事務視圖索引備份和恢復概念介紹

    MySQL事務視圖索引備份和恢復概念介紹

    這篇文章主要介紹了MySQL事務、視圖、索引、備份和恢復，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2022-09-09
  • mysql5.7.17.msi安裝圖文教程

    mysql5.7.17.msi安裝圖文教程

    這篇文章主要為大家詳細介紹了mysql 5.7.17.msi安裝圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2017-07-07
  • 詳解MySQL數據庫千萬級數據查詢和存儲

    詳解MySQL數據庫千萬級數據查詢和存儲

    百萬級、千萬級數據處理，核心關鍵在于數據存儲方案設計，存儲方案設計的是否合理，直接影響到數據CRUD操作?？傮w設計可以考慮一下幾個方面進行設計考慮：數據存儲結構設計；索引設計；數據主鍵設計；查詢方案設計。
    2021-05-05
  • MySQL 將文件導入數據庫(load data Statement)

    MySQL 將文件導入數據庫(load data Statement)

    本文主要介紹了MySQL 將文件導入數據庫,可以使用load data infile語句將文件中的數據加載到數據庫中,感興趣的可以了解一下
    2024-09-09
  • Mysql 乘法除法精度不一致問題(除法后四位小數)

    Mysql 乘法除法精度不一致問題(除法后四位小數)

    這篇文章主要介紹了Mysql 乘法除法精度不一致，除法后四位小數,本文通過問題分析實例代碼講解，給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
    2020-03-03
  • Mysql事務中Update是否會鎖表？

    Mysql事務中Update是否會鎖表？

    這篇文章主要給大家介紹了關于Mysql事務中Update是否會鎖表的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2020-12-12
  • 詳解JDBC數據庫鏈接及相關方法的封裝

    詳解JDBC數據庫鏈接及相關方法的封裝

    這篇文章主要介紹了詳解JDBC數據庫鏈接及相關方法的封裝的相關資料,下面是封裝的具體類，用到了泛型和反射，希望能幫助到大家，需要的朋友可以參考下
    2017-08-08

最新評論