浅谈Mysql主键索引与非主键索引区别

目录
  • 什么是索引
  • 主键索引和普通索引的区别
  • 索引具体采用的哪种数据结构
  • InnoDB使用的B+ Tree的索引模型,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点?
  • B+ Tree的叶子节点都可以存哪些东西?
  • 聚簇索引和非聚簇索引,在查询数据的时候有区别?
  • Index Condition Pushdown(索引下推)
  • 查询优化器
  • 关于索引的题

什么是索引

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)构成一个索引

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、对比各种执行方案的代价,找出成本最低的那一个

关于索引的题

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

由于历史原因,这个表需要 a、b 做联合主键。
那意味着,单独在字段 c 上创建一个索引,就已经包含了三个字段,为什么要创建“ca”“cb”这两个索引?

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

问题:这位同事的解释对吗? 哪些索引没有必要,可以删除?

答案:

(1) 主键 a,b 的聚簇索引组织顺序相当于 order by a,b ,也就是先按 a 排序,再按 b 排序,c 无序。

–a--|–b--|–c--|–d--
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d

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

–c--|–a--|–主键部分b-- (注意,这里不是 ab,而是只有 b)
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

上面的这个索引ca 与索引c 的数据是一模一样的。

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

–c--|–b--|–主键部分a-- (同上)
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

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

