【无标题】index

Nuria_Niu

已于 2023-06-16 17:06:00 修改

阅读量48

点赞数

文章标签： sql 数据库

于 2023-06-06 16:10:26 首次发布

什么是索引

索引是一种数据结构，它可以加快数据的检索速度。通过在数据库表的一列或多列上创建索引，可以对这些列进行排序，并且可以快速地进行查询、排序和聚合操作。索引的类型有哪些？索引的类型可以分为主键索引、唯一索引、普通索引、全文索引等。主键索引：主键索引是一种特殊的唯一索引，用于标识表中每一行数据的唯一性。唯一索引：唯一索引用于保证表中某一列的唯一性，同一表中可以有多个唯一索引。普通索引：普通索引用于加快表中某一列的检索速度，同一表中可以有多个普通索引。

全文索引：全文索引用于对文本类数据进行全文检索，可以加快文本类数据的检索速度。

什么情况下需要创建索引？
需要创建索引的情况包括：

经常用于查询的列需要创建索引，可以加快查询速度。

经常用于排序的列需要创建索引，可以加快排序速度。

经常用于关联的列需要创建索引，可以加快关联速度。

需要注意的是，过多的索引会增加数据库的维护成本，并且会占用额外的存储空间。因此，在创建索引时需要根据实际情况进行判断，避免创建过多的索引。

索引的优缺点

索引的优点：

提高检索效率，可以快速定位到数据。

加快排序和分组操作的速度。

保证数据的唯一性和完整性。

索引的缺点：

１）创建索占空间，添加索引的时候，数据库引擎需要合成索引，在合成的过程中，需要有大量的数据指向过的调整。
２）.索引的生成一定会影响增加删除的性能，尤其是增加数据和删除数据之后，重新维护新的数据库的索引结构。

如何优化索引的性能？

可以通过以下方式优化索引的性能：

创建合适的索引，避免创建过多的索引，因为过多的索引会降低查询性能。

使用覆盖索引，可以避免查询结果集过大的问题。

避免在索引列上使用函数和表达式，因为这会导致索引失效。

避免使用模糊查询，可以使用全文索引来提高查询性能。

定期维护索引，包括重新构建索引、收缩索引、压缩索引等。

index 的数据结构

索引（Index）是对数据库一列或者多列的值进行排序的一种数据结构，使用索引可以快速访问数据库表中的特定信息，提高查询的效率。
常用的索引数据结构包括：

B-Tree（平衡树）索引：B-Tree是一种常用的索引数据结构，它可以对数据进行快速的查找、插入和删除操作。B-Tree索引中的每一个节点都包含多个键值对，每个键值对对应一个数据记录的主键值和指向该数据记录的指针。

B+Tree（平衡树）索引：B+Tree是一种改进的B-Tree索引，它采用了只有叶子节点存储数据记录的方式，可以减少磁盘I/O操作，提高数据检索效率。

Hash索引：Hash索引使用哈希表来存储数据记录的主键值和指针，可以快速地进行数据查找操作。Hash索引适用于等值查询，但不适用于范围查询和排序操作。

全文索引：全文索引使用倒排索引（Inverted Index）来存储文本数据，可以对文本数据进行全文检索。倒排索引的每一个条目包含一个单词和指向包含该单词的文档的指针。

其中，B-Tree和B+Tree索引是最常用的索引数据结构，它们可以适用于各种类型的数据查询操作，并且具有较好的平衡性和稳定性。Hash索引适用于等值查询，但在范围查询和排序操作上表现不佳。全文索引适用于文本数据的全文检索，但对于其他类型的数据则不适用。

Mysql

索引的分类：

主键索引
唯一索引
普通索引
全文索引 text varchar里应用
组合索引

