精华内容
下载资源
问答
  • mysql设置联合索引
    千次阅读
    2021-01-19 04:27:11

    mysql之联合索引测试:

    前期准备:

    建立联合索引?

    CREATE TABLE `test` (

    `id` bigint(16) NOT NULL AUTO_INCREMENT,

    `aaa` varchar(16) NOT NULL,

    `bbb` varchar(16) NOT NULL,

    `ccc` int(11) NOT NULL,

    PRIMARY KEY (`id`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    如果表已经建好了,那么修改表:

    create index `sindex` on `test` (`aaa`,`bbb`,`ccc`);

    这种方式报错

    alert table test add INDEX `sindex` (`aaa`,`bbb`,`ccc`)

    修改表的存储引擎:

    ALTER TABLE tablename ENGINE = MyISAM;

    ALTER TABLE tablename ENGINE = INNODB;

    Explain:

    其中最重要的字段为:id、type、key、rows、Extra

    id:

    1、id相同:执行顺序由上至下

    2、id不同:如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

    3、id相同又不同(两种情况同时存在):id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

    type:

    访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    key:

    查询中如果使用了覆盖索引,则该索引仅出现在key列表中

    rows:

    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

    Extra:

    1、Using filesort :

    mysql对数据使用一个外部的索引排序,而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”

    2、Using temporary:

    使用临时表保存中间结果,也就是说mysql在对查询结果排序时使用了临时表,常见于order by 和 group by

    3、Using index:

    表示相应的select操作中使用了覆盖索引(Covering Index),避免了访问表的数据行,效率高

    如果同时出现Using where,表明索引被用来执行索引键值的查找(参考上图)

    如果没用同时出现Using where,表明索引用来读取数据而非执行查找动作

    覆盖索引(Covering Index):也叫索引覆盖。就是select列表中的字段,只用从索引中就能获取,不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

    Explain:

    下方的控制台主要关注两个栏,type和extra

    当extra出现道Using filesort和Using temproary这两个时,表示无法使用索引版,必须尽快做优化。

    当type出现all时,表示走的是全表扫描没有走索引,效率低下,这时需要对sql进行调优。

    当type出现ref或者index时,表示走的是索引,index是标准不重复的索引,ref表示虽然使用了索引,但是索引列中有重复的值,但是就算有权重复值,也只是在重复值的

    范围内小范围扫描,不造成重大的性能影响。

    测试语句是否使用了索引:

    网上说联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。但是

    我蒙蔽了,在我实际的测试中,aaa bbb ccc 这三个条件不管删除那个,怎么组合where条件查询 type:index extral:Using where; Using index 难道说都用到索引了嘛?

    sql1:explain select * from test where aaa=1 and bbb=1;

    用到了索引

    sql2:explain select * from test where bbb=1 and aaa=1;

    联合索引设置

    aaa bbb

    bbb aaa

    bbb

    更多相关内容
  • 主要介绍了MySQL联合索引用法,结合实例形式分析了MySQL联合索引的具体定义与使用方法,需要的朋友可以参考下
  • 主要介绍了Mysql联合索引最左匹配原则,使用联合索引的好处多多,具体内容详情大家跟随脚本之家小编一起学习吧
  • MySQL索引之联合索引

    千次阅读 2021-09-11 22:34:10
    目录联合索引联合索引的存储结构建表 联合索引 在平时开发中,我们最常见的是聚集索引,但在我们需要多条件查询的时候,就不得不建立联合索引,来提高我们的查询效率 联合索引:也称复合索引,就是建立在多个字段上...

    1. 联合索引

    在平时开发中,我们最常见的是聚集索引,但在我们需要多条件查询的时候,就不得不建立联合索引,来提高我们的查询效率

    • 联合索引:也称复合索引,就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree
    • 一颗 B+ Tree 只能根据一个值来构建,所以联合索引使用 最左 的字段来构建 B+ Tree

    1.1. 联合索引的存储结构

    如下图所示,表的数据如右图,ID 为主键,创建的联合索引为 (A,B),注意联合索引顺序,左图是模拟的联合索引的 B+ Tree 存储结构
    在这里插入图片描述

    • 叶子节点是线性排列,并且每个节点的数据排列顺序和创建索引字段的顺序一致。如 1,11,23,1 是对应着联合索引 (A,B) 字段顺序的,可以对照右图看
    • 非叶子节点存储完整的索引关键字信息,排列规则和叶子节点一致。如 2,84,3 是对应着联合索引 (A,B) 字段顺序的
    • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件,同样联合索引 (A,B) 也会生成一个 B+ Tree ,只不过联合索引 B+ Treedata 部分存储的是联合索引所在行的主键值。如 01,02,102,09 等它们是联合索引所在行的主键值
    • 根据图中叶子节点的数据可以看出,所有的数据都是按照列 A 进行排序的 1,1,1,2,3,3,4,4B 列的顺序为 1,2,2,1,1,5,1,5B 列是 全局 无序的。如果使用 B = 1 这种查询条件是没有办法使用到索引的,因为联合索引首先是按 A 排序的(使用 最左 的字段来构建 B+ Tree),B 是无序的
    • 我们还可以发现在 A 值相等的情况下,B 值又是按顺序排列的,但是这种顺序是局部的。如 1,11,21,23,13,5。所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。例如 A = 1 and B = 2A,B 字段都可以使用索引,因为在 A 值确定的情况下 B 是相对有序的,而 A > 1 and B = 2A 字段可以匹配上索引,但 B 值不可以,因为 A 的值是一个范围,在这个范围中 B 是无序的

    1.2. 联合索引的查询流程

    • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件,同样联合索引 (A,B) 也会生成一个 B+ Tree ,只不过联合索引 B+ Treedata 部分存储的是联合索引所在行的主键值
    • 拿到联合索引所在行的主键值后,在通过主键索引 B+ Tree 就可以直接拿到具体的行数据了

    1.3. 最左前缀匹配原则

    最左优先,以最左边的为起点任何 连续 的索引都能匹配上,但遇到范围查询 (>、<、between、like) 就会停止匹配。之所以会有 最左前缀匹配原则和联合索引的索引构建方式及存储结构 是有关系的

    • 联合索引是使用多列索引的第一列(最左)构建的 B+ Tree
    • 用上面 (A,B) 的例子就是使用 A 列构建的 B+ Tree ,当 A 列值相等时再以 B 列进行排序(相对有序)

    1.3.1. 最左前缀匹配原则说明

    • 如查询 A = 1,那么 A 字段肯定是可以使用索引的,因为所构建的索引 B+ Tree 是依据最左即 A 列构建的,数据自然是全局有序的
    • 如查询 A = 1 and B = 2,那么 A,B 字段都可以使用索引,因为在 A 值确定的情况下 B 是相对有序的
    • 如查询 B= 2 and A = 1,对于这种情况 A,B 字段都可以使用索引的。MySQL 查询优化器会判断纠正这条 sql 语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以 MySQL 查询优化器会最终以这种顺序进行查询执行
    • 另外如查询 B = 2,这种情况 也是会用到索引的;可观察 explain 结果中的 type 字段是一个 indexindexALL 的区别为 index 类型只遍历索引树,这通常比 ALL 快。而上述 3 种查询情况 type 字段是一个 ref。详情:https://blog.csdn.net/qq_24690761/article/details/52787897

    2. 索引下推

    现在有一个需求:检索出表中名字第一个字是张,而且没有删除的信息 (is_del = 1)

    select * from t_user where name like '张%' and is_del=1
    
    • MySQL 5.6 之前,只能从匹配的位置一个个回表。到主键索引上找出数据行,再对比字段值
    • MySQL 5.6 中引入的索引下推优化 (index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数

    2.1. 无索引下推的执行流程

    根据 (username, is_del) 联合索引查询所有满足名称以“张”开头的索引,然后回表查询出相应的全行数据,然后再筛选出未删除的用户数据。过程如下图

    每一个虚线箭头表示回表一次
    在这里插入图片描述

    2.2. 有索引下推的执行流程

    每一个虚线箭头表示回表一次
    在这里插入图片描述
    1 跟图 2 的区别是,InnoDB(username, is_del) 索引内部就判断了数据是否逻辑删除,对于逻辑删除的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID1、ID4 这两条记录回表取数据判断,就只需要回表 2

    • InnoDB 引擎的表,索引下推 只能用于联合索引,因为 InnoDB 的主键索引树叶子结点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果
    • 索引下推一般可用于所求查询字段(select 列)不是或不全是联合索引 的字段,查询条件为多条件查询且查询条件子句(whereorder by)字段全是联合索引

    参考:https://blog.csdn.net/sinat_41917109/article/details/88944290

    展开全文
  • 主要介绍了MySQL联合索引功能与用法,结合具体实例形式分析了联合索引的概念、功能、具体使用方法与相关注意事项,需要的朋友可以参考下
  • Mysql 联合索引

    2022-02-25 14:40:30
    MySQL可以使用多个字段同时建立一个索引,叫做联合索引。上文中讲到索引的底层结构就是一个二叉树,联合索引也是一样,它的非叶子节点中存的就不只是一个列,是索引的所有列,并且它的排序就是根据索引列的先后顺序来...

    联合索引底层数据结构

            MySQL可以使用多个字段同时建立一个索引,叫做联合索引。上文中讲到索引的底层结构就是一个二叉树,联合索引也是一样,它的非叶子节点中存的就不只是一个列,是索引的所有列,并且它的排序就是根据索引列的先后顺序来排的。

        例如建立了一个(‘name’,‘age’,‘position’)三个列的联合索引,那么非叶子节点中就存储了name,age,position字段,排序的时候先根据第一个字段name来排序,再按第二个字段age排序,再按position字段排序。从左往右,name为H开头的肯定排在b开头的数据后面,name相同的情况下,age=10的肯定排在age=9的后面,以此类推。
    在这里插入图片描述
    那么根据此图,我们就能很好理解联合索引的最左前缀原则了。
    以上图为例。 我创建了一个示例表:

    CREATE TABLE `test_index` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `position` varchar(255) DEFAULT NULL,
      `update_time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    并且已经创建了索引 `idx_name_age_position` (`name`,`age`,`position`) 
    

    索引长度计算方法

    计算规则
    1.索引字段,没有设置NOT NULL,需要占用一个字节。NULL在mysql中是用一个标志位来表示的,用一个字节,null也走索引,并且排在索引的最前面。并不是有些说法说的那样不走索引的
    2.定长字段:tinyiny占1个字节、int占4个字节、bitint占8个字节、date占3个字节、datetime占5个字节,char(n)占n个字符。
    3.变长字段:varchar(n)占n个字符+2个字节。
    4.不同的字符集,一个字符占用的字节数不同:

    latin1编码,每个字符占用一个字节
    gbk编码,每个字符占用两个字节
    utf8编码,每个字符占用三个字节
    utf8mb4编码,每个字符占用四个字节

    像上面的例子,我用的是utf8编码,并且没有设置不可为null。
    name索引长度: 2553+2+1=768 (null标志位占一个字节)
    age索引长度:4+1=5 (null标志位占一个字节)
    position索引长度: 255
    3+2+1=768 (null标志位占一个字节)

    联合索引示例

    .

    1. explain select * from t where name=‘Bill’ and age=40 and position=‘dev’
      在这里插入图片描述
      走索引的,并且可以看到索引长度为1541,768+768+5=1541,正好对的上,说明走了这个索引的全部字段。
      我如果把name列改为不可为Null(具体过程就不写了),再来看一下
      在这里插入图片描述
      key_len变成1540了,说明 null 确实占一个字节。这里只为说明 null 占一个字节,后续的示例都是可以为null情况的。
    2. explain SELECT * FROM test_index where name=‘Bill’ and age>30;
      在这里插入图片描述
      走索引,并且索引长度为773(768+5)说明走了索引的name,age字段
    3. explain SELECT * FROM test_index where name=‘Bill’ and age>30 and position = ‘dev’;. 在这里插入图片描述
      走索引,只走了索引的name,age字段,position字段没走。可以看到索引长度为773(768+5)
      确实走不了position字段这部分,age是一个范围查找,定位到age=30之后,可以根据索引找到>30的记录,但是position索引没法用啊,你不能说age=Bill,age=30,position=dev这条记录的数据右边都是满足条件的,age=31,position=aaa 也是在它右边,是不满足查询要求的。只能对前两个字段用索引
    4. explain SELECT * FROM test_index where name=‘Bill’ and position = ‘dev’;
      在这里插入图片描述
      走索引,只走了索引的name字段部分,position字段没走,可以看到索引长度为768
    5. explain SELECT * FROM test_index where name=‘Bill’ and position > ‘dev’;. 在这里插入图片描述
      走索引,只走了索引的name字段部分,position字段没走,可以看到索引长度为768
    6. explain SELECT * FROM test_index where age=30 and position = ‘dev’;
      在这里插入图片描述
      不走索引,因为第一列没被引用,那么要想找到 age=30 and position = 'dev’的记录,只能把索引全部扫描一遍,再通过回表去找到对应记录,那还不如全表扫描。
    7. explain SELECT * FROM test_index where name>‘Bill’ and age=30 and position = ‘dev’; 在这里插入图片描述
      走索引,只走了索引的name字段部分,age 和 position字段没走,可以看到索引长度为768
      name是范围查找,可以走name那一部分索引
    展开全文
  • MySQL单列索引和联合索引

    千次阅读 多人点赞 2022-09-02 11:11:33
    多个单列索引在多条件查询时优化器会优先选择最优索引策略,可能只用一个索引,也可能将多...但多个单列索引底层会创建多个B+索引树,比较占用空间,也会浪费一定搜索效率,故如果只有多条件联合查询时最好建联合索引

    一、简介

    利用索引中的附加列,可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。

    联合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用,如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓,电话簿将没有用处。

    所以说创建联合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,联合索引非常有用;仅对后面的任意列执行搜索时,联合索引则没有用处。

    二、单列索引

    多个单列索引在多条件查询时优化器会优先选择最优索引策略,可能只用一个索引,也可能将多个索引全用上。但多个单列索引底层会创建多个B+索引树,比较占用空间,也会浪费一定搜索效率,故如果只有多条件联合查询时最好建联合索引。

    三、最左前缀原则

    顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上,如果第一个字段是范围查询需要单独建一个索引,在创建联合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。这样的话扩展性比较好,比如username经常需要作为查询条件,而age不常使用,则需要把username放在联合索引的第一位置,即最左边。

    1、创建复合索引

    ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)
    

    2、满足复合索引的最左特性,哪怕只是部分,复合索引生效

    SELECT * FROM employee WHERE NAME='哪吒编程'
    

    3、没有出现左边的字段,则不满足最左特性,索引失效

    SELECT * FROM employee WHERE salary=5000
    

    4、复合索引全使用,按左侧顺序出现 name,salary,索引生效

    SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000
    

    5、虽然违背了最左特性,但MySQL执行SQL时会进行优化,底层进行颠倒优化

    SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'
    

    6、理由

    复合索引也称为联合索引,当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。

    联合索引不满足最左原则,索引一般会失效。

    四、同时存在联合索引和单列索引(字段有重复),这个时候查询mysql会怎么用索引呢?

    这个涉及到MySQL本身的查询优化器策略,当一个表有多条索引可走时,mysql根据查询语句的成本来选择走哪条索引;

    有人说where查询是按照从左到右的顺序,所以筛选力度大的条件尽量放在前面。网上百度过,确实有这种说法,但我亲自测试过,MySQL执行优化器会对其进行优化,当不考虑索引时,where条件顺序对效率没有影响,真正有影响的是是否用到了索引!

    五、联合索引本质

    当创建**(a, b, c)联合索引时,相当于创建了(a)单列索引,(a, b)联合索引以及(a, b, c)联合索引,想要索引生效的话,只能使用者三种组合;当然,我们上面测试过,a, c组合也可以,但实际上只用到了a的索引,c并没有用到。

    六、索引失效

    1、like子查询,%放前面;

    2、非空判断 is not null;or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效;

    3、or语句(前后都有索引才行,SQL优化要避免写or语句);

    4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。

    七、其它知识点

    1、需要加索引的字段,要在where条件中

    2、数据量少的字段不需要加索引,因为建索引有一定开销,如果数据量小则没有必要建索引,速度范围慢。

    3、联合索引比每个列建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会越慢、另外建立多列索引时,顺序也是需要注意的,应该讲严格的索引放在前面,这样筛选的力度会更大,效率更高。

    八、MySQL存储引擎简介

    1、InnoDB

    支持事务处理,支持外键,支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为支持事务的提交和回滚。

    2、MyISAM

    插入速度快,空间和内存使用比较低。如果表主要是用于插入新纪录和读取记录,那么选择MyISAM能实现处理高效率。如果应用的完整性、并发要求比较低,也可以使用。

    注意,同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理,可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表,可以选择MEMORY存储引擎。

    九、索引结构(方法、算法)

    在mysql中常用两种索引结构(算法)BTree和Hash,两种算法检索方式不一样,对查询的作用也不一样。

    1、Hash

    Hash索引的底层实现是由Hash表来实现的,非常适合以 key-value 的形式查询,也就是单个key 查询,或者说是等值查询。

    Hash 索引可以比较方便的提供等值查询的场景,由于是一次定位数据,不像BTree索引需 要从根节点到枝节点,最后才能访问到页节点这样多次IO访问,所以检索效率远高于BTree索引。但是对于范围查询的话,就需要进行全表扫描了。

    但为什么我们使用BTree比使用Hash多呢?主要Hash本身由于其特殊性,也带来了很多限制和弊端:

    1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询,不能使用范围查询。
    2. 联合索引中,Hash索引不能利用部分索引键查询。 对于联合索引中的多个列,Hash是要么全部使用,要么全部不使用,并不支持BTree支持的联合索引的最优前缀,也就是联合索引的前面一个或几个索引键进行查询时,Hash索引无法被利用。
    3. Hash索引无法避免数据的排序操作 由于Hash索引中存放的是经过Hash计算之后的Hash值,而且Hash值的大小关系并不一定和Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算。
    4. Hash索引任何时候都不能避免表扫描 Hash索引是将索引键通过Hash运算之后,将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中,由于不同索引键存在相同Hash值,所以即使满足某个Hash键值的数据的记录条数,也无法从Hash索引中直接完成查询,还是要通过访问表中的实际数据进行比较,并得到相应的结果。
    5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高 对于选择性比较低的索引键,如果创建Hash索引,那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据访问,而造成整体性能底下。

    2、B+ Tree

    B+Tree索引是最常用的mysql数据库索引算法,因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量,

    例如:

    select * from user where name like 'jack%'; select * from user where name like 'jac%k%';
    

    如果一通配符开头,或者没有使用常量,则不会使用索引,

    例如:

    select * from user where name like '%jack'; select * from user where name like simply_name;
    

    3、 B+/-Tree原理

    在数据库中,数据量相对较大,多路查找树显然更加适合数据库的应用场景,接下来我们就介绍这两类多路查找树,毕竟作为程序员,心里没点B树怎么能行呢?

    B树:B树就是B-树,他有着如下的特性:

    1. B树不同于二叉树,他们的一个节点可以存储多个关键字和多个子树指针,这就是B+树的特点;
    2. 一个m阶的B树要求除了根节点以外,所有的非叶子子节点必须要有[m/2,m]个子树;
    3. 根节点必须只能有两个子树,当然,如果只有根节点一个节点的情况存在;
    4. B树是一个查找二叉树,这点和二叉查找树很像,他都是越靠前的子树越小,并且,同一个节点内,关键字按照大小排序;
    5. B树的一个节点要求子树的个数等于关键字的个数+1;

    B+树就是B树的plus版

    1. B+树将所有的查找结果放在叶子节点中,这也就意味着查找B+树,就必须到叶子节点才能返回结果;
    2. B+树每一个节点的关键字个数和子树指针个数相同;
    3. B+树的非叶子节点的每一个关键字对应一个指针,而关键字则是子树的最大,或者最小值;

    将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

    在这里插入图片描述
    通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:

    InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为〖10〗^3)。
    也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。

    实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

    数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引,聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

    十、SQL优化思维导图

    在这里插入图片描述

    十一、MySql数据库进阶实战

    本书是作者根据以往的教学与实践进行的总结,重点介绍了 MySQL 数据库的核心原理与架构,涉及开发、运维、管理与架构等知识。存储引擎、MySQL用户管理与访问控制、管理MySQL的数据库对象、MySQL应用程序开发、MySQL的事务与锁、MySQL备份与恢复、MySQL的主从性能与主主复制、MySQL的高可用架构、MySQL优化与运维管理、MySQL数据库的监控和使用MySQL数据库的中间件。根据本书中的实战步骤进行,可以在读者实际项目的生产环境中快速应用并实施MySQL。

    基于 MySQL 8.0 版本编写,为提供完整的实例代码。适合对 MySQL 技术运载的各种平台架构师、读者维管理人员和项目开发人员阅读。无论读者是否不管接触过数据库技术,只要具备基础的Linux和SQL知识,都能够快速掌握MySQL并提升实战经验。

    在这里插入图片描述

    展开全文
  • 通过联合索引优化MYSQL慢查询 一、慢SQL分析 慢SQL如下 SELECT SUM(t.amount) as amount FROM (SELECT game_id,room_id,is_robot,amount,record_time FROM t_statements_logs WHERE game_id = 30 AND is_robot = 0 ...
  • 查询背景 有一个表tmp_test_course大概有10万条记录,然后有个json字段叫outline,存了一对多关系(保存了多个编码,例如jy1577683381775) 我们需要在这10万条数据中检索特定类型的数据,目标总数据量:2931条 ...
  • 一方面 只在a 上建索引那么是index range scan, 不像联合索引那样可以index unique scan , 我觉得速度差的不多(数据量不大的情况).另一方面,DML也会更新index,更新三个column肯定比更新一个column慢...
  • mysql联合索引详解

    千次阅读 2021-01-20 13:25:24
    联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不...
  • 在项目中,常常要用到联合唯一 在一些配置表中,一些列的组合成为一条记录。 比如,在游戏中,游戏的分区和用户id会形成一条记录。(比如,一个qq用户可以在艾欧尼亚、德玛西亚创建两个账号)
  • --联合索引查询第一个、第二个参数(走索引)explain select* from emp where ENAME = 'wang' and JOB单例索引:在数据库的单例上建立的索引。联合索引:在数据库的多列上建立的索引。两者使用区别:如果在一个大量的...
  • mysql联合索引失效

    2022-03-25 14:11:00
    联合索引,能够缩小查询范围的字段放在第一个,比如表sensor_...‘2022-02-20’ ,此时然后按照联合索引最左匹配有使用到了time,distinct_id,但是经过time查询后的数据还会是全表,mysql会认为该查询还不如走全表查询
  • 介绍MySQL联合索引和覆盖索引的联系及特点
  • mysql中如何用命令创建联合索引

    千次阅读 2021-11-05 10:33:06
    目录普通联合索引唯一联合索引普通联合索引和唯一联合索引的区别 普通联合索引 语法: create index 索引名称 on 表名(字段名称) 示例: create index firstIndex on student(id, name, address); 注意: 索引...
  • 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 ...
  • 联合索引的顺序,难道不是哪个查询条件最多用就放在前面的吗?比如商品有三个分类A,B,C,类似「界门纲目科属种」那样,越左类别越大。还有一个是商品来源D,不一定会用于查询条件中。举个例子,A代表衣服,B代表...
  • MySQL数据库联合索引的命中规则

    千次阅读 2021-07-27 16:18:46
    本文中联合索引的定义为(MySQL): ALTER TABLE table_name ADD INDEX (col1,col2,col3); 二、联合索引的本质 当创建(col1,col2,col3)联合索引时,相当于创建了(col)单列索引,(clo1,clo2)联合索引以及(col1,col2,...
  • 分析上面的sql执行的逻辑: 从联合索引里找到所有小于该审核时间的主键id(假如在该时间戳之前已经审核了100万条数据,则会在联合索引里取出对应的100万条数据的主键 id) 未来如果有一个优化就好了,目前还有:对...
  • 对于查询语句“SELECT E.* FROM E WHERE E.e1=1 AND E.e3=2”涉及到两列,这个时候我们一般采用一个联合索引(e1, e3);而不用两个单列索引,这是因为一条查询语句往往应为mysql优化器的关系只用一个索引,就算你有两...
  • 如何利用MySQL添加联合唯一索引

    千次阅读 2022-07-05 11:51:35
    mysql
  • MySQL联合索引的原理

    千次阅读 2021-05-17 15:47:31
    面试中被问到了联合索引,突然就涉及到了知识盲区,对不起,我只知道B+树,B树,哈希索引,聚簇索引,非聚簇索引,联合索引的原理?。。 对不起涉及到了我的知识盲区了。 这里对联合索引做一个总结,联合索引...
  • mysql联合索引排序的问题

    千次阅读 2019-12-30 19:04:36
    背景 在test数据库中,查询一组数据会根据id进行排序,而同样的条件在pre数据库中查询却没有根据默认的id主键来排序,于是激起了...若查询条件走联合索引,则结果会按照联合索引的从左到右的字段先后顺序排序。
  • mysql如何创建多个联合索引

    千次阅读 2021-01-18 19:09:07
    mysql创建多个联合索引的方法:可以使用CREATE INDEX或ALTER TABLE语句来创建。在创建联合索引时,应该仔细考虑列的顺序。MySQL中索引规则:1、需要加索引的字段,要在where条件中2、数据量少的字段不需要加索引3、...
  • 联合索引的存储结构联合索引字段的先后顺序b+树可以存储的数据条数总结 前言 ​ 对于联合索引我们知道,在使用的时候有一个最左前缀的原则,除了这些呢,比如字段放置的位置,会不会对索引的效率产生影响呢? 最左...
  • mysql可以设置联合唯一索引吗?

    千次阅读 2021-01-18 21:11:58
    mysql可以设置联合唯一索引,方法:使用“Alter table 表名 add UNIQUE index 索引名 (字段1,字段2)”语句来设置,它会删除重复的记录,保留一条,然后建立联合唯一索引联合唯一索引项目需要将某个表的某两个字段...
  • mysql索引(五)联合索引

    千次阅读 2021-07-14 11:35:42
    Mysql索引大概有五种类型: 普通索引(INDEX):最基本的索引,没有任何限制 唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列...联合(组合)索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 79,388
精华内容 31,755
关键字:

mysql设置联合索引

mysql 订阅