精华内容
下载资源
问答
  • MySQL索引之主键索引

    2020-12-15 16:43:01
    在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别? 上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。 1、主键索引 主键索引,简称主键,原文是PRIMARY KEY,...
  • mysql主键索引、非主键索引、联合索引、覆盖索引、最左匹配、索引下推

    索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。

    索引的常见模型

    实现索引的方式却有很多种,所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多,这里我先给你介绍三种常见、也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树。

    哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的键即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。

    不可避免地,多个 key 值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表(java中的hashmap也是这个做法)。

    缺点是区间查询速度很慢,需要全部扫描一遍。所以哈希表这种结构适用于只有等值查询的场景,比如 Memcached 及其他一些 NoSQL 引擎。


    而有序数组在等值查询和范围查询场景中的性能就都非常优秀。用二分法就可以快速找到某个元素,这个时间复杂度是 O(log(N))。同时很显然,这个索引结构支持范围查询。

    如果仅仅看查询效率,有序数组就是最好的数据结构了。但是,在需要更新数据的时候就麻烦了,你往中间插入一个记录就必须得挪动后面所有的记录,成本太高。

    所以,有序数组索引只适用于静态存储引擎,比如你要保存的是 2017 年某个城市的所有人口信息,这类不会再修改的数据。


    二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。这个搜索时间复杂度是 O(log(N))。

    当然为了维持 O(log(N)) 的查询复杂度,你就需要保持这棵树是平衡二叉树。为了做这个保证,更新的时间复杂度也是 O(log(N))。

    树可以有二叉,也可以有多叉。多叉树就是每个节点有多个儿子,儿子之间的大小保证从左到右递增。

    你可以想象一下一棵 100 万节点的平衡二叉树,树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代,从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说,对于一个 100 万行的表,如果使用二叉树来存储,单独访问一个行可能需要 20 个 10 ms 的时间,这个查询可真够慢的。

    为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N 叉”树。这里,“N 叉”树中的“N”取决于数据块的大小。

    以 InnoDB 的一个整数字段索引为例,这个 N 差不多是 1200。这棵树高是 4 的时候,就可以存 1200 的 3 次方个值,这已经 17 亿了。考虑到树根的数据块总是在内存中的,一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。其实,树的第二层也有很大概率在内存中,那么访问磁盘的平均次数就更少了。

    N 叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。

    InnoDB 的索引模型

    在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的。

    每一个索引在 InnoDB 里面对应一棵 B+ 树。

    假设,我们有一个主键列为 ID 的表,表中有字段 k,并且在 k 上有索引。

    create table T(
        id int primary key, 
        k int not null, 
        name varchar(16),
        index (k))engine=InnoDB;
    

    表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6),两棵树的示例示意图如下。

    在这里插入图片描述

    InnoDB中索引类型分为主键索引非主键索引

    主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。

    非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。

    基于主键索引和普通索引的查询有什么区别?

    • 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;
    • 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表

    也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

    主键索引的存储并不是物理上连续的,而是逻辑上连续的。

    索引维护

    B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。以上面这个图为例,如果插入新的行 ID 值为 700,则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。

    而更糟的情况是,如果 R5 所在的数据页已经满了,根据 B+ 树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。

    除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约 50%。

    当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

    你可能在一些建表规范里面见到过类似的描述,要求建表语句里一定要有自增主键。当然事无绝对,我们来分析一下哪些场景下应该使用自增主键,而哪些场景下不应该。

    自增主键是指自增列上定义的主键,插入新记录的时候可以不指定 ID 的值,系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

    也就是说,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。

    而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。

    除了考虑性能外,我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段,比如字符串类型的身份证号,那应该用身份证号做主键,还是用自增字段做主键呢?

    由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约 20 个字节,而如果用整型做主键,则只要 4 个字节,如果是长整型(bigint)则是 8 个字节。

    显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小,一个数据页内能存储的信息就更多(减少了IO次数)。




    在下面这个表 T 中,如果我执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?

    mysql> create table T (
    ID int primary key,
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '',
    index k(k))
    engine=InnoDB;
    
    insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
    

    在这里插入图片描述

    现在,我们一起来看看这条 SQL 查询语句的执行流程:

    1. 在 k 索引树上找到 k=3 的记录,取得 ID = 300;
    2. 再到 ID 索引树查到 ID=300 对应的 R3;
    3. 在 k 索引树取下一个值 k=5,取得 ID=500;
    4. 再回到 ID 索引树查到 ID=500 对应的 R4;
    5. 在 k 索引树取下一个值 k=6,不满足条件,循环结束。

    在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。

    那么,有没有可能经过索引优化,避免回表过程呢?

    覆盖索引

    如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引

    由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

    基于上面覆盖索引的说明,我们来讨论一个问题:在一个市民信息表上,是否有必要将身份证号和名字建立联合索引?假设这个市民表的定义是这样的:

    CREATE TABLE `tuser` (
      `id` int(11) NOT NULL,
      `id_card` varchar(32) DEFAULT NULL,
      `name` varchar(32) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `ismale` tinyint(1) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `id_card` (`id_card`),
      KEY `name_age` (`name`,`age`)
    ) ENGINE=InnoDB
    

    如果有根据身份证号查询市民信息的需求,我们只要在身份证号字段上建立索引就够了。而再建立一个(身份证号、姓名)的联合索引,是不是浪费空间?

    如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。

    索引字段的维护总是有代价的。因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

    最左前缀原则

    看到这里你一定有一个疑问,如果为每一种查询都设计一个索引,索引是不是太多了。

    如果我现在要按照市民的身份证号去查他的家庭地址呢?虽然这个查询需求在业务中出现的概率不高,但总不能让它走全表扫描吧?反过来说,单独为一个不频繁的请求创建一个(身份证号,地址)的索引又感觉有点浪费。应该怎么做呢?

    这里,我先和你说结论吧。B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。

    我们用(name,age)这个联合索引来分析。

    在这里插入图片描述

    可以看到,索引项是按照索引定义里面出现的字段顺序排序的

    当你的逻辑需求是查到所有名字是“张三”的人时,可以快速定位到 ID4,然后向后遍历得到所有需要的结果。

    如果你要查的是所有名字第一个字是“张”的人,你的 SQL 语句的条件是"where name like ‘张 %’"。这时,你也能够用上这个索引,查找到第一个符合条件的记录是 ID3,然后向后遍历,直到不满足条件为止。

    可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。

    基于上面对最左前缀索引的说明,我们来讨论一个问题:在建立联合索引的时候,如何安排索引内的字段顺序。

    这里我们的评估标准是,索引的复用能力。因为可以支持最左前缀,所以当已经有了 (a,b) 这个联合索引后,一般就不需要单独在 a 上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

    所以现在你知道了,这段开头的问题里,我们要为高频请求创建 (身份证号,姓名)这个联合索引,并用这个索引支持“根据身份证号查询地址”的需求。

    那么,如果既有联合查询,又有基于 a、b 各自的查询呢?查询条件里面只有 b 的语句,是无法使用 (a,b) 这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护 (a,b)、(b) 这两个索引。

    这时候,我们要考虑的原则就是空间了。比如上面这个市民表的情况,name 字段是比 age 字段大的 ,那我就建议你创建一个(name,age) 的联合索引和一个 (age) 的单字段索引。

    索引下推

    我们还是以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。那么,SQL 语句是这么写的:

    mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
    

    你已经知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录 ID3。当然,这还不错,总比全表扫描要好。

    然后呢?

    当然是判断其他条件是否满足。

    在 MySQL 5.6 之前,只能从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。

    MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

    图 A 和图 B,是这两个过程的执行流程图。

    在这里插入图片描述

    (图A,无索引下推执行流程)

    在这里插入图片描述

    (图B,索引下推执行流程)

    在图 A 和 B 这两个图里面,每一个虚线箭头表示回表一次。

    图 A 中,在 (name,age) 索引里面我特意去掉了 age 的值,这个过程 InnoDB 并不会去看 age 的值,只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此,需要回表 4 次。

    图 B 跟图 A 的区别是,InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。

    Q&A

    1. “N叉树”的N值在MySQL中是可以被人工调整的么?曾经面试被问到过这问题,当时就懵逼了…

      面试中题面越简单的问题越暗藏凶险,可见一斑…
      
      可以按照调整key的大小的思路来说;
      
      如果你能指出来5.6以后可以通过page大小来间接控制应该能加分吧
      
      面试回答不能太精减,计算方法、前缀索引什么的一起上😄
      
    2. 遇到面试官问我innodb B+树主键索引的叶子节点存的是什么,我说是行,面试官说错,存的是页。

       B+树的叶子节点是page (页),一个页里面可以存多个行
      
    3. 但现在一般自增索引都设置为bigint?

      特别合理,因为现在很多业务插入数据很凶残,容易超过int 上限,
      
      实际上是建议设置bigint unsigned
      
    4. 非聚集索引上为啥叶子节点的value为什么不是地址,这样可以直接定位到整条数据,而不用再次对整棵树进行查询?

      这个叫作“堆组织表”,MyISAM就是这样的,各有利弊。你想一下如果修改了数据的位置的情况,InnoDB这种模式是不是就方便些
      
    5. 现在只是讲到了单表主键建索引的,分库的时候不能用到自增主键,这个索引怎么处理呢?

      分库分表可以用自增主键的,有的公司是用步长来控制
      
    6. 当table T出现以下数据时: (300, 3) (400, 3)时,通个非主键索引3查到对应ID 有两个300和400,那是否要回表两次?

      是的
      
    7. 一张表里是否不建议创建多个unique key 唯一索引?

    没有这个约束,但是要评估一下合理性,要注意数据量大的时候,多个唯一索引插入速度很慢
    
    1. mysql的索引并不是越多越好?

      肯定不是越多越好了😅,还是要维护代价的
      
    2. 对于上面例子中的 InnoDB 表 T,如果你要重建索引 k,你的两个 SQL 语句可以这么写:alter table T drop index k;alter table T add index(k);,如果你要重建主键索引,也可以这么写:alter table T drop primary key;alter table T add primary key(id);,我的问题是,对于上面这两个重建索引的作法,说出你的理解。如果有不合适的,为什么,更好的方法是什么?

      索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。
      重建索引 k 的做法是合理的,可以达到省空间的目的。但是,重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,你可以用这个语句代替 : alter table T engine=InnoDB。在专栏的第 12 篇文章《为什么表数据删掉一半,表文件大小不变?》中,我会和你分析这条语句的执行流程。
      
    3. 今天这个 alter table T engine=InnoDB 让我想到了我们线上的一个表, 记录日志用的, 会定期删除过早之前的数据. 最后这个表实际内容的大小才10G, 而他的索引却有30G. 在阿里云控制面板上看,就是占了40G空间. 这可花的是真金白银啊.
      后来了解到是 InnoDB 这种引擎导致的,虽然删除了表的部分记录,但是它的索引还在, 并未释放.
      只能是重新建表才能重建索引.如果当时看到了这个专栏,把这个语句拿来用,就可以省下不少钱了.

    4. 有些资料提到,在不影响排序结果的情况下,在取出主键后,回表之前,会在对所有获取到的主键排序,请问是否存在这种情况?

      有的, Multi-Range Read (MRR) 由于不论是否使用这个策略,SQL语句写法不变,就没有在正文中提
      
    5. 下面两条语句有什么区别,为什么都提倡使用2:
      1.select * from T where k in(1,2,3,4,5)
      2.select * from T where k between 1 and 5

      第一个要树搜素5次
      第二个搜索一次
      
    6. 面试官问:说下怎么让mysql的myisam引擎支持事务,网上搜了下,也没有结果!

       ……… 面试官是魔鬼吗😄
      
      我怀疑他是想说用lock table 来实现,但是这样只能实现串行化隔离级别,
      
      其它隔离都实现不了。
      
      但是因为mysiam不支持崩溃恢复,所以即使用lock table硬实现,也是问题多多:
      
      ACID里面, 原子性和持久性做不到;
      隔离性只能实现基本用不上的串行化;
      一致性在正常运行的时候依赖于串行化,在异常崩溃的时候也不能保证。
      
      这样实现的事务不要也罢。
      
      你这么答复面试官,应该能加到分吧😄
      
    7. 以前看过某些文章上面写:如果查询顺序和联合索引的顺序不一致,优化器会自动做优化,是这样的吗?

      是的,查询语句的where里面各个判断调换顺序没关系的
      
    8. 针对索引下推有两个问题请教您
      1.之前一般认为range查询比如"a > 5 and b = ‘123’"在联合索引(a,b)中b是不起作用的,在ICP下是不是意味着b就可以起到作用了,我们还是应该尽量将查询中用到的字段放入联合索引中。
      2.针对1的问题,“a > 5 and a < 10 and b=‘123’”在ICP作用下的执行过程是什么样子的?

      1. 是的
      2. 流程是这样的:
         a) 把 a>5 and b='123'传入引擎,
         b)引擎找到第一个a>5的行(这里是快速定位),如果发现b<>'123',找下一个,直到满足b='123',
        c)把找到的行返回给server层, server层根据a是否小于10决定要不要取下一个
      
    9. 我想问一下如果一张表有ab两个单独的索引字段,在一条查询语句中where a=”” and b=””,优化器阶段会选择哪个索引,还是两个索引都走?

      有三种可能,
      1.只选a, 然后用b过滤
      2. 只选b ,然后用a过滤
      3.选a和b,然后两个单独跑出来的结果取交集
      
      优化器选择的策略是数据分布
      
    10. alter table T engine=InnoDB 重建索引是否会产生行锁?

      5.6版本以后支持onlineddl,
      
      行锁不会,有mdl读锁
      
    11. 一次查询为啥不能只回表一次呢?比如范围查询通过二级索引定位到全部符合条件的主键在回表一次?

      这样还需要地方把”满足条件的所有主键值“存起来
      就要用到临时表了,性能反而更差
      
    12. 联合索引c,b,where条件里为c,order by b,这样的语句,联合索引把c,b都用到嘛?

      会的,前提是c必须是=,不能是大于或者小于
      
    13. 您好,在不影响排序结果的情况下,在取出主键后,回表之前,会在对所有获取到的主键排序,请问是否存在这种情况?
      不是取出一个主键回表一次吗,什么情况下一次取出多个主键再回表?

      存在,在MySQL 里面叫做“MRR优化”
      
      这个是自动的,代码版本支持就会这么做
      
    14. 一张表两个字段id, uname,id主键,uname普通索引
      SELECT * FROM test_like WHERE uname LIKE ‘j’/ ‘j%’ / ‘%j’/ ‘%j%’
      模糊查询like后面四种写法都可以用到uname的普通索引

      添加一个age字段
      like后面的’%j’/ ‘%j%’ 这两种情况用不到索引
      把select * 改为 select id / select uname / select id,uname
      like后面’j’/ ‘j%’ / ‘%j’/ ‘%j%’ 这四种情况又都可以用到uname普通索引

      建立uname,age的联合索引
      模糊查询还是 LIKE ‘j’/ ‘j%’ / ‘%j’/ '%j%'四种情况
      其中select id / select uname / select id,uname
      会用到uname的普通索引
      select * 会用到uname,age的组合索引

      看到好些文章会说模糊查询时以%开头不会用到索引,实践后发现结论跟文章描述的有出入。
      看了索引的这两节内容对上面的各种情况有的可以解释通了,有的仍然有些模糊,想问下上面这些情况使用索引时为什么是这样的?

      1. like 'j' 或 'j%' 可以使用索引,并且快速定位记录。
      2. like '%j' 或 '%j%',只是在二级索引树上遍历查找记录,并不能快速定位(扫描了整棵索引树)。
      3. 只有 id 和 uname 字段时,上述 4 种 like 查询,uname 索引能满足 id 和 uname 的查询情况,不需要回表,所以选择了使用 uname 的索引树解决问题。
      4. 添加了 age 但无联合索引 (uname, age) 的情况,如果使用 uname 索引树,需要回表。在 like '%j' 或 '%j%' 直接扫描主键索引树,现象就是没有使用 uname 索引。
      5. 添加了 age 字段,也添加了 (uname, age) 索引,和第 3 点同理,使用覆盖索引就能满足 select * 的字段查询,不需要回表,因此使用了 (uname, age) 索引树。但是只有 like 'j' 和 'j%'
      能快速定位记录,而 like '%j' 和 '%j%' 也能使用该索引树,但是不能快速定位,需要顺序遍历。
      
    展开全文
  • Mysql主键索引与非主键索引区别

    千次阅读 2020-05-05 01:18:05
    什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的...1.主键索引索引着数据,然而普通索引索引着主键ID值(这是在innodb中,但是如果是myisam中,主键索引和普通索引是没有区别的都是直接索...

    什么是索引

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

    展开全文
  • 主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。 在MySQL中,InnoDB数据表的主键...
  • mysql主键索引与非主键索引区别

    千次阅读 2019-03-06 18:19:54
    主键是逻辑键,索引是物理键。主键不实际存在,而索引实际存在于数据库中。 索引会真正产生文件。数据会真正产生文件。 redo log 记录的内容:物理日志,"某个数据页上做了什么修改" ,循环使用。 bin log...

    一、前言

    主键是逻辑键,索引是物理键。主键不实际存在,而索引实际存在于数据库中。
    索引会真正产生文件。数据会真正产生文件。
    redo log 记录的内容:物理日志,"某个数据页上做了什么修改" ,循环使用。
    bin log 记录的内容:逻辑日志,语句的原始逻辑"ID=1 ,2 " ,追加使用。
    主键不会产生文件,主键的搜索依靠ID索引树。

    二、数据的概念

    数据有数据页的概念,mysql也有内存的概念。mysql查询的时候,先检测所在的数据页是否在内存中,如果存在,查询直接返回;如果不存在,则从磁盘加载到内存,再返回结果。

    对于更新操作,如果数据在内存中不存在,则先从磁盘加载到内存,cpu修改后,再将值放入内存,并且再更新到redo log 日志表中。

    三、索引的原理

    1.B+树索引

     B+树所有的关键字都出现在叶子节点的链表(稠密索引)中,且链表中的关键字是有序的。非叶子节点只起索引作用(稀疏索引)。
    如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+树。
    如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,ID 的值为 500,再到 ID 索引树搜索一次。这个过程称回表
    也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,在应用中应该尽量使用主键查询。

    2. 一个数据页满了,按照B+Tree算法,新增加一个数据页,叫做页分裂,会导致性能下降。空间利用率降低大概50%。
    当相邻的两个数据页利用率很低的时候,系统会做数据页合并,合并的过程是分裂过程的逆过程。

    3.从性能和存储空间方面考量,自增主键往往是更合理的选择。

    4.显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
    因此自增主键比较合适。

    四、覆盖索引

    create table T 
    ( ID int primary key, 
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '', 
    index k(k))
    engine=InnoDB;
    
    insert into T values(100,1, 'aa'),
    (200,2,'bb'),
    (300,3,'cc'),
    (500,5,'ee'),
    (600,6,'ff'),
    (700,7,'gg');
    
    select * from T where k between 3 and 5 

     这种查询K的索引,首先搜索到主键,然后搜索主键的索引,回表拿到具体的信息。
    select ID from T where k between 3 and 5 这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果不需要回表。由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

    五、最左前缀原则

    索引为(a,b), 查询条件b无法使用联合索引,但是查询条件a可以使用索引。
    %%开头是不会使用索引的。

    六、关于索引的题

    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保留。

    展开全文
  • 主键索引和非主键索引

    千次阅读 2019-09-07 22:38:40
    主键索引主键索引的区别是:非主键索引的叶子节点存放的是主键的值,主键索引的叶子节点存放的是整行数据。其中,非主键索引又称为二级索引,主键索引又称为聚簇索引。 根据这两种结构我们来进行下查询,看看...

    对于一张数据表,ID为主键
    在这里插入图片描述

    在这里插入图片描述
    非主键索引和主键索引的区别是:非主键索引的叶子节点存放的是主键的值,主键索引的叶子节点存放的是整行数据。其中,非主键索引又称为二级索引,主键索引又称为聚簇索引。

    根据这两种结构我们来进行下查询,看看他们在查询上有什么区别。
    1、如果查询语句是 select * from table where ID = 100,如果是主键索引则只需要搜索ID这颗B+树。
    2.如果查询语句是 select * from table where k = 1,即非主键的查询方式,要先搜索K索引树,然后再对ID索引树搜索一遍。

    参考:
    https://www.cnblogs.com/heishuichenzhou/p/10813463.html

    展开全文
  • 文章目录MySQL学习笔记-主键索引和二级索引1.笔记图2.索引的常见模型3.InnoDB 的索引模型4.主键索引和普通索引查询的区别5.如何选择主键字段6.笔记图7.回表过程8.最左前缀原则 MySQL学习笔记-主键索引和二级索引 在 ...
  • 一、 创建主键(主键=主键索引=聚集索引) 主键是什么? 答:拿主键可以唯一确定一条数据,它和物理存储排序一致,不能为空,一个表只能有一个。 原本没有创建的主键的表在磁盘上存储为: Id=0;username=username0;sex...
  • MySQL重新建立主键索引和非主键索引的方法 重新建立主键索引方法 这是错误的写法 alter table T drop primary key; alter table T add primary key(id); mysql官方文档写了三种措施: 整个数据库迁移,先dump出来...
  • 一般是在建表的时候指定了主键,就会创建主键索引, CREATE INDEX不能用来创建主键索引,使用 ALTER TABLE来代替。 如何使用? 方式1:ALTER TABLE table_name ADD PRIMARY KEY ( column ) 比如:ALTER TABLE users ...
  • 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。 索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。 ...
  • 唯一索引和主键索引区别: 一个表只能有一个主键索引,但是可以有多个唯一索引 主键索引一定是唯一索引,唯一索引则不一定是主键索引 主键索引可以与外键构成完整性约束,唯一索引不可以 唯一性索引列允许空值,而...
  • 主键索引和普通索引的区别

    万次阅读 2019-08-07 17:46:28
    主键索引和普通索引的区别 主键索引的叶子结点存放了整行记录,普通索引的叶子结点存放了主键ID,查询的时候需要做一次回表查询 一定要回表查询么? 不一定,当查询的字段刚好是索引的字段或者索引的一部分,就可以...
  • 主键索引主键索引不可以为空 主键索引可以做外键 一张表中只能有一个主键索引 普通索引: 用来加速数据访问速度而建立的索引。多建立在经常出现在查询条件的字段和经常用于排序的字段。 被索引的数据列允许...
  • 主键和复合主键 一个表中可以存在一个主键或者多个主键,都...主键索引和唯一索引 相同点:都起到了约束数据记录唯一性的作用 不同点: 主键是一种约束,而唯一索引是索引,他们本质不一样 主键创建之后一定包含着...
  • 主键索引和普通索引有什么区别?

    千次阅读 2020-01-17 09:57:19
      在 MySQL 中, 索引是在存储...在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, InnoDB 使⽤了 B+ 树索引模型,所以数据都是存储在 B+ 树中的, 如图所示: 从图中可以看出, 根据叶子节点内容不同,索引类...
  • 唯一索引与主键索引的比较

    千次阅读 2019-02-20 23:02:51
    唯一索引不允许两行具有相同的索引值。 如果现有数据中存在重复的键值,则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时,数据库也拒绝接受此数据。例如,如果在 employee 表...
  • 主键索引 主键索引就是聚簇索引,每张表有且只能有一个主键,可以由一个或多个字段组成。 主键索引的三个条件:1.主键键值必须是唯一2.不能含有null值3.保证主键是自增的 自增列作主键可以使写入的顺序也是自增的,...
  • mysql 主键索引,组合索引,单列索引使用场景 CREATE TABLE button ( id bigint(20) NOT NULL AUTO_INCREMENT, --主键索引 button_name varchar(45) NOT NULL COMMENT ‘功能名称’, app_id bigint(20) NOT NULL, ...
  • 今天给大家介绍mysql InnoDB 下3种常用的索引:主键索引(聚集索引)、非聚集索引、覆盖索引 下面已一张表具体的表为例:来演示数据查找的过程。 譬如:一张用户表 User表 Id是主键 主键索引,也被称为”聚集...
  • 各种树的应用场景 AVL树: 最早的平衡二叉树之一。...B/B+树: 用在磁盘文件组织 数据索引和数据库索引。 Trie树(字典树): 用在统计和排序大量字符串,如自动机。 索引分类 1, 全文索引 2, 哈希索引 3, b+树索引 ...
  • 一、MYSQL索引的分类 索引用于快速查找具有特定列值的行。如果没有索引,MySQL必须从第一行开始,然后读取整个表以查找...MYSQL的索引主要分为主键索引(PRIMARY KEY),唯一索引(UNIQUE),普通索引(INDEX)和全文索...
  • 作者 | 吴海存责编| 徐威龙封图|CSDN下载于视觉中国导读:本文主要针对limit分页时,是优先基于主键索引还是辅助索引等层面展开分析,对limit及offset的用法以及是否...
  • 主键索引也叫聚簇索引,非主键索引也是二级索引。 主键索引存储的是主键id和全部数据,二级索引储存的是索引值和主键值,当我们查询的字段不在索引储存的数据中,就会进行回表,即通过普通索引找到主键值,再通过主...
  • 1、概念 1)、查看索引 show index from 数据库表名 2)、alter table 数据库表...1)、PRIMARY KEY(主键索引) ALTER TABLE table_name ADD PRIMARY KEY ( column ) 2)、UNIQUE(唯一索引) ALTER TABLE table...
  • 普通索引 最基本的索引,没有任何限制 mysql&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) -- 在索引上添加mul mysql&amp;amp;amp;amp;amp;...
  • 联合主键索引问题

    千次阅读 2019-08-05 14:59:27
    字段a、b、c作为联合主键,每个主键字段在什么情况下可以使用索引? 测试环境 mysql8.0.15 InnoDB引擎 测试过程 建立个user表,具体信息如下: user表信息如下: 上表中对id,age,sum这个三个字段设定...
  • 索引总结 索引为什么提高了查询的速率,但是update delete insert 的速度会变慢 因为:这三种操作会破坏二叉树的,所以会造成速度变慢 什么样的索引使用什么样的方法进行查询 对于模糊查询 1 使用全文索引的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 253,996
精华内容 101,598
关键字:

主键索引