Example Analysis of SQL tuning of 10 million-level user system

This article mainly introduces the example analysis of tens of millions of user system SQL tuning, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Users have millions of active users and tens of millions of registered users every day, and if there is no sub-database and sub-table, there may be only one user table in the DB, with tens of millions of user data in a single table.

A system specifically filters a large number of users through various conditions, and then pushes some messages to those users:

Some promotional messages

The news of asking you to apply for a membership card

To tell you that there is news of a special offer.

Through some conditions to filter out a large number of users, for these users to do push, this process is more time-consuming-filtering user process.

Users have millions of active users and tens of millions of registered users every day, and if there is no sub-database and sub-table, there may be only one user table in the DB, with tens of millions of user data in a single table.

SQL for filtering users to the operating system:

SELECT id, name FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)

In general, tables that store user data are divided into two tables:

Store the user's core data, such as id, name, nickname, mobile phone number, etc., that is, the users table in the above SQL statement

Store some extension information about the user, such as home address, hobbies, last login time, etc., namely users_extent_ info table

There is a sub-query, which aims at the user's extension information table, that is, users_extent_info checks the last login time