Problem background: This is an SQL of query, reply, etc. which is relatively poor in NSV (a measure of user satisfaction. The smaller the value is, the more dissatisfied the user is). However, it is found that the database hangs up after a while, reporting an error of too many connections. Use the show full processlist command to check the connection of SQL. The results are very strange
select max(id), max(date), type, sum(like_pv), sum(trample_pv), (sum(like_pv) - sum(trample_pv))/(sum(like_pv) + sum(trample_pv)) as nsv,query, diff, '' as subtype, sum(total_pv) as total_pv, sum(total_uv),max(reply) as reply from domain_like_dislike where date >= 20220320 and date <= 20220327 and diff ='TOP_QUERY' group by query, type having nsv < 0.3 and type like 'VOICE%' order by total_pv descBy default, MySQL’s Max_ Connections is 151, which I haven’t modified, but when executing this SQL, execute show full processlist to see that the number of SQL connections is increasing, and there are many SQL completely unrelated to the above SQL, and the number of connections soon reaches 151, resulting in the error of too many connections
As shown in the figure below, the data in the show full processlist info column is executed (many SQL statements have nothing to do with the SQL actually executed above, but why are they executed at this time?)
There are about 1000W pieces of data in the database
And the following results are obtained by using the explain keyword
Ask your predecessors if there is any way to check and solve this problem?,To confirm whether there is really a connection, it is easy to runmysqlInstall one on your machinetsharkJust grab the bag, because you don’t have to movemysql。If you don’t want to installtshark, turn it onmysqlofgeneral_log, which needs to be modifiedmysql serverConfigure and restartmysql serverreference resources:Tshark capture MySQL protocol package
To confirm whether there is really a connection, it is easy to runmysqlInstall one on your machinetsharkJust grab the bag, because you don’t have to movemysql。If you don’t want to installtshark, turn it onmysqlofgeneral_log, which needs to be modifiedmysql serverConfigure and restartmysql serverreference resources:Tshark capture MySQL protocol package
After scanning the whole table, try adding an index first
alter table domain_like_dislike add index idx_diff_type_date_query(diff,type,date,`query`);Then explain.In addition, your type field is not aggregated. You can put it in where instead of havingOK, I’ll try
After adding the index, the explain keyword does use the index, but an error is still reported after a while. Too many connections, run show full processlist and see that the number of connections has exceeded 151
this
too many connectionsIt certainly doesn’t have much to do with here. It depends on which places in your code are connecting to the database and why. In addition, you can see the execution efficiency after indexing.But I’m surprised that I’m querying in Navicat now, and the table is domain_ like_ Dislike, it has nothing to do with other tables user and role. When executing this SQL, there is an SQL such as select count (*) from users where 1 = 1, which is also connecting to the database? I’m confused
You should check the code more
OK, thanks. Let me see it again