Mysql索引基础原理
索引的概念
索引是特殊数据结构: 定义在查找时作为查找条件的字段
索引实现在存储引擎
功能:
1.约束数据
2.加速查询
优点:
索引可以降低服务需要扫描的数据量,减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
缺点:
占用额外空间,影响插入速度
索引的查询方式
b tree索引
二叉树结构二分查找(按顺序存储) 在2的n次方的总记录数中,找到某条记录只需要n次查询即可
索引列的行数据最终都会在叶子页按顺序存储,节点页存储的是索引值的索引.非叶子节点只是为了方便算法寻找叶子节点
叶子节点比较特别,他们的指针指向的是被索引的数据,而不是其他的节点页(不同的引擎指针类型不同),其实在根节点与叶子节点之间可能有很多层节点页,树的深度和表的大小直接相关
btree树索引列是顺序组织存储的,所以很适合查找范围数据
创建索引 key(last_name,first_name,dob))
索引对多个值进行排序的依据是create table语句中定义索引时的列顺序
可以使用btree索引的查询类型,btree索引使用用于全键值、键值范围、或者键前缀查找,其中键前缀查找只适合用于根据最左前缀的查找。前面示例中创建的多列索引对如下类型的查询有效:
A:全值匹配
全值匹配指的是和索引中的所有列进行匹配,即可用于查找姓名和出生日期
B:匹配最左前缀
只查找姓,即只使用索引的第一列
C:匹配列前缀
可以只匹配某一列值的开头部分,如:匹配以J开头的姓的人,这里也只是使用了索引的第一列,且是第一列的一部分
D:匹配范围值
allen和barrymore之间的人,这里也只使用了索引的第一列
E:精确匹配某一列并范围匹配另外一列
如查找所有姓为allen,并且名字字母是K开头的,即,第一列last_name精确匹配,第二列first_name范围匹配
F:只访问索引的查询
btree通常可以支持只访问索引的查询,即查询只需要访问索引,而无需访问数据行,即,这个就是覆盖索引的概念。需要访问的数据直接从索引中取得。
索引排序
order by操作,一般来说,如果btree可以按照某种方式查找的值,那么也可以按照这种方式用于排序
order by子句满足前面列出的几种查询类型,则这个索引也可以满足对应的排序需求
btree索引的使用限制
(注意,这里不是指的where条件的顺序,即where条件中,不管条件顺序,只要where中出现的列在多列索引中能够从最左开始连贯起来就能使用到多列索引)
B:不能跳过索引中的列,如:查询条件为姓和出生日期,跳过了名字列,这样,多列索引就只能使用到姓这一列
where last_name=xxx and first_name like ‘xxx%’ and dob=’xxx’;这样,first_name列可 以使用索引,这列之后的dob列无法使用索引。
hash索引
基于哈希表实现,只有精确匹配索引所有列的查询才有效,对于每一行数据,存储引擎都会对所有的索引列的值计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码不一样,哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。
mysql> select * from testhash;
------- -----------
| fname | lname |
------- -----------
| Arjen | Lentz |
| Baron | Schwartz |
| Peter | Zaitsev |
| Vadim | Tkachenko |
------- -----------
4 rows in set (0.00 sec)
假设索引使用假想的哈希函数f(),它返回下面的值:
f('Arjen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458
则哈希索引的数据结构如下:
槽: 值:
2323 指向第1行的指针
2458 指向第4行的指针
7437 指向第2行的指针
8784 指向第3行的指针
哈希索引自身只需要存储对应的哈希值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快
哈希索引限制
memory引擎的数据都保存在内存里),所以大部分情况下这一点对性能的影响并不明显。
B:哈希索引数据并不是按照索引列的值顺序存储的,所以也就无法用于排序
a,b)上建立哈希索引,如果只查询数据列a,则无法使用该索引
=,in(),<=>(注意,<>和<=>是不同的操作),不支持任何范围查询(必须给定具体的where条件值来计算hash值,所以不支持范围查询)
索引的种类
普通索引
加速查询速度
主键索引
加速查找 不能为空 不能重复
唯一索引
加速查找 不能重复
组合索引(多列组成一个索引)
组合索引的效率要高于索引合并
1.联合主键索引
2.联合唯一索引
3.联合普通索引
全文索引
给整个数据库创建索引表
覆盖索引
id是一个主键索引
查询的数据直接从索引文件中就可以获取无需再次到表中读取数据
select id from testdata where id=10000;
索引合并
name 是一个普通索引
id 是一个主键索引
在过滤条件的时候组合使用了多个单列索引
select * from testdata where id=999 and name="aaa"
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhibgbjg
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
微信运动停用后别人还能看到步数吗
PHP中文网 07-22 -
excel打印预览压线压字怎么办
PHP中文网 06-22