浅谈索引

最新推荐文章于 2024-08-17 12:13:26 发布

sharkyv

最新推荐文章于 2024-08-17 12:13:26 发布

阅读量1.2k

点赞数 1

分类专栏： Java 文章标签：索引 java

本文链接：https://blog.csdn.net/sharkyv/article/details/113003894

版权

Java 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

什么是索引？
首先，索引是一种存储结构，是一个排序的列表。其次，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。
它的作用可以理解为图书的目录，可以根据目录中的页码快速找到所需的内容。索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。在数据的量十分庞大的时候，使用索引可以有效的加快查询(select)的速度，因为使用索引以后不用扫描全表来定位查找某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引在Mysql中的应用
创建索引

CREATE INDEX indexName ON table_name (column_name)

添加索引

ALTER table tableName ADD INDEX indexName(columnName)

建表时指定索引

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

删除索引

DROP INDEX {INDEXNAME} ON MYTABLE;

关于使用ALTER命令添加和删除索引

添加数据表的索引：

方式1——主键索引：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE table1_name ADD PRIMARY KEY (column_ilst);

方式2——唯一索引：这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

ALTER TABLE table2_name ADD UNIQUE index_name(column_list);

方式3——普通索引：添加普通索引，索引值可出现多次。

ALTER TABLE table3_name ADD INDEX index_name(column_list);

方式4——全文索引：该语句指定了索引为 FULLTEXT ，用于全文索引。

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list):

方式5——组合索引：用多个列名组合构建成的索引，列名的值不能有空值。
此方式建立的组合索引遵循最左前缀原则，最常用的在最左端，且不跳过索引中的列其余依次递减，例如如下的组合索引相当于建立了col_1、col_1col_2、col_1col_2col_3三个索引，与此同时col_2或者col_3是不能使用索引的

ALTER TABLE table_name ADD INDEX index_name('col_1','col_2','col_3');

在 ALTER 命令中使用 DROP 子句来删除索引

ALTER TABLE testalter_tbl ADD INDEX (c);
ALTER TABLE testalter_tbl DROP INDEX c;

显示索引信息

SHOW INDEX FROM table_name; \G

\G可以格式化输出信息

根据索引进行查询

（1）具体查询
SELECT * FROM TABLE_NAME col_1=col_2 （col_1为建表时建的索引，col_2为某值）

（2）模糊查询
SELECT * FROM TABLE_NAME WHERE COL_1 LIKE '%索 '；
SELECT * FROM TABLE_NAME WHERE COL_1 LIKE ‘索%’ ；
SELECT * FROM TABLE_NAME WHERE COL_1 LIKE ‘%索%’ ；
SELECT * FROM TABLE_NAME WHERE COL_1 LIKE ‘%索%’ AND COL_1 LIKE ‘%引%’；
SELECT * FROM TABLE_NAME WHERE COL_1 LIKE ‘[索所锁]引’ //查询COL_1中有索引、所引、锁引的数据

SELECT * FROM table_name WHERE column_1 LIKE ‘[^索所锁]引’; //查询COL_1中除了索引、所引、锁引意外的都可以

索引的优点和缺点

优点：可以加快查找的速度，减少I/O的次数；根据索引来进行分组(group by)和排序(order by)操作，可以加快分组和排序的速度。
缺点：索引本身就是一张表，会占用到内存空间，索引只会加速查找的效率同时会降低删除，添加，修改等操作的效率。

关于索引的数据结构

为什么不用二叉查找树的形式？
在这里插入图片描述
如上图，执行Select * from t where col2=89；
如果无索则从第一行开始查找，需要查找六次。
用二叉查找树形式，key—value，key为索引字段，value是存放数据所在的磁盘地址，从根节点开始查找，二叉排序树的特性是右边的子节点元素要大于父节点元素，左边的节点元素要小于父节点元素。
如果用二叉排序树来作为索引的数据结构，若进行顺序数据递增存储则结构为下图，并不能起到查找优化的效果
在这里插入图片描述

为什么不用红黑树的形式进行存储？
ps：哈希表上一条链表上的元素超过8个的时候，链表将转成红黑树
在这里插入图片描述
红黑树本身就没有满足树的平衡规则，它是一种近似平衡的结构，它有自己的平衡规则，对于红黑树来说，只要从跟到叶子节点经过相同数目的黑色节点那么它就是平衡的。
如果用红黑树作为索引的数据结构，要考虑到树的高度，如果存储100w行数据记录，则树的高度要满足2^n=100w，此时如果查找数据在最下面的叶子节点上的数据，则至少要遍历整个树的高度才可以，简单说也就是这种数据结构会随着数据的增加而增加树高，树高不可控。而好的方式，就是无论数据有多少都能把查找的次数限制在一个可控的范围之内而不是随着数据的增加而让查找数据的时间增长，即想办法控制树的纵向。
解决方法：通过节点的横向扩展继而控制纵向

