If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set. When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.
KEY idx_account(accountId,serverid)),然后 ,
mysql> explain select 1 from player where serverid=0 limit 1;
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+
| 1 | SIMPLE | player | index | NULL | idx_account | 780 | NULL | 3 | Using where; Using index |
+----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+还有必要 为serverid 单独建一个索引吗。
但是 上面explain 里, 的key 显示出了idx_account ,不是Null.
有点混乱了。 。
first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done
by using an index, this is very fast. If a filesort must be done, all rows that match the query without
the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are
found. After the initial rows have been found, MySQL does not sort any remainder of the result set.
When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count
unique rows.
从发布文档可以看出 你可以使用distinct limit 1 来确认是否存在limit 1 是肯定有效的,不会全表扫描
而且对于这种Cardinality很差的字段,索引的作用不会很大