Index analysis and optimization solution _ MySQL

Index analysis and optimization solution 1. what is index?

Indexes are used to quickly search for records with specific values. all MySQL indexes are saved as B-trees. If no index exists, MySQL must scan all the records of the entire table from the first record until the required records are found. The more records in the table, the higher the operation cost. If an index has been created on the column used as a search condition, MySQL can quickly obtain the location of the target record without scanning any records. If the table has 1000 records, the index search records should be at least 100 times faster than the sequential scan records.

Suppose we have created a table named "people:

Create table people (peopleid smallint not null, name CHAR (50) not null );

Then, we randomly insert 1000 different name values into the people table. Displays a small part of the data file of the people table:

We can see that there is no clear order for the name column in the data file. If we create an index for the name column, MySQL will sort the name column in the index:

For each item in the index, MySQL internally stores the "pointer" of the actual record location in a data file for it ". Therefore, if we want to find the peopleid of the record whose name is equal to "Mike" (the SQL command is "SELECT peopleid FROM people WHERE name =/'Mike /';"), mySQL can search for the "Mike" value in the name index, and then directly go to the corresponding row in the data file to return the peopleid (999) of the row accurately ). In this process, MySQL only needs to process one row to return results. If there is no index for the "name" column, MySQL will scan all records in the data file, that is, 1000 records! Obviously, the less records that need to be processed by MySQL, the faster it can complete the task.

II. index types

MySQL provides multiple index types:

Common index

This is the most basic index type, and it has no limitations such as uniqueness. Common indexes can be created in the following ways:

CREATE an INDEX, for example, CREATE INDEX <索引的名字> ON tablename (column list );
Modify a TABLE, such as alter table tablename add index [INDEX name] (column list );
Specify an INDEX when creating a TABLE, for example, create table tablename ([...], INDEX [INDEX name] (column list ));

Unique index

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

CREATE an INDEX, for example, CREATE UNIQUE INDEX <索引的名字> ON tablename (column list );
Modify a TABLE, such as alter table tablename add unique [index name] (column list );
Specify an index when creating a TABLE, for example, create table tablename ([...], UNIQUE [index name] (column list ));

Primary key

A primary key is a unique index, but it must be specified as a "primary key ". If you have used columns of the AUTO_INCREMENT type, you may already be familiar with primary keys and other concepts. The primary key is generally specified during TABLE creation, for example, "create table tablename ([...], primary key (column list ));". However, we can also ADD a primary key by modifying the TABLE, for example, "alter table tablename add primary key (column list );". Each table can have only one primary key.

Full-text index

MySQL supports full-text indexing and full-text retrieval from version 3.23.23. In MySQL, the full-text index type is FULLTEXT. Full-TEXT indexes can be created on VARCHAR or TEXT columns. It can be created using the create table command or the alter table or create index command. For large-scale datasets, using the alter table (or create index) command to CREATE a full-text INDEX is faster than inserting a record into an empty TABLE with a full-text INDEX.
III. single column index and multi-column index

An index can be a single-column index or multiple-column index. The following is an example to illustrate the differences between the two indexes. Suppose there is a people table:

Alter table people add index fname_lname_age (firstname, lastname, age );

Because the index file is saved in B-tree format, MySQL can immediately convert it to the appropriate firstname, then to the appropriate lastname, and finally to the appropriate age. Without scanning any record of the data file, MySQL finds the target record correctly!

So, if you create a single column index on the columns firstname, lastname, and age respectively, will the effect be the same as creating a multi-column index with firstname, lastname, and age? The answer is no. the two are completely different. When we perform a query, MySQL can only use one index. If you have three single-column indexes, MySQL will try to select the most restrictive index. However, even if it is the most restrictive single-column index, its capacity is certainly far lower than the multiple-column index of the three columns firstname, lastname, and age.

4. leftmost prefix

Multi-column index has another advantage, which is embodied by the concept of Leftmost Prefixing. Continue to consider the previous example. now we have a multiple-column index on the firstname, lastname, and age columns. We call this index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:

Firstname, lastname, age
Firstname, lastname
Firstname
On the other hand, it is equivalent to the index created on the combination of columns (firstname, lastname, age), (firstname, lastname), and (firstname. All of the following queries can use this fname_lname_age index:

Table type possible_keys key key_len ref rows Extra
People ref fname_lname_age 102 const, const, const 1 Where used

Next, let's take a look at the meaning of the EXPLAIN analysis result.

Table: The name of the table.

Type: the type of the connection operation. The following describes the ref connection type in the MySQL documentation:

"For the combination of each record and another table, MySQL reads all records with matching index values from the current table. If the connection operation only uses the leftmost prefix of the KEY, or if the KEY is not of the UNIQUE or primary key type (in other words, if the connection operation cannot select a UNIQUE row based on the KEY value ), mySQL uses the ref connection type. If the key used for the connection operation matches only a small number of records, the ref is a good connection type ."

In this example, since the index is not of the UNIQUE type, ref is the best connection type we can get.

If the EXPLAIN command shows that the connection type is "ALL" and you do not want to select a majority of records from the table, the MySQL operation efficiency will be very low because it needs to scan the entire table. You can add more indexes to solve this problem. For more information, see the instructions in the MySQL manual.

Possible_keys:

Possible index names. The index name is the index nickname specified during index creation. if the index does not have a nickname, the name of the first column in the index is displayed by default (in this example, it is "firstname "). The meaning of the default index name is often not obvious.

Key:

It displays the name of the index actually used by MySQL. If it is NULL, MySQL does not use an index.

Key_len:

The length of the part used in the index, in bytes. In this example, key_len is 102, of which firstname occupies 50 bytes, lastname occupies 50 bytes, and age occupies 2 bytes. If MySQL only uses the firstname part of the index, key_len will be 50.

Ref:

It displays the column name (or the word "const"). MySQL selects rows based on these columns. In this example, MySQL selects rows based on three constants.

Rows:

The number of records that MySQL considers to be scanned before finding the correct results. Obviously, the ideal number here is 1.

Extra:

There may be many different options, most of which will have a negative impact on the query. In this example, MySQL only reminds us that it will use the WHERE clause to limit the search result set.

VII. index disadvantages

So far, we have discussed the advantages of indexes. In fact, indexes also have disadvantages.

First, the index occupies disk space. Generally, this problem is not very prominent. However, if you create an index for each possible combination of columns, the size of the index file will grow much faster than that of the data file. If you have a large table, the size of the index file may reach the maximum file size allowed by the operating system.

Second, for operations that require data writing, such as DELETE, UPDATE, and INSERT operations, indexes will reduce their speed. This is because MySQL not only writes the changed data to the data file, but also writes the changes to the index file.

[Conclusion]

In large databases, indexing is a key factor to increase the speed. No matter how simple the table structure is, a 500000-row table scan operation will not be fast at any time. If you have such a large table on your website, you must take some time to analyze which indexes can be used and check whether you can rewrite the query to optimize the application. Note that this document assumes that you are using MySQL 3.23, and some queries cannot be executed on MySQL 3.22.