Shulou(Shulou.com)06/01 Report--

This article will explain in detail what the MySQL index optimization rules are, the editor thinks it is very practical, so share it for you to do a reference, I hope you can get something after reading this article.

Today's mysql tutorial will introduce MySQL's index optimization rules.

I believe everyone has heard of the foreword index, but how many people really know how to use it? Usually work to write SQL will really consider how this SQL can use the index, how can it improve the efficiency of execution? This article describes in detail several principles of index optimization, as long as it can be applied at any time in the work, I believe that the SQL you write must be the most efficient and powerful. The brain map of the article is as follows: index optimization rule 1, leading fuzzy query of like statement can not use index select * from doc where title like'% XX';-can not use index select * from doc where title like 'XX%';-non-leading fuzzy query, you can use index copy code because page search forbids left fuzzy or full fuzzy, if necessary, you can use search engine to solve. 2. Union, in and or can all hit the index. It is recommended to use inunion to hit the index, and MySQL consumes the least CPU. Select * from doc where status=1union allselect * from doc where status=2; copy code in can hit the index. Query optimization consumes more CPU than union all, but it is negligible. In general, it is recommended to use in. Select * from doc where status in (1,2); copy code or the new version of MySQL 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 copy code 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; replication code can be optimized to in query: select * from doc where status in (0mem3, 4); copy code 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=? Copying code can create federated indexes (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). When establishing a joint index, when the field with the highest degree of differentiation has a mixed judgment condition of non-equal sign and equal sign on the far left, 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=? Copy the code, but it is recommended that the order after where is the same as the federated index, 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 full index failure range conditions of the column after the range column are: =, between, and so on. 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 'and' 1986-12-31 'copy code 6, do not do anything on the index column (calculation, function), otherwise it will lead to index failure and turn to full table scan, such as the following SQL statement, even if the index is established on date, it will scan the whole table: 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.