mysql索引的分类php_MySQL索引介绍与索引优化

前几天PHP初学者官方群[168159147]里有些新手提问关于MySQL索引的相关问题，比如MySQL索引怎么用，MySQL索引何时用，MySQL索引使用准则，MySQL索引的优化及MySQL索引的优缺点等。下面天涯PHP博客结合互联网上的一些知识点，做一个简单的总结及概述，以供新手学习。

[1] MySQL索引简介

[1.1] MySQL索引使用场景

索引一般用于在数据规模大时对查询进行优化的一种机制，对于一般的查询来说，MySQL会去遍历整个表，来查询符合要求的结果；如果借助于索引，MySQL会将要索引的字段按照一定的算法进行处理，并生成一个类似于书本目录的文件存放在相应的位置，这样在查询时，MySQL会先去查找这些"目录"，然后根据这些"目录"来快速定位所需记录的位置，这样的查找不用遍历整个记录集，速度自然会很快，对于海量数据尤其如此。

[1.2] MySQL索引分类

MySQL索引分为单列索引和组合索引：

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引：即一个索包含多个列。

[1.3] MySQL索引类型

[1.3.1] 普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

<1>创建索引：CREATE INDEX indexName ON tableName(tableColumns(length))

如果是CHAR,VARCHAR类型，length可以小于字段实际长度;如果是 BLOB 和 TEXT 类型，必须指定length，下同。

<2>修改表结构：ALTER tableName ADD INDEX [indexName] ON (tableColumns(length))

<3>创建表的时候直接指定：CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length))

[1.3.2] 唯一索引

它与前面的"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

<1>创建索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

<2>修改表结构：ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))

<3>创建表的时候直接指定：CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length))

[1.3.3] 主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：CREATE TABLE testIndex(i_testID INT NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(16) NOT NULL,PRIMARY KEY(i_testID))

当然也可以用ALTER命令，一个表只能有一个主键。

[1.3.4] 全文索引

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

[2] MySQL索引优化

[3] MySQL索引使用准则

[3.1] 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。当然，所选择的列和用于WHERE 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。出现在连接子句中的列或出现在形如col1= col2 的表达式中的列是很适合索引的列。查询中的col_b 和col_c 就是这样的例子。如果MySQL能利用连接列来优化一个查询，表示它通过消除全表扫描相当可观地减少了表行的组合。

[3.2] 使用惟一索引

考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处(不管搜索哪个值，都会得出大约一半的行)。

[3.3] 使用短索引

如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个CHAR(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不同的值。

[3.4] 利用最左前缀

在创建一个n 列的索引时，实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同，索引一个列的前缀是利用该的前n 个字符作为索引值。)

假如一个表在分别名为s t a t e、city 和zip 的三个列上有一个索引。索引中的行是按state/city/zip 的次序存放的，因此，索引中的行也会自动按state/city 的顺序和state 的顺序存放。这表示，即使在查询中只指定state 值或只指定state 和city 的值，MySQL也可以利用索引。因此，此索引可用来搜索下列的列组合：state,city,zip

state,city

sate

MySQL不能使用不涉及左前缀的搜索。例如，如果按city 或zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个zip 代码(索引中的列1和列3)，则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。

[3.5] 不要过度索引

不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。

[3.6] 考虑在列上进行的比较类型

MySQL只有对以下操作符才使用索引：，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如：SELECT peopleid FROM people WHERE firstname LIKE 'Mich%'

这个查询将使用索引，但SELECT peopleid FROM people WHERE firstname LIKE '%ike'

这个查询不会使用索引。

如果只将某个列用于其他类型的运算时(如STRCMP())，对其进行索引没有价值。