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

In view of how to optimize the index in MySQL, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

The brain map of the article is as follows:

Index optimization rules

1. The leading fuzzy query of like statement cannot use index.

Select * from doc where title like'% XX';-cannot use index select * from doc where title like 'XX%';-non-leading fuzzy query, you can use index

Because the page search forbids left ambiguity or full ambiguity, you can use search engines to solve it if necessary.

2. Union, in and or can all hit the index. It is recommended to use in.

Union can hit the index, and MySQL consumes the least CPU.

Select * from doc where status=1 union all select * from doc where status=2

In can hit the index, and query optimization consumes more CPU than union all, but it can be ignored. In general, it is recommended to use in.

Select * from doc where status in (1,2)

The new version of MySQL of or can hit the index, and query optimization consumes more CPU than in, so it is not recommended to use or frequently.

Select * from doc where status = 1 or status = 2

Add: in some places, it is said that if or is used in the where condition, the index will fail, resulting in a full table scan. This is a misunderstanding:

① requires that all fields used by the where clause must be indexed

② if the amount of data is too small, mysql finds that full table scans are faster than index lookups when making an execution plan, so indexes are not used

③ ensures that the mysql version is above 5.0and that the query optimizer turns on index_merge_union=on, that is, there is index_merge_union and on in the variable optimizer_switch.

3. Negative conditional queries cannot use indexes

Negative conditions are:! =, not in, not exists, not like and so on.

For example, the following SQL statement:

Select * from doc where status! = 1 and status! = 2

Can be optimized to an in query:

Select * from doc where status in (0Jing 3pm 4)

4. Leftmost prefix principle of federated index

If a federated index is built on three fields, it will automatically build a | (aformab) | (arecaline brem c) group index.

Log in to the business requirements. The SQL statement is as follows:

Select uid, login_time from user where login_name=? Andpasswd=?

A federated index can be created (login_name, passwd). Because there are few single-condition query requirements of passwd in business, and there are many single-condition query requirements of login_name, federated indexes of (login_name, passwd) can be established instead of (passwd, login_name).

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

When building a federated index, the most differentiated field is on the far left.

When there is a mixed judgment condition of non-equal sign and equal sign, the column of the equal sign condition is preceded when the index is established. Such as where a >? And bounded indexes, then even if an is more differentiated, b must be placed at the top of the index.

When querying the leftmost prefix, it does not mean that the where order of the SQL statements should be the same as the federated index.

The following SQL statement can also hit (login_name, passwd) the federated index:

Select uid, login_time from user where passwd=? Andlogin_name=?

However, it is still recommended that the order after where is the same as that of the federated index to form a good habit.

If index, where axiom 3 and b like 'abc%' and caster 4, a works, b works, c doesn't work.

5. You cannot use the column to the right of the range condition in the index (the range column can use the index). The index of the column after the range column is invalid.

The range conditions are: =, between, etc.

The index is used for at most one range column, and cannot be used fully if there are two range columns in the query condition.

If there is a federated index (empno, title, fromdate), then emp_no can use the index in the following SQL, while title and from_date do not.

Select * from employees.titles where emp_no < 10010 'and title='Senior Engineer'and from_date between' 1986-01-01-01 'and' 1986-12-31'

6. Do not do any operation (calculation, function) on the index column, otherwise it will cause the index to fail and turn to full table scan

For example, the following SQL statement scans the entire table even if an index is established on date:

Select * from doc where YEAR (create_time)

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.