information_schema.TABLE_STATISTICS

冷不防 2023-08-17 16:23 125阅读 0赞

**version: 5.7.21 Percona Server**

### \#\#\# 本以为是读出来几行 ROWS\_READ 就加几个，没成想不是这样的，请看下面的例子 ###

>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         |        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 |
    +--------------+------------+-----------+--------------+

转载于:https://www.cnblogs.com/moon1223/p/11171305.html

information_schema.TABLE_STATISTICS

发表评论取消回复

还没有评论，来说两句吧...

相关阅读