精华内容
下载资源
问答
  • 5分钟搞懂MySQL - 索引下推优化

    千次阅读 多人点赞 2021-03-16 01:23:52
    对于长期与MySQL同流合污的朋友们来说,或许,“索引下推优化”这个词并不陌生,嗯。。经常听到,但是MySQL的这个“优化”到底优化了啥?就懵懵懂懂了,反正不是公司优化我就行了是吧。。来,让我们继续快乐的卷下去...

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL江湖路 | 专栏目录

      对于长期与MySQL同流合污的朋友们来说,或许,“索引下推优化”这个词并不陌生,嗯。。经常听到,但是MySQL的这个“优化”到底优化了啥?就懵懵懂懂了,反正不是公司优化我就行了是吧。。来,让我们继续快乐的卷下去~

    在这里插入图片描述

      其实呢,这个索引下推优化起源于MySQL5.6版本,全名叫:“索引条件下推”,英文名字 Index Condition Pushdown,我们叫他 ICP吧,ICP的诞生主要是为了进一步提高B+Tree索引查询的可用性。

      它的作用通俗一些的话,我们举个栗子。

      如下,在表 T 创建联合索引 index(name,age)

    select * from T where name like '提莫%' and age = 100
    • MySQL没有索引下推优化时:

      对于联合索引index(name,age),我们知道,根据B+Tree天然有序的存储特性,LIKE + 右侧模糊匹配虽可以使用到name索引,但模糊匹配后得到的结果变成无序,所以后面条件无法再使用到索引,因此需回表提取出name like '提莫%'结果集后,再通过普通查询得到age = 100的最终结果。

      那么 age 字段的索引就这么浪费了么?MySQL想白嫖我服务器资源?
    在这里插入图片描述
      通过参考李海翔老师的流程图,我们可以看出,没有索引下推的情况下,第三步从索引树上取到 name like ‘提莫%’ 数据后,就回表了,后续再处理where剩下的条件,剩下的条件在我们这里也就是 age = 100;相当于多了一次查询过滤操作。

      机灵的小伙伴发现,索引字段换个顺序不就得了?聪明~ 这确实是5.6之前的一种处理方式,但缺点也很明显,比如字段过滤数据能力降低,没了免费午餐(排序)等~

    • MySQL引入了ICP优化后;
      在这里插入图片描述

      如图,在索引内部取到name结果之后(步骤3),步骤4就顺便判断了结果中的age是否等于100,对于不等于100的记录直接跳过,因此在index(name,age)这棵索引树中直接匹配到了结果记录,减少了完整查询记录(一条完整元组)读取的个数,此时拿着结果集的id去主键索引树中回表查询全部数据,减少了二次查询时间,I/O次数也会减少。

    Cool~

    mysql> explain select name from T where name like '提莫%' and age = 100;
    +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
    | id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
    +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
    |  1 | SIMPLE      | T     | NULL       | range | index_name    | index_name | 72      | NULL |    3 |    25.00 | Using index condition    |
    +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
    1 row in set, 1 warning (0.00 sec)
    

      当你在使用Explain解析SQL时,可以看出Extra的值为Using index condition,表示已经使用了索引下推。

    附、一张有故事的照片(十五)

    在这里插入图片描述

    吉林的一位母亲因癌症晚期
    担心去世后照顾不了孩子
    在她生命的最后一段时间里
    给儿子织完了25岁之前需要的所有毛裤

    展开全文
  • 在表记录不多的情况下会选择全表扫描like KK% 一般情况都会走索引索引下推(Index Condition Pushdown,ICP)为什么范围查找Mysql没有用索引下推优化?Mysql如何选择合适的索引trace工具用法: 前言 前面几个章节,...


    前言

    前面几个章节,只是了解到了一些索引的使用原则,以及explain关键字查看具体使用的什么索引。接下来两个章节来思考如何更好地使用索引。

    示例表

    drop table `employees`;
    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=1 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());
    -- 插入一些示例数据
     drop procedure if exists insert_emp;
     delimiter ;;
     create procedure insert_emp()
     begin
     declare i int;
     set i=1;
     while(i<=100000)do
     insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
     set i=i+1;
     end while;
     end;;
     delimiter ;
     call insert_emp();
    

    联合索引第一个字段用范围不会走索引

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

    在这里插入图片描述

    mysql内部可能觉得第一个字段就用范围,结果集应该很大,回表效率不高,还不如就全表扫描

    强制走索引

    EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
    

    在这里插入图片描述

    虽然使用了强制走索引让联合索引第一个字段范围查找也走索引,扫描的行rows看上去也少了点,但是最终查找效率不一定比全表 扫描高,因为回表效率不高。

    覆盖索引优化

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

    在这里插入图片描述

    ken_len列的74是这样来的,当我们用utf-8的编码方式,计算方法就为3 * n + 2,而我们当时建表的时候,name字段是24位,也就是3 * 24 + 2 = 74,所以索引是name

    in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描

    EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
    

    在这里插入图片描述

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

    在这里插入图片描述

    like KK% 一般情况都会走索引

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

    在这里插入图片描述

    索引下推(Index Condition Pushdown,ICP)

    like KK%其实就是用到了索引下推优化

    对于辅助的联合索引(name,age,position),正常情况按照最左前缀原则,SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’ 这种情况只会走name字段索引,因为根据name字段过滤完,得到的索引行里的age和 position是无序的,无法很好的利用索引。

    在MySQL5.6之前的版本,这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引,然后拿这些索引对应的主键逐个回表,到主键索 引上找出相应的记录,再比对age和position这两个字段的值是否符合。

    MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可 以有效的减少回表次数。使用了索引下推优化后,上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后,同时还会在索引里过 滤age和position这两个字段,拿着过滤完剩下的索引对应的主键id再回表查整行数据。 索引下推会减少回表次数,对于innodb引擎的表索引下推只能用于二级索引,innodb的主键索引(聚簇索引)树叶子节点上保存的是全 行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。

    为什么范围查找Mysql没有用索引下推优化?

    估计应该是Mysql认为范围查找过滤的结果集过大,like KK% 在绝大多数情况来看,过滤后的结果集比较小,所以这里Mysql选择给 like KK% 用了索引下推优化,当然这也不是绝对的,有时like KK% 也不一定就会走索引下推。

    Mysql如何选择合适的索引

     EXPLAIN select * from employees where name > 'a'
    

    在这里插入图片描述

    如果用name索引需要遍历name字段联合索引树,然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据,成本比全表扫描 还高,可以用覆盖索引优化,这样只需要遍历name字段的联合索引树就能拿到所有结果,如下:

    EXPLAIN select name,age,position from employees where name > 'a' ;
    

    在这里插入图片描述

    对于 name>‘a’ 和 name>‘zzz’ 的执行结果,mysql最终是否选择走索引或者一张表涉及多个索引,mysql最 终如何选择索引,可以用trace工具来一查究竟,开启trace工具会影响mysql性能,所以只能临时分析sql使用,用 完之后立即关闭。

     EXPLAIN select * from employees where name > 'zzz' ;
    

    在这里插入图片描述

    trace工具用法:

    set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
    select * from employees where name > 'a' order by position;
    SELECT * FROM information_schema.OPTIMIZER_TRACE;
    

    结果集2中trace的内容

    {
      "steps": [
        {
          "join_preparation": { --预备工作
            "select#": 1,
            "steps": [
              {
                "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
              }
            ] /* steps */
          } /* join_preparation */
        },
        {
          "join_optimization": { --进行优化 
            "select#": 1,
            "steps": [
              {
                "condition_processing": {--条件处理
                  "condition": "WHERE",
                  "original_condition": "(`employees`.`name` > 'a')",
                  "steps": [
                    {
                      "transformation": "equality_propagation",
                      "resulting_condition": "(`employees`.`name` > 'a')"
                    },
                    {
                      "transformation": "constant_propagation",
                      "resulting_condition": "(`employees`.`name` > 'a')"
                    },
                    {
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "(`employees`.`name` > 'a')"
                    }
                  ] /* steps */
                } /* condition_processing */
              },
              {
                "substitute_generated_columns": { -- 替换生成的列
                } /* substitute_generated_columns */
              },
              {
                "table_dependencies": [ -- 表的依赖关系
                  {
                    "table": "`employees`",
                    "row_may_be_null": false,
                    "map_bit": 0,
                    "depends_on_map_bits": [
                    ] /* depends_on_map_bits */
                  }
                ] /* table_dependencies */
              },
              {
                "ref_optimizer_key_uses": [ -- 使用键
                ] /* ref_optimizer_key_uses */
              },
              {
                "rows_estimation": [ -- 预估表的访问成本
                  {
                    "table": "`employees`",
                    "range_analysis": {
                      "table_scan": { -- 全表扫描情况
                        "rows": 100099, -- 扫描行数
                        "cost": 10084.3 -- 扫描成本
                      } /* table_scan */,
                      "potential_range_indexes": [ -- 查询可能用到的索引
                        {
                          "index": "PRIMARY", -- 主键索引
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "idx_name_age_position", -- 辅助索引
                          "usable": true,
                          "key_parts": [
                            "name",
                            "age",
                            "position",
                            "id"
                          ] /* key_parts */
                        }
                      ] /* potential_range_indexes */,
                      "setup_range_conditions": [ -- 设置范围条件
                      ] /* setup_range_conditions */,
                      "group_index_range": {
                        "chosen": false,
                        "cause": "not_group_by_or_distinct"
                      } /* group_index_range */,
                      "skip_scan_range": {
                        "potential_skip_scan_indexes": [
                          {
                            "index": "idx_name_age_position",
                            "usable": false,
                            "cause": "query_references_nonkey_column"
                          }
                        ] /* potential_skip_scan_indexes */
                      } /* skip_scan_range */,
                      "analyzing_range_alternatives": { -- 分析各个索引的使用成本
                        "range_scan_alternatives": [
                          {
                            "index": "idx_name_age_position", 
                            "ranges": [
                              "a < name" -- 索引使用范围
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": false, -- 使用该索引获取的记录是否按照主键排序
                            "using_mrr": false,
                            "index_only": false,-- 是否使用覆盖索引
                            "rows": 50049, -- 索引扫描行数
                            "cost": 17517.4, -- 索引扫描成本
                            "chosen": false,-- 是否选择该索引
                            "cause": "cost"
                          }
                        ] /* range_scan_alternatives */,
                        "analyzing_roworder_intersect": { 
                          "usable": false,
                          "cause": "too_few_roworder_scans"
                        } /* analyzing_roworder_intersect */
                      } /* analyzing_range_alternatives */
                    } /* range_analysis */
                  }
                ] /* rows_estimation */
              },
              {
                "considered_execution_plans": [ -- 考虑执行计划
                  {
                    "plan_prefix": [
                    ] /* plan_prefix */,
                    "table": "`employees`",
                    "best_access_path": { -- 最优访问路径
                      "considered_access_paths": [ -- 最终选择的访问路径
                        {
                          "rows_to_scan": 100099,
                          "access_type": "scan", -- 访问类型: 为scan,全表扫描
                          "resulting_rows": 100099,
                          "cost": 10082.2,
                          "chosen": true, -- 确定选择
                          "use_tmp_table": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100, -- 行过滤百分比
                    "rows_for_plan": 100099,
                    "cost_for_plan": 10082.2,
                    "sort_cost": 100099,
                    "new_cost_for_plan": 110181,
                    "chosen": true
                  }
                ] /* considered_execution_plans */
              },
              {
                "attaching_conditions_to_tables": { --将条件附加到表上
                  "original_condition": "(`employees`.`name` > 'a')",
                  "attached_conditions_computation": [
                  ] /* attached_conditions_computation */,
                  "attached_conditions_summary": [ -- 附加条件概要
                    {
                      "table": "`employees`",
                      "attached": "(`employees`.`name` > 'a')"
                    }
                  ] /* attached_conditions_summary */
                } /* attaching_conditions_to_tables */
              },
              {
                "optimizing_distinct_group_by_order_by": {
                  "simplifying_order_by": {
                    "original_clause": "`employees`.`position`",
                    "items": [
                      {
                        "item": "`employees`.`position`"
                      }
                    ] /* items */,
                    "resulting_clause_is_simple": true,
                    "resulting_clause": "`employees`.`position`"
                  } /* simplifying_order_by */
                } /* optimizing_distinct_group_by_order_by */
              },
              {
                "reconsidering_access_paths_for_index_ordering": {
                  "clause": "ORDER BY",
                  "steps": [
                  ] /* steps */,
                  "index_order_summary": {
                    "table": "`employees`",
                    "index_provides_order": false,
                    "order_direction": "undefined",
                    "index": "unknown",
                    "plan_changed": false
                  } /* index_order_summary */
                } /* reconsidering_access_paths_for_index_ordering */
              },
              {
                "finalizing_table_conditions": [
                  {
                    "table": "`employees`",
                    "original_table_condition": "(`employees`.`name` > 'a')",
                    "final_table_condition   ": "(`employees`.`name` > 'a')"
                  }
                ] /* finalizing_table_conditions */
              },
              {
                "refine_plan": [ -- 精简计划
                  {
                    "table": "`employees`"
                  }
                ] /* refine_plan */
              },
              {
                "considering_tmp_tables": [
                  {
                    "adding_sort_to_table": "employees"
                  } /* filesort */
                ] /* considering_tmp_tables */
              }
            ] /* steps */
          } /* join_optimization */
        },
        {
          "join_execution": { -- 执行
            "select#": 1,
            "steps": [
              {
                "sorting_table": "employees",
                "filesort_information": [
                  {
                    "direction": "asc",
                    "expression": "`employees`.`position`"
                  }
                ] /* filesort_information */,
                "filesort_priority_queue_optimization": {
                  "usable": false,
                  "cause": "not applicable (no LIMIT)"
                } /* filesort_priority_queue_optimization */,
                "filesort_execution": [
                ] /* filesort_execution */,
                "filesort_summary": {
                  "memory_available": 262144,
                  "key_size": 40,
                  "row_size": 190,
                  "max_rows_per_buffer": 1379,
                  "num_rows_estimate": 100099,
                  "num_rows_found": 100003,
                  "num_initial_chunks_spilled_to_disk": 31,
                  "peak_memory_used": 269496,
                  "sort_algorithm": "std::stable_sort",
                  "sort_mode": "<fixed_sort_key, packed_additional_fields>"
                } /* filesort_summary */
              }
            ] /* steps */
          } /* join_execution */
        }
      ] /* steps */
    }
    

    全表扫描的成本小于使用索引查找的成本,所以最后选择了全表扫描

    set session optimizer_trace="enabled=off"; ‐‐关闭trace
    

    Order by与Group by优化

    • Case1:
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and position = 'dev' ORDER BY age; 
    

    在这里插入图片描述
    利用最左前缀法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序过程中,因为Extra字段里没有using filesort

    • Case 2:
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position;
    

    在这里插入图片描述

    从explain的执行结果来看:key_len=74,查询使用了name索引,由于用了position进行排序,跳过了 age,出现了Using filesort。

    • Case 3:
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age,position;
    

    在这里插入图片描述

    查找只用到索引name,age和position用于排序,无Using filesort。

    • Case 4:
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position,age;
    

    在这里插入图片描述

    和Case 3中explain的执行结果一样,但是出现了Using filesort,因为索引的创建顺序为 name,age,position,但是排序的时候age和position颠倒位置了。

    • Case 5:
    EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 18 ORDER BY position,age;
    

    在这里插入图片描述

    与Case 4对比,在Extra中并未出现Using filesort,因为age为常量,在排序中被优化,所以索引未颠倒, 不会出现Using filesort。

    • Case 6:
    EXPLAIN SELECT * FROM employees WHERE name = 'zhuge' ORDER BY age asc,position DESC;
    

    在这里插入图片描述

    虽然排序的字段列与索引顺序一样,且order by默认升序,这里position desc变成了降序,导致与索引的 排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式

    • Case 7:
    EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','zhuge') ORDER BY age,position;
    

    在这里插入图片描述

    对于排序来说,多个相等条件也是范围查询

    • Case 8:
    EXPLAIN SELECT * FROM employees WHERE name > 'a' ORDER BY name;
    

    在这里插入图片描述

    可以用覆盖索引优化

    EXPLAIN SELECT name,age,position FROM employees WHERE name > 'a' ORDER BY name;
    

    在这里插入图片描述

    优化小结:

    • MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index 效率高,filesort效率低。
    • order by满足两种情况会使用Using index。
      • order by语句使用索引最左前列。
      • 使用where子句与order by子句条件列组合满足索引最左前列。
    • 尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则。
    • 如果order by的条件不在索引列上,就会产生Using filesort。
    • 能用覆盖索引尽量用覆盖索引
    • group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意,where高于having,能写在where中 的限定条件就不要去having限定了。

    Using filesort文件排序原理详解

    filesort文件排序方式

    • 单路排序:
      一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;用trace工具可 以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
    • 双路排序(又叫回表排序模式):
      首先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID,然后在 sort buffer 中进行排序,排序完后需要再次取回其它需要的字段;用trace工具 可以看到sort_mode信息里显示< sort_key, rowid >

    MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来 判断使用哪种排序模式。

    • 如果 字段的总长度小于max_length_for_sort_data ,那么使用 单路排序模式;
    • 如果 字段的总长度大于max_length_for_sort_data ,那么使用 双路排序模∙式。

    不考虑mysql的底层数据结构和其他因素,进行剖析
    单路排序的详细过程:

    • 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
    • 根据主键 id 取出整行,取出所有字段的值,存入 sort_buffer 中
    • 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
    • 重复步骤 2、3 直到不满足 name = ‘zhuge’
    • 对 sort_buffer 中的数据按照字段 position 进行排序、
    • 返回结果给客户端

    双路排序的详细过程:

    • 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
    • 根据主键 id 取出整行,把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
    • 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
    • 重复 3、4 直到不满足 name = ‘zhuge’
    • 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
    • 遍历排序好的 id 和字段 position,按照 id 的值回到原表中取出 所有字段的值返回给客户端

    其实对比两个排序模式,单路排序会把所有需要查询的字段都放到 sort buffer 中,而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序,然后再通过主键回到原表查询需要的字段。

    如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了,可以适当把 max_length_for_sort_data 配置小点,让优化器选择使用双路排序算法,可以在sort_buffer 中一次排序更 多的行,只是需要再根据主键回到原表取数据。

    如果 MySQL 排序内存有条件可以配置比较大,可以适当增大 max_length_for_sort_data 的值,让优化器 优先选择全字段排序(单路排序),把需要的字段放到 sort_buffer 中,这样排序后就会直接从内存里返回查询结果了。

    所以,MySQL通过 max_length_for_sort_data 这个参数来控制排序,在不同场景使用不同的排序模式, 从而提升排序效率。

    注意,如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序,但不能因为这个就随便增 大sort_buffer(默认1M),mysql很多参数设置都是做过优化的,不要轻易调整。

    索引设计原则

    代码先行,索引后上

    主体业务功能开发完毕,把涉及到该表相关sql都要拿出来分析之后再建立 索引。

    联合索引尽量覆盖条件

    设计一个或者两三个联合索引(尽量少建单值索引),让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段,确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

    不要在小基数字段上建立索引

    索引基数是指这个字段在表里总共有多少个不同的值,比如一张表总共100万行记录,其中有个性别字段, 其值不是男就是女,那么该字段的基数就是2。

    如果对这种小基数字段建立索引的话,还不如全表扫描了,因为你的索引树里就包含男和女两种值,根本没 法进行快速的二分查找,那用索引就没有太大的意义了。

    一般建立索引,尽量使用那些基数比较大的字段,就是值比较多的字段,那么才能发挥出B+树快速二分查 找的优势来。

    长字符串我们可以采用前缀索引

    尽量对字段类型较小的列设计索引,比如说什么tinyint之类的,因为字段类型较小的话,占用磁盘空间也会 比较小,此时你在搜索的时候性能也会比较好一点。

    当然,这个所谓的字段类型小一点的列,也不是绝对的,很多时候你就是要针对varchar(255)这种字段建立 索引,哪怕多占用一些磁盘空间也是有必要的。

    对于这种varchar(255)的大字段可能会比较占用磁盘空间,可以稍微优化下,比如针对这个字段的前20个 字符建立索引,就是说,对这个字段里的每个值的前20个字符放在索引树里,类似于 KEY index(name(20),age,position)。

    此时你在where条件里搜索的时候,如果是根据name字段来搜索,那么此时就会先到索引树里根据name 字段的前20个字符去搜索,定位到之后前20个字符的前缀匹配的部分数据之后,再回到聚簇索引提取出来 完整的name字段值进行比对。

    但是假如你要是order by name,那么此时你的name因为在索引树里仅仅包含了前20个字符,所以这个排 序是没法用上索引的, group by也是同理。所以这里大家要对前缀索引有一个了解。

    where与order by冲突时优先where

    在where和order by出现索引设计冲突时,到底是针对where去设计索引,还是针对order by设计索引?到 底是让where去用上索引,还是让order by用上索引?

    一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据,接着再进行排序。

    因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据,然后做排序的成本可 能会小很多。

    基于慢sql查询做优化

    可以根据监控后台的一些慢sql,针对这些慢sql查询做特定的索引优化。

    展开全文
  • [root@HE3 ~]# /usr/ local /mariadb/bin/mysql -uroot -S /tmp/mariadb.sockWelcome to the MariaDB monitor. Commands end with ; or g.Your MariaDB connection id is 26Server version: 10.1.16-MariaDB MariaDB...

    [root@HE3 ~]# /usr/ local /mariadb/bin/mysql -uroot -S /tmp/mariadb.sock

    Welcome to the MariaDB monitor. Commands end with ; or g.

    Your MariaDB connection id is 26

    Server version: 10.1.16-MariaDB MariaDB Server

    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

    MariaDB [(none)]> select version();

    + -----------------+

    | version() |

    + -----------------+

    | 10.1.16-MariaDB |

    + -----------------+

    1 row in set (0.00 sec)

    MariaDB [(none)]> use helei;

    Reading table information for completion of table and column names

    You can turn off this feature to get a quicker startup with -A

    Database changed

    MariaDB [helei]> show create table helei_stuG

    *************************** 1. row ***************************

    Table : helei_stu

    Create Table : CREATE TABLE `helei_stu` (

    `id` int (10) unsigned NOT NULL AUTO_INCREMENT,

    ` name ` varchar (10) NOT NULL DEFAULT '' ,

    `class` tinyint(3) unsigned NOT NULL DEFAULT '0' ,

    `score` tinyint(3) unsigned NOT NULL DEFAULT '0' ,

    PRIMARY KEY (`id`),

    KEY `idx_class_score` (`class`,`score`),

    KEY `idx_name` (` name `)

    ) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8

    1 row in set (0.00 sec)

    MariaDB [helei]> explain select * from helei_stu where class=2 and score >60;

    + ------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    + ------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+

    | 1 | SIMPLE | helei_stu | range | idx_class_score | idx_class_score | 2 | NULL | 8 | Using index condition |

    + ------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+-----------------------+

    1 row in set (0.00 sec)

    MariaDB [helei]> select version();

    + -----------------+

    | version() |

    + -----------------+

    | 10.1.16-MariaDB |

    + -----------------+

    1 row in set (0.00 sec)

    展开全文
  • 索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,该服务器会评估WHERE行的条件。启用ICP后,如果WHERE只使用索引中的...

    索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,该服务器会评估WHERE行的条件。启用ICP后,如果WHERE只使用索引中的列来评估部分 条件,MySQL服务器会推送这部分内容。WHERE条件下到存储引擎。然后,存储引擎通过使用索引条目来评估推送的索引条件,并且仅当满足该条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。

    指数条件下推优化的适用性受以下条件限制:

    ICP用于 range, ref, eq_ref,和 ref_or_null访问方法时,有必要访问完整的表行。

    ICP可用于表InnoDB 和MyISAM表,包括分区InnoDB和 MyISAM表。

    对于InnoDB表,ICP仅用于二级索引。ICP的目标是减少全行读取的数量,从而减少I / O操作。对于 InnoDB聚簇索引,已将完整记录读入InnoDB 缓冲区。在这种情况下使用ICP不会降低I / O.

    在虚拟生成列上创建的二级索引不支持ICP。InnoDB 支持虚拟生成列上的二级索引。

    引用子查询的条件无法下推。

    引用存储函数的条件无法下推。存储引擎无法调用存储的函数。

    触发条件无法下推。(有关触发条件的信息,请参见 第8.2.2.4节“使用EXISTS策略优化子查询”。)

    要了解此优化的工作原理,请首先考虑在不使用索引条件下推时索引扫描的进度:

    获取下一行,首先读取索引元组,然后使用索引元组找到并读取整个表行。

    测试WHERE适用于此表的条件部分。根据测试结果接受或拒绝该行。

    使用索引条件下推,扫描会像这样进行:

    获取下一行的索引元组(但不是完整的表行)。

    测试WHERE适用于此表的条件部分,并且只能使用索引列进行检查。如果不满足条件,则继续下一行的索引元组。

    如果满足条件,请使用索引元组来查找并读取整个表行。

    测试WHERE 适用于此表的条件的剩余部分。根据测试结果接受或拒绝该行。

    EXPLAIN使用“索引条件下推”时,输出显示 Using index condition在 Extra列中。它没有显示,Using index 因为当必须读取完整的表行时,这不适用。

    假设一个表包含有关人员及其地址的信息,并且该表的索引定义为 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的zipcode价值但不确定姓氏,我们可以这样搜索:

    SELECT * FROM people

    WHERE zipcode=‘95054‘

    AND lastname LIKE ‘%etrunia%‘

    AND address LIKE ‘%Main Street%‘;

    MySQL可以使用索引来扫描人 zipcode=‘95054‘。第二部分(lastname LIKE ‘%etrunia%‘)不能用于限制必须扫描的行数,因此,如果没有Index Condition Pushdown,此查询必须为所有拥有的人检索完整的表行 zipcode=‘95054‘。

    使用索引条件下推,MySQL lastname LIKE ‘%etrunia%‘在读取整个表行之前检查该 部分。这样可以避免读取与索引元组相对应的完整行,这些索引元组与zipcode条件匹配 但不符合 lastname条件。

    默认情况下启用索引条件下推。可以optimizer_switch通过设置index_condition_pushdown标志来控制 系统变量 :

    SET optimizer_switch = ‘index_condition_pushdown=off‘;

    SET optimizer_switch = ‘index_condition_pushdown=on‘;

    总结

    1.对于 where constant + like查询可以尝试使用联合索引

    // name和stu_id有联合索引

    explain select * from course where name = ‘ww‘ and stu_id like ‘%4%‘;

    2.对于 where constant + order by index column可以尝试使用联合索引;

    // name和stu_id有联合索引

    explain select * from course where name = ‘ww‘ order by stu_id;

    以上另种情况都会使用Using index condition。第一种是过滤like的模糊匹配,第二种是进行联合索引的排序。

    索引下推经常使用的场景:

    对于二级索引

    select的列不使用覆盖索引

    多条件查询(where中多条件,where + order by) + 联合索引

    参考

    原文:https://www.cnblogs.com/lxyit/p/9456679.html

    展开全文
  • MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过 程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数,如图所示 在这个过种中InnoDB 在 (name,age) 索引...
  • 大家觉得写还可以,可以点赞、收藏、关注一下...索引条件下推优化 概述 索引条件推(Index Condition Pushdown,简称ICP)是针对MySQL使用索引从表中检索行的情况进行的优化。并在Mysql5.6的版本上才推出。MySQL提供
  • 通过参考李海翔老师的流程图,我们可以看出,没有索引下推的情况,第三步从索引树上取到 name like ‘提莫%’ 数据后,就回表了,后续再处理where剩下的条件,剩下的条件在我们这里也就是 age = 100;相当于多了一...
  • Mysql内部认为范围查找过滤的结果集过大,like xx%大多数情况结果集较小,所以mysql选择给【like】用了索引下推优化;但不是绝对的,有时也不一定会走索引下
  • 上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这...
  • Mysql性能优化:什么是索引下推? 导读 本文章始发于本人公众号:码猿技术专栏,原创不易,谢谢关注推荐。 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不...
  • 索引下推

    2021-02-25 16:03:03
    当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索...
  • ICP 优化适用于 MySQL 利用索引从表里检索数据的场景。ICP 适用的场景ICP 用于访问方法是 range/ref/eq_ref/ref_or_null,且需要访问表的完整行记录。ICP适用于 InnoDB 和 MyISAM 的表,包括分区的表。对于 InnoDB ...
  • InnoDB首先会使用主键创建一个主键B+树索引和数据文件,此外还会通过联合索引(b,c,d)生成一个索引树,同样是B+树的结构,只不过它的data部分存储的是联合索引所在行的主键值(上图叶子节点紫色背景部分),这里...
  • MySQL中的索引下推

    2021-02-03 03:17:31
    索引下推用一句话总结是:索引下推是数据库检索数据过程中为减少回表次数而做的优化。首先介绍什么是数据库回表,回表是一种数据库检索过程。通常发生在使用二级索引检索非主索引数据的过程中。举个例子:usertest...
  • 覆盖索引索引下推③. Mysql如何选择合适的索引④. Order by与Group by优化⑤. filesort文件排序方式(了解)⑥. 索引设计原则 ①. 坏境准备 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `...
  • innodb的索引下推

    2021-02-14 16:12:28
    索引下推,是mysql优化联合索引查询的一种方案,叫做索引下推不如翻译为索引条件下推更合适(Index Condition Pushdown)简称ICP,因为他实际上是把where中的查询索引条件,下推给了存储引擎 本文涉及到的内容有: ...
  • 通俗的讲就是,如果索引的列在 select 所需获得的列中(因为在 mysql 中索引是根据索引列的值进行排序的,所以索引节点中存在该列中的部分值)或者根据一次索引查询就能获得记录就不需要回表,如果 select 所需获得...
  • 简单易懂的MySQL覆盖索引、前缀索引索引下推

    千次阅读 多人点赞 2021-07-17 20:08:04
    文章目录前言聚集索引/非聚集索引 前言 聚集索引/非聚集索引
  • sql综合前人的经验结果:索引下推是数据库检索数据过程当中为减小回表次数而作的优化。docker判断是否须要回表的是由mysql存储引擎控制,默认从mysql5.6版本开始支持。数据库下面用docker分别建立基于mysql5.5和...
  • MySQL小知识-索引下推

    2021-10-08 16:56:18
    索引下推(ICP)是针对MySQL使用索引从表中检索数据行的情况的优化。 在没有索引下推的情况,MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器,服务器再进行WHERE条件的判断,确认是否将...
  • 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给...
  • 索引条件下推,Index Condition Pushdown,简称ICP,是MySQL内部通过索引查询数据的一种优化方法,简单来说就是将原本需要在Server层对数据进行过滤的条件下推到了引擎层去做,在引擎层过滤更多的数据,这样从引擎层...
  • 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中,叶子...
  • Mysql 索引下推

    2021-04-16 17:19:52
    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 53,439
精华内容 21,375
关键字:

索引下推优化