代码编织梦想

mysql 5.6之后引入的一个特性,索引下推通过减少回表的次数来提高数据库的查询效率;

回表就是索引中找到了数据id,然后回表去拿到所有数据,再比对另一个过滤条件。

所谓的索引下推,就是在搜索引擎中提前判断对应的搜索条件是否满足,满足了再去回表,通过减少回表次数进而提高查询效率;

如果使用组合索引,就可以利用覆盖索引避免回表操作

如果主键自增,并达到最大,会怎么样。

  • 如果是insert语句,会报唯一键冲突的错误。
  • 如果是insert on duplicate key update,所有insert on duplicate key update语句的update操作都会执行到id为该字段数据类型的最大值的记录上。
  • 如果是insert ignore,会返回rows affected = 0。

InnoDB
InnoDB是MySQL数据库的默认引擎,我们需要了解它的一个重要概念:聚簇索引。InnoDB按照每张表的主键构造一棵B+树,主键对应的索引就是聚簇索引,在每个叶子节点存放着数据表的行数据。所以主键对于InnoDB来说至关重要。

InnoDB对聚簇索引的处理
1、第一种情况:用户定义了主键,那么InnoDB会使用主键作为聚簇索引;
2、第二种情况:用户没有定义主键,那么InnoDB会使用第一个非空的唯一索引作为聚簇索引;
3、第三种情况:用户既没有定义主键,也没有定义唯一索引,那么InnoDB会自动生成一个不可见的ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引,该列是一个6字节的自增数值,随着插入而自增。

影响
很明显,缺少主键的表,InnoDB会内置一列用于聚簇索引来组织数据。而没有建立主键的话就没法通过主键来进行索引,查询的时候都是全表扫描,小数据量没问题,大数据量就会出现性能问题。

而且不设置主键不单单影响到查询,在进行写操作的时候,由于ROW_ID是共享的,这意味着插入的时候需要共享该序列,那么高并发插入的时候为了保持唯一性就避免不了锁的竞争,进而影响性能。

总结
由以上分析可以推论,在建表的时候无论如何都要设置主键,如何没有可用作主键的字段,可以设计一个业务无关的字段,比如xx_id。
 

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_43929696/article/details/129826048

mysql索引篇之覆盖索引、前缀索引、索引下推-爱代码爱编程

覆盖索引 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中,叶子结点保存着索引值和主键值,当使用二级索引进行查询时,需要进行回表操作。假如我们现在有如下表结构 CREATE TABLE `us

MySQL回表、索引覆盖、索引下推-爱代码爱编程

准备 创建一张表,并创建一个自增主键索引和一个组合索引 CREATE TABLE index_opt_test ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(11) DEFAULT NULL, title varchar(11) DEFAULT NULL, age int

回表与覆盖索引,索引下推-爱代码爱编程

转载地址: https://www.jianshu.com/p/d0d3de6832b9 一、什么是回表查询? 通俗的讲就是,如果索引的列在 select 所需获得的列中(因为在 mysql 中索引是根据索引列的值进行排序的,所以索引节点中存在该列中的部分值)或者根据一次索引查询就能获得记录就不需要回表,如果 select 所需获得列中有大量的非索引列

mysql索引下推_MySQL中的索引下推-爱代码爱编程

前段时间看了一下数据库相关知识,出现了索引下推这个名词,有必要记录下来作为知识储备。索引下推用一句话总结是:索引下推是数据库检索数据过程中为减少回表次数而做的优化。 首先介绍下什么是数据库回表,回表是一种数据库检索过程。通常发生在使用二级索引检索非主索引数据的过程中。举个例子: usertest数据表 假设有上面一张表,上面的ID字段是主键索

讲一讲索引下推-爱代码爱编程

索引下推指的是在联合索引中,当搜索的索引字段被中断后,在遍历已确定要回表的结果集中 继续使用后面的索引字段进行匹配,只有匹配到的才回表查询的机制   举个例子: a,b 联合索引 select * from test where a like 'k%' and b=1   按最左前缀原理,当利用索引查询完a之后就不会继续用b进行查询了,而是直接

MySQL索引:回表、索引覆盖,最左匹配原则、索引下推-爱代码爱编程

MySQL索引:回表、索引覆盖,最左匹配原则、索引下推 MySQL索引类型 1.普通索引:最基本的索引,没有任何限制 2.唯一索引(unique index):索引列的值必须唯一,但是允许为空 3.主键索引:特殊的唯一索引,但是不允许为空,一般在建表的时候同时创建主键索引 4.组合索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字

索引下推-爱代码爱编程

索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL

mysql索引下推-爱代码爱编程

前言 写博客是自己对知识梳理,目前是写给自己看,算是自己学习后的作业,也是为了养成一个良好的习惯。 一、什么是索引下推 1.索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询; 2.在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到

mysql--索引--回表-爱代码爱编程

目录 1 索引 1.1 聚簇索引 1.2 联合索引(复合索引) 1.3 唯一索引 1.4 普通索引 1.5 全文索引 2 回表 1 索引 1.1 聚簇索引 聚簇索引:数据和索引一起的叫做聚簇索引 非聚簇索引(二级索引/辅助索引):数据和索引分开存储的叫做聚簇索引 myisam中只有非聚簇索引,innodb中既支持聚簇索引也支

MySQL中的 索引、聚簇索引、非聚簇索引、回表、索引覆盖、索引下推 都是啥?-爱代码爱编程

背景 本文是将网络上搜集到一些相关知识进行汇总,按着自己的理解和方式进行了重新的编辑。 1、MySQL 的索引 针对 MySQL 的索引,主要有以下几条: 它是一棵 B+Tree每一个 B+Tree 的节点都是一个「数据页」每一个「数据页」默认会占用 16KB 的磁盘空间索引是在存储引擎层实现的,所以并没有统一的索引标准,即不同存储引擎的索引的工作

mysql索引下推-爱代码爱编程

1.什么是索引下推? 索引下推(Index Condition PushDown,简称ICP)是从MySQL5.6开始引入的一个特性,索引下推通过减少回表的次数来提高数据库的查询效率; 2.案例 准备: ①.为

mysql 回表、索引覆盖、 索引下推-爱代码爱编程

1. 索引种类 创建数据表& 插入数据 create table user( id int(10) auto_increment, name varchar(30), age tinyint(4), primary key (id), index idx_age (age) USING BTREE )en

mysql索引下推与回表_索引下推和回表-爱代码爱编程

1.简介: 介绍:索引下推是mysql5.6版本的新特性 方法:就是把服务层查询工作下推到引擎层去处理 作用:它能减少回表查询次数,提高查询效率,减少io的开销 回表:使用非聚簇索引进行查找数据时,需要根据主键值去聚簇索引中再查找一遍完整的用户记录,这个过程叫做回表. 聚簇索引和非聚簇索引的区别:聚簇索引可以直接找到数据,非聚簇索引先找到聚簇索引