Mysql note 6_index

What is an index?

An object in the database.

It is used in the database to accelerate table queries.

Using quick path access to locate data reduces disk I/o.

It is stored independently of a table, but cannot exist independently. It must belong to a table.

Automatically maintained by the database. When a table is deleted, its indexes are automatically deleted.

The index function is similar to the book directory. There is almost no book without a directory, so there is almost no table without an index.

How to create an index:

Create index name on table name (field name );

Use index:

Add the index field after the where clause.

Indexing principle:

Index page table

Index_name | loc | <--------------------------------------------- |

Name | c:/xxx/xx | id | name (index) | sex | age |

... |

Database query method:

1. Full table scan: scan all fields to locate the location of the data on the disk.

2. Index scan: locates data on the index page.

3. Shared statement.

Note:

Index fields that are frequently queried.

If there are few columns in the table, it is not suitable for creating indexes.

After multiple insert, delete, and update operations are performed, index fragments (such as disk fragments and junk files) may occur, affecting the query speed. Therefore, we should reorganize the indexes. That is, the index is deleted and then re-created.