Shulou(Shulou.com)05/31 Report--

Editor to share with you about the MySQL index, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL index

The establishment of MySQL index is very important for the efficient operation of MySQL, and the index can greatly improve the retrieval speed of MySQL.

For example, if the properly designed MySQL that uses the index is a Lamborghini, then the MySQL without the design and use of the index is a human tricycle.

Taking the catalogue page (index) of the Chinese dictionary as an example, we can quickly find the words we need according to the catalogue (index) sorted by pinyin, strokes, partial radicals, etc.

Indexes are divided into single-column indexes and combined indexes.

A single-column index, that is, an index contains only a single column, and a table can have multiple single-column indexes, but this is not a combined index.

A composite index, that is, an index contains multiple columns.

When creating an index, you need to make sure that the index is the condition that is applied to the SQL query statement (usually as a condition of the WHERE clause).

In fact, an index is also a table that holds the primary key and index fields and points to the records of the entity table.

All of the above talk about the benefits of using indexes, but excessive use of indexes will lead to abuse. Therefore, the index also has its disadvantages: although the index greatly improves the query speed, it slows down the speed of updating the table, such as INSERT, UPDATE, and DELETE on the table. Because when updating the table, MySQL not only saves the data, but also saves the index file.

Create an index file that takes up disk space. The second drawback of occupying resources

General index

The most basic type of index, there are no restrictions such as uniqueness.

Create an index

This is the most basic index, and it has no restrictions. It can be created in the following ways:

CREATE INDEX indexName ON table_name (column_name)

If it is of type CHAR,VARCHAR, the length can be less than the actual length of the field; for types of BLOB and TEXT, length must be specified.

Modify table structure (add index)

ALTER table tableName ADD INDEX indexName (columnName)

Specify directly when you create a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, INDEX [indexName] (username (length)

Syntax for deleting an index

DROP INDEX [indexName] ON mytable

Unique index

A unique index is an index that does not allow any two rows to have the same index value.

When there are duplicate key values in existing data, most databases do not allow the newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the employee table, no two employees can have the same last name.

Create an index

CREATE UNIQUE INDEX indexName ON mytable (username (length))

Modify table structure

ALTER table mytable ADD UNIQUE [indexName] (username (length))

Specify directly when you create a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, UNIQUE [indexName] (username (length)

Use the ALTER command to add and remove indexes

There are four ways to add an index to a data table:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

This statement adds a primary key, which means that the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): the value that this statement creates an index must be unique (NULL may appear multiple times except for NULL).

ALTER TABLE tbl_name ADD INDEX index_name (column_list): add a normal index, where the index value can appear multiple times.

* * ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

The above is all the contents of the article "MySQL Index". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.