mysql 索引使用

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

但:
索引会占用空间,创建、更新索引索会使用一定资源。不能盲目增加。

索引类型:

主要记录下 聚集索引 和 非聚集索引 下主键索引和辅助索引的原理

主要阐述:两者底层数据结构都是B+tree
非聚集索引叶子节点存的是数据地址(通过叶子节点上的地址去取数据),主键索引和辅助索引一样

聚集索引的主键索引叶子节点下存的直接就是数据(所以说此刻整张表就是一个主键索引文件)。但聚集索引下的辅助索引,其叶子节点下存的是主键(自我理解为:通过辅助索引列查询到主键值,然后通过主键索引拿到数据)。

以下引用他人下图片:

MyISAM--非聚集索引
非聚集-主键索引
非聚集-辅助索引

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

可以看到:MyISAM存储引擎,索引结构叶子节点存储关键字和数据地址,也就是说索引关键字和数据没有在一起存放,体现在磁盘上,就是索引在一个文件存储,数据在另一个文件存储,例如一个user表,会在磁盘上存储三个文件 user.frm(表结构文件) user.MYD(表的数据文件) user.MYI(表的索引文件)。

InnoDB--聚集索引

虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

如下图所示:


聚集索引-主键索引-叶子节点存值

键索引(同时也是数据文件)的示意图

可以看到,索引关键字和数据在叶节点上,在一起存储。这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。

InnoDB存储引擎 - 辅助索引


InnoDB存储引擎 - 辅助索引

索引使用

创建索引 主要有:
单列的索引

相对简单,针对某一列建立索引
create UNIQUE index 索引名 on 表名(列名);

其中,unique为修饰,可选。
UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
当查询 where 条件后有该列时会触发通过索引查找

多列的联合索引

create UNIQUE index 索引名 on 表名(列名1,列名2,列名3);
联合索引相对复杂,遵循最左前缀原理需要理解:

解释一下最左前缀原则:

当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性

索引操作

常用的索引增、删、查

查询当前表已存在索引:

show index form tableName ;

新增索引,上文也已举例建表后创建语句:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) ;[USING 索引方法];

建表时创建索引:

CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件],
……,
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

);
其实,可以观察建表语句后发现,我们建表时定义的主键其实就使用了唯一索引对当列进行了约束。

删除索引:

drop index 索引名 on 表名;