A few things to note about the MySQL index

In the database, the biggest impact on performance includes the lock policy, cache policy, index policy, storage policy, execution plan optimization policy.

Index policy determines the efficiency of the database to locate data quickly, and storage policy determines the efficiency of data persistence.

1. The index does not store null values.

More specifically, single-column indexes do not store null values, and composite indexes do not store all null values. The index cannot store Null, so when you apply the is null condition to this column, because of the root on the index

Without the Null value, the index cannot be used, only the full table can be scanned.

Why can't index columns store Null values?

Many comparison operations are involved in constructing index column values. The particularity of Null value lies in that most of the operations involved are null. In this case, the null value is actually not

Participate in the indexing process. That is, the null value does not appear on the leaf node of the index tree like any other value 1.

2. Not suitable for columns with fewer key values (columns with more duplicate data).

If the index column TYPE has five key values and if there are 10,000 pieces of data, THEN WHERE TYPE = 1 accesses 2,000 data blocks in the table.

In addition to accessing index blocks, 1 accesses more than 200 data blocks.

If a full table scan is performed, assuming 10 pieces of data per block, then only 1000 blocks are accessed, since a full table scan is performed on blocks accessed

Less than 1, and you're certainly not using the index.

3. Leading fuzzy queries cannot utilize indexes (like 'XX' or like '%XX%')

Suppose there is such a value of 1 column code as 'AAA','AAB','BAA','BAB', if where code like '%AB' condition, since the preceding is

Fuzzy, so you can't take advantage of the order of the index, you have to look for each one to see if it satisfies the condition. This can result in a full index scan or a full table scan

The stroke. If this is the case for where code like 'A % ', you can find the position of CODE beginning with A in CODE, when encountering B beginning

Data, you can stop looking, because the later data 1 must not meet the requirements. This allows you to take advantage of the index.

4.MySQL mainly provides two kinds of indexes: ES73en-ES74en index and Hash index.

The B tree index has scope and prefix lookup capabilities, and for B trees with N nodes, the complexity of retrieving 1 record is O(LogN). That's the same thing as looking for 2 points.

Hash indexes can only do equal lookup, but no matter how big the Hash table is, the lookup complexity is O(1).

Obviously, if there is a large difference in values, and the equivalent search (=, < , > The Hash index is a more efficient choice with the lookup complexity of O(1).

The B tree is a better choice if the values are relatively different and are dominated by range lookup, which supports range lookup.

The two main storage engines in MySQL, MyISAM and InnoDB, adopt different indexing and storage strategies. This article analyzes their similarities, differences and performance.