我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MYSQL(二)数据库聚集/非聚集索引,索引和锁

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MYSQL(二)数据库聚集/非聚集索引,索引和锁

聚集索引(InnoDB,使用B+Tree作为索引结构)

在一个结构中保存了b-tree索引和数据行;按照主键的顺序存储在叶子页上;

主键索引:叶节点存储key-value为(主键数据:所有剩余列数据)

二级索引(非聚簇索引):叶节点存储key-value为(索引列数据:主键数据)

非叶节点只存储 索引列

优点:

可以把相关数据保存在一起,如根据用户id聚集电子邮箱信息,只需要读取少数的数据页就能获取某个id用户的全部邮件;

数据访问更快,将索引和数据保存在同一个b-tree中;

使用覆盖索引扫描的查询可以直接使用叶节点中的主键值;

缺点:

插入速度严重依赖于插入顺序,按照主键的顺序插入是加载数据到innodb表中速度最快的方式;

插入新行可能面临页分裂的问题,页分裂导致表占用更多磁盘空间;

通过二级索引需要两次查找,存储引擎找到二级索引的叶子节点获得对应的主键值,根据这个值去聚簇索引中找到对应的行

主键:

如果表没有什么数据需要被聚集(如上述邮件用户id),那么可以定义一个代理键作为主键,使用auto_increment自增列;


非聚集索引(MyISAM使用B+Tree作为索引结构)

按照数据插入顺序存储在磁盘上,访问数据需要一次系统调用;

主键索引/二级索引:叶节点存储(索引列数据:数据在磁盘上的行号)


对比:

InnoDB提供事务支持事务,外键等功能;MyISAM不支持。

InnoDB支持行级锁;MyISAM只支持表级锁

InnoDB要求必须有主键;MyISAM允许没有任何索引和主键的表存在,索引都是保存行的地址。


覆盖索引


一个索引包含(或者说覆盖)所有需要查询的字段的值

覆盖索引要存储索引列的值,只能用b-tree索引做覆盖索引(不能用哈希索引,全文索引等)

优点

1. MyISAM存储引擎在内存中只存储索引,覆盖索引不需要进行系统调用;

2. innodb存储引擎的聚簇索引机制,二级主键如果能覆盖查询,可以避免对主键索引的二次查询;


全文索引

希望通过关键字的匹配来进行查询过滤,而不是通过常规的数值比较,范围过滤操作;

MyISAM的全文索引是一类特殊的B-Tree索引,共有两层,第一层是所有关键字,对于每一个关键字的第二层,包含的是一组相关的“文档指针”;

对于文档对象中的所有词语的过滤条件:

1. 停用词列表中的词语都不会被索引

2. 长度大于指定范围和小于指定范围的词语不会被索引

另外,全文索引不会存储关键字具体匹配在哪一列。


1. 自然语言全文索引

根据where子句中的MATCH AGAINST区分是否使用全文索引

Eg.在表file_text的字段title,description上建立fulltext全文索引

Select film_id,title,right(description,25)

Match(title,description) against(‘factory  casualties’) as relevance

From file_text where Match(title,description) against(‘factory  casualties’);

结果:

Film_id         title                             right(description,25)                     relevance

831           spirited casualties           a car is a baloon factory                  8.4692449702

126           casualties encino            face a boy in a monastery                 5.2615661621

... ...

函数match()将返回关键词的匹配相关度,是一个浮点数。


2. 布尔全文索引

用户可以自定义被搜索词语的相关性;用户可以通过一些前置修饰符定制搜索:
example meaning

Dinosaur 包含dinosaur的行rank值更高

-dinosaur 包含dinosaur的行rank值更低

+dinosaur 行记录必须包含dinosaur

-dinosaur 行记录不能包含dinosaur

Dino* 包含以dino开头的单词的行rank值更高

Eg. Select film_id,title,right(description,25)

From file_text where Match(title,description) against(‘+factory  +casualties’ in boolean mood);

结果:

Film_id title right(description,25)

831         spirited casualties   a car is a baloon factory

搜索的关键词是不常见的词语时,比LIKE操作要快得多,因为是直接从索引中过滤记录。

全文索引的限制:

1. mysql的全文索引只有一种判断相关性的方法:词频。没有其他相关性排序算法,如存储的位置。

2. 只有全文索引全部在内存中时,性能才能非常好

3. 其他的where条件,只能在mysql完成全文搜索返回记录后才能进行

4. 全文索引不能存储列的实际值,不能用作覆盖扫描

5. 除了相关性排序,不能用作其他排序

配置和优化

1. 停用词表

2. 允许最小词长

