数据库-索引

Meteoritex
171 阅读11分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路

索引

腾讯云数据库负责人林晓斌说过：“我们面试 MySQL 同事时只考察两点，索引和锁”。

（一）索引概述

  • MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
  • 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

（二）优缺点

1. 优点

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO（读和写）成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

2. 缺点

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（三）分类

1. 按照底层实现的方式

主要有Hash索引和B+索引。(两种查找方式数据结构均有介绍)

Hash索引 O(1)

B+索引

2. 按照功能划分

主要介绍普通索引、唯一索引和组合索引（多列索引）。

普通索引

（1）特点

普通索引使用没有限制，允许在所有数据类型上创建，可以有重复值和空值。

一个索引只包含单个列，一个表可以有多个单列索引。

（2）创建普通索引

1）建表时创建索引

语法

INDEX 索引名 (列名)

示例

create table student(
    id int primary key,
	name varchar(20),
	age int,
	index name_indx(name)
)

在这里插入图片描述

测试是否创建成功

show index from student

在这里插入图片描述

2）修改表时创建索引

语法

ALTER TABLE 表名 ADD INDEX 索引名(列名)

示例

create table student2(
    id int primary key,
	name varchar(20),
	age int
)
ALTER TABLE student2 ADD INDEX name_indx(name)

测试是否创建成功

show index from student2

在这里插入图片描述

3）直接创建

语法

CREATE INDEX 索引名 ON 表名(列名)

示例

create table student3(
     id int primary key,
	name varchar(20),
	age int
)
CREATE INDEX name_indx ON student3(name)

测试是否创建成功

show index from student3

在这里插入图片描述

唯一索引

（1）特点

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。

（2）创建唯一索引

1）建表时创建索引

语法

UNIQUE INDEX 索引名 (列名)

示例

create table student4(
    id int primary key,
	    name varchar(20),
	age int,
	email varchar(200),
	UNIQUE INDEX email_un_index(email)
)

在这里插入图片描述

测试是否创建成功

show index from student4

在这里插入图片描述

2）修改表时创建索引

语法

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(列名)

示例

create table student5(
    id int primary key,
	    name varchar(20),
	age int,
	email varchar(200)
)
ALTER TABLE student5 ADD UNIQUE INDEX email_un_indx(email)

在这里插入图片描述

3）直接创建

语法

CREATE UNIQUE  INDEX 索引名 ON 表名(列名)

示例

create table student6(
    id int primary key,
	name varchar(20),
	age int,
	email varchar(200)
)
CREATE UNIQUE  INDEX email_un_indx ON student6(email)

在这里插入图片描述

4）添加唯一约束方式

示例

create table student7(
  id int primary key,
	name varchar(20),
	age int,
	email varchar(200) unique
)

在这里插入图片描述

添加了唯一约束的列，数据库会直接添加唯一索引。

组合索引

（1）特点

同时在多个列上添加索引。当查询时，条件是添加了索引的列的组合，可以提高查询效率。

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。

（2）创建组合索引

1）建表时创建索引

语法

INDEX 索引名 (列名1,列名2...,列名n)

示例

create table student8(
    id int primary key,
	name varchar(20),
	age int,
	index name_age_indx(name,age)
)

在这里插入图片描述

2）修改表时创建索引

语法

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名1,列名2...,列名n)

示例

create table student9(
    id int primary key,
	name varchar(20),
	age int
)
ALTER TABLE student9 ADD UNIQUE INDEX name_age_indx(name,age)

在这里插入图片描述

3）直接创建

语法

CREATE  INDEX 索引名 ON 表名(列名1,列名2...,列名n)

示例

create table student10(
    id int primary key,
	name varchar(20),
	age int
);
CREATE INDEX name_age_indx ON student10(name,age)

在这里插入图片描述

（四）创建索引的条件

1. 创建索引

（1）主键自动建立唯一索引。

（2）频繁作为查询条件的字段应该创建索引。

（3）查询中与其它表关联的字段，外键关系建立索引。

（4）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

（5）普通/组合索引的选择问题，在高并发下倾向创建组合索引。

2. 不创建索引

（1）频繁更新的字段不适合创建索引。

​ 因为每次更新不单单是更新了记录还会更新索引，加重了IO负担

（2）Where条件里用不到的字段不创建索引

（3）表记录太少。

（4）经常增删改的表。

（5）如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

（五）索引使用注意事项

1. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。Is null和is not null 无法使用索引。

2. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序在符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4. like语句查询

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5. 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MySQL性能损失。

6. MySQL使用不等于(!= 或者<>)时无法使用索引会导致全表扫描

在MySQL的查询语句的查询条件中如果使用了不等于，那么将索引失效，进行全表扫描方式查询。

7. 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

（六）索引的内部原理

  • 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
  • 这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
  • 换句话说。索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

相关算法介绍

Hash算法

优点：通过字段的值计算hash值，定位数据非常快。

缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小比较。

排序二叉树

特性：分为左子树，右子树和根节点，左子树比根节点值要小，右子树比根节点值要大。

缺点：有可能产生不平衡 类似于链表的结构。

平衡二叉树

特点：

a、它的左子树和右子树都是平衡二叉树

b、左子树比中间小,右子树比中间值大

c、左子树和右子树的深度之差的绝对值不超过1

缺点：

a、插入操作需要旋转

b、支持范围查询，但回旋查询效率较低，比如要查找大于8的，会回旋到父节点7、10

c、如果存放几百条数据的情况下，树高度越高，查询效率会越慢

BTREE树

目前大部分数据库及文件系统都采用B-Tree或其变种B+tree作为索引结构，Btree结构可以有效的解决之前的相关算法所遇到的问题。

B-Tree（具体介绍详解--->学习数据结构 反正搞懂它会感觉到非常的神奇）

B+tree

MyISAM引擎使用B+Tree

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

InnoDB引擎使用B+Tree

InnoDB的叶节点的data域存放的是数据，相比MyISAM效率要高一些，但比较占硬盘内存大小。

谈谈你对b树和b+树的理解：

三个方面回答（答案来源于抖音-->@跟着Mic学架构（java面试系列））

  • 我们B树是一种多路平衡查找树 ，从在二叉树基础上增加一条规则-->二叉排序树（因为会出现斜树的情况）-->平衡二叉树（为了保持平衡引入左旋和右旋机制）-->然后B树是一种多路平衡二叉树，它满足平衡二叉树的所有规则，同时它也可以有多个子树，子树的数量取决于它关键字的数量。子路数量等于关键字数量+1。因此从这个特征来看，在存储同样数据量的情况下平衡二叉树它的高度是一定会大于B树的。而所谓的B+树是在B树的基础上做了增强。

    两者最大的区别：B树的数据存储在每个节点上 而B+树中的每个数据是存储在叶子节点上，并且通过双向链表的方式把叶子节点的所有数据进行连接（属于InnoDB的特征）。B+树的子路数量是等于它关键字的数量。

  • B树和B+树一般是应用在文件系统和数据库系统中，用来去减少磁盘IO所带来的性能损耗的一个机制。以Mysql中Innob为例，当我们去通过select语句去查询一条数据的时候，InnoDB需要去磁盘上去读取数据而这个过程会涉及到磁盘I/O以及磁盘的随机I/O。我们知道，磁盘I/O的性能是特别低的，特别是随机磁盘的I/O，为了去理解为什么性能低呢，我们来看一下磁盘IO的工作原理。首先，系统会把数据的逻辑地址传给磁盘，磁盘控制电路按照寻址的逻辑把逻辑地址翻译成物理地址，也就是确定要读取的数据在哪个磁道哪个扇区，为了读取这个扇区的数据，需要把磁头放在这个扇区的上面，为了实现这个点，磁盘会不断的去旋转。把目标的扇区旋转到磁头的下面，使得磁头能够去找到对应的磁道，这里会涉及到寻道的时间和旋转时间的一个损耗。很明显，磁盘I/O这个过程的性能开销是非常大的特别是查询的数据量比较多的情况下，所以在InnoDB里面干脆对存储在磁盘上的数据建立一个索引，然后把索引数据以及索引列对应的磁盘地址以B+树的方式进行存储。当我们需要查找目标数据的时候，我们根据索引从B+树中查找目标数据就可以了。由于B+树的子路比较多，所以只需要较少次数的磁盘IO就能查找到目标数据。

  • 为什么要用B树或者B+树来做索引结构呢，原因是AVL树的高度要比B树或B+树的高度高，而高度就意味着磁盘IO的数量，所以为了减少磁盘IO的次数，文件或数据库系统才会使用B树或者B+树来做数据结构。

avatar
文章
阅读
粉丝