mysql索引优化是什么意思,有哪些优化mysql索引的方式请举例

　　MySQL索引分析和优化(to)-关注LAMP

　　MySQL索引分析与优化(转自kcloze)

　　发布时间：2012年5月15日星期二3点56分

　　1.什么是指数？

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

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

　　1创建表people ( peopleid SMALLINT NOT NULL，name CHAR(50)NOT NULL)；然后，我们在people表中随机插入1000个不同的名称值。下图显示了people表所在的数据文件的一小部分：

　　如您所见，数据文件中的名称列没有明确的顺序。如果我们创建name列的索引，MySQL将在索引中对name列进行排序：

　　对于索引中的每一项，MySQL都会在内部保存一个“指针”,指向它在数据文件中的实际记录位置。因此，如果我们想要找到名称等于“Mike”的记录的PeopleID(SQL命令是“Select PeopleID from people where Name= Mike ；))，MySQL可以在name的索引中查找“Mike”的值，然后直接转到数据文件中对应的行并准确返回该行的peopleid(999)。在这个过程中，MySQL只需要处理一行就可以返回结果。如果没有“姓名”列的索引，MySQL将扫描数据文件中的所有记录，即1000条记录！显然，MySQL需要处理的记录越少，它完成任务的速度就越快。

　　第二，指数的类型

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

　　总索引

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

　　1.在tablename(列的列表)上创建一个索引，如CREATE INDEX的名称；

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

　　3.创建表时指定索引，例如CREATE TABLE TABLENAME ([.]，index[索引的名称](列的列表)；

　　唯一性指数

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

　　1.创建一个索引，如在tablename(列列表)上创建唯一索引的名称；

　　2.修改表，如ALTER TABLE tablename ADD UNIQUE[name of index](列的列表)；

　　3.创建表时指定索引，例如CREATE TABLE TABLENAME ([.]，UNIQUE[索引名称](列列表)；

　　主键

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

　　全文索引

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

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

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

　　12创建表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。例如，我们可能需要找到一个17岁的用户的PeopleID，他的名字是Mike Sullivan(SQL命令是Select PeopleID from people，其中名字= Mike ，姓氏= Sullivan ，年龄=17岁；)。因为我们不希望MySQL每次执行查询时都扫描整个表，所以我们需要考虑在这里使用索引。

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

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

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

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

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

　　第四，最左边的前缀

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

　　名字，姓氏，年龄

　　名字，姓氏

　　名字

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

　　12345678从名字=Mike 和姓氏=Sullivan 以及年龄=17 的人中选择peopleid选择people id FROM people WHERE first name= Mike AND last name= Sullivan ；选择people id FROM people WHERE first name= Mike ；以下查询根本无法使用该索引：SELECT people id FROM people WHERE last name= Sullivan ；从年龄=17 的人中选择peopleid从姓氏=Sullivan 和年龄=17 的人中选择peopleid五、选择索引列

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

　　23选择年龄# #不要使用名= Mike # #考虑使用索引，姓=Sullivan ##考虑使用索引。这个查询与前面的查询略有不同，但它仍然是一个简单的查询。因为在SELECT部分引用了年龄，所以MySQL不会用它来限制列的选择。因此，对于这个查询，没有必要创建age列的索引。这里有一个更复杂的例子：

　　123456SELECT people.age，# #不使用索引town.name ##不使用people left join town on people . townid=town . townid # #考虑使用索引，其中firstname=Mike ##考虑使用索引，lastname=Sullivan ##考虑使用索引。与前面的示例一样，因为firstname和lastname出现在WHERE子句中，所以仍然有必要为这两列创建索引。此外，由于town表的townid列在join子句中，我们需要考虑创建该列的索引。

　　那么，我们是否可以简单地认为，WHERE子句和join子句中出现的每一列都应该被索引？差不多，但不完全是。我们还必须考虑比较列的运算符的类型。MySQL只对以下操作符使用索引：=，=，=，=，BETWEEN，in，有时还有LIKE。可以在LIKE操作中使用索引的情况是指另一个操作数不以通配符(%或_)开头的情况。例如

　　1从名字中包含“Mich%”的人中选择peopleid该查询将使用索引，但是

　　1从名字类似“%ike”的人中选择peopleid该查询将不使用索引。

　　不及物动词指数效率分析

　　现在我们知道了一些如何选择索引列的知识，但无法判断哪一个最有效。MySQL提供了内置的SQL命令帮助。

　　来自：http://www . kuqin . com/database/2008 09 22/19367 . html