大佬教程收集整理的这篇文章主要介绍了MySQL索引【详解】，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
路人在搞计算机之前c;是负责小区建设规划的c;上级领导安排路人负责一个万人小区建设规划c;并提了一个要求：可以快速通过户主姓名找到户主的房子；让路人出个好的解决方案。
刚开始路人没什么经验c;实在想不到什么好办法。
路人告诉领导：你可以去敲每户的门c;然后开门之后再去询问房主姓名c;是否和需要找的人姓名一致。
领导一听郁闷了：我敲你的头c;1万户c;我一个个找c;找到什么时候了？你明天不用来上班了。
这里面涉及到的时间有：走到每户的门口耗时、敲门等待开门耗时、询问户主获取户主姓名耗时、将户主姓名和需要查找的姓名对比是否一致耗时。加入要找的人刚好在最后一户c;领导岂不是要疯掉了c;需要重复1万次上面的操作。
上面是最原始c;最耗时的做法c;可能要找的人根本不在这个小区c;白费力的找了1万次c;岂不是要疯掉。
路人灵机一动c;想到了一个方案：
| 户主姓名 | 房屋编号 | @H_874_282@
|---|---|
| 刘德华 | 00001 | @H_874_282@
| 张学友 | 00002 | @H_874_282@
| 路人 | 00888 | @H_874_282@
| 路人甲java | 10000 | @H_874_282@
此时领导要查找 路人甲Java 时c;过程如下：
路人告诉领导c;这个方案比方案1有以下好处：
案1省了不少时间。
领导笑着说c;不错不错c;有进步c;不过我找 路人甲Java 还是需要挨家挨户看门牌号1万次啊！。。。。。你再去想想吧c;看看是否还有更好的办法来加快查找速度。
路人下去了苦思冥想c;想出了方案3。
方案2中第2步最坏的情况还是需要找1万次。
路人去上海走了一圈c;看了那边小区搞的不错c;很多小区都是搞成一栋一栋的c;每栋楼里面有100户c;路人也决定这么搞。
路人告诉领导：
户主目录表 还是有1万条记录c;如下：
| 户主姓名 | 房屋编号 | @H_874_282@
|---|---|
| 刘德华 | 001-08-04 | @H_874_282@
| 张学友 | 022-18-01 | @H_874_282@
| 路人 | 088-25-04 | @H_874_282@
| 路人甲java | 100-25-04 | @H_874_282@
此时领导要查找 路人甲Java 时c;过程如下：
按照姓名在 户主目录表 查找 路人甲Java c;找到对应的编号是 100-25-04 c;将编号分解c;得到：栋号（100）、楼层（25）、楼号（04）
从第一栋开始找c;看其栋号是否是100c;直到找到编号为100为止c;这个过程需要找100次c;然后到了第100栋楼下
此方案分析：
查找 户主目录表 1万次c;不过这个是在表格中c;不用动身走路去找c;只需要动动眼睛对比一下数字c;速度还是比较快的
将方案2中的第2步优化为上面的 2/3/4 步骤c;上面最坏需要匹配129次（栋100+层25+楼号4次）c;相对于方案2的1万次好多了
领导拍拍路人的肩膀：小伙子c;去过上海的人确实不一样啊c;这次方案不错c;不过第一步还是需要很多次c;能否有更好的方案呢？
路人下去了又想了好几天c;突然想到了我们常用的字典c;可以按照字典的方式对方案3中第一步做优化c;然后提出了方案4。
| 姓首字母：A | @H_874_282@ |
|---|---|
| 姓名 | 户号 | @H_874_282@
| 阿三 | 010-16-01 | @H_874_282@
| 阿郎 | 017-11-04 | @H_874_282@
| 啊啊 | 008-08-02 | @H_874_282@
| 姓首字母：L | @H_874_282@ |
|---|---|
| 姓名 | 户号 | @H_874_282@
| 刘德华 | 011-16-01 | @H_874_282@
| 路人 | 057-11-04 | @H_874_282@
| 路人甲Java | 048-08-02 | @H_874_282@
现在查找户号步骤如下：
通过姓名获取姓对应的首字母
在L表中循环遍历c;找到 路人甲Java 的户号
根据户号按照方案3中的(2/3/4)步骤找对应的户主
理想情况：
1万户主的姓氏分配比较均衡c;那么每个姓氏下面分配385户（10000/26） c;那么找到某个户主c;最多需要:26次+385次 = 410次c;相对于1万次少了很多。
最坏的情况：
1万个户主的姓氏都是一样的c;导致这1万个户主信息都位于同一个姓氏户主表c;此时查询又变为了1万多次。不过出现姓氏一样的情况比较低。
如果担心姓氏不足以均衡划分户主信息c;那么也可以通过户主姓名的笔画数来划分c;或者其他方法c;主要是将用户信息划分为不同的区c;可以快速过滤一些不相关的户主。
上面几个方案为了快速检索到户主c;用到了一些数据结构c;通过这些数据结构对户主的信息进行组织c;从而可以快速过滤掉一些不相关的户主c;减少查找次数c;快速定位到户主的房子。
通过上面的示例c;我们可以概况一下索引的定义：索引是依靠某些数据结构和算法来组织数据c;最终引导用户快速检索出所需要的数据。
索引有2个特点：
通过数据结构和算法来对原始的数据进行一些有效的组织
通过这些有效的组织c;可以引导使用者对原始数据进行快速检索
使用mysql最多的就是查询c;我们迫切的希望mysql能查询的更快一些c;我们经常用到的查询有：
按照id查询唯一一条记录
按照某些个字段查询对应的记录
查找某个范围的所有记录（between and）
对查询出来的结果排序
@H_988_516@mysql的索引的目的是使上面的各种查询能够更快。
上一篇中有详细的介绍c;可以过去看一下：什么是索引？
索引的本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果c;同时把随机的事件变成顺
序的事件c;也就是说c;有了这种索引机制c;我们可以总是用同一种查找方式来锁定数据。
以机械硬盘来说c;先了解几个概念。
扇区：磁盘存储的最小单位c;扇区一般大小为512Byte。
磁盘块：文件系统与磁盘交互的的最小单位（计算机系统读写磁盘的最小单位）c;一个磁盘块由连续几个（2^n）扇区组成c;块一般大小一般为4KB。
磁盘读取数据：磁盘读取数据靠的是机械运动c;每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分c;寻道时间指的是磁臂移动到指定磁道所需要的时间c;主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速c;比如一个磁盘7200转c;表示每分钟能转7200次c;也就是说1秒钟能转120次c;旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间c;一般在零点几毫秒c;相对于前两个时间可以忽略不计。那么访问一次磁盘的时间c;即一次磁盘IO的时间约等于5+4.17 = 9ms左右c;听起来还挺不错的c;但要知道一台500 -MIPS的机器每秒可以执行5亿条指令c;因为指令依靠的是电的性质c;换句话说执行一次IO的时间可以执行40万条指令c;数据库动辄十万百万乃至千万级数据c;每次9毫秒的时间c;显然是个灾难。
@H_684_8@mysql中的页@H_988_516@mysql中和磁盘交互的最小单位称为页c;页是mysql内部定义的一种数据结构c;默认为16kbc;相当于4个磁盘块c;也就是说mysql每次从磁盘中读取一次数据是16KBc;要么不读取c;要读取就是16KBc;此值可以修改的。
我们对数据存储方式不做任何优化c;直接将数据库中表的记录存储在磁盘中c;假如某个表只有一个字段c;为int类型c;int占用4个bytec;每个磁盘块可以存储1000条记录c;100万的记录需要1000个磁盘块c;如果我们需要从这100万记录中检索所需要的记录c;需要读取1000个磁盘块的数据（需要1000次io）c;每次io需要9msc;那么1000次需要9000ms=9sc;100条数据随便一个查询就是9秒c;这种情况我们是无法接受的c;显然是不行的。
一个磁盘块4kbc;一条记录4bytec;4kb / 4byte = 4 * 1024 byte / 4 byte = 1024 条 （1K）
我们迫切需要这样的数据结构和算法：
我们来找找c;看是否能够找到这样的算法和数据结构。我们看一下常见的检索算法和数据结构。
从一组无序的数据中查找目标数据c;常见的方法是遍历查询c;n条数据c;时间复杂度为O(n)c;最快需要1次c;最坏的情况需要n次c;查询效率不稳定。
二分法查找也称为折半查找c;用于在一个有序数组中快速定义某一个需要查找的数据。
原理是：
先将一组无序的数据排序（升序或者降序）之后放在数组中c;此处用升序来举例说明：用数组中间位置的数据A和需要查找的数据F对比c;如果A=Fc;则结束查找；如果A<Fc;则将查找的范围缩小至数组中A数据右边的部分；如果A>Fc;则将查找范围缩小至数组中A数据左边的部分c;继续按照上面的方法直到找到F为止。
示例：
从下列有序数字中查找数字9c;过程如下
[1,2,3,4,5,6,7,8,9]
第1次查找：[1,2,3,4,5,6,7,8,9]中间位置值为5c;9>5c;将查找范围缩小至5右边的部分：[6、7、8、9]
第2次查找：[6、7、8、9]中间值为8c;9>8 c;将范围缩小至8右边部分：[9]
第3次查找：在[9]中查找9c;找到了。
可以看到查找速度是相当快的c;每次查找都会使范围减半c;如果我们采用顺序查找c;上面数据最快需要1次c;最多需要9次c;而二分法查找最多只需要3次c;耗时时间也比较稳定。
二分法查找时间复杂度是:O(logN)(N为数据量)c;100万数据查找最多只需要20次（ =1048576 ）
二分法查找数据的优点：定位数据非常快c;前提是：目标数组是有序的。
如果我们将mysql中表的数据以有序数组的方式存储在磁盘中c;那么我们定位数据步骤是：
步骤取出所有数据耗费的io次数太多c;步骤2耗费的内存空间太大c;还有新增数据的时候c;为了保证数组有序c;插入数据会涉及到数组内部数据的移动c;也是比较耗时的c;显然用这种方式存储数据是不可取的。
链表相当于在每个节点上增加一些指针c;可以和前面或者后面的节点连接起来c;就像一列火车一样c;每节车厢相当于一个节点c;车厢内部可以存储数据c;每个车厢和下一节车厢相连。
链表分为单链表和双向链表。
每个节点中有持有指向下一个节点的指针c;只能按照一个方向遍历链表c;结构如下：
//单项链表 class Node1{
private Object data;//存储数据
private Node1 nextNode;//指向下一个节点
}
每个节点中两个指针c;分别指向当前节点的上一个节点和下一个节点c;结构如下：
//双向链表
class Node2{
private Object data;//存储数据
private Node1 prevNode;//指向上一个节点
private Node1 nextNode;//指向下一个节点
}
链表的优点：
链表的缺点：
二叉树是每个结点最多有两个子树的树结构c;通常子树被称作“左子树”（left subtree）和“右子树” （right subtree）。二叉树常被用于实现二叉查找树和二叉堆。二叉树有如下特性：
数组[20,10,5,15,30,25,35]使用二叉查找树存储如下：
每个节点上面有两个指针（left,rigth）c;可以通过这2个指针快速访问左右子节点c;检索任何一个数据最多只需要访问3个节点c;相当于访问了3次数据c;时间为O(logN)c;和二分法查找效率一样c;查询数据还是比较快的。
但是如果我们插入数据是有序的c;如[5,10,15,20,30,25,35]c;那么结构就变成下面这样：
二叉树退化为了一个链表结构c;查询数据最差就变为了O(N)。
二叉树的优缺点：
查询数据的效率不稳定c;若树左右比较平衡的时c;最差情况为O(logN)c;如果插入数据是有序的c;退化为了链表c;查询时间变成了O(N)
数据量大的情况下c;会导致树的高度变高c;如果每个节点对应磁盘的一个块来存储一条数据c;需io次数大幅增加c;显然用此结构来存储数据是不可取的
平衡二叉树是一种特殊的二叉树c;所以他也满足前面说到的二叉查找树的两个特性c;同时还有一个特性：
平衡二叉树相对于二叉树来说c;树的左右比较平衡c;不会出现二叉树那样退化成链表的情况c;不管怎么插入数据c;最终通过一些调整c;都能够保证树左右高度相差不大于1。
这样可以让查询速度比较稳定c;查询中遍历节点控制在O(logN)范围内
如果数据都存储在内存中c;采用AVL树来存储c;还是可以的c;查询效率非常高。不过我们的数据是存在磁盘中c;用过采用这种结构c;每个节点对应一个磁盘块c;数据量大的时候c;也会和二叉树一样c;会导致树的高度变高c;增加了io次数c;显然用这种结构存储数据也是不可取的。
B杠树 c;千万不要读作B减树了c;B-树在是平衡二叉树上进化来的c;前面介绍的几种树c;每个节点上面只有一个元素c;而B-树节点中可以放多个元素c;主要是为了降低树的高度。
一棵m阶的B-Tree有如下特性【特征描述的有点绕c;看不懂的可以跳过c;看后面的图】：
每个节点最多有m个孩子c;R_233_11845@称为b树的阶
除了根节点和叶子节点外c;其它每个节点至少有Ceil(m/2)个孩子
若根节点不是叶子节点c;则至少有2个孩子
所有叶子节点都在同一层c;且不包含其它关键字信息
每个非终端节点包含n个关键字（健值）信息
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字c;且关键字升序排序
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于kic;但都大于k(i-1)
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Treec;首先定义一条记录为一个二元组[key, data] c;key为记录的键值c;对应表中的主键值c;data为一行记录中除主键外的数据。对于不同的记录c;key值互不相同。
B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支c;如下图所示为一个3阶的B-Tree：
每个节点占用一个盘块的磁盘空间c;一个节点上有两个升序排序的关键字和三个指向子树根节点的指针c;指针存储的是子节点所在磁盘块的地址。两个键将数据划分成的三个范围域c;对应三个指针指向的子树的数据的范围域。以根节点为例c;关键字为17和35c;P1指针指向的子树的数据范围为小于17c;P2指针指向的子树的数据范围为17~35c;P3指针指向的子树的数据范围为大于35。
模拟查找关键字29的过程：
根据根节点找到磁盘块1c;读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35）c;找到磁盘块1的指针P2
根据P2指针找到磁盘块3c;读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30）c;找到磁盘块3的指针P2
根据P2指针找到磁盘块8c;读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29
分析上面过程c;发现需要3次磁盘I/O操作c;和3次内存查找操作c;由于内存中的关键字是一个有序表结构c;可以利用二分法快速定位到目标数据c;而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-树相对于avl树c;通过在节点中增加节点内部数据的个数来减少磁盘的io操作。
上面我们说过mysql是采用页方式来读写数据c;每页是16KBc;我们用B-树来存储mysql的记录c;每个节点对应mysql中的一页（16KB）c;假如每行记录加上树节点中的1个指针占160Bytec;那么每个节点可以存储1000（16KB/160byte）条数据c;树的高度为3的节点大概可以存储（第一层1000+第二层10002+第三层10003）10亿条记录c;是不是非常惊讶c;一个高度为3个B-树大概可以存储10亿条记录c;我们从10亿记录中查找数据只需要3次io操作可以定位到目标数据所在的页c;而页内部的数据又是有序的c;然后将其加载到内存中用二分法查找c;是非常快的。
可以看出使用B-树定位某个值还是很快的(10亿数据中3次io操作+内存中二分法)c;但是也是有缺点的：
先看个b+树结构图：
b+树的特征
每个结点至多有m个子女
除根结点外,每个结点至少有[m/2]个子女c;根结点至少有两个子女
有k个子女的结点必有k个关键字
父节点中持有访问子节点的指针
父节点的关键字在子节点中都存在（如上面的1/20/35在每层都存在）c;要么是最小值c;要么是最大值c;如果节点中关键字是升序的方式c;父节点的关键字是子节点的最小值
最底层的节点是叶子节点
持范围查找
b+树与b-树的几点不同：
b+树中一个节点如果有k个关键字c;最多可以包含k个子节点（k个关键字对应k个指针）；而b-树对应k+1个子节点（多了一个指向子节点的指针）
b+树除叶子节点之外其他节点值存储关键字和指向子节点的指针c;而b-树还存储了数据c;这样同样大小情况下c;b+树可以存储更多的关键字
b+树叶子节点中存储了所有关键字及datac;并且多个节点用链表连接c;从上图中看子节点中数据从左向右是有序的c;这样快速可以支撑范围查找（先定位范围的最大值和最小值c;然后子节点中依靠链表遍历范围数据）
B-Tree和B+Tree该如何选择？
B-Tree因为非叶子结点也保存具体数据c;所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点c;每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中c;BTree查找某个关键字的效率更高
由于B+Tree所有的数据都在叶子结点c;并且结点之间有指针连接c;在找大于某个关键字或者小于某个关键字的数据的时候c;B+Tree只需要找到该关键字然后沿着链表遍历就可以了c;而B-Tree还需要遍历该关键字结点的根结点去搜索。
由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据c;而B+Tree非叶子结点只存储关键字信息c;而每个页的大小有限是有限的c;所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据c;B-Tree的深度会更大c;增大查询时的磁盘I/O次数c;进而影响查询效率。
InnoDB中的索引：
Innodb中有2种索引：主键索引（聚集索引）、辅助索引（非聚集索引）。
主键索引：每个表只有一个主键索引c;b+树结构c;叶子节点同时保存了主键的值也数据记录c;其他节点只存储主键的值。
辅助索引：每个表可以有多个c;b+树结构c;叶子节点保存了索引字段的值以及主键的值c;其他节点只存储索引指端的值。
@H_988_516@myISAM引擎中的索引：
B+树结构c;R_233_11845@yISM使用的是非聚簇索引c;非聚簇索引的两棵B+树看上去没什么不同c;节点的结构完全一致只是存储的内容不同而已c;主键索引B+树的节点存储了主键c;辅助键索引B+树存储了辅助键。表数据存储在独立的地方c;这两颗B+树的叶子节点都使用一个地址指向真正的表数据c;对于表数据来说c;这两个键没有任何差别。由于索引树是独立的c;通过辅助键检索无需访问主键的索引树。
如下图：为了更形象说明这两种索引的区别c;我们假想一个表存储了4行数据。其中Id作为主索引c;Name作为辅助索引c;图中清晰的显示了聚簇索引和非聚簇索引的差异。
我们看一下上图中数据检索过程。
InnoDB数据检索过程：
如果需要查询id=14的数据c;只需要在左边的主键索引中检索就可以了。
如果需要搜索name='ellison’的数据c;需要2步：
辅助索引这个查询过程在mysql中叫做回表。
@H_988_516@myISAM数据检索过程：
在索引中找到对应的关键字c;获取关键字对应的记录的地址
通过记录的地址查找到对应的数据记录
我们用的最多的是innodb存储引擎c;所以此处主要说一下innodb索引的情况c;innodb中最好是采用主键查询c;这样只需要一次索引c;如果使用辅助索引检索c;涉及到回表操作c;比主键查询要耗时一些。
innodb中辅助索引为什么不像myisam那样存储记录的地址？
表中的数据发生变更的时候c;会影响其他记录地址的变化c;如果辅助索引中记录数据的地址c;此时会受影响c;而主键的值一般是很少更新的c;当页中的记录发生地址变更的时候c;对辅助索引是没有影响的。我们来看一下mysql中页的结构c;页是真正存储记录的地方c;对应B+树中的一个节点c;也是mysql中读写数据的最小单位c;页的结构设计也是相当有水平的c;能够加快数据的查询。
@H_988_516@mysql中页是innodb中存储数据的基本单位c;也是mysql中管理数据的最小单位c;和磁盘交互的时候都是以页来进行的c;默认是16kbc;R_233_11845@ysql中采用b+树存储数据c;页相当于b+树中的一个节点。
页的结构如下图：
每个Page都有通用的头和尾c;但是中部的内容根据Page的类型不同而发生变化。Page的头部里有我们关心的一些数据c;下图把Page的头部详细信息显示出来：
我们重点关注和数据组织结构相关的字段：Page的头部保存了两个指针c;分别指向前一个Page和后一个Pagec;根据这两个指针我们很容易想象出Page链接起来就是一个双向链表的结构c;如下图：
再看看Page的主体内容c;我们主要关注行数据和索引的存储c;他们都位于Page的User Records部分c;User Records占据Page的大部分空间c;User Records由一条一条的Record组成。在一个Page内部c;单链表的头尾由固定内容的两条记录来表示c;字符串形式的"Infimum"代表开头c;"Supremum"代表结尾c;这两个用来代表开头结尾的Record存储在system Records的c;Infinum、Supremum和User Records组成了一个单向链表结构。最初数据是按照插入的先后顺序排列的c;但是随着新数据的插入和旧数据的删除c;数据物理顺序会变得混乱c;但他们依然通过链表的方式保持着逻辑上的先后顺序c;如下图：
把User Record的组织形式和若干Page组合起来c;就看到了稍微完整的形式。
innodb为了快速查找记录c;在页中定义了一个称之为page directory的目录槽（slots）,每个槽位占用两个字节（用于保存指向记录的地址）c;page directory中的多个slot组成了一个有序数组（可用于二分法快速定位记录c;向下看）c;行记录被Page Directory逻辑的分成了多个块c;块与块之间是有序的c;能够加速记录的查找c;如下图：
看上图c;每个行记录的都有一个n_owned的区域（图中粉色区域）c;n_owned标识所属的slot这个这个块有多少条数据c;伪记录Infimum的n_owned值总是1c;记录Supremum的n_owned的取值范围为[1,8]c;其他用户记录n_owned的取值范围[4,8]c;并且只有每个块中最大的那条记录的n_owned才会有值c;其他的用户记录的n_owned为0。
在page中查询数据的时候c;先通过b+树中查询方法定位到数据所在的页c;然后将页内整体加载到内存中c;通过二分法在page directory中检索数据c;缩小范围c;比如需要检索7c;通过二分法查找到7位于slot2和slot3所指向的记录中间c;然后从slot3指向的记录5开始向后向后一个个找c;可以找到记录7c;如果里面没有7c;走到slot2向的记录8结束。
n_owned范围控制在[4,8]内c;能保证每个slot管辖的范围内数据量控制在[4,8]个c;能够加速目标数据的查找c;当有数据插入的时候c;page directory为了控制每个slot对应块中记录的个数（[4,8]）c;此时page directory中会对slot的数量进行调整。
b+树中叶子页之间用双向链表连接的c;能够实现范围查找
整体上来说mysql中的索引用到了b+树c;链表c;二分法查找c;做到了快速定位目标数据c;快速范围查找。
分为聚集索引和非聚集索引。
每个表有且一定会有一个聚集索引c;整个表的数据存储在聚集索引中c;R_233_11845@ysql索引是采用B+树结构保存在文件中c;叶子节点存储主键的值以及对应记录的数据c;非叶子节点不存储记录的数据c;只存储主键的值。当表中未指定主键时c;R_233_11845@ysql内部会自动给每条记录添加一个隐藏的rowid字段（默认4个字节）作为主键c;用rowid构建聚集索引。
聚集索引在mysql中又叫主键索引。
也是b+树结构c;不过有一点和聚集索引不同c;非聚集索引叶子节点存储字段（索引字段）的值以及对应记录主键的值c;其他节点只存储字段的值（索引字段）。
每个表可以有多个非聚集索引。
@H_447_727@mysql中非聚集索引分为单列索引：即一个索引只包含一个列。
多列索引（又称复合索引）： 即一个索引包含多个列。
唯一索引： 索引列的值必须唯一c;允许有一个空值。
看一张图：
上面的表中有2个索引：id作为主键索引c;name作为辅助索引。
innodb我们用的最多c;我们只看图中左边的innodb中数据检索过程：
如果需要查询id=14的数据c;只需要在左边的主键索引中检索就可以了。
如果需要搜索name='ellison’的数据c;需要2步：
辅助索引相对于主键索引多了第二步。
方式1：
create [unique] index 索引名称 on 表名(列名[(length)]);
方式2：
alter 表名 add [unique] index 索引名称 on (列名[(length)]);
如果字段是char、varchar类型c;length可以小于字段实际长度c;如果是blog、text等长文本类型c;必须指定length。
[unique]：中括号代表可以省略c;如果加上了uniquec;表示创建唯一索引。
如果table后面只写一个字段c;就是单列索引c;如果写多个字段c;就是复合索引c;多个字段之间用逗号隔开。
drop index 索引名称 on 表名;
查看某个表中所有的索引信息如下：
show index from 表名;
可以先删除索引c;再重建索引。
/*建库javacode2018*/
DROP DATABASE IF EXISTS javacode2018;
CREATE DATABASE javacode2018;
USE javacode2018;
/*建表test1*/
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (
id INT NOT NULL COMMENT '编号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
sex TinYINT NOT NULL COMMENT '性别,1：男c;2：女',
email VARCHAR(50)
);
/*准备数据*/
DROP PROCEDURE IF EXISTS proc1;
DELIMITER $
CREATE PROCEDURE proc1()
BEGIN
DECLARE i INT DEFAULT 1;
START transaCTION;
WHILE i <= 2000000 DO
INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(@H_5_1600@mod(i,2),1,2),concat('javacode',i,'@163.com'));
SET i = i + 1;
if i%10000=0 THEN
COMMIT;
START transaCTION;
END IF;
END WHILE;
COMMIT;
END $
DELIMITER ;
CALL proc1();
SELECT count(*) FROM test1;
上图中使用存储过程循环插入了200万记录c;表中有4个字段c;除了sex列c;其他列的值都是没有重复的c;表中还未建索引。
插入的200万数据中c;idc;namec;email的值都是没有重复的。
@H_355_732@mysql> SELEct * from test1 a where a.id = 1; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@H_948_1772@@163.com | +----+-----------+-----+-------------------+ 1 row in set (0.77 sec)
上面我们按id查询了一条记录耗时770毫秒c;我们在id上面创建个索引感受一下速度。
@H_355_732@mysql> create index idx1 on test1 (id); Query OK, 0 rows affected (2.82 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELEct * from test1 a where a.id = 1; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@H_948_1772@@163.com | +----+-----------+-----+-------------------+ 1 row in set (0.00 sec)
上面的查询是不是非常快c;耗时1毫秒都不到。
我们在Name上也创建个索引c;感受一下查询的神速c;如下：
@H_355_732@mysql> create unique index idx2 on test1(name); Query OK, 0 rows affected (9.67 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELEct * from test1 where name = 'javacode1'; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@H_948_1772@@163.com | +----+-----------+-----+-------------------+ 1 row in set (0.00 sec)
通过email检索一下数据
@H_355_732@mysql> SELEct * from test1 a where a.email = 'javacode1000085@163.com'; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 1000085 | javacode1000085 | 1 | javacode1000085@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (1.28 sec)
耗时1秒多c;回头去看一下插入数据的sqlc;我们可以看到所有的email记录c;每条记录的前面15个字符是不一样的c;结尾是一样的（都是@163.com）c;通过前面15个字符就可以定位一个email了c;那么我们可以对email创建索引的时候指定一个长度为15c;这样相对于整个email字段更短一些c;查询效果是一样的c;这样一个页中可以存储更多的索引记录c;命令如下：
@H_355_732@mysql> create index idx3 on test1 (email(15)); Query OK, 0 rows affected (7.67 sec) Records: 0 Duplicates: 0 Warnings: 0
然后看一下查询效果：
@H_355_732@mysql> SELEct * from test1 a where a.email = 'javacode1000085@163.com'; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 1000085 | javacode1000085 | 1 | javacode1000085@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
耗时不到1毫秒c;神速。
我们看一下test1表中的所有索引c;如下
@H_355_732@mysql> show index from test1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | NON_UNIQUE | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test1 | 0 | idx2 | 1 | name | A | 1992727 | NULL | NULL | | BTREE | | | | test1 | 1 | idx1 | 1 | id | A | 1992727 | NULL | NULL | | BTREE | | | | test1 | 1 | idx3 | 1 | email | A | 1992727 | 15 | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
可以看到test1表中3个索引的详细信息(索引名称、类型c;字段)。
我们删除idx1c;然后再列出test1表所有索引c;如下：
@H_355_732@mysql> drop index idx1 on test1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from test1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | NON_UNIQUE | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test1 | 0 | idx2 | 1 | name | A | 1992727 | NULL | NULL | | BTREE | | | | test1 | 1 | idx3 | 1 | email | A | 1992727 | 15 | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
本篇主要是mysql中索引管理相关一些操作c;属于基础知识c;必须掌握。
当我们对某个字段的值进行某种检索的时候c;如果这个检索过程中c;我们能够快速定位到目标数据所在的页c;有效的降低页的io操作c;而不需要去扫描所有的数据页的时候c;我们认为这种情况能够有效的利用索引c;也称这个检索可以走索引c;如果这个过程中不能够确定数据在那些页中c;我们认为这种情况下索引对这个查询是无效的c;此查询不走索引。
如上图c;所有的数据都是唯一的c;查询105的记录c;过程如下：
数据如上图c;查询[55,150]所有记录c;由于页和页之间是双向链表升序结构c;页内部的数据是单项升序链表结构c;所以只用找到范围的起始值所在的位置c;然后通过依靠链表访问两个位置之间所有的数据即可c;过程如下：
数据如上图。
查询以 f 开头的所有记录
过程如下：
将P1数据加载到内存中
在P1页的记录中采用二分法找到最后一个小于等于f的值c;这个值是fc;以及第一个大于f的c;这个值是zc;f指向叶节点P3c;z指向叶节点P6c;此时可以断定以f开头的记录可能存在于[P3,P6)这个范围的页内c;即P3、P4、P5这三个页中
加载P3这个页c;在内部以二分法找到第一条f开头的记录c;然后以链表方式继续向后访问P4、P5中的记录c;即可以找到所有已f开头的数据
包含的查询在sql中的写法是%f%c;通过索引我们还可以快速定位所在的页么？
可以看一下上面的数据c;f在每个页中都存在c;我们通过P1页中的记录是无法判断包含f的记录在那些页的c;只能通过io的方式加载所有叶子节点c;并且遍历所有记录进行过滤c;才可以找到包含f的记录。
所以如果使用了%值%这种方式c;索引对查询是无效的。
当b+树的数据项是复合的数据结构c;比如(name,age,seX)的时候c;b+树是按照从左到右的顺序来建立搜索树的c; 比如当(张三,20,F)这样的数据来检索的时候c;b+树会优先比较name来确定下一步的所搜方向c;如果name相同再依次比较age和sexc;最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候c;b+树就不知道下一步该查哪个节点c;因为建立搜索树的时候name就是第一个比较因子c;必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时c;b+树可以用name来指定搜索方向c;但下一个字段age的缺失c;所以只能把名字等于张三的数据都找到c;然后再匹配性别是F的数据了c; 这个是非常重要的性质c;即索引的最左匹配特性。
来一些示例我们体验一下。
下图中是3个字段(a,b,C)的联合索引c;索引中数据的顺序是以 a asc,b asc,c asc 这种排序方式存储在节点中的c;索引先以a字段升序c;如果a相同的时候c;以b字段升序c;b相同的时候c;以c字段升序c;节点中每个数据认真看一下。
由于页中的记录是以a asc,b asc,c asc这种排序方式存储的c;所以a字段是有序的c;可以通过二分法快速检索到c;过程如下：
方法和上面的一样c;可以确定a=1 and b=5的记录位于{1,1,1}和{1,5,1}关联的范围内c;查找过程和a=1查找步骤类似。
这种情况通过P1页中的记录c;是无法判断b=1的记录在那些页中的c;只能加锁索引树所有叶子节点c;对所有记录进行遍历c;然后进行过滤c;此时索引是无效的。
这种情况和查询b=1也一样c;也只能扫描所有叶子节点c;此时索引也无效了。
这种也是无法利用索引的c;也只能对所有数据进行扫描c;一条条判断了c;此时索引无效。
这种只能利用到索引中的a字段了c;通过a确定索引范围c;然后加载a关联的所有记录c;再对c的值进行过滤。
这种情况只能先确定a=1 and b>=0所在页的范围c;然后对这个范围的所有页进行遍历c;c字段在这个查询的过程中c;是无法确定c的数据在哪些页的c;此时我们称c是不走索引的c;只有a、b能够有效的确定索引页的范围。
类似这种的还有>、<、between andc;多字段索引的情况下c;R_233_11845@ysql会一直向右匹配直到遇到范围查询(>、<、between、likE)就停止匹配。
上面说的各种情况c;大家都多看一下图中数据c;认真分析一下查询的过程c;基本上都可以理解了。
上面这种查询叫做最左匹配原则。
我们看2个有序数组
[1,2,3,4,5,6,7,8,8,9,10]
[1,1,1,1,1,8,8,8,8,8]
上面2个数组是有序的c;都是10条记录c;如果我需要检索值为8的所有记录c;那个更快一些？
咱们使用二分法查找包含8的所有记录过程如下：先使用二分法找到最后一个小于8的记录c;然后沿着这条记录向后获取下一个记录c;和8对比c;知道遇到第一个大于8的数字结束c;或者到达数组末尾结束。
采用上面这种方法找到8的记录c;第一个数组中更快的一些。因为第二个数组中含有8的比例更多的c;需要访问以及匹配的次数更多一些。
这里就@R_234_9446@的区分度问题：
索引区分度 = count(disTint 记录) / count(记录)。
当索引区分度高的时候c;检索数据更快一些c;索引区分度太低c;说明重复的数据比较多c;检索的时候需要访问更多的记录才能够找到所有目标数据。
当索引区分度非常小的时候c;基本上接近于全索引数据的扫描了c;此时查询速度是比较慢的。
第一个数组索引区分度为1c;第二个区分度为0.2c;所以第一个检索更快的一些。
/*建库javacode2018*/
DROP DATABASE IF EXISTS javacode2018;
CREATE DATABASE javacode2018;
USE javacode2018;
/*建表test1*/
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (
id INT NOT NULL COMMENT '编号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
sex TinYINT NOT NULL COMMENT '性别,1：男c;2：女',
email VARCHAR(50)
);
/*准备数据*/
DROP PROCEDURE IF EXISTS proc1;
DELIMITER $
CREATE PROCEDURE proc1()
BEGIN
DECLARE i INT DEFAULT 1;
START transaCTION;
WHILE i <= 4000000 DO
INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(@H_5_1600@mod(i,2),1,2),concat('javacode',i,'@163.com'));
SET i = i + 1;
if i%10000=0 THEN
COMMIT;
START transaCTION;
END IF;
END WHILE;
COMMIT;
END $
DELIMITER ;
CALL proc1();
上面插入的400万数据c;除了sex列c;其他列的值都是没有重复的。
按照id查询记录
@H_355_732@mysql> SELEct * from test1 where id = 1; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@H_948_1772@@163.com | +----+-----------+-----+-------------------+ 1 row in set (1.91 sec)
id=1的数据c;表中只有一行c;耗时近2秒c;由于id列无索引c;只能对400万数据进行全表扫描。
test1表中没有明确的指定主键c;我们将id设置为主键：
@H_355_732@mysql> alter table test1 @H_384_736@modify id int not null priMary key; Query OK, 0 rows affected (10.93 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from test1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | NON_UNIQUE | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test1 | 0 | PRIMary | 1 | id | A | 3980477 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
id被置为主键之后c;会在id上建立聚集索引c;随便检索一条我们看一下效果：
@H_355_732@mysql> SELEct * from test1 where id = 1000000; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 1000000 | javacode1000000 | 2 | javacode1000000@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
这个速度很快c;这个走的是上面介绍的唯一记录检索。
@H_355_732@mysql> SELEct count(*) from test1 where id between 100 and 110; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.00 sec)
速度也很快c;id上有主键索引c;这个采用的上面介绍的范围查找可以快速定位目标数据。
但是如果范围太大c;跨度的page也太多c;速度也会比较慢c;如下：
@H_355_732@mysql> SELEct count(*) from test1 where id between 1 and 2000000; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (1.17 sec)
上面id的值跨度太大c;1所在的页和200万所在页中间有很多页需要读取c;所以比较慢。
所以使用between and的时候c;区间跨度不要太大。
平时我们做项目的时候c;建议少用表连接c;比如电商中需要查询订单的信息和订单中商品的名称c;可以先查询查询订单表c;然后订单表中取出商品的iD列表c;采用in的方式到商品表检索商品信息c;由于商品id是商品表的主键c;所以检索速度还是比较快的。
通过id在400万数据中检索100条数据c;看看效果：
@H_355_732@mysql> SELEct * from test1 a where a.id in (100000, 100001, 100002, 100003, 100004, 100005, 100006, 100007, 100008, 100009, 100010, 100011, 100012, 100013, 100014, 100015, 100016, 100017, 100018, 100019, 100020, 100021, 100022, 100023, 100024, 100025, 100026, 100027, 100028, 100029, 100030, 100031, 100032, 100033, 100034, 100035, 100036, 100037, 100038, 100039, 100040, 100041, 100042, 100043, 100044, 100045, 100046, 100047, 100048, 100049, 100050, 100051, 100052, 100053, 100054, 100055, 100056, 100057, 100058, 100059, 100060, 100061, 100062, 100063, 100064, 100065, 100066, 100067, 100068, 100069, 100070, 100071, 100072, 100073, 100074, 100075, 100076, 100077, 100078, 100079, 100080, 100081, 100082, 100083, 100084, 100085, 100086, 100087, 100088, 100089, 100090, 100091, 100092, 100093, 100094, 100095, 100096, 100097, 100098, 100099); +--------+----------------+-----+------------------------+ | id | name | sex | email | +--------+----------------+-----+------------------------+ | 100000 | javacode100000 | 2 | javacode100000@H_948_1772@@163.com | | 100001 | javacode100001 | 1 | javacode100001@H_948_1772@@163.com | | 100002 | javacode100002 | 2 | javacode100002@H_948_1772@@163.com | ....... | 100099 | javacode100099 | 1 | javacode100099@H_948_1772@@163.com | +--------+----------------+-----+------------------------+ 100 rows in set (0.00 sec)
耗时不到1毫秒c;还是相当快的。
@H_355_732@mysql> create index idx1 on test1(name); Query OK, 0 rows affected (13.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index idx2 on test1(sex); Query OK, 0 rows affected (6.77 sec) Records: 0 Duplicates: 0 Warnings: 0
看一下查询：
@H_355_732@mysql> SELEct * from test1 where name='javacode3500000' and sex=2; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
上面查询速度很快c;name和sex上各有一个索引c;觉得上面走哪个索引？
有人说name位于where第一个c;所以走的是name字段所在的索引c;过程可以解释为这样：
我们看一下name='javacode3500000’检索速度c;确实很快c;如下：
@H_355_732@mysql> SELEct * from test1 where name='javacode3500000'; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
走name索引c;然后再过滤c;确实可以c;速度也很快c;果真和where后字段顺序有关么？我们把name和sex的顺序对调一下c;如下：
@H_355_732@mysql> SELEct * from test1 where sex=2 and name='javacode3500000'; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
速度还是很快c;这次是不是先走sex索引检索出数据c;然后再过滤name呢？我们先来看一下sex=2查询速度：
@H_355_732@mysql> SELEct count(id) from test1 where sex=2; +-----------+ | count(id) | +-----------+ | 2000000 | +-----------+ 1 row in set (0.36 sec)
看上面c;查询耗时360毫秒c;200万数据c;如果走sex肯定是不行的。
我们使用explain来看一下：
@H_355_732@mysql> explain SELEct * from test1 where sex=2 and name='javacode3500000'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | SELEct_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test1 | NULL | ref | idx1,idx2 | idx1 | 62 | const | 1 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
possible_keys：列出了这个查询可能会走两个索引（idx1、idx2）
实际上走的却是idx1（key列：实际走的索引）。
当多个条件中有索引的时候c;并且关系是and的时候c;会走索引区分度高的c;显然name字段重复度很低c;走name查询会更快一些。
看两个查询
@H_355_732@mysql> SELEct count(*) from test1 a where a.name like 'javacode1000%'; +----------+ | count(*) | +----------+ | 1111 | +----------+ 1 row in set (0.00 sec) mysql> SELEct count(*) from test1 a where a.name like '%javacode1000%'; +----------+ | count(*) | +----------+ | 1111 | +----------+ 1 row in set (1.78 sec)
上面第一个查询可以利用到name字段上面的索引c;下面的查询是无法确定需要查找的值所在的范围的c;只能全表扫描c;无法利用索引c;所以速度比较慢c;这个过程上面有说过。
当需要查询的数据在索引树中不存在的时候c;需要再次到聚集索引中去获取c;这个过程叫做回表c;如查询：
@H_355_732@mysql> SELEct * from test1 where name='javacode3500000'; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 3500000 | javacode3500000 | 2 | javacode3500000@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
上面查询是*c;由于name列所在的索引中只有name、id两个列的值c;不包含sex、emailc;所以上面过程如下：
查询中采用的索引树中包含了查询所需要的所有字段的值c;不需要再去聚集索引检索数据c;这种叫索引覆盖。
我们来看一个查询：
SELEct id,name from test1 where name='javacode3500000';
name对应idx1索引c;id为主键c;所以idx1索引树叶子节点中包含了name、id的值c;这个查询只用走idx1这一个索引就可以了c;如果SELEct后面使用*c;还需要一次回表获取sex、email的值。
所以写sql的时候c;尽量避免使用*c;*可能会多一次回表操作c;需要看一下是否可以使用索引覆盖来实现c;效率更高一些。
简称ICPc;Index Condition Pushdown(ICp)是MySQL 5.6中新特性c;是一种在存储引擎层使用索引过滤数据的一种优化方式c;ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
举个例子来说一下：
我们需要查询name以javacode35开头的c;性别为1的记录数c;sql如下：
@H_355_732@mysql> SELEct count(id) from test1 a where name like 'javacode35%' and sex = 1; +-----------+ | count(id) | +-----------+ | 55556 | +-----------+ 1 row in set (0.19 sec)
过程：
上面的过程中需要走name索引以及需要回表操作。
如果采用ICP的方式c;我们可以这么做c;创建一个(name,seX)的组合索引c;查询过程如下：
这个过程中不需要回表操作了c;通过索引的数据就可以完成整个条件的过滤c;速度比上面的更快一些。
@H_355_732@mysql> insert into test1 (id,name,sex,email) values (4000001,'1',1,'javacode2018@163.com'); Query OK, 1 row affected (0.00 sec) mysql> SELEct * from test1 where name = '1'; +---------+------+-----+----------------------+ | id | name | sex | email | +---------+------+-----+----------------------+ | 4000001 | 1 | 1 | javacode2018@H_948_1772@@163.com | +---------+------+-----+----------------------+ 1 row in set (0.00 sec) mysql> SELEct * from test1 where name = 1; +---------+------+-----+----------------------+ | id | name | sex | email | +---------+------+-----+----------------------+ | 4000001 | 1 | 1 | javacode2018@H_948_1772@@163.com | +---------+------+-----+----------------------+ 1 row in set, 65535 warnings (3.30 sec)
上面3条sqlc;我们插入了一条记录。
第二条查询很快c;第三条用name和1比较c;name上有索引c;name是字符串类型c;字符串和数字比较的时候c;会将字符串强制转换为数字c;然后进行比较c;所以第二个查询变成了全表扫描c;只能取出每条数据c;将name转换为数字和1进行比较。
数字字段和字符串比较什么效果呢？如下：
@H_355_732@mysql> SELEct * from test1 where id = '4000000'; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 4000000 | javacode4000000 | 2 | javacode4000000@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec) mysql> SELEct * from test1 where id = 4000000; +---------+-----------------+-----+-------------------------+ | id | name | sex | email | +---------+-----------------+-----+-------------------------+ | 4000000 | javacode4000000 | 2 | javacode4000000@H_948_1772@@163.com | +---------+-----------------+-----+-------------------------+ 1 row in set (0.00 sec)
id上面有主键索引c;id是int类型的c;可以看到c;上面两个查询都非常快c;都可以正常利用索引快速检索c;所以如果字段是数组类型的c;查询的值是字符串还是数组都会走索引。
@H_355_732@mysql> SELEct a.name+1 from test1 a where a.name = 'javacode1'; +----------+ | a.name+1 | +----------+ | 1 | +----------+ 1 row in set, 1 warning (0.00 sec) mysql> SELEct * from test1 a where concat(a.name,'1') = 'javacode11'; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@H_948_1772@@163.com | +----+-----------+-----+-------------------+ 1 row in set (2.88 sec)
name上有索引c;上面查询c;第一个走索引c;第二个不走索引c;第二个使用了函数之后c;name所在的索引树是无法快速定位需要查找的数据所在的页的c;只能将所有页的记录加载到内存中c;然后对每条数据使用函数进行计算之后再进行条件判断c;此时索引无效了c;变成了全表数据扫描。
结论：索引字段使用函数查询使索引无效。
@H_355_732@mysql> SELEct * from test1 a where id = 2 - 1; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@H_948_1772@@163.com | +----+-----------+-----+-------------------+ 1 row in set (0.00 sec) mysql> SELEct * from test1 a where id+1 = 2; +----+-----------+-----+-------------------+ | id | name | sex | email | +----+-----------+-----+-------------------+ | 1 | javacode1 | 1 | javacode1@H_948_1772@@163.com | +----+-----------+-----+-------------------+ 1 row in set (2.41 sec)
id上有主键索引c;上面查询c;第一个走索引c;第二个不走索引c;第二个使用运算符c;id所在的索引树是无法快速定位需要查找的数据所在的页的c;只能将所有页的记录加载到内存中c;然后对每条数据的id进行计算之后再判断是否等于1c;此时索引无效了c;变成了全表数据扫描。
结论：索引字段使用了函数将使索引无效。
我们有个订单表t_order(id,user_id,addtime,pricE)c;经常会查询某个用户的订单c;并且按照addtime升序排序c;应该怎么创建索引呢？我们来分析一下。
在user_id上创建索引c;我们分析一下这种情况c;数据检索的过程：
我们要知道当数据量非常大的时候c;排序还是比较慢的c;可能会用到磁盘中的文件c;有没有一种方式c;查询出来的数据刚好是排好序的。
我们再回顾一下mysql中b+树数据的结构c;记录是按照索引的值排序组成的链表c;如果将user_id和addtime放在一起组成联合索引(user_id,addtimE)c;这样通过user_id检索出来的数据自然就是按照addtime排好序的c;这样直接少了一步排序操作c;效率更好c;如果需addtime降序c;只需要将结果翻转一下就可以了。
以上是大佬教程为你收集整理的MySQL索引【详解】全部内容，希望文章能够帮你解决MySQL索引【详解】所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。