数据库索引详解

        哈喽！大家好 ，我是不吃糖，本期博客为大家详细解析数据库索引的相关内容，主页还有MySQL、Oracle、Java的相关内容，之后还会继续更新数据开发相关的内容，感兴趣的朋友可以点个关注进主页了解喔！！！本期内容正式开始！

 在数据库领域，索引如同书籍的目录，能够帮助快速定位数据，显著提升查询效率。MySQL 和 Oracle 作为两款主流的关系型数据库管理系统，都提供了丰富的索引功能，但它们在索引的实现、特性和应用场景上存在诸多差异。深入了解这些区别，对于数据库开发和优化工作至关重要。接下来，我们将对 MySQL 与 Oracle 的索引进行详细解析与全面对比。

索引概念

        MySQL 中的索引是一种数据结构，它是对数据库表中一列或多列的值进行排序的一种结构。通过索引，数据库引擎无需扫描整个表，而是可以快速定位到符合查询条件的数据行，从而大幅提升查询性能。例如，在一个存储大量用户信息的表中，若经常根据用户 ID 进行查询，为用户 ID 列创建索引后，查询速度将得到显著提升。此外，索引还可以在一定程度上保证数据的唯一性（如唯一索引），增强数据的完整性。

索引优缺点

优点：

1. 索引大大减小了服务器需要扫描的数据量，从而加快数据的检索速度，这也是创建索引的最主要原因
2. 索引可以帮助服务器避免排序和创建临时表（group by会创建临时表）
3. 索引可以将随机IO变成顺序IO
4. 索引对于InnoDB非常重要，因为它可以让查询锁更少的元组，提高了表的并发性
5. 关于InnoDB、索引和锁：InnoDB可以在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
6. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
7. 可以加速锁和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
8. 在使用分组和排序字句进行数据检索时，同样可以显著减少查询中分组和排序的时间
9. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

1. 创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加
2. 索引需要占用物理空间，除了数据表占用数据空间外，每个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么占用的空间会更大
3. 对表中数据进行增、删、改时，索引也要动态维护，这降低了整体的维护速度
4. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大实际效果
5. 对于非常小的表，大部分情况下简单的全表扫描更有效

索引使用原则

1. ‘主键’和‘外键’会自动创建索引，无需人工操作
2. 索引需要占‘物理空间’，同数据表的‘表空间’tablespace一样
3. 创建索引和维护索引要耗费时间，这种时间随着数据量的zeng

索引类型

        索引的分类分为逻辑分类与物理分类。

逻辑分类

• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许null值插入

（1）直接创建索引

create index index_name on table_name(col_name);

例：为sc表sid列创建普通索引

create index in1 on sc(sid);

(2)以修改表结构方式添加索引

alter table table_name add index index_name(col_name);

例：为sc表scid列添加索引

alter table sc add index in2(scid);

(3)创建表同时添加索引

create table news(
id int(10),
primary key(id),
index index_name(id)
)

• 复合索引：复合索引是在多个字段上创建的索引。复合索引遵循‘最左前缀原则’，即在查询条件中使用了复合索引的第一个字段，索引才会被使用，因此，在复合索引中索引列的顺序至关重要。

（1）创建一个复合索引

create index index_name on
table_name(col_name1,col_name2,...);

例：为sc表scid和sid添加复合索引

create index in3 on sc (scid,sid);

（2）修改表结构的方式添加索引

alter table table_name add index index_name (col_name1,col_name2,...);

例：为sc表scid和sid添加复合索引

ALTER TABLE 表名 DROP INDEX 索引名; -- 删除索引
alter table sc drop index in3;     -- 删除刚创建的索引in3
alter table sc add index in3(scid,sid); -- 以修改表结构方式创建复合索引in3

• 唯一索引：唯一索引和普通索引类似，主要区别在于，唯一索引限制列的值必须唯一，但允许为null（只允许一条空值），如果是组合索引，则列的组合必须唯一。一张表可以有多个唯一索引

（1）创建唯一索引

        创建单列唯一索引

create unique index index_name on table_name (col_name);

例：为sc表scid建立单个唯一索引

create unique index in4 on sc(scid);

        创建多列唯一索引

create unique index index_name on table_name(col_name1,col_name2,...);

例：为sc表scid和sid建立多列的唯一索引

create unique index in5 on sc(scid,sid);

