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

This article mainly introduces the mysql database index types, the article is very detailed, has a certain reference value, interested friends must read it!

This article will focus on describing the four types of masql database indexes. How to build database indexes? Columns that appear in WHERE and JOIN need to be indexed, but not exactly, because MySQL only uses indexes for =, BETWEEN,IN, and sometimes LIKE. I hope this article can help you. First of all, let's understand what the index is. It can be summed up in one sentence: index is the key to fast search.

The establishment of MySQL index is very important for the efficient operation of MySQL. Here are several common MySQL index types

In database tables, indexing fields can greatly improve the query speed. Suppose we create a mytable table:

The code is as follows:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL)

We randomly inserted 10000 records into it, including one: 5555, admin.

When looking up the record SELECT * FROM mytable WHERE username='admin'; of username= "admin", if an index has been established on username, MySQL can accurately find the record without any scan. Instead, MySQL scans all records, that is, querying 10000 records.

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. Composite index, that is, a single index contains multiple columns.

MySQL index types include:

I. General index

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

1. Create an index

The code is as follows:

CREATE INDEX indexName ON mytable (username (length))

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

two。 Modify table structure

The code is as follows:

ALTER mytable ADD INDEX [indexName] ON (username (length))-- specify directly when creating a table

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

-- the syntax for deleting an index:

DROP INDEX [indexName] ON mytable

Second, unique index

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

The code is as follows:

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

-- modify the table structure

ALTER mytable ADD UNIQUE [indexName] ON (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)

III. Primary key index

It is a special unique index and no null values are allowed. Typically, the primary key index is created at the same time as the table is created:

The code is as follows:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, PRIMARY KEY (ID))

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

IV. Combined index

To visually compare single-column indexes with combined indexes, add multiple fields to the table:

The 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)

In order to further extract the efficiency of MySQL, it is necessary to consider the establishment of a composite index. Is to build name, city, and age into an index:

The code is as follows:

ALTER TABLE mytable ADD INDEX name_city_age (name (10), city,age); [code]

When you build the table, the usernname length is 16, and here you use 10. This is because in general, the length of the name will not exceed 10, which will speed up the index query, reduce the size of the index file, and improve the update speed of INSERT.

If we set up a single-column index on usernname,city,age and let the table have three single-column indexes, the query efficiency will be much different from that of the above combined index, which is much lower than our combined index. Although there are three indexes at this time, MySQL can only use one of the single-column indexes that it seems to be the most efficient.

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

Why doesn't usernname,city,age usernname,city usernname have a composite index like city,age? This is due to the result of the leftmost prefix of the MySQL composite index. The simple understanding is to start with the leftmost combination. Not all queries that contain these three columns will use this composite index, but the following SQL will use this composite index:

[code]

SELECT * FROM mytable WHREE username= "admin" AND city= "Zhengzhou" SELECT * FROM mytable WHREE username= "admin"

The following will not be used:

The code is as follows:

SELECT * FROM mytable WHREE age=20 AND city= "Zhengzhou" SELECT * FROM mytable WHREE city= "Zhengzhou"

5. How to build an index

Now that we have learned to build an index, under what circumstances do we need to build an index? Generally speaking, columns that appear in WHERE and JOIN need to be indexed, but this is not entirely the case, because MySQL only uses indexes for =, BETWEEN,IN, and sometimes LIKE. For example:

The 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. cityboy 'Zhengzhou'

At this point, you need to index city and age, and since the userame of the mytable table also appears in the JOIN clause, it is also necessary to index it.

I just mentioned that only certain times of LIKE need to be indexed. Because MySQL does not use indexes when querying with the wildcard characters% and _. For example, the following sentence uses an index:

The code is as follows:

SELECT * FROM mytable WHERE username like'admin%'

The following sentence will not be used:

The code is as follows:

SELECT * FROM mytable WHEREt Name like'%admin'

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

VI. Shortcomings of the index

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:

1. Although indexing greatly improves query speed, it also slows down the speed of updating tables, such as INSERT, UPDATE, and DELETE on tables. Because when updating the table, MySQL not only saves the data, but also saves the index file.

two。 Create an index file that takes up disk space. In general, this problem is not too serious, but if you create multiple composite indexes on a large table, the index file will expand very quickly.

Indexing is only one factor in improving efficiency, and if your MySQL has tables with a large amount of data, you need to take the time to study how to build the best indexes, or optimize query statements.

7. When using an index, you should pay attention to:

There are some tips and considerations when using indexes:

1. The index will not contain columns with null values

As long as the column contains a null value, it will not be included in the index, and as long as one column in the composite index contains a null value, the column is invalid for the composite index. So let's not let the default value of the field be NULL when designing the database.

two。 Use short index

Index the string column and specify a prefix length if possible. For example, if you have a column of CHAR, and if most of the values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes can not only improve the query speed, but also save disk space and Imax O operations.

3. Index column sorting

The MySQL query uses only one index, so if the index is already used in the where clause, the columns in the order by will not use the index. Therefore, do not use the sort operation when the database default sorting can meet the requirements; try not to include multiple column sorting, and it is best to create a composite index on these columns if necessary.

4.like statement operation

In general, the use of like operations is discouraged, and if you have to, how to use it is also a problem. Like "% aaa%" does not use an index while like "aaa%" can use an index.

5. Do not operate on columns

The code is as follows:

Select * from users where YEAR (adddate)

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.