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

This article introduces how to establish a database index in MYSQL, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

1. By creating a uniqueness index, we can ensure the uniqueness of each row of data in the database table.

2. It can greatly accelerate the speed of data retrieval, which is also the main reason for creating an index.

3. The connection between the meter and the table can be accelerated, which is of great significance in realizing the integrity of the data.

4. When using grouping and sorting clauses for data retrieval, you can also significantly reduce the time of grouping and sorting in the query.

What are the disadvantages of the index?

1. It takes time to create and maintain an index, which increases as the amount of data increases.

2. In addition to the data table occupies data space, each index also takes up a certain amount of physical space. If you want to establish a clustered index, you will need more space.

3. When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

What are the types of indexes?

1. General index

This is the most basic index type, and it has no restrictions such as uniqueness.

2. Uniqueness index

This index is basically the same as the previous "normal index", but there is one difference: all values of the index column can only appear once, that is, they must be unique.

3. Primary key

It is a special unique index and no null values are allowed.

4. Full-text index

MySQL supports full-text indexing and full-text retrieval since version 3.23.23.

Single-column index and combined index:

A single-column index is to build an index on a single field.

A composite index is an index created on two or more columns. When searching, it is best to create a composite index on two or more columns as a key value.

What are the considerations for building and using indexes:

1. The index should be based on the fields that often perform select operations. This is because, if these columns are rarely used, the presence or absence of indexes does not significantly change the query speed. On the contrary, due to the increase of the index, the maintenance speed of the system is reduced and the space requirement is increased.

2. The index should be based on a field with a unique value. This is the best way to get the most out of the index. Such as the id field of the primary key, the unique name name field, and so on. If the index is based on fields with fewer unique values, such as gender gender fields, few category fields, and so on, just indexing makes little sense.

3. Columns defined as text, image, and bit data types should not be indexed. Because the amount of data in these columns is either quite large or very few.

4. When the modification performance is much greater than the retrieval performance, you should not create an index. Modification performance and retrieval performance contradict each other. When the index is added, the retrieval performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, indexes should not be created when modification performance is much greater than retrieval performance.

5. Columns that appear in WHERE and JOIN need to be indexed.

6. When querying with wildcard characters% and _, the MySQL index is invalid. But this index is valid: select * from tbl1 where name like 'xxx%', so it's important to write your SQL carefully.

An example is given to illustrate in detail the difference between single-column index and combined index and some details in the use of index.

Build a table:

CREATE TABLE myIndex (

I_testID INT NOT NULL AUTO_INCREMENT

Vc_Name VARCHAR (50) NOT NULL

Vc_City VARCHAR (50) NOT NULL

I_Age INT NOT NULL

I_SchoolID INT NOT NULL

PRIMARY KEY (i_testID)

);

Among the 10000 records, there are 5 vc_Name= "erquan" records distributed in 7, 8 and 8, but the combination of city,age,school is different.

Take a look at this T-SQL:

SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City=' Zhengzhou 'AND i_Age=25

First consider creating a single column index:

An index is established on the vc_Name column. When executing T-SQL, MYSQL quickly targets five records in vc_Name=erquan and takes them out and puts them in an intermediate result set. In this result set, we first exclude the records whose vc_City is not equal to "Zhengzhou", then exclude the records whose i_Age is not equal to 25, and finally screen out the only records that meet the criteria.

Although the index has been established on vc_Name, and MYSQL does not have to scan the whole table when querying, the efficiency has been improved, but it is still a long way from our requirements. Similarly, the efficiency of single-column indexes established in vc_City and i_Age is similar.

In order to further extract the efficiency of MySQL, it is necessary to consider the establishment of a composite index. Is to build the vc_Name,vc_City,i_Age into an index:

ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name (10), vc_City,i_Age);-- notice that when you build a table, the vc_Name length is 50, so why use 10 here? 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.

When performing T-SQL, MySQL does not need to scan any records to find the only record!

Someone must ask, if you set up a single-column index on vc_Name,vc_City,i_Age and let the table have three single-column indexes, will the query be as efficient as the combined index mentioned above? Hey, hey, it's very different, much lower than our combined index ~ ~ although there are three indexes at this time, MySQL can only use the single-column index that it seems to be the most efficient.

The establishment of such a combined index is actually equivalent to the establishment of separate indexes

Vc_Name,vc_City,i_Age

Vc_Name,vc_City

Vc_Name

Such three combined indexes! Why is there no composite index like vc_City,i_Age? This is due to the result of the leftmost prefix of the combined 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 T-SQL will:

SELECT * FROM myIndex WHREE vc_Name= "erquan" AND vc_City= "Zhengzhou"

SELECT * FROM myIndex WHREE vc_Name= "erquan"

The following will not be used:

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City= Zhengzhou

SELECT * FROM myIndex WHREE vc_City= Zhengzhou

On how to build a database index in MYSQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.