Shulou(Shulou.com)06/01 Report--

Now to introduce the database index, and its advantages and disadvantages. The characteristics and applications of MySQL index are described in detail. This paper analyzes how to avoid the failure of MySQL, how to use EXPLAIN to analyze query statements, and how to optimize the application of MySQL index. This article is excerpted from Section 8.9 of the MySQL 5 authoritative Guide (3rd).

Indexes are special files (indexes on InnoDB data tables are part of the table space) that contain reference pointers to all records in the data table.

Note: [1] indexing is not omnipotent! Indexes can speed up data retrieval operations, but slow down data modification operations. Each time the data record is modified, the index must be refreshed. To make up for this defect in some program, many SQL commands have a DELAY_KEY_WRITE entry. The purpose of this option is to temporarily stop MySQL from refreshing the incoming rows immediately after each new record is inserted and each existing one is modified by the command, and the refresh of the index will not take place until all records have been inserted / modified. In situations where many new records need to be inserted into a data table, the role of the DELAY_KEY_WRITE option will not always be obvious.

[2] in addition, the index takes up a considerable amount of space on the hard disk. Therefore, you should index only the most frequently queried and sorted data columns. Note that if a data column contains many duplicate contents, indexing it does not have much practical effect.

In theory, it is possible to create an index for each field in a data table, but MySQL limits the total number of indexes in the same data table to 16.

1. Index of InnoDB data table

Indexes are much more important to MyISAM data than InnoDB data tables. On InnoDB data tables, indexes are much more important to InnoDB data tables. On InnoDB data tables, indexes not only play a role in searching for data records, but also serve as the basis for row-level locking mechanisms. "data row-level locking" means that individual records being processed are locked during the execution of a transaction operation, preventing other users from accessing them. This locking will affect, but is not limited to, SELECT. LOCK IN SHARE MODE, SELECT... The FOR UPDATE command and the INSERT, UPDATE, and DELETE commands.

For the sake of efficiency, row-level locking of InnoDB data tables actually occurs on their indexes, not on the table itself. Obviously, the data row-level locking mechanism works only if the relevant data table has an appropriate index for locking.

two。 Limit

If there is an unequal sign (WHERE coloum! =...) in the query condition of the WEHERE clause MySQL will not be able to use the index

Similarly, if the function (WHERE DAY (column) =...) is used in the query condition of the WHERE clause. MySQL will also not be able to use the index

In JOIN operations (when you need to extract data from multiple data tables), MySQL can use indexes only if the primary and foreign keys have the same data type.

If you use the comparison operators like and REGEXP,MySQL in the query condition of the WHERE clause, you can use the index only if the first character of the search template is not a wildcard. For example, the index will be used if the query condition is LIKE 'abc%',MySQL; if the query condition is LIKE'% abc',MySQL, the index will not be used.

In the ORDER BY operation, MySQL uses the index only if the sort condition is not a query condition expression. (even so, in queries involving multiple tables, even if indexes are available, those indexes do little to speed up ORDER BY.)

If a data column contains many duplicate values, it will not work well even if it is indexed. For example, it is not necessary to create an index for a data column if it contains only values such as "0swap 1" or "YUnip N".

Index type:

General index, unique index, and primary index

1. General index

The only task of a normal index (an index defined by the keyword KEY or INDEX) is to speed up access to data. Therefore, it should only be for those that appear most frequently in the query condition (WHERE column =...) Or the data column in the sort condition (ORDER BY column) creates an index. Whenever possible, you should select the column with the most neat and compact data (such as a column of integer type) to create the index.

two。 Unique index

A normal index allows indexed data columns to contain duplicate values. For example, because a person may have the same name, the same name may appear two or more times in the same employee profile data table.

If you can be sure that a data column will only contain values that differ from each other, you should define it as a unique index with the keyword UNIQUE when you create an index on that data column. The advantages of this: first, it simplifies the management of the index by MySQL, which makes the index more efficient; second, when a new record is inserted into the data table, MySQL will automatically check whether the value of this field of the new record has already appeared in this field of a record; if so, MySQL will refuse to insert the new record. In other words, the unique index can guarantee the uniqueness of the data record. In fact, in many cases, the purpose of creating unique indexes is not to improve access speed, but to avoid data duplication.

3. Primary index

It has been stressed many times before that an index must be created for the primary key field, which is called the "primary index". The only difference between a primary index and a unique index is that the former uses the keyword PRIMARY instead of UNIQUE when defining it.

4. Foreign key index

If you define a foreign key constraint for a foreign key field, MySQL defines an internal index to help it manage and use foreign key constraints in the most efficient way.

5. Composite index

