如何设置MySQL数据库和编写存储过程进行批量数据插入。通过理解这些概念，您将能够优化SQL查询并提高数据库的响应时间。
在本文中，我们将学习如何通过对表列进行索引来提高SQL查询的快速响应时间。我们将涵盖安装MySQL、创建存储过程、分析查询以及了解索引的影响的步骤。
我在Ubuntu上使用了MySQL 8版本。同时，我使用Dbeavor工具作为MySQL客户端连接到MySQL服务器。让我们一起学习吧。
我在演示中使用了MySQL，然而，在所有其他数据库中，概念是相同的。
1.下面是我们安装MySQL并使用root用户访问的方法。
这个MySQL实例仅用于测试，因此我使用了一个简单的密码
# 安装 $ sudo apt install mysql-server # 启动服务 $ sudo systemctl start mysql.service #开始使用服务 $ sudo mysql # 设置密码规则 mysql> SET GLOBAL validate_password.policy = 0; # 设置一个简单的密码 mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; # 退出 mysql> exit # 试试重新登录 $ mysql -uroot -ppassword
2.创建一个数据库并使用它。
# 创建数据库 mysql> create database testdb; # 查看数据库 mysql> show databases; # 使用这个数据库 mysql> use testdb;
3.创建两个表，employee1和employee2。
其中，employee1没有主键，而employee2有主键。
# 创建表 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) # 创建表 employee2，并且设置一个主键 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. 检查每个表的索引
我们会发现employee2表已经在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. 创建一个存储过程来批量插入数据到这两个表中。
我们将在每个表中插入20000条记录。然后可以使用CALL procedure-name命令调用存储过程。
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的记录
我们会发现employee1表的响应速度较慢，因为它没有任何索引。
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的输出。
该命令实际执行查询，并对查询进行规划、监控并计算在执行计划的各个点处所花费的时间和行数。
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"表上执行了一个过滤操作，只会选择"id"列值为3456的行。 (cost=1989 rows=1965) (actual time=5.3..31.9 rows=1 loops=1)：这部分提供了关于查询执行的一些性能相关信息： cost=1989：它代表整个查询执行的成本估算。成本是度量执行查询所需的计算工作量的相对指标。 rows=1965：它表示在查询的这一部分中将处理的预估行数。 actual time=5.3..31.9：这显示了查询执行这一部分的实际时间，以毫秒为单位进行测量。 rows=1 loops=1：表示该查询的这一部分在循环中执行的次数。 -> Table scan on employee1 (cost=1989 rows=19651) (actual time=0.034..29.7 rows=20000 loops=1)：这部分显示正在"employee1"表上执行表扫描操作： Table scan：这意味着数据库正在扫描整个"employee1"表，以查找与过滤条件匹配的行。 cost=1989：此表扫描操作的成本估算。 rows=19651：在"employee1"表中的预估行数。 actual time=0.034..29.7：表扫描操作的实际执行时间，以毫秒为单位进行测量。 rows=20000 loops=1：此表扫描操作在循环中执行的次数。 总体而言，这个查询计划表明数据库正在执行一个查询，将"employee1"表进行筛选，仅返回"id"列等于3456的行。 表扫描操作读取了共计20,000行以找到匹配的行，并且估算成本为1989个单位。 实际执行时间为5.3至31.9毫秒，取决于符合过滤条件的行数。
在这里，我们发现对于employee1，执行了一次表扫描，这意味着要扫描或搜索整个表来获取结果。我们也称之为对表进行全面扫描。
8. 对于employee2表，我们发现只搜索并获取了一行结果。
因此，如果表中有很多记录，我们将观察到SQL查询响应时间的明显改善。
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) #根据ChatGPT对这个查询计划的解释： 在执行之前获取的行：这部分表示数据库在执行主查询之前获取了一些数据。 (cost=0..0 rows=1)：这个操作的成本估算为0个单位，它预期只获取一行。 (actual time=110e-6..190e-6 rows=1 loops=1)：这提供了数据获取操作的实际时间： actual time=110e-6..190e-6：数据获取操作的实际时间范围，以微秒(µs)为单位进行测量。 rows=1：获取的行数。 loops=1：此数据获取操作在循环中执行的次数。 总体而言，查询计划的这部分表示数据库在执行主查询之前获取了一行数据。这个数据获取操作的实际时间范围为110到190微秒。这个初步的数据获取可能与获取一些执行主查询所需的关键信息或参数有关。
9. 让我们分析当我们在两个表的非索引列FirstName上搜索记录时的查询计划。
从输出中，我们发现执行了表扫描来搜索记录，这需要相当长的时间来获取数据。
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列上创建一个索引
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列的单条记录时的查询计划。
我们发现employee1快速提供响应，只有1行要搜索，并且在使用FirstName列上的索引时，在employee1表上执行了索引查找。但对于employee2，响应时间较长，并且要搜索所有20000行才能获得响应。文章来源：https://www.toymoban.com/diary/sql/686.html
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)
就是这样，同学们。本文将帮助我们理解索引对表的影响，如何使用"explain analyze"命令分析查询。还有关于如何设置MySQL和如何编写用于批量插入的存储过程的学习内容。文章来源地址https://www.toymoban.com/diary/sql/686.html
到此这篇关于了解索引对表的影响以及如何分析查询计划，在MySQL中进行SQL查询性能调优的文章就介绍到这了,更多相关内容可以在右上角搜索或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