在搜索的精度和搜索的效率之间找到合适的平衡点。


索引和锁

      索引可以让查询锁定更少的行,innodb只有在访问行时才会对其加锁,而索引可以减少innodb访问的行数,从而减少锁的数量;

但是,只有当innodb在存储引擎层能够过滤掉不需要的行时才有效,如果无法过滤,那么在innodb检索到数据并返回给服务器层,mysql才能应用where语句进行过滤,而innodb已经锁住了这些行,直到服务器层过滤完成后释放锁;

如:select actor_id from sakila.actor where actor_id < 5 (范围)and actor_id <> 1 (过滤) for update;

执行explain命令,显示type为range,表示mysql为该查询选择的执行计划是索引范围查询,即在存储引擎层只执行了actor_id < 5的条件,查询结果:2,3,4;而被锁定的数据行:1,2,3,4;

即使使用索引,也可能锁住一些不需要的行,但是不使用索引查找的话mysql会全表扫描并锁住所有的行。



免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MYSQL(二)数据库聚集/非聚集索引,索引和锁

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL 聚集索引和二级索引

Clustered and Secondary Indexes(聚集索引和二级索引)Every InnoDB table has a special index called the clustered index where the data for the
MySQL 聚集索引和二级索引
2022-03-11

搞懂MySQL聚集索引与非聚集索

我们在开发或者面试的过程中经常会涉及到索引。今天我们来详细剖析一下索引常用的知识点。首先我们先介绍mysql的常用的存储引擎,其次是索引分类。

mysql聚簇索引和非聚簇索引区别

聚簇索引的行按索引键顺序物理存储,非聚簇索引的行不按顺序存储且包含指向数据行的指针。聚簇索引提高范围和等值查询性能,每个表只能有一个;非聚簇索引允许在多个列上创建索引,每个表可以有多个。MySQL 聚簇索引和非聚簇索引的区别聚簇索引和非聚
mysql聚簇索引和非聚簇索引区别
2024-08-01

MySQL聚簇索引和非聚簇索引的区别详情

目录聚簇索引非聚簇索引总结前言:在 mysql 默认引擎 InnoDB 中,索引大致可分为两类:聚簇索引和非聚簇索引,它们的区别也是常见的面试题,所以我们今天就来盘它们。聚簇索引聚簇索引(Clustered Index)一般指的是主键
2022-06-14

mysql聚簇索引和非聚簇索引有什么区别

MySQL中的聚簇索引和非聚簇索引是两种不同的索引类型,它们在存储和查询数据时有一些区别:聚簇索引:聚簇索引将数据行存储在索引的叶子节点中,而不是单独的数据页中。这意味着索引本身就是数据的一部分,可以通过索引直接访问数据,而不需要额外的查找
mysql聚簇索引和非聚簇索引有什么区别
2024-04-09

mysql聚簇索引和非聚簇索引有什么不同

MySQL中的聚簇索引和非聚簇索引有以下不同点:1. 存储方式:聚簇索引中,数据行的顺序与索引的顺序一致,索引直接指向数据行。非聚簇索引中,索引是独立于数据行的,索引中的每个条目都包含一个指向对应数据行的指针。2. 数据访问:使用聚簇索引进
2023-09-15

MySQL聚簇索引和非聚簇索引的区别是什么

这篇文章主要介绍“MySQL聚簇索引和非聚簇索引的区别是什么”,在日常操作中,相信很多人在MySQL聚簇索引和非聚簇索引的区别是什么问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”MySQL聚簇索引和非聚簇索引
2023-07-02

MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么

今天小编给大家分享一下MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下
2023-04-21

聚集索引和非聚集索引,很简单的面试题,但是很多人都不知道

非聚簇索引是将索引和数据分开存储,那么在访问数据的时候就需要2次查找,但是和InnoDB的非聚簇部分还是有所区别。InnoDB是需要查找2次树,先查找辅助索引树,再查找聚簇索引树(这个过程也叫回表)。而MyISAM的主键索引叶子结点的存储的

MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引详细介绍

目录一、索引类型二、聚簇索引和非聚簇索引三、覆盖索引四、联合索引最左前缀匹配原则索引下推五、唯一索引一、索引类型索引根据底层实现可分为B-Tree索引和哈希索引,大部分时候我们使用的都是B-Tree索引,因为它良好的性能和特性更适合于构建
2023-04-19

mysql怎么创建聚集索引

在MySQL中,你可以使用`CREATE INDEX`语句来创建聚集索引。以下是创建聚集索引的一般语法:```sqlCREATE [UNIQUE] CLUSTERED INDEX index_nameON table_name (colum
2023-08-29

编程热搜

目录