精华内容
下载资源
问答
  • 聚簇索引 介绍 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上是通过一个结构中保存了B-Tree索引和数据行。因为无法同时把数据行存在两个不同的地方,所以一个表只能有一个聚簇...

    聚簇索引

    • 介绍

      聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上是通过一个结构中保存了B-Tree索引和数据行。因为无法同时把数据行存在两个不同的地方,所以一个表只能有一个聚簇索引(覆盖索引可以模拟多个聚簇索引的情况)

    • 索引的建立

      一个表有且只有一个聚簇索引;

      InnoDB一般是通过主键建立聚簇索引;

      如果没有定义主键,InnoDB会选择一个唯一的非空索引代替;

      如果没有这样的索引,InnoDB会隐式定义一个主键来作为局促索引;

      InnoDB只聚集在同一页面中的记录,包含相邻键值的页面可能会相距甚远;

    • 优点

      1.可以把相关数据保存在一起,减少I/O操作;

      2.数据访问更快。找到索引即找到数据;

      3.使用覆盖索引扫描的查询可以直接使用叶节点的主键值;

    • 缺点

      1.插入速度严重依赖插入顺序。按主键顺序插入是加载数据到InnoDB表中最快的方式。如果不是按照主键顺序加载数据,那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表;

      2.更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的列移动到新的位置;

      3.基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题;

      4.聚簇索引可能导致全表扫描变慢,尤其是比较稀疏,或者由于页分裂导致数据存储不连续的情况;

      5.二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列。二级索引访问需要两次索引查找。

    聚簇索引

    非聚簇索引

    • 介绍

      也叫辅助索引或二级索引。一个表中可以有多个二级索引,其叶子节点存放的不是一整行数据,而是键值。叶子节点的索引行中,包含了一个指向聚簇索引的指针,从而在聚簇索引树中找到一整行数据。

    • 优点

      理论上可以有无限多个,不受限制。当然大家都知道,实际中并不是索引越多越好

    • 缺点

      非聚簇索引总是要进行二次查询,增加I/O操作
      非聚簇索引

    联合索引

    • 介绍

      包含多个字段的索引。当不需要考虑排序和分组时,将选择性最高的列放在前面同时是比较推荐的;

      选择性高是指:某列数据的散列性强,即重复值少。

    • 优点

      减少索引的建立和维护成本,符合最左匹配原则,参考

    覆盖索引

    • 介绍

      判断标准:使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

      从辅助索引中就能获取到需要的记录,而不需要查找聚簇索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息,所以数据量较聚集索引较少,可以减少大量I/O操作

      查询联合索引中的某个或某几个字段的时候,where语句最好是根据最左匹配来定义,那么就会出现覆盖索引

      select语句中,如果要查询的结果列,都在联合索引的列中,那么一般都会出现覆盖索引,即explain的时候,会出现Using Index

    • 注意

      1、覆盖索引也并不适用于任意的索引类型,索引必须存储列的值

      2、Hash 和full-text索引不存储值,因此MySQL只能使用B-TREE

      3、并且不同的存储引擎实现覆盖索引都是不同的

      4、并不是所有的存储引擎都支持它们,Memory不支持

      5、如果要使用覆盖索引,一定要注意SELECT 列表值取出需要的列,不可以是SELECT *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降,不能为了利用覆盖索引而这么做

      6:遇到以下情况,执行计划不会选择覆盖查询
      select选择的字段中含有不在索引中的字段 ,即索引没有覆盖全部的列。
      where条件中不能含有对索引进行like的操作。

    总结

    聚簇索引:有且只有一个,通常是主键,推荐是id自增主键

    非聚簇索引:只能通过聚簇索引,找到对应的整行数据

    辅助索引:遵循最左匹配原则,列顺序有一定的经验和讲究

    覆盖索引:是数据库后台自动处理的

    展开全文
  • 首先,博主这边要用ES来代替传统的mysql操作,那么原来的多表联合查询操作自然也要转换为多索引联合查找。这里使用elasticsearch-php库来操作ES,原生的ES也是大同小异的。 日期查询优秀文章参考: 1、 自定义日期格式...

    一、前言

          首先,博主这边要用ES来代替传统的mysql操作,那么原来的多表联合查询操作自然也要转换为多索引联合查找。这里使用elasticsearch-php库来操作ES,原生的ES也是大同小异的。

    日期查询优秀文章参考:

    1、 自定义日期格式以及ES内置的日期格式

    官方文档:https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html
    优秀博客:
    https://blog.csdn.net/u011019726/article/details/69541946
    https://blog.csdn.net/gui66497/article/details/80433693
    2、 日期问题,可能是需要先设置好mapping才能 查

    链接:https://blog.csdn.net/weixin_36270623/article/details/84794652 (这个在新建索引的时候最好就设置好)

    二、正文示例

          一直听说ES的日期查询有个坑,那就是时区问题,现在终于轮到我碰到了,有点期待呢。记录一下。 这里我本地刚开始是搜索不到的,传过来的日期参数形如:2019-03-28 11:23:52。后来看到kibana中显示:02/Apr/2019:18:18:20 -0700,所以试着在原来2019-03-29T01:43:01.368Z的基础上加上7小时,发现可以搜索到了。这是因为在数据入库的时候,没有指定时区为asia/shanghai,而ES默认选择时区为UTC,因此存储的数据和本地的json数据存在时间差7小时。

    1、多索引联合日期范围查询

    代码:

       $params = [
                'index' => ['zeusa.evony.com.accesslog-2019.03.28','zeusa.evony.com.accesslog-2019.03.29'], 
                'type' => 'doc',
                'body' => [
                    'from'=>10,
                    'size'=>20,  
       	"_source"=>[
                        "includes"=>[ "pixel*"],
                       // "excludes"=> [ "*.description" ]
                    ],
                    'query' => [
                            "range" =>[
                                    "@timestamp" => [   
                                    "gt" => "2019-03-28T23:58:56.052Z", 
                                    "lt" =>"2019-03-29T23:58:56.052Z",  
                                    "time_zone" =>"+07:00",
                                ]
                            ] 
                    ]
                ]
            ];
      $repos =  $this->client->search($params);
            $arr = [];
            foreach($repos['hits']['hits'] as $v)
            {
                $arr[] = $v['_source'];
            }
    

    解释:
    (1)多索引查询: 多索引一起用,逗号隔开以数组的形式传过去,实测可以。根据业务把索引名拼成一个数组传进去即可
    (2)关于size: 默认只查出来10条,可以自己控制条数,单默认设置不超过10000条。如果要获取查询的数量,设置为0即可。
    如果要查询的数据很多,参考:https://blog.csdn.net/bushanyantanzhe/article/details/79109721 设置max_result_window参数即可
    (3)关于查询字段: 字段名称要和kibana中看到的ES数据名称保持一致,比如这个字段前面的@一定要有,因为kibana中的字段是有@
    (4)时间日期格式: 格式要和元数据的格式保持一致,不能是简单的2019-03-28 11:23:52,要么查询不出来数据,要不就会报错:

    failed to parse date field [2019-03-28 11:23:52] with format [strict_date_optional_time||epoch_millis]: [Text '2019-03-28 11:23:52' could not be parsed, unparsed text found at index 10
    

    (5)时间日期部分: 查询的时候,可以不精确到最后的sss,一样可以正常查询出来.但必须要带上TZ,不然会报错:

    failed to parse date field [2019-03-29 08:43:11] withformat[strict_date_optional_time||epoch_millis
    

    这里的TZ参考:https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html ,直接搜索 strict_date_time 即可找到该定义
    (6)time_zone: 经过反复测试,这个字段没起到作用。可能是我的问题,欢迎指导,谢谢
    (7)_source: 这部分是为了筛选数据,因为正常查询出来的数据总是含有很多无用的字段,我们做分析只需要部分字段即可。所以可以通过_source的"includes“属性
    规定返回的字段,”excludes"属性规定哪部分不反悔。我这里要查询的字段都有个前缀pixel,例如pixel.user_id等,所以使用pixel.*过滤。
    参考官方文档:https://www.elastic.co/guide/en/elasticsearch/reference/1.7/docs-get.html#get-source-filtering

    2、如果要精准查询,替换range为:

      "bool"=>[
                    "filter" =>[
                        "term" => [ "@timestamp" =>"2019-03-29T08:43:11.274Z" ]
                     ]
                ]
    

    注意: 如果想要精准匹配,那就要用term,这样只会查出user_id=2478的记录。如果是用match查询,那么会查出所有带有user_id=2478的记录,也就是会有很多,比如 xxxxx&user_id=2478xxxxx 也会查出来。

    3、格式化日期加上7小时

      $start = "2019-03-28 16:48:11";       //转化搜索的时间为TZ格式,并且加上7小时 date('Y-m-d H:i:s',strtotime("$start+7hour")));
       $st_arr = explode(' ',date('Y-m-d H:i:s',strtotime("$start+7hour")));
       $start = $st_arr[0].'T'.$st_arr[1].'Z';
    

    关于日期可以参考:https://blog.csdn.net/li_lening/article/details/80930323

          以上就是关于elasticsearch的多索引查询以及范围日期查询的内容,多索引的关键是用数组的方式传过去,源码部分会遍历这个索引数组,分别查询组合。日期查询的关键是时区转换,其他的就是DSL的标准格式了。

    end

    展开全文
  • (使用的普通索引联合唯一索引的部分前缀) 简单 select 查询,name是普通索引(非唯一索引) explain select * from film where name = 'film1'; 关联表查询,idx_film_actor_id 是 film_id 和 actor_id 的...


    前言

    sql的调优在实际开发中非常常见,特别是服务器压力上去之后就需要考虑给耗时大的,常用的sql进行优化,提高服务器的高可用。调优时会用到explain工具,本章就来使用下explain,看看这到底有多强大。


    一、EXPLAIN

    使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈;
    在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而不是执行这条SQL;

    如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中;

    二、 新建三张表

    # 演员表
    DROP TABLE IF EXISTS `actor`;
    CREATE TABLE `actor` (
        `id` int(11) NOT NULL,
        `name` varchar(45) DEFAULT NULL,
        `update_time` datetime DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-2 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
    
    # 电影表
    DROP TABLE IF EXISTS `film`;
    CREATE TABLE `film` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(10) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
    
    # 演员和电影的关联表
    DROP TABLE IF EXISTS `film_actor`;
    CREATE TABLE `film_actor` (
        `id` int(11) NOT NULL,
        `film_id` int(11) NOT NULL,
        `actor_id` int(11) NOT NULL,
        `remark` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_film_actor_id` (`film_id`,`actor_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
    

    三、explain 中的列

    explain select * from actor;
    

    以上的执行计划结果:
    在这里插入图片描述

    id 列

    id 列的编号是 select 的序列号,有几个 select 就有几个id,并且 id 的出现顺序是按 select 出现的顺序增长的。
    id 列越大执行优先级越高,id 相同则从上往下执行,id为NULL最后执行。

    select_type 列

    select_type 表示对应行是简单还是复杂的查询。
    简单查询只有 simple,复杂查询有:primary,subquery,derived,union;

    • simple:简单查询。查询不包含 子查询 和 union;
    explain select * from film where id = 2;
    

    在这里插入图片描述

    • primary:最外层的 select ;---- 复杂查询
    • subquery:包含在 select 中的子查询(不在 from 子句中) – 复杂查询
    • derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义) ; --复杂查询

    用下面的例子了解 primary、subquery 和 derived 类型:

     set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
     explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
     set session optimizer_switch='derived_merge=on'; #还原默认配置
    

    在这里插入图片描述
    分析SQL的执行顺序: 先执行 film 表的查询 --> 再执行 actor 表的查询 --> 最后再执行生成的临时表查询;

    第1行( id 为 1)的 select_type 为 primary,表示的最外层的查询;id 为 1,是最小的,则最后执行;

    第2行( id 为 3)的 select_type 为 derived,表示的 from 后面的子查询,也叫派生表,别名为 der 的派生表;id 为 3,是最大的,则最先执行的;

    第3行(id 为 2)的 select_type 为 subquery,表示的 from 前面的子查询;id 为 2,是中间的,则在中间执行;

    • union:在 union 中的第二个和之后的 select 都为 union;
    explain select 1 union all select 1;
    

    在这里插入图片描述

    table 列

    这一列表示 explain 的一行正在访问哪个表。

    当 from 子句中有子查询时,table列是 < derivenN > 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查询。

    当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的select 行 id。

    NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表

    explain select min(id) from film;
    

    在这里插入图片描述

    type列

    这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。
    依次从最优到最差分别为:
    system > const > eq_ref > ref > range > index > ALL
    一般来说,得保证查询达到 range 级别,最好达到 ref;

    • const, system:mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)system = 1。

    用于 primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。system是const的特例,表里只有一条数据时为 system;(使用主键或者唯一索引的时候会出现)

    set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
    explain extended select * from (select * from film where id = 1) tmp;
    set session optimizer_switch='derived_merge=on'; #还原默认配置
    

    在这里插入图片描述

    • eq_ref:primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。

    这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种 type。
    (在连接查询的时候,使用了主键或唯一索引的全部字段)

    explain select * from film_actor left join film on film_actor.film_id = film.id;
    

    在这里插入图片描述

    说明: film_id:表的联合索引中的一个字段 ,但是 type 为 All;
    因为使用的 * 查询的,指的要查询所有的字段,但是 film_actor 表的 remark 字段没有建立索引的,所以需要全表扫描;

    • ref:相比 eq_ref,不使用唯一索引,而是使用普通索引 或者 唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行;

    (使用的普通索引 或 联合唯一索引的部分前缀)

    简单 select 查询,name是普通索引(非唯一索引)

    explain select * from film where name = 'film1';
    

    在这里插入图片描述
    关联表查询,idx_film_actor_id 是 film_id 和 actor_id 的联合索引,这里使用到了film_actor的左边前缀 film_id 部分。

    explain select film_id from film left join film_actor on film.id = film_actor.film_id;
    

    在这里插入图片描述

    • range:范围扫描通常出现在 in(), between , > , <, >= 等操作中。使用一个索引来检索给定范围的行。
    explain select * from actor where id > 1;
    

    在这里插入图片描述

    • index:扫描全表索引,通常比 All 快一些;
    explain select * from film;
    

    在这里插入图片描述
    film 表的所有字段都建立了索引,使用 * 查询,则 type 为 index;

    • ALL:即全表扫描,意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。
    explain select * from actor;
    

    在这里插入图片描述

    possible_keys 列

    这一列显示查询 可能 使用哪些 索引 来查找。
    explain 时可能出现 possible_keys 有值,而 key 显示 NULL 的情况,这是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
    如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能,然后用 explain 查看效果。

    key 列

    这一列显示mysql 实际 采用哪个 索引 来优化对该表的访问。
    如果没有使用索引,则该列是 NULL。如果想强制mysql使用或忽视 possible_keys 列中的索引,在查询中使用 force index、ignore index。

    explain select * from film where name = 'film1';
    

    在这里插入图片描述

    key_len列

    这一列显示了mysql 在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。
    film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成,并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列:film_id列来执行索引查找。

    explain select * from film_actor where film_id = 2;
    

    在这里插入图片描述
    key_len计算规则如下:

    • 字符串
      • char(n):n字节长度
      • varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
    • 数值类型
      • tinyint:1字节
      • smallint:2字节
      • int:4字节
      • bigint:8字节
    • 时间类型
      • date:3字节
      • timestamp:4字节
      • datetime:8字节

    注意:如果字段允许为 NULL,需要1字节记录是否为 NULL
    索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

    ref列

    这一列显示了在 key 列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)。

    rows列

    这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。扫描的索引可能的行数

    Extra列

    这一列展示的是额外信息。常见的重要值如下:
    Using index > Using index condition > Using where

    Using index:

    使用覆盖索引;覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。也就是查询的结果集中的所有字段都是在索引中的;

    explain select film_id from film_actor where film_id = 1;
    

    在这里插入图片描述

    remark 没有索引,所以

    explain select film_id,remark from film_actor where film_id = 1;
    

    在这里插入图片描述

    Using index condition:

    查询的列不完全被索引覆盖,where条件中是一个联合索引的前导列的范围;

    explain select * from film_actor where film_id > 1
    

    在这里插入图片描述

    Using where:

    使用 where 语句来处理结果,查询的列未被索引覆盖;在查找使用索引的情况下,需要回表去查询所需的数据

    explain select * from actor where name = 'a';
    

    在这里插入图片描述

    Using temporary:

    mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。

    • actor.name没有索引,此时创建了张临时表来 distinct;
      (distinct 查询可能会使用到临时表)
    explain select distinct name from actor;
    

    在这里插入图片描述

    • film.name 建立了 idx_name 索引,此时查询时 extra 是 using index, 没有用临时表;
      将索引树加载到内存中,然后去重;
     explain select distinct name from film;
    

    在这里插入图片描述

    Using filesort:

    将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

    • actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录
    explain select * from actor order by name;
    

    在这里插入图片描述

    • film.name建立了idx_name索引,此时查询时extra是using index
    explain select * from film order by name;
    

    在这里插入图片描述

    Select tables optimized away:

    使用某些聚合函数(比如 max、min)来访问存在索引的某个字段;已经被 MySQL 优化过了;

    explain select min(id) from film;
    

    在这里插入图片描述

    索引实践

    CREATE TABLE `employees` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
        `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
        `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
        `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
        PRIMARY KEY (`id`),
        KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
    
    INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
    INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());
    INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
    

    全值匹配

    全值匹配指,所查询的字段都是索引字段。

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
    

    在这里插入图片描述

    name 是 联合索引 idx_name_age_position 的前导字段;
    key_len 为 74,name为 varchar(24),则 3 * 24 + 2 = 74,所以使用联合索引中的 name 字段走的索引;

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
    

    在这里插入图片描述

    name,age 是联合索引 idx_name_age_position 的字段;
    key_len 为 78,name为 varchar(24),则 3 * 24 + 2 = 74;age 为 int ,所以值为4 ; 74 + 4 = 78,所以使用联合索引中的 name,age 字段走的索引;

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
    

    在这里插入图片描述

    name,age,position 是联合索引 idx_name_age_position 的字段;

    key_len 为 140,name为 varchar(24) 类型,则 3 * 24 + 2 = 74;age 为 int 类型,所以值为4;position 为 varchaer(20),所以值为 3 * 20 + 2 = 62; 74 + 4 + 62 = 140,所以使用联合索引中的 name,age,position 字段走的索引;

    最左前缀法则

    如果建立了联合索引,要遵守最左前缀法则。指的是查询从联合索引的最左前列开始并且不跳过索引中的列。

    • 使用了联合索引的前两个字段查询;
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 22;
    

    在这里插入图片描述
    key_len 为 78,(3 * 24 + 2) + 4 = 78;走了 name 和 age 索引;

    • 使用联合索引的第1, 3 字段查询
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and position ='manager'
    

    在这里插入图片描述

    key_len 为 74,name 的 长刚好为 74,所以只有 name 走了索引;

    联合索引的底层存储是 先比较最前面的字段,最前面的字段一样则比较第2个字段,第2个一样才去比较第3个字段;第1个字段 name 去比较了,可以搜索到一部分, position 为 联合索引的第3个字段,但是索引在存储和查找时候不可能跳过第2个字段直接去比较第3个字段的,position 字段还是要列出大范围的数据做查询,因此 name 走了索引,position 没有走索引。

    索引上不使用(计算、函数、(自动or手动)类型转换)

    一般来说,只要给索引列增加了函数操作,MySQL的底层直接就不会使用索引去处理的。会导致索引失效而转向全表扫描。

    EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
    

    在这里插入图片描述

    • 给 hire_time 增加一个普通索引:
    ALTER TABLE `employees`
    ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
    EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
    

    在这里插入图片描述

    针对以上的SQL,转化为日期范围查询,就会走索引:

    EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';
    

    在这里插入图片描述
    还原最初索引状态

    ALTER TABLE `employees`
    DROP INDEX `idx_hire_time`;
    

    存储引擎不会使用索引中范围查找条件右边的列

    联合索引的字段顺序,范围查找之后的列都不会去走索引;

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
    

    在这里插入图片描述

    只会走前两个字段的索引。 第一个字段 name 使用的相等,所以可以找到具体的数据,第2个字段会缩小到一个范围,第3个字段是在这个范围里做相等的查询,还是会要将这个范围去遍历一遍的,所以只有 name,age 走了索引。

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
    

    在这里插入图片描述

    尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句

    EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
    

    在这里插入图片描述

    EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
    

    在这里插入图片描述

    mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

    EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
    

    在这里插入图片描述

    is null, is not null 也无法使用索引

    建议在建立字段的时候都设置为 not null,设置一个默认的值;

    EXPLAIN SELECT * FROM employees WHERE name is null
    

    在这里插入图片描述

    like以通配符开头(’$abc…’)mysql索引失效会变成全表扫描操作

    EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
    

    在这里插入图片描述

    EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'
    

    在这里插入图片描述

    模糊查找的时候,前模糊不走索引,后模糊会走索引;
    因为在后模糊的时候,我们知道了这个字段的前面有几个字符,我们在索引中比较的只去比较前面的几个字符就好了;

    解决like '%字符串%'索引不被使用的方法?

    • 使用覆盖索引,查询字段必须是建立覆盖索引字段
    EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
    

    在这里插入图片描述

    • 如果不能使用覆盖索引则可能需要借助搜索引擎

    字符串不加单引号索引失效

    EXPLAIN SELECT * FROM employees WHERE name = '1000';
    

    在这里插入图片描述

    EXPLAIN SELECT * FROM employees WHERE name = 1000; 
    

    在这里插入图片描述

    因为 name 为字符串类型,MySql 会做隐式的类型转换,做了类型的转换,所以不会去走索引;

    少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引,详见范围查询优化

    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
    

    在这里插入图片描述

    范围查询优化

    给年龄添加单值索引 :

    ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
    

    执行范围查询

    explain select * from employees where age >=1 and age <=2000; 
    

    在这里插入图片描述

    从执行计划的结果可以看出,以上的范围查找现在还会走索引;但是呢,我再多加点数据之后就不走索引了。

    在这里插入图片描述

    没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

    **优化方法:**可以将大的范围拆分成多个小范围

    explain select * from employees where age >=1 and age <=1000;
    explain select * from employees where age >=1001 and age <=2000;
    

    在这里插入图片描述

    还原最初索引状态:

    ALTER TABLE `employees` DROP INDEX `idx_age`;
    

    索引使用总结

    建立了一个联合索引: (a, b,c)

    where语句索引是否被使用
    where a = 3Y,使用到a
    where a = 3 and b = 5Y,使用到a,b
    where a = 3 and b = 5 and c = 4Y,使用到a,b,c
    where b = 3 或 where b = 3 and c = 4 或者 where c = 4N
    where a = 3 and c = 5使用到a,但是c不可以,b中间断了
    where a = 3 and b>4 and c = 5使用到a和b,c不能用在范围之后,b断了
    where a = 3 and b like ‘kk%’ and c = 4Y,使用到a,b,c
    where a = 3 and b like ‘%kk’ and c = 4Y,只用到a
    where a = 3 and b like ‘%kk%’ and c = 4Y,只用到a
    where a = 3 and b like ‘k%kk%’ and c = 4Y,使用到a,b,c

    like KK%相当于常量,所以走索引;%KK和%KK% 相当于范围,所以不走索引;
    没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

    展开全文
  • 多个单列索引联合索引的区别详解

    万次阅读 多人点赞 2018-06-24 17:40:58
    那么当查询条件为2个及以上时,我们是创建多个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试表(表记录...

    背景:
    为了提高数据库效率,建索引是家常便饭;那么当查询条件为2个及以上时,我们是创建多个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。


    一、联合索引测试

    注:Mysql版本为 5.7.20

    创建测试表(表记录数为63188):

    CREATE TABLE `t_mobilesms_11` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用户id,创建任务时的userid',
      `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手机号码',
      `billMonth` varchar(32) DEFAULT NULL COMMENT '账单月',
      `time` varchar(32) DEFAULT NULL COMMENT '收/发短信时间',
      `peerNumber` varchar(64) NOT NULL COMMENT '对方号码',
      `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
      `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-发送; RECEIVE-收取',
      `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
      `serviceName` varchar(256) DEFAULT NULL COMMENT '业务名称. e.g. 点对点(网内)',
      `fee` int(11) DEFAULT NULL COMMENT '通信费(单位分)',
      `createTime` datetime DEFAULT NULL COMMENT '创建时间',
      `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
      PRIMARY KEY (`id`),
      KEY `联合索引` (`userId`,`mobile`,`billMonth`)
    ) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手机短信详情'
    

    我们为userId, mobile, billMonth三个字段添加上联合索引!

    我们选择 explain 查看执行计划来观察索引利用情况:


    1.查询条件为 userid

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222'
    

    这里写图片描述

    可以通过key看到,联合索引有效


    2.查询条件为 mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'
    

    这里写图片描述
    可以看到联合索引无效


    3.查询条件为 billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'
    

    这里写图片描述
    联合索引无效


    4.查询条件为 userid and mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'
    

    这里写图片描述
    联合索引有效


    5.查询条件为 mobile and userid

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  mobile='13281899972' AND userid='2222' 
    

    这里写图片描述
    在4的基础上调换了查询条件的顺序,发现联合索引依旧有效


    6.查询条件为 userid or mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'
    

    这里写图片描述
    and 换成 or,发现联合所索引无效


    7.查询条件为 userid and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND billMonth='2018-04'
    

    这里写图片描述
    这两个条件分别位于联合索引位置的第一和第三,测试联合索引依旧有效


    8.查询条件为 mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    这两个条件分别位于联合索引位置的第二和第三,发现联合索引无效


    9.查询条件为 userid and mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    所有条件一起查询,联合索引有效!(当然,这才是最正统的用法啊!)


    二、单列索引测试

    创建三个单列索引:
    这里写图片描述

    1.查询条件为 userid and mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    我们发现三个单列索引只有 userid 有效(位置为查询条件第一个),其他两个都没有用上。

    那么为什么没有用上呢?按照我们的理解,三个字段都加索引了,无论怎么排列组合查询,应该都能利用到这三个索引才对!

    其实这里其实涉及到了mysql优化器的优化策略!当多条件联合查询时,优化器会评估用哪个条件的索引效率最高!它会选择最佳的索引去使用,也就是说,此处userid 、mobile 、billMonth这三个索引列都能用,只不过优化器判断使用userid这一个索引能最高效完成本次查询,故最终explain展示的key为userid。


    2.查询条件为 mobile and billMonth

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
    

    这里写图片描述
    我们发现此处两个查询条件只有 mobile 生效(位置也为查询条件第一个)


    3.查询条件为 userid or mobile

    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' OR mobile='13281899972' 
    

    这里写图片描述
    这次把 and 换成 or,发现两个查询条件都用上索引了!

    我们在网上可能常常看到有人说or会导致索引失效,其实这并不准确。而且我们首先需要判断用的是哪个数据库哪个版本,什么引擎?

    比如我用的是mysql5.7版本,innodb引擎,在这个环境下我们再去讨论索引的具体问题。

    关于or查询的真相是:
    所谓的索引失效指的是:假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法,没啥问题。

    此刻需要注意type类型为index_merge
    我查资料说mysql 5.0 版本之前 使用or只会用到一个索引(即使如上我给userid和mobile都建立的单列索引),但自从5.0版本开始引入了index_merge索引合并优化!也就是说,我们现在可以利用上多个索引去优化or查询了。

    index_merge作用:
    1、索引合并是把几个索引的范围扫描合并成一个索引。
    2、索引合并的时候,会对索引进行并集,交集或者先交集再并集操作,以便合并成一个索引。
    3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

    index_merge应用场景:

    1.对OR语句求并集,如查询SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"时,如果c1和c2列上分别有索引,可以按照c1和c2条件进行查询,再将查询结果合并(union)操作,得到最终结果

    2.对AND语句求交集,如查询SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"时,如果c1和c2列上分别有索引,可以按照c1和c2条件进行查询,再将查询结果取交集(intersect)操作,得到最终结果

    3.对AND和OR组合语句求结果


    三、结论

    通俗理解:
    利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处

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


    重点:

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


    最左前缀原则:

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


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

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


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


    联合索引本质:

    当创建**(a,b,c)联合索引时,相当于创建了(a)单列索引**,(a,b)联合索引以及**(a,b,c)联合索引**
    想要索引生效的话,只能使用 a和a,b和a,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到!
    注:这个可以结合上边的 通俗理解 来思考!


    其他知识点:

    1、需要加索引的字段,要在where条件中
    2、数据量少的字段不需要加索引;因为建索引有一定开销,如果数据量小则没必要建索引(速度反而慢)
    3、避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
    4、联合索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高


    最后的说明:

    网上关于索引优化等文章太多了,针对各个数据库各个版本各种引擎都可能存在不一样的说法

    我们的SQL引擎自带的优化也越来越强大,说不定你的某个SQL优化认知,其SQL引擎在某次升级中早就自优化了。

    所以要么跟进官方文档,要么关注数据库大牛的最新文章,要么在现有数据库环境下自己去亲手测试!

    数据库领域的水很深。。大家加油。。共勉 ~

    展开全文
  • 联合索引

    2020-06-16 15:08:42
    联合索引(各种索引) 聚集索引和非聚集索引 数据库中B+树索引可以分为聚集索引和非聚集索引(辅助索引) 聚集索引 每张表只有一个聚集索引,且是建立在主键上面的。 主键索引 在InnoDB存储引擎中,每张表都有...
  • 联合索引:MySQL中使用多个字段同时建立一个索引联合索引。 在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序一次使用,否则无法命中索引。 在建立联合索引的时候应该注意索引列的顺序,一般情况下,...
  • 复合索引 /多列索引 /联合索引 /组合索引,一个意思,不同叫法。 含有多个列字段的索引 联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2. 例如: 创建数据库表 CREATE TABLE `test` ('aaa'...
  • MySQL联合索引

    万次阅读 2020-06-02 22:31:35
    联合索引概念: 联合索引又叫复合索引,即一个覆盖表中两列或者以上的索引,例如: index_name(column a,column b) 1 创建方式 执行alter table语句时创建 alter table table_name add index index_name(column_list)...
  • MySQL B+ 树的索引-联合索引

    千次阅读 2019-03-26 22:34:00
    联合索引 联合索引是指对表上的多个列进行索引联合索引的创建方法与单个索引创建的方法一样,不同之处是有多个索引列。 如下图: 创建一个two_key 表,并且id_fid是联合索引联合的列是(id, fid) 那么...
  • 单列索引联合索引的区别

    千次阅读 2018-09-28 09:49:54
    多个单列索引联合索引的区别详解 单列索引联合索引的区别
  • 单列索引联合索引

    2020-12-13 20:56:03
    联合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用,如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓...
  • 索引也分为很多种(聚集、非聚集、联合索引等),数据结构主要有哈希索引和B+树等,哈希索引在单个查询性能上很强大,但不适合做范围查询。以下讨论主要是建立在B+树索引上面的。 索引一般采用B+树的数据结构,B+树...
  • 在《面试官:为啥加了索引查询会变快?》一文中,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的 我们一起来...
  • mysql普通索引以及联合索引介绍

    千次阅读 2019-03-16 17:57:45
    mysql普通索引以及联合索引介绍 命名规则:表名_字段名 1、需要加索引的字段,要在where条件中 2、数据量少的字段不需要加索引 3、如果where条件中是OR关系,加索引不起作用 4、符合最左原则 ...
  • 索引联合索引看似很简单但是往往不一定用的对。 假设数据库2个字段a,b都是查询条件 第一个问题:是建立2个索引还是一个联合索引? 第二个问题:如果建立联合索引那么字段的顺序有什么讲究? 原则: 1.如果...
  • 数据库索引联合索引基本知识

    千次阅读 2015-03-27 11:26:06
    数据库索引联合索引基本知识
  • mysql 联合索引生效的条件、索引失效的条件

    万次阅读 多人点赞 2019-02-23 10:11:30
    1.联合索引失效的条件 联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key...
  • mysql普通索引联合索引测试

    千次阅读 2018-11-21 12:40:20
    索引就用空间来换取时间 ...1 联合索引的第一个字段可以当普通索引来用, 即比如我的联合索引是name+source, 那么我只拿name当where当条件也会命中索引, 但是用source就不会了, 查询全部的数据 2 如果只是两...
  • 创建时不是联合索引,而是唯一索引(player_id),更改为联合索引 CREATE TABLE `willow_player` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `player_id` bigint(16) NOT NULL DEFAULT '0' unique, `award_type...
  • mysql联合索引

    千次阅读 2018-08-04 16:09:58
    1.联合索引 1.1概念 联合索引又叫复合索引,即一个覆盖表中两列或者以上的索引 ,例如:index (column a,column b)。 1.2创建方式 1.2.1执行alter table语句时创建 alter table table_name add index index...
  • MySQL联合索引原理解析

    千次阅读 2018-12-09 13:36:00
    联合索引又叫复合索引,是MySQL的InnoDB引擎中的一个索引方式,如果一个系统频繁地使用相同的几个字段查询结果,就可以考虑建立这几个字段的联合索引来提高查询效率。 如何建立索引 举个例子: create table `table_...
  • 那么当查询条件为2个及以上时,我们是创建多个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试表(表记录数...
  • 联合索引(多列索引

    万次阅读 多人点赞 2018-08-07 15:37:09
    联合索引是指对表上的多个列进行索引联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2. 最左匹配原则 假定上图联合索引的为(a,b)。联合索引也是一棵B+树,不同的是B+树在对索引a排序...
  • 命名规则:表名_字段名 1、需要加索引的字段,要在where条件中 2、数据量少的字段不需要加索引 3、如果where条件中是OR...联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用
  • elasticsearch通过顶部多索引,实现联合查询

    万次阅读 热门讨论 2019-05-06 15:15:38
    一、前言       之前试了下多索引查询,就是索引以...elasticsearch的多索引联合查询以及范围日期查询示例 背景:使用es-php + es7.0 二、正文 1、首先索引部分还是以数组的形式 '...
  • 一. 索引的使用: 1. 主键(默认是自带索引的)和外键...1. 查询条件中出现联合索引第一列或全部则能利用联合索引 2. 只要联合条件全部在 3. 查询条件中没有出现第一列,而出现第二列或者第三列都不会利用上联合
  • 简单描述MySQL中,索引,主键,唯一索引联合索引 的区别,对数据库的性能有什么影响 索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分),它们 包含着对数据表里所有记录的引用指针。 普通索引...
  • 联合索引(复合索引)和单个索引

    千次阅读 2019-02-23 15:56:41
    那么当查询条件为2个及以上时,我们是创建多个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试表(表记录数...
  • mysql聚集索引,非聚集索引联合索引 mysql索引是一个排好序的数据结构,mysql底层选用的是B+树结构,会自动将索引从左往右从小到大依次排好序,如下图: 看叶子节点,可以发现是从左到右从小到大排好序的结构。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 115,048
精华内容 46,019
关键字:

索引联合