Accelerate PHP dynamic website About MySQL index analysis and optimization_PHP tutorial

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

1. What is an index?

Indices are used to quickly find records with specific values. All MySQL indexes are saved in the form of B-trees. If there is no index, when executing a query, MySQL must scan all records in the entire table starting from the first record until it finds a record that meets the requirements. The greater the number of records in the table, the higher the cost of this operation. If an index has been created on the column used as the search condition, MySQL can quickly get the location of the target record without scanning any records. If the table has 1000 records, finding the records through the index is at least 100 times faster than scanning the records sequentially.

Suppose we create a table called people:

CREATE TABLE people ( peopleid SMALLINT NOT NULL,

name CHAR(50) NOT NULL );

Then, we insert 1000 different name values ​​into the people table completely randomly. The name columns do not have any explicit order in the data file. If we create an index on the name column, MySQL will sort the name column in the index. For each item in the index, MySQL internally saves a "pointer" to the actual record location in the data file. So, if we want to find the peopleid of a record whose name is equal to "Mike" (the SQL command is "SELECT peopleid FROM people WHERE name='Mike';"), MySQL is able to look up the "Mike" value in the index for name and then go directly to The corresponding row in the data file returns exactly the peopleid (999) of that row. During this process, MySQL only needs to process one row to return the result. If there is no index on the "name" column, MySQL will scan all records in the data file, that is, 1,000 records! Obviously, the smaller the number of records MySQL needs to process, the faster it can complete the task.

2. Type of index

MySQL provides a variety of index types to choose from:

Normal Index:

This is the most basic index type, and it has no restrictions such as uniqueness. Ordinary indexes can be created in the following ways:

Create an index, for example CREATE INDEX ON tablename (list of columns);

Modify the table, such as ALTER TABLE tablename ADD INDEX [name of index] (list of columns);

Specify the index when creating the table, such as CREATE TABLE tablename ([...], INDEX [name of index] (list of columns));

Unique index:

This type of index is basically the same as the previous "ordinary index", but there is one difference: all values ​​in the index column can only appear once, that is, they must be unique. Unique indexes can be created in the following ways:

Create an index, for example CREATE UNIQUE INDEX ON tablename (list of columns);

Modify the table, such as ALTER TABLE tablename ADD UNIQUE [name of index] (list of columns);

Specify the index when creating the table, such as CREATE TABLE tablename ([...], UNIQUE [name of index] (list of columns));

Primary key:

The primary key is a unique index, but it must be specified as "PRIMARY KEY". If you have ever worked with columns of type AUTO_INCREMENT, you may already be familiar with concepts like primary keys. The primary key is generally specified when creating the table, such as "CREATE TABLE tablename ([...], PRIMARY KEY (list of columns));". However, we can also add primary keys by modifying the table, such as "ALTER TABLE tablename ADD PRIMARY KEY (list of columns);". Each table can only have one primary key.

Full text index:

MySQL supports full-text indexing and full-text search starting from version 3.23.23. In MySQL, the index type of full-text index is FULLTEXT. Full-text indexes can be created on VARCHAR or TEXT type columns. It can be created by the CREATE TABLE command, or by the ALTER TABLE or CREATE INDEX command. For large data sets, creating a full-text index through the ALTER TABLE (or CREATE INDEX) command is faster than inserting records into an empty table with a full-text index.