MySQL教程之索引的使用

使用索引

索引是用于加速查询中最为重要的一个环节。如果你不使用索引，那么在许多情况下，通过其他途径来提高性能的各种尝试都是在浪费时间。在使用中，应首先使用索引来最大程度地改进性能，然后再看看是否还有其他有用的技术。

索引的优点

• 无索引的表就只是一组无序的行。如果要找到其中的某行，就需要检查表的每一行，这是一个全表扫描操作，其效率超低。
• 而如果给一个表的某一列加上一个索引，这个索引会将这一列排序，对于有序表的查找就不需要全表扫描了。（许多数据库都使用了各式各样的技术来快速定位索引值，例如二分法）

索引的实现

对于不同的Mysql存储引擎，索引的具体实现细节会有所不同。

• 对于MyISAM表，其数据行保留在数据文件里，而索引值则保存在索引文件里。一个表可以有多个索引，但他们都保存在同一个索引文件里。索引文件里的每一个索引都有一组有序的关键字行构成，这组关键字行主要用于快速访问数据文件。
• InnoDB同样是将索引值当做是一组有序值。默认情况下，InnoDB存储引擎只使用一个表空间，在这个表空间你的内部，管理着所有InnoDB表的数据存储和索引存储。

索引的典型使用

假设有3个无索引表，每个表中包含一列（1000行），想找出3列同时相等的数。

• 使用扫描匹配就需要使用1000*1000*1000次，效率极低
• 如果使用了索引，如下处理：
  
  • 在表2和表3中建立索引。
  • 从表1中选着第一行，获取值，利用表2和表3的索引直接检索，看是否有满足要求的数据。
  • 然后选择表1的下一行，继续重复的操作。
  • 这样就只需要对表1进行全扫描操作。

使用索引的情况

• 用于加快对where字句匹配的行进行搜索的速度，或者用于对另一个链接表里的行匹配的行进行搜索的速度。
• 对于使用MIN()或MAX()函数的查找，MySQL可以在不逐行检查的情况下，快速查找到索引列里的最小值或最大值。
• 对于ORDER BY或GROUP BY子句，使用索引高效地完成分类和分组的操作。
• 通过索引来读取查询所请求的所有信息。要对这个表中的某个索引数组类型列进行查询，而且你并不打算查询该表的其他列。在这种情况下，读取索引值就可以了，不需要访问数据行。

索引的代价

• 插入、删除和更新数据的速度变慢，对于这些操作都需要更改索引表。
• 索引会占用磁盘空间，会让你很快达到表的大小极限。

如何挑选索引

1. 为用于搜索、排序或分组的列创建索引，而对于用作输出显示的列则不用创建索引。
2. 认真考虑数据列基数
   数据列基数是指它所容纳的所有非重复值的个数，如（1、3、7、4、7、3）的基数是4。列基数越高，索引使用的效果越好。对于包含年龄的列，索引可以很容易的将各个行区分开。但对于记录性别的行，索引操作将毫无用处。当查询优化程序确定出某个值在表里出现的频率很大时，就会跳过索引，转去执行全表扫描操作。
3. 索引短小值
   尽量选用较小的数据类型，
   
   • 短小值可以让比较操作更快
   • 短小值可以让索引短小，减少对磁盘I/O的请求
   • 对于更短小的键值，键缓存里的索引块可以容纳更多的键值。
4. 索引字符串值的前缀
   对字符串（char、varchar、binary、varbinary、text和blob）列进行索引，将前几个字符用于建立索引就可以了
5. 利用最左前缀
   当创建包含n个列的复合索引时，实际上会创建n个专供使用的索引。若创建顺序为a/b/c的组合索引，如果有以下的搜索，都会使用索引
   
   • a/b/c
   • a/b
   • a
   • 这就是最左前缀，其他情况不使用索引
6. 不要建立过多的索引
   过多的索引会浪费空间和修改时间，如果已经有组合索引了，就不需要建立单独的索引。
7. 让参与比较的索引类型保持匹配
   在创建索引时，大部分存储引擎都会选择他们要使用的索引实现。例如，InnoDB总会使用B树索引，MyISAM也会使用B树索引，但对于空间类型则会改用R树实现。MEMORY会使用散列索引，但它也支持B树索引。在选择索引类型时，请考虑计划在索引列上执行的是什么类型的比较操作。
   
   • 在使用运算符=或<=>（a<=>b结合比较运算符，当a小于、等于、大于b时 分别返回一个小于、等于、大于0的integer值）完成精确匹配的比较操作里，散列索引的速度非常快。
   • 在使用< <= = >= > <> != 和BETWEEN运算符进行精确比较或范围比较时，使用B树索引会带来高效，B树索引还可用在使用运算符LIKE进行模式匹配的操作里。
8. 利用慢查询日志找出那些性能低劣的查询