MySQL

MySQL 03 - 索引

简介:索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

1. 什么是索引?为什么要建立索引?

索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

例如:有一张person表,其中有2000万条记录,记录着2000万个人的信息,有一个phone的字段记录每个人的电话号码,现在想要查询出指定电话号码的人的信息。

如果没有索引,那么将从表中第一条记录一条条往下遍历,直到找到该条信息为止。如果有了索引,那么会将该phone字段,通过一定的方法进行存储,好让查询该字段上的信息时,能够快速找到对应的数据,而不必在遍历2000万条数据了。其中MySQL中的索引的存储类型有两种:BTree、HASH。 也就是用树或者Hash值来存储该字段。

2. 索引的分类

需要注意的是,索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引:

  • MyISAM和InnoDB存储引擎:只支持BTREE索引,也就是说默认使用BTree,不能够更换。
  • MEMORY/HEAP存储引擎:支持HASH和BTree索引。

索引可分为四类来讲:单列索引(普通索引、唯一索引、主键索引)、组合索引、全文索引、空间索引。

  1. 单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。

    • 普通索引:MySQL中基本索引类型,没有限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
    • 唯一索引:索引列中的值必须是唯一的,但是允许为空值。
    • 主键索引:是一种特殊的唯一索引,不允许有空值。
  2. 组合索引

在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。

  1. 全文索引

全文索引,只有在MyISAM引擎上才能使用,只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引,即在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行。

  1. 空间索引

空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种:GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时,使用SPATIAL关键字。要求引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。

3. 索引的优点和缺点及使用原则

  • 优点:

    1. 所有的MySql列类型(字段类型)都可以被索引,也就是可以给任意字段设置索引。
    2. 大大加快数据的查询速度。
  • 缺点:

    1. 创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
    2. 索引也需要占空间,我们知道数据表中的数据也会有最大上限设置的,如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值。
    3. 当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度。
  • 使用原则:

通过上面说的优点和缺点,我们应该可以知道,并不是每个字段度设置索引就好,也不是索引越多越好,而是需要自己合理的使用。

  1. 主键自动建立唯一索引。
  2. 频繁作为查询条件的字段应该建立索引。
  3. 查询中与其他表关联的字段,外键关系建立索引。
  4. 频繁更新的字段不适合创建索引,因为每次更新数据时还需要更新索引。
  5. where条件里用不到的字段不创建索引。
  6. 高并发情况下倾向建立组合索引。
  7. 查询中排序的字段应该建立索引以提高排序速度。
  8. 查询中统计或分组的字段应该建立索引。
  9. 表记录较少的情况不需要建立索引。
  10. 数据列的数据重复且分布平均,建立索引将没有多大的效果。