MySQL index analysis and optimization
Indexing is used to quickly find records with specific values, all MySQL indexes are saved in the form of B-tree. If there is no index, Mysql must start scanning all records of the entire table until you find a record that meets the requirements from the first record. The more records in the table, the higher the cost of this operation. If an index has been created as the column of the search criteria, MySQL does not need to scan any records to quickly get the location where the target recording is located. If there is a 1000 records, it is 100 times faster than the order scan record by index lookup record.
Suppose we have created a table named people:
Create Table People (PeopleId Smallint Not Null, Name Char (50) Not Null;
Then, we completely randomly insert 1000 different Name values into the people table. The figure below shows a small part of the data file where the People table is:
It can be seen that there is no clear order in the NAME column in the data file. If we have created an index of the Name column, MySQL will sort the Name column in the index:
For each of the index, MySQL saves a "pointer" that actually records the location in a data file internally. Therefore, if we want to find the nameID (SQL command "Select PeopleID from People WHERE Name = 'Mike';"), MySQL can find the "Mike" value in the Name index, then go directly to The corresponding rows in the data file are accurately returned to the PEOPLEID (999) of the line. During this process, Mysql can return the result only on one line. If there is no "name" column index, MySQL wants to scan all records in the data file, that is, 1000 records! Obviously, the less the number of records that require mysql processing, the faster it completes the speed of the task.
Index type
Mysql offers a variety of index types for selection: Normal index This is the most basic index type, and it does not have limitations such as uniqueness. Normal index can be created in the following ways:
Create an index, such as the name of the Create Index
Modify the table, such as the ALTER TABLETENAME ADD INDEX [Name] (list of columns);
Specify an index when you create a table, such as CREATE TABLE TABLENAME ([...], Index Name] (list of columns));
Unique index This index is basically the same as the "normal index", but there is a difference: all values of the index column can only appear once, ie must be unique. Unique index can be created in the following ways:
Create an index, such as the name of Create Unique Index
Modify the table, such as the ALTER TABLENAME ADD UNIQUE [Name] (list of columns);
Specify an index when creating a table, such as Create Table TableName ([...], UNIQUE [Name] (list list)
);
The primary key primary key is a unique index, but it must be specified as "Primary Key". If you have used the AUTO_INCREMENT type column, you may have been familiar with the concept of primary key. The primary key is generally specified when the table is created, such as "CREATE TABLE TABLENAME ([...], a list of Primary Key);". However, we can also add primary keys by modifying tables, such as "ALTER TABLENAME ADD PRIMARY Key;". Each table can only have a primary key. The full-text index mysql starts support full-text index and full-text search from version 3.23.2. In MySQL, the index type of full-text index is FullText. Full-text index can be created on the column of varchar or text type. It can be created by the CREATE TABLE command or created via the alter table or create index command. For large-scale datasets, create a full-text index through the ALTER TABLE (or Create Index) command, which is more fast than the empty table with a full-text index. The discussion below will no longer involve the full-text index. For more information, see Mysql Documentation. A single column index and multi-column index
The index can be a single column index or a multi-column index. Below we will explain the difference between these two indexes through specific examples. Suppose there is such a people table:
Create Table People (PeopleId Smallint Not Null Auto_Increment, FirstName Char (50)
NOT NULL, Lastname Char (50) Not Null, Age Smallint Not Null, Townid Smallint Not
NULL, PRIMARY Key (PeopleId);
Below is the data we inserted into this people table:
There are four names "mikes" in this data fragment (two of them sullivans, two McConnells), there are two ages of 17, and one name is different from Joe Smith. The main purpose of this table is to return the corresponding people according to the designated user name, name, and age. For example, we may need to look for the people named Mike Sullivan, age 17-year-old user (SQL command for select people = 'Mike' and lastname = 'sullivan' and age = 17;). Since we don't want Mysql to scan the entire table every time you execute a query, you need to consider using an index. First, we can consider creating an index on a single column, such as the firstname, lastname, or age column. If we create the index of the first Table General;, Mysql will quickly limit the search range to the records of those firstname = 'mike' through this index, and then on this "intermediate result set" Search for other conditions: It first eliminates the records of LastName not equal to "sullivan", and then excludes records that AGEs do not equal 17. After the record satisfies all search criteria, MySQL returns the final search results. Since the index of the firstname column is established, the efficiency of MySQL has increased much more efficient than the full scan of the execution table, but the number of records we ask MYSQL scan is still far more than actual. Although we can delete an index on the firstname column, create a LastName or Age column index, but in the field, regardless of the creation of index search efficiency, it is still similar. In order to improve search efficiency, we need to consider using multiple column indexes. If you create a multi-column index for the three columns of FirstName, LastName, and AGE, MySQL can find the correct result for only one search! Here is the SQL command to create this multi-column index: ALTER TABLE people add index fname_lname_age (firstname, lastname, age);
Since the index file is saved in B-tree format, MySQL can go to the appropriate firstname, then go to the appropriate lastname, and finally go to the appropriate AGE. In the absence of any record of scanning data files, MySQL correctly identifies the target record of the search! Then, if you create a single column index on both columns, whether the effect is the same as the multi-column index of firstname, lastname, age? The answer is negative, both of which are completely different. When we perform queries, mysql can only use an index. If you have three single coli index, MySQL will try to select a restriction of the most stringent index. However, even if the most stringent single column index is limited, its restrictions are definitely lower than the multi-column index on the three columns of FirstName, Lastname, and AGE.
Left prefix
Multi-column index has another advantage that it is reflected in the concept called the LEFTMOST prefixing. Continue to consider the previous example, now we have multiple columns on the firstname, lastname, and the Age column, saying this index is FNAME_LNAME_AGE. When the search condition is a combination of the following columns, MySQL will use the FNAME_LNAME_AGE index: firstname, lastname, AGE
Firstname, Lastname
Firstname
It is also equivalent to the index we create (firstname, lastname, agn), (firstname, lastname), (firstname), and (firstname). Both these queries can use this FNAME_LNAME_AGE index:
Select people = 'Mike' and lastname = 'sullivan' and
AGE = '17 '; select people =' mike 'ANDD
Lastname = 'sullivan'; select people = 'mike'; the
FOLLOWING Queries Cannot Use the index at all: Select PeopleId from People Where
Lastname = 'sullivan'; select people = '17 '; select people
From people where lastname = 'sullivan' and age = '17 ';
Select index columns
In the process of performance optimization, choose to create an index on which columns is one of the most important steps. It can be considered that there are two main types of columns of the index: columns appearing in the WHERE clause, the columns appearing in the join clause. Please see the following query:
Select Age ## Do not use index from mind where firstname = 'mike' ## Consider using indexed and
Lastname = 'sullivan' ## Consider using an index
This query is slightly different from the previous query, but still belongs to a simple query. Since AGE is referenced in the Select section, MySQL does not use it to limit the column selection operation. Therefore, for this query, it is not necessary to create an index of the AGE column. Here is a more complex example:
Select people.age, ## Do not use the index Town.name ## Do not use index from people Left Join Town on
People.townID = Town.townID ## Consider using index where firstname = 'mike' ## Consider using indexed and
Lastname = 'sullivan' ## Consider using an index
As with the previous example, since firstname and lastname appear in the WHERE clause, these two columns still have the necessary indexes. In addition, since the TownID of the TOWN table is now in the Join clause, we need to consider creating the index of the column. So, can we simply think the WHERE clause and each column appearing in the Join clause? Almost like this, but not complete. We must also take into account the type of operators that compare columns. MySQL only uses indexes for the following operators: <, <=, =,>,> =, betWeen, in, and some LIKE. The case where the index can be used in the LIKE operation means that the other operand is not at the beginning of the wildcard (% or _). For example, "SELECT People Like 'MICH%';" This query will use index, but "Select People Like '% IKE';" This query will not use indexes. Analysis index efficiency
Now we already know how to choose the knowledge of the column, but it is not possible to judge which one is most effective. MySQL provides a built-in SQL command to help us complete this task, this is the explain command. The general syntax of the explain command is: explain
. You can find more instructions about this command at MySQL documentation. Below is an example:
Explain Select People = 'Mike' and lastname = 'sullivan'
And Age = '17 ';
This command will return the following analysis results:
TableTypePossible_Keyskey Key_LENREF ROWSEXTRAPEOPLEREFFNAME_LNAME_AGEFNAME_LNAME_AGE102CONST, CONST, Const1where Used
Let's take a look at the meaning of this explain analysis result. TABLE: This is the name of the table. TYPE: The type of connection operation. The following is an explanation of the MySQL document regarding the REF connection type: "For each combination of records recorded in another table, MySQL will read all records with matching index values from the current table. If the connection operation only uses the most Left prefix, or if the key is not a UNIQUE or Primary Key type (in other words, if the connection operation does not select a unique line according to the key value), mysql uses the REF connection type. If the connection used by the connection operation only matches a small amount of records, then REF is a good connection type. "In this example, since the index is not a unique type, REF is the best connection type we can get. If the Explain displays the connection type is "all", and you don't want to select most records from the table, then MySQL's operational efficiency will be very low because it wants to scan the entire table. You can join more indexes to solve this problem. For more information, see Mysql Manual Description. Possible_Keys: The name of the index that may be utilized. The index name here is the index nickname specified by the index; if the index does not have a nickname, the default is displayed by the first column in the index (in this case, it is "firstname"). The meaning of the default index name is often not very obvious. KEY: It shows the name of the index actually used by MySQL. If it is empty (or null), MySQL does not use an index. Key_len: The length of the part used in the index is used in byte. In this example, Key_len is 102, where firstname accounted for 50 bytes, LastName accounted for 50 bytes, and Age accounted for 2 bytes. If mysql only uses the firstname section in the index, Key_len will be 50. Ref: It shows the name of the column (or word "const"), and MySQL will select line according to these columns. In this example, MySQL selects lines according to three constant. Rows: MySQL thinks it must scan the number of records before finding the correct result. Obviously, the most ideal number here is 1. Extra: There may be many different options here, 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. Summary of indexes
So far, we discuss the advantages of indexes. In fact, the index is also disadvantageous. First, the index should take up the disk space. Usually, this problem is not very prominent. However, if you create an index of each possible column combination, the growth rate of the index file volume will be far more than the data file. If you have a big table, the size of the index file may reach the maximum file limit allowed by the operating system. Second, for operations that need to write data, such as delete, update, and insert operations, indexes reduce their speed. This is because MySQL not only writes the change data to the data file, but it also writes these changes to the index file. [Conclusion] In large databases, indexes are a key factor in improving speed. Regardless of the structure of the table, a 500,000 line scan operation is not fast, no matter how fast. If there is also a large-scale table on your website, you do what indexes can be used for some time to analyze, and consider whether you can rewrite the query to optimize the application. For more information, see mysql manual. Also note that this article assumes that the mysql you use is version 3.23, and some queries cannot be executed on version 3.22 MySQL.
