如何設(shè)置MySQL數(shù)據(jù)庫和編寫存儲過程進行批量數(shù)據(jù)插入。通過理解這些概念，您將能夠優(yōu)化SQL查詢并提高數(shù)據(jù)庫的響應(yīng)時間。

在本文中，我們將學(xué)習如何通過對表列進行索引來提高SQL查詢的快速響應(yīng)時間。我們將涵蓋安裝MySQL、創(chuàng)建存儲過程、分析查詢以及了解索引的影響的步驟。

我在Ubuntu上使用了MySQL 8版本。同時，我使用Dbeavor工具作為MySQL客戶端連接到MySQL服務(wù)器。讓我們一起學(xué)習吧。

我在演示中使用了MySQL，然而，在所有其他數(shù)據(jù)庫中，概念是相同的。

1.下面是我們安裝MySQL并使用root用戶訪問的方法。

這個MySQL實例僅用于測試，因此我使用了一個簡單的密碼

# 安裝
$ sudo apt install mysql-server
# 啟動服務(wù)
$ sudo systemctl start mysql.service
#開始使用服務(wù)
$ sudo mysql
# 設(shè)置密碼規(guī)則
mysql> SET GLOBAL validate_password.policy = 0;
# 設(shè)置一個簡單的密碼
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
# 退出
mysql> exit
# 試試重新登錄
$ mysql -uroot -ppassword

2.創(chuàng)建一個數(shù)據(jù)庫并使用它。

# 創(chuàng)建數(shù)據(jù)庫
mysql> create database testdb;
# 查看數(shù)據(jù)庫
mysql> show databases;
# 使用這個數(shù)據(jù)庫
mysql> use testdb;

3.創(chuàng)建兩個表，employee1和employee2。

其中，employee1沒有主鍵，而employee2有主鍵。

