Accelerate PHP dynamic websites MySQL index analysis and optimization

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

1. What is an index?

Indexes 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 records through the index is at least 100 times faster than scanning the records sequentially.

Suppose we create a table named 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. Types of indexes

MySQL provides a variety of index types to choose from:

Ordinary 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, such as CREATE INDEX 6bdc9436bb2f0d48acfe1b965ab3a1d0 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 kind of index and The previous "ordinary index" is basically the same, but there is one difference: all values ​​of 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, such as CREATE UNIQUE INDEX 6bdc9436bb2f0d48acfe1b965ab3a1d0 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 a 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 used 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. The discussion below in this article no longer covers full-text indexes; for more information, see the MySQL documentation.

3. Single-column index and multi-column index

The index can be a single-column index or a multi-column index. Below we use specific examples to illustrate the differences between these two indexes. Suppose there is such 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 is the data we insert into this people table:

There are four people named "Mikes" in this data fragment (two of them are Sullivans and two are McConnells), There were two 17-year-olds and one with the unusual name of Joe Smith.

The main purpose of this table is to return the corresponding peopleid based on the specified user's last name, first name, and age. For example, we may need to find the peopleid of a user whose name is Mike Sullivan and whose age is 17 years old (the SQL command is SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;). Since we don't want MySQL to scan the entire table every time a query is executed, indexes need to be considered here.

首先，我们可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname)；），MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录，然后再在这个“中间结果集”上进行其他条件的搜索：它首先排除那些lastname不等于“Sullivan”的记录，然后排除那些age不等于17的记录。当记录满足所有搜索条件之后，MySQL就返回最终的搜索结果。

由于建立了firstname列的索引，与执行表的完全扫描相比，MySQL的效率提高了很多，但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引，再创建lastname或者age列的索引，但总地看来，不论在哪个列上创建索引搜索效率仍旧相似。

为了提高搜索效率，我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果！下面是创建这个多列索引的SQL命令：

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age)；

由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！

那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

四、最左前缀

多列索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引：

firstname，lastname，age
firstname，lastname
firstname

从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引：

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';

五、选择索引列

在性能优化过程中，选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列：在WHERE子句中出现的列，在join子句中出现的列。请看下面这个查询：

SELECT age ## 不使用索引
FROM people WHERE firstname='Mike' ## 考虑使用索引
AND lastname='Sullivan' ## 考虑使用索引

这个查询与前面的查询略有不同，但仍属于简单查询。由于age是在SELECT部分被引用，MySQL不会用它来限制列选择操作。因此，对于这个查询来说，创建age列的索引没有什么必要。下面是一个更复杂的例子：

SELECT people.age, ##不使用索引
town.name ##不使用索引
FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引
WHERE firstname='Mike' ##考虑使用索引
AND lastname='Sullivan' ##考虑使用索引

与前面的例子一样，由于firstname和lastname出现在WHERE子句中，因此这两个列仍旧有创建索引的必要。除此之外，由于town表的townid列出现在join子句中，因此我们需要考虑创建该列的索引。那么，我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢？差不多如此，但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引：da9a35d818b73c4dd4603fcd175d71ed，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引，但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

以上内容就是加速PHP动态网站 MySQL索引分析和优化，希望可以帮助到大家，想要更多相关内容请关注PHP中文网（www.php.cn）！