一篇文章让你学会数据库的索引

最新推荐文章于 2024-09-25 11:06:23 发布

攻爱村

最新推荐文章于 2024-09-25 11:06:23 发布

阅读量176

点赞数

文章标签：数据库索引 mysql 数据结构

本文链接：https://blog.csdn.net/weixin_44121885/article/details/117032899

版权

索引

作用: 基于目前大部分数据库场景查询比写入多得多,进而进行优化查询

成因:由于数据库是将数据存入磁盘,而读写磁盘的数据对计算机来说需要巨大的代价(需要9ms左右一次),和主存的速度相比差了很多,所以需要一次性有目的性的将磁盘中的数据放入主存中,而简单的搜索树无法满足这个条件,因此使用索引,将每次查找数据的时候的磁盘io次数限制在很小的数量级.

B树(或者B-树)

特点

这是一颗多路查找平衡树

关键字分布在整棵树的所有节点

任何一个关键字只出现在一个节点中

搜索可能在非叶子节点中结束

搜索性能等价于在关键字全集内做一次二分查找

描述一个B树要指定一个树的阶数每一个节点最多有阶数-1

插入

前提先决：一个5阶树（则每个节点最多容纳4个节点）

1）根据key找到叶子节点插入

2）判断当前节点的key个数是否小于m-1,满足则插入，不满足则进入步骤3

3）将这个节点二分，取中间的上升到上一个节点，两边的分成两个子节点

删除

删除分几种情况

1）删除叶子节点，假设该节点的删除后节点数大于2/m个，则直接删除

2）删除叶子节点，假设该节点的删除后的节点数小于2/m个，则观察兄弟节点

2-1）当兄弟节点的节点数大于2/m ，将父节点的元素移动到该节点，将兄弟节点的元素移动到兄弟节点

2-2）当兄弟节点的节点数小于2/m，将父节点的元素移到该节点，将该节点和兄弟节点合并。

3）删除非叶子节点，将后继子节点的元素复制到删除元素的位置，然后将子节点中的该元素删除，当删除之后，需要考虑子节点的节点数是否大于2/m 如果不大于，则执行2）

B+树

特点:非叶子节点的子树指针和关键字个数相同

非叶子节点指针的子树指针p[i],指向关键字属于[k[i],k[i+1]]的子树

为所有的叶子节点增加一个指针(方便顺序查找?)

所有关键字都在叶子节点出现

搜索只在叶子节点命中

非叶子节点是叶子节点的索引层,叶子节点才是数据层’

根节点的最大元素就是整个树的最大元素

在非聚集索引中，叶子节点只存放数据位置的地址（或者说数据的指针），聚集索引存放数据本身的内容

所有的中间节点元素都存在于子节点，在子节点元素中是最大（小）的

相比B树的优点

B+树的磁盘读写代价更低,因为非叶子节点全是索引,如果读取一页数据,那么能读到的索引就更多,就能更快找到想要的数据(概率上)

查询效率更稳定(都存在叶子节点)

通过遍历叶子节点就可以实现全数据遍历

在范围查询上，因为b+树有指针，所以更方便

插入

当节点数量大于m-1,按照中间元素分裂成两部分，中间元素作为父节点，需要注意的是，中间元素经过分裂之后仍然要存储到右子节点中

删除

因为有叶子有指针，所以删除不需要经过父节点

1）删除索引树的最大值或者最小值

查找删除后，向上回溯所有的最大值或最小值将其替换成现有的最大最小值

·2）删除该元素，该元素所在节点元素数量大于>m/2，直接删除

3）删除该元素，该元素所在节点元素数量小于m/2，考虑兄弟节点

3-1）在3的条件下，兄弟节点的元素数量大于2/M 将兄弟节点的元素借用，接着改变父节点的元素，因为兄弟节点的最大值改变了

3-2）在3的条件下，兄弟节点元素小于2/m 将兄弟节点和本节点合并

哈希索引

缺点：没办法利用索引完成排序

不支持最左匹配原则

大量键值如果重复，哈希索引效率会降低

不支持大范围查询

索引失效

多个单列索引在多条件查询时优化器会选择最优索引策略，可能 只用一个索引，也可能将多个索引都用上。

但是多个单列索引底层会建立多个 B+索引树，比较占用空间，也会浪费搜索效率所以 多条件联合查询时最好建联合索引。

原因:

1 违反最左匹配原则

联合索引必须是以最左边为起点,连续的索引,假如(abc)建立联合索引,则a ab abc都是有效的联合索引

