从具有最小/最大字段的MySQL数据库中提取记录的规范方法是什么? [英] What's the canonical way to pull a record from a MySQL database that has a least/greatest field?
问题描述
假设您周日下午坐在家里，并且想从数据库中知道班级排名最低的学生.假设您的数据库表如下所示:
Say you're sitting at home on a Sunday afternoon and you want to know, from your database, the student with the lowest class ranking. Suppose your database table looks like this:
+----------------+-----------+------------+------------+----------------------------+------+-------+
| uid | last_name | first_name | dob | email | rank | grade |
+----------------+-----------+------------+------------+----------------------------+------+-------+
| 13428700000001 | Smith | John | 1990-12-03 | ...@gmail.com | 99 | 4 |
| 13428721960000 | Li | Kai Li | 1979-02-15 | ...@miryclay.com | 12 | 2 |
| 13428722180001 | Zhang | Xi Xiong | 1993-11-09 | ...@163.com | 5 | 5 |
| 13428739950000 | Zhou | Ji Hai | 1991-06-06 | ...@msn.com | 234 | 1 |
| 13428739950001 | Pan | Yao | 1992-05-12 | ...@email.com | 43 | 2 |
| 13428740010001 | Jin | Denny | 1994-06-02 | ...@yahoo.com | 198 | 3 |
| 13428740010002 | Li | Fonzie | 1991-02-02 | ...@hotmail.com | 75 | 3 |
| 13428743370000 | Ma | Haggar | 1991-08-16 | ...@haggars.com | 47 | 4 |
| 13428743590001 | Ren | Jenny | 1990-03-29 | ...@email.com | 5 | 2 |
| 13428774040000 | Chen | Dragon | 1999-04-12 | ...@aol.com | 23 | 5 |
| 13428774260001 | Wang | Doctor | 1996-09-30 | ...@yahoo.com | 1 | 5 |
| 13430100000000 | Chanz | Heyvery | 1994-04-04 | ...@gmail.com | 107 | 2 |
+----------------+-----------+------------+------------+----------------------------+------+-------+
我可以通过SELECT * FROM students WHERE rank = (SELECT MAX(rank) FROM students);执行此操作，但这是最简单，最常见的方法吗?
I can do this via a SELECT * FROM students WHERE rank = (SELECT MAX(rank) FROM students); but is this the easiest, most common way to do it?
推荐答案
这种方式也很常见:
SELECT s1.*
FROM students s1
LEFT JOIN students s2 ON s1.rank < s2.rank
WHERE s2.uid IS NULL;
LEFT JOIN的工作原理是，当s1.rank达到最大值时，不存在更大的s2.rank，并且s2行的值将为NULL.
The LEFT JOIN works on the basis that when s1.rank is at its maximum value, there is no s2.rank with a greater value and the s2 rows values will be NULL.
但是我要说的是，您的操作方式是最常见，最容易理解的方式，是的.
But I'd say that your way of doing it is the most common, easiest to understand way of doing it, yes.
关于为什么有时有时变慢的问题:
On the question why it is slower sometimes:
此查询的性能取决于编写的谨慎程度".以您的数据为例:
The performance of this query depends on "how careful it is written". Taken your data as example:
drop table if exists students;
CREATE TABLE students
(`uid` bigint, `last_name` varchar(5), `first_name` varchar(8), `dob` varchar(10), `email` varchar(16), `rank` int, `grade` int)
;
INSERT INTO students
(`uid`, `last_name`, `first_name`, `dob`, `email`, `rank`, `grade`)
VALUES
(13428700000001, 'Smith', 'John', '1990-12-03', '...@gmail.com', 99, 4),
(13428721960000, 'Li', 'Kai Li', '1979-02-15', '...@miryclay.com', 12, 2),
(13428722180001, 'Zhang', 'Xi Xiong', '1993-11-09', '...@163.com', 5, 5),
(13428739950000, 'Zhou', 'Ji Hai', '1991-06-06', '...@msn.com', 234, 1),
(13428739950001, 'Pan', 'Yao', '1992-05-12', '...@email.com', 43, 2),
(13428740010001, 'Jin', 'Denny', '1994-06-02', '...@yahoo.com', 198, 3),
(13428740010002, 'Li', 'Fonzie', '1991-02-02', '...@hotmail.com', 75, 3),
(13428743370000, 'Ma', 'Haggar', '1991-08-16', '...@haggars.com', 47, 4),
(13428743590001, 'Ren', 'Jenny', '1990-03-29', '...@email.com', 5, 2),
(13428774040000, 'Chen', 'Dragon', '1999-04-12', '...@aol.com', 23, 5),
(13428774260001, 'Wang', 'Doctor', '1996-09-30', '...@yahoo.com', 1, 5),
(13430100000000, 'Chanz', 'Heyvery', '1994-04-04', '...@gmail.com', 107, 2)
;
查询说明如下:
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
-------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | students | ALL | (null) | (null) | (null) | (null) | 12 | Using where |
| 2 | SUBQUERY | students | ALL | (null) | (null) | (null) | (null) | 12 | |
我的查询中这样的人:
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | s1 | ALL | (null) | (null) | (null) | (null) | 12 | |
| 1 | SIMPLE | s2 | ALL | (null) | (null) | (null) | (null) | 12 | Using where |
几乎相同.这两个查询都不使用索引，而是扫描所有行.现在，我们在列rank上添加索引.
Almost the same. Neither query uses an index, all rows are scanned. Now we're adding an index on column rank.
drop table if exists students;
CREATE TABLE students
(`uid` bigint, `last_name` varchar(5), `first_name` varchar(8), `dob` varchar(10), `email` varchar(16), `rank` int, `grade` int
, key rankkey(rank)
)
;
查询中的解释:
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | students | ref | rankkey | rankkey | 5 | const | 1 | Using where |
| 2 | SUBQUERY | (null) | (null) | (null) | (null) | (null) | (null) | (null) | Select tables optimized away |
与我的:
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | s1 | ALL | (null) | (null) | (null) | (null) | 12 | |
| 1 | SIMPLE | s2 | ALL | rankkey | (null) | (null) | (null) | 12 | Using where |
您的查询使用索引，而我的则不使用.
Your query uses the index, mine doesn't.
现在，我们要在表中添加一个主键.
Now we're adding a primary key to the table.
drop table if exists students;
CREATE TABLE students
(`uid` bigint, `last_name` varchar(5), `first_name` varchar(8), `dob` varchar(10), `email` varchar(16), `rank` int, `grade` int
, key rankkey(rank)
, primary key(uid)
);
根据您的查询进行解释:
Explain from your query:
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | students | ref | rankkey | rankkey | 5 | const | 1 | Using where |
| 2 | SUBQUERY | (null) | (null) | (null) | (null) | (null) | (null) | (null) | Select tables optimized away |
和我的
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
-------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | s1 | ALL | (null) | (null) | (null) | (null) | 12 | |
| 1 | SIMPLE | s2 | index | rankkey | rankkey | 5 | (null) | 12 | Using where; Using index; Not exists |
这样，它们很可能同样快.这就是查询和表通常的构建方式.每个表都应该有一个主键，如果您经常对rank列运行查询过滤，那么您当然应该在其上有一个索引.因此几乎没有区别.现在，这取决于表中有多少行(如果它是唯一索引和/或聚集索引).但这将导致现在有点太远了.但请注意，在此示例中，检查的行数有所不同.对于小数据来说，没有什么区别，对于大数据量来说，肯定是有区别的.但是(！)对于两个查询，此行为可能都会改变，具体取决于索引.
This way they are most likely equally fast. And this is how the query and the table is usually built. Every table should have a primary key and if you're running a query filtering on the rank column very often you should of course have an index on it. So there's almost no difference. It all depends now on how much rows you have in your table, if it's a unique index and/or a clustered one. But that would lead now a bit too far. But note, that in this example there's a difference in how much rows are examined. With small data there's no difference, with large data volumes there sure is. But(!) this behaviour might change for both queries, depending on the index.
如果编写查询的人犯了错误怎么办?如果他这样写的话:
What if the one who writes the query makes a mistake? What if he writes it like this:
SELECT s1.*
FROM students s1
LEFT JOIN students s2 ON s1.rank < s2.rank
WHERE s2.last_name IS NULL;
查询仍然有效并且有效，但是
The query still works and is valid, but
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | s1 | ALL | (null) | (null) | (null) | (null) | 12 | |
| 1 | SIMPLE | s2 | ALL | rankkey | (null) | (null) | (null) | 12 | Using where |
再次
不使用索引.
again the index is not used.
如果我们再次删除主键并按如下方式编写查询，该怎么办:
What if we remove the primary key again and write the query like this:
SELECT s1.*
FROM students s1
LEFT JOIN students s2 ON s1.rank < s2.rank
WHERE s2.rank IS NULL;
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
-------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | s1 | ALL | (null) | (null) | (null) | (null) | 12 | |
| 1 | SIMPLE | s2 | index | rankkey | rankkey | 5 | (null) | 12 | Using where; Using index |
再次使用索引.
结论:如果正确执行，两个查询的运行速度应相同. 只要索引在等级列上，您的速度就很快.如果考虑到索引，同样适用于我的.
Conclusion: Both queries should run equally fast, if done right. Yours is fast as long as an index is on rank column. Same applies for mine if written with indexes in mind.
希望这会有所帮助.
这篇关于从具有最小/最大字段的MySQL数据库中提取记录的规范方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
