• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL InnoDB索引创建语句

MySQL InnoDB 彭东稳 7年前 (2017-01-06) 32966次浏览 已收录 0个评论

一、索引算法

一般,我们看到术语“索引”和“键”交换使用,但实际上这两个是不同的。索引是存储在数据库中的一个物理结构,键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。

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

多列索引:即一个索引包含多个列,但是此时列的顺序也十分重要,因为MySQL只能高效地使用索引的最左前缀列(一般用在WHERE字句带AND操作会有用)。

1)B+tree索引

B+树是一种经典的数据结构,由平衡树和二叉查找树结合产生,它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树,在B+树中,所有的记录节点都是按键值大小顺序存放在同一层的叶节点中,叶节点间用指针相连,构成双向循环链表,非叶节点(根节点、枝节点)只存放键值,不存放实际数据。

普通索引

普通索引是MySQL最基本的B+树索引类型,它没有任何限制。

唯一索引

它与前面的”普通索引”类似,不同的就是:索引列的值必须唯一,但允许为NULL。如果是联合唯一索引,则列值的组合必须唯一。

这里需要明白主键索引(primary key)、唯一索引(unique index)、普通索引(inde)与主键约束(primary key)、唯一键约束(unique key)的区别,这里说的索引都是Btree索引。

唯一键约束(unique key)和主键约束(primary key)创建时都会默认隐式创建同名的唯一索引,以键名为索引名。唯一键约束索引列的值必须唯一,但允许为NULL。而主键约束索引不允许有空值,且在一张表中只允许有一个主键约束,但允许存在复合主键索引,也就是两个字符共同组成一个主键索引,两个字段组合必须唯一。一般是在建表的时候同时创建主键约束。

唯一键约束和主键约束与唯一索引的区别是,当主键约束或者唯一键约束失效时,隐式创建的唯一索引会被删除。但你可以先创建唯一索引,后创建主键约束或唯一键约束。这样显式创建的唯一索引不受主键约束或唯一键约束失效的影响。

2)Hash索引

所谓Hash索引,当我们要给某张表某列增加索引时,将这张表的这一列进行哈希算法计算,得到哈希值,排序在哈希数组上。所以Hash索引可以一次定位,其效率很高,而Btree索引需要经过多次的磁盘IO。既然Hash索引的效率要比B-Tree高很多,为什么大家不都用Hash索引而还要使用B-Tree索引呢?任何事物都是有两面性的,Hash索引也一样,虽然Hash索引效率高,但是Hash索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。

1)Hash索引仅仅能满足”=”,”IN”和”<=>”查询,不能使用范围查询。

2)Hash索引无法被用来避免数据的排序操作。

3)Hash索引不能利用部分索引键查询。

4)Hash索引每次都要表扫描。

5)Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

3)全文索引

FULLTEXT类型索引,可以在CHAR,VARCHAR或者TEXT类型的列上创建,之前仅仅是MYISAM存储引擎支持,从MySQL5.6版本开始支持InnoDB引擎的全文索引,语法层面上大多数兼容之前MyISAM的全文索引模式。全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节,如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情,而不是简单的WHERE条件匹配。

4)空间索引

MyISAM支持空间索引,主要用于地理空间数据类型,例如GEOMETRY。和B-Tree索引不同,这类索引无须前缀查询。空间索引会从素有维度来索引数据。查询时,可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善,所以大部分人都不会使用这个特性,MySQL 5.7对GIS改进了很多。开源关系数据库系统中对GIS的解决方案做得比较好的就是PostgreSQL的PostGIS。

下面主要介绍B+树索引的管理。

二、B+树索引管理

第一种:普通索引

直接创建索引

修改表结构添加索引

创建表时创建索引

[length]:可选,如果是CHAR或VARCHAR类型,length可以小于字段实际长度。如果是 BLOB 和 TEXT 类型,必须指定length。

[using btree|hash]:可选,btree或hash都是用来排序算法,默认是btree。

第二种:唯一索引

直接创建索引

修改表结构添加索引

创建表时创建索引

创建唯一键约束

创建主键约束

这里就体现出来了,我们使用唯一索引和唯一键约束都可以创建一条唯一索引,在创建唯一键约束时会自动创建一条唯一键索引。

另外有时候可能会碰到我们需要为以前的表创建一个索引,有可能以前的数据中存在重复的记录机会报Duplicate entry,那怎么办呢?

它会删除重复的记录(别怕,会保留一条),然后建立唯一索引,高效而且人性化。

第三种:联合索引

直接创建索引(联合唯一索引)

修改表结构添加索引(联合唯一索引)

创建表时创建索引(普通联合索引)

索引删除

查看索引

通过SHOW INDEX可以查看到一个表上的所有索引。

Table:索引所在的表名。

Non_unique:非唯一的索引,0表示是唯一索引,1表示不是唯一索引。

Key_name:索引名称,用户可以通过这个名字来DROP INDEX。

Seq_in_index:索引中该列的位置,对于单列索引都是1,对于联合索引就比较直观了,会有序号1、2、3等。

Column_name:索引列的名称。

Collation:列是以什么方式存储在索引中的,可以是A或是NULL,B+树索引总是A,即排序的。如果使用了Hash索引,这里会显示NULL。因为Hash根据hash桶存放索引数据,而不是对数据进行排序。

Cardinality:非常关键的值,表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1,如果非常小,那么用户需要考虑是否可以删除此索引。

Sub_part:是否是列的部分索引,如果值为NULL表示整个列被索引了。如果是数值,那么表示只取了这个列的部分数据做索引了。

Index_type:索引的类型,默认都是BTREE索引。

Comment:索引注释。

如何检测所创建的索引是否有效(或者说索引使用情况)

Handler_read_key越高越好。

Handler_read_rnd_next越高越不好。

三、索引性能测试

1)分别在没有索引和有索引的情况下执行查询。

2)分别在没有索引和有索引的情况下新增数据。

创建没有索引的表

插入数据

创建有索引的表

查看表

刷新查询缓存,如果没有效果重启MySQL是最有效的

执行查询计划

查询计划可以用于输出查询的详细信息,可以看出此条SQL语句有没有用到索引。

另外一个没有索引的表在接收数据导入时比有索引的表要快很多。因为当一个表建立索引之后,导入数据时。每导入一行数据随之索引就需要变化,从而导致导入数据慢。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!