MySQL 查询如何高效地选择前 n 行?
mysqlmysqli database
使用索引高效地选择前 n 行。我们首先创建一个表 −
mysql> create table DemoTable (StudentName varchar(100), StudentScore int ); Query OK, 0 rows affected (0.66 sec)
示例
使用 insert 命令在表中插入一些记录 −
mysql> insert into DemoTable values('John',34);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('Carol',55);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Bob',58);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Sam',38);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Mike',48);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Adam',41);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Chris',47);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('Robert',40);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('David',89);
Query OK, 1 row affected (0.18 sec)
使用 select 语句显示表中的所有记录 −
mysql> select *from DemoTable;
输出
+-------------+--------------+ | StudentName | StudentScore | +-------------+--------------+ | John | 34 | | Carol | 55 | | Bob | 58 | | Sam | 38 | | Mike | 48 | | Adam | 41 | | Chris | 47 | | Robert | 40 | | David | 89 | +-------------+--------------+ 9 rows in set (0.00 sec)
示例
以下是高效选择前 n 行的查询。我们使用了 ORDER BY 并跳过了 5 行。跳过后，可以看到 3 条记录，因为我们使用了 LIMIT 3−
mysql> alter table DemoTable ADD INDEX name_score(StudentName,StudentScore); Query OK, 0 rows affected (0.61 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select StudentName,StudentScore from DemoTable order by StudentScore LIMIT 5,3;
输出
+-------------+--------------+ | StudentName | StudentScore | +-------------+--------------+ | Mike | 48 | | Carol | 55 | | Bob | 58 | +-------------+--------------+ 3 rows in set (0.00 sec)
