目录
一、索引
经过前面的学习,我们已经在Linux上安装了MySQL,这次我们首先来实现一下用windows上的datagrip来连接Linux上的MySQL,我们首先需要在finalshell上连接Linux主机并登录MySQL的root用户来创建一个用户并给其部分权限!
-- 创建用户 create user 'winroot'@'%' identified by '密码'; -- 给予权限 grant 权限列表 on 数据库.表名 from 'winroot'@'%';
这是我们登录datagrip,远程连接MySQL。我们需要知道linux的IP地址。
点击加号,选择MySQL。
依次输入以上信息,如果连接不成功,可能是Linux防火墙的问题,开启3306端口或者直接关闭防火墙就可以解决问题。连接成功后就可以在windows的datagrip上对Linux的MySQL进行一系列的操作!
1.索引概述
1.1概述
接下来,我们正式进入今天的主题------->索引
索引(index),是帮助MySQL高效获取数据的数据结构。
如果没有索引时,查找数据
一个一个的挨个匹配,=======>全表扫描
如果有索引,这里以二叉树为例,但真正的索引不是二叉树
需要找age=12的数据,
12与13对比,小走左边;
12与10对比,大走右边!========>高效
1.2 优缺点
优点:提高数据检索效率,降低数据库的IO成本;通过索引列对数据进行排序,降低数据排序成本,降低CPU消耗!
缺点:索引列也需要占用空间;索引大大提高了查询效率,同时却页降低更新表的速度,如对表进行 insert, update , delete 时,效率降低。
2.索引结构
索引实在存储引擎层实现的,不同的存储引擎有不同的结构。
B+Tree索引:最常见的索引类型,大部分引擎都支持B+Tree索引。
Hash索引:底层数据结构是用哈希表实现的只有精准匹配索引列查询才有效,不支持范围查询。
R-Tree(空间索引):是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型。
Full-text(全文索引):是一种建立倒排索引,快速匹配文档的方式类似于lucene、solr、es
索引 InnoDB MyISAM Memory B+Tree索引 √ √ √ Hash索引 × × √ R-tree索引 × √ × Full-text索引 5.6版本后支持 √ ×
支持——√ 不支持——× 常说的索引,若无特别指明,都是值B+Tree结构组织的索引。
推荐一个学习数据结构的网址:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
二叉树:
但如果插入数据的顺序不同,就会出现以下情况:
弊端:二叉树在顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢。
红黑树:为解决成为单向链表的问题,构建了红黑树,它是自平衡的
弊端:大数据量情况下,层级较深,检索速度慢。
B-Tree(多路平衡查找树) :多路---->一个节点可包含多个子节点
以一颗最大度数为5(5阶)[一个节点下最多可存的子节点个数]的B-Tree为例(每个节点最多存储4个key,5个指针)
位于中间的元素 152 会向上分裂为根节点。
数据就在每个节点的下面
B+Tree:
所有的元素都会出现在叶子节点
与 B-Tree 区别:所有的数据都会出现在叶子节点;叶子节点形成一个单向链表。
在MySQL中,对B+Tree今昔能力优化,增加了一个只想相邻子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问性能。
Hash(哈希索引): 采用一系列的Hash算法,将简直换算成新的Hash值,映射在对应的槽位上,然后存储在Hash表中。
为name字段创建索引:
1.首先会算出每一行的哈希值。
2.将name字段单拎出来进行hash运算,放入对应的槽位上
若两个(或多个)键值,映射到相同的槽位上,就产生了Hash冲突(也称hash碰撞),可以通过链表来解决。
Hash索引特点:1.只能用于对等比较(=、in),不支持范围查询(between、>、<)
2.无法利用索引完成排序操作
3.查询效率高,通常秩序一次检索即可,效率高于B+Tree
只有MEmory存储引擎支持Hash索引,而InnoDB存储引擎具有自适应hash功能,Hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的
面试题: 为什么InnoDB存储引擎选择使用B+Tree索引结构?
1.对于二叉树,层级更少,搜索效率高
2.对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这就导致一夜中存储的简直减少,指针跟着减少,要保存同样大量数据,只能增加树的高度,导致性能降低
3.相对于Hash索引,B+Tree支持范围匹配及排序操作
3.索引分类
主键索引 | 针对表中主键创建的索引 | 默认自动创建,只能有一个 | Primary |
唯一索引 | 避免同一表中某数据列中的值重复 | 可多个 | Unique |
常规索引 | 快速定位特定数据 | 可多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可多个 | Fulltext |
在InnoDB存储引擎中,根据索引的存储形式,又分为一下两种:
(1)聚集索引(clustered index) 将数据存储与索引放在一起,索引结构的叶子节点保存了行数据。(必须有,且只有一个)
(2)二级索引(secondary index)将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键。(可存在多个)
聚集索引选取规则:
1.如果存在主键,主键索引就是聚集索引。
2.若无主键,将使用第一个唯一索引作为聚集索引
3.若无主键,无唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
#用主键建立索引 ====>聚集索引=====>叶子节点下所挂的数据是该id所在行的所有数据
#用其他列建立索引===>二级索引=====>叶子节点下所挂的是该数据的id主键数据。
简单看一个例子:
select * from user where name = '张三';
这个SQL的运行情况:
-- 思考: InnoDB主键索引的B+Tree高度有多高?
假设:一行数据大小为 1K ,一夜可存储16行这样的数据,InnoDB指针占用6个字节的空间,主键即使是bigint,占用字节数为8
高度为2: 设有n个key,求非叶子节点能存多少个key和指针
n * 8 + (n+1)* 6 = 16 * 1024
=====>得n=1170
可存储的数据量= 1171 * 16 = 18736
高度为3 :1171 * 1171 * 16 = 21939856 (2000多万)
4.索引语法
#创建索引
create [unique| fulltext] index 索引名称 on 表名(索引字段,...); -- ...表示可以有多个字段,单个字段叫单列索引;多个字段叫联合索引
# 查看索引
show index from 表名;
# 删除索引
drop index 索引名称 on 表名;
二、总结
经过以上得学习,我们对索引有了一个大概得认识,接下来我们会针对索引学习性能分析、使用规则等。希望以上内容对大家有所帮助!当然如果有表述不当的地方,也请大家前来批评指正!谢谢大家!!!