关于B-Tree
B-Tree根据红黑树改造而来，每一个大节点里面放很多的key-value结构的索引，叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复，节点中的数据索引从左到右递增排列。
在这里插入图片描述
ps：为何不把所有数据都存储在一层树上则树高为1查找什么数据都只用一次查找可以吗？
一个节点过大则对于查找而言没有任何意义上的帮助，参考数组

关于B+Tree
T-Tree的变种——多插平衡树白色地方为指针在mysql中占6bit字节，非叶子节点不存储data，只存储索引(会出现重复的冗余现象)，这样一个节点可以放更多的索引，叶子节点包含所有索引字段，叶子节点用指针连接，可提高区间访问的性能。
在这里插入图片描述
Mysql底层对B+s树每一个节点的大小默认值是有设置的为16kb，如果索引设置的数据类型为bigint大小为8bit
则每个节点可存储的索引数为16kb/14bit=1170

且B+Tree的数据结构中每个小的节点中的数据和其对应的子夜几点都是成二叉树的形式。

关于B+Tree中的横向小指针

如果用Hash结构做索引，hash运算速度极快，查找速度也非常快直接找到数据所在地址那为什么不用hash?首先hash查找数据速度快仅限于单个数据查找很快。那如果执行select * from t where col1>6这种sql语句的时候并不能有效的执行查找。没办法进行范围查找。而指针的存在就是方便了指明数据的查找方向。这是B-tree原本没有的。

关于索引中的聚集索引和非聚集索引
非聚集索引MyISAM、聚集索引InnoDB存储引擎是用来形容表的不是形容数据库级别的，表中的数据是存储在磁盘上面的
首先看MyISAM在磁盘中的存储情况：
在这里插入图片描述
.frm文件是存储着建表时候搭建的表的框架的文件，.myd文件是存储data数据的文件，.myi文件是存储着表中设置的索引的文件，简单来说非聚集索引的存储方式就是索引的文件和数据存放的文件是分离的。
如下图，是用的非聚集索引的方式构建的表，如在执行Select * from t where col1=49语句的时候，首先会读取myi文件根据根节点的索引字段，对比得到49大于15小于56则进行第一次io读取根据指针指向二层的子节点对比数据49大于15大于20大于等于49则进行第二次io到第三层，第三层的子节点中是存有磁盘数据地址的，根据对应的索引字段的磁盘地址再到myd文件中去找到对应地址所存储的数据值。
在这里插入图片描述
InnoDB索引在磁盘中的存储情况：

.frm存储表的框架，ibd文件是存储data数据+索引的合集
Ibd表数据文件本身就是按B+Tree组织的一个索引结构文件，聚集索引的叶节点包含了完整的数据记录。
InnoDB表必须要有主键，并且推荐使用整型的自增主键。
为什么？
首先，如果不使用整型而用UUID形式，UUID形式是一长串随机生成的字符串，首先查找的时候读取字符串的时候要比读取int慢很多，需要先把String类型转换成ACSII码再按照国际排序去对比比较大小。
其次，为何自增，因为B+TREE结构中，任何一个节点中的索引都是从左到右递增的，每个大节点和大节点之间也是从左到右递增的，用的自增的方式是为了防止节点的二次分裂对性能维护有帮助，比如用随机存储索引而不自增，上面说过底层算法给每个节点的数据大小为16kb如果一个数据已经按照从左到右的数据存好了，这时候又有一个索引值的大小正好需要存储在该节点中，则，该节点则需要进行分裂把数据放入，同时还要影响到相关的子节点。
在这里插入图片描述

关于联合索引的底层数据结构

在这里插入图片描述
什么时候用索引？
设立自增主键标识字段唯一的时候
有排序的列用索引
高并发情况下要用索引
经常作为查找条件的列要用索引，如常在where、order by xx desc/asc这种语句中的列要用索引
查找其他表关联字段标识外键的时候要用索引
常用聚合函数查找的列要用索引，比如count(col)或者max(col)、sum(col)
什么时候不用索引？
持久性不强的列没必要，如常增删改的列
重复性大的列没必要
数据量不太的列没必要建立索引
什么时候用聚集索引？什么时候用非聚集索引？
原文：http://www.51testing.com/html/87/300987-818673.html

动作描述	使用聚集索引	使用非聚集索引
外键列	√	√
主键列	√	√
列经常被分组排序	√	√
返回某范围内的数据	√	×
小数目的不同值	√	×
大数目的不同值	×	√
频繁更新的列	×	√
频繁修改索引列	×	√
一个或极少不同值	×	×