技术名词：
回表：普通索引，先去普通B+树查找返回主键id ,再去主键B+树查找，返回属性，这个过程就叫回表。回表原来主键的表。
覆盖索引：
select * from table where name=“zhangsan”;
select id from table where name=“zhangsan”;
name 索引正常查找是查name B+ 树，再去查主键B+树回表
但是select id 指明id ,只需要查name B+树，就能找到id ,不需要再去主键B+树，这个过程就叫覆盖索引。
最左匹配：组合索引中，给多个字段建索引，先要匹配最左边的字段。
(name,age)创建组合索引。最左匹配原则，必须先匹配最左边一个name.
select * from table where name=“zhangsan”; // 可以用到索引
select * from table where age=11; // 否
select * from table where name=“zhangsan” and age=11; //可以
select * from table where age=11 and name=“zhangsan”; //可以。mysql 会有执行顺序的优化。
想要把四个都覆盖所以，还可以建name 索引，或者age,name 组合，age 索引， age和name类型长度选择较小的更好。
索引下推 5.7版本之后才有：
在组合索引中 name,age
正常1.先根据name 从存储引擎中把符合规则的数据拉取出到mysql的service层
2.再在server 层按照age 进行数据过滤
索引下推： (把第2个操作进行下推，合并操作)
直接从存储引擎拉取数据的时候直接按照name 和age 做判断，将符合的结果返回给mysqlserver.
数据库是三层结构，Control ，server , 存储引擎。索引下推就是把过滤的操作都推到底层存储引擎中完成。

谓词下推：
select t1.name,t2.name, form t1 join t2 on t1.id=t2.id;
执行顺序：选择2，把过滤的操作推到下层执行来减少过滤量提高效率。
1.把所有的字段先做表关联，然后再从关联好的表中选择需要的4个字段
2.先把两张表需要的四个字段取出，然后再做表关联。

索引匹配方式：

全职匹配
匹配最左前缀匹配左边前面的几列
匹配列前缀 like 左边% 不能触发索引，右边%可以
匹配范围值 B+树有序
精确匹配列和范围匹配列
只访问索引的查询

show profile for query 1; 展示上次查询的每步骤性能时间

B+树

是为了减少于磁盘交互的次数，特点，层高低，只有叶子节点存数据。遍历少
https://www.bilibili.com/video/BV1Ch4y1G7HW/?p=3&spm_id_from=pageDriver
比如一个user 表（id,name, phone)
记录保存在inno db，innodb 存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的最小单位，默认为16kb, 数据页大小16KB.
在一个数据页中，用户记录是按照主键由小到大的顺序排列的。数据页之内用单向连接表结构，页内数据可以快速查找，数据页之间是双向链表解耦。
目录页，索引是把对页面的最小值和最大值做一个记录按片分，创建多片索引结构；目录项记录每页开始记录和页码。如果目录项多了，就再为目录项创建目录项 ··
innodb 会为每个表自动创建一个主键索引，不需要显示的书写，创建索引的语句。主键索引（也叫聚集索引）。

主键索引

两个特点：
按照主键的大小对用户记录和数据页进行排序，记录用单项链表接连，数据页使用双向链表连接。
B+树的叶子节点保存了用户的完整的记录。（索引即数据，数据及索引）
在这里插入图片描述
没有主键，优先选取unique 键作为主键，没有的话，就自动为每一条用户记录添加一个DB_ROW_ID的列作为默认主键
innodb 主键索引一定会存在。

普通索引

也叫二级索引
name 索引通过name 排序记录B+树，但是主键比较快，不需要完全建不同的可以通过name 找对应id ,再从主键索引查找，即下图创建name 索引包含Id。目录项里包含name ,id , 页码。但是name 不唯一，可能存在多个id ,需要多次回表操作。

联合索引

叶子节点包含name ,phone, id , 最左匹配原则， name 排序，再phone 排序。
目录项的节点包含每页第一个记录name,phone,id ,页码。利用B+数快速定位到数据页。页内快速定位到记录。找到id,在主键索引B+数里再进行回表操作，如果多条，就多次回表操作。

SqlServer

1.分类：

聚集索引（查字典-拼音查字法）拼音在字典里的汉字有序，位置相近
非聚集索引（查字典-类似部首查字法）部首在字典汉字所在页没有顺序的，不在一起
主XML索引
辅助XML索引
空间索引
表存储分页
数据页：8KB （8192字节，减去96字节-页数的描述）的头大小的数据。页之间双向链表。

如果一行某列数据量大，nvarchar 可以超过8KB。
数据存储－LOB类型，存储大对象数据类型，每个可以有２DB可以跨多页，页数不一定连续。
数据存储－堆结构，没有聚集索引的表，无序的，用”索引分配映射IAM“页将堆的页面联系在一起。