索引是什么?

索引图解

定义:数据库索引,是数据库管理系统(DBMS)中一个排序的数据结构,以协助快速查询、 更新数据库表中数据。

在这里插入图片描述
首先数据是以文件的形式存放在磁盘上面的,每一行数据都有它的磁盘地址。如果 没有索引的话,要从 500 万行数据里面检索一条数据,只能依次遍历这张表的全部数据, 直到找到这条数据。 但是有了索引之后,只需要在索引里面去检索这条数据就行了,因为它是一种特殊 的专门用来快速检索的数据结构,我们找到数据存放的磁盘地址以后,就可以拿到数据 了。就像我们从一本 500 页的书里面去找特定的一小节的内容,肯定不可能从第一页开 始翻。那么这本书有专门的目录,它可能只有几页的内容,它是按页码来组织的,可以 根据拼音或者偏旁部首来查找,只要确定内容对应的页码,就能很快地找到我们想要的 内容

索引类型

在 InnoDB 里面,索引类型有三种,普通索引、唯一索引(主键索引是特殊的唯一 索引)、全文索引。

  • 普通(Normal):也叫非唯一索引,是最普通的索引,没有任何的限制。
  • 唯一(Unique):唯一索引要求键值不能重复。另外需要注意的是,主键索引是一 种特殊的唯一索引,它还多了一个限制条件,要求键值不能为空。主键索引用 primay key 创建。
  • 全文(Fulltext):针对比较大的数据,比如我们存放的是消息内容,有几 KB 的数 据的这种情况,如果要解决 like 查询效率低的问题,可以创建全文索引。只有文本类型 的字段才可以创建全文索引,比如 char、varchar、text。

InnoDB 逻辑存储结构

https://dev.mysql.com/doc/refman/5.7/en/innodb-disk-management.html https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html
MySQL 的存储结构分为 5 级:表空间、段、簇、页、行。

在这里插入图片描述
表空间 Table Space 上表空间可以看做是 InnoDB 存储引擎逻辑结构的 最高层,所有的数据都存放在表空间中。分为:系统表空间、独占表空间、通用表空间、 临时表空间、Undo 表空间。

段 Segment 表空间是由各个段组成的,常见的段有数据段、索引段、回滚段等,段是一个逻辑 的概念。一个 ibd 文件(独立表空间文件)里面会由很多个段组成。 创建一个索引会创建两个段,一个是索引段:leaf node segment,一个是数据段: non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。 也就是说,一个表的段数,就是索引的个数乘以 2。

簇 Extent 一个段(Segment)又由很多的簇(也可以叫区)组成,每个区的大小是 1MB(64 个连续的页)。 每一个段至少会有一个簇,一个段所管理的空间大小是无限的,可以一直扩展下去, 但是扩展的最小单位就是簇。

页 Page 为了高效管理物理空间,对簇进一步细分,就得到了页。簇是由连续的页(Page) 组成的空间,一个簇中有 64 个连续的页。 (1MB/16KB=64)。这些页面在物理上和 逻辑上都是连续的。 跟大多数数据库一样,InnoDB 也有页的概念(也可以称为块),每个页默认 16KB。 页是 InnoDB 存储引擎磁盘管理的最小单位,通过 innodb_page_size 设置。 一个表空间最多拥有 2^32 个页,默认情况下一个页的大小为 16KB,也就是说一个 表空间最多存储 64TB 的数据。 注意,文件系统中,也有页的概念。 操作系统和内存打交道,最小的单位是页 Page。文件系统的内存页通常是 4K。
在这里插入图片描述

SHOW VARIABLES LIKE 'innodb_page_size';

在这里插入图片描述
往表中插入数据时,如果一个页面已经写完,产生一个新的叶页面。如果一个簇的 所有的页面都被用完,会从当前页面所在段新分配一个簇。 如果数据不是连续的,往已经写满的页中插入数据,会导致叶页面分裂:
在这里插入图片描述
行 Row
InnoDB 存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存 放
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

索引方式:真的是用的 B+Tree 吗?

