DWQA QuestionsCategory: Artificial IntelligenceAsk a question that SQL takes a long time to execute until the database collapses?
Dream journey asked 2 weeks ago

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 desc

By 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?)
image.pngimage.png
There are about 1000W pieces of data in the database
image.png
And the following results are obtained by using the explain keyword
image.png
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

Unique replied 2 weeks ago

After scanning the whole table, try adding an index firstalter 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 having

Unique replied 2 weeks ago

OK, I’ll try

Unique replied 2 weeks ago

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

Unique replied 2 weeks ago

thistoo 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.

Unique replied 2 weeks ago

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

Unique replied 2 weeks ago

You should check the code more

Unique replied 2 weeks ago

OK, thanks. Let me see it again

1 Answers
ponponon answered 2 weeks ago

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