When using the database in actual development, it is inevitable to encounter some large tabular data. When querying these data, sometimes SQL will be particularly slow. At this time, experienced experts will tell you which areas are searched more, just add an index.

So, how to construct the index reasonably? Here, I want to share some of my experiences. If there is anything wrong, please criticize and correct me.

1, don't blindly build an index, analyze it before creating it.

Although the index can greatly improve our query performance, we should also know that the index tree should be maintained when you add, delete or modify it. So the more indexes, the better, but on demand. It is best to analyze the whole module after the development and establish a joint index for most queries.

2. Use federated indexes to cover as many conditions as possible.

This means that if there are five WHEREs and one order by in a slow sql, our joint index will cover these five query conditions as much as possible, and if necessary, order by will also cover them.

3. Small radix fields do not need an index.

This means that if a field in a table has only a few values, then the index you set for this field is actually meaningless. For example, if you set an index, then sorting is meaningless (that is, men and women are separated in the index).

Therefore, the establishment of the index should choose the data with large cardinality as much as possible, so as to make the best use of the index.

4. Long strings can use prefix index.

We try to choose smaller types of fields for the index, such as varchar(20) and varchar(256). There is an obvious gap between the index based on 20 or above and the index based on 256 (the string is too long to sort, alas).

Of course, if varchar(256) must be indexed, the first 20 characters can be selected and put into the index tree (here 20 is not absolute, so the smallest character field design that can distinguish data as much as possible should be selected). In this way, the key index (name (20), age and job title) will only search the first 20 characters of the name, but the prefix index cannot be applied to order by and group by.

5. Designing indexes for sorting fields has a low priority.

If we have a range search followed by a sort field in SQL, then we will index the range search fields first instead of sorting them first.

6. If slow SQL appears, you can only design a federated index for this SQL.

But the optimization of slow SQL needs to be analyzed step by step. You can use explain to check the analysis results of SQL statements first, and then make corresponding improvements according to the results. We'll talk about explanation next time.

PS: Add the explain keyword before the select statement, MySQL will set a flag on the query, and executing the query will return the information of the execution plan instead of executing this SQL.