MySQL Note 6_ index

What is an index?

An object in the database.

The query that is used in the database to speed up the table.

The disk's I/O is reduced by locating the data by using the fast path access method.

Separate from the table, but cannot exist independently, and must belong to a table.

Automatically maintained by the database and the indexes on the table are automatically deleted when the table is deleted.

The index works like a book's directory, with almost no book without a table of contents, so there are few tables without indexes.

How to create an index:

Create index index name on table name (field name);

Use index:

The where is followed by an indexed field.

Index 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 the fields and locate the location of the disk where the data resides.

2. Scan by index: Locate data by index page.

3, share the statement.

Attention:

Build indexes on fields that are frequently queried.

If the table has very few columns, it is not appropriate to build an index.

Index fragmentation (like disk fragmentation, garbage files, and so on) can affect query speed after multiple inserts, deletes, and update, so we should reorganize the index. That is, delete the index and rebuild the index.