SQL optimization mainly revolves around indexes. A major focus of SQL optimization is to avoid index failure, because index failure will lead to full table scanning. In the case of large amount of data, you can clearly feel the decrease in query speed.
Let’s talk about various scenarios of index failure.
- When using indexes, typeImplicit conversion. For example, for example, person_no is 12345 of varchar type. At this time, the query should be [select * from table where person_no='12345';]. At this time, the index is valid. However, if the query is mistakenly written as [select * from table where person_no=12345;], the data can be found, but type conversion will be performed and matched, which will cause the index to fail. Other types of implicit conversions (such as DATE and string conversions) may also cause failure, and implicit conversions of different character sets (such as utf8mb4 and latin1) may also fail. Make sure that the condition value is consistent with the field type when querying.
- Or is used in the where condition. If both fields have indexes, it may triggerIndex Merge(Index Merge); if there is no index on one side, the full table will be scanned. Here you can consider adding an index to the fields in the or condition, or substituting the or with union.
- Not in lineLeftmost prefix rule, If a joint index (A, B, C) is established and only B and C conditions are used in the where conditions, this joint index will be invalid, so it must include A. If A and C are included, C's index is also invalid because B is not in the condition, so if you want C to take effect, you must add the relevant conditions of B.
- Usedis not nullgrammar. When creating an index for a field, most fields on the field have values to have this requirement. At this time, if the field allows NULL and there is an index, it may be valid if the data index that is null is null, but when using is not null to query non-null records, if most of the data is not NULL, the optimizer may choose a full table scan, resulting in the index failure.
- Using the not in syntax or !=, querying elements that are not outside a certain set will result in a full table scan. Use left join or not exists to optimize not in.
- Using > or < or between and syntax, range query is valid for the current column index, but subsequent columns in the joint index cannot use the index. For example [select * from table where a > 100 and b = 200;
a walks through the index, b cannot walk through the index】, it is recommended to optimize to place the equal value condition column to the left of the joint index.
- Using fuzzy query like may cause the index to fail. At this time, using '%xxx' will inevitably lead to the index to fail. If 'xxx%' is used, the index may be useful. Simply put, try not to match the suffix, but match the prefix. If usingFull text index(such as MySQL
FULLTEXT), which can optimize fuzzy query.
- Operations on index columns in conditions can also cause index failure. For example, when a single column index is established for person_no, you need to query the data whose first three digits of person_no are '123'. The query statement is [select id, person_no from table where substring(person_no, 1, 3)='123']. At this time, the substring operation is performed, resulting in the index failure, so avoiding the index column operation during query.
- The data is too concentrated in steps, such as the gender field, which is only possible for both male and female, and the index is not recommended to be built on such fields.