information_schema.TABLE_STATISTICS
version: 5.7.21 Percona Server
>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 就加几个，没成想不是这样的，请看下面的例子
>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 | +--------------+------------+-----------+--------------+
纸上得来终觉浅 绝知此事要躬行
