提示:以下是本篇文章正文内容,下面案例可供参考
一、索引概述
1.1 索引是什么
索引是帮助数据库高效获取数据的数据结构。
1.2 索引的分类
1.2.1 从存储结构上来划分
Btree索引(B+tree,B-tree)
哈希索引
full-index全文索引
RTree
1.2.2 从应用层次上来划分
普通索引:即一个索引只包含单个列,一个表可以有多个单列索引。
唯一索引:索引列的值必须唯一,但允许有空值。
复合索引:一个索引包含多个列。
1.2.3 从表记录的排列顺序和索引的排列顺序是否一致来划分
聚集索引:表记录的排列顺序和索引的排列顺序一致。
非聚集索引:表记录的排列顺序和索引的排列顺序不一致。
1)简单概括
聚集索引:就是以主键创建的索引。
非聚集索引:就是以非主键创建的索引(也叫做二级索引)。
2) 详细概括:
聚集索引
优势:聚集索引表记录的排列顺序和索引的排列顺序一致,所以查询效率快,因为只要找到第一个索引值记录,
其余的连续性的记录在物理表中也会连续存放,一起就可以查询到。
缺点:新增比较慢,因为为了保证表中记录的物理顺序和索引顺序一致,在记录插入的时候,会对数据页重新排序。
非聚集索引:
索引的逻辑顺序与磁盘上行的物理存储顺序不同,非聚集索引在叶子节点存储的是主键和索引列,
当我们使用非聚集索引查询数据时,需要拿到叶子上的主键再去表中查到想要查找的数据。这个过程就是我们所说的回表。
3) 聚集索引和非聚集索引的区别:
聚集索引在叶子节点存储的是表中的数据。
非聚集索引在叶子节点存储的是主键和索引列。
4) 例子 查字典
通过拼音查找直接查找到对应汉字页码 可理解 为聚集索引 即逻辑顺序和物理顺序一致的情况
通过部首查找到对应汉字,对应部首下的汉字无顺序但是排列在一起。后在查找到对应页码 为非聚集索引 即逻辑顺序和物理顺序不一致的情况
二、索引底层数据结构
2.1 哈希索引
可能直接想到的就是用哈希表来实现快速查找,就像我们平时用的hashmap一样,value = get(key) O(1)时间复杂度一步到位,
确实,哈希索引是一种方式
1) 定义
哈希索引就是采用一定的哈希算法,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
本质上就是把键值换算成新的哈希值,根据这个哈希值来定位。
2)局限性
哈希索引没办法利用索引完成排序。
不能进行多字段查询。
在有大量重复键值的情况下,哈希索引的效率也是极低的(出现哈希碰撞问题)。
不支持范围查询。
在MySQL常用的InnoDB引擎中,还是使用B+树索引比较多。
InnoDB是自适应哈希索引的(hash索引的创建由==InnoDB存储引擎自动优化创建==,我们干预不了)。
2.2 设计索引的数据结构
假设要查询某个区间的数据,我们只需要拿到区间的起始值,然后在树中进行查找。
如数据为:6、10、15、23、27、33、42
1)查询[7,30]区间的数据
当查找到起点节点10后,再顺着链表进行遍历,直到链表中的节点数据大于区间的终止值为止。所有遍历到的数据,就是符合区间值的所有数据。
2)还可以怎么优化呢?
利用二叉查找树,区间查询的功能已经实现了。但是,为了节省内存,我们只能把树存储在硬盘中。
那么,每个节点的读取或者访问,都对应一次硬盘IO操作。每次查询数据时磁盘IO操作的次数,也叫做IO渐进复杂度,也就是树的高度。
所以,我们要减少磁盘IO操作的次数,也就是要降低树的高度。
结构优化过程如下图所示:
这里将二叉树变为了M叉树,降低了树的高度,那么这个M应该选择多少才合适呢?
问题:对于相同个数的数据构建m叉树索引,m叉树中的m越大,那树的高度就越小,那m叉树中的m是不是越大越好呢?到底多大才合适呢?
不管是内存中的数据还是磁盘中的数据,操作系统都是按页(一页的大小通常是4kb,这个值可以通过getconfig(PAGE_SIZE)命令查看)来读取的,一次只会读取一页的数据。
如果要读取的数据量超过了一页的大小,就会触发多次IO操作。所以在选择m大小的时候,要尽量让每个节点的大小等于一个页的大小。
一般实际应用中,出度d(树的分叉数)是非常大的数字,通常超过100;树的高度(h)非常小,通常不超过3。
2.3 B树
顺着解决问题的思路知道了我们想要的数据结构是什么。目前索引常用的数据结构是B+树,先介绍一下什么是B树(也就是B-树)。
1)B树的特点:
关键字分布在整棵树的所有节点。
任何一个关键字出现且只出现在一个节点中。
搜索有可能在非叶子节点结束。
其搜索性能等价于在关键字全集内做一次二分查找。
如下图所示:
2.4 B+树
了解了B树,再来看一下B+树,也是MySQL索引大部分情况所使用的数据结构。
1)B+树基本特点
非叶子节点的子树指针与关键字个数相同。
非叶子节点的子树指针P[i],指向关键字属于 [k[i],K[i+1])的子树(注意:区间是前闭后开)。
为所有叶子节点增加一个链指针。
所有关键字都在叶子节点出现。
这些基本特点是为了满足以下的特性。
2)B+树的特性
所有的关键字都出现在叶子节点的链表中,且链表中的关键字是有序的。
搜索只在叶子节点命中。
非叶子节点相当于是叶子节点的索引层,叶子节点是存储关键字数据的数据层。
3)相对B树,B+树做索引的优势
B+树的磁盘读写代价更低。B+树的内部没有指向关键字具体信息的指针,所以其内部节点相对B树更小,如果把所有关键字存放在同一块盘中,那么盘中所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相应的,IO读写次数就降低了。
树的查询效率更加稳定。B+树所有数据都存在于叶子节点,所有关键字查询的路径长度相同,每次数据的查询效率相当。而B树可能在非叶子节点就停止查找了,所以查询效率不够稳定。
B+树只需要去遍历叶子节点就可以实现整棵树的遍历。
2.5 MongoDB的索引为什么选择B树,而MySQL的索引是B+树?
因为MongoDB不是传统的关系型数据库,而是以Json格式作为存储的NoSQL非关系型数据库,目的就是高性能、高可用、易扩展。摆脱了关系模型,所以范围查询和遍历查询的需求就没那么强烈了。
2.6 MyISAM存储引擎和InnoDB存储引擎的索引有什么区别
1)MyISAM存储引擎
主键索引
MyISAM的索引文件(.MYI)和数据文件(.MYD)文件是分离的,索引文件仅保存记录所在页的指针(物理位置),通过这些指针来读取页,进而读取被索引的行。
树中的叶子节点保存的是对应行的物理位置。通过该值,存储引擎能顺利地进行回表查询,得到一行完整记录。
同时,每个叶子也保存了指向下一个叶子的指针,从而方便叶子节点的范围遍历。
辅助索引
在MyISAM中,主键索引和辅助索引在结构上没有任何区别,只是主键索引要求key是唯一的,而辅助索引的key可以重复。
2)Innodb存储引擎
主键索引
InnoDB主键索引中既存储了主健值,又存储了行数据。
辅助索引
对于辅助索引,InnoDB采用的方式是在叶子节点中保存主键值,通过这个主键值来回表查询到一条完整记录,因此按辅助索引检索其实进行了二次查询,效率是没有主键索引高的。
三、MySQL如何添加索引
1) 添加PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2)添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column`)
3)添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name (`column` )
4)添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT (`column`)
5)添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`)
四、MySQL索引失效
在上一节中了解了索引的多种数据结构,以及B树和B+树的对比等,大家应该对索引的底层实现有了初步的了解。这一节从应用层的角度出发,看一下如何建索引更能满足我们的需求,以及MySQL索引什么时候会失效的问题。
先来思考一个小问题。
问题:当查询条件为2个及2个以上时,是创建多个单列索引还是创建一个联合索引好呢?它们之间的区别是什么?哪个效率高呢?
先来建立一些单列索引进行测试:
这里建立了一张表,里面建立了三个单列索引userId,mobile,billMonth。然后进行多列查询。
explain select * from `t_mobilesms_11` where userid = '1' and mobile = '13504679876' and billMonth = '1998-03'
我们发现查询时只用到了userid这一个单列索引,这是为什么呢?因为这取决于MySQL优化器的优化策略。
当多条件联合查询时,优化器会评估哪个条件的索引效率高,它会选择最佳的索引去使用。也就是说,此处三个索引列都可能被用到,只不过优化器判断只需要使用userid这一个索引就能完成本次查询,故最终explain展示的key为userid。
4.1 总结
多个单列索引在多条件查询时优化器会选择最优索引策略,可能只用一个索引,也可能将多个索引都用上。
但是多个单列索引底层会建立多个B+索引树,比较占用空间,也会浪费搜索效率 所以多条件联合查询时最好建联合索引。
那联合索引就可以三个条件都用到了吗?会出现索引失效的问题吗?
4.2 联合索引失效问题
该部分参考并引用文章:一张图搞懂MySQL的索引失效(https://segmentfault.com/a/1190000021464570)
创建user表,然后建立 name, age, pos, phone 四个字段的联合索引 全值匹配(索引最佳)。
1)违反最左匹配原则
最左匹配原则:最左优先,以最左边的为起点任何连续的索引都能匹配上,如不连续,则匹配不上。
如:建立索引为(a,b)的联合索引,那么只查 where b = 2 则不生效。换句话说:如果建立的索引是(a,b,c),也只有(a),(a,b),(a,b,c)三种查询可以生效。
这里跳过了最左的name字段进行查询,发现索引失效了。
遇到范围查询(>、<、between、like)就会停止匹配。
比如:a= 1 and b = 2 and c>3 and d =4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,因为c字段是一个范围查询,它之后的字段会停止匹配。
2)在索引列上做任何操作
如计算、函数、(手动或自动)类型转换等操作,会导致索引失效而进行全表扫描。
explain select * from user where left(name,3) = 'zhangsan' and age =20
这里对name字段进行了left函数操作,导致索引失效。
3)使用不等于(!= 、<>)
explain select * from user where age != 20;
explain select * from user where age <> 20;
4)like中以通配符开头(‘%abc’)
索引失效
explain select * from user where name like ‘%zhangsan’;
解决失效问题
覆盖索引方式
select name from user where name like ‘%zhangsan’;
即select后面不通过 * 方式
索引生效
explain select * from user where name like ‘zhangsan%’;
5)字符串不加单引号索引失效
explain select * from user where name = 2000;
6)or连接索引失效
explain select * from user where name = ‘2000’ or age = 20 or pos =‘cxy’;
7)order by
正常(索引参与了排序),没有违反最左匹配原则。
explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;
违反最左前缀法则,导致额外的文件排序(会降低性能)。
explain select name,age from user where name = 'zhangsan' order by pos;
8)group by
正常(索引参与了排序)。
explain select name,age from user where name = 'zhangsan' group by age;
违反最左前缀法则,导致产生临时表(会降低性能)。
explain select name,age from user where name = 'zhangsan' group by pos,age;
五、执行计划explain各列描述
`type`
all 全表扫描
index 按照索引的全表扫描
range 有范围的索引扫描
ref 该索引的列的值不唯一
ref_eq 该索引的列的值唯一,使用主键或者唯一性索引来查找
`key_len`
表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要。
对于,char、varchar、blob、text等字符集来说,key len的长度还和字符集有关,latin1一个字符占用1个字节,
gbk一个字符占用2个字节,utf8一个字符占用3个字节。null占一个字节。不为null不占用
六、索引实践
1.PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column` )
3.INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
5.多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
七、相关函数
length():mysql里面的length()函数是一个用来获取字符串长度的内置函数。
char_length():在mysql内置函数里面查看字符串长度的还有一个函数是char_length()。
两个函数的区别是:
length(field): 单位是字节,utf8编码下,一个汉字三个字节,一个数字或字母一个字节。gbk编码下,一个汉字两个字节,一个数字或字母一个字节。
char_length(field):单位为字符,不管汉字还是数字或者是字母都算是一个字符。
参考:
https://www.jb51.net/article/229580.htm
https://m.php.cn/article/486583.html
https://blog.csdn.net/h1403635700/article/details/126244945
https://blog.51cto.com/yetaotao/5800641
https://blog.csdn.net/wuxiaowu123/article/details/102831700
http://www.45fan.com/article.php?aid=1CTGA3kvMa430U3y