Notes for protecting Indexes

1. is null and IS NOT NULL
Null cannot be used as an index. Any column containing null values will not be included in the index. Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved.
Any statement optimizer that uses is null or is not null in the where clause cannot use indexes.
2. Avoid using incompatible data types.
Incompatible data types represent the type conversion of data retrieved from the entire table, and access will change to full table Scan
Select * from employee where last_name = 100; note that last_name is of the varchar type.
3. Join Columns
For joined columns, the optimizer does not use indexes even if the last joined value is a static value.
Eg: select * from employee where first_name | ''| last_name = 'chen junjie ';
4. like statements starting with a wildcard (%)
Select * from employee where last_name like '% junjie % ';
5. Performing operations on the index field will invalidate the index.
Avoid performing function or expression operations on fields in the WHERE clause. This will cause the engine to stop using the index for full table scanning.
Eg: SELECT * FROM T1 WHERE F1/2 = 100 should be changed to: SELECT * FROM T1 WHERE F1 = 100*2
6. Avoid using it! = Or <>, IN, not in, and so on.
Because this will make the system unable to use the index, but can only directly search the data in the table.
Eg: select * from employee where age! = 30;
The optimizer cannot use indexes to determine the number of rows to be hit. Therefore, you need to search all rows in the table.
Change to: select * from employee where age <30 or age> 30 ;()
7. Try to avoid using or in the where clause to connect to the condition. Otherwise, the engine will discard the index and perform full table scanning,
For example, select id from employee where num = 10 or num = 20
The query can be as follows: select id from employee where num = 10 union all select id from employee where num = 20