索引分析和优化的区别(索引的优劣)

索引用于快速查找具有特定值的记录，所有MySQL索引都以B树的形式保存。如果没有索引，MySQL在执行查询时必须从第一条记录开始扫描整个表的所有记录，直到找到符合要求的记录。表中的记录越多，这个操作的代价就越高。如果已经在列上创建了索引作为搜索条件，MySQL可以快速找到目标记录的位置，而无需扫描任何记录。如果表中有1000条记录，按索引查找记录比按顺序扫描记录至少快100倍。

假设我们创建了一个名为people:

创建表people ( peopleid SMALLINT NOT NULL，name CHAR(50)NOT NULL)；

然后，我们在people表中随机插入1000个不同的名称值。数据文件中的名称列没有明确的顺序。如果我们创建一个name列的索引，MySQL会对索引中的name列进行排序，对于索引中的每一项，MySQL内部都会保存一个指向数据文件中实际记录位置的“指针”。因此，如果我们想找到姓名等于“Mike”的记录的People id(SQL命令是“Select People id from People Where Name = & # 39；迈克& # 39；;")，MySQL可以在name的索引中查找“Mike”的值，然后直接到数据文件中对应的行，准确返回那一行的peopleid(999)。在这个过程中，MySQL只需要处理一行就可以返回结果。如果没有“name”列的索引，MySQL将扫描数据文件中的所有记录，即1000条记录！显然，MySQL需要处理的记录越少，完成任务的速度就越快。

第二，指数的类型

MySQL提供了多种索引类型可供选择:

通用索引:

这是最基本的索引类型，它没有唯一性等限制。可以通过以下方式创建常规索引:

创建索引，如在tablename上创建索引；

修改表，如alter table表名添加索引(列的列表)；

创建表时指定一个索引，如create table table table...]，index[索引的名称](列的列表)；

唯一性指数:

这个索引与前面的“普通索引”基本相同，但有一点不同:索引列的所有值只能出现一次，即必须唯一。可以通过以下方式创建唯一索引:

创建索引，如在tablename上创建唯一索引；

修改表，如alter table表名添加unique(列的列表)；

创建表时指定一个索引，如create table table table...]，unique[索引名称](列列表)；

主键:

主键是唯一索引，但必须指定为“主键”。如果您曾经使用过AUTO_INCREMENT类型的列，您可能已经熟悉了诸如主键这样的概念。主键通常在创建表时指定，例如，“create table tablename ([...]，主键)；"。不过我们也可以通过修改表来添加主键，比如“alter table table name add primary key”。。每个表只能有一个主键。

全文索引:

MySQL从3.23.23版本开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型是FULLTEXT。可以对VARCHAR或TEXT类型的列创建全文索引。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模数据集，通过ALTER TABLE(或CREATE INDEX)命令创建全文索引比在带有全文索引的空表中插入记录更快。在下一篇文章中不再讨论全文索引。更多信息，请参考MySQL文档。

第三，单列索引和多列索引

索引可以是单列索引，也可以是多列索引。让我们通过具体的例子来说明这两个指标的区别。假设有这样一个人表:

创建表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))；

以下是我们插入到这个人员表中的数据:

在这个数据剪辑中，有四个人的名字是“迈克”(其中两个是苏利文的，两个是麦康奈尔)，两个17岁的人，还有一个名字不同的乔·史密斯。

该表的主要目的是根据指定用户的姓、名和年龄返回相应的peopleid。例如，我们是否需要查找名为Mike Sullivan的17岁用户的PeopleID(SQL命令是Select PeopleID from People Where First Name = & # 39；迈克& # 39；而且姓= & # 39；沙利文& # 39；而年龄= 17；)。因为我们不希望MySQL每次执行查询时都扫描整个表，所以我们需要考虑在这里使用索引。

首先，我们可以考虑在单个列上创建索引，比如firstname、lastname或age列。如果我们创建一个名列的索引(alter table people add index first name)；)，MySQL会很快将搜索范围限制在那些firstname = & # 39迈克& # 39；“Sullivan”的记录，然后在这个“中间结果集”上搜索其他条件:先排除那些姓氏不等于“Sullivan”的记录，再排除那些年龄不等于17的记录。当记录满足所有搜索条件时，MySQL返回最终的搜索结果。

