MySQL index creation

First, 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 more than one record, finding records by index is at least four 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 a different name value 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 is able to find the "Mike" value in the index of name , then go directly to the corresponding row in the data file and return exactly the row'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, which is a record! 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 indexes such as the name of the Create index< index > on tablename ( List of columns );
Modify the table, such as ALTER table tablename ADD Index [ name of index ] ( list of columns );
Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [ name of indexed ] ( 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 ( List of columns );
Modify the table, such as ALTER table tablename ADD UNIQUE [ index name ] ( list of columns );
Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [ index name ] ( 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
　　 MySQLFrom3.23.23Version to support full-text indexing and full-text retrieval. InMySQL, the index type of the full-text index isFulltext。 The full-text index can beVARCHAROrTEXTCreated on columns of type. It can be done byCREATE TABLEcommand is created, or you canALTER TABLEOrCREATE INDEXCommand creation. For large-scale datasets,ALTER TABLE(orCREATE INDEXcommand to create a full-text index is faster than inserting a record into an empty table with a full-text index. The following discussion in this article no longer involves full-text indexing, for more information, seeMySQL Documentation。
　　
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 apeopleTable:
　　
　　 CREATE TABLE people (Peopleid SMALLINT NOT NULL auto_increment, FirstName char (a) NOT NULL, LastName char (.) Not N ULL, age SMALLINT not NULL, Townid SMALLINT NOT NULL, PRIMARY KEY (Peopleid));
　　
Here's what we're inserting into thispeopleData for the table:
　　
There are four names in this data fragment"Mikes"Of the people (of whom two surnamesSullivans, two surnameMcConnells), there are two age -years old, and there's a name that's different.Joe Smith。
　　
The primary purpose of this table is to return the corresponding user name, name, and age according to the specifiedPeopleid。 For example, we may need to find the nameMike SullivanAge --year-old user'sPeopleid（SQLCommand forSELECT Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan ' and age=17;）。 Because we don't want to letMySQLEach time you execute a query to scan the entire table, you need to consider using an index.

First, we can consider creating an index on a single column, such asFirstName、LastNameOr AgeColumn. If we createFirstNameThe index of the column (ALTER TABLE People ADD INDEX FirstName (firstname);），MySQLThis index will quickly limit the search scope to thosefirstname= ' Mike 'The record, and then again in this"Intermediate result set"Other criteria on the search: it first excludes thoseLastNameNot equal to"Sullivan"The records and then exclude those AgeNot equal to -The record. After all the search criteria have been met,MySQLThe final search results are returned.
　　
Due to the establishment ofFirstNameThe index of the column, compared to the full scan of the execution table,MySQLThe efficiency has been improved a lot, but we askMySQLThe number of records scanned still goes far beyond what is actually needed. Although we can deleteFirstNameIndex on the column, and then create theLastNameOr AgeThe index of the column, but in general it appears that the search efficiency is still similar regardless of which column is created.
　　
To improve search efficiency, we need to consider using multi-column indexes. If theFirstName、LastNameAnd AgeThese three columns create a multi-column index,MySQLYou can find the right results with just one search! The following is the creation of this multi-column indexSQLCommand:
　　
　　 ALTER TABLE People ADD INDEX fname_lname_age (firstname,lastname,age);
　　
Becauseindex files are saved in b -tree format,MySQL can immediately go to the appropriate FirstName, 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, do you want to create a firstname, LastName, Age's multi-column index? The answer is 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, by callingLeftmost prefix（leftmost prefixing) is reflected in the concept. Continue to consider the previous example, and now we have aFirstName、LastName、 Agecolumn, we call the indexFname_lname_age。 When the search condition is a combination of the following columns,MySQLwill useFname_lname_ageIndex:
　　
　　 FirstName，LastName，Age
　　 FirstName，LastName
　　 FirstName
On the other hand, it's equivalent to what we created(FirstName，LastName，Age )、(FirstName，LastName)And(FirstName)The indexes on these column combinations. The following queries all have the ability to use thisFname_lname_ageIndex:
　　
　　 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 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 an indexed column
In the performance optimization process, choosing which columns to create indexes on is one of the most important steps. There are two main types of columns that you can consider using indexes:Columns that appear in the where clause, the column that appears in the join clause。 Consider the following query:
　　
　　 SELECT Age # #Do not use indexes
　　 From people WHERE firstname= ' Mike ' # #Consider using an index
　　 and lastname= ' Sullivan ' # #Consider using an index
　　
This query is slightly different from the previous query, but it still belongs to a simple query. Because Ageis inSELECTSection is quoted,MySQLIt is not used to restrict column selection operations. Therefore, for this query, create a AgeThe index of the column is not necessary. The following is a more complex example:
　　
　　 SELECT People.age, # #Do not use indexes
　　 Town.name # #Do not use indexes
　　 From people left JOIN
　　 People.townid=town.townid # #Consider using an index
　　 WHERE firstname= ' Mike ' # #Consider using an index
　　 and lastname= ' Sullivan ' # #Consider using an index
　　
As with the previous example, asFirstNameAndLastNameappear inWHEREclause, so these two columns still have the necessary to create an index. In addition, due to Townof the tableTownidList nowJoinclause, so we need to consider the index that created the column.
　　
So, can we simply think that it should be indexedWHEREClauses andJoinEach column that appears in a clause? Almost so, but not entirely. We also have to take into account the type of operator that compares the columns.MySQLThe index is used only for the following operators:<，<=，=，>，>=，between，inch, and at some point the like。 Can be in likeThe case in which an index is used in an operation means that the other operand is not a wildcard character (% or _)the beginning of the situation. 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 a few things about how to choose indexed columns, we can't tell which one 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. You can find more instructions on 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 will return the following analysis results:
　　
Now let's take a look at this.EXPLAINThe meaning of the analysis results.
　　
　　
　　 Table: This is the name of the table.
　　 type: The type of the connection operation. Below isMySQLDocumentation aboutrefDescription of the connection type:
　　
　　 "For each combination of records with another table,MySQLAll records with matching index values are read from the current table. If the connection operation uses only the leftmost prefix of the key, or if the key is notUNIQUEOrPRIMARY KEYType (in other words, if the connection operation cannot select a unique row based on the key value), theMySQLUserefThe connection type. If the key used by the connection operation matches only a small number of records, therefis a good type of connection."
　　
In this example, because the index is notUNIQUETyperefIs the best connection type we can get.
　　
IfEXPLAINShow connection Type is"All", and you don't want to select most of the records from the table, thenMySQLOperation efficiency will be very low because it is going to scan the entire table. You can add more indexes to solve this problem. For more information, seeMySQLThe 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 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 theMySQLThe name of the index that is actually used. If it is empty (orNULL), youMySQLDo not use indexes.
　　
　　 Key_len：
The length of the part to be used in the index, in bytes. In this case,Key_lenIs102, whereFirstNamePlaceholder -BytesLastNamePlaceholder -Bytes AgePlaceholder2Bytes. IfMySQLUse only those in the indexFirstNamesection, youKey_lenWill be -。
　　
　　 ref：
It shows the name of the column (or the word"Const"），MySQLRows are selected based on these columns. In this case,MySQLSelects a row based on three constants.
　　
　　 rows：
　　 MySQLThe number of records that it must scan before it can find the correct results. Obviously, the ideal number here is1。
　　
　　 Extra：
Many different options may appear here, most of which will adversely affect the query. In this case,MySQLJust to remind us that it will be usedWHEREclause to restrict the search result set.
　　
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.
　　
"Concluding remarks" in large databases, indexes are a key factor in increasing speed. No matter how simple the structure of a table is, a table scan operation that 500000 rows at a time is not fast anyway. If you have such a large-scale table on your site, you really should take the time to analyze which indexes you can take and consider whether you can rewrite the query to optimize your application. To learn more