MYSQL索引

最新推荐文章于 2024-07-16 11:25:46 发布

姑娘加油

最新推荐文章于 2024-07-16 11:25:46 发布

阅读量358

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MYSQL

本文链接：https://blog.youkuaiyun.com/qq_37937537/article/details/83380821

MYSQL 专栏收录该内容

3 篇文章

订阅专栏

MySQl索引

1，索引的概念

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

目的：提高查询的效率

2，索引的用法

创建索引

单列索引

create table user (id int primary key,name varchar(20),age int,index(name));
create index name_index on user(name);

联合索引

create table user1(id int primary key,name varchar(20),age int,index(name,age));
create index name_age_index on user1(name,age);

删除索引

drop index index_name on user;

索引的种类

primary key 主键索引
Unique 唯一性索引
Create index 单列索引/多列索引

4，索引的使用

explain（+SQl语句）：查看查询语句的查询计划

Id：代表着表的查询顺序

Select_type：显示的是select语句的访问类型（子查询、连接查询...）

Table：牵扯到的表名

Type：显示的是访问类型，null代表最坏情况。

Possible_keys：可能会用到的索引

Key:查询时用到的索引

Key_len：索引的长度

Rows：影响了几行

Extra：其他备注信息（是否使用了临时表，是否使用了外部排序）

示例（一个表的查询只能使用一个索引）

单列索引

当User表对name这个字段没有索引时，它将会遍历整个user表（3行）来寻找这个where过滤条件。

为user中的name字段加上索引之后，我们可以清楚的看到此时使用了name_index这个索引，执行这个查询语句仅仅只遍历了一行。

联合索引

当我们不建立索引时，这时不仅遍历了全表，还产生了using filesort（外部排序）---》非常降低效率

我们创建一个联合索引，这时候会使用到联合索引，也不会出现外部排序了。

如果联合索引没有使用到它的第一个字段，则不会触发联合索引

连接查询使用索引

内连接：小表进行全部扫描，大表根据索引直接去找

外连接：左连接时最好给右表加索引，右连接给左表加索引。

表中有多个索引的话：

索引使用的注意事项：

要使用联合索引，一定要先使用联合索引的第一个字段
给经常作为where过滤条件的字段和分组排序字段创建联合索引
一般给区分度高的字段创建索引
varchar(200) create index desc_index on table product(desc);尤其是对于字符串类型的字段，创建索引的时候，可以先指定索引的长度，只用字符串的一部分来创建索引数据就可以了
多表join，先按个表的查询条件比较那个开销小，从小表中取出所有符合条件的，在大表查

不能使用索引的情况

Like通配符放在最前，不能用索引
对列用函数运算，不能用索引
数制转换不能用索引
Or、not in、!=、<>不能用索引

5，索引的底层结构

（以InnoB存储引擎支持的B+树索引为例）

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

在我们的系统底层，是分为这三个部分的，我们一般进行Io操作时，用户空间通过操作系统来操作计算机硬件中的磁盘来读取数据的。磁盘一般是通过磁盘块来传递数据，而操作系统中是通过page为单位来分配内存的。

B树与B+树的区别

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B树

B+树

区别：

B-树节点上不仅要存索引值，还存储索引值所在行的记录；而B+树非叶子节点存的全部都是关键字（索引值），只有叶子节点上，存储了索引值以及对应的记录。
B+树索引关键字，都会出现在叶子节点上，非叶子节点只存关键字，意味着，所有的记录都在叶子节点上存储，所有记录搜索的时间是平均的
B+树的层数要比B-树低一些，所以搜索的时间会更快
B+树把所有叶子节点都串在了一条链表结构上

我们所用到的索引它的底层是一颗B+树

B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。

查询B+树索引的流程

首先通过B+树索引找到叶节点，再找到对应的数据页，然后把数据页加载到内存中，通过二分查找Page 中的槽，查找出一个粗略的目录，然后根据槽的指针指向链表中的行记录，之后再链表中依次查找。

B+树索引的分类

主键索引

主键索引是按每张表的主键构造的一颗B+树，并且叶节点存放着整张表的行记录数据。

InnoDB引擎在组织索引和数据时，就是通过聚簇索引检索具体Data Page。这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键。在InnoDB引擎中每一个叶子节点都是一个Page信息，构成链表结构后就可以检索每一个Page的上一个Page和下一个Page信息，这恰好也是InnoDB引擎中预读功能的实现基础。

只需要将主索引B+树的非叶子节点加载到内存中。当检索请求需要读取某一个具体的Data Page时，再从磁盘上进行读取。

辅助索引

非聚族索引首先也是一颗B+树，只是非聚簇索引的叶子节点不再关联具体的Data Page信息，而是关联另一个索引值。InnoDB引擎下工作的每一个数据表虽然都只有一个聚簇索引，那就是它的主索引。但是每一张数据表可以有多个非聚簇索引，而后者的叶子节点全部存储着对应的数据主键信息

聚集索引与非聚集索引

聚集索引（数据和索引在一个文件中）
非聚集索引（数据和索引独立存放）

一般我们执行这个语句

Select age from user where name = ‘哈哈’;

假设我们给name设置了索引。Id为这个表的主键

首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

6，MYSQL存储引擎

MySQL存储引擎 => 表的存储方式 *.frm（表的结构） D（表的数据）（索引）

InnoDB （B+树索引结构）

支持外键
支持事务
支持并法操作的锁级别是 =》行锁 =》锁的粒度小 =》并发能力强 =》会产生死锁
它支持的索引是聚集索引(数据和索引是在一个文件中存储的）InnoDB的数据文件本身就是索引文件，表数据文件本身就是按B+Tree组织的一个索引结构

user.frm user.ibd

B+树索引结构
InnoDB要求表必须有主键列，如果用户没有指定，InnoDB会自动为该表生成整形类型的主键列，目的就是为了生成主键索引树，让记录有地方存储

得出的优化点

不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大
用非单调的字段作为主键在InnoDB中也不好，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键就很不错了
聚簇索引键被更新造成的成本除了索引数据可能会移动，相关的所有记录数据也要移动

MyISAM