Accelerate MySQL index analysis and optimization for dynamic websites

This article describes how to accelerate MySQL index analysis and optimization for dynamic websites. 1. what is an index? Indexes are used to quickly search for records with specific values. all MySQL indexes are retained as B-trees. If there is no index, perform the query

This article describes how to accelerate MySQL index analysis and optimization for dynamic websites.

1. what is an index?

Indexes are used to quickly search for records with specific values. all MySQL indexes are retained as B-trees. If no index exists, MySQL must scan all the records in all the tables from the first record until the records matching the request are found. The more records in the table, the higher the cost of this control. If an index has been created for the column used as a search condition, MySQL can quickly obtain the position 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 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. The name column in the data file has no clear order. 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 reserves a data file for it to record the "pointer" of the actual location ". Therefore, if we want to find the peopleid of the "Mike" record (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 the records in the data file, that is, 1000 records! Obviously, the less records that need to be processed by MySQL, the faster it completes 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 using the following methods:

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 similar to the previous "normal index", but there is a difference: all values of the index column can only be displayed once, that is, they must be unique. You can use the following methods to create a unique index: