MySQL的索引

为什么要使用索引

使用索引主要是为了加快查询速度。通常影响查询速度的最大的也是索引的正确使用。

一个没有索引的数据表就是一个无序的数据行集合，如果我们要查询数据表的某个数据行，就要检查数据表的每一个数据行，看是否与期望值匹配。如果数据表很大，这个过程就很慢，效率很低。

针对某个（或某些）字段创建的索引中包含了数据表里每一个数据行的对应字段的值。索引中的值是经过分类排序的。使用索引可以得知匹配数据行在什么位置结束，从而跳过其余部分。这也是索引可以提高搜索效率的一个原因。另外一个原因则是定位算法的使用，使用定位算法可以不用从索引开始位置线性扫描就能快速定位到第一个匹配项。关于定位算法目前先不多说，只需要知道这些算法可以加快索引速度，并且索引的确是一个提速的好方法。

索引的存储细节

对于不同的存储引擎，索引的存储细节有所不同。

对于MyIsam数据表来说，数据表的数据行是在数据文件里，而索引值保存在索引文件里。一个数据表可以有多个索引，但如果这样的话，所有的索引都储存在同一个索引文件里。索引文件里的每一个索引都是由分类的关键记录数组组成的，这些数组用于快速访问数据文件。

Innodb存储引擎没有按照上面的方法将数据行和索引值分开。默认情况下，Innodb存储引擎使用的是一个表空间。在这个表空间里，它管理着所有的Innodb类型数据表的数据和索引的存储。虽然可以通过配置让Innodb为每个数据表创建一个自己的表空间，数据表的数据和索引也是保存在同一个表空间文件里的。

使用索引的方式

MySQL使用索引的方式有如下几种：

• 快速查询，一是在查询操作中把与where子句所给出的条件相匹配的数据行尽快找出来；二是在关联操作中把与其他数据表里的数据行相匹配的数据行尽快找出来；
• 对于使用MIN()或MAX()函数的查询，如果数据列带索引，那么它的最大值和最小值能够被迅速找到而不用通过逐行检查的方法来查找；
• MySQL经常使用索引来迅速地完成order by子句和group by子句的排序和分组操作；
• MySQL可以通过使用索引来避免为一个查询整体读取数据行。比如要从MyIsam数据表的一个有索引的数据列里选取值，而且并不打算选取数据表里的其他数据列。在这种情况下，MySQL从这个索引文件读取索引值时，实际上就已经得到了这个值，而不需要再次读取数据文件。

索引的缺点

一般情况下，使用索引都是最好的提速方案之一。但是使用索引也有一些时间和空间上的缺点：

• 索引加快了查询速度，但是却降低了在带索引的数据列里插入、删除以及修改数据的速度。这是因为写入一条记录时不仅会写入到数据文件，还会要求维护所有的索引。一个数据表的索引越多，需要做出的调整就越多，平均性能就下降越多。
• 索引要占据磁盘空间，索引越多，占据的表空间越大，也就容易更快达到数据表的尺寸极限。
• 有这两方面的缺点，可以得到一个结论：如果不需要某个特定的索引来加快查询速度，就不要创建它。

挑选索引

挑选索引及使用索引可以从如下几个方向来考虑。

1. 尽量为用来搜索、排序或分组的数据列编制索引，不要为作为输出显示的数据列编制索引。换句话说，适合用来作为索引的列是在where子句中、在联结子句中、在order by或者group by子句中出现的数据列。

2. 综合考虑各数据列的维度。数据列的维度等于它所容纳的非重复值的个数。当查询优化程序确定出某一个数值在数据表中出现的频率超过30%时，查询优化程序通常会跳过索引而进行全表扫描。比如标识性别的列就不值得为其创建索引。

3. 为短小的值进行索引。在创建索引时，应尽量选择比较“小”的数据类型。因此在建表时对关键字段类型的选择需要注意。

4. 为字符串值的前缀编索引。假如要为字符串的数据列编索引，应当尽可能给出前缀长度。比如有一个长度为char(200)的数据列，大多数的值的前10个或者前20个字符都是唯一的，那就不用为整个数据列编制索引，仅为前20个或前30个字符编制索引即可。这样不仅可以节省索引空间，且可以使查询进行得更快。

5. 充分利用最左边的前缀。这个是在使用复合索引时需要注意的一点。假使有这样一个学生信息表(学校编号,班级编号,学生编号,学生姓名,性别,电话,联系地址)，为其中的学校编号、班级编号、学生编号创建了一个复合索引。索引中的数据行是以“学校编号/班级编号/学生编号”的顺序存储的，因此索引中的记录也是以“学校编号/班级编号”的顺序存储的，自然也是以“学校编号”的顺序存储的。有了这个复合索引后就无需以“学校编号”或“学校编号/班级编号”再创建索引。在这例子中，执行查询时，如果没有在where子句中使用学校编号，那么就无法使用索引。如果查询的是学校编号和学生编号的组合，也只能使用学校编号这个索引，而无法使用复合索引。

6. 让索引类型与打算进行比较操作的类型保持匹配。这是说在挑选索引类型的时候，一定要考虑打算在被索引的数据列上进行什么类型的比较操作。比如散列索引在判断是否相等时速度极快，在进行范围比较如“>、<、>=、<=”或者between/and操作时却表现不佳；B树索引在使用精确比较操作和范围比较操作时都很有效率。如果匹配模式是以一个纯字符串而不是一个通配符开头的话，B树索引还可以使用LIKE操作符进行模式匹配操作。

7. 谨慎使用比较函数。对于某些比较函数（比如STRCMP()），服务器必须为每个数据行计算出函数值，这就排除了使用该数据列上的索引的可能性。

8. 利用慢查询日志找出性能低劣的查询。

#######