在 Navicat 的工具中,创建索引,索引方式有两种
Hash 和 B Tree。
HASH:以 KV 的形式检索数据,也就是说,它会根据索引字段生成哈希码和指针, 指针指向数据
在这里插入图片描述
第一个,它的时间复杂度是 O(1),查询速度比较快。因为哈希索引里面的数据不是 按顺序存储的,所以不能用于排序。 第二个,我们在查询数据的时候要根据键值计算哈希码,所以它只能支持等值查询 (= IN),不支持范围查询(> < >= <= between and)。 另外一个就是如果字段重复值很多的时候,会出现大量的哈希冲突(采用拉链法解 决),效率会降低。
:InnoDB 内部使用哈希索引来实现自适应哈希索引特性。 这句话的意思是 InnoDB 只支持显式创建 B+Tree 索引,对于一些热点数据页, InnoDB 会自动建立自适应 Hash 索引,也就是在 B+Tree 索引基础上建立 Hash 索引, 这个过程对于客户端是不可控制的,隐式的。 我们在 Navicat 工具里面选择索引方法是哈希,但是它创建的还是 B+Tree 索引,这 个不是我们可以手动控制的。 buffer pool 里面有一块区域是 Adaptive Hash Index 自适应哈希 索引,就是这个。 这个开关默认是 ON:

show variables like 'innodb_adaptive_hash_index';

从存储引擎的运行信息中可以看到:

show engine innodb status\G

MySQL 架构

这里我们主要关注一下最常用的两个存储引擎,MyISAM 和 InnoDB 的索引的实现

MySQL 数据存储文件

show VARIABLES LIKE 'datadir';

在这里插入图片描述
在这里我们能看到,每张 InnoDB 的表有两个文件(.frm 和.ibd),MyISAM 的表 有三个文件(.frm、.MYD、.MYI)。
有一个是相同的文件,.frm。 .frm 是 MySQL 里面表结构定义的文件,不管你建表 的时候选用任何一个存储引擎都会生成。 我们主要看一下其他两个文件是怎么实现 MySQL 不同的存储引擎的索引的。

MyISAM

在 MyISAM 里面,另外有两个文件: 一个是.MYD 文件,D 代表 Data,是 MyISAM 的数据文件,存放数据记录,比如我 们的 user_myisam 表的所有的表数据。 一个是.MYI 文件,I 代表 Index,是 MyISAM 的索引文件,存放索引,比如我们在 id 字段上面创建了一个主键索引,那么主键索引就是在这个索引文件里面。 也就是说,在 MyISAM 里面,索引和数据是两个独立的文件。 那我们怎么根据索引找到数据呢? MyISAM 的 B+Tree 里面,叶子节点存储的是数据文件对应的磁盘地址。所以从索 引文件.MYI 中找到键值后,会到数据文件.MYD 中获取相应的数据记录。
在这里插入图片描述

这里是主键索引,如果是辅助索引,有什么不一样呢? 在 MyISAM 里面,辅助索引也在这个.MYI 文件里面。 辅助索引跟主键索引存储和检索数据的方式是没有任何区别的,一样是在索引文件 里面找到磁盘地址,然后到数据文件里面获取数据。
在这里插入图片描述

InnoDB

InnoDB 只有一个文件(.ibd 文件),那索引放在哪里呢? 在 InnoDB 里面,它是以主键为索引来组织数据的存储的,所以索引文件和数据文 件是同一个文件,都在.ibd 文件里面。 在 InnoDB 的主键索引的叶子节点上,它直接存储了我们的数据。在这里插入图片描述
什么叫做聚集索引(聚簇索引)? 就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。(比如字典的目录 是按拼音排序的,内容也是按拼音排序的,按拼音排序的这种目录就叫聚集索引)。 在 InnoDB 里面,它组织数据的方式叫做叫做(聚集)索引组织表(clustered index organize table),所以主键索引是聚集索引,非主键都是非聚集索引。 如果 InnoDB 里面主键是这样存储的,那主键之外的索引,比如我们在 name 字段 上面建的普通索引,又是怎么存储和检索数据的呢?
在这里插入图片描述
InnoDB 中,主键索引和辅助索引是有一个主次之分的。 辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询,会根据主键值在主 键索引中查询,最终取得数据。 比如我们用 name 索引查询 name= ‘青山’,它会在叶子节点找到主键值,也就是 id=1,然后再到主键索引的叶子节点拿到数据。 为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢?如果主键的数据 类型比较大,是不是比存地址更消耗空间呢? 我们前面说到 B Tree 是怎么实现一个节点存储多个关键字,还保持平衡的呢? 是因为有分叉和合并的操作,这个时候键值的地址会发生变化,所以在辅助索引里 面不能存储地址。

另一个问题,如果一张表没有主键怎么办?
1、如果我们定义了主键(PRIMARY KEY),那么 InnoDB 会选择主键作为聚集索引。
2、如果没有显式定义主键,则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引 作为主键索引。
3、如果也没有这样的唯一索引,则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐 藏的聚集索引,它会随着行记录的写入而主键递增。

