数据库 ·

Mysql索引

作者:刘通亮 

一、索引简介

索引是对数据库表中的一列或多列的值进行排序的一种结构,使用索引可以提高数据库中特定数据的查询速度。

原理:索引是经过某种算法优化的一种数据结构,可以快速查找。在mysql中使用索引可以快速找到被查询的数据,避免全表扫描,从而确定这一行记录的位置。

1、索引的优点和缺点

优点:

  1. 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。(比如微信中的openId,unionId)
  2. 可以大大加快数据的查询速度,这是创建索引的最主要的原因。
  3. 在实现数据的参考完整性方面,可以加速表和表之间的连接。
  4. 在使用分组和排序字句进行数据查寻时,也可以显著减少查询中分组和排序的时间。

缺点:

  1. 创建索引和维护索引要好费时间,并且随着数据量的增加所耗费的时间也会增加。
  2. 索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。
  3. 当对表中的数据尽心更增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

2、索引的分类

(1)  普通索引和唯一索引(重要)

普通索引:mysql中的基本索引类型,允许在定义索引的列中插入重复值和空值。

唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。主键索引是一种特殊的唯一索引,不允许有空值。

(2)  单列索引和组合索引(重要)

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

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

(3)  全文索引(了解)

全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。Mysql中只有MyISAM存储引擎支持全文索引(Mysql5.6以后InnoDB存储引擎也支持全文索引,笔者在Mysql5.72中在InnoDB存储引擎的表中建立过,但是实际应用没有做研究)。

(4)  空间索引(了解)

空间索引是对空间数据类型的字段建立的索引,Mysql中的空间数据类型有4种,分别是:GEOMETRY(几何)、POINT(点)、LINESTRING(线段)、POLYGON(多边形)。Mysql中使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MyISAM的表中创建。

3、索引的设计原则

  1. 索引并非越多越好,一个表中如有大量的索引,不仅占中磁盘空间,而且会影响insert、update、delete等语句的性能,因为表中的数据更改的同时,索引也会进行调整和更新。
  2. 避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。从而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。
  3. 数量小表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
  4. 在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值,因此就无需建立索引。如果建立索引不但不会提高查询效率,反而会严重降低数据更新速度。
  5. 当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引能确保定义的列的数据完整性,以提高查询速度。
  6. 在频繁进行排序或者分组(即进行group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

二、创建索引

1  创建表的时候创建索引

create  table  table_name  [col_name data_type]
[unique|fulltext|spatial]  [index|key]  [index_name]  (col_name [length]) [asc | desc]

示例(普通索引和唯一索引):

(1)   普通单列索引

创建user表,有id,name,phone,age四个字段,在name字段上建立普通索引。Mysql索引

查看索引:

有三种方式:

  1. show create table user

Mysql索引

上图中箭头指的那一行key和index是等价的,都表示索引。

2. desc user

Mysql索引

PRI表示主键(非空的唯一索引)UNI表示唯一索引MUL表示普通索引(列上可以有重复值)

3. show index from user(重要)

Mysql索引

上图中描述了user表中所有的索引的详细信息。具体的含义可以自己去查,这里不在废话。

(2)   创建唯一索引

创建user表,有id,name,phone,age四个字段,在phone字段上建立唯一索引。

第一种方式Mysql索引

第二种方式Mysql索引

这种方式唯一索引的名字会自动生成,默认为字段名

Mysql索引

注:读者也可以尝试用show create table user或者desc user去查看下。

Mysql索引

(3)创建组合索引

创建user表,有id,name,phone,age四个字段,在phone、name、age字段上建立组合索引。

Mysql索引

查看索引

Mysql索引

建立唯一组合索引

Mysql索引

查看索引

2  在已经存在的表上创建索引

(1)使用alter table语句创建索引

alter  table  table_name  add  [unique|fulltext|spatial] 
[index|key]  [index_name] (col_name[length],...)  [asc|desc]

示例:普通组合索引

先创建user表,有id,name,phone,age四个字段

Mysql索引

在phone、name、age字段上建立组合索引。

Mysql索引

查看索引

Mysql索引

(2)使用create index语句创建索引

create  [unique|fulltext|spatial]  index  index_name
on  table_name (col_name[length],...)  [asc|desc]

示例:普通组合索引

先创建user表,有id,name,phone,age四个字段

Mysql索引

在phone、name、age字段上建立组合索引。

Mysql索引

查看索引

Mysql索引

三、删除索引

  1. 使用alter table语句删除索引
alter  table  table_name  drop  index  index_name

2. 使用drop index语句删除索引

drop  index  index_name  on  table_name

示例:Mysql索引

Mysql索引

 

参与评论