Index in mysql database


The index is added to the field of the database table, and it is a mechanism that exists to improve the query efficiency. An index can be added to a field of a table, and an index can also be added to a combination of multiple fields. The index is equivalent to a table of contents of a book, and it is a mechanism that exists to narrow the scope of scanning.

For a dictionary, there are two ways to find a Chinese character:

​ The first method: page by page hinders the search until it is found. This search method is a full dictionary scan. The efficiency is relatively low.

​ The second way: first locate an approximate location through the directory (index), and then directly locate this location, do a local scan, narrow the scope of the scan, and find it quickly. This search method belongs to index retrieval, which is more efficient.

In practice, the front of the Chinese dictionary is sorted according to a, b, c, d...

Only after sorting can there be interval search.

Indexes in the mysql database need to be sorted, and the sorting of this index is the same as the TreeSet data structure. The bottom layer of TreeSet (TreeMap) is a self-balanced binary tree, and the index in mysql is a B-Tree data structure.

Store in accordance with the principle of small left and large right. The data is accessed in an in-order traversal mode.

How does the index work?

In any database, an index object is automatically added to the primary key, and an index is automatically provided on the id field. In addition, in mysql, if there is a unique constraint on a field, an index object is automatically created.

In any database, any record of any table has a physical storage number of the hard disk on the hard disk storage.

In mysql, the index is a single object, and different storage engines exist in different forms. In the MyISAM storage engine, the index is stored in a .MYI file. In the InnoDB storage engine, the index is stored in a logical name called tablespace. In the MEMORY storage engine, the index is stored in memory. Regardless of the storage engine, the index exists in the form of a tree.

The realization principle of the index is to reduce the scan and avoid the full table scan.

Insert picture description here

Under what conditions, do we consider adding an index to the field?

Condition 1: The amount of data is huge.

Condition 2: This field often appears after where, exists as a condition, and is always scanned.

Condition 3: This field is rarely operated by DML (insert delete update). (Because the index needs to be reordered after DML)

How to create the index? How to delete? What is the grammar?

Create index:

cerate index emp_ename_index on emp(ename);

Add an index to the ename field of the emp table and name it: emp_ename_index

Delete index:

drop index emp_ename index on emp

Delete the emp_ename_index index object on the emp table.

When the index becomes invalid, when does the index become invalid?

The first case of failure

Use fuzzy query

select* from emp where ename like '%T';

Even if an index is added to ename, it will not be indexed because the fuzzy match starts with "%".

The second case of failure

It will be invalid when using or. If you use or, the condition fields on both sides of or must be indexed, and then the index will be used. If one of the fields is not indexed, the index on the other field will also be invalid. If ename has an index, the job will fail if it is not indexed.

select* from emp where ename='king' or job='manager';

The third case of failure

When using a composite index, the left column search is not used, and the index is invalid

What is a compound index?

Two fields, or more fields are combined to add an index, called a compound index.

create index emp_job_sal_index on emp(job,sal);

The fourth case of failure:

In the where index column participates in the operation, the index is invalid.

create index emp_sal_index on emp(sal);
select* from emp where sal+1=800;

The fifth case of failure:

In the where index column uses a function

select *from emp where lower(ename)='smith';

Indexes are an important means of optimizing various databases. The first consideration when optimizing is the index. The index is also divided into many categories

Single index: add an index on a field.

Composite index: Add an index on two or more fields.

Primary key index: Add an index on the primary key.

Unique index: Add an index on the field with unique constraint.