开启辅助访问

Bo's Oracle Station

Bo's Oracle Station › 博客首页 ›Oracle技术› Oracle云、Linux、MySQL和Java › 查看内容

【博客文章2026】MySQL 9.x数据库管理技巧12：MySQL数据库的SQL调优

2026-4-13 08:50| 发布者: admin| 查看: 362| 评论: 0|原作者: Bo Tang

摘要: 首先介绍了通过慢查询日志定位低效SQL的方法。随后，针对一个典型聚合查询，分析了其全表扫描和嵌套循环连接带来的高I/O问题，通过SQL改写（引入子查询预先聚合过滤）显著减少了参与连接的行数，速度提升一倍。另一案例中，针对“查找名称以T开头的城市”的模糊查询，通过在name字段上创建索引，将全表扫描优化为索引范围扫描，速度提升6倍。本文强调，通过慢日志分析、执行计划解读以及合理的SQL改写与索引设计，可有效提升MySQL数据库的查询性能。
【博客文章2026】MySQL 9.x数据库管理技巧12：MySQL数据库SQL调优----SQL改写和索引


Author: Bo Tang

1. MySQL数据库SQL调优诊断日志：

    slow log是执行MySQL数据库调优时，必须查看的日志。查看实验环境中slow log是否打开，也查看slow log所在的位置：