An index can overwrite multiple data columns, such as an INDEX (columnA, columnB) index. The characteristic of this kind of index is that MySQL can use one such index selectively. If the query operation requires only one index on the columnA data column, you can use the composite index INDEX (columnA, columnB). However, this usage applies only to combinations of data columns that rank first in a composite index. For example, INDEX (A, B, C) can be used as an index of An or (A, B), but not as an index of B, C, or (B, C).

6. The length of the index

When defining indexes for data columns of type CHAR and VARCHAR, you can limit the length of the index to a given number of characters (this number must be less than the maximum number of characters allowed in this field). The advantage of this is that you can generate an index file that is smaller in size but faster to retrieve. In most applications, the string data in the database is dominated by a variety of names, and setting the length of the index to 10 to 15 characters is enough to narrow the search to a few data records.

When creating indexes on data columns of type BLOB and TEXT, you must limit the length of the index; the maximum index length allowed by MySQL is 255characters.

6, full-text index

A normal index on a text field can only speed up the retrieval of the string that appears at the beginning of the field content (that is, the character at the beginning of the field content). If the field contains a large piece of text made up of several or even more words, a normal index is of little use. This retrieval is often in the form of LIKE% word%, which is complex for MySQL, and if the amount of data to be processed is large, the response time will be very long.

Such occasions are exactly where full-text indexing (full-text index) can give full play to its skills. When generating this type of index, MySQL will create a list of all the words that appear in the text, and the query operation will retrieve the relevant data records based on this list. The full-text index can be created with the data table, or it can be added later using the following command if necessary:

ALTER TABLE tablename ADD FULLTEXT (column1, column2)

With full-text indexing, you can use the SELECT query command to retrieve data records that contain one or more given words. Here is the basic syntax for this type of query command:

SELECT * FROM tablename

WHERE MATCH (column1, column2) AGAINST ('word1',' word2', 'word3')

The above command will query all the data records with word1, word2, and word3 in the column1 and column2 fields.

Note: InnoDB data tables do not support full-text indexing.

Optimization of query and index

Only when there is enough test data in the database, its performance test results have practical reference value. If there are only a few hundred data records in the test database, they are often all loaded into memory after the first query command is executed, which makes subsequent query commands execute very quickly-with or without indexes. Database performance test results are meaningful only when there are more than 1000 records in the database and the total amount of data exceeds the total memory on the MySQL server.

People can often get some help from the EXPLAIN SELECT command when they are not sure which data columns should be indexed.

This is really simply prefixing a normal SELECT command with an EXPLAIN keyword. With this keyword, MySQL will not execute the SELECT command, but analyze it. MySQL will list information such as the execution of the query and the indexes used, if any, in tabular form.

In the output of EXPLAIN command.

Column 1:

Is the name of the data tables read from the database, arranged in the order in which they were read.

Column 2:

The type column specifies the association (JOIN) between this data table and other data tables. Among the various types of associations, system is the most efficient, followed by const, eq_ref, ref, range, index, and All (All means that all records in the data table must be read once corresponding to each record in the upper data table-a situation that can often be avoided with an index).

Column 3:

The possible_keys data column gives each index that MySQL can choose when searching for data records.

Column 4:

The key data column is the index actually selected by MySQL, and the length of this index in bytes is given in the key_len data column. For example, for an index of an INTEGER data column, the byte length will be 4. If a composite index is used, you can also see which parts of it are used by MySQL in the key_len data column. As column 5:

As a general rule, the smaller the value in the key_len data column, the better (meaning faster).

Column 6:

The ref data column gives the name of the data column in another data table in the associated relationship.

Column 7:

The row data column is the number of data rows that MySQL is expected to read from this data table when executing the query. The product of all the numbers in the row data column gives us a rough idea of how many combinations this query needs to process.

Column 8:

The extra data column provides more information about the JOIN operation. For example, if MySQL must create a temporary data table when executing this query, you will see the word using temporary in the extra column.

#

Commonly used indexing commands:

1. Add PRIMARY KEY (primary key index)

Mysql > ALTER TABLE `table_ name` ADD PRIMARY KEY (`column`)

two。 Add UNIQUE (unique index)

Mysql > ALTER TABLE `table_ name` ADD UNIQUE (`column`)

3. Add INDEX (normal index)

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column`) index_name can be customized

4. Add FULLTEXT (full-text index)

Mysql > ALTER TABLE `table_ name` ADD FULLTEXT (`column`)

5. Add multi-column index (composite index)

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column1`, `column2`, `column3`)

6. The method of optimizing the index

Explain select XXXXX

7, see how to add index types

A meme MySQL > show create table table_name

B, use the Navicat tool to view

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.