由于firstname列索引的建立，MySQL的效率比全表扫描提高了很多，但是我们要求MySQL扫描的记录数量仍然远远超过实际需要。虽然我们可以删除firstname列上的索引，然后在lastname或age列上创建索引，但总的来说，无论我们在哪一列上创建索引，搜索效率都是相似的。

为了提高搜索效率，我们需要考虑使用多列索引。如果为firstname、lastname和age三列创建多列索引，MySQL只能找到一次正确的结果！以下是创建该多列索引的SQL命令:

ALTER TABLE people添加索引fname_lname_age(名字，姓氏，年龄)；

因为索引文件是以B树格式保存的，所以MySQL可以立即转到相应的名字，然后转到相应的姓氏，最后转到相应的年龄。不用扫描数据文件中的任何记录，MySQL就正确地找出了搜索的目标记录！

那么，如果在名字、姓氏、年龄三列上创建一个单列索引，效果和创建名字、姓氏、年龄多列索引一样吗？答案是否定的，两者完全不同。当我们执行查询时，MySQL只能使用一个索引。如果您有三个单列索引，MySQL将尝试选择限制性最强的索引。但是，即使是限制性最强的单列索引，在firstname、lastname和age列上的限制性也肯定远远小于多列索引。

第四，最左边的前缀

多列索引还有一个优点，就是通过最左前缀的概念来体现。继续考虑前面的例子，我们现在在firstname、lastname和age列上有一个多列索引，我们称之为fname_lname_age。当搜索条件是以下列的组合时，MySQL使用fname_lname_age索引:

名，姓，年龄名，姓名

另一方面，它相当于在(名字，姓氏，年龄)，(名字，姓氏)和(名字)的组合上创建一个索引。以下查询可以使用此fname_lname_age索引:

选择people id FROM people WHERE first name = & # 39；迈克& # 39；而且姓= & # 39；沙利文& # 39；而年龄= & # 39；17'；选择people id FROM people WHERE first name = & # 39；迈克& # 39；而且姓= & # 39；沙利文& # 39；；选择people id FROM people WHERE first name = & # 39；迈克& # 39；；以下查询根本无法使用该索引:SELECT people id FROM people WHERE last name = & # 39；沙利文& # 39；；选择people id FROM people WHERE age = & # 39；17'；选择people id FROM people WHERE last name = & # 39；沙利文& # 39；而年龄= & # 39；17'；

五、选择索引列

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

SELECT age ##不使用firstname = & # 39迈克& # 39；# #考虑使用索引和lastname = & # 39沙利文& # 39；# #考虑使用指数

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

SELECT people.age，##不使用索引town.name ##不使用people.townid = town.townid # #上的people left join town的索引，考虑使用索引WHERE firstname = & # 39迈克& # 39；# #考虑使用索引和lastname = & # 39沙利文& # 39；# #考虑使用指数

与前面的示例一样，由于firstname和lastname出现在WHERE子句中，所以仍然有必要为这两列创建索引。此外，由于town表的townid列在join子句中，我们需要考虑为这个列创建一个索引。那么，我们是否可以简单地认为，出现在WHERE子句和join子句中的每一列都应该被索引呢？差不多，但不完全是。我们还必须考虑比较列的运算符的类型。MySQL只对以下操作符使用索引:=、BETWEEN、in，有时还有LIKE。当另一个操作数不以通配符(%或_)开头时，可以在LIKE操作中使用引号。例如，“从名字类似& # 39；Mich % & # 39;"该查询将使用索引，但是“从名字类似& # 39；% ike & # 39;"该查询将不使用索引。