MySQL's index

What is an index:

The index in the database is similar to a catalog in a book, where you can quickly find the information you need without having to read the entire book. In the database, the index enables the database program to find the data that it needs without having to scan the entire table. The catalogue in the book is a list of words, It notes the page numbers that contain each word, and in the database, because the data is stored in the data table, the index is created on the data Table object, consisting of keys generated by one field or multiple fields in the table, stored in the data structure (b-tree or hash table), MySQL allows you to quickly and efficiently find fields associated with key values, with the storage type of the rage index, and you can divide the index into B-tree indexes and hash indexes.

The role of indexes is to improve database performance by greatly improving the retrieval speed of databases by using indexes.

Index classification:

In MySQL, the index is divided into six categories:

1. General Index

The basic index type in MySQL at normal indexing allows duplicate and null values to be inserted in the column that defines the index, and its only task is to speed up access to the data.

2. Unique index

Unique index does not allow two rows to appear the same index value

3. Primary KEY index

The primary key index is automatically created for defining a primary key in the database diagram, and the primary key index is the only special type

4. Composite Index

When you create an index, you do not have to create an index on one of the columns, and as you create a primary key, you can combine multiple columns as indexes, which are called composite indexes.

5. Full-Text Indexing

The role of full-text indexing is to support full-text lookups on columns that define indexes, allowing duplicate and null values to be inserted in these indexed columns.

6. Spatial index

A spatial index is an index that is established on a column of spatial data types.

Example of creating an index:

1.IQUE or unique KEY (unique index)

Mysql> ALTER TABLE table_name add unique (' column ')

2ulltext (full-text index)

Mysql> ALTER TABLE TABLE_NAME ADD FULLTEXT (' column ')

3 Index (normal index)

Ysql> ALTER TABLE table_name ADD index index_name (' column ')

to modify an index in a table:

ALTER TABLE TableName DROP primary Key,add primary key (Fileda,filedb)

View Index:

Show index from TABLE_NAME;

Backup and restore of databases

　　To back up a database using the Musqldump command

Grammar:

Mysqldump-u username-h host-p *******

dbname [tbname1[tbname2 ...] > Filename.sql

　　*username represents the user name

*host indicates the host name of the logged-on user, such as the host can be omitted

*password means login password

*dbname for databases that need to be backed up

*tbname for data tables that need to be backed up, you can specify multiple tables, which are optional, such as backing up the entire database.

*filename.sql represents the name of the backup file.

　　　　To back up the database using the Musqldump command:

Grammar:

Mysql-u sername-p * * * * [dbname] <filename.sql

　　*username represents the user name

*dbname indicates the database name

*filename.sql the file after the database is backed up

MySQL's index