领诺科技网站建设,wordpress经常卡顿,辽宁定制网站建设推广,火狐网站开发好的插件索引的概念
MySQL的索引是⼀种数据结构#xff0c;它可以帮助数据库高效地查询、更新数据表中的数据。索引通过 ⼀定的规则排列数据表中的记录#xff0c;使得对表的查询可以通过对索引的搜索来加快速度。
MySQL索引类似于书籍的目录#xff0c;通过指向数据行的位置…索引的概念
MySQL的索引是⼀种数据结构它可以帮助数据库高效地查询、更新数据表中的数据。索引通过 ⼀定的规则排列数据表中的记录使得对表的查询可以通过对索引的搜索来加快速度。
MySQL索引类似于书籍的目录通过指向数据行的位置可以快速定位和访问表中的数据比如 汉语字典的目录索引页我们可以按笔画、偏旁部首、拼音等排序的⽬录索引快速查找到需 要的字。
探讨索引的数据结构
我们来一起探讨那种数据结构用来当作索引更适合数据库
首先是哈希表对于哈希表来说查找的时间复杂度为 O(1)十分优秀但是却不适合数据库因为哈希表不支持范围查找而我们数据库是需要支持范围查找的例如我们有张成绩表要查询成绩大于等于60以上的学生时这时就要求我们数据库能支持**范围查找**所以哈希表并不适合作索引 接下来登场的是二叉搜索树查找的时间复杂度为O(logN)但是如果出现极端情况二叉搜索树是可能会退化成一颗单分支的树这时候时间复杂度就变成O(N)还是不理想这时候大家可能会想到AVL树或者红黑树AVL树就不说了旋转次数太多了在数据库面前数据量十分庞大如果旋转呵呵… 我们来看一下红黑树虽然保持相对平衡但是在数据一多的情况下我们无法保证树到底有多高
为什么要讨论树高呢 因为数据库的数据是存储在磁盘上的所以当你需要读取数据的时候是需要进行磁盘的IO的磁盘的IO速度是十分慢的如果IO 次数一高效率就会低下哎所以我们应该减少磁盘IO次数也就是减小树高那么红黑树就不能满足了 磁盘IO 是制约数据库性能的主要因素 既然红黑树不行那我们可以考虑B树这时一颗多路平衡树由于是多路所以可以降低书高但是我们的MySQL还是不满意觉得效率还不是不够高于是MySQL 就使用B树的变形也就是B树我们在前面就知道B树有一些特点真实的数据都是保存在叶子节点上的非叶子结点只是起到一个导航的作用并且叶子结点是使用双向链表进行连接的所以在数据库进行范围查找的时候十分方便。
B树示例
B树示例 B树的时间复杂度是O(logN)并且可以有效的控制树高 B树与B树的对比 1.B树的叶子结点之间有相互连接的引用可以通过这个连接找到与其相邻的兄弟节点mysql 在组织叶子结点时使用的时双向链表 2.非叶子结点的值包涵在叶子结点中MySQL 非叶子结点只保存了对子结点的引用没有保存真实的数据所有真实的数据都是在叶子结点中 3.对于B树而言在相同书高的情况下查找任意元素的时间复杂度都是一样的性能均衡。 MySQL的页
在 .ibd 文件中最重要的结构体是 Page(页)页是内存与磁盘交互最小单元默认大小是 16KB 每一个.ibd 文件由页组成
每次内存与磁盘的交互至少读取一页所以在磁盘中每个页内部的地址都是连续的之所以这么做是因为在使用数据的过程中根据局部性原理将来要使用的数据大概率与当前访问的数据在空间上是临近的所以一次从磁盘中读取一页的数据放入内存中当下次查询的数据还在这个页中的时候就可以直接从内存中读取从而减少磁盘IO以此来提高性能。 局部性原理 是指程序在执行时呈现出局部性规律在一段时间内整个程序的执行仅限于程序中的某⼀部 分。相应地执行所访问的存储空间也局限于某个内存区域局部性通常有两种形式时间局部 性和空间局部性。 时间局部性Temporal Locality如果⼀个信息项正在被访问那么在近期它很可能还会被再 次访问。 空间局部性Spatial Locality将来要用到的信息大概率与正在使用的信息在空间地址上是临 近的。 每一个页中即使没有数据也会使用 16KB 的存储空间同时与索引的B树中的节点对应 我们可以查询MySQL中页的大小 16384 / 1024 16 KB
数据页
数据库由很多种的页索引页、数据页等等这里我们讨论数据页
页头页尾
数据页由页头和页尾 这里我们关注的是页头里包含上一页页号和下一页页号通过这两个属性可以把页与与之间链接起来形成一个双向链表。 页主体
页主体部分是保存真实数据的主要区域每当创建一个新页时都会自动分配两个行一个是页内最小行Infinum另一个是页内最大行Supremun这两行并不存储任何真实数据而是最为数据行链表的头和尾【你可以理解为链表的两个空节点一个是空的头节点另一个是空的尾节点】
第一个数据行有一个记录下一行的地址偏移量的区域next_record将页内所有的数据行组成了一个单向链表
当向一个新页插入数据时将Infimun 连接第一个数据行最后一行真实数据连接Supremun这样数据行就构成了一个单向链表当更多的数据行插入之后会按照主键从小到大的顺序进行连接
页目录
看到这里大家是不是又疑惑单向链表的查找效率不是很慢吗时间复杂度是O(N)当然开发数据库的人也想到了为了提高查找效率InnoDB采用二分查找来解决查询效率问题于是他们开发了页目录 具体的实现方式是在每一个页中加入一个叫做页目录的结构将页内包括最大行和最小行在内的数据行进行分组这里单独约定最小行独自为一组其他分组最多8条数据同时将最大行放在最后一个分组中按照主键从小到大的顺序记录在页目录中页目录每一个位置称为槽每一个槽对应一个分组一旦分组中数据行超过8个的时候就会分裂出一个新的分组 在后续查找某条数据行时通过二分查找就可以找到对应的槽然后在槽对应的分组中进行最多8条数据的便即可获得目标数据 数据页头
数据页头记录了当前页保存数据相关的信息
B树在数据库中的应用 非叶子节点保存索引数据叶子节点保存真实数据 举个例子查找 id 为7 的数据行首先通过索引页1发现 7 7 进入到索引页2然后 7 9进入到数据页4最后找到id 为 7 的数据行一共进行了三次磁盘IO。 以上的IO过程加载索引页1–加载索引页2–加载数据页3 我们现在来感受B树的强大
假设一条数据大小为 1KB忽略数据页中页头页尾等等非真实数据的内容也就是说一个数据页可以保存16条数据。
索引页保存的是索引这里我们以主键索引为例索引页保存主键值和下一页的地址主键用bigint 来保存也就是8字节地址为6字节一共就是14字节页的大小为16KB那么一个索引页可以保存 16 * 1024 / 14 1170 条索引记录
如果B树树高为三层的话一条索引记录对应一个数据页那么三层B树就可以保存 1170 * 1170 * 16 21,902,400 条数据第一层一个结点有1170 条索引那么第二层就有1170个结点第二层每个结点都有1170条索引也就是说如果是在两千多万条数据的表中我们通过三次IO 就可以完成数据的检索了。
索引的分类
主键索引
当在表中定义了一个主键primary key 的时候MySQL会将其作为索引这个索引我们称之为主键索引。
我们推荐在每一张表都去创建一个主键如果没有的话可以添加一个自增列。
唯一索引
当在表中定义一个唯一键unique 时MySQL会自动创建索引这个索引被称之为唯一索引
普通索引
最基本的索引类型没有唯一性的限制也就是相比于唯一索引来说可以存在重复的列
普通索引可以由多列组合组成这时候可以称为复合索引。
普通索引是由用户自己手动创建的如果用户觉得某个列查询比较频繁可以考虑为这一列创建索引。
全文索引
基于文本列char、varchar上创建的索引叫做全文索引。
全文索引可以加快对这列文本列包含的数据查询和DML操作用于全文搜索仅有MyISAM 和 InnoDB 引擎支持该索引。
聚集索引
与主键索引是同义词也就是说主键索引就是聚集索引也可以称为聚簇索引。
如果表中没有定义主键primary key InnoDB 会使用第一个 unique 和 not null 的列作为聚集索引。 如果表中实在没有primary key 或者合适的 unique 索引InnoDB 会为新生成的行生成一个行号为 6 字节的 ROW_ID 字段记录ROW_ID 是单调递增的并且使用 ROW_ID 作为索引。 注意ROW_ID 是MySQL内部的设置用户是无法获取的MySQL使用这个索引是为了更好地创建索引树来管理数据。 非聚集索引
非聚集索引也可以称为二级索引就是除了聚集索引以外的索引就是非聚集索引。
二级索引中的每条记录都包含改行的主键列以及二级索引指定的列。
InnoDB 会使用这个主键值来搜索聚集索引中的行这个过程称为回表查询
索引覆盖
当一个 select 语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列这时候就可以直接返回数据就不用进行回表查询这样的现象被称为索引覆盖 简单解释一下聚集索引创建的索引树是包含完整的真实数据的非聚集索引创建的索引树是没有包含完整的真实数据但是包含对应的主键值如果通过非聚集索引查询到的数据不满足用户要求会通过这个主键值来到主键索引树获取完整的数据这个现象就是回表查询也就是查了两个索引树 如果通过非聚集索引的索引树能获取到目标数据就不需要进行回表查询直接返回数据即可这就是索引覆盖 举个例子我们有一张学生表包含学生的 id 姓名班级其中定义学生的 id 为主键值姓名字段定义为普通索引现在我要查询张三的所有信息
由于姓名就是索引通过张三到姓名的索引树中查找随后只能获得主键值id 姓名为张三的数据但是我们要的是张三所有的数据所以数据库就会通过主键值到主键索引树去查找张三的完整的数据这就是回表查询
如果我们至少想查张三的姓名select name from student where name 张三; 这时废话sql 至少作为演示数据库通过姓名这个索引树就可以得到张三这个名字直接返回数据不需要进行回表查询这就是索引覆盖
索引的使用
自动创建
当表中定义了主键、外键、唯一键MySQL都会为其创建对应的索引以及索引树。
如果表中没有任何约束MySQL回自动为每一列生成一个索引并且使用ROW_ID 进行标识
手动创建
主键索引
在创建表时直接在字段后面定义主键
create table test0 (id bigint primary key auto_increment,name varchar(50)
);在创建表时先定义好字段最后定义主键
create table test0 (id bigint auto_increment,name varchar(50),primary key(id)
);创建表之后我们可以通过修改表来定义主键
create table test0 (id bigint,name varchar(50)
);alter table test0 add primary key(id);如果你还想让主键自增还可以在写下面的语句
alter table test0 modify id bigint auto_increment;modify 后面要跟完整的字段定义语句。 唯一索引
在创建表时在定义字段的同时直接定义唯一键unique
create table test1 (id bigint,name varchar(50) unique
);创建表时在定义完字段后单独定义唯一键unique
create table test1 (id bigint,name varchar(50),unique(name)
);在创建表后可以通过修改表的字段来定义唯一键
create table test1 (id bigint,name varchar(50)
);alter table test1 add unqiue(name);普通索引
创建表时再创建完字段后定义普通索引index
create table test2 (id bigint,name varchar(50),index(name)
);通过查看表结构我们得知普通索引的标记为MUL 创建完表后可以通过修改表的方式添加普通索引index
create table test2 (id bigint,name varchar(50)
);alter table test2 add index(name);再创建表后可以通过 create 语句手动创建普通索引
语法形式create index index_name on table_name(字段名);
index_name 表示索引名也就是说你可以自己命名索引。
推荐索引命名为 ind_表名_字段名这样我们在查询索引的时候可以得知这是在哪张表的普通索引并且是哪个字段。
create table test2 (id bigint,name varchar(50)
);create index ind_test2_name on test2(name);复合索引
和创建普通主键一样只是多加一些字段名。这里直接上sql 语句
create table test3(id bigint primary key auto_increment,sn bigint unique,name varchar(50) not null,class_name varchar(20) not null,index(sn,name)
);create table test3(id bigint primary key auto_increment,sn bigint unique,name varchar(50) not null,class_name varchar(20) not null
);alter table test3 add index (sn,name);create table test3(id bigint primary key auto_increment,sn bigint unique,name varchar(50) not null,class_name varchar(20) not null
);create index ind_test3_sn_name on test3(sn,name);注意事项
索引的创建应该建立在高频的查询列上并且数据量比较大因为数据量小尽管这个是高频查询的列但是全表扫描会比索引树要更快一些。
索引需要占用额外的空间每创建一个索引都会创建对应的索引树。
对于表进行插入、删除、更新操作的时候由于同时会修改索引树可能会影响到性能。
创建过多或者不合理的索引会导致性能的下降所以我们要谨慎创建索引。
查看索引
有三种方式查看索引方式一show keys from table_name; 我们来看一下上面的表格Table 表示表名 Non_unique 表示是否不唯一 0 表示否唯一1表示是不唯一 Seq_in_index 表示在索引中的标号从 1 开始排号test3 有一个复合索引sn, name所以 name 标号为 2 Column_name 表示对应的字段名 Key_name 表示索引名主键索引默认索引名为 PRIMARY Index_type 表示索引的数据结构这里显示BTREE说明是B树其实就是B树B树是B树的变形。 方式二show index from table_name;
获得的结果集和上面是一样的
方式三desc table_name; 这是简单查询索引 删除索引
删除主键索引alter table table_name drop primary key; 如果发生上面的报错信息是因为这个主键值带有自增属性我们要先删除其自增的属性然后才能进行删除主键索引的操作
alter table test3 modify id bigint;删除了自增属性后可以进行主键索引的删除alter table test3 drop primary key; 删除其他索引alter table table_name drop index 索引名; 查看查询语句有没有走索引
以学生表为例 查看索引 进行全表数据获取select * from student; 查看查询语句有没有走索引的语法格式为explain 查询语句 我们现在来看一下全表数据获取有没有走索引 介绍一下select_type 表示查询方式type 表示访问类型key 表示索引possible_keys 表示可能走的索引 由上图可知查询为简单查询访问类型为ALL(全表扫描)key 索引为 NULL说明没有走索引。
我们来看一下 type:
ALLindexrangerefeq_refconst,systemNULL
从左到右性能越来越好
ALL扫描全表 index : 扫描全部索引树 range: 扫描部分索引在索引范围扫描对索引的扫描开始于某一点返回匹配值域的行常见于 between、、等查询 ref : 使用非唯一索引或非唯一索引前缀进行的查找不是主键或者不是唯一索引 eq_ref唯一性索引扫描遂于每个索引键表中只要一条记录与之匹配常见于主键或唯一索引扫描 constsystem : 单表中最多有一个匹配行查询起来非常迅速例如根据主键或唯一索引查询。system 是const类型的特例当查询的表只有一行的情况下使用system NULL:不用访问表或者索引直接就能得到结果
我们使用主键来进行查询 可以看出走的是主键索引并且进行的是范围查找。
最后我们来看一下Extra: Using index: 表示使用索引如果只有 Using index 说明他没有查询到数据表只用索引表就完成了这次查询这个也叫索引覆盖 Using where: 表示条件查询如果不读取表中的所有数据或者不是仅仅通过索引树就能获得所许需要的数据的时候则会出现 Using where
小结
创建索引主键索引(primary key) 唯一索引(unique) 普通索引(index)
创建索引可以在创建表中定义也可以在表外创建
表外创建索引alter table table_name add primary key / unique / index 字段名; 还有 普通索引的专属创建形式create index 索引名 on table_name(字段名,字段名...);
修改表的字段alter table table_name modify 字段定义语句; 通过字段定义语句就可以修改对应的字段属性了。
查看索引的方法show keys/index from table_name 或者简单查看索引desc table_name; 简单查看索引的结果集的Key: PRI 表示主键索引UNI表示唯一索引MUL 表示普通索引
删除索引alter table table_name (primary key) / (index 索引名);
