Mysql 索引概念,分类,使用技巧,优化分析总结介绍

概念

  • mysql index官方文档
  • 索引是数据库管理系统中一种数据结构,用以协助快速查询数据库表中数据,典型的索引结构如B+ tree。

有什么用?

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。

分类

物理存储顺序

  • MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引(二级索引)。 聚集索引是Innodb引擎才有的概念。
  • 为什么说是按照物理存储顺序分类呢?因为聚集索引的逻辑顺序就是数据记录的物理顺序,而非聚集索引却不是如此。 举个例子,聚集索引就像是汉语字典中的拼音索引,字典中的字就是按照这个顺序存的。非聚集索引就像是部首索引,相同偏旁的字在索引页上是相邻的,但是真实的位置却不一定是相邻的。类似的例子还有电话簿,大家自行想象一下。
  • 聚集索引怎么选定呢?
    • 第一个唯一且不能为NULL的列就会被选为聚集索引,一般就是主键了。
    • 如果没有符合条件的列,引擎会自动生成一个6字节的ROWID。
  • 非聚集索引为什么被称为二级索引呢? 因为其索引最终是指向聚集索引的key,一般来说记录还要通过聚集索引才能找到真实的记录,这样要经过两次索引查询,所以被称为二级索引。
  • 非聚集索引可以有多个么?Innodb存储引擎中行记录就是按照聚集索引维度顺序存储的,Innodb的表也称为索引表;因为行记录只能按照一个维度进行排序,所以一张表只能有一个聚集索引。

逻辑存储

  • 唯一索引:column不可重复的索引
  • 主键索引:非NULL的唯一索引
  • 普通索引:相比前两者要求要更低,可以重复,可以为空,一般要求区分度比较大,不然可能也不会走这个索引
  • 联合索引:多列组合的索引
  • 覆盖索引:官方定义是说包含了一个查询的所有列的索引。列索引(column index),或者是联合索引(composite index)就可以成为覆盖索引。

数据类型

  • b+ tree索引: btree也就是平衡多路查找树的升级版本
  • hash索引:适合做精确的搜索,不适合做范围查询
  • fulltext索引:希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询
    • 引擎支持
      • MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
      • MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
      • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
  • R-tree索引: 一种多维度的空间索引,例如:地图左边,巨型,多边形

b+ tree

  • b+ tree官方文档(注意官方文档上一直写的是b-tree,但实际上大家为了区分普通的b-tree,都会叫b+ tree)
  • b tree: 在计算机科学中,B树(B-tree)是一种树状数据结构,它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树,概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同,B-树为系统最优化大块数据的读和写操作。
  • b+ tree
    • 一种广泛用于各种数据库索引的数据结构, 能始终保持索引是排序的状态,支持等于和范围查询(包含大于,小于,between and)
    • 它并不是一颗二叉树,因为它的子节点个数不限于两个
  • b+ tree与 b tree的区别是什么?参考文章浅谈算法和数据结构: 十 平衡查找树之B树
    • b+ tree的非叶子节点不包含数据信息,只包含导航信息
    • 树的所有叶结点构成一个有序链表,可以按照关键码排序的次序遍历全部记录。
    • b+ tree有n棵子树的结点中含有n个关键字; (而b tree是n棵子树有n-1个关键字)
  • B+ tree的优点在于:
    • 由于B+树在内部节点上不好含数据信息,因此在内存页中能够存放更多的key。 数据存放的更加紧密,具有更好的空间局部性。因此访问叶子几点上关联的数据也具有更好的缓存命中率。
    • B+树的叶子结点都是相链的,因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连,所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻,所以缓存命中性没有B+树好。

索引列

  • 单列索引
  • 多列索引-组合索引/联合索引/复合索引

存储引擎支持

Innodb

  • B+ tree索引
  • (5.6及以后的版本)全文索引
  • (5.7及以后的版本)地理空间索引(Geospatial indexing)
  • 自适应哈希索引:注意这个只是个优化项目,不能自己在DDL中选定。在Mysql5.6及以上可用,这个索引是Innodb针对于Using 和 in 这两种操作的一种优化手段,只存在于内存中,是基于已有B+ tree索引建立的。hash的key是任意长度的b+ tree索引的前缀,这个长度是根据索引被搜索的模式来决定的。

Myisam

  • B+ tree索引
  • 全文索引
  • 地理空间索引(Geospatial indexing)

使用技巧

建索引

  • 对常用排序、分组,CURD条件字段应当建立索引
  • 索引的数量不宜过多,避免冗余索引,DBA的建议是不超过5个。联合索引的键不超过3个,太都了之后更新效率必然受到影响,而且似乎也没有必要,极有可能造成索引的冗余
  • 主键尽量是数字,避免使用字符串,因为使用数字的判断速度快
  • 索引的区分度尽量的大
  • 索引(逻辑)类型选择,按照逻辑顺序
    • 每张表必须有一个主键索引,因为主键索引速度快
    • 对于常用的字段,如果唯一可以建立唯一索引,如果不唯一可考虑建立普通索引
    • 组合使用列,可以使用联合索引

用索引

  • 能用主键索引的地方一定要用上,速度快
  • 避免在索引字段上使用函数
  • 联合索引的最左前缀原则

优化分析

  • 5.7版本explain关键字,限于篇幅,这里只是简单说一下
    • 作用:获取SQL的执行计划信息
    • 信息来源:SQL语句优化器-optimizer。这些信息包含:优化器是怎么处理语句的, 包括表连接的顺序。具体的信息见explain输出格式
    • 作用范围: SELECT, DELETE, INSERT, REPLACE以及UPDATE
  • 5.7版本慢查询日志

常见面试题

  • 索引类型 @见笔记
  • 聚集索引和非聚集索引的差别? @见笔记
  • 自适应哈希索引是什么? @见笔记
  • 覆盖索引也有什么用? @见笔记
  • 索引的优化方式有哪些?或者说有没有一些索引优化经验?
    • 使用explain做分析
    • 建索引的一些原则, 见笔记
  • 聚集索引的选定标准是什么? @见笔记
  • 聚集索引跟主键索引的差别是什么? @见笔记
  • 考察不同情况下是否会使用到联合索引 @遵循最左前缀原则
  • b+ tree和b tree有什么区别?见笔记
  • b+ tree支持范围查询么?支持,相邻叶子节点会连接起来
  • 相比于hash索引,b+ tree索引有什么好处? 支持范围查询
  • 索引页和数据页的关系是什么?
    • 聚集索引的叶子节点是数据
    • 非聚集索引的索引叶子节点,对应的是聚集索引的键值,需要到聚集索引也就是数据页去查找数据
  • 一张表全是索引会怎么样?
    • 索引页数据量太大
    • 有重复索引,没必要,举个例子:使用A条件就可以找到某一条记录,那么剩余的索引条件B,C,D,E其实都是没有必要的

参考文章

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 201,681评论 5 474
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 84,710评论 2 377
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 148,623评论 0 334
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,202评论 1 272
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,232评论 5 363
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,368评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,795评论 3 393
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,461评论 0 256
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,647评论 1 295
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,476评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,525评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,226评论 3 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,785评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,857评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,090评论 1 258
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,647评论 2 348
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,215评论 2 341

推荐阅读更多精彩内容

  • 转载:http://blog.codinglabs.org/articles/theory-of-mysql-in...
    qf1007阅读 1,280评论 0 0
  • 索引的本质 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,...
    mysia阅读 573评论 0 1
  • 索引 数据库中的查询操作非常普遍,索引就是提升查找速度的一种手段 索引的类型 从数据结构角度分 1.B+索引:传统...
    一凡呀阅读 2,851评论 0 8
  • 这篇文章主要涉及到MySQL的知识点: 索引(包括分类及优化方式,失效条件,底层结构) sql语法(join,un...
    一根薯条阅读 2,691评论 0 8
  • 每当深夜来临时似睡不睡的怜冰儿,却总是无法入眠也不知又在胡乱想些什么,而有时细细回想究竟是什么会让怜冰儿想入非非不...
    怜冰儿阅读 386评论 0 0