MySQL 服务器状态变量角度看max、min优化

转载
MySQL
-
- 字
2024-04-11

转载至:  https://www.modb.pro/db/397684 

实验※

有一张测试表user_test，表结构如下：

mysql> desc user_test;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | bigint(20)  | NO   | PRI | NULL              | auto_increment              |
| login_name  | varchar(30) | NO   |     | NULL              |                             |
| email       | varchar(50) | YES  |     |                   |                             |
| phonenumber | varchar(11) | YES  |     |                   |                             |
| sex         | char(1)     | YES  |     | 0                 |                             |
| password    | varchar(50) | YES  |     |                   |                             |
| create_time | datetime    | YES  | MUL | CURRENT_TIMESTAMP |                             |
| update_time | datetime    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+


 

表中索引如下


mysql> show index from user_test;
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table     | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| user_test |          0 | PRIMARY         |            1 | id          | A         |      992906 |     NULL | NULL   |      | BTREE      |         |               | YES     |
| user_test |          1 | idx_create_time |            1 | create_time | A         |      992906 |     NULL | NULL   | YES  | BTREE      |         |               | YES     |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.00 sec)


 

表中数据量100w。

mysql> select count(*) from user_test;;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.17 sec)


 

接下来我从Innodb_rows_read这个MySQL服务器维护的状态变量来比较下如下两个语句的资源消耗。

sql1:

select max(id) as max_id from user_test  where create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), "%Y-%m-%d") and sex=0;


 

sql2:

select id as max_id from user_test  where create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), "%Y-%m-%d") and sex=0 order by id desc limit 1;


 

我们知道sql1和sql2这两个sql的作用是一样的，取满足where条件的max id。注意取的是max id，不是max(create_time)，而且还有附加条件status=0。

来分别看下这两个sql所带来的Innodb_rows_read增量。

sql1:

mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)

mysql> select max(id) as max_id from user_test  where create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), "%Y-%m-%d") and sex=0;
+--------+
| max_id |
+--------+
| 500000 |
+--------+
1 row in set (0.38 sec)

mysql> select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)

mysql> select @b-@a;
+---------+
| @b-@a   |
+---------+
| 1000293 |
+---------+
1 row in set (0.00 sec)


 

sql2:

mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select id as max_id from user_test  where create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), "%Y-%m-%d") and sex=0 order by id limit 1;
+--------+
| max_id |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)

mysql> select id as max_id from user_test  where create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), "%Y-%m-%d") and sex=0 order by id desc limit 1;
+--------+
| max_id |
+--------+
| 500000 |
+--------+
1 row in set (0.20 sec)

mysql> select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
Query OK, 1 row affected (0.00 sec)

mysql> select @b-@a;
+--------+
| @b-@a  |
+--------+
| 500265 |
+--------+
1 row in set (0.00 sec)


 

可以看到使用了“max(id)“的sql1的Innodb_rows_read的增量为1000293，差不多是user_test表的大小，所以我们可以说max函数会造成全表扫描。使用了“order by id desc limit 1”的sql2，它的Innodb_rows_read的增量为500265，基本上是“create_time < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY), "%Y-%m-%d")”所过滤的行数，即索引扫描行数。sql2相比sql1，性能消耗要低得多。当表中数据很大时，这个差值将更明显。

 

 

  • Innodb_rows_read

    The number of rows read from InnoDB tables.  

    它是一种累加的算法，sql每扫描一行，该值就累加1。

  • Innodb_data_reads

    The total number of data reads (OS file reads).   

    数据读取总数（操作系统文件读取）

  • Innodb_pages_read

    The number of pages read from the InnoDB buffer pool by operations on InnoDB tables.  

    读取的页数

  • Innodb_buffer_pool_reads

    The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.  

    是一个计数器，它记录了InnoDB在缓冲池中找不到请求的数据或索引时，需要从磁盘读取的次数。这是一个非常重要的性能指标，因为它直接反映了缓存的命中率。如果这个值很高，那就意味着InnoDB的缓存命中率很低，大量的磁盘I/O操作正在发生，这可能会严重影响数据库的性能。

  • Innodb_buffer_pool_read_requests

    The number of logical read requests.

    也是一个计数器，它记录了InnoDB从缓冲池中读取数据或索引的请求次数。这个值通常会比innodb_buffer_pool_reads高很多，因为它包括了所有从缓冲池中的读取操作，无论是否命中了缓存。

Optimizer Trace 是MySQL 5.6.3里新加的一个特性，可以把MySQL Optimizer的决策和执行过程输出成文本，结果为JSON格式，兼顾了程序分析和阅读的便利。
1.performance_schema.session_status，保存当前会话的状态变量，可以利用它来统计innodb读取行数。
2.可以利用performance_schema库里面的optimizer_trace来查看语句执行的详细信息。语句：select trace from information_schema
.optimizer_trace;

结论※

针对需要使用max或者min函数的场景，可以考虑替换成order by id desc/acs limit 1。

目录