请选择 进入手机版 | 继续访问电脑版

[MySql] 浅谈Mysql主键索引与非主键索引区别

[复制链接]
查看119 | 回复18 | 2021-9-12 22:20:14 | 显示全部楼层 |阅读模式
目次

什么是索引

MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据布局 ,索引的目的 在于进步 查询服从 ,类比字典;实际 上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,索引列也是要占用空间。

主键索引和平凡 索引的区别

1.主键索引索引着数据,然而平凡 索引索引着主键ID值(这是在innodb中,但是假如 是myisam中,主键索引和平凡 索引是没有区别的都是直接索引着数据)
2.当你查询用的是where id=x 时,那只必要 扫描一遍主键索引,然后拿到相应数据,但是假如 是查询的平凡 索引的话,那么会先扫描一次平凡 索引,拿到主键值,然后再去扫主键索引,拿到所必要 的数据,这个过程叫做回表

索引具体 采用的哪种数据布局

常见的MySQL重要 有两种布局 :Hash索引和B+ Tree索引,我们利用 的是InnoDB引擎,默认的是B+树

InnoDB利用 的B+ Tree的索引模子 ,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点?

B+ Tree索引和Hash索引区别 哈希索引得当 等值查询,但是不无法举行 范围查询 哈希索引没办法利用 索引完成排序 哈希索引不支持多列团结 索引的最左匹配规则 假如 有大量重复键值得环境 下,哈希索引的服从 会很低,由于 存在哈希碰撞标题

B+ Tree的叶子节点都可以存哪些东西?

在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的黑白 主键索引,也被称之为非聚簇索引。

聚簇索引和非聚簇索引,在查询数据的时间 有区别?

聚簇索引查询会更快
由于 主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还必要 再通过主键的值再举行 一次查询,这个过程称作回表。
非主键索引肯定 会查询多次吗?
覆盖索引也可以只查询一次,覆盖索引(covering index)指一个查询语句的实验 只用从索引中就可以或许 取得,不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时,MySQL只必要 通过索引就可以返回查询所必要 的数据,如许 避免了查到索引后再返回表操作,减少I/O进步 服从 。 如,表covering_index_sample中有一个平凡 索引 idx_key1_key2(key1,key2)。当我们通过SQL语句:select key2 from covering_index_sample where key1 = ‘keytest';的时间 ,就可以通过覆盖索引查询,无需回表。

Index Condition Pushdown(索引下推)

MySQL 5.6引入了索引下推优化,默认开启,利用 SET optimizer_switch = ‘index_condition_pushdown=off';可以将其关闭。官方文档中给的例子和表明 如下: people表中(zipcode,lastname,firstname)构成一个索引

  1. WHERE zipcode=‘95054' AND lastname LIKE ‘%etrunia%' AND address LIKE ‘%Main Street%';
复制代码

假如 没有利用 索引下推技术,则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判定 数据是否符合条件。 假如 利用 了索引下推技术,则MYSQL起首 会返回符合zipcode='95054'的索引,然后根据lastname LIKE '%etrunia%'筛选出符合条件的索引后再返回到MySQL服务端,然后MySQL服务端基于address LIKE '%Main Street%'来判定 数据是否符合条件,如许 返回给MySQL服务端的索引数又会减少。有了索引下推优化,可以在有like条件查询的环境 下,减少回表次数。

查询优化器

一条SQL语句的查询,可以有不同的实验 方案,至于终极 选择哪种方案,必要 通过优化器举行 选择,选择实验 成本最低的方案。 在一条单表查询语句真正实验 之前,MySQL的查询优化器会找出实验 该语句全部 大概 利用 的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的实验 计划。 优化过程大致如下: 1、根据搜刮 条件,找出全部 大概 利用 的索引 2、计算全表扫描的代价 3、计算利用 不同索引实验 查询的代价 4、对比各种实验 方案的代价,找出成本最低的那一个

关于索引的题

  1. CREATE TABLE `geek` (
  2. `a` int(11) NOT NULL,
  3. `b` int(11) NOT NULL,
  4. `c` int(11) NOT NULL,
  5. `d` int(11) NOT NULL,
  6. PRIMARY KEY (`a`,`b`),
  7. KEY `c` (`c`),
  8. KEY `ca` (`c`,`a`),
  9. KEY `cb` (`c`,`b`)
  10. ) ENGINE=InnoDB;
复制代码

由于汗青 缘故起因 ,这个表必要 a、b 做团结 主键。
那意味着,单独在字段 c 上创建一个索引,就已经包含了三个字段,为什么要创建“ca”“cb”这两个索引?

  1. select * from geek where c=N order by a limit 1;
  2. select * from geek where c=N order by b limit 1;
复制代码

标题 :这位同事的表明 对吗? 哪些索引没有必要,可以删除?

答案:

(1) 主键 a,b 的聚簇索引构造 次序 相称 于 order by a,b ,也就是先按 a 排序,再按 b 排序,c 无序。

  1. –a--|–b--|–c--|–d--
  2. 1 2 3 d
  3. 1 3 2 d
  4. 1 4 3 d
  5. 2 1 3 d
  6. 2 2 2 d
  7. 2 3 4 d
复制代码

(2) 索引 ca 的构造 是先按 c 排序,再按 a 排序,同时记录主键。

  1. –c--|–a--|–主键部分b-- (注意,这里不是 ab,而是只有 b)
  2. 2 1 3
  3. 2 2 2
  4. 3 1 2
  5. 3 1 4
  6. 3 2 1
  7. 4 2 3
复制代码

上面的这个索引ca 与索引c 的数据是如出一辙 的。

(3) 索引 cb 的构造 是先按 c 排序,在按 b 排序,同时记录主键

  1. –c--|–b--|–主键部分a-- (同上)
  2. 2 2 2
  3. 2 3 1
  4. 3 1 2
  5. 3 2 1
  6. 3 4 1
  7. 4 3 2
复制代码

以是 结论是ca可以去掉,cb保留。

到此这篇关于浅谈Mysql主键索引与非主键索引区别的文章就先容 到这了,更多相干 Mysql主键索引与非主键索引内容请搜刮 脚本之家从前 的文章或继续欣赏 下面的相干 文章渴望 大家以后多多支持脚本之家!


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

avatar 风雨狼 | 2021-9-16 19:00:12 | 显示全部楼层
怎么我回帖都没人理我呢?
回复

使用道具 举报

avatar 天褐女孩涛 | 2021-9-25 20:54:33 | 显示全部楼层
鸟大了,什么林子都敢进啊!
回复

使用道具 举报

avatar V刘晨曦 | 2021-9-29 19:22:23 | 显示全部楼层
好多兽医在广场上义诊,admin楼主去看看吧!
回复

使用道具 举报

avatar 淡情惜缘缘wt | 2021-10-1 22:51:04 | 显示全部楼层
admin楼主英明!
回复

使用道具 举报

avatar 阿呜O | 2021-10-11 17:49:10 | 显示全部楼层
缺乏激情了!
回复

使用道具 举报

avatar 阳子1989 | 2021-10-11 18:57:29 | 显示全部楼层
admin楼主就是我的榜样哦
回复

使用道具 举报

avatar 喜欢吃芒果干俺 | 2021-10-11 21:29:29 | 显示全部楼层
你觉得该怎么做呢?
回复

使用道具 举报

avatar 单色不单叼 | 2021-10-11 21:32:02 | 显示全部楼层
视死如归的架势啊!
回复

使用道具 举报

avatar 关儿1 | 2021-10-11 22:12:03 | 显示全部楼层
好东西,赞一个!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则