逆月翎 · 2019年10月23日

浅谈Mysql索引

文章原创于公众号:程序猿周先森。本平台不定时更新,喜欢我的文章,欢迎关注我的微信公众号。

file

我们都知道,数据库索引可以帮助我们更加快速的找出符合的数据,但是如果不使用索引,Mysql则会从第一条开始查询,直到查询到符合的数据,这样也会导致一个问题:如果没有添加索引,表中数据很大则查询数据花费的时间更多。而这时候我们为字段添加一个索引,Mysql就会快速搜索数据,可以节省大量时间。MyISAM和InnoDB是最经常使用的两个存储引擎,MyISAM和InnoDB索引都是采用B+树的数据结构,那B树和B+树的区别是什么呢?

B树
B树是一种多路搜索树,搜索时从根节点开始,对节点内的有序关键字进行二分查找,如果命中则结束搜索,否则根据搜索大小结果进入左右子节点重复搜索,直到找到搜索结果。

特点:

  • 关键字分布在B树所有节点。
  • 关键字不会重复出现在多个节点。
  • 搜索可能在非叶子节点就结束。

B+树

B+树实际上是一种特殊的B树,和B树感官最明显的一个不同点在于B+树关键字只会出现在叶子结点中,并且关键字在链表中是有序的,也就是B+树的搜索最后只会在叶子结点中命中结果,那非叶子结点在B+树充当什么角色呢?非叶子节点在B+树中相当于是叶子结点的索引,而叶子结点是存储关键字数据的数据层。既然Mysql索引采用B+树的数据结构,那么相比于B树,B+树做索引的优势在哪里呢:

  • 磁盘读写代价更低。
  • 查询效率更稳定。
  • 遍历元素效率高。

讲完了B树和B+树的概念,接下来就需要开始谈谈索引了。其实Mysql索引的数据结构有两种:B+树、Hash。但是在MyISAM和InnoDB存储引擎当中只能使用B+树,索引其实总共可以分为四类:

  • 单列索引:单列索引有三种,包括普通索引、唯一索引、主键索引
  • 组合索引
  • 全文索引
  • 空间索引

单列索引

单列索引,顾名思义就是一个索引只能作用于单列,但是一个数据表可以同时拥有多个单列索引。单列索引一共有三种:普通索引、唯一索引、主键索引。

普通索引:

基本的索引类型,不会对数据加入任何限制,一样允许添加了普通索引的普通索引的数据列存在空值或重复值,添加普通索引的目的只是为了查询数据会更快一点。

唯一索引:

对单列添加唯一索引,就代表这个列只能是唯一值,比如用户表用户名可以添加唯一索引,这样用户名必须是唯一值,但是可以为空值。

主键索引:

其实就是在唯一索引的基础上,不允许列出现空值的存在。

组合索引

选中数据表的多列组合然后创建索引,但是组合索引并不是说创建成功都可以被使用,而是需要遵循最左前缀集合。也就是只有在查询条件中使用了这些字段的左边字段,组合索引才会生效。下面我们举个例子来解释下什么叫做最左前缀。

首先创建一个表test_10_09,并且将id, username, sex三个列组合然后添加索引。

CREATE TABLE test_10_09 (

id INT NOT NULL,

username VARCHAR (20) NOT NULL,

idcard VARCHAR (18),

sex VARCHAR (3) NOT NULL,

INDEX MultiIdx (id, username, sex)

)

我们说组合索引想要生效需要满足最左前缀。那什么叫做最左前缀呢?最左前缀其实就是利用组合索引中最左边的列来匹配数据,以上面的例子我们可以看到,组合索引最左边的列是id,所以说如果我们查询的条件不包括id,也就是不满足最左前缀原则,这时候查询操作是无法利用到我们创建的组合索引的。我们可以使用EXPLAIN指令来测试查询条件带与不带id会有什么效果:
file

file

可以看到我们带id查询可以通过索引去查询,但是查询不带id查询无法触发最左前缀原则,于是组合索引并没有生效。

全文索引

全文索引其实就是字面意思,使用全文索引可以在一连串文字中通过某个关键词,就可以找到包含字段的记录行。但是全文索引有着很多限制:

  • 在InnoDB存储引擎不支持使用,只允许在MyISAM存储引擎中使用。
  • 全文索引只能在char、varchar、text三种类型的数据列使用。
  • 所搜的关键字默认至少要4个字符。
  • 全局索引要借助MATCH函数。

空间索引

  • mysql 5.7开始支持空间索引。空间索引一般是适用于包含空间操作的系统,比如游戏开发。
  • 空间索引只能在GEOMETRY、POINT、LINESTRING、POLYGON4种空间数据类型的数据列使用。并且添加空间索引的数据列必须非空。
  • 在创建空间索引必须使用SPATIAL关键字。

索引优点

  • 数据表的所有数据列都可以添加索引。
  • 使用唯一索引或者主键索引可以保证数据的唯一性。
  • 使用索引可以提高查询数据的效率和性能。

索引缺点

  • 使用索引会占用一定的物理空间。
  • 数据插入以及修改都需要维护索引,会影响性能。

索引使用原则

  • 经常需要插入或者更新操作的表不宜设置太多索引,因为数据插入以及修改都需要维护索引,会影响性能。
  • 数据量少的表不建议添加索引,否则可能反而降低查询效率及性能。
  • 在列取值范围比较少时不使用索引,比如专业名只有三个取值,使用索引意义确实不大。
  • 组合索引将最经常使用的列放在第一列,保证组合索引能满足最左前缀的要求。
  • 如果列取值唯一,可以为字段添加唯一性索引,提高查询效率。
  • 索引尽量添加在数据量比较少的列上面,比如varchar(100)检索效率肯定没有varchar(30)来得快,所以说数据量多的列添加索引查询效率会更慢。

欢迎关注公众号:程序猿周先森。文章原创于微信公众号,本平台不定时更新。

推荐阅读
关注数
1
文章数
35
NodeJS服务端开发,文章倾向人群为前后端开发程序猿。
目录
极术微信服务号
关注极术微信号
实时接收点赞提醒和评论通知
安谋科技学堂公众号
关注安谋科技学堂
实时获取安谋科技及 Arm 教学资源
安谋科技招聘公众号
关注安谋科技招聘
实时获取安谋科技中国职位信息