# 創(chuàng)建表 employee1
mysql> CREATE TABLE employee1 (id int,LastName varchar(255),FirstName varchar(255),Address varchar(255),profile varchar(255));
Query OK, 0 rows affected (0.01 sec)
# 創(chuàng)建表 employee2，并且設(shè)置一個主鍵 
mysql> CREATE TABLE employee2 (id int primary key,LastName varchar(255),FirstName varchar(255),Address varchar(255),profile varchar(255));
Query OK, 0 rows affected (0.02 sec
# 查看表
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| employee1        |
| employee2        |
+------------------+
2 rows in set (0.00 sec)

4. 檢查每個表的索引

我們會發(fā)現(xiàn)employee2表已經(jīng)在id列上有一個索引，因為它是主鍵。

mysql> SHOW INDEXES FROM employee1 \G;
Empty set (0.00 sec)

ERROR: 
No query specified

mysql> SHOW INDEXES FROM employee2 \G;
*************************** 1. row ***************************
        Table: employee2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

5. 創(chuàng)建一個存儲過程來批量插入數(shù)據(jù)到這兩個表中。

我們將在每個表中插入20000條記錄。然后可以使用CALL procedure-name命令調(diào)用存儲過程。

mysql> 

CREATE PROCEDURE testdb.BulkInsert()
BEGIN
		DECLARE i INT DEFAULT 1;
truncate table employee1;
truncate table employee2;
WHILE (i <= 20000) DO
    INSERT INTO testdb.employee1 (id, FirstName, Address) VALUES(i, CONCAT("user","-",i), CONCAT("address","-",i));
    INSERT INTO testdb.employee2 (id,FirstName, Address) VALUES(i,CONCAT("user","-",i), CONCAT("address","-",i));    
   SET i = i+1;
END WHILE;
END

mysql> CALL testdb.BulkInsert() ;

mysql> SELECT COUNT(*) from employee1 e ;
COUNT(*)|
--------+
    20000|
    

mysql> SELECT COUNT(*) from employee2 e ;
COUNT(*)|
--------+
    20000|

6. 選擇任意隨機id的記錄

我們會發(fā)現(xiàn)employee1表的響應(yīng)速度較慢，因為它沒有任何索引。

mysql> select * from employee2 where id = 15433;
+-------+----------+------------+---------------+---------+
| id    | LastName | FirstName  | Address       | profile |
+-------+----------+------------+---------------+---------+
| 15433 | NULL     | user-15433 | address-15433 | NULL    |
+-------+----------+------------+---------------+---------+
1 row in set (0.00 sec)

mysql> select * from employee1 where id = 15433;
+-------+----------+------------+---------------+---------+
| id    | LastName | FirstName  | Address       | profile |
+-------+----------+------------+---------------+---------+
| 15433 | NULL     | user-15433 | address-15433 | NULL    |
+-------+----------+------------+---------------+---------+
1 row in set (0.03 sec)

mysql> select * from employee1 where id = 19728;
+-------+----------+------------+---------------+---------+
| id    | LastName | FirstName  | Address       | profile |
+-------+----------+------------+---------------+---------+
| 19728 | NULL     | user-19728 | address-19728 | NULL    |
+-------+----------+------------+---------------+---------+
1 row in set (0.03 sec)

mysql> select * from employee2 where id = 19728;
+-------+----------+------------+---------------+---------+
| id    | LastName | FirstName  | Address       | profile |
+-------+----------+------------+---------------+---------+
| 19728 | NULL     | user-19728 | address-19728 | NULL    |
+-------+----------+------------+---------------+---------+
1 row in set (0.00 sec)

mysql> select * from employee1 where id = 3456;
+------+----------+-----------+--------------+---------+
| id   | LastName | FirstName | Address      | profile |
+------+----------+-----------+--------------+---------+
| 3456 | NULL     | user-3456 | address-3456 | NULL    |
+------+----------+-----------+--------------+---------+
1 row in set (0.04 sec)

mysql> select * from employee2 where id = 3456;
+------+----------+-----------+--------------+---------+
| id   | LastName | FirstName | Address      | profile |
+------+----------+-----------+--------------+---------+
| 3456 | NULL     | user-3456 | address-3456 | NULL    |
+------+----------+-----------+--------------+---------+
1 row in set (0.00 sec)

7. 檢查命令EXPLAIN ANALYZE的輸出。

該命令實際執(zhí)行查詢，并對查詢進行規(guī)劃、監(jiān)控并計算在執(zhí)行計劃的各個點處所花費的時間和行數(shù)。

mysql> explain analyze select * from employee1 where id = 3456;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employee1.id = 3456)  (cost=1989 rows=1965) (actual time=5.24..29.3 rows=1 loops=1)
    -> Table scan on employee1  (cost=1989 rows=19651) (actual time=0.0504..27.3 rows=20000 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

#從ChatGPT的詳細解釋中可以得到以下信息：

filter: (employee1.id = 3456)：這表示在"employee1"表上執(zhí)行了一個過濾操作，只會選擇"id"列值為3456的行。

(cost=1989 rows=1965) (actual time=5.3..31.9 rows=1 loops=1)：這部分提供了關(guān)于查詢執(zhí)行的一些性能相關(guān)信息：

cost=1989：它代表整個查詢執(zhí)行的成本估算。成本是度量執(zhí)行查詢所需的計算工作量的相對指標。

rows=1965：它表示在查詢的這一部分中將處理的預(yù)估行數(shù)。

actual time=5.3..31.9：這顯示了查詢執(zhí)行這一部分的實際時間，以毫秒為單位進行測量。

rows=1 loops=1：表示該查詢的這一部分在循環(huán)中執(zhí)行的次數(shù)。
-> Table scan on employee1 (cost=1989 rows=19651) (actual time=0.034..29.7 rows=20000 loops=1)：這部分顯示正在"employee1"表上執(zhí)行表掃描操作：

Table scan：這意味著數(shù)據(jù)庫正在掃描整個"employee1"表，以查找與過濾條件匹配的行。

cost=1989：此表掃描操作的成本估算。

rows=19651：在"employee1"表中的預(yù)估行數(shù)。

actual time=0.034..29.7：表掃描操作的實際執(zhí)行時間，以毫秒為單位進行測量。

rows=20000 loops=1：此表掃描操作在循環(huán)中執(zhí)行的次數(shù)。

總體而言，這個查詢計劃表明數(shù)據(jù)庫正在執(zhí)行一個查詢，將"employee1"表進行篩選，僅返回"id"列等于3456的行。
表掃描操作讀取了共計20,000行以找到匹配的行，并且估算成本為1989個單位。
實際執(zhí)行時間為5.3至31.9毫秒，取決于符合過濾條件的行數(shù)。

在這里，我們發(fā)現(xiàn)對于employee1，執(zhí)行了一次表掃描，這意味著要掃描或搜索整個表來獲取結(jié)果。我們也稱之為對表進行全面掃描。

8. 對于employee2表，我們發(fā)現(xiàn)只搜索并獲取了一行結(jié)果。

因此，如果表中有很多記錄，我們將觀察到SQL查詢響應(yīng)時間的明顯改善。

mysql> explain analyze select * from employee2 where id = 3456;
+---------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                           |
+---------------------------------------------------------------------------------------------------+
| -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=110e-6..190e-6 rows=1 loops=1)
 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#根據(jù)ChatGPT對這個查詢計劃的解釋：

