information_schema.TABLE_STATISTICS

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

version: 5.7.21 Percona Server

  1. >CREATE TABLE `t1` (
  2.   `id` int(11) NOT NULL,
  3.   `a` int(11) DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  7. >select * from t1;
  8. +----+------+
  9. | id | a    |
  10. +----+------+
  11. |  1 |   12 |
  12. |  2 |    2 |
  13. |  3 |    3 |
  14. |  4 |    4 |
  15. +----+------+
  17. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  18. +--------------+------------+-----------+--------------+
  19. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  20. +--------------+------------+-----------+--------------+
  21. | sbtest       | t1         |       118 |           17 |
  22. +--------------+------------+-----------+--------------+
  23. 1 row in set (0.00 sec)
  25. >select * from t1 limit 1;
  26. +----+------+
  27. | id | a    |
  28. +----+------+
  29. |  1 |   12 |
  30. +----+------+
  31. 1 row in set (0.00 sec)
  33. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  34. +--------------+------------+-----------+--------------+
  35. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  36. +--------------+------------+-----------+--------------+
  37. | sbtest       | t1         |       119 |           17 |

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

  1. >CREATE TABLE `t1` (
  2.   `id` int(11) NOT NULL,
  3.   `a` int(11) DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  7. >select * from t1;
  8. +----+------+
  9. | id | a    |
  10. +----+------+
  11. |  1 |   12 |
  12. |  2 |    2 |
  13. |  3 |    3 |
  14. |  4 |    4 |
  15. +----+------+
  17. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  18. +--------------+------------+-----------+--------------+
  19. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  20. +--------------+------------+-----------+--------------+
  21. | sbtest       | t1         |        87 |           16 |
  22. +--------------+------------+-----------+--------------+
  24. >explain select * from t1 order by a limit 1;
  25. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  26. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
  27. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  28. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
  29. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  30. 1 row in set, 1 warning (0.00 sec)
  32. >select * from t1 order by a limit 1;
  33. +----+------+
  34. | id | a    |
  35. +----+------+
  36. |  2 |    2 |
  37. +----+------+
  38. 1 row in set (0.00 sec)
  40. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  41. +--------------+------------+-----------+--------------+
  42. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  43. +--------------+------------+-----------+--------------+
  44. | sbtest       | t1         |        91 |           16 |
  45. +--------------+------------+-----------+--------------+
  46. 1 row in set (0.00 sec)

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

  1. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  2. +--------------+------------+-----------+--------------+
  3. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  4. +--------------+------------+-----------+--------------+
  5. | sbtest       | t1         |        95 |           16 |
  6. +--------------+------------+-----------+--------------+
  8. >explain select * from t1 where id >2 order by a limit 1;
  9. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  10. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
  11. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  12. |  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using filesort |
  13. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  14. 1 row in set, 1 warning (0.00 sec)
  16. >select * from t1 where id >2 order by a limit 1;
  17. +----+------+
  18. | id | a    |
  19. +----+------+
  20. |  3 |    3 |
  21. +----+------+
  22. 1 row in set (0.00 sec)
  24. # 可以看到ROWS_READ 加的是2，因为已经通过索引把数据过滤剩两个了。
  25. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  26. +--------------+------------+-----------+--------------+
  27. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  28. +--------------+------------+-----------+--------------+
  29. | sbtest       | t1         |        97 |           16 |
  30. +--------------+------------+-----------+--------------+

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

  1. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  2. +--------------+------------+-----------+--------------+
  3. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  4. +--------------+------------+-----------+--------------+
  5. | sbtest       | t1         |        91 |           16 |
  6. +--------------+------------+-----------+--------------+
  7. 1 row in set (0.00 sec)
  9. >select * from t1 where a<10 order by a limit 1;
  10. +----+------+
  11. | id | a    |
  12. +----+------+
  13. |  2 |    2 |
  14. +----+------+
  15. 1 row in set (0.00 sec)
  17. >explain select * from t1 where a<10 order by a limit 1;
  18. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
  19. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
  20. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
  21. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where; Using filesort |
  22. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
  24. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  25. +--------------+------------+-----------+--------------+
  26. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  27. +--------------+------------+-----------+--------------+
  28. | sbtest       | t1         |        95 |           16 |
  29. +--------------+------------+-----------+--------------+

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

  1. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  2. +--------------+------------+-----------+--------------+
  3. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  4. +--------------+------------+-----------+--------------+
  5. | sbtest       | t1         |        97 |           16 |
  6. +--------------+------------+-----------+--------------+
  8. >explain select * from t1 limit 1;
  9. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  10. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
  11. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  12. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
  13. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  14. 1 row in set, 1 warning (0.00 sec)
  16. > select * from t1 limit 1;
  17. +----+------+
  18. | id | a    |
  19. +----+------+
  20. |  1 |   12 |
  21. +----+------+
  22. 1 row in set (0.00 sec)
  24. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  25. +--------------+------------+-----------+--------------+
  26. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  27. +--------------+------------+-----------+--------------+
  28. | sbtest       | t1         |        98 |           16 |
  29. +--------------+------------+-----------+--------------+
  30. 1 row in set (0.00 sec)

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

  1. # 先插入一行数据
  2. >insert into t1 values(5,3);
  3. Query OK, 1 row affected (0.01 sec)
  5. >select * from t1;
  6. +----+------+
  7. | id | a    |
  8. +----+------+
  9. |  1 |   12 |
  10. |  2 |    2 |
  11. |  3 |    3 |
  12. |  4 |    4 |
  13. |  5 |    3 |
  14. +----+------+
  15. 5 rows in set (0.00 sec)
  17. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  18. +--------------+------------+-----------+--------------+
  19. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  20. +--------------+------------+-----------+--------------+
  21. | sbtest       | t1         |       104 |           17 |
  22. +--------------+------------+-----------+--------------+
  23. 1 row in set (0.00 sec)
  25. >explain select * from t1 where id >2 and a=3 order by a limit 1;
  26. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  27. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
  28. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  29. |  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |    20.00 | Using where |
  30. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  31. 1 row in set, 1 warning (0.00 sec)
  33. >select * from t1 where id >2 and a=3 order by a limit 1;
  34. +----+------+
  35. | id | a    |
  36. +----+------+
  37. |  3 |    3 |
  38. +----+------+
  39. 1 row in set (0.00 sec)
  41. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  42. +--------------+------------+-----------+--------------+
  43. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  44. +--------------+------------+-----------+--------------+
  45. | sbtest       | t1         |       105 |           17 |
  46. +--------------+------------+-----------+--------------+
  47. 1 row in set (0.00 sec)

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

a>3 已经过滤剩两行了，为啥还是加3 呢？？？

难道是有filesort 的话就是扫描多少行ROWS_READ就加几吗？？？

  1. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  2. +--------------+------------+-----------+--------------+
  3. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  4. +--------------+------------+-----------+--------------+
  5. | sbtest       | t1         |       115 |           17 |
  6. +--------------+------------+-----------+--------------+
  7. 1 row in set (0.00 sec)
  9. >explain select * from t1 where id >2 and a>3  order by a limit 1;
  10. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  11. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
  12. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  13. |  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |    33.33 | Using where; Using filesort |
  14. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
  15. 1 row in set, 1 warning (0.01 sec)
  17. >select * from t1 where id >2 and a>3  order by a limit 1;
  18. +----+------+
  19. | id | a    |
  20. +----+------+
  21. |  4 |    4 |
  22. +----+------+
  23. 1 row in set (0.01 sec)
  25. >select TABLE_SCHEMA,TABLE_NAME,ROWS_READ,ROWS_CHANGED from information_schema.TABLE_STATISTICS where TABLE_NAME="t1";
  26. +--------------+------------+-----------+--------------+
  27. | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED |
  28. +--------------+------------+-----------+--------------+
  29. | sbtest       | t1         |       118 |           17 |
  30. +--------------+------------+-----------+--------------+

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

发表评论

表情：
评论列表 （有 0 条评论，194人围观）

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

相关阅读