（2）修改表结构方式创建唯一索引

alter table table_name add unique index index_name (col_name); -- 单列
alter table table_name add unique index index_name (col_name1 , col_name2 ,...);
    -- 多列

例：为sc表scid建立单个唯一索引，为sc表scid和sid建立多个列的唯一索引

alter table sc drop index in4;    -- 删除刚创建的索引in4
alter table sc drop index in5;    -- 删除刚创建的索引in5
 
alter table sc add unique index in4(scid);        -- 修改表结构方式创建索引in4
alter table sc add unique index in5(scid , sid);  -- 修改表结构方式创建索引in5

（3）创建表时直接指定索引

create table 'news'(
id int(10),
primary key(id),
unique index_name_unique(title)
)
 
-- 例：
create table news (
	id int(10),
	primary key(id),
	unique in6(id)
)

• 主键索引：主键索引是一种特殊的唯一索引，一张表只能有一个主键索引，不允许重复，不允许有空值。一般在建表时同时创建主键索引。

（1）创建表时添加主键索引

create table news(
	id int(10),
	primary key (id)
)

（2）修改表结构方式添加主键索引

alter table table_name add primary key(col_name);

• 全文索引：通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引，MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

create fulltext index index_name on tbl_name (col_name);

物理分类

索引物理分类可以分为聚簇索引和非聚簇索引。

聚簇是为了提高某个属性或属性组的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块。

 聚簇索引：不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树，且B+树叶子节点存放的都是表的行记录时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储和索引放到了一块，找到索引也就找到了数据。

一般是主键索引、唯一索引  一张表只能有一个聚簇索引，不是必须有/

非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

聚簇索引优缺点：

优点：

1. 访问速度更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
2. 聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
2. 更新主键代价很高，因为将会导致被更新的行移动。
3. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

B+树索引

B+树结构图 

1.B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。

之所以这么做是因为在数据库中页的大小是固定的，InnoDB中页的默认大小是16KB.

如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数又会再次减少，数据查询的效率也会更快。

另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储000×1000×1000=10 亿个数据。

一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。

2.因为B+树索引的所有数据均存储在叶子节点，那么数据是按照顺序排列的。

那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

有心的读者可能还发现上图B+树各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

其实上面的B树我们也可以对各个节点加上链表。这些不是它们之前的区别，是因为在MySQL的InnoDB存储引擎中，索引就是这样存储的。

也就是说上图中的B+树索引就是InnoDB中B+树索引真正的实现方式，准确来说应该是聚集索引。

通过上图可以看到，在InnoDB中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MySAM中B+树索引实现与InnoDB中略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

最左前缀原则

MySQL建立联合索引时遵守最左前缀原则，即最左优先（查询条件精确匹配索引的左边连续），在MySQL中，查询条件会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配，即复合索引范围查询之后的字段不会用到索引/

• 如果有一个2列的索引（col1，col2），则已经对（col1）、（col1，col2）上建立了索引；
• 如果有一个3列的索引（col1，col2，col3），则已经对（col1）、（col1，col2）、（col1，col2，col3）上建立了索引；

所以当我们查询col3或col2、col3时是用不到索引的，如果查询col1或col1、col2时才会用到索引，其中col1、col2的顺序可以打乱，MySQL的查询优化器会自动优化成索引可以识别的形式。

索引失效情况

        数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也不会奏效。

        1.范围查询

        MySQL会一直向右匹配直到遇到索引搜索键使用>,<,between,like就停止匹配。一旦权重最高的索引搜索键使用范围查询，那么其他搜索键就无法用作索引。即索引最多使用一个范围列，因此如果查询条件中有两个范维列则无法全用到索引。

        2.对索引列进行运算

        如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。即，对索引列函数计算后过滤，索引会失效。

        3.or条件

        or条件除了左右两边同时是主键的时候索引才会生效，其他情况索引均会失效。

        4.数据类型不一样（隐式条件转换）

        如果列是字符串类型，传入条件必须是用引号引起来，不然会导致索引失效。

        例：将其他数据类型隐式转换成字符串导致索引失效。

        5.like模糊查询

        如搜素键值以通配符%开头（如：like'%abc'），则索引失效，直接全表扫描；若只是以%结尾，则不影响索引构建。

本期内容就到此结束啦，下期会更新数据库执行计划的相关内容，感兴趣的友友可以关注一下嗷！