Accelerate MySQL index analysis and optimization for dynamic websites _ MySQL

Accelerate MySQL index analysis and optimization for dynamic websites 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. Full-text indexing is not covered in the following discussions. For more information, see MySQL documentation.
　　
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:
　　
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 ));
　　
The following figure shows the data we inserted into the people table:
　　
In this data segment, there are four people named "Mikes" (two named Sullivans and two named McConnells), two 17-year-olds, and one named Joe Smith.
　　
This table is mainly used to return the corresponding peopleid based on the specified user name, name, and age. For example, we may need to find the peopleid of a user named Mike Sullivan AND a 17-year-old user (the SQL command is SELECT peopleid FROM people WHERE firstname = 'Mike 'AND lastname = 'sullivan' AND age = 17; ). Because we don't want MySQL to scan the entire table every time it executes a query, we need to consider using indexes here.
　　
First, we can consider creating an index on a single column, such as the firstname, lastname, or age column. If we create an INDEX for the firstname column (alter table people add index firstname (firstname);), MySQL will use this INDEX to quickly limit the search range to those records whose firstname = 'Mike, then, search for other conditions on the intermediate result set: it first excluded the records whose lastname is not equal to "Sullivan", and then excluded those records whose age is not equal to 17. After all the search conditions are met, MySQL returns the final search result.
　　
Because the index of the firstname column is created, MySQL is much more efficient than the full scan of the execution table. However, we require that the number of records scanned by MySQL still far exceed the actual needs. Although we can delete the index on the firstname column and then create the index on the lastname or age column, it seems that no matter which column is created, the search efficiency is still similar.
　　
To improve search efficiency, we need to consider using multi-column indexes. If you create a multi-column index for the columns firstname, lastname, and age, MySQL only needs to retrieve the correct results once! The following is an SQL command to create this multi-column index:
　　
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:
　　
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 ';
　　
5. select an index column
In the performance optimization process, selecting the columns to create an index is one of the most important steps. You can consider using two types of indexes: columns that appear in the WHERE clause and 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 is still a simple query. Because age is referenced in the SELECT part, MySQL does not use it to restrict column selection operations. Therefore, it is unnecessary to create an index for the age column for this query. The following is a more complex example:
　　
SELECT people. age, # No index
Town. name # No index
FROM people left join town ON
People. townid = town. townid # consider using indexes
WHERE firstname = 'Mike '# use indexes
AND lastname = 'sullivan' # consider using indexes
　　
As in the preceding example, because firstname and lastname appear in the WHERE clause, the two columns still need to create indexes. In addition, because the townid of the town table is listed in the join clause, we need to consider creating an index for this column.
　　
So can we simply think that every column in The WHERE clause and join clause should be indexed? This is almost the case, but not completely. We must also consider the operator types for column comparison. MySQL uses indexes only for the following operators: <, <=, =,>,> =, BETWEEN, IN, and sometimes LIKE. If you can use an index in the LIKE operation, another operand does not start with a wildcard (% or. For example, the query "SELECT peopleid FROM people WHERE firstname LIKE 'Mich % ';" uses an index, but "SELECT peopleid FROM people WHERE firstname LIKE' % ike '; "This query does not use indexes.
　　
6. analyze index efficiency
Now we know some knowledge about how to select an index column, but we cannot determine which one is the most effective. MySQL provides a built-in SQL command help