mysql advanced (2) index simple tutorial

Mysql Index Simple Tutorial

Basic Concepts

Indexing refers to storing the content of field A that you set as an index in an independent interval S, which contains only the content of this field. When searching for the contents of this field A, it will be searched directly from this independent interval instead of searching in the data table. After finding these qualified fields, read the physical address of the real data record pointed to by field A, and then output the corresponding data content. If you are looking for a field that is not indexed, it will be searched from the data table. Because the data table has many irrelevant fields, the database program will not omit or search them. It takes a certain amount of resources to determine those irrelevant fields and jump in the record multiple times. Of course, the more indexes you set, the better. Because the indexes are placed in this independent interval S, the larger the independent interval S, the greater the search resources. If you only have one field indexed, then your search on this field is very fast.

The purpose of creating an index is to speed up the search or sorting of records in the table. Setting an index for a table comes with a price: First, it increases the storage space of the database, and second, it takes more time to insert and modify data (because the index also changes accordingly).

The advantage of the index is that it can sort the specified columns and improve the speed of retrieval.

A simple example:

The data in a certain column is

id name

12 Xiao Li

10 Xiaolong

5 Xiaoqing

99 Xiaohong

After creating an index on the id column, an index table will be generated

id index

5 3

10 2

12 1

99 4

When querying where id =10, the index table is used. Because below 10 is 5. Therefore, the table scan operation is no longer performed. Return the second piece of data, corresponding to the second row of the main table. This improves the query speed. If no index is added, the entire main table will be scanned.

You need to search Baidu for related information such as the type of index and which columns need to be indexed. What I tell you here are some basic concepts.

The functions, advantages and disadvantages of database indexes

Why create an index? This is because creating indexes can greatly improve system performance.

First, by creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Third, it can speed up the connection between tables, which is particularly meaningful in achieving referential integrity of data.

Fourth, when using grouping and sorting clauses for data retrieval, the time for grouping and sorting in queries can also be significantly reduced.

Fifth, by using indexes, you can use optimization hiders during the query process to improve system performance.

Some people may ask: There are so many advantages to adding an index, why not create an index for every column in the table? Although this idea is reasonable, it is also one-sided. Although indexes have many advantages, it is very unwise to add an index to every column in the table. This is because there are many downsides to adding an index.

First, creating and maintaining indexes takes time, and this time increases as the amount of data increases.

Second, the index needs to occupy physical space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If you want to build a clustered index, the space required will be larger. .

Third, when adding, deleting, and modifying data in the table, the index must be dynamically maintained, which reduces the data maintenance speed.

Indexes are built on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can be indexed and which columns cannot be indexed. Generally speaking, indexes should be created on these columns, for example:

On columns that are often searched, you can speed up the search;

On the column that is the primary key, force the column The uniqueness and arrangement structure of the data in the organization table;

are often used in the columns of the connection. These columns are mainly foreign keys, which can speed up the connection;

In the columns that are often used in the connection, Create an index on the column that needs to be searched based on the range, because the index has been sorted and its specified range is continuous;

Create indexes on columns that often need to be sorted, because the index has been sorted, so that queries can use the sorting of the index to speed up sorting query time;

Create indexes on columns that are often used in the WHERE clause , speed up the judgment of conditions.

Similarly, indexes should not be created for some columns. Generally speaking, these columns that should not be indexed have the following characteristics:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, indexing or not indexing does not improve query speed. On the contrary, due to the addition of indexes, the maintenance speed of the system is reduced and the space requirements are increased.

Second, indexes should not be added to columns with few data values. This is because, since these columns have very few values, such as the gender column of the personnel table, in the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the data that needs to be searched in the table. The proportion of rows is huge. Increasing the index does not significantly speed up retrieval.

Third, indexes should not be added to columns defined as text, image and bit data types. This is because the data volume of these columns is either quite large or has very few values.

Fourth, when the modification performance is far greater than the retrieval performance, the index should not be created. This is because modification performance and retrieval performance are contradictory to each other. When adding indexes, retrieval performance will be improved, but modification performance will be reduced. When reducing indexes, modification performance will increase and retrieval performance will decrease. Therefore, when modification performance is much greater than retrieval performance, indexes should not be created.

Methods to create indexes and characteristics of indexes

Methods to create indexes

There are many ways to create indexes, including direct index creation methods and indirect index creation methods method. Create an index directly, such as using the CREATE INDEX statement or use the Create Index Wizard, or create an index indirectly, such as defining a primary key constraint or a unique key constraint in a table, and the index is also created at the same time.

Although both methods can create indexes, the specific contents of creating indexes are different.

Use the CREATE INDEX statement or use the Create Index Wizard to create an index. This is the most basic way to create an index, and this method is the most flexible. You can customize the index to meet your needs.

When using this method to create an index, you can use many options, such as specifying the fullness of the data page, sorting, sorting statistics, etc., so that you can optimize the index. Using this method, you can specify the type, uniqueness and compositeness of the index, that is, you can create a clustered index or a non-clustered index, you can create an index on one column or on two Or create an index on more than two columns.

You can also create an index indirectly by defining primary key constraints or unique key constraints. A primary key constraint is a logic that maintains data integrity by restricting records in a table to have the same primary key record. When creating a primary key constraint, the system automatically creates a unique clustered index.

Although, logically, the primary key constraint is an important structure, but in terms of physical structure, the structure corresponding to the primary key constraint is a unique clustered index. In other words, in physical implementation, there is no primary key constraint, but only a unique clustered index.

Similarly, when creating a unique key constraint, an index is also created. This index is a unique non-clustered index. Therefore, when using constraints to create an index, the type and characteristics of the index have basically been determined, and there is less room for user customization.

When defining a primary key or unique key constraint on a table, if the table already has a standard index created using the CREATE INDEX statement, the index created by the primary key constraint or unique key constraint will overwrite the previously created index. Standard index. In other words, indexes created by primary key constraints or unique key constraints have a higher priority than indexes created using the CREATE INDEX statement.

Characteristics of index

Index has two characteristics, namely unique index and composite index.

Unique index ensures that all data in the index column is unique and does not contain redundant data. If the table already has a primary key constraint or a unique key constraint, SQL Server automatically creates a unique index when the table is created or modified.

However, if uniqueness must be guaranteed, a primary key constraint or a unique key constraint should be created instead of a unique index. When creating a unique index, you should carefully consider these rules: When creating a primary key constraint or a unique key constraint on a table, SQL Server automatically creates a unique index;

If the table already contains data, Then when creating an index, SQL Server checks the redundancy of the data already in the table; whenever an insert statement is used to insert data or a modify statement is used to modify data, SQL Server checks the data for redundancy: If there are redundant values, then SQL Server cancels the execution of the statement and returns an error message;

Ensure that each row of data in the table has a unique value, which ensures that each entity can be uniquely confirmed; only when the entity can be guaranteed Create a unique index on the complete column. For example, you cannot create a unique index on the name column in the personnel table because people can have the same name.

A composite index is an index created on two or more columns. When searching, when two or more columns serve as a key value, it is best to create a composite index on these columns. When creating a composite index, you should consider these rules: Up to 16 columns can be combined into a single composite index, and the total length of the columns that make up the composite index cannot exceed 900 bytes, which means that the length of the composite column cannot be too long;

In a composite index, all columns must come from the same table, and composite columns cannot be created across tables; in a composite index, the order of the columns is very important, so the order of the columns must be carefully arranged. In principle, the most unique column should be defined first. For example, the index on (COL1, COL2) is different from the index on (COL2, COL1) because the order of the columns of the two indexes is different;

In order for the query optimizer to use a composite index, the WHERE clause in the query statement must refer to the first column in the composite index; when there are multiple key columns in the table, the composite index is very useful; using a composite index can improve Query performance, reduce the number of indexes created on a table.

Type of index

Non-unique index means that the values ​​in this index are allowed to be repeated. Relative to the unique index, the values ​​in this index are not allowed to be repeated.

A simple example is like our ID card. If stored in the database. If you create an index on the name, it is a non-unique index because a person with the same name exists. If you create an index on the ID number, it will be a unique index. Because the numbers are repeated, it will be troublesome.

The above is the content of mysql advanced (2) index simple tutorial. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!