了解索引对表的影响以及如何分析查询计划，在MySQL中进行SQL查询性能调优

如何设置 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  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 行才能获得响应。

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 模板网！

原文地址:https://www.toymoban.com/diary/sql/686.html

如若转载，请注明出处：如若内容造成侵权 / 违法违规 / 事实不符，请联系站长进行投诉反馈，一经查实，立即删除！