一、索引的基本概述
1、什么是索引
索引是创建表上的,是对数据库表中一列或多列的值进行排列的一种结构,使用索引可快速访问数据库表中的特定信息。
举个例子吧:如果把数据库看成一本词典,索引就相当于一本词典的目录,我们可以通过索引快速的找到词典中的词。对于数据库来说,可以通过索引快速查找表中的数据。
2、索引建立的位置及结构存放位置
1)索引是建立在表上的
2)索引一般以文件形式存在磁盘中(也可以存于内存中),存储的索引的原理大致概括为以空间换时间,数据库在未添加索引的时候进行查询默认的是进行全量搜索,也就是进行全局扫描,有多少条数据就要进行多少次查询,然后找到相匹配的数据就把他放到结果集中,直到全表扫描完。而建立索引之后,会将建立索引的KEY值放在一个n叉树上(B+树)。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表,每次以索引进行条件查询时,会去树上根据key值直接进行搜索。
其中:(1)Innodb引擎
.frm 文件存储表结构
.ibd 文件存储数据和索引
(2)myIsam引擎:
.myi 文件存储索引
.myd文件存储数据
.frm 文件存储表结构
3、索引的优点
(1)建立索引可以有效地缩短检索的时间
(2)建立索引可以加快表与表之间的连接
(3)为用来排序或者分组的字段加上索引可以加快索引的分组及排序
(4)建立索引的列可以保证行的唯一性
总之建立索引的目的:加快对表中的记录的查询和排序
4、索引的缺点
在此处想到一句话:事物都有两面性(相对论),索引也是不例外的
(1)创建索引和维护索引需要耗费时间,并且随着数据量的增加而增加
(2)索引需要占用物理的空间,并且一个索引都占用一定的空间(数据表占用的是数据库的空间)
(3)会降低表增删改的效率,因为每次进行增删改索引要进行动态维护,造成数据的维护速度降低了,这就导致时间变长
从此处我们也可以看出来,不是啥时候都可以选择索引,要充分考虑到上述的优缺点。
当数据库中的数据量大时,查询的响应速度不能满足自己的需求,此时我们可以考虑使用合理的索引来提升查询速度。
5、索引的种类
(1)普通索引:在创建普通索引时,不附加任何限制条件。这类索引可以创建在任何数据类型中,其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后,查询时可以通过索引进行查询。
(2)唯一性索引:使用UNIQUE参数可以设置索引为唯一性索引。**在创建唯一性索引时,限制该索引的值必须是唯一的。**通过唯一性索引。可以更快速的确定某条记录。主键就是一种特殊唯一性索引。
(3)全文索引:使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在char,varchar或text类型的字段上。查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度。MySQL数据库中3.23.23版开始支持全文索引,但只有MyISAM存储引擎支持全文索引。在默认情况下,全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后,可以执行区分大小写的全文索引。
(4)单列索引:在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引。只要保证该索引只对应一个字段即可。
(5)多列索引:多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。
(6)空间索引:使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索,而且索引的字段不能为空值
(7)主键索引:索引建立在主键上。 当然反之则是辅助索引
主键索引:
InnoDB :叶子节点存储主键和这个主键所对应的所有数据。
MyISAM :叶子节点存储的是主键(建立索引的属性)和数据的地址。
辅助索引:
InnoDB :叶子节点存储建立索引的属性和属性所对应的主键值。
MyISAM :叶子节点存储建立索引的属性和属性对应的数据的地址。
6、底层数据结构
B+树索引和哈希索引
其中哈希索引:无法做范围查询但是查询单个字段速度快
7、索引的设计原则
(1)为经常需要分组、排序和联合操作的字段建立索引
(2)限制索引的数目,
(3)尽量使用数量少的索引
(4)尽量使用前缀来索引
(5)选择唯一性索引
(6)删除不再使用或者很少使用的索引
(7)为常作为查询条件的字段建立索引
注意:选择索引的最终目的是提高查询的速度。
二、索引的创建和修改删除等命令
1、索引的创建
创建表时直接创建
(1)创建普通索引
(2)创建唯一性索引
(3)创建全文索引
(4)创建单例索引
(5)创建多例索引
(6)创建空间索引
2、在已存在的表上创建索引