精华内容
下载资源
问答
  • Mysql联合索引的使用索引下推覆盖索引概念 建立一张user表,id、name、age、address。建立联合索引(name,age)。 理解索引下推覆盖索引的概念首先要理解,联合索引的普通索引的区别。 比如(name, age) ...

    Mysql联合索引的使用索引下推、覆盖索引概念

    建立一张user表,id、name、age、address。建立联合索引(name,age)。
    CREATE TABLE user_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(10) DEFAULT NULL,
    age int(10) DEFAULT NULL,
    address varchar(10) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY name (name) USING BTREE,
    KEY name_age (name,age)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    理解索引下推、覆盖索引的概念首先要理解,联合索引的普通索引的区别。 比如(name, age) 和 单独name索引的区别。
    语句1:
    select * from user_test where name = “aaa” and age=10
    此时用到的索引是联合索引,不过需要回表查询。
    在这里插入图片描述

    语句2:
    select * from user_test where name like “aa%” and age =10
    此时无法在aaa处定位到具体记录,也就是联合索引只用了一般。因为联合索引用完整的条件是,比如(name, age)首先通过name能定位到一类记录,在判断age。如果此时通过name定位到两类记录,那么联合索引只能用到一半,此时就会发生索引下推。
    mysql5.6前,这样的查询需要回表查询,需要一条条重新到聚簇索引定位到主键记录,然后进行判断。
    MySQL 5.6 后,索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表查询需要的次数。在联合索引查询时就做了判断。
    在这里插入图片描述
    注意:这个extra 并没有using where。说明只进行了回表,并没有在server层进行了逻辑判断。
    语句3:
    select name,age from user_test where name like “aa%” and age =10。
    此时这个语句就避免了回表查询,用到了覆盖索引和索引下推。直接在二级索引上,得到需要的数据。
    在这里插入图片描述
    其中 using where , using index . using where && using index. using index condition.区别,下面的博客讲的个人认为比较清晰。
    using where在server 层做了判断, using index 覆盖索引, using where && using index.是在非聚簇索引上,进行了查询,并且在server层做了判断,using index condition是回表查询,也用了索引下推(可能是用到了部分联合索引(name, age)只用到了name和整个联合索引(name,age)), 但是不用在server层做判断。

    https://blog.csdn.net/Saintyyu/article/details/99694649?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1.not_use_machine_learn_pai&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1.not_use_machine_learn_pai

    展开全文
  • 今天我们来聊聊在MySQL索引优化中两种常见的方式,索引覆盖和索引下推索引覆盖要了解索引覆盖,需要先了解几个索引的基础知识B+树索引B+树索引是InnoDB中的一种很常见的索引类型。关于B+树,这里不做深入的介绍,不...

    写在前面

    在MySQL数据库中,索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点,索引就是为了提高数据查询的效率。今天我们来聊聊在MySQL索引优化中两种常见的方式,索引覆盖索引下推

    索引覆盖

    要了解索引覆盖,需要先了解几个索引的基础知识

    B+树索引

    B+树索引是InnoDB中的一种很常见的索引类型。关于B+树,这里不做深入的介绍,不太清楚的小胖友可以看单独介绍B树、B+树的文章。简单来说,是因为使用B+树存储数据可以让一个查询尽量少的读磁盘,从而减少查询时磁盘I/O的时间。在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。举例说明,假设我们有一张表,该表主键为id,且有用户名(user_name)和用户年龄(age)两个字段,其中age字段上有索引,所以建表语句如下图所示:

    3c499bd8729aec12b218bbb01d7e040c.png

    表中有5条数据

    888deb4e87cdcd76fd971fb68a47046b.png

    基于上面这种表设计和表中的数据,在InnoDB中的索引结构是下面这个样子的

    eae38cd146d62429aae0355df3be16df.png

    根据叶子节点的内容,索引的类型分为主键索引非主键索引
    • 主键索引的叶子结点存的是整条记录,如上图紫色部分所示

    • 非主键索引的叶子结点存的是主键的地址值,根据二级索引叶子结点中的地址可以找到主键索引中的这一条数据。所以非主键索引也被称为二级索引,如上图右半边黄色部分所示

    那主键索引、二级索引的概念和我们今天介绍的内容有什么关系呢?我们下面来具体说说回表根据刚才提供的表结构,我们尝试来做两次次这样的查询:
    select *from lyb_test where id = 2select *from lyb_test where age = 12
    • 第一条语句使用主键作为检索条件,即为主键查询,根据上图所示我们知道,如果是主键查询,我们只需要搜索左边这颗主键索引树即可快速查询到id=2的这条数据

    • 第二条语句使用的是二级索引、即age作为检索条件,这和主键查询有什么区别呢?如果是二级索引查询,则需要先搜索左侧的age索引树,得到id的值为2,再到右侧的主键索引树搜索一次。

    像第二种查询语句这样,通过非主键索引查询数据时,我们先通过非主键索引树查找到主键值,然后再在主键索引树搜索一次(根据rowid再次到数据块里取数据的操作),这个过程称为回表,也就是说非主键索引查询会比主键查询多搜索一棵树

    索引覆盖

    结合上面的知识储备,我们进一步来优化一下刚才的SQL

    select *from lyb_test where age = 12
    当这条语句执行时,我们知道会进行两次索引树查询,第一次在二级索引上查询到主键索引的引用,然后到主键索引树中查询到所需要的数据,这个过程我们称之为回表。那为什么要有回表操作呢?由于查询的结果是所有字段,所需要的数据只有主键上才有,所以不得不回表。我们如果将sql改造为下面这种方式:
    select id from lyb_test where age = 12

    由于查询的值是ID,而id的值已经在age索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,当SQL语句的所有查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中,便可以直接使用索引查询而不需要回表。即在这个查询里,索引age已经“覆盖了”我们的查询需求,故称为索引覆盖。

    索引下推

    还是基于刚才的表结构和数据,我们现在针对user_name和age建立联合索引,索引建立之后,查询姓名以b开头且年龄大于等于13的用户信息,SQL语句如下:

    select * from user_table where username like 'b%' and age >= 13
    语句的执行过程有两种可能性:
    1. 根据(username,age)联合索引查询所有满足名称以"b"开头的索引,然后回表查询出相应的全行数据,再筛选出满足年龄大于等于13的用户数据。如果表中user_name以b开头的数据有n条,则需要回表n次

    2. 根据(username,age)联合索引查询所有满足名称以"b"开头的索引,然后直接再筛选出年龄大于等于13的索引,之后再回表查询全行数据。经过两次筛选之后,回表次数一定小于上述第一种情况

    我们把第二种语句执行的过程称之为索引下推在MySQL中,索引下推是默认启用的状态。在使用InnoDB存储引擎的数据表中,索引下推只能用于二级索引。我们可以通过修改MySQL系统变量来控制索引下推是否开启。设置如下:
    SET optimizer_switch = 'index_condition_pushdown=off';// 关闭SET optimizer_switch = 'index_condition_pushdown=on';// 开启

    索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段,查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。假设表t有联合索引(a,b),下面语句可以使用索引下推提高效率

    select * from t where a > 2 and b > 10

    上述就是索引覆盖、回表、索引下推的相关概念和使用场景。当然针对MySQL的索引优化还有其他非常多的方式,我们可以在之后的文章中讨论。本文到这里就结束啦,谢谢小伙伴们的阅读~

    展开全文
  • 上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这...

    一、前言

    上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这就是本章要讲的内容。

    二、联合索引

    联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。

    开讲之前我们先弄一张学生表,表数据如下:
    在这里插入图片描述
    下面我们给出一个需求:查询表中以字母"L"开头的姓名及年龄。

    1、常规的写法(回表查询)

    SELECT name,age FROM `t_user` where name like 'l%' ;
    

    这种写法,明显效率是低下的,我们用explain 分析一下:
    在这里插入图片描述
    由图中可以看出,在数据库中进行了全表扫描。下面我们看一下数据库中的执行过程。

    第一步:全表扫描数据,找出以“l”开头的主键id.
    第二步:将所有查询出来的数据每一个都回表,根据id来查询出想要的数据。
    

    2.优化写法(索引覆盖)
    因为我们要查询name和age。所以,我们对name和age建立了联合索引,建立后的索引图如下:
    在这里插入图片描述
    从图中我们可以看出,叶子节点中的键值都是按顺序存储的并且都包含了名字和年龄,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

    索引会先根据 name 排序,如果 name 相同,再根据 age 进行排序。

    我们对name和age建立索引后,当我们查询name和age二个字段时,直接会从索引中查出来而不需要回表查询,这种方式就是索引覆盖。执行步骤是这样的:

    第一步:使用联合索引(name,age)查询以“l”开头的数据
    第二步:在索引中取出name和age.
    

    这种方式是不是高效多了,你要是还不信,我们用explain看一下,如下图:

    EXPLAIN SELECT name,age FROM `t_user` where name like 'l%' ;
    

    在这里插入图片描述
    从图中我们看的出,使用了(name,age)索引。

    2.1 联合索引最左匹配原则

    联合索引在使用的时候一定要注意顺序,一定要注意符合最左匹配原则。

    最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。

    这个定义不太好理解,我解释一下:

    假如对字段 (a, b, c) 建立联合索引,现在有这样一条查询语句:

    where a > xxx and b=yyy and c=zzz
    where a like 'xxx%' and b=yyy and c=zzz
    

    在这个条件语句中,只有a用到了索引,后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等),就停止后边的匹配。”的意思。

    我们还是假如对字段 (a, b, c) 建立联合索引,

    1.如下查询语句可以使用到索引:

    where a = xxx
    where a = xxx and b = xxx
    where a = xxx and b = xxx and c = xxx
    where a like 'xxx%'
    where a > xxx
    where a = xxx order by b
    where a = xxx and b = xxx order by c group by a
    

    2.如下查询条件也会使用索引:

    where b = xxx and a = xxx
    where a = xxx and c = xxx and b = xxx
    

    虽然b和a的顺序换了,但是mysql中的优化器会帮助我们调整顺序。

    3.如下查询条件只用到联合索引的一部分:

    where a = xxx and c = xxx   可以用到 a 列的索引,用不到 c 列索引。
    where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    

    4.如下查询条件完全用不到索引

    where b = xxx
    where c = xxx
    where a like '%xxx'			-- 不满足最左前缀
    where d = xxx order by a	-- 出现非排序使用到的索引列 d 
    where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等
    

    如何选择合适的联合索引

    1.where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的,建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。

    2.如果是 where a > xxx and b = xxx 或 where a like ‘xxx%’ and b = xxx 这样的语句,可以对 (b, a) 建立索引。

    3.如果是 where a = xxx order by b 这样的语句,可以对 (a, b) 建立索引。

    三、索引覆盖

    索引覆盖在上面我们已经介绍了。由上面的介绍我们知道,建立了联合索引后,直接在索引中就可以得到查询结果,从而不需要回表查询聚簇索引中的行数据信息。

    索引覆盖可以带来很多的好处:

    • 辅助索引不包含行数据的所有信息,故其大小远小于聚簇索引,因此可以减少大量的IO操作。
    • 索引覆盖只需要扫描一次索引树,不需要回表扫描聚簇索引树,所以性能比回表查询要高。
    • 索引中列值是按顺序存储的,索引覆盖能避免范围查询回表带来的大量随机IO操作。 判断一条语句是否用到索引覆盖:

    这个我们需要用explain查看一下。
    在这里插入图片描述
    Using index 就表示使用到了索引 , 并且所取的数据完全在索引中就能拿到,也就是用到了索引覆盖。

    四、索引下推

    索引下推是索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。

    索引下推是把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断,这样能有效减少回表。

    举例说明:

    首先使用联合索引(name,age),现在有这样一个查询语句:

    select *  from t_user where name like 'L%' and age = 17;
    

    这条语句从最左匹配原则上来说是不符合的,原因在于只有name用的索引,但是age并没有用到。

    不用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:再根据这四条索引数据中的 id 值,逐一进行回表扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server 层。
    第三步:在server层判断age = 17,进行筛选,最终只留下 Lucy 用户的数据信息。
    

    使用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:根据 age = 17 这个条件,对四条索引数据进行判断筛选,最终只留下 Lucy 用户的数据信息。
    (注意:这一步不是直接进行回表操作,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选)
    第三步:将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。
    

    比较二者的第二步我们发现,索引下推的方式极大的减少了回表次数。

    索引下推需要注意的情况:

    下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用

    开启索引下推:

    索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。默认情况下,索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

    set optimizer_switch='index_condition_pushdown=off'; 	-- 关闭索引下推
    set optimizer_switch='index_condition_pushdown=on';		-- 开启索引下
    

    五、结尾

    好了,本章就讲到这里吧,下一章,我们对所有的知识进行一下总结。

    另外大家帮忙关注我,每天更新优质内容。关注我有大量学习资料和学习视频赠送。
    在这里插入图片描述
    扫二维码关注公众号【Java程序员的奋斗路】可领取如下学习资料:
    1.1T视频教程(大约有100多个视频):涵盖Javaweb前后端教学视频、机器学习/人工智能教学视频、Linux系统教程视频、雅思考试视频教程,android.等
    2.项目源码:20个JavaWeb项目源码

    展开全文
  • 索引下推(减少回表次数) 存储引擎与底层实现的数据结构 数据结构 - 索引怎么选择合适的数据结构?中分析过能作为索引的数据结构主要有散列表(Hash表)、红黑树、跳表、B+树(B树)以及有序数组,并且分析了它们...

    目录

    存储引擎与底层实现的数据结构

    InnoDB主键索引和二级索引

    复合索引(一棵B+树过滤过个条件)

    覆盖索引(不用回标)

    索引下推(减少回表次数)

    前缀索引(长字符串索引优化)


    存储引擎与底层实现的数据结构

         数据结构 - 索引怎么选择合适的数据结构?中分析过能作为索引的数据结构主要有散列表(Hash表)、红黑树、跳表、B+树(B树)以及有序数组,并且分析了它们适合场景。Mysql的索引与存储引擎相关,但是Mysql内常用的存储引擎有InnoDB、MyiSAM、Memory,在Mysql5.5版本后,InnoDB已经作为默认的存储引擎,并且很多互联网公司基本都要求只能使用InnoDB存储引擎。Memory作为临时表的默认存储引擎,所以研究的重点基本就是InnodDB和Memory引擎,也就是基本关注B+树和散列表的数据结构索引。他们底层支持的数据结构如下图:

        InnoDB存储引擎本身只支持B+树,之前分析过B+树比较适合磁盘存储。B+树是多路平衡搜索树,最佳实践N值为1200左右,树高为4时就可存储 1200的3次方,此时已经存储了17亿数据了。由于第一层数据总是在内存中,那么相当于17亿数据,最多查询磁盘3次,如果第二层刚好也在内存中,那么对多查询2次磁盘。也就是说InnoDB的最底层数据结构是B+树,B+树可能存储在内存中可能存储在磁盘中,存储的单元是数据页(操作系统数据缓存页),即数据缓存页是内存和磁盘的链接点。              磁盘 -> 数据页 -> 内存 

        而查询数据所在的行到达在内存中还是在磁盘中,由服务器所在的 InnoDB缓存页大小决定,Buffer Pool由 my.cnf配置中的参数控制:

         innodb_buffer_pool_size:引擎的缓存池大小,一般为物理内存的 60% - 80%;

         innodb_buffer_pool_instances:IBP(InnoDB Buffer Pool)的个数,Linux系统,如果 innodb_buffer_pool_size 大小超过 1GB,innodb_buffer_pool_instances 值就默认为 8;否则,默认为 1;

        缓存的大小有限,那么只能将数据命中率最高的放入缓存中,Mysql使用的LRU缓存淘汰算法。LRU可以理解成一个链表,链表的节点就是数据缓存页,刚被访问过的放到链表一头,最老被访问过的放到一头,当有新的数据缓存页被访问加入时,从最老的一头淘汰。但是链表本身删除节点的时间复杂度是O(1),但是访问时间复杂度是O(N),性能很低。怎么才能提高访问性能呢? 一般会使用散列表进行访问,即整个LRU由散列表+双向链表组成,类似于Java中的LinkedHashMap的数据结构。

        由于磁盘局部性原理,访问数据页时有预读数据页的功能,即我们从磁盘中获取到了多余的数据页,加入LRU的话就是浪费存储空间。还有我们可能会(处理报表等sql)对一个历史数据表进行整表分页全表查询一遍,那么也会对LRU照成冲击,可能需要很长的时间才能让缓存命中率恢复。针对预读数据页和冷数据扫描的情况,Mysql对LRU进行了改造,将LRU链分成 5/8的young区和 3/8的old区。当数据需要淘汰时直接从old区的末尾开始,而当新访问数据页时先判断在缓存中是否存在,如果不存在则直接将数据添加到old区,否则当满足下面配置时才会真正移动到young区域:    innodb_old_blocks_time:mysql缓存页使用的LRU,old区升级为young区的时间判断。默认值1000,单位毫秒;

        而对于数据库而言,数据页或者数据行本身的存储结构如下图  表空间 -> 段(叶子点段、非叶子点段、回滚段)-> 区 -> 页 -> 行

        表空间:数据库由一个或者多个表空间组成;表是一个逻辑容器,表空间存储的对象是段,在一个表空间中有一个或者多个段,但是一个段只能属于一个表空间,如上图。

        段:段由一个或者多个区组成,段不要求区与区之间相邻,当创建数据库或者索引时就会创建相应的段。

        区:在InnoDB中,一个区会分配64个连续的页。

        页:默认大小是16KB

     

    InnoDB主键索引和二级索引

        InnoDB索引由B+树构成,B+树由非叶子节点和叶子节点组成。非叶子节点不存储数据,而叶子节点存储数据。InnoDB中主键索引叶子节点存储的是整个表的行数据信息,称为聚簇索引,而非主键索引(也叫二级索引)的叶子节点存储的是主键的值(内存地址、指针)。

        如果我们在表上为非主键创建索引时,相当于是维护了一棵二级索引的B+树,经过优化器判断后知道要查找该二级索引B+树。该B+树的非叶子节点存储的是索引字段的值,叶子节点存储的是主键索引B+树的值,查询过程就是两棵B+树(两次时间复杂度为O(logN)的查询过程)。二级索引的根据主键id到主键索引上的查询过程叫做回表

        如果是根据主键id查询那么就是直接查询主键B+树,时间复杂度是O(logN)。

        如果查询的字段没有索引,那么就是在主键的B+树上,进行树的遍历匹配值过程,时间复杂度是O(N)。

     

    复合索引(一棵B+树过滤过个条件)

        覆盖索引就是将一个表的多个字段创建同一个索引,即在B+树上非叶子节点上存储的就是多个有序字段的值。有序就是最左原则,也是面试的高频点。创建语句:

    ALTER TABLE 表名称 ADD INDEX idx_索引名称 (字段1, 字段2, 字段N);

       关于复合索引经常的面试题就是(最左原则),那是因为在B+树上,每个节点都是基于创建索引时的有序字段,只要理解了复合索引的B+树就理解的最左:

       在表中创建了一个复合索引 A,B,C,下面哪些查询条件可以使用索引(如 where A = ? and B = ?and C = ?):

       A,B,C  可以走索引

       AB       可以走索引

       A          可以走索引

       BC       不能走索引

       AC       不能走索引

       CBA     可以走索引,因为在优化器阶段,可以将查询条件顺序调整为:ABC

     

    覆盖索引(不用回标)

       上面的使用二级索引再查询主键索引时涉及到了回标的操作,如果查询的条数比较很多,查询的字段也比较多,那么回标操作的代价就比较大。如果查询的字段以及在二级索引中(二级索引中包含了主键ID),那么就没有必要进行回表,称为覆盖索引。所以一般要求我们在写select语句时不要使用select * ,因为这种情况肯定是不能走覆盖索引的。并且覆盖索引往往伴随着复合索引,因为我们一般查询的字段不止 单索引字段和主键。

     

    索引下推(减少回表次数)

        比如一张表有 name 和 age 两个字段的复合索引,当我们执行查询条件 where name = “XXX” and age = ‘XX’ and ... 时,在Mysql 5.6的版本中,在复合索引的中匹配到了 name字段值后,则会直接到主键索引上去根据age字段值进行过滤。而在Mysql 5.6之后,在满足了name字段的值后,会继续在复合索引上根据age 字段的值进行过滤,满足条件再回表,减少回表的次数,称为 索引下推(Index Condition PushDown)

    前缀索引(长字符串索引优化)

        添加索引时使用 alter table t add index indexName(字段名),默认为全字段索引。但是如果索引的字段为字符串,并且字符长度非常大,比如长度为 100等,那么建立的全字段索引将是一棵非常庞大的B+树。此时,如果字符串的区分度非常大,比如字符串前N位就可以排除大部分的数据,那么我们就可以为该字段建立一个前缀索引。 如:alter table t add index indexName(email(8))

        只是创建前缀索引,如果该字段的字符串只有 5位,而索引的是前 8位,前缀索引本身也不知道是不是真正的全字段信息了,那么就会执行回表操作(即回表操作一定会执行),获取整个字段的信息。所以,使用了前缀索引,一定不可能是覆盖索引。

        由于数据是变动的,并且我们自己根据业务不能完全推断出建立多长的前缀索引是最优的,那么我们根据自己能接受的最大区分度,比如 5%,执行查询操作如下:

    select 
        count(distinct left(email,4))as L4,
        count(distinct left(email,5))as L5,
        count(distinct left(email,6))as L6,
        count(distinct left(email,7))as L7,
    from t;

    前缀索引的优化操作:

    1)、倒序

        如果前面的区分度不够大,而后面的区分度比较大,此时我们可以 对该字段进行排序的存、查询操作,比如身份证前面的区分度很低。reverse的过程可以在数据库中处理,也可以在java中先处理好

    insert into t(。。。id_card)values(。。。reverse(id_card字段值));

    select * from t  where id_card = reverse(id_card字段值);

    2)、增加hash字段

        利用散列函数,增加一个hash值 字段,只是需要去均衡增加字段的代价和长字符串索引的代价【如果字符串本身非常长,又要基于精确查询】。

    alter table t add id_card_crc int unsigned, add index(id_card_crc);

    select field_list from t where id_card_crc=crc32('id_card字段值') and id_card='id_card字段值'   // 使用crc函数处理值的插入、查询

     

        以上两种对前缀索引的改造方案,都只支持精确查找,好处就是节约了空间,节省了扫描的行数。

        从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段。当然,倒序存储方式使用 4 个字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个 hash 字段也差不多抵消了。在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的 CPU 资源会更小些。从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

     

     

    展开全文
  • 覆盖索引 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引...
  • 覆盖索引 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引...
  • 快速理解 Mysql 回表 索引覆盖 索引下推回表操作索引覆盖索引下推 回表操作 Mysql 每页大小为16K(B+树结构,所以16K足以),关于主键索引辅助索引的结构这里简单说一下。 InnoDB 主键(聚簇索引):仅在叶子节点...
  • 覆盖索引在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中...
  • 今天看到一篇关于数据库优化和索引写的非常好的文章,分享一下.文章转载至:https://www.cnblogs.com/Leo_wl/p/13093705.html 一、前言 最近小农在找工作,因为今年疫情的特殊原因,导致工作不是特别好找,所以一旦...
  • 索引类型索引类型分为主键索引和非主键索引。(一定要牢记,是怎么存储数据的)主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。非主键索引的叶子节点内容是主键的值。...
  • 索引类型索引类型分为主键索引和非主键索引。(一定要牢记,是怎么存储数据的)主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。非主键索引的叶子节点内容是主键的值。...
  • 覆盖索引:即从辅助索引中得到查询的记录,而不需要查询聚集索引中的记录,覆盖索引必须要存储索引列的值,而哈希索引、空间索引全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引。...
  • 索引类型索引类型分为主键索引和非主键索引。(一定要牢记,是怎么存储数据的)主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。非主键索引的叶子节点内容是主键的值。...
  • Table of Contents 一、聚集索引(Clustered Index)...六、索引下推 七、基于explain分析sql并印证结论 八、参考 MySQL主流的版本默认引擎是InnoDB,InnoDB中索引结构为B+树,为什么使用B+树?寻址次数少且查询...
  • 创建一张表,并创建一个自增主键索引和一个组合索引 1 2 3 4 5 6 7 8 9 10 CREATE TABLE index_opt_test ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(11) DEFAULT NULL, title varchar(11) DEFAULT NULL...
  • 文章目录1 回表2 最左匹配原则3 索引覆盖4 索引下推 1 回表 这先要从InnoDB的索引实现说起,InnoDB有两大类索引: 聚集索引(clustered index) 普通索引(secondary index) InnoDB聚集索引普通索引有什么差异? ...
  • innodb的索引下推

    万次阅读 2020-08-25 16:32:18
    索引下推,是mysql优化多列索引查询的一种方案,叫做索引下推不如翻译为索引条件下推更合适(Index Condition Pushdown)简称ICP,因为他实际上是把where中的查询索引条件,下推给了存储引擎 本文涉及到的内容有: ...
  • 索引
  • 覆盖索引 通过联合索引将所有字段囊括,查询时就不需要进行回表操作,称为覆盖索引。 最左前缀原则 字段满足联合索引最左N个字段,就可以利用索引进行查询。 一般来说当存在索引(a,b)后,不需要再单独为a建立索引。...
  • 索引下推 在前面学习整理mysql数据结果索引相关知识的时候,遇到了几个概念,像是回表、聚簇索引、最左匹配等,这篇将常听到的概念整理到一起,便于以后翻阅小伙伴们阅读,各位看到此博客的小伙伴,如有不对的...
  • 表数据和索引在同一个位置是聚集索引,一般主键是聚集索引,如果没有主键用唯一键,如果主键唯一键都没有用rowid 非聚集索引 表数据和索引不在同一个位置 回表 举个例子 create table mytable(id int primary,name...
  • 什么是聚簇索引:将数据和索引储存在一起,节点储存索引,叶子节点储存数据 普通索引:节点储存普通索引,叶子节点储存聚簇索引 聚簇索引的选取: 用主键作为聚簇索引 如果没有主键,那么选取第一个具有唯一...
  • 对索引不了解的可以先去看我的这篇文章:从根本去理解MySQL的索引原理数据结构 回表 使用二级索引进行查询,在二级索引树上找到记录后,根据主键回到主键索引树搜索的过程,称为回表。 覆盖索引 使用二级索引...
  • 本篇文章是一篇面试对话,其中参考里面的知识点结合自身情况进行加强,感谢博主。 索引概念、索引模型 对于查询做一些优化?...A:常见的MySQL主要有两种结构:Hash索引和B+ Tree索引,我们使用的是I
  • 四、索引下推 五、小结 一、引入 在开始这篇文章之前,首先明确一个概念,聚集索引的B+树的每个节点就是一个索引页,索引页会根据先前规定好的度数来决定一个索引页放多少个索引值。 非叶子节点只有索引区...
  • 我们容易有以一个误区,就是在经常使用的查询条件上都建立索引索引越多越好,那到底是不是这样呢? 在讲正文之前,我们先来看一个重要的属性列的离散度,公式如下: count(distinct(column_name)) : count(*) --列...

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 132
精华内容 52
关键字:

索引下推和覆盖索引