How to optimize Big Data Queries

If the query method is unreasonable for querying millions of data, the system performance and server pressure will be seriously affected.

Common query optimization solutions are as follows:

1. To optimize the query, try to avoid full table scanning. First, consider creating an index on the columns involved in where and order.

2. Try to avoid null judgment in the WHERE clause; otherwise, the query will discard the index for full table scanning. You can set the default value to 0 on null.

3. Try to avoid using or in the WHERE clause to connect to the condition. Otherwise, the query will discard the index for full table scan. You can consider replacing it with Union all.

4. Use in and not in with caution. Otherwise, full table scan may occur. If you can use between, you do not need in.

5. Avoid using like for fuzzy search. You can use full-text search.

6. Try to avoid using it in the WHERE clause! = Or <> operator. Otherwise, full table scan is performed.

7. Use view to accelerate query.

8. You can use distinct instead of group.

9. If Union all is used, union is not used.

10. Avoid performing function operations in the WHERE clause.