select _rowid name from t2;

索引使用原则

我们容易有以一个误区,就是在经常使用的查询条件上都建立索引,索引越多越好, 那到底是不是这样呢?

列的离散(sàn)度(如何选择列为索引)

第一个叫做列的离散度,我们先来看一下列的
离散度的公式: count(distinct(column_name)) : count(*),
列的全部不同值和所有数据行的比例。 数据行数相同的情况下,分子越大,列的离散度就越高。
在这里插入图片描述

在这里插入图片描述

show indexes from user_innodb;

而 name 的离散度更高,比如“富贵”的这名字,只需要扫描一行。

在这里插入图片描述
查看表上的索引,Cardinality [kɑ:dɪ’nælɪtɪ] 代表基数,代表预估的不重复的值 的数量。索引的基数与表总行数越接近,列的离散度就越高

show indexes from user_innodb;

在这里插入图片描述
如果在 B+Tree 里面的重复值太多,MySQL 的优化器发现走索引跟使用全表扫描差 不了多少的时候,就算建了索引,也不一定会走索引。

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
建立索引,要使用离散度(选择度)更高的字段。

联合索引最左匹配

前面我们说的都是针对单列创建的索引,但有的时候我们的多条件查询的时候,也 会建立联合索引。单列索引可以看成是特殊的联合索引。 比如我们在 user 表上面,给 name 和 phone 建立了一个联合索引。

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

在这里插入图片描述
在这里插入图片描述
联合索引在 B+Tree 中是复合的数据结构,它是按照从左到右的顺序来建立搜索树的 (name 在左边,phone 在右边)。 从这张图可以看出来,name 是有序的,phone 是无序的。当 name 相等的时候, phone 才是有序的。 这个时候我们使用 where name= ‘富贵’ and phone = '159xx '去查询数据的时候, B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右。如果 name 相同的时候再比较 phone。但是如果查询条件没有 name,就不知道第一步应该查哪个 节点,因为建立搜索树的时候 name 是第一个比较因子,所以用不到索引。

什么时候用到联合索引

所以,我们在建立联合索引的时候,一定要把最常用的列放在最左边。 比如下面的三条语句,能用到联合索引吗?
1)使用两个字段,可以用到联合索引:

EXPLAIN SELECT * FROM user_innodb WHERE name= '富贵' AND phone = '15900278162'; -- 用的到

在这里插入图片描述
2)使用左边的 name 字段,可以用到联合索引:

EXPLAIN SELECT * FROM user_innodb WHERE name= '富贵'  -- 用的到

在这里插入图片描述
3)使用右边的 phone 字段,无法使用索引,全表扫描:

EXPLAIN SELECT * FROM user_innodb WHERE phone = '15900278162' --用不到

在这里插入图片描述

在这里插入代码片

如何创建联合索引

说我们的项目里面有两个查询很慢。

SELECT * FROM user_innodb WHERE name= ? AND phone = ?;
SELECT * FROM user_innodb WHERE name= ?;

按照我们的想法,一个查询创建一个索引,所以我们针对这两条 SQL 创建了两个索 引,这种做法觉得正确吗?

CREATE INDEX  name on user_innodb(name); 
CREATE INDEX  phone on user_innodb(name,phone);

当我们创建一个联合索引的时候,按照最左匹配原则,用左边的字段 name 去查询 的时候,也能用到索引,所以第一个索引完全没必要。 相当于建立了两个联合索引(name),(name,phone)。
如果我们创建三个字段的索引 index(a,b,c),
相当于创建三个索引:
index(a)
index(a,b)
index(a,b,c)
用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引 的。不能不用第一个字段,不能中断。 这里就是 MySQL 联合索引的最左匹配原则。

覆盖索引

回表: 非主键索引,我们先通过索引找到主键索引的键值,再通过主键值查出索引里面没 有的数据,它比基于主键索引的查询多扫描了一棵索引树,这个过程就叫回表。

例如:select * from user_innodb where name =? ;

在这里插入图片描述
在辅助索引里面,不管是单列索引还是联合索引,如果 select 的数据列只用从索引 中就能够取得,不必从数据区中读取,这时候使用的索引就叫做覆盖索引,这样就避免 了回表

我们先来创建一个联合索引:

ALTER TABLE user_innodb DROP INDEX comixd_name_phone; 
ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

这三个查询语句都用到了覆盖索引:



EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '富贵' AND phone = '15900278162';
EXPLAIN SELECT name FROM user_innodb WHERE name= '富贵' AND phone = '15900278162'; 
EXPLAIN SELECT phone FROM user_innodb WHERE name= '富贵' AND phone = '15900278162';

