继续浏览精彩内容
慕课网APP
程序员的梦工厂
打开
继续
感谢您的支持,我会继续努力的
赞赏金额会直接到老师账户
将二维码发送给自己后长按识别
微信支付
支付宝支付

MySQL的索引知识

白板的微信
关注TA
已关注
手记 341
粉丝 70
获赞 385

本文目录

  • 一、什么是索引。

  • 二、索引的类型

  • 三、B树索引和Hash索引的比较

  • 四、索引的挑选

  • 五、索引的代价

  • 六、创建索引

  • 七、删除索引

  • 八、[PRIMARY|UNIQUE]KEY与[UNIQUE]INDEX的关系

  • 九、参考资料

一、什么是索引。

索引是用来加速查询的技术的选择之一,在通常情况下,造成查询速度差异 的因素就是索引是否使用得当。当我们没有对数据表的某一字段段或者多个 字段添加索引时,实际上执行的全表扫描操作,效率很低。而如果我们为某 些字段添加索引,mysql在执行搜索时便可以通过扫描索引,然后再找出索 引对应的值,从而提高效率。

二、索引的类型

实际上索引的类型不多,以下只是针对个人以前遇到的索引概念的解释,有 可能某个索引有多种称呼,只是取决于你用哪个角度去描述它。

  • B树索引:采用B-trees数据结构存储索引,比如PRIMARY KEYUNIQUE ,INDEX

  • Hash索引:将一个散列函数应用于每一个列值,最终的散列值都会被存入索引,用于执行查找。

  • R树索引:采用R-trees数据结构存储索引,比如Spatial index。(空间数据类型的索引)

  • 全文索引(FULLTEXT INDEX):一般在CHARVARCHAR或者TEXT列上创建此索引。可用来代替like ‘%xx%’实现模糊查询。

  • 前缀索引:只对一个列或者多个列的前几个字符或者字节索引。

  • 唯一索引:只对一个列创建索引。

  • 多列索引:对多个列创建索引。在多列索引中必须注意最左前缀这个原则。比如对于(col1,col2,col3)这三列进行索引时,只有(col1)(col1,col2),(col1,col2,col3)才能进行索引搜索。
    注意(col1,col3)也不能进行索引。

  • 聚簇索引:每个InnoDB表都有一个特殊的索引称为聚簇索引,一般来说,当为一个表定义一个PRIMAY KEY时,InnoDB就会使用它作为聚簇索引。如果没有定义PRIMARY KEY时,MySQL就会查找第一个非空的 UNIQUE index作为聚簇索引。如果以上两种情况都不满足的话,InnoDB内部会在表的每一行产生一个隐藏的并且名为GEN_CLUST_INDEX的聚簇索引。这个聚簇索引是一个六个字节长度的行ID字段,ID值随着新行的插入而单调增长。实际上,除了聚簇索引,其他索引都称为二级索引。在InnoDB中,二级索引的每一行(将索引假设为行方便理解,实际上索引的存储方式取决于具体的存储引擎)中都包含着一个PRIMARY KEY列,InnoDB使用PRIMARY KEY这一列的列值在聚簇索引中查找相对应的数据(可以将聚簇 索引理解为中间值),从而最后得到最终的结果集。聚簇索引的数据分布如下图:(图来自《高性能MySQL》)
    5c448e2c0001cf6705080359.jpg
    上图中,节点页存放是索引(对应着二级索引的PRIMARY KEY),叶子页存放着所对应的数据,节点页和叶子页这个整体就称为聚簇索引,由此可见,聚簇索引更像是一种数据存储结构。

  • 覆盖索引:当查询的结果集可以通过所创建的索引查找出来时,这个索引就称为覆盖索引。
    下面举个例子:
    5c448e2c00015dfd05510104.jpg
    对于上面这个表,当执行下面的语句时,就会使用覆盖索引查询。
    5c448e2d0001378705470228.jpg
    因为我们在创建表的时候对last_namefirst_name创建了多列索引,并且在查询的时候只查询这两列的结果,因此MySQL会使用覆盖索引查询数据,这也意味着MySQL不会对实际的数据行进行查询,因为所需结果已经可以从索引中查找出来了。
    另外可以看一下下面的SQL语句:
    5c448e2d00017b7c05540232.jpg
    在上面的SQL语句中,我们想查询idlast_name的值,而id是主键,last_name是多列索引中的最左索引,但是此时的查询依旧使用覆盖索引查询。原因在于id实际是作为聚簇索引的,而多列索引自然就是二级索引了,上面提到,二级索引都包含着一列PRIMARY KEY列,而列值就是聚簇索引的索引值,因此此时MySQL可以直接使用覆盖索引中查找出对应的结果集。

