MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件),不然创建的索引就没有意义了。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
1、索引的优/缺点:
优点:
- 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。
缺点:
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
- 建立索引会占用磁盘空间的索引文件。
2、索引类型
索引共有四种:主键、普通索引、唯一索引和全文索引。
PRIMARY 主键: 就是 唯一 且 不能为空;
UNIQUE 唯一索引: 不允许有重复,但允许为NULL
INDEX : 普通索引
FULL TEXT 全文索引: 用于在一篇文章中检索文本信息
举个例子来说,假如有一个商场会员卡的表,包含字段如下:
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT
1、会员编号,作为主键,使用 PRIMARY;
2、如果在“会员姓名”字段上建索引,可以选择普通索引(会员姓名可能重复);
3、如果在“会员身份证号码”字段上建索引,可以选择唯一索引(身份证唯一且不可重复);
4、如果在“会员备注信息”字段上建索引,可以选择 FULLTEXT索引,全文搜索;
不过 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。
用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。
3、单列/组合 索引
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
4、查看索引
SHOW INDEX FROM table_name
5、索引的数据结构
MySql官方对索引的定义为:索引是帮助MySql高效获取数据的数据结构。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
如下面的示意图所示:
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和—个指向对应数据记录物理地址的指针,这样就可以运用二叉查找快速获取到相应数据。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。