Extra 里面值为“Using index”代表使用了覆盖索引。
很明显,因为覆盖索引减少了 IO 次数,减少了数据的访问量,可以大大地提升查询 效率

索引条件下推(ICP)

https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

再来看这么一张表,在 last_name 和 first_name 上面创建联合索引。

CREATE TABLE `employees` ( 
`emp_no` int(11) NOT NULL,
`birth_date` date NULL,
 `first_name` varchar(14) NOT NULL,
  last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL,
  `hire_date` date NULL, PRIMARY KEY (`emp_no`) ) ENGINE=InnoDB

关闭 ICP:

  set optimizer_switch='index_condition_pushdown=off';

查看参数:

  show variables like 'optimizer_switch';

现在我们要查询所有姓 wang,并且名字最后一个字是 zi 的员工,比如王胖子,王 瘦子。查询的 SQL:

select * from employees where last_name='wang' and first_name LIKE '%zi' ;

这条 SQL 有两种执行方式:
1、根据联合索引查出所有姓 wang 的二级索引数据,然后回表,到主键索引上查询 全部符合条件的数据(3 条数据)。然后返回给 Server 层,在 Server 层过滤出名字以 zi 结尾的员工。
2、根据联合索引查出所有姓 wang 的二级索引数据(3 个索引),然后从二级索引 中筛选出 first_name 以 zi 结尾的索引(1 个索引),然后再回表,到主键索引上查询全 部符合条件的数据(1 条数据),返回给 Server 层。
在这里插入图片描述
很明显,第二种方式到主键索引上查询的数据更少。 注意,索引的比较是在存储引擎进行的,数据记录的比较,是在 Server 层进行的。 而当 first_name 的条件不能用于索引过滤时,Server 层不会把 first_name 的条件传递 给存储引擎,所以读取了两条没有必要的记录。 这时候,如果满足 last_name='wang’的记录有 100000 条,就会有 99999 条没有 必要读取的记录。 执行以下 SQL,Using where:

explain select * from employees where last_name='wang' and first_name LIKE '%zi' ;

在这里插入图片描述
Using Where 代表从存储引擎取回的数据不全部满足条件,需要在 Server 层过滤。 先用 last_name 条件进行索引范围扫描,读取数据表记录,然后进行比较,检查是 否符合 first_name LIKE ‘%zi’ 的条件。此时 3 条中只有 1 条符合条件。

开启 ICP:

set optimizer_switch='index_condition_pushdown=on';

此时的执行计划,Using index condition:
在这里插入图片描述
把 first_name LIKE '%zi’下推给存储引擎后,只会从数据表读取所需的 1 条记录。 索引条件下推(Index Condition Pushdown),5.6 以后完善的功能。只适用于二 级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作

索引的创建与使用

因为索引对于改善查询性能的作用是巨大的,所以我们的目标是尽量使用索引。

索引的创建

1、在用于 where 判断 order 排序和 join 的(on)字段上创建索引
2、索引的个数不要过多。——浪费空间,更新变慢。
3、区分度低的字段,例如性别,不要建索引。 ——离散度太低,导致扫描行数过多。
4、频繁更新的值,不要作为主键或者索引。 ——页分裂
5、组合索引把散列性高(区分度高)的值放在前面。
6、创建复合索引,而不是修改单列索引。
7、过长的字段,怎么建立索引?
8、为什么不建议用无序的值(例如身份证、UUID )作为索引?存储页是顺序进行的

什么时候用不到索引?

1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、 计算(+ - * /):

explain SELECT * FROM `t2` where id+1 = 4;

2、字符串不加引号,出现隐式转换

explain SELECT * FROM `user_innodb` where name = 136;
explain SELECT * FROM `user_innodb` where name = '136';

3、like 条件中前面带%

explain select *from user_innodb where name like '%wang';

过滤的开销太大,所以无法使用索引。这个时候可以用全文索引。
4、负向查询
NOT LIKE 不能:

explain select *from employees where last_name not like 'wang'

!= (<>)和 NOT IN 在某些情况下可以:

explain select *from employees where emp_no not in (1) 
explain select *from employees where emp_no <> 1

其实,用不用索引,最终都是优化器说了算。 优化器是基于什么的优化器? 基于 cost 开销(Cost Base Optimizer),它不是基于规则(Rule-Based Optimizer), 也不是基于语义。怎么样开销小就怎么来。

https://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#38960 https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

有待完善

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