好的程序代码本身就是最好的文档@《代码大全》Steve McConnell

information_schema.TABLE_STATISTICS

MySQL 2019-07-11 浏览量: 113 字数统计: 204 最后更新: 2019-07-11 16:53

文章目录[显示]

version: Percona 5.7.21

>CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

>select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       118 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

>select * from t1 limit 1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
+----+------+
1 row in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       119 |           17 |
+--------------+------------+-----------+--------------+

本以为是读出来几行ROWS_READ就加几个,没成想不是这样的,请看下面的例子

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        87 |           16 |
+--------------+------------+-----------+--------------+

>explain select * from t1 order by a limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

>select * from t1 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        91 |           16 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

order by 没走索引,最后走的filesort, 表里面有4条数据,最后ROWS_READ 是加的4。(应该是order by 了几条,加的就是几个),可以对比一下下面的例子

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        95 |           16 |
+--------------+------------+-----------+--------------+

>explain select * from t1 where id >2 order by a limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

>select * from t1 where id >2 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.00 sec)

# 可以看到ROWS_READ 加的是2,因为已经通过索引把数据过滤剩两个了。
>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        97 |           16 |
+--------------+------------+-----------+--------------+

假如where没有用上索引,再order by 的话还是4个

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        91 |           16 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

>select * from t1 where a<10 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0.00 sec)

>explain select * from t1 where a<10 order by a limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        95 |           16 |
+--------------+------------+-----------+--------------+

但是如果条件里面没有order 并且没有filesout,limit 几个就加几个

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        97 |           16 |
+--------------+------------+-----------+--------------+

>explain select * from t1 limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

> select * from t1 limit 1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
+----+------+
1 row in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |        98 |           16 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

但是 where条件里面有主键和非索引,order by非索引的话 也是limit 几就是几

# 先插入一行数据
>insert into t1 values(5,3);
Query OK, 1 row affected (0.01 sec)

>select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |   12 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    3 |
+----+------+
5 rows in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       104 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

>explain select * from t1 where id >2 and a=3 order by a limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |    20.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

>select * from t1 where id >2 and a=3 order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.00 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       105 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

下面这个我没想明白是为什么

a>3 已经过滤剩两行了,为啥还是加3 呢???
难道是有filesort 的话就是扫描多少行ROWS_READ就加几吗???

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       115 |           17 |
+--------------+------------+-----------+--------------+
1 row in set (0.00 sec)

>explain select * from t1 where id >2 and a>3  order by a limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

>select * from t1 where id >2 and a>3  order by a limit 1;
+----+------+
| id | a    |
+----+------+
|  4 |    4 |
+----+------+
1 row in set (0.01 sec)

>select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
+--------------+------------+-----------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
+--------------+------------+-----------+--------------+
| sbtest       | t1         |       118 |           17 |
+--------------+------------+-----------+--------------+
小蜗牛 说:
Freedom is the source from which all meaning and all values spring .


文章版权归 原文作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权| 转载必须包含本声明,并以超链接形式注明原文作者和本文原始地址: https://www.tougetu.com/2019/07/TABLE_STATISTICS.html

2 条评论

  1. 土豆泥

    村里通网啦

    1. 小蜗牛

      快帮忙分析一下呀

添加新评论

代码 Pastebin Gist 加粗 删除线 斜体 链接 签到