MySQL Index analysis and optimization

mysql| Index | Optimization One, what is index?

Indexes are used to quickly look for records with a specific value, and all MySQL indexes are saved as a B-tree. Without an index, MySQL must begin scanning all records of the entire table from the first record until a record is found that meets the requirements. The greater 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 the location of the target record without scanning any records. If the table has 1000 records, the index lookup record is at least 100 times times faster than the sequential scan record.

Let's say we created a table named people:


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


Then we completely randomly insert 1000 different name values into the People table. The following figure shows a small portion of the data file for the People table:




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




For each item in the index, MySQL internally saves it a "pointer" to the location of the actual record in the data file. So, if we want to find the Peopleid (SQL command for the Select Peopleid from people WHERE Name= ' Mike ') that name equals "Mike" records , MySQL is able to find the "Mike" value in the index of name and then go directly to the corresponding row in the data file, returning exactly the Peopleid (999) of the row. In this process, MySQL can return results only by processing a single line. If there is no index for the "name" column, MySQL scans all the records in the data file, that is, 1000 records! Obviously, the fewer records that need to be processed by MySQL, 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 uniqueness, such as restrictions. Normal indexes can be created in several ways:
Create indexes, such as the name of the CREATE INDEX < index > on tablename (a list of columns);
Modify a table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);
Specify the index when creating the table, such as CREATE TABLE TableName ([...], index [first name] (List of columns));

Uniqueness Index
This index is basically the same as the previous "normal index", but there is a difference: all values of an indexed column can only appear once, that is, must be unique. Uniqueness indexes can be created in several ways:
Create an index, such as the name of the Create UNIQUE Index < index > on tablename (a list of columns);
Modify a table, such as ALTER TABLE TableName ADD UNIQUE [name of index] (list of columns);
Specify the index when creating the table, such as CREATE TABLE TableName ([...], UNIQUE [name of index] (List of columns));

Primary key
A primary key is a unique index, but it must be specified as "PRIMARY key." If you've ever used a auto_increment type of column, you're probably already familiar with the concept of a primary key. Primary keys are typically specified when creating tables, such as "CREATE TABLE TableName ([...], PRIMARY key (List of columns)"; ”。 However, we can also add a primary key by modifying the table, such as "ALTER table tablename ADD PRIMARY key (a list of columns);" ”。 Each table can have only one primary key.

Full-text indexing
MySQL supports full-text indexing and Full-text search starting with version 3.23.23. In MySQL, the index type of the Full-text index is fulltext. Full-text indexing can be created on columns of varchar or text type. It can be created from the CREATE TABLE command, or through ALTER TABLE or CREATE INDEX commands. For a large dataset, it is faster to create a FULL-TEXT index through the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with Full-text indexing. The discussion below in this article no longer involves full-text indexing, and for more information, see MySQL documentation.
Three-row index and multiple-column index

An index can be a single-column index or a multiple-column index. Here's a concrete example to illustrate the difference between the two indexes. Suppose there is such a people table:


CREATE TABLE people (Peopleid SMALLINT NOT NULL auto_increment, FirstName char is NOT NULL, LastName char () is not NULL, Age SMALLINT NOT NULL, Townid SMALLINT NOT NULL, PRIMARY KEY (Peopleid));


Here is the data we inserted into this people table:




The data fragment contains four people named "Mikes" (two of whom are Sullivans, two surnamed McConnells), two people of 17 years of age and a different name for Joe Smith.

The main purpose of this table is to return the corresponding Peopleid based on the specified user's last name, name, and age. For example, we might need to look up the Peopleid (SQL command for select Peopleid from people WHERE Firstname= ' Mike ' and Lastname= ') whose name is Mike Sullivan, age 17. Sullivan ' and age=17; Since we don't want MySQL to scan the entire table every time we execute a query, we need to consider using an index.

First, we can consider creating indexes on individual columns, such as FirstName, LastName, or age columns. If we create the index of the FirstName column (ALTER TABLE people ADD index FirstName (firstname), MySQL will quickly limit the search to those firstname= ' Mike's record, and then search for other conditions on this "intermediate result set": It first excludes those records that LastName not equal to "Sullivan", and then excludes those whose age does not equal 17. When the record satisfies all the search criteria, MySQL returns the final search results.

Because of the index of the FirstName column, MySQL is much more efficient than a full scan of the table, but we require that the number of records that MySQL scans is still far exceed what is actually needed. Although we can delete the index on the FirstName column and then create an index of the LastName or the age column, it seems generally that the efficiency of creating index searches is still the same regardless of which column.

To improve search efficiency, we need to consider using multiple-column indexes. If you create a multiple-column index for the three columns of FirstName, LastName, and age, MySQL can find the right results with just one retrieval! Here is the SQL command to create this multiple-column index:



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


Because the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName, then go to the appropriate LastName, and finally go to the appropriate age. Without scanning the data file for any one record, 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, does the effect look like a multi-column index that creates a firstname, LastName, and age? The answer is no, the two are completely different. MySQL can only use one index when we execute the query. 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 indexes are certainly far less restrictive than the FirstName, LastName, age three columns.

Four, the left prefix

There is another advantage to a multiple-column index, which is embodied by the concept of the leftmost prefix (leftmost prefixing). Continuing to consider the previous example, we now have a FirstName index on the LastName, The Age column, which we call the index fname_lname_age. When a 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 we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) The combination of these columns. The following queries are able to use this Fname_lname_age index:



