Shulou(Shulou.com)06/01 Report--

This article mainly introduces the basic knowledge of the quick start MySQL database index, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the basic knowledge of the quick start MySQL database index.

-the concept of an index-

The index in the ⑴ database is similar to the catalog in the book

In a book, you don't need to read the whole book, you can use the catalogue to quickly find the information you need.

The table of contents in the book is a list of words, including a famous page number.

⑵ database index

In a database, an index enables a database program to find the data it needs without scanning the entire table

An index in a database is a collection of one or more column values in a table and a list of logical pointers to the data pages that physically identify these values.

Advantages: speed up the query speed, retrieval speed, can accurately locate, do not need to find the whole article

-the role of the index

After ⑴ sets the appropriate index, the database uses a variety of fast positioning techniques, which can greatly speed up the query rate.

⑵, especially when the table is very large, or when the query is designed to multiple tables, using indexes can make the query a thousand times faster.

⑶ can reduce the IO (input and output) cost of the database, and the index can also reduce the sorting cost of the database.

(memory will be consumed when reading)

⑷ ensures the uniqueness of data in data tables by creating uniqueness indexes.

⑸ can speed up the join between tables (you can use related fields as an index)

When using grouping sorting, ⑹ can greatly reduce grouping and sorting time.

-- Classification of the index--

⑴ General Index

This is the most basic index type, and it has no restrictions such as uniqueness.

⑵ uniqueness index

This kind of index is basically the same as the previous "ordinary index", but with one difference: all values of the index column can only appear once, that is, they must be unique.

⑶ primary key

This is a unique index, but it must be specified as "PRIMARY KEY"

⑷ full-text index

The MySQL3.23.23 version begins to support full-text indexing and full-text retrieval. In MySQL, the index type of the full-text index is FULLTEXT, and the full-text index can be created on a column of type VARCHAR or TEXT

-the principle basis for creating an index-

The primary key and foreign key of the ⑴ table must have an index

Tables with more than 300 rows of ⑵ data should have indexes

Tables that ⑶ often joins with other tables should be indexed on the join field

Fields with poor ⑷ uniqueness are not suitable for indexing (repetitive, popular, repetitive)

Fields that are updated too frequently by ⑸ are not suitable for index creation

Fields that ⑹ often appears in the where clause, especially those in large tables, should be indexed.

The ⑺ index should be built on fields with high selectivity

⑻ indexes should be built on small fields. For large text fields or even super-long fields, do not build indexes.

-method of creating an index-

After selecting the appropriate index according to the needs of the enterprise, you can use CREATE INDEX to create the index

CREATE INDEX plus each index keyword can create each type of index.

PS: first you need to access the database

⑴ creates a normal index

Syntax structure: create index (name of index) on tablename (list of columns)

For example:

Mysql > create index salary_index on IT_salary (payroll); # create an index for IT_salary (default normal index), with the index name salary_index

Mysql > show index from IT_salary; # from the IT_index data table, view the index

⑵ creates a unique index

Syntax structure: create unique index (index name) on tablename (table name)

For example:

Mysql > create unique index salary_unique on IT_salary (name); # create a unique index of IT_salary and the index name is salary_unique

Mysql > show index from IT_salary; # from the IT_index data table, view the index

There are two ways for ⑶ to create a primary key index. One is to create the primary key while creating the table, and the primary key index is created automatically.

Command format:

Create table tablename ([…] , primary ke (the name of the table); # when you create the table, you add the primary key, that is, it is automatically created

Alter table tablename add primary key (list of columns); # forget to add the primary key after creation, in this way

Attach:

Change the table structure

Add table primary key, command format: alter table add primary key (id)

1. Add a column:

Alter table info add column age int

# change table info to add column age type

two。 Delete a column:

Alter table info drop column age

# change table info delete column age

Example of primary key index

Mysql > alter table IT_salary add primary key (employee ID)

⑷ Index View

Show index from tablename

Show keys from tablename

View the index example

Mysql > show index from IT_salary

Mysql > show keys from IT_salary

After reading the above basic knowledge about quick start MySQL database index, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.