MySQL index type summary and usage tips and precautions

Index fields in database tables can greatly improve the query speed. Suppose we have created a mytable table:

Copy codeThe Code is as follows: create table mytable (id int not null, username VARCHAR (16) not null );

We randomly inserted 10000 records, one of which is 5555 and admin.

When querying the record SELECT * FROM mytable WHERE username = 'admin'; of username = "admin", MySQL does not need to perform any scans if an index has been created on username, this record can be found accurately. On the contrary, MySQL scans all records to query 10000 records.

Indexes are divided into single-column indexes and composite indexes. A single-column index contains only one column. A table can have multiple single-column indexes, but this is not a combination index. A composite index contains multiple columns.

MySQL indexes include:

I. general Indexes

This is the most basic index with no restrictions. It can be created in the following ways:

1. Create an index

Copy codeThe Code is as follows: create index indexName ON mytable (username (length ));

For CHAR and VARCHAR types, the length can be smaller than the actual length of the field; For BLOB and TEXT types, the length must be specified, the same below.

2. Modify the table structure
Copy codeThe Code is as follows:
ALTER mytable add index [indexName] ON (username (length) -- specify

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;

Ii. Unique Index

It is similar to the previous normal index. The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:

Copy codeThe Code is as follows: create unique index indexName ON mytable (username (length ))
-- Modify Table Structure
ALTER mytable add unique [indexName] ON (username (length ))
-- Specify
Create table mytable (id int not null, username VARCHAR (16) not null, UNIQUE [indexName] (username (length )));

Iii. Primary Key Index

It is a special unique index and does not allow null values. Generally, when creating a table, you can create a primary key index at the same time:

Copy codeThe Code is as follows: create table mytable (id int not null, username VARCHAR (16) not null, primary key (ID ));

Of course, you can also use the ALTER command. Remember: A table can only have one primary key.

Iv. Composite Index

To visually compare Single-Column indexes and composite indexes, add multiple fields to the table:
Copy codeThe Code is as follows: create table mytable (id int not null, username VARCHAR (16) not null, city VARCHAR (50) not null, age int not null );

To further extract MySQL efficiency, you must consider establishing a composite index. Create name, city, and age in an index:

Copy codeThe code is as follows: alter table mytable add index name_city_age (name (10), city, age); [code]
The usernname length is 16 when the table is created, and 10 is used here. This is because the name length generally does not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.

If a single column index is created on usernname, city, and age respectively, the table has three single column indexes, and the query efficiency will be significantly different from that of the preceding composite indexes, far lower than our combined index. Although there are three indexes at this time, MySQL can only use one of them, which seems to be the most efficient single-column index.

The establishment of such a composite index is actually equivalent to the establishment of the following three composite indexes:

Why does usernname, city, age usernname, and city usernname have no combined indexes such as city and age? This is because MySQL Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. This composite index is not used for queries that contain these three columns. The following SQL statements use this composite index:
[Code]
SELECT * FROM mytable WHREE username = "admin" AND city = "Zhengzhou" SELECT * FROM mytable WHREE username = "admin"

The following are not used:
Copy codeThe Code is as follows:
SELECT * FROM mytable WHREE age = 20 AND city = "Zhengzhou" SELECT * FROM mytable WHREE city = "Zhengzhou"

5. Time to create an index

Now we have learned how to create an index. Under what circumstances do we need to create an index? IN general, you need to create an index for the columns that appear IN the WHERE and JOIN operations, but this is not the case because MySQL only applies to <, <=, =,>,> =, BETWEEN, IN, and sometimes LIKE will use the index. For example:

Copy codeThe Code is as follows: SELECT t. Name FROM mytable t left join mytable m ON t. Name = m. username WHERE m. age = 20 AND m. city = 'zhengzhou'

In this case, you need to create an index for the city and age. Because the userame of the mytable table also appears in the JOIN clause, it is also necessary to create an index for it.

As mentioned earlier, only LIKE needs to be indexed in some cases. MySQL does not use an index when it starts with the wildcard "%" and. For example, the following sentence uses an index:
Copy codeThe Code is as follows:
SELECT * FROM mytable WHERE username like 'admin %'
The following statement will not be used:
Copy codeThe Code is as follows: SELECT * FROM mytable WHEREt Name like '% admin'

Therefore, pay attention to the above differences when using LIKE.

Vi. Index Deficiency

The advantages of using indexes are described above, but excessive use of indexes will cause abuse. Therefore, the index also has its disadvantages:

1. Although the index greatly improves the query speed, it also reduces the speed of updating the table, such as performing INSERT, UPDATE, and DELETE operations on the table. When updating a table, MySQL not only needs to save data, but also stores the index file.

2. index files that occupy disk space when an index is created. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will expand very quickly.

Indexes are only a factor to improve efficiency. If your MySQL database has a large data volume of tables, you need to spend time researching and creating the best indexes or optimizing query statements.

7. Notes on using Indexes

Tips and notes for using indexes:

1. The index does not contain columns with NULL values.

As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.

2. Use short Indexes

Index a string or column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

3. Index column sorting

MySQL queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.

4. like statement operation

Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.

5. Do not perform operations on columns
Copy codeThe Code is as follows:
Select * from users where YEAR (adddate) <2007;

The operation will be performed on each row, which will cause index failure and scan the entire table, so we can change it:

Copy codeThe Code is as follows: select * from users where adddate <'2017-01-01 ';

6. Do NOT use the not in and <> operations

The above section describes the MySQL index type. Hope to help you.