MySQL 系列的目标是: 通过这个系列从入门到全面掌握一个高级开发所需要的全部技能.
这是 MySQL 系列第 21 篇.
本文开始连续 3 篇详解 MySQL 索引:
第 1 篇来说说什么是索引?
第 2 篇详解 MySQL 中索引的原理
第 3 篇结合索引详解关键字 explain
本文为索引第一篇: 我们来了解一下什么是索引?
路人在搞计算机之前, 是负责小区建设规划的, 上级领导安排路人负责一个万人小区建设规划, 并提了一个要求: 可以快速通过户主姓名找到户主的房子; 让路人出个好的解决方案.
方案 1
刚开始路人没什么经验, 实在想不到什么好办法.
路人告诉领导: 你可以去敲每户的门, 然后开门之后再去询问房主姓名, 是否和需要找的人姓名一致.
领导一听郁闷了: 我敲你的头, 1 万户, 我一个个找, 找到什么时候了? 你明天不用来上班了.
这里面涉及到的时间有: 走到每户的门口耗时, 敲门等待开门耗时, 询问户主获取户主姓名耗时, 将户主姓名和需要查找的姓名对比是否一致耗时.
加入要找的人刚好在最后一户, 领导岂不是要疯掉了, 需要重复 1 万次上面的操作.
上面是最原始, 最耗时的做法, 可能要找的人根本不在这个小区, 白费力的找了 1 万次, 岂不是要疯掉.
方案 2
路人灵机一动, 想到了一个方案:
给所有的户主制定一个编号, 从 1-10000, 户主将户号贴在自家的门口
路人自己制作了一个户主和户号对应的表格, 我们叫做: 户主目录表, 共 1 万条记录, 如下:
户主姓名 | 房屋编号 |
---|---|
刘德华 | 00001 |
张学友 | 00002 |
路人 | 00888 |
路人甲 java | 10000 |
此时领导要查找路人甲 Java 时, 过程如下:
按照姓名在户主目录表查找路人甲 Java, 找到对应的编号:
10000
然后从第一户房子开始找, 查看其门口户号是否是 10000, 直到找到为止
路人告诉领导, 这个方案比方案 1 有以下好处:
如果要找的人不在这个小区, 通过户主目录表就确定, 不需要第二步了
步骤 2 中不需要再去敲每户的门以及询问户主的姓名了, 只需对比一下门口的户号就可以了, 比方案 1 省了不少时间.
领导笑着说, 不错不错, 有进步, 不过我找路人甲 Java 还是需要挨家挨户看门牌号 1 万次啊!..... 你再去想想吧, 看看是否还有更好的办法来加快查找速度.
路人下去了苦思冥想, 想出了方案 3.
方案 3
方案 2 中第 2 步最坏的情况还是需要找 1 万次.
路人去上海走了一圈, 看了那边小区搞的不错, 很多小区都是搞成一栋一栋的, 每栋楼里面有 100 户, 路人也决定这么搞.
路人告诉领导:
将 1 万户划分为 100 栋楼, 每栋楼有 25 层, 每层有 4 户人家, 总共 1 万户
给每栋楼一个编号, 范围是[001,100], 将栋号贴在每栋楼最显眼的位置
给每栋楼中的每层一个编号, 编号范围是[01,25], 将层号贴在每层楼最显眼的位置
户号变为: 栋号 - 楼层 - 层中编号, 如路人甲 Java 户号是: 100-20-04, 贴在每户门口
户主目录表还是有 1 万条记录, 如下:
户主姓名 | 房屋编号 |
---|---|
刘德华 | 001-08-04 |
张学友 | 022-18-01 |
路人 | 088-25-04 |
路人甲 java | 100-25-04 |
此时领导要查找路人甲 Java 时, 过程如下:
按照姓名在户主目录表查找路人甲 Java, 找到对应的编号是 100-25-04, 将编号分解, 得到: 栋号(100), 楼层(25), 楼号(04)
从第一栋开始找, 看其栋号是否是 100, 直到找到编号为 100 为止, 这个过程需要找 100 次, 然后到了第 100 栋楼下
从 100 栋的第一层开始向上走, 走到每层看其编号是否为 25, 直到走到第 25 层, 这个过程需要匹配 25 次
在第 25 层依次看看户号是否为 100-25-04, 匹配了 4 次, 找到了路人甲 Java
此方案分析:
查找户主目录表 1 万次, 不过这个是在表格中, 不用动身走路去找, 只需要动动眼睛对比一下数字, 速度还是比较快的
将方案 2 中的第 2 步优化为上面的 2/3/4 步骤, 上面最坏需要匹配 129 次(栋 100 + 层 25 + 楼号 4 次), 相对于方案 2 的 1 万次好多了
领导拍拍路人的肩膀: 小伙子, 去过上海的人确实不一样啊, 这次方案不错, 不过第一步还是需要很多次, 能否有更好的方案呢?
路人下去了又想了好几天, 突然想到了我们常用的字典, 可以按照字典的方式对方案 3 中第一步做优化, 然后提出了方案 4.
方案 4
对户主表进行改造, 按照姓的首字母 (a-z) 制作 26 个表格, 叫做: 姓氏户主表, 每个表格中保存对应姓氏首字母及所有户主和户号. 如下:
姓首字母:A | |
---|---|
姓名 | 户号 |
阿三 | 010-16-01 |
阿郎 | 017-11-04 |
啊啊 | 008-08-02 |
姓首字母:L | |
---|---|
姓名 | 户号 |
刘德华 | 011-16-01 |
路人 | 057-11-04 |
路人甲 | 048-08-02 |
现在查找户号步骤如下:
通过姓名获取姓对应的首字母
在 26 个表格中找到对应姓的表格, 如路人甲 Java, 对应 L 表
在 L 表中循环遍历, 找到路人甲 Java 的户号
根据户号按照方案 3 中的 (2/3/4) 步骤找对应的户主
理想情况:
1 万户主的姓氏分配比较均衡, 那么每个姓氏下面分配 385 户(10000/26) , 那么找到某个户主, 最多需要: 26 次 + 385 次 = 410 次, 相对于 1 万次少了很多.
最坏的情况:
1 万个户主的姓氏都是一样的, 导致这 1 万个户主信息都位于同一个姓氏户主表, 此时查询又变为了 1 万多次. 不过出现姓氏一样的情况比较低.
如果担心姓氏不足以均衡划分户主信息, 那么也可以通过户主姓名的笔画数来划分, 或者其他方法, 主要是将用户信息划分为不同的区, 可以快速过滤一些不相关的户主.
上面几个方案为了快速检索到户主, 用到了一些数据结构, 通过这些数据结构对户主的信息进行组织, 从而可以快速过滤掉一些不相关的户主, 减少查找次数, 快速定位到户主的房子.
索引是什么?
通过上面的示例, 我们可以概况一下索引的定义: 索引是依靠某些数据结构和算法来组织数据, 最终引导用户快速检索出所需要的数据.
索引有 2 个特点:
通过数据结构和算法来对原始的数据进行一些有效的组织
通过这些有效的组织, 可以引导使用者对原始数据进行快速检索
MySQL 为了快速检索数据, 也用到了一些好的数据结构和算法, 来组织表中的数据, 加快检索效率.
下篇文章将对 MySQL 索引原理做详细介绍, 敬请期待, 喜欢的关注一下谢谢!
MySQL 系列目录
第 1 篇: MySQL 基础知识
第 2 篇: 详解 MySQL 数据类型(重点)
第 3 篇: 管理员必备技能(必须掌握)
第 4 篇: DDL 常见操作
第 5 篇: DML 操作汇总(insert,update,delete)
第 6 篇: select 查询基础篇
第 7 篇: 玩转 select 条件查询, 避免采坑
第 8 篇: 详解排序和分页(order by & limit)
第 9 篇: 分组查询详解(group by & having)
第 10 篇: 常用的几十个函数详解
第 11 篇: 深入了解连接查询及原理
第 12 篇: 子查询
第 13 篇: 细说 NULL 导致的神坑, 让人防不胜防
第 14 篇: 详解事务
第 15 篇: 详解视图
第 16 篇: 变量详解
第 17 篇: 存储过程 & 自定义函数详解
第 18 篇: 流程控制语句
第 19 篇: 游标详解
第 20 篇: 异常捕获及处理详解
MySQL 系列大概有 20 多篇, 喜欢的请关注一下, 欢迎大家加我微信 itsoku 或者留言交流 MySQL 相关技术!
来源: https://www.cnblogs.com/itsoku123/p/11636079.html