Accelerated PHP dynamic website about MySQL Index analysis optimization _php tutorial

This article mainly describes how to accelerate the Dynamic web site of MySQL index analysis and optimization.

I. 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 1000 records, finding records by index is at least 100 times 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 (not NULL);

Then, we completely randomly insert 1000 different name values into the People table. The Name column does not have any explicit order in the data file. If we create the index of the name column, MySQL will sort the name column in the index, and 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 can find the "Mike" value in the index of name, then go directly to the corresponding line in the data file and return exactly the line'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, that is, 1000 records! 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 index, such as CREATE 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 Create UNIQUE index <索引的名字> on tablename (a 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 index:

MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column. It can be created by the CREATE TABLE command or by the ALTER TABLE or CREATE INDEX command. For large datasets, it is faster to create a full-text index by using the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with a full-text index.

http://www.bkjia.com/PHPjc/364147.html www.bkjia.com true http://www.bkjia.com/PHPjc/364147.html techarticle This article mainly describes how to accelerate the Dynamic web site of MySQL index analysis and optimization. I. What is an index? Indexes are used to quickly look for records with specific values, all MySQL indexes ...