到此这篇关于浅谈Mysql主键索引与非主键索引区别的文章就介绍到这了,更多相关Mysql主键索引与非主键索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL索引之主键索引

    在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别? 上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别. 1.主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录.一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL. 在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则: 1.采用一个没有业务用途的自增属性列作为主键: 2.主键字段值总是不更新,只有新增或

  • MySQL中主键索引与聚焦索引之概念的学习教程

    主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录.一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL. 在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则: 采用一个没有业务用途的自增属性列作为主键: 主键字段值总是不更新,只有新增或者删除两种操作: 不选择会动态更新的类型,比如当前时间戳等. 这么做的好处有几点: 新增数据时,由于主键值是顺序增长的,innodb page发生分裂的概率降低了:可以

  • Oracle与Mysql主键、索引及分页的区别小结

    区别: 1.主键,Oracle不可以实现自增,mysql可以实现自增. oracle新建序列,SEQ_USER_Id.nextval 2.索引: mysql索引从0开始,Oracle从1开始. 3.分页, mysql: select * from user order by desc limit n ,m. 表示,从第n条数据开始查找,一共查找m条数据. Oracle:select * from user select rownum a * from ((select * from user)a

  • MySQL 主键与索引的联系与区别分析

    关系数据库依赖于主键,它是数据库物理模式的基石.主键在物理层面上只有两个用途: 惟一地标识一行. 作为一个可以被外键有效引用的对象. 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针.下面是主键和索引的一些区别与联系. 1. 主键一定是唯一性索引,唯一性索引并不一定就是主键. 所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引.因为主键可以唯一标识某一行记录,所以可以确保执行数据更新.删除的

  • 当Mysql行锁遇到复合主键与多列索引详解

    背景 今天在配合其他项目组做系统压测,过程中出现了偶发的死锁问题.分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题. 比如表t_lock_test中有两个主键都为primary key(a,b) ,但是更新时却通过update t_lock_test .. where a = ? ,然后该事务内又有insert into t_lock_test values(...) InnoDB中的锁算法是Ne

  • 浅谈MySQL存储过程中declare和set定义变量的区别

    在存储过程中常看到declare定义的变量和@set定义的变量.简单的来说,declare定义的类似是局部变量,@set定义的类似全局变量. 1.declare定义的变量类似java类中的局部变量,仅在类中生效.即只在存储过程中的begin和end之间生效. 2.@set定义的变量,叫做会话变量,也叫用户定义变量,在整个会话中都起作用(比如某个应用的一个连接过程中),即这个变量可以在被调用的存储过程或者代码之间共享数据.如何理解呢?可以看下面这个简单例子,很好理解. (1)先执行下面脚本,创建一

  • 浅谈Mysql主键索引与非主键索引区别

    目录 什么是索引 主键索引和普通索引的区别 索引具体采用的哪种数据结构 InnoDB使用的B+ Tree的索引模型,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点? B+ Tree的叶子节点都可以存哪些东西? 聚簇索引和非聚簇索引,在查询数据的时候有区别? Index Condition Pushdown(索引下推) 查询优化器 关于索引的题 什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据结构,索引的目的在于提高查询效率,类比字典:实际上

  • 浅谈MySQL中的自增主键用完了怎么办

    在面试中,大家应该经历过如下场景 面试官:"用过mysql吧,你们是用自增主键还是UUID?" 你:"用的是自增主键" 面试官:"为什么是自增主键?" 你:"因为采用自增主键,数据在物理结构上是顺序存储,性能最好,blabla-" 面试官:"那自增主键达到最大值了,用完了怎么办?" 你:"what,没复习啊!!"    (然后,你就可以回去等通知了!) 这个问题是一个粉丝给我提的,我觉得

  • 浅谈MYSQL主键约束和唯一约束的区别

    目录 主键约束 唯一约束 主键约束 PRIMARY KRY 主键 是唯一的 一张表只能有一个主键 AUTO_INCREMENT 一定要和主键连用 主键一定是非空的 NOT NULL 特点:不能为空,不重复 ##没有约束 Create table stu0( Id int Name varcahr(50) ) Insert into stu0(name)value("张三丰"): ##方式一:创建表,并且添加主键约束 Create table stu1( Id int primary k

  • 浅谈MySQL和Lucene索引的对比分析

    MySQL和Lucene都可以对数据构建索引并通过索引查询数据,一个是关系型数据库,一个是构建搜索引擎(Solr.ElasticSearch)的核心类库.两者的索引(index)有什么区别呢?以前写过一篇<Solr与MySQL查询性能对比>,只是简单的对比了下查询性能,对于内部原理却没有解释,本文简单分析下两者的索引区别. MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式. M

  • 浅谈mysql的索引设计原则以及常见索引的区别

    索引定义:是一个单独的,存储在磁盘上的数据库结构,其包含着对数据表里所有记录的引用指针. 数据库索引的设计原则: 为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引. 那么索引设计原则又是怎样的? 1.选择唯一性索引 唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录. 例如,学生表中学号是具有唯一性的字段.为该字段建立唯一性索引可以很快的确定某个学生的信息. 如果使用姓名的话,可能存在同名现象,从而降低查询速度. 2.为经常需要排序.分组和联合操

  • 浅谈Mysql哪些字段适合建立索引

    1 数据库建立索引常用的规则如下: 1.表的主键.外键必须有索引: 2.数据量超过300的表应该有索引: 3.经常与其他表进行连接的表,在连接字段上应该建立索引: 4.经常出现在Where子句中的字段,特别是大表的字段,应该建立索引: 5.索引应该建在选择性高的字段上: 6.索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引: 7.复合索引的建立需要进行仔细分析:尽量考虑用单字段索引代替: A.正确选择复合索引中的主列字段,一般是选择性较好的字段: B .复合索引的几个字段是否经常同

  • 浅谈mysql增加索引不生效的几种情况

    增加索引可以提高查询效率. 增加索引就是增加一个索引文件,存放的是数据的地址,类似与我们文档的目录,在查找过程中可以不用从书的内容查找,直接根据目录对应的页码查找.索引是根据地址查找. 创建索引,索引使用的数据结构也有很多种.常见的是B-tree,哈希等.mysql默认使用的数据库索引是innerDB,innerDB的索引结构是B-tree. 但是在使用过程中哪些情况增加索引无法达到预期的效果呢?下面列举几种常见情况: 假设name age address 都已经加了索引.索引名字分别为 ind

  • 浅谈mysql哪些情况会导致索引失效

    下面有一些培训教学机构的口诀和我个人的一些总结: 为了讲解以下索引内容,我们先建立一个临时的表 test02 CREATE TABLE `sys_user` ( `id` varchar(64) NOT NULL COMMENT '主键', `name` varchar(64) DEFAULT NULL COMMENT '名字', `age` int(64) DEFAULT NULL COMMENT '年龄', `pos` varchar(64) DEFAULT NULL COMMENT '职位

  • 浅谈MySql整型索引和字符串索引失效或隐式转换问题

    目录 问题概述 问题重现 问题引申 结论 问题概述 今天在上班时,DBA突然找出来一段sql,表示该sql存在隐式转换,不走索引.经过我们的查看后,发现是类型varchar的字段, 我们使用条件传入了数值型的值,由于担心违反保密协议,在此就不贴图了,由我重现一下类似情况给大家看一下. 问题重现 首先我们先创建一张用户表test_user,其中USER_ID为了效果我们设置为varchar类型且加上唯一索引. CREATE TABLE test_user ( ID int(11) NOT NULL

随机推荐