另外,遇到(><beteen 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

3使用不等于(!=,<>)

explain select * from user where age != 20;

4 like中以通配符开头(’%xxxx’),（注意只有开头会这样）

explain select * from user where name like ‘%zhangsan’;

5字符串不加单引号

6 使用or链接

7 order by（by之后违反最左前缀原则||含非索引字段分组）

8 group by （by后违反最左前缀原则||含非索引字段分组）

9使用的字符集不一致 (如果存储的信息有存储emoji的需要需要将格式改成utf8mb

为何索引大多使用B+树

**为什么不用哈希？**能保证数据按照键的顺序进行存储，可以按照某种顺序进行遍历，对于排序和范围查询等操作，b和b+会比哈希带来更好的性能哈希作为底层数据结构能用o（1）的速度处理单行crud，但是无法完成范围查询和排序（只能使用全表扫描）

为什么不用B？

计算机读写文件是以页为单位将数据从磁盘读到内存，而B+不会在非叶子节点存储数据（只存储索引），能减少顺序遍历时产生的额外随机io

聚集索引和非聚集索引

聚集索引

一种确定表中数据物理存储结构的索引，叶子节点是数据节点，因此聚集索引只能有一个，但是聚集索引也可以是组合索引。

非聚集索引以及回表操作

叶子节点存储的是一个包含一个指向对应数据块的指针，非聚集索引的叶子节点里存放的是聚集索引的key （而不是主键，但是大多数聚集索引都是以主键）

因为非聚集索引中存放的是聚集索引的key 因此查询到key之后还得从数据库里以聚集索引的key进行复查，这个操作就是回表

覆盖索引

由于非聚集索引会出现回表操作导致性能下降，如若建立了联合索引，且搜索的内容和搜索的条件都在索引之内，就能在非聚集索引之内完成数据的查询操作，完成覆盖索引，避免回表操作

什么情况下会用到索引、什么情况下不会用？

表的主键、外键必须有索引

数据量超过300应该有索引

经常和其他表进行连接的表在连接字段上应该有索引

经常出现在where子句的字段中，特别是大表的字段，应该建立索引

不要建立在大的文本字段上

什么情况下要简历复合索引

1 需要复合的索引是否经常以and的形式出现在where中

2 如果复合的索引字段超过3个要考虑是否合理必要

3 有单字段索引和复合索引的条件下要考虑删除复合索引

4 频繁进行数据操作的表不要建立太多的索引

存储过程

介绍：类似于java里的功能封装，存储过程将一组特定的sql语句集存储在该数据库中，并且经过第一次调用后不需要再次编译，用户通过制定存储过程的名字病给出参数来执行

特点：

可以完成较复杂的判断和运算

可编程性强，灵活

sql编程的代码可重复使用

执行速度相对快

减少网络之间的数据传输（因为存储过程已经存到数据库里并且不需要多次编译）

创建方法

create procedure 名称()
begin 
.....
end

create procedure testa()
begin
    select * from users;
    select * from orders;
end;

call testa();

引擎对比

	InnoDB	MyISAM
是否支持事务	是	否
是否支持行级锁（用于并发()	是	否（只支持表级锁
是否支持外键	是	否
是否支持异常崩溃的安全恢复	是（redo log）	否
是否支持mvvc（行级锁的一个升级，可以有效减少加锁操作，提供性能。	是	否

数据库数据类型

数值类型

类型	字节/范围
tinyint	1字节 -128~127 无符号位 -256-255
smallint	2字节 -2^16~216
mediumint	3字节 -2^32-232
int	4
bigint	8
int(M)	m表示总位数
mysql没有bool

类型	字节/范围
float	4字节
double	8字节
定义浮点型时,需指定总位数和小数位数 float(M,D) ,M表示总位数 D表示小数位数

char	定长,速度快,浪费空间(最多255字符与编码无关)
varchar	变长,速度慢,节省空间(最多65535字符,与编码有关)
	varchar的最大有效长度由使用的字符集以及最大使用行决定 utf8最大21844个字符 gbk 最大为32766个 latin1 最大为65532个

blob	二进制字符串
text	非二进制字符串(不能默认值,也不需要定义长度)

类型	字节数/代表的东西	范围
datetime	8/日期和时间	1000-01-01 00:00:00 到 9999-12-31 23:59:59
date	3/日期
timestamp	4/时间戳	19700101000000 到 2038-01-19 03:14:07
year	1/年份
time	3/时间	-838:59:59 到 838:59:59