[root@station95 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12637
Server version: 9.6.0 MySQL Community Server - GPL

Copyright (c) 2000, 2026, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%slow%';
+-----------------------------+-----------------------------------+
| Variable_name               | Value                             |
+-----------------------------+-----------------------------------+
| log_slow_admin_statements   | OFF                               |
| log_slow_extra              | OFF                               |
| log_slow_replica_statements | OFF                               |
| log_slow_slave_statements   | OFF                               |
| slow_launch_time            | 2                                 |
| slow_query_log              | ON                                |
| slow_query_log_file         | /var/lib/mysql/station95-slow.log |
+-----------------------------+-----------------------------------+
7 rows in set (0.005 sec)

    为了筛选慢SQL目的，需要设置slow log记录的阈值起点。再次说明，因为本博客的小工作负载实验目的，所以在会话中设置为0.01秒：    

mysql>  set long_query_time=0.01;
Query OK, 0 rows affected (0.000 sec)

mysql>   show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.010000 |
+-----------------+----------+
1 row in set (0.008 sec)
 
2. MySQL数据库SQL调优之SQL改写：

    假设生产环境有这么一个查询需求：拥有超过100座城市的国家，以及所拥有城市的具体数目，按照城市的具体数目递增排序：

mysql> use world_y;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select co.name, count(ci.id) from city ci join country co on (ci.countrycode=co.code) group by co.name having count(ci.id)>=100 order by count(ci.id) ;
+--------------------+--------------+
| name               | count(ci.id) |
+--------------------+--------------+
| Philippines        |          136 |
| Mexico             |          173 |
| Russian Federation |          189 |
| Japan              |          248 |
| Brazil             |          250 |
| United States      |          274 |
| India              |          341 |
| China              |          362 |
+--------------------+--------------+
8 rows in set (0.014 sec)
 
    由于假设认为该SQL语句的执行时间偏长，所以检查slow log，查看该SQL语句是否出现：
    查看/var/lib/mysql/station95-slow.log：

/usr/sbin/mysqld, Version: 9.6.0 (MySQL Community Server - GPL). started with:
Tcp port: 3309  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 9.6.0 (MySQL Community Server - GPL). started with:
Tcp port: 3309  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2026-04-17T15:28:48.938906Z
# User@Host: root[root] @ localhost []  Id: 35594
# Query_time: 0.005840  Lock_time: 0.000003 Rows_sent: 3  Rows_examined: 3
SET timestamp=1776439728;
select co.name, count(ci.id) from city ci join country co on (ci.countrycode=co.code) group by co.name having count(ci.id)>=100 order by count(ci.id);

    发现该SQL语句出现在slow log中，查看执行计划（包括传统格式）：

mysql> explain select co.name, count(ci.id) from city ci join country co on (ci.countrycode=co.code) group by co.name having count(ci.id)>=100 order by count(ci.id) ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: count(ci.id)
    -> Filter: (count(ci.ID) >= 100)
        -> Table scan on
            -> Aggregate using temporary table
                -> Nested loop inner join  (cost=1844 rows=4079)
                    -> Table scan on ci  (cost=416 rows=4079)
                    -> Single-row index lookup on co using PRIMARY (Code = ci.CountryCode)  (cost=0.25 rows=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

mysql> explain format=traditional select co.name, count(ci.id) from city ci join country co on (ci.countrycode=co.code) group by co.name having count(ci.id)>=100 order by count(ci.id) ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | ci    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                   | 4079 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | co    | NULL       | eq_ref | PRIMARY       | PRIMARY | 12      | world_y.ci.CountryCode |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.001 sec)
 
    从执行计划上分析：全表扫描访问city表，PRIMARY索引访问country表，以city表作为驱动表通过嵌套循环连接country表。
    全表扫描访问city表的成本是416（需要访问4079行）。
    PRIMARY索引访问country表的成本是0.25（需要访问1行）。
    嵌套循环的成本是1844-416-0.25=1427.75，总成本1844（需要访问4079行）。在以上分析中，得出结论：两个步骤都访问了4079行，产生了大量的IO。
    根据以上分析，以尽量减少IO的方向来改写以上的SQL语句：
    先做子查询：以CountryCode作为分组依据，将city表进行分组，过滤出超过100计数的CountryCode。然后再将city表与该子查询连接。该子查询代替了country表，显著减少了参与连接的行数。

mysql>  select name , a.citycount from country join  ( select CountryCode, count(id)  citycount   from city group by CountryCode having count(id)>=100 ) a   on (a.countrycode=country.code) order by  a.citycount;
+--------------------+-----------+
| name               | citycount |
+--------------------+-----------+
| Philippines        |       136 |
| Mexico             |       173 |
| Russian Federation |       189 |
| Japan              |       248 |
| Brazil             |       250 |
| United States      |       274 |
| India              |       341 |
| China              |       362 |
+--------------------+-----------+
8 rows in set (0.008 sec)
 
    其响应时间已经控制在0.01秒以内。检查slow log，查看该SQL语句不再出现。查看执行计划（包括传统格式）：

mysql> explain select name , a.citycount from country join  ( select CountryCode, count(id)  citycount   from city group by CountryCode having count(id)>=100 ) a   on (a.countrycode=country.code) order by  a.citycount;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: a.citycount
    -> Stream results  (cost=1044 rows=0)
        -> Nested loop inner join  (cost=1044 rows=0)
            -> Table scan on country  (cost=24.6 rows=238)
            -> Index lookup on a using (CountryCode = country.`Code`)  (cost=0.25..4.29 rows=17.1)
                -> Materialize  (cost=0..0 rows=0)
                    -> Filter: (count(city.ID) >= 100)
                        -> Table scan on
                            -> Aggregate using temporary table
                                -> Table scan on city  (cost=416 rows=4079)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

mysql> explain format=traditional select name , a.citycount from country join  ( select CountryCode, count(id)  citycount   from city group by CountryCode having count(id)>=100 
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref                  | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------------+------+----------+---------------------------------+
|  1 | PRIMARY     | country    | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL                 |  238 |   100.00 | Using temporary; Using filesort |
|  1 | PRIMARY     | | NULL       | ref  |    | | 12      | world_y.country.Code |   17 |   100.00 | NULL                            |
|  2 | DERIVED     | city       | NULL       | ALL  | NULL          | NULL        | NULL    | NULL                 | 4079 |   100.00 | Using temporary                 |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------------+------+----------+---------------------------------+
3 rows in set, 1 warning (0.001 sec)
 
    从执行计划（传统格式）上分析：由于引入了子查询，出现了PRIMARY这种select_type。从执行计划上分析：只有在最初的Table scan on city需要访问4079行，之后的所有访问步骤的行数都很少，因此嵌套循环的成本显著减低。SQL改写后，结果集不变，速度提升1倍。

3. MySQL数据库SQL调优之索引：

    假设生产环境有这么一个查询需求：找出所有名字以T开头的城市：

mysql> use world_y;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select  name from city where name like 'T%';
+--------------------------------+
| name                           |
+--------------------------------+
| Tilburg                        |
| Tirana                         |
| Tébessa                        |
| Tlemcen (Tilimsen)             |
| Tiaret                         |
| Tafuna                         |
| The Valley                     |
| Tres de Febrero                |
......
| Tulsa                          |
| Toledo                         |
| Tampa                          |
| Tacoma                         |
| Tempe                          |
| Tallahassee                    |
| Torrance                       |
| Topeka                         |
| Thousand Oaks                  |
+--------------------------------+
255 rows in set (0.012 sec)
 
    由于假设认为该SQL语句的执行时间偏长，所以检查slow log，查看该SQL语句是否出现：
    查看/var/lib/mysql/station95-slow.log：

/usr/sbin/mysqld, Version: 9.6.0 (MySQL Community Server - GPL). started with:
Tcp port: 3309  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 9.6.0 (MySQL Community Server - GPL). started with:
Tcp port: 3309  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2026-04-17T15:28:48.938906Z
# User@Host: root[root] @ localhost []  Id: 35594
# Query_time: 0.005840  Lock_time: 0.000003 Rows_sent: 3  Rows_examined: 3
SET timestamp=1776439728;
select co.name, count(ci.id) from city ci join country co on (ci.countrycode=co.code) group by co.name having count(ci.id)>=100 order by count(ci.id);
SET timestamp=1776698071;
select  name from city where name like 'T%';

    发现该SQL语句出现在slow log中，查看执行计划（包括传统格式）：

mysql> explain select  name from city where name like 'T%'\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (city.`Name` like 'T%')  (cost=416 rows=453)
    -> Table scan on city  (cost=416 rows=4079)

1 row in set (0.001 sec)

mysql> explain format=traditional select  name from city where name like 'T%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4079
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.001 sec)
 
    从执行计划（传统格式）上分析：type为ALL，说明访问路径是全表扫描。为了加速查询，在city表的name字段上创建索引，以满足查询谓词的过滤条件：

mysql> create index i_city_name on city(name);
Query OK, 0 rows affected (0.088 sec)
Records: 0  Duplicates: 0  Warnings: 0

    再次执行SQL语句：

mysql> use world_y;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select  name from city where name like 'T%';
+--------------------------------+
| name                           |
+--------------------------------+
| Tilburg                        |
| Tirana                         |
| Tébessa                        |
| Tlemcen (Tilimsen)             |
| Tiaret                         |
| Tafuna                         |
| The Valley                     |
| Tres de Febrero                |
......
| Tulsa                          |
| Toledo                         |
| Tampa                          |
| Tacoma                         |
| Tempe                          |
| Tallahassee                    |
| Torrance                       |
| Topeka                         |
| Thousand Oaks                  |
+--------------------------------+
255 rows in set (0.002 sec)
 
    其响应时间已经控制在0.01秒以内。检查slow log，查看该SQL语句不再出现。查看执行计划（包括传统格式）：

mysql> explain select  name from city where name like 'T%'\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (city.`Name` like 'T%')  (cost=56.1 rows=255)
    -> Covering index range scan on city using i_city_name over ('T' <= Name <= 'T????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????')  (cost=56.1 rows=255)

1 row in set, 1 warning (0.001 sec)

mysql>  explain format=traditional select  name from city where name like 'T%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: range
possible_keys: i_city_name
          key: i_city_name
      key_len: 140
          ref: NULL
         rows: 255
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.001 sec)
 
    从执行计划（传统格式）上分析：type为range，说明访问路径是索引范围扫描。速度提升6倍。





路过
雷人
握手
鲜花
鸡蛋
收藏 邀请
上一篇：【博客文章2026】MySQL 9.x数据库管理技巧11：触发器、基于值的审计和事件---与Oracle数据库作对比

相关分类

QQ|手机版|Bo's Oracle Station   

GMT+8, 2026-4-22 09:55 , Processed in 0.055672 second(s), 21 queries .

返回顶部