MySQL index analysis and optimization design scheme

I. What is an index?

Indexes are used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly get to where the target record is without scanning any records. If a table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records.

Let's say we've created a table named people:

CREATE TABLE people (Peopleid SMALLINT not NULL, name CHAR (a) not null);

Then, we completely randomly insert 1000 different name values into the People table. Shows a small portion of the data file where the people table resides:

As you can see, the Name column in the data file does not have any definite order. If we create the index of the name column, MySQL will sort the name column in the index:

For each item in the index, MySQL internally holds a pointer to the location of the actual record in the data file. So if we want to find the name equals "Mike" Record Peopleid (SQL command for "Select Peopleid from people WHERE name=\ ' mike\ ';" ), MySQL can find the "Mike" value in the index of name, then go directly to the corresponding line in the data file and return exactly the line's Peopleid (999). In this process, MySQL only has to process one row to return the results. If there is no index to the "name" column, MySQL will scan all records in the data file, that is, 1000 records! Obviously, the fewer records that require MySQL to process, the faster it can complete the task.

Ii. Types of indexes

MySQL offers a variety of index types to choose from:

Normal index

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

Create an index, such as the name of the CREATE INDEX < index > on tablename (List of columns), modify the table, for example ALTER TABLE tablename ADD index [name of index] (list of columns); Specify an index when creating a table, such as C reate TABLE tablename ([...], index [names of indexes] (list of columns); Uniqueness Index

This index is basically the same as the previous "normal index", but there is one difference: all the values of an indexed column can only occur once, that is, they must be unique. A unique index can be created in the following ways:

Create an index, such as the name of the Create UNIQUE Index < index > on tablename (a list of columns), modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns), when creating a table Index, such as Create TABLE tablename ([...], UNIQUE [name of index] (list of columns); primary key

The primary key is a unique index, but it must be specified as "PRIMARY key". If you've ever used a auto_increment type column, you're probably already familiar with concepts like the primary key. The primary key is typically specified when creating the table, such as "CREATE TABLE TableName ([...], PRIMARY KEY (List of columns)"; ”。 However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table.

Full-Text Indexing

MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column. It can be created by the CREATE TABLE command or by the ALTER TABLE or CREATE INDEX command. For large datasets, it is faster to create a full-text index by using the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with a full-text index.

Third, single-row index and multi-column index

The index can be a single-column index or a multicolumn index. Let's take a concrete example to illustrate the differences between the two indexes. Suppose there is such a people table:

ALTER TABLE People ADD INDEX fname_lname_age (firstname,lastname,age);

Since the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName and then go to the appropriate LastName, and finally to the appropriate age. In the absence of any record of the scanned data file, MySQL correctly finds the target record of the search!

So, if you create a single-column index on the three columns of FirstName, LastName, and age, will the effect be the same as creating a multicolumn index of FirstName, LastName, and age? No, the two are totally different. When we execute the query, MySQL can use only one index. If you have three single-column indexes, MySQL will try to select one of the most restrictive indexes. However, even the most restrictive single-column index is limited in its ability to be significantly less than a multicolumn index on the three columns of FirstName, LastName, and age.

Four, the leftmost prefix

A multi-column index has another advantage, which is manifested by the concept of the leftmost prefix (leftmost prefixing). Continuing with the previous example, we now have a multi-column index on the FirstName, LastName, and age columns, which we call the index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:

Firstname,lastname,agefirstname,lastnamefirstname

On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations. The following queries all have the ability to use this Fname_lname_age index:

Table Type Possible_keys key Key_len ref rows Extra

People ref fname_lname_age Fname_lname_age 102 Const,const,const 1 Where used

Let's take a look at the meaning of this explain analysis result.

Table: This is the name of the watch. Type: Types of connection operations. The following is a description of the MySQL documentation about the ref connection type:

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

In this example, because the index is not a unique type, ref is the best connection type we can get.

If explain shows that the connection type is "All" and you do not want to select most of the records from the table, then MySQL will be very inefficient because it will scan the entire table. You can add more indexes to solve this problem. For more information, see the MySQL manual for instructions.

Possible_keys:

The name of the index that may be available. The index name here is the index nickname specified when the index was created, and if the index does not have a nickname, the first column in the index is displayed by default (in this case, it is "FirstName"). The meaning of the default index name is often not obvious.

Key:

It shows the name of the index that MySQL actually uses. If it is empty (or null), then MySQL does not use the index.

Key_len:

The length of the part to be used in the index, in bytes. In this example, Key_len is 102, where FirstName accounts for 50 bytes, lastname accounts for 50 bytes, and age is 2 bytes. If MySQL only uses the FirstName portion of the index, then Key_len will be 50.

Ref

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

Rows

MySQL considers the number of records that it must scan before it can find the correct results. Obviously, the ideal number here is 1.

Extra:

Many different options may appear here, most of which will adversely affect the query. In this case, MySQL just reminds us that it will restrict the search result set with a WHERE clause.

Vii. Disadvantages of the index

So far, we've been talking about the advantages of indexes. In fact, indexes also have drawbacks.

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

Second, indexes can slow down operations that require writing data, such as delete, update, and insert operations. This is because MySQL not only writes the change data to the data file, but it also writes the changes to the index file.

Conclusion

In large databases, indexing is a key factor in increasing speed. No matter how simple the table structure is, a 500000-row table scan operation will not be fast anyway. If you have such a large-scale table on your site, you really should take some time to analyze which indexes you can use and whether you can rewrite the query to optimize your application. Also note that this article assumes that the MySQL you are using is version 3.23, and some queries cannot be performed on MySQL version 3.22.

MySQL index analysis and optimization design scheme