Author: alibaba tao technology link: www.zhihu.com/question/29… Source: Zhihu

When the server responds to a request from the client, the client must receive the entire return result, not simply take the first few results and then ask the server to stop sending. Query should return only necessary data as far as possible to reduce the size and number of communication packets and improve efficiency.

2, IN and NOT IN should be used with caution, resulting IN full table scan.

The above example uses an index with IN, but does NOT use an index with NOT IN.

The above example uses IN without an index. The MySQL optimizer will choose the least expensive way to execute. IN and NOT IN can both fail indexes, but NOT always.

3. LIKE causes full table scan

LIKE causes index invalidation because it does not follow the optimal left prefix rule. String b-tree sorting: Sort the string by the first letter. If the first letter is the same, sort the string by the second letter, and so on. The % sign is placed on the right of the b-tree. The index order of the b-tree is sorted by the size of the first letter. The prefix matches the first letter. So you can do an ordered search on the B+ tree, looking for data that starts with the right letter. So sometimes you can use indexes.

4. The associative index must follow the best left prefix rule

GLS_CODE,BARCODE, and SKU_ID create a joint index. If there is no GLS_CODE column in the WHERE condition, the joint index will not be removed. When creating this kind of multi-column federated index, the order of the columns is very important. The combination order of the b-tree index is the same as that of the columns when the INDEX is created. The order of the first column is determined, and the order of the other columns is uncertain. Restrictions on b-tree indexes. If the query is not in the order in which the indexes are created, the indexes cannot be used.

SQL > select * from ‘where’ where (select * from ‘where’); This is because the MySQL optimizer reorders the query.

5, do not do anything on the index column

Index column calculation, function, and type conversion may cause index failure and perform full table scan.

6, is null, is not null may cause index invalidation

7, attention should be paid to the where, order by, group by the back of the column, more than a table column is indexed, priority combination index

Adding indexes

SQL > select * from ‘where’; SQL > select * from ‘where’

When single quotes are not used, the comparison between strings and numbers does not match. MySQL will perform an implicit type conversion to convert them to floating point numbers for comparison, which will invalidate the index. 9, the count () and count (1) dev.mysql.com/doc/refman/ misunderstanding… Count () is exactly as efficient as count(1), and there is no way that count(1) is faster than count(*).

(The author of This article: Alibaba Tmall Luxury Operation Center)