SELECT Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan ' and age= ' 17 '; SELECT Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan '; SELECT Peopleid from people WHERE firstname= ' Mike '; The following queries cannot use of the index at All:select Peopleid from people WHERE lastname= ' Sullivan '; SELECT Peopleid from people WHERE age= ' 17 '; SELECT Peopleid from people WHERE lastname= ' Sullivan ' and age= ' 17 ';


V. Select index Columns

In the performance tuning process, it is one of the most important steps to choose which columns to create indexes on. There are two main types of columns that you can consider using indexes: columns that appear in the WHERE clause, columns that appear in the join clause. See the following query:



SELECT Age # # No index from people WHERE firstname= ' Mike ' # Consider using indexes and lastname= ' Sullivan ' # consider using indexes


This query is slightly different from the previous query, but it still belongs to a simple query. Because age is referenced in the Select section, MySQL does not use it to restrict column selection operations. Therefore, it is not necessary to create an index of the age column for this query. The following is a more complex example:



SELECT people.age, # #不使用索引 Town.name # #不使用索引 from people left JOIN town on People.townid=town.townid # #考虑使用索引 WHERE firstn Ame= ' Mike ' #考虑使用索引 and lastname= ' Sullivan ' #考虑使用索引


As in the previous example, because FirstName and LastName appear in the WHERE clause, the two columns still have the necessary to create the index. In addition, because the Townid of the town table is listed in the JOIN clause now, we need to consider creating the index of the column.

So, can we simply assume that each column that appears in the WHERE clause and join clause should be indexed? Pretty much, but not quite. We must also consider the type of operator that compares the columns. MySQL uses indexes only on the following operators: <,<=,=,>,>=,between,in, and some times like. The case where an index can be used in a like operation is when another operand does not begin with a wildcard character (% or _). For example, "Select Peopleid from people WHERE firstname like ' mich% ';" This query will use the index, but "select Peopleid from people WHERE firstname like '%ike ';" This query does not use indexes.

Vi. Analysis of index efficiency

Now that we know how to choose an indexed column, we can't tell which is the most effective. MySQL provides a built-in SQL command to help us complete this task, which is the explain command. The general syntax for the EXPLAIN command is: EXPLAIN <sql command >. You can find more instructions for this command in the MySQL documentation. Here is an example:



EXPLAIN SELECT Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan ' and age= ' 17 ';


This command returns the following analysis result:


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

Now let's take a look at the implications of this explain analysis result.


Table: This is the name of the watch.
Type: The types of connection operations. Here is a description of the MySQL document about the type of ref connection:

"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 join operation cannot select a unique row based on the key value), then MySQL uses the ref join type. If the key used by the join operation matches only a small number of records, ref is a good connection type. ”

In this case, 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 don't want to select most of the records from the table, then MySQL will be very inefficient because it scans the entire table. You can add more indexes to solve this problem. For more information, please refer to the MySQL manual description.

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 name of the first column in the index (in this case, "FirstName") is displayed by default. 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), MySQL does not use the index.

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

Ref
It displays the name of the column (or the word "const"), and MySQL selects the rows based on those columns. In this case, MySQL selects rows based on three constants.

Rows
The number of records that MySQL believes it must scan before it finds the correct result. Obviously, the ideal figure here is 1.

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

Vii. Disadvantages of indexing

So far, we've been talking about the advantages of indexing. 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 for 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 reduce their speed for operations that need to write 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.

"Closing" In a large database, indexing is a key factor in improving speed. No matter how simple the structure of the table is, a 500000-row table scan operation will not be quick at any rate. If you have such a large scale on your site, you really should take some time to analyze which indexes you can use and consider whether you can rewrite the query to optimize your application. For more information, see MySQL Manual. Also note that this article assumes that you are using MySQL is version 3.23, some of the query can not be implemented on the 3.22 version of MySQL.