在執(zhí)行之前獲取的行：這部分表示數(shù)據(jù)庫在執(zhí)行主查詢之前獲取了一些數(shù)據(jù)。

(cost=0..0 rows=1)：這個操作的成本估算為0個單位，它預(yù)期只獲取一行。

(actual time=110e-6..190e-6 rows=1 loops=1)：這提供了數(shù)據(jù)獲取操作的實際時間：

actual time=110e-6..190e-6：數(shù)據(jù)獲取操作的實際時間范圍，以微秒(μs)為單位進行測量。

rows=1：獲取的行數(shù)。

loops=1：此數(shù)據(jù)獲取操作在循環(huán)中執(zhí)行的次數(shù)。

總體而言，查詢計劃的這部分表示數(shù)據(jù)庫在執(zhí)行主查詢之前獲取了一行數(shù)據(jù)。這個數(shù)據(jù)獲取操作的實際時間范圍為110到190微秒。這個初步的數(shù)據(jù)獲取可能與獲取一些執(zhí)行主查詢所需的關(guān)鍵信息或參數(shù)有關(guān)。

9. 讓我們分析當我們在兩個表的非索引列FirstName上搜索記錄時的查詢計劃。

從輸出中，我們發(fā)現(xiàn)執(zhí)行了表掃描來搜索記錄，這需要相當長的時間來獲取數(shù)據(jù)。

mysql> explain analyze select * from employee2 where FirstName = 'user-13456';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employee2.FirstName = 'user-13456')  (cost=2036 rows=2012) (actual time=15.7..24 rows=1 loops=1)
    -> Table scan on employee2  (cost=2036 rows=20115) (actual time=0.0733..17.8 rows=20000 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> explain analyze select * from employee1 where FirstName = 'user-13456';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employee1.FirstName = 'user-13456')  (cost=1989 rows=1965) (actual time=23.7..35.2 rows=1 loops=1)
    -> Table scan on employee1  (cost=1989 rows=19651) (actual time=0.0439..28.9 rows=20000 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

10. 在employee1表的FirstName列上創(chuàng)建一個索引

mysql> CREATE INDEX index1 ON employee1 (FirstName);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from employee1 \G;
*************************** 1. row ***************************
        Table: employee1
   Non_unique: 1
     Key_name: index1
 Seq_in_index: 1
  Column_name: FirstName
    Collation: A
  Cardinality: 19651
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

ERROR: 
No query specified

11. 再次檢查兩個表在搜索FirstName列的單條記錄時的查詢計劃。

我們發(fā)現(xiàn)employee1快速提供響應(yīng)，只有1行要搜索，并且在使用FirstName列上的索引時，在employee1表上執(zhí)行了索引查找。但對于employee2，響應(yīng)時間較長，并且要搜索所有20000行才能獲得響應(yīng)。

mysql> explain analyze select * from employee1 where FirstName = 'user-13456';
+-------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on employee1 using index1 (FirstName='user-13456')  (cost=0.35 rows=1) (actual time=0.0594..0.0669 rows=1 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> explain analyze select * from employee2 where FirstName = 'user-13456';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employee2.FirstName = 'user-13456')  (cost=2036 rows=2012) (actual time=15.7..23.5 rows=1 loops=1)
    -> Table scan on employee2  (cost=2036 rows=20115) (actual time=0.075..17.5 rows=20000 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

就是這樣，同學(xué)們。本文將幫助我們理解索引對表的影響，如何使用"explain analyze"命令分析查詢。還有關(guān)于如何設(shè)置MySQL和如何編寫用于批量插入的存儲過程的學(xué)習內(nèi)容。文章來源地址http://www.zghlxwxcb.cn/article/686.html

到此這篇關(guān)于了解索引對表的影響以及如何分析查詢計劃，在MySQL中進行SQL查詢性能調(diào)優(yōu)的文章就介紹到這了,更多相關(guān)內(nèi)容可以在右上角搜索或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！