mysql - 一个sql的问题

Question

stat表 字段 uid，act，time
我想取出每个uid最近的一个act
我现在的sql:

select * from (select * from stat order by uid,time desc) a group by uid

这个表数据量比较大，查起来有点慢，有没有更好的办法

甚至这个：

select act,count(*) num from (select * from (select * from stat order by uid,time desc) a group by uid) b group by act

表：

CREATE TABLE `stat` (
  `uid` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `act` bigint(20) NOT NULL,
  `time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`uid`,`act`),
  KEY `index_time` (`time`) USING BTREE,
  KEY `index_act` (`act`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

Let me tell you my opinion first. If there are any mistakes, please correct me.
First of all, I think the SQL statement of the questioner is wrong. It seems that the result is correct because it only relies on the implementation mechanism of the database to execute group by. For example, if you change the sort order, the result will be wrong.
After using group by, the fields that the subject can query are either in the aggregate function or the fields of group by. Writing like 'select * from a group by uid' is not standardized because the database is in accordance with After the uid is grouped, a set of act and time field values ​​will be randomly selected. The question's SQL seems to be working normally. It should be that after sorting, the database selects the highest sorted one by default.
I think the correct SQL should be like this:

select stat.* from stat ,(select uid,max(time) as time from stat group by uid) tmp where stat.uid=tmp.uid  and stat.time=tmp.time;

First group and query the latest execution time of each person, and then obtain the complete operation information based on uid and time.
If the subject's database has indexed uid, the query efficiency of this SQL should still be acceptable.
A little personal opinion.

I made a few modifications on the original basis, and the local test performance has been slightly improved. My local test data is 30,000 sets. You can try it:

select stat.* from stat ,(select uid,max(time) as time from stat group by uid order by null) tmp where  stat.time=tmp.time and stat.uid=tmp.uid;

Answer 2

Try to create indexes on the columns of the query conditions, B number index, B+ number index, etc.

You can search on Baidu for details on how to create it

Answer 3

Why does this statement need to nest a subquery? Isn’t it possible to use one statement? The logic is not very clear,,, but under normal circumstances I use explain to print the query information

Answer 4

One group by is enough why write two layers