Shulou(Shulou.com)05/31 Report--

This article is to share with you the content of an example analysis of mysql big data query optimization. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Mysql small amount of data, unnecessary optimization, large amount of data, optimization is necessary, do not optimize a query for 10 seconds, optimize properly, the same query for 10 milliseconds.

What a painful understanding it is!

Mysql optimization, in the words of programmers, is index optimization and where condition optimization.

Experimental environment: MacBook Pro MJLQ2CH/A,mysql5.7, data volume: 2.12 million +

ONE:

Select * from article INNER JOIN (SELECT id FROM article WHERE length (content_url) > 0 and (select status from source where id = article.source_id) = 1 and (select status from category where id = article.category_id) = 1 and status = 1 and id

< 2164931 order by stick desc,pub_time desc limit 240,15 ) AS tUSING(id); 咋一看，大佬肯定会想杀了我，没事做啥自关联，还是inner join。XX楼的，把我的杀猪刀拿来，我要宰了博主！！！ 说实话，早上出门我的脑袋没被门挤，我也不想这样的。 1.数据量大了，你要做offset很大的分页查询，还真的这样提速，原因 --->

Overwrite the whole table with the id in the join child table to avoid full table scanning.

Look at my order by (whisper: it's just an order by,TM who can't write), you replace this order by with the field desc or explain in your own table. Extra-> filesort! Shit!

two。 For this kind of order by with multiple conditions, we usually add index to the two fields directly, but we still add Extra-- > filesort. In another way, add a joint index to all the conditions that follow the order by, and be sure to follow your order by order. This leaves only where for Extra.

Then look at where, (select status from source where id = article.source_id) = 1 and. How to write JB!

3. I thought about using the join+index method and finally tested it out, which is almost the same as this way. The production environment is written in this way, then this is it, there can be two less indexes (source_id,category_id), no one can stop the lazy disease, and then come back to optimize it after suffering losses.

4. I arrived at this point only last night when I get. The order in which the where condition is satisfied is to meet the last condition first. From right to left, after deleting the index test, it really works. It can be reduced from 6 seconds to 4 seconds. After optimizing the index, we found that the time-consuming effect of the sequence is almost negligible, 0.x milliseconds.

TWO:

Select * from article INNER JOIN (SELECT id FROM article WHERE INSTR (ifnull (title,''), 'Wolf Warriors') > 0 and status! = 9 order by pub_time desc limit 1005,10) AS t USING (id)

Um-- it's inner join again.

INSTR (ifnull (title,''), 'Wolf Warriors') > 0, why not use like.

1. Considering that this is the search of the management platform, there is no search on the search engine, the search engine synchronizes the data once an hour, and the data is incomplete. The manager only cares about the results he wants when searching. Like% XX% cannot go to the index, which is 5 times less efficient than instr, and he tested regexp'. * XX*.', is still a little more time-consuming than instr.

Desc or explain, look, filesort. Add an index to pub_time, or filesort.

two。 There is another scenario in this case, SELECT id FROM article force index (pub_time), which specifies that this index is used. But this way of writing is too inflexible, OUT! Baidu, there are experts to guide the puzzle: build a joint index of status and pub_time (the condition of pub_time_status,order is in front), let where query, put this index on force automatically.

THREE:

Select * from article where status! = 9 order by pub_time desc limit 1000000, 25 order by pub_time desc limit Desc or explain, or filesort. I thought we built a joint index for status and pub_time, tell me why.

Well, I don't know, build a joint index status_pub_time between status and pub_time, this time the where condition comes first, explain doesn't have filesort, but this index is not used, it hooks up pub_time_status. I don't understand.

At the same time, I explain the SQL of TWO, all of which are as follows:

You can't delete either of the two. If you delete one, sql will filesort!

FOUR:

SELECT * from follow where (SELECT status FROM source WHERE id=follow.source_id) = 1 and follow.type=1) or ((select status from topic WHERE id=follow.source_id) = 1 and follow.type=2)) AND user_id=10054 ORDER BY sort limit 15 as t using 15; SELECT * from follow inner join (SELECT id from follow where (SELECT status FROM source WHERE id=follow.source_id) = 1 and follow.type=1) or ((select status from topic WHERE id=follow.source_id) = 1 and follow.type=2)) AND user_id=10054 ORDER BY sort limit 15 (id) (SELECT id, source_id, user_id, temporary, sort, follow_time, read_time,type from follow where (SELECT status FROM source WHERE id=follow.source_id) = 1 and follow.type=1 and user_id=10054) union all (SELECT id, source_id, user_id, temporary, sort, follow_time, read_time,type from follow where (select status from topic WHERE id=follow.source_id) = 1 and follow.type=2 and user_id=10054) ORDER BY sort limit 15

Look at these three sentences of sql,interesting, aren't they?

To be fair, I have optimized the index, user_id_sort (user_id,sort), so that where force this index when using user_id to judge.

The first sentence: 0.48ms

Second sentence: 0.42ms

The third sentence: 6ms, the reason why it takes so long is that union (query the table twice and merge it into a child table) cannot be overwritten on the sort of order by with index.

Sometimes union is not necessarily faster than or.

Thank you for reading! On the "mysql big data query optimization example analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.