1. definition of index invalidation
- Although you have built an index on this column and the query criteria are also index columns, the final execution plan does not follow its index.
- Here are some key points that cause this problem:
2. column to column comparison
- In a table, two columns (id and c\u id) have separate indexes. The following query criteria will not go through the index
select * from test where id=c_id;
- In this case, it will be considered that full table scanning is better.
3. NULL value condition exists
- When designing database tables, we should try our best to avoid the occurrence of NULL values. If NULL values are unavoidable, we should also give a DEFAULT value. Numerical types can be given as 0, -1 and so on. Sometimes, if there is a problem with empty strings, we should give a space or other. If the index column is nullable, it will not be indexed. The index value is less than the count(*) value of the table. In this case, the execution plan will naturally scan the whole table.
select * from test where id is not null;
4.NOT condition
- When creating an index, create an entry for each index column. If the query criteria are equivalent or range query, the index can find the corresponding entry according to the query criteria. On the other hand, when the query condition is non, it is difficult to locate the index. At this time, the execution plan may prefer full table scanning. Such query conditions include: < >, NOT, in, not exists
select * from test where id<>500; select * from test where id in (1,2,3,4,5); select * from test where not in (6,7,8,9,0); select * from test where not exists (select 1 from test_02 where test_02.id=test.id);
5.LIKE wildcard
- When using fuzzy search, try to use post wildcards, such as name | '%', because when you search the index, it will match the index column from the front. At this time, it can be found. If you use pre matching, it will be very troublesome to check the index. For example, if you query all people surnamed Zhang, you can search 'Zhang%'. On the contrary, if you query all the people named 'Ming', it can only be% Ming. How to locate the index? In the case of pre matching, the execution plan will prefer to select full table scanning. INDEX RANGE SCAN can be used for post matching.
- Therefore, in business design, we should try to consider the problem of fuzzy search and use more post wildcards.
select * from test where name like Zhang||'%';
6. conditions include
- Try not to use functions for index columns in query conditions, such as the following SQL
select * from test where upper(name)='SUNYANG';
- In this way, the index will not be used, because the index may be different from that after calculation, and cannot be located to the index. However, if the query condition is not to calculate the index column, the index can still be used. such as
select * from test where name=upper('sunyang');
--INDEX RANGE SCAN
- Such functions include: to_char,to_date,to_number, trunc, etc
7. the leading columns of the composite index are big
- When the differentiation of the leading columns of the composite index is small, we have INDEX SKIP SCAN. The differentiation of the current leading column is large, and the splitting of the leading column will consume a lot of resources when checking the post leading column. The execution plan is not as fast as the full table scan, and then the index fails.
select * from test where owner='sunyang';
8. data type conversion
- When the query condition has implicit conversion, the index will be inval id ated. For example, the number type stored in the database has the following form when querying:
select * from sunyang where id='123';
9. predicate operation
- As we said above, functional operations cannot be performed on index columns, which also includes predicate operations of addition, subtraction, multiplication and division, which will also invalidate the index. Create a sunyang table with the index id. look at the SQL:
select * from sunyang where id/2=:type_id;
- It is obvious that the index column id is divided by '/2'. At this time, the index will become invalid. This situation should be rewritten as follows:
select * from sunyang where id=:type_id*2;
10. Does MySQL use IN to go through the index
- Conclusion: IN will certainly go through the index, but when the value range of IN is large, it will lead to index failure and go through the full table scan
navicat visualizer uses explain function to view sql execution information
Scenario 1: when the value IN in has only one primary key
We only need to pay attention to the most important type information. It is obvious whether the index is used for withdrawal:
The type result values are in order from good to bad:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
all: full table scan
Index: another form of full table scanning, but the scanning method is based on the index order
Range: index scanning with a range. Compared with index scanning with a full table, it has a range limit, so it is better than index scanning
ref: the lookup criteria column uses an index and is not primary key or unique. In fact, it means that although the index is used, the value of the index column is not unique and there are duplicates. In this way, even if the index is used to quickly find the first piece of data, it still cannot be stopped, and a small-scale scan near the target value is required. But its advantage is that it does not need to scan the entire table, because the index is ordered. Even if there are duplicate values, it is scanned in a very small range.
const: generally, if a primary key is placed after where as a conditional query, the mysql optimizer can convert the query optimization into a constant. How and when to convert depends on the optimizer
Generally speaking, it is necessary to ensure that the query reaches at least the range level. It is better to reach ref. when index and all appear in type, it means that the full table scan is taken without index, which is inefficient. In this case, it is necessary to tune the sql.
When using filer or using temporary occurs in extra, it means that the index cannot be used and must be optimized as soon as possible.
possible_keys: the index used by sql
Key: displays the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL
Rows: displays the number of rows MySQL thinks it must check when executing a query.
Scenario 2: expand the value range IN in
At this point, the index is still used, but the efficiency is reduced
Scenario 3: continue to expand the value range of IN
It is found that the index has not been taken at this time, but the full table scan
12. use or after where to invalidate the index (use or as little as possible)
Simple example demonstration:
Create two normal indexes,
CREATE INDEX index_name_email ON students(email); CREATE INDEX index_name_phone ON students(phone);
Use the following to query sql,
# Index used EXPLAIN select * from students where stud_id='1' or phone='18729902095' # Index used EXPLAIN select * from students where stud_id='1' or email='742981086@qq.com' #-------------------------- # Index not used EXPLAIN select * from students where phone='18729902095' or email='742981086@qq.com' # Index not used EXPLAIN select * from students where stud_id='1' or phone='222' or email='742981086@qq.com'
13. if the column type is a string, the data must be quoted in quotation marks in the condition, otherwise the index is not used
Create an index for name!
CREATE INDEX index_name ON students(name);
# Use index EXPLAIN select * from students where name='110' # Index not used EXPLAIN select * from students where name=110
14.DATE_FORMAT() formats the time. If the formatted time is compared, the index may become invalid.
- Delete the index created on students! Re create_ Create an index on date!
CREATE INDEX index_create_date ON students(create_date);
# Use index EXPLAIN SELECT * from students where create_date >= '2010-05-05' # Index not used EXPLAIN SELECT * from students where DATE_FORMAT(create_date,'%Y-%m-%d') >= '2010-05-05'