三、B树索引和Hash索引的比较

  • InnoDB存储引擎和MyISAM存储引擎都只支持B树索引(实际上InnoDB还支持自适应的hash索引,只是不能人为创建),MEMORY存储引擎默认使用hash索引,但它也支持B树索引。

  • 在使用<<==>=><>!=BETWEEN运算符,进行精确比较或者范围比较时,使用B树索引会带来高效。如果匹配模式是以一个纯字符串,而不是一个通配符作为开头的,那么B树索引还可以用在使用like进行模式匹配的操作里。
    下面举个例子:
    5c448e2e0001193e05400119.jpg
    5c448e2e0001c7d105390229.jpg
    5c448e2e0001c37805390233.jpg

  • 对于hash索引,在使用运算符=或者<=>(安全等于的意思,当比较的值含有null值的时候,来返回一个布尔值)完成精确(这里说精确是因为hash索引是用一个hash函数对整个列值hash,而不是某几个字符或者字节)匹配的比较操作里,散列索引的速度非常快。
    5c448e2e0001c98705540345.jpg

四、索引的挑选

  • 一般对于出现在WHERE子句中的列、连接子句中的列、或者出现在ORDER BYGROUP BY子句中的列创建索引是比较好的。

  • 尽量索引短小值。应尽量选用较小的数据类型。比如值的长度不超过25个字符,那么就不要用CHAR(200),其他数据类型同理。特别是InnoDB表来说,因为它使用的是聚簇索引,如果主键过长的话,会导致二级索引占用的存储空间过大。

  • 索引字符串值的前缀。当对字符串列进行索引时,应当尽可能指定前缀长度。比如某一个列的前N个字符足够唯一的话,那么就可以不用为整列进行索引。

五、索引的代价

索引确实可以加快检索速度,但是它同时也降低了索引列的插入、删除和更新值的速度,因为写入一个行不仅是写入一个数据行,还要更改索引。表的索引越多,需要做出的更改就越多,平均性能下降得也就越多。并且当所创建的索引过多时,mysql查询优化器在选择使用哪种索引方案时,也会降低一定的效率。其次,索引也会占用磁盘空间,多个索引会占据更大的空间。与没有索引相比,使用索引很快便达到表的大小极限。

六、创建索引

  • 使用CREATE TABLE创建索引(index_name可选)
    5c448e2f0001b3ea05390194.jpg

  • 使用ALTER TABLE为已有表创建索引(index_name可选)
    5c448e2f0001cbcd05530093.jpg

  • 使用CREATE INDEX创建索引(index_name不可省略)
    5c448e330001402705540368.jpg
    如果某个索引列在索引时使用了PRIMARY KEYSPATIAL,则它必须为NOT NULL的。其他索引列允许包含NULL值。
    如果想要限制某个索引,让它只包含唯一值,那么可以把这个索引创建为PRIMARY KEYUNIQUE索引。 这两种索引很像,主要区别有一下两点:

  • 每个表只能包含一个PRIMARY KEY。因为PRIMARY KEY的名字总是为PRIMARY,而同一个表不允许有两个同名的索引。可以在一个表里放置多个UNIQUE索引。

  • PRIMARY KEY不可以包含NULL值,而UNIQUE索引可以。如果某个UNIQUE索引包含了NULL值,那么它就可以包含多个NULL值。因为NULL值不会与任何值相等,包括它本身。

七、删除索引

最后,我们可以通过DROP INDEXALTER TABLE语句来删除索引

  • 通过DROP INDEX删除索引
    5c448e3400013b5c05330054.jpg

  • 通过DROP INDEX删除索引
    5c448e3400010f1405370043.jpg

八、[PRIMARY|UNIQUE]KEY与[UNIQUE]INDEX的关系

首先来看一下MySQL创建表的语句:(图来自《MySQL官方文档》,图太大所以省略了一部分)
5c448e34000122b305390094.jpg
5c448e34000164e405420307.jpg
从上图可以看出,实际上INDEXKEY是同义词,之所以同时存在主要是为了与其他数据库系统做兼容,另外还有以下两个结论。

  • PRIMARY KEY 与 UNIQUE[INDEX|KEY]很相似,具体区别可以查看上面的内容。

  • INDEX和KEY允许出现相同的列值,但是UNIQUE[INDEX|KEY]不允许出现相同的列值。(记住NULL != NULL)

九、参考资料

作者:之旅

原文链接:https://www.cnblogs.com/tomiku/p/10050373.html

打开App,阅读手记
0人推荐
发表评论
随时随地看视频慕课网APP