数据库索引学习笔记（7月11号）

最新推荐文章于 2024-07-25 16:06:56 发布

Yuchao Huang

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量301

点赞数

分类专栏： QG训练营日志文章标签： mysql 数据库索引后端学习笔记

本文链接：https://blog.csdn.net/weixin_43001913/article/details/95527428

版权

QG训练营日志专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

作者信息
前言
一.什么是数据库索引？
二.数据库索引分类
三.数据库索引的语法
四.高性能索引策略
五.总结
六.参考资料

作者信息

作者：黄钰朝
邮箱：kobe524348@gmail.com
日期：2019年7月11日

前言

今天主要学的是数据库索引的知识。

学习了索引的分类，创建索引，删除索引，查询索引等数据库语法，了解数据结构中的B树，B+树等知识，了解索引额工作原理，索引的优缺点，学习B-tree索引和哈希索引，聚簇索引，二级索引，联合索引，覆盖索引，唯一索引等概念，并了解使用索引提高查询性能的方法。

一.什么是数据库索引？

1.1 什么是索引?

数据库索引就是一个数据结构，主要是为了避免查询的时候进行逐行读取，提高查询速度。

1.2 索引的优点

提高查询速度
建立唯一索引，数据库会拒绝插入重复的数据，防止重复
表连接时提高速度

1.3 索引的缺点

降低插入，删除的速度
降低更新索引列的速度
索引占用物理空间
对于具有大量重复数据的列，索引起不到作用，比如性别这一列基本男女各占一半，对这一列的索引不起作用。

1.4 索引之外的解决方案

对于数据量非常庞大的系统，可以采取生成元数据信息的方式提高查询速度，比如使用了很多张表来记录用户数据的系统，如果使用一个“哪个用户的信息保存在哪张表上”这样的元数据信息，可以避免大量的查询操作。

二.数据库索引分类

2.1 B-Tree索引

先上图：

在这里插入图片描述

B树索引要求对数据进行排序，然后通过逐层比较快速找到数据。

其中索引数据在磁盘中是分块储存的，每个结点都是一个“块”，每访问一个结点，就需要访问一次磁盘，每个结点上都要进行比较，决定在下一层结点中的走向。比如图中根结点的P1，P2，P3分别指向了比17小，在17-35之间，和比35大的结点。逐层比较下去，直到获取到对应的数据，如果不存在则返回NULL。

需要注意的是，这里只有最下面的一行叶子结点的值是真实存在于数据库中的，上面分支结点和根结点中的值只是用于辅助查找的。

2.2 哈希索引

哈希索引对索引列的所有行计算出一个哈希码，并记录行指针和哈希码的对应关系。查询数据时，只需要使用哈希函数计算哈希码，再根据哈希索引找到对应的行指针即可。

但是哈希索引也有限制：

哈希索引只存储行指针，无法避免行读取，但是此时的行读取是把整行加载到内存中，开销也不算大
哈希索引无法用于排序，因为并不是按索引值顺序存储的。
哈希索引不支持部分索引列的查找，比如建立在（A,B)列上的索引，只根据A列的数据无法进行查找
哈希索引只支持等值比较的查找
哈希冲突过多会导致性能下降

2.3 聚簇索引和非聚簇索引

聚簇索引和上面的B-Tree索引和哈希索引不同，不是一种单独的索引类型，而是从数据存储的角度对索引进行分类，聚簇索引和非聚簇索引的区别就在于索引是否和数据保存在一起，对于聚簇索引，索引的叶子结点上保存了数据，而对于非聚簇索引，其索引文件和数据文件是分开的，索引的叶子结点上只保存了对应数据的指针。

正由于聚簇索引是关系到数据在物理上的存储的，因此聚簇索引只能有一个。而非聚簇索引可以有多个。

InnoDB引擎就是使用聚簇索引的。

2.4 二级索引，辅助索引

很简单，除了聚簇索引外的所有索引，称为二级索引、辅助索引

2.5 联合索引

通常的索引是对一个字段创建的，而对多个字段做的一个索引，就是联合索引（联合索引不等于对多个字段分别做单独的索引）。查询的时候，是先按一个字段查询，再到下一个字段，而且这个顺序是不变的，所以要用到下面的最左前缀原理。

2.5.1 联合索引的最左前缀原理

