Analysis of mysql indexes and Analysis of mysql

Analysis of mysql indexes and Analysis of mysql

A database index is a data structure designed to speed up table operations. You can use one or more columns to create an index by providing fast random search and Efficient Sorting of access records.

Which columns should be considered for SQL queries and one or more indexes should be created for these columns.

In fact, the index is also a table, where the pointer to the primary key or index field is saved and points to the actual table type for each record.

Users cannot see indexes. They are only used to accelerate queries and will be used by database search engines to increase the speed of searching records.

The INSERT and UPDATE statements take more time to create indexes. As a SELECT statement, you can quickly operate on these tables. This is because the database needs to update the inserted or updated index values when inserting or updating data.

Simple and unique index

You can create a unique value index on a table. A unique index means that two rows cannot have the same index value. The syntax for creating an index on a table is as follows:

CREATE UNIQUE INDEX index_nameON table_name ( column1, column2,...);

You can use one or more columns to create an index. For example, we can use tutorial_author to create a tutorials_tbl index.

CREATE UNIQUE INDEX AUTHOR_INDEXON tutorials_tbl (tutorial_author)

You can create a simple index on a table. To create a simple index, only the UNIQUE keyword is omitted. Simple indexes can be repeated in the table.

If you want to sort the values of the columns to be indexed in descending order, you can add the reserved word DESC after the column name.

mysql> CREATE UNIQUE INDEX AUTHOR_INDEXON tutorials_tbl (tutorial_author DESC)

Use the ALTER command to add and delete Indexes

You can add four types of indexes to a table:

• Alter table tbl_name add primary key (column_list): ADD a primary key, which means that the index value must be unique and cannot be empty.
• Alter table tbl_name add unique index_name (column_list): the value of the number of created indexes must be UNIQUE (except NULL values cannot be used, others can appear multiple times ).
• Alter table tbl_name add index index_name (column_list): adds a common INDEX, where any value appears multiple times.
• Alter table tbl_name add fulltext index_name (column_list): creates a special FULLTEXT index for text search purposes.

The following is an example of adding an index to an existing table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can use the ALTER command and the DROP clause to delete an index. Try the following example to delete the created index.

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

Use the ALTER command to add and delete the PRIMARY KEY

You can also use the same method to add a primary key. However, to ensure that the primary key is used properly in the column, you must specify not null.

The following is an example of adding a primary key to an existing table. The column must be added with the not null attribute and then added as a primary key.

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

You can use the ALTER command to delete the primary key as follows:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

If the index to be deleted is not a primary key, you must specify the index name.

Show index information

You can use the show index Command to list all table-related indexes. Vertical format output (specified by \ G), which is a commonly useful statement to avoid long-line summary output:

Try the following example:

mysql> SHOW INDEX FROM table_name\G........

Summary

The above is the mysql index introduced by xiaobian. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!