MySQL Learning notes--indexes and views

Index and manage Indexes

A database object in the schema

Function: Used in the database to speed up the query of the table

Create: Automatically create indexes on primary key and unique key

Reduces disk I/O by quickly locating data by using fast path access methods

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

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

The index acts like a directory on a book, and almost no book has no table of contents, so few tables have no index

Indexing: Indexing on fields that are frequently queried, using index to optimize queries (index avoids a full scan of the table)

Principle: When an index is created in a field, the database generates an index page, and the index page saves not only the indexed data, but also the specific physical address of the index in the database.

Note: If the table has very few columns, it is not suitable for indexing. Index fragmentation occurs when the INSERT, delete, and update are executed many times. affect the query speed, we should reorganize the index

Reorganization method: Drop index index_name;

CREATE INDEX index_name on table (column)

# Create indexes manually CREATE INDEX Index_tb_dept_name  on tb_dept (NAME); # Use an index to add an index to a where to improve query efficiency SELECT *  from WHERE NAME='Tom'

Views and Management views

The benefits of a view: You can restrict access to data, make complex queries easier, provide data independence, and provide different explicit

# Create a viewCREATE VIEWEmo_v_10 asSELECTNAME as 'name', sex'Sex', age'Age' fromtb_empWHEREdept_id=2; # Working with viewsSELECT *  fromEmo_v_10

MySQL Learning notes--indexes and views