PS:这里又是直接摘了别人写的博客。我感觉很清楚了，不用再写自己的理解了。

最左前缀原理
我们再来详细介绍一下联合索引的查询。还是上面例子，我们在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：

以下的查询方式都可以用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；

上面三个查询按照（a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。

如果查询语句是：

select * from table where a=1 and c=3；那么只会用到索引a。

如果查询语句是：

select * from table where b=2 and c=3；因为没有用到最左前缀a，所以这个查询是用不到索引的。

如果用到了最左前缀，但是顺序颠倒会用到索引吗？

比如：

select * from table where b=2 and a=1；
select * from table where b=2 and a=1 and c=3；

如果用到了最左前缀而只是颠倒了顺序，也是可以用到索引的，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询，这样查询优化器就不用重新编译了。

2.6 覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引
覆盖索引不是一种单独的索引类型，而是mysql判断查询条件中的字段可以被某个索引覆盖时选择的一种索引方式。

三.数据库索引的语法

PS: 以下关于索引的指令的内容直接摘自菜鸟教程

3.1 创建普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length)); 
如果是char,varchar类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

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

3.2 创建唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

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

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

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

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

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

3.3 查看索引

使用show index 指令即可

SHOW INDEX FROM table_name

3.4 删除索引

使用drop index 指令即可

DROP INDEX [indexName] ON mytable;

四.高性能索引策略

4.1 查询语句使用独立的列

使用where子句查询时，应当使用独立的列，也就是索引列不要参与“计算”，否则MySQL不会使用索引.

4.2 使用前缀索引

选择合适长度的前缀作为索引，可以避免索引列的值过长带来的存储空间占用，又能提高查询效率。

4.3 使用多列索引

多列索引和对多列分别建立索引的性能不一样，多列索引的性能要好

4.4 合理安排索引列顺序

通常来说，选择性高的索引列放在前面

4.5 使用聚簇索引

聚簇索引获取数据更快，因为数据和索引放在一起

4.6 使用自增的主键插入行

UUID主键的插入效率和查询效率都低，因为UUID很长，而且是无序的，因此使用自增主键可以提高性能。

4.7 使用覆盖索引

使用覆盖索引无需回表
索引条目比数据行条目少，减少数据访问

五.总结

今天学的是数据库索引，早上在看索引的原理，总体来说，觉得数据库的原理还是知道的太少，今天的博客感觉写不出很多自己的感悟，对于数据库的原理没有建立起一个知识体系。很多概念，似乎懂了，似乎又不是那么的有把握，后面学索引的分类，聚簇索引，总感觉有点不安，感觉知识体系存在漏洞，学到B树索引的时候，看了很多博客都觉得不是很清楚，很多细节没有提到，直到晚上的时候，仔细看了一些有深度，详细的博客，才觉得差不多弄明白其中的原理。看来还是要把数据结构学得再深入一点，再找时间详细地看一些细节上的东西，把这些数据库的原理研究清楚。

小结今日收获：

索引用于提高查询速度，但是降低插入，删除，修改的性能
索引相关的语法：CREATE INDEX，SHOW INDEX，DROP INDEX
B-Tree索引使用排序的数据配合B+树实现
哈希索引利用行指针和哈希码的对应关系实现快速查询
唯一索引可用于防止插入重复数据
聚簇索引的索引文件直接保存数据而不是数据指针，只能有一个
B+树的数据只保存在叶结点，分支结点和根结点上的数据实际不存在于数据库中，只是用于辅助查找，因此每次查询经过路程一样
innoDB中使用的是聚簇索引，并且有自适应哈希索引，默认开启，可以关闭
索引不是一定生效，某些查询可能无法用到索引，应使用最左前缀原理

六.参考资料

Yuchao Huang

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库索引学习笔记（7月11号）

文章目录作者信息前言一.什么是数据库索引？1.1 什么是索引?1.2 索引的优点1.3 索引的缺点1.4 索引之外的解决方案二.数据库索引分类2.1 B-Tree索引2.2 哈希索引2.3 聚簇索引和非聚簇索引2.4 二级索引，辅助索引2.5 联合索引2.5.1 联合索引的最左前缀原理2.6 覆盖索引三.数据库索引的语法3.1 创建普通索引创建索引修改表结构(添加索引)创建表的时候直接指定3.2 ...
复制链接

扫一扫

专栏目录