MySQL index analysis optimization = PHP dynamic website accelerator _ MySQL

MySQL index analysis optimization PHP dynamic website acceleration this article mainly describes how to accelerate MySQL index analysis and optimization of dynamic websites. ISH Chinese tutorial online

1. what is an index? ISH Chinese tutorial online

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. ISH Chinese tutorial online

Suppose we have created a table named "people": iSH China tutorial online.

Create table people (peopleid smallint not null, iSH Chinese tutorial online

Name CHAR (50) not null); iSH Chinese tutorial online

Then, we randomly insert 1000 different name values into the people table. 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 location where the actual record in a data file is located ". 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. ISH Chinese tutorial online

II. index type iSH China tutorial online

MySQL provides a variety of index types to choose from: iSH China tutorial online

Normal index: iSH China tutorial online

This is the most basic index type, and it has no limitations such as uniqueness. Common indexes can be created in the following ways: iSH China tutorial online

CREATE an INDEX, for example, CREATE INDEX <索引的名字 designtimesp="19529"> ON tablename (column list); iSH Chinese tutorial online

Modify a TABLE, for example, alter table tablename add index [INDEX name] (column list); iSH China tutorial online

Specify an INDEX when creating a TABLE, for example, create table tablename ([...], INDEX [INDEX name] (column list); iSH China tutorial online

Unique index: iSH China tutorial online

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. The unique index can be created in the following ways: iSH China tutorial online

CREATE an INDEX, for example, CREATE UNIQUE INDEX <索引的名字 designtimesp="19535"> ON tablename (column list); iSH Chinese tutorial online

Modify a TABLE, for example, alter table tablename add unique [index name] (column list); iSH China tutorial online

Specify an index when creating a TABLE, for example, create table tablename ([...], UNIQUE [index name] (column list); iSH China tutorial online

Primary key: iSH China tutorial online

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. ISH Chinese tutorial online

Full-text index: iSH China tutorial online

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.