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

Mysql database index application example analysis, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

I. the concept of index

An index is a way to speed up the retrieval of data in a table. The index of is similar to the index of books. In books, the index allows users to quickly find the information they need without flipping through the whole book. In a database, indexes also allow database programs to quickly find data in tables without having to scan the entire database.

Second, the characteristics of the index

1. The index can speed up the retrieval of the database.

two。 The index reduces the speed of database insertion, modification, deletion and other maintenance tasks.

3. The index is created on the table, not on the view

4. Indexes can be created either directly or indirectly

5. You can use indexes in optimized hiding

6. Use the query processor to execute SQL statements, using only one index at a time on a table

7. Other

Third, the advantages of index

1. Create a uniqueness index to ensure the uniqueness of each row of data in the database table

two。 Greatly accelerate the speed of data retrieval, which is also the main reason for creating an index

3. The connection between the accelerometer and the table is particularly meaningful in achieving the referential 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 a query.

5. By using the index, the optimization hider can be used in the process of query to improve the performance of the system.

IV. Shortcomings of the index

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

two。 The index needs to occupy the physical space, in addition to the data table occupies the data space, each index also takes up a certain amount of physical space, if you want to establish a clustered index, then the space required will be more.

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

V. Index classification

1. Create indexes directly and indirectly

Create the index directly: CREATE INDEX mycolumn_index ON mytable (myclumn)

Indirect index creation: define primary key constraints or unique key constraints, and indexes can be created indirectly

two。 General index and unique index

General index: CREATE INDEX mycolumn_index ON mytable (myclumn)

Unique index: ensures that all data in the index column is unique and can be used for both clustered and non-clustered indexes

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable (mycolumn)

3. Single index and composite index

Single index: that is, non-composite index

Composite index: also known as a composite index, which contains multiple field names and up to 16 fields in the index establishment statement.

CREATE INDEX name_index ON username (firstname,lastname)

4. Clustered index and non-clustered index (clustered index, cluster index)

Clustered index: physical index, which is the same as the physical order of the base table, and the order of data values is always in order.

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable (mycolumn) WITH

ALLOW_DUP_ROW (allows clustered indexes with duplicate records)

Non-clustered index: CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable (mycolumn)

VI. Use of the index

1. When the field data is updated less frequently, the query is used more frequently and there are a large number of duplicate values, it is recommended to use clustered index.

two。 Multiple columns are often accessed at the same time, and each column contains duplicate values. Consider establishing a combined index.

3. The leading column of the composite index must be well controlled, otherwise it can not play the effect of the index. If the leading column is not in the query condition when querying, the composite index will not be used. The leading column must be the most frequently used column

4. Before the multi-table operation is actually performed, the query optimizer will list several groups of possible join schemes according to the join conditions and find out the best one with the least system overhead. The join condition should fully consider the table with index and the table with a large number of rows; the choice of inner and outer surface can be determined by the formula: the number of matching rows in the outer table * the number of times of each search in the inner table, and the minimum product is the best scheme.

Any operation on a column in the 5.where clause is calculated column by column while sql is running, so it has to perform a table search without using the index above the column; if these results are available at query compilation time, they can be optimized by the sql optimizer, using indexes to avoid table search (for example: select * from record where substring (card_no,1,4) = '5378'

& & select * from record where card_no like '5378%') any operation on a column will result in a table scan, including database functions, evaluation expressions, and so on. When querying, move the operation to the right of the equal sign as much as possible

The 'in'' in the 6.where condition is logically equivalent to 'or', so the parser converts in (0j1) to column=0 or column=1 to execute. We expect it to look up each or clause separately, and then add the results, so that it can take advantage of the index on the column; but in fact, it adopts the "or strategy", that is, it first takes out the rows that satisfy each or clause, stores them in the worksheet of the tempdb, builds a unique index to remove the duplicate rows, and finally calculates the results from the temporary table. Therefore, the actual process does not take advantage of indexes on column, and the completion time is also affected by the performance of the tempdb database. In and or clauses often use worksheets to invalidate the index; if you do not produce a large number of duplicate values, you can consider taking the clause apart; the split clause should contain the index

7. Be good at using stored procedures, which make sql more flexible and efficient

After reading the above, have you mastered the method of example analysis of mysql database index application? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.