今天看了阿里云的一篇文章,提到了关于分页的问题,之前我也没有注意到过。
mysql> create table baixyu(id int primary key auto_increment,name varchar(10),age int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into baixyu(name,age) select table_name,table_rows from information_schema.tables;
Query OK, 143 rows affected, 107 warnings (0.56 sec)
mysql> select * from baixyu order by age desc limit 0,10;
+-----+------------+-------+
| id | name | age |
+-----+------------+-------+
| 100 | events_sta | 10000 |
| 109 | events_wai | 10000 |
| 92 | events_sta | 10000 |
| 67 | help_relat | 1215 |
| 90 | events_sta | 1000 |
| 93 | events_sta | 1000 |
| 88 | accounts | 1000 |
| 95 | events_sta | 1000 |
| 89 | cond_insta | 1000 |
| 91 | events_sta | 1000 |
+-----+------------+-------+
10 rows in set (0.00 sec)
mysql> select * from baixyu order by age desc limit 10,10;
+-----+------------+------+
| id | name | age |
+-----+------------+------+
| 96 | events_sta | 1000 |
| 93 | events_sta | 1000 |
| 101 | events_sta | 1000 |
| 94 | events_sta | 1000 |
| 88 | accounts | 1000 |
| 104 | events_sta | 1000 |
| 95 | events_sta | 1000 |
| 90 | events_sta | 1000 |
| 102 | events_sta | 1000 |
| 103 | events_sta | 1000 |
+-----+------------+------+
10 rows in set (0.00 sec)
看到记录95在第一页第二页都出现了
create index idx_age on baixyu(age);
mysql> select * from baixyu force index(idx_age) order by age desc limit 0,10;
+-----+------------+-------+
| id | name | age |
+-----+------------+-------+
| 109 | events_wai | 10000 |
| 100 | events_sta | 10000 |
| 92 | events_sta | 10000 |
| 67 | help_relat | 1215 |
| 139 | users | 1000 |
| 138 | threads | 1000 |
| 137 | table_lock | 1000 |
| 136 | table_io_w | 1000 |
| 135 | table_io_w | 1000 |
| 134 | socket_sum | 1000 |
+-----+------------+-------+
10 rows in set (0.00 sec)
mysql> select * from baixyu force index(idx_age) order by age desc limit 10,10;
+-----+------------+------+
| id | name | age |
+-----+------------+------+
| 133 | socket_sum | 1000 |
| 132 | socket_ins | 1000 |
| 129 | setup_inst | 1000 |
| 126 | session_co | 1000 |
| 125 | session_ac | 1000 |
| 124 | rwlock_ins | 1000 |
| 122 | objects_su | 1000 |
| 121 | mutex_inst | 1000 |
| 120 | hosts | 1000 |
| 119 | host_cache | 1000 |
+-----+------------+------+
10 rows in set (0.00 sec)
`
``
oracle的rownum问题
create table baixyu as select object_id,object_name,data_object_id from dba_objects;
“`