MySQL 每次查詢一條數據查詢十次與一次查詢十條數據之間的區別

時間  2020-09-05 標籤 mysql 每次 查詢 一條 數據 查詢 十 次 一次 查詢 十條 數據 之間 區別

一 引子

有個知友邀請我回答問題，問道：「MySQL 每次查詢一條數據查 10 次和一次查詢 10 條數據效率有多少差距？」 css


整體上來講，一次查詢 10 條數據效率是高於每次查詢一條數據查 10 次的。但究竟差距多少，具體的數據很難說。這原本是一個很簡單的問題，但我仍是想親身實踐下，給之後碰到這個問題的朋友一點參考。我先作一個模擬，而後在文末給出一個分析。 mysql

說明：本文中的模擬只能提供參考。實際狀況跟硬件配置、系統負載等因素相關。 sql

二 模擬

在作模擬以前，得有數據。因此我建立了一組測試數據，以下： vim

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> USE test;
Database changed
 
mysql> CREATE TABLE user
     -> (id INT PRIMARY KEY AUTO_INCREMENT,
     -> name VARCHAR( 20 ),
     -> age INT,
     -> sex CHAR( 2 ),
     -> city VARCHAR( 20 ),
     -> work VARCHAR( 10 )
     -> ) DEFAULT CHARSET utf 8 ENGINE = INNODB;
Query OK, 0 rows affected ( 0.10 sec)
 
mysql> INSERT INTO user(name, age, sex, city, work) \
     -> VALUES( "robin01" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin02" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin03" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin04" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin05" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin06" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin07" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin08" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin09" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin10" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin11" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin12" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin13" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin14" , 19 , "M" , "GuangZhou" , "DBA" ),\
     -> ( "robin15" , 19 , "M" , "GuangZhou" , "DBA" );
Query OK, 15 rows affected ( 0.03 sec)
Records: 15  Duplicates: 0  Warnings: 0

接着，爲了模擬一條數據查詢十次，我寫了一個存儲過程。這個存儲過程也很簡單，以下： 緩存

說明：這裏的模擬若是這樣會更好：不用循環，寫十條 SQL，ID 不一樣。查詢相同的數據會受查詢緩存的影響，多少有些誤差。數據少，差異不是太大，因此這裏仍是這樣模擬了。 數據結構


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
vim /tmp/proc_loop.sql
delimiter //
DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
    DECLARE int_val INT DEFAULT 0 ;
    test_loop : LOOP
       IF (int_val = 10 ) THEN
          LEAVE test_loop;
       END IF;
    SELECT * FROM user WHERE id = 7 ;
    SET int_val = int_val + 1 ;
    END LOOP;
END //
delimiter ;
而後，執行此外部 SQL。在調用此存儲過程以前，我設置了 profiling ＝ 1，目的是統計 SQL 執行時間（只截取了須要的數據）。數據量比較少，耗費時間都是毫秒級，甚至更少。因此採用了這個笨辦法。以下：



?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> source /tmp/proc_loop.sql
Query OK, 0 rows affected ( 0.00 sec)
 
Query OK, 0 rows affected ( 0.00 sec)
 
mysql> SET profiling = 1 ;
Query OK, 0 rows affected ( 0.00 sec)
 
mysql> call proc_loop_test();
 
mysql> SHOW PROFILES;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|       13 | 0.00019700 | SELECT * FROM user WHERE id = 7 |
|       14 | 0.00009800 | SELECT * FROM user WHERE id = 7 |
|       15 | 0.00016200 | SELECT * FROM user WHERE id = 7 |
|       16 | 0.00016100 | SELECT * FROM user WHERE id = 7 |
|       17 | 0.00012100 | SELECT * FROM user WHERE id = 7 |
|       18 | 0.00014500 | SELECT * FROM user WHERE id = 7 |
|       19 | 0.00010000 | SELECT * FROM user WHERE id = 7 |
|       20 | 0.00010300 | SELECT * FROM user WHERE id = 7 |
|       21 | 0.00009300 | SELECT * FROM user WHERE id = 7 |
|       22 | 0.00009300 | SELECT * FROM user WHERE id = 7 |
+----------+------------+---------------------------------+
15 rows in set ( 0.00 sec)
再接着，利用分頁一條 SQL 查詢 10 條數據，而且查詢所用時間（只截取了須要的數據）。



?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> SELECT * FROM user LIMIT 0 , 10 ;
+----+---------+------+------+-----------+------+
| id | name    | age  | sex  | city      | work |
+----+---------+------+------+-----------+------+
|  1 | robin 01 |   19 | M    | GuangZhou | DBA  |
|  2 | robin 02 |   19 | M    | GuangZhou | DBA  |
|  3 | robin 03 |   19 | M    | GuangZhou | DBA  |
|  4 | robin 04 |   19 | M    | GuangZhou | DBA  |
|  5 | robin 05 |   19 | M    | GuangZhou | DBA  |
|  6 | robin 06 |   19 | M    | GuangZhou | DBA  |
|  7 | robin 07 |   19 | M    | GuangZhou | DBA  |
|  8 | robin 08 |   19 | M    | GuangZhou | DBA  |
|  9 | robin 09 |   19 | M    | GuangZhou | DBA  |
| 10 | robin 10 |   19 | M    | GuangZhou | DBA  |
+----+---------+------+------+-----------+------+
10 rows in set ( 0.00 sec)
 
mysql> SHOW PROFILES;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00030400 | SELECT * FROM user LIMIT 0 , 10 |
+----------+------------+-------------------------------+
1 row in set ( 0.00 sec)
最後，統計每次查詢一條數據查詢 10 此所需時間，完成後，計算和一次查詢 10 條數據耗費時間的比值，能夠看到，每次查詢一條數據，查詢 10 次耗費時間爲 0.00127300 秒，一次查詢 10 條數據耗費時間爲 0.00030400 秒，他們之間的比值爲 4.1875。若是數據量夠大，數據夠複雜，這個比值會更大的。



?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SELECT 0.00019700 + 0.00009800 + 0.00016200 + \
        -> 0.00016100 + 0.00012100 + 0.00014500 + 0.00010000 \
        -> + 0.00010300 + 0.00009300 + 0.00009300 \
        -> AS mutiple_select;
+----------------+
| mutiple_select |
+----------------+
|     0.00127300 |
+----------------+
1 row in set ( 0.00 sec)
 
mysql> SELECT 0.00127300 / 0.00030400 AS times;
+----------------+
| times          |
+----------------+
| 4.187500000000 |
+----------------+
1 row in set ( 0.00 sec)

三 分析

MySQL 中，每一次查詢要通過以下過程： oop

  • SQL 接口（SQL Interface）接受用戶輸入的 SQL 命令，此時會創建 Socket 鏈接；
  • SQL 命令傳遞到解析器（Parser）的時候會被解析器驗證和解析，將 SQL 語句分解成數據結構，並將這個結構傳遞到後續步驟，之後 SQL 語句的傳遞和處理就是基於這個結構；若是在分解構成中遇到錯誤，那麼就說明這個 SQL 語句是不合理的。
  • SQL 語句在查詢以前會使用查詢優化器（Optimizer）對查詢進行優化，構建查詢計劃；
  • 若是查詢緩存有命中的查詢結果，查詢語句就能夠直接去查詢緩存中取數據。這一部分是經過查詢緩存（Cache 和 Buffer）實現。 
联系我们 沪ICP备2021010478号-7