索引为什么能提高数据访问性能?
很多人只知道索引能够提高数据库的性能 , 但并不是特别了解其原理 , 其实我们可以用一个生活中的示例来理解 .
我们让一个不太懂计算机的朋友去图书馆确认一本叫做《MySQL性能调优与架构设计》的书是否在藏 , 这样对他说 : “请帮我借一本计算机类的数据库书籍 , 是属于MySQL数据库范畴的 , 叫做《MySQL性能调优与架构设计》” . 朋友会根据所属类别 , 前往存放 “计算机” 书籍区的书架 , 然后寻找 “数据库” 类存放位置 , 在找到一堆讲述 “MySQL”的书籍 , 最后可能发现目标在藏 .
在这个过程中 : “计算机” -> “数据库” -> “MySQL” -> “在藏” -> “《MySQL性能调优与架构设计》” 其实就是一个”根据索引查找数据的典型案例” , “计算机” -> “数据库” -> “MySQL” -> “在藏” 就是朋友查找图书的索引 .
假设没有这个索引 , 那查找这本书的过程会变成怎么样呢 ? 朋友只能从图书馆入口一个书架一个书架的 “遍历” , 直到找到这本书为止 , 如果幸运 ,可能在第一个书架就找到 , 但如果不幸呢 , 那就惨了 , 可能要将整个图书馆所有的书架都找一遍才能找到我们想要的这本书 .
注 : 这个例子中的 “索引” 是记录在朋友的大脑中的 , 实际上 , 每个图书馆都会有一个非常全的实际存在的索引系统 , 由很多个贴上了明显标签的小抽屉构成 . 这个索引系统中存放着非常齐全详尽的索引数据 , 标识出我们需要查找 “目标” 在某个区域的某个书架上 , 而且每当有新的书籍入库 , 旧的书籍销毁以及书籍信息修改,都需要对索引进行及时的修正 .
下面我们通过上面这个生活中的小示例 , 来分析一下索引 , 看看能得出那些结论 ?
索引有哪些副作用 ?
图书的变更(增 , 删 , 改)都需要修订索引 , 索引存在额外的维护成本 .
查找翻阅索引系统需要消耗时间 , 索引存在额外的访问成本 .
这个索引系统需要一个地方来存放 , 索引存在额外的空间成本 .
索引是不是越多越好?
如果我们的这个图书馆只是一个进出中转站 , 里面的新书进来后很快就会转发去其他图书馆耳聪这个馆藏中”清除” , 那我们的索引就只会不断的修改 , 而很少被用来查找图书 .
所以 , 对于类似于这样的存在非常大更新量的数据 , 索引的维护成本会非常高 , 如果其检索需求很少 , 而且对检索效率并没有非常高的要求的时候 , 我们并不建议创建索引 , 或者尽量减少索引 .
如果我们的书籍量少到只有几本或者只有一个书架 , 索引并不会带来什么作用 , 甚至可能还会浪费依稀查找索引所花费的时间 .
所以 , 对于数据量极小到通过索引检索还不如直接遍历来得快的数据 , 也不适合使用索引 .
如果我们的图书馆只有一个10平方的面积 , 现在连放书架都已经非常拥挤 , 而且馆藏还在不断增加 , 我们还能考虑创建索引吗?
所以，当我们连存储基础数据的空间都捉襟见肘的时候 , 我们也应该尽量减少低效或者是去除索引 .
索引该如何设计才高效?
如果我们仅仅只是告诉对方的 : “帮我确认一本数据库类别的讲述MySQL的叫做《MySQL性能调优和架构设计》的书是否在藏 “ , 结果又会如何呢? 朋友只能找到一个大类区域一个大类区域的去寻找”数据库” 类别 , 然后再找到 “MySQL”范畴 , 在看到我们所需是否在藏 . 由于我们说少了一个 “计算机类” , 朋友就必须到每一个大类去寻找 .
所以我们应该尽量查找条件尽可能多的在索引中 , 尽可能通过索引完成所有的过渡 , 回表只是取出额外的数据字段 .
如果我们是这样说的 , “帮我确认一本讲述MySQL的数据库范畴的计算机丛书 , 叫做《MySQL性能调优与架构设计》, 看是否在藏” . 如果这位朋友并不知道计算机是一个大类 , 也不知道数据库属于计算机大类 , 这位朋友就悲剧了 , 首先他得遍历每一个类别 , 确认 “MySQL 存在哪些类别中然后从包含”MySQL” 书籍中再看有哪些是”数据库” 范畴的(有可能部分讲述PHP或者其他开发语言的) , 然后再排除非计算机的 , 然后才能确定 .
所以 , 字段的顺序对组合索引效率有至关重要的作用 , 过滤效果越好的字段需要更靠前 .
如果我们还有一个需求 : “帮我讲图书馆中的所有计算机图书借来” . 朋友如果通过索引来找 , 每次找到索引柜找到计算机书籍所在的区域 , 然后从书架上搬下一格(假设只能以一格为单位从上取下 , 类比数据库中 block /page 为单位读取) , 读取第一本 , 然后再从索引柜找到计算机图书所在的区域 , 在搬一格 , 取下一本 … 如此反复直至取完所有的书 , 如果他不通过 索引来找又会怎么样呢 ? 他需要从第一个书架一直往后找 , 当找到计算机的书, 搬下一格 , 取出所有计算机的书再往后 , 直至所有的书架全部看一遍 , 在这过程中 , 如果计算机类的书籍比较多 , 通过索引来取,所花费的时间很可能远大于直接遍历 , 因为不断望都的索引翻阅所消耗的时间会非常长 .
所以 , 当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候 , 使用索引并不一定优于全表扫描 .
如果我们的朋友不知道”数据库”这个类别可以属于”计算机”这个大类 , 抑或者图书馆的索引系统中这两个类别属性并没有关联关系 , 又会怎样呢 ? 也就是说 , 朋友得到的是2个独立的索引 , 一个是告知 “计算机”这个大类所在的区域 , 一个是”数据库”这个小类所在的区域(很可能是多个区域)，那么他只能二者选其一来搜索我的需求 . 即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找 , 那这样的效率实际过程中也会比较低下 .
所以 , 在实际使用过程中 , 一次数据访问一般只能利用到1个索引 , 这一点在索引创建过程中一定要注意 , 不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了 .