MySQL索引优化

已于 2024-03-26 10:36:32 修改
阅读量897 收藏 11
点赞数 14
分类专栏： MySQL 文章标签： mysql sql
于 2024-03-21 22:53:50 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43135259/article/details/136904019
版权

示例

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());
INSERT INTO employees (age, position, hire_time)
SELECT
    FLOOR(RAND() * 40 + 20) AS age,
    CASE FLOOR(RAND() * 4)
        WHEN 0 THEN 'manager'
        WHEN 1 THEN 'engineer'
        WHEN 2 THEN 'dev'
        WHEN 3 THEN 'assistant'
    END AS position,
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365 * 10) DAY) AS hire_time
FROM
    (SELECT
        n1.n + n10.n * 10 + n100.n * 100 + n1000.n * 1000  AS id
    FROM
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n1
    CROSS JOIN
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n10
    CROSS JOIN
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n100
    CROSS JOIN
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n1000
    CROSS JOIN
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n10000
    ) AS numbers;
		
UPDATE employees SET name = CONCAT('番茄炒蛋', id) WHERE name not in ('LiLei','HanMeimei','Lucy');
		
-- 新建一张与上表一摸一样的表;数据量只插几条
	CREATE TABLE `employees_copy` (
  `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_copy(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees_copy(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees_copy(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

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

    explain select * from employees where name > 'LiLei' and age = 22 and position = 'dev'

    结论: 联合索引第一个字段就用范围查找不会走索引,mysql内部可能觉得第一个字段就用范围,结果集应该很大,回表效率不高,还不如就全表扫描

  2. 强制走索引

    explain select * from employees force index(idx_name_age_position) where name > 'LiLei' and age = 22 and position = 'dev'

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

    做个小实验:

    -- 关闭查询缓存
set global query_cache_size = 0;
set global query_cache_type = 0;
-- 执行时间0.205s
select * from employees where name > 'LiLei'
-- 执行时间0.437s
select * from employees force index(idx_name_age_position) where name > 'LiLei'

  3. 覆盖索引优化

    explain select name,age,position from employees where name > 'LiLei' and age = 22 and position = 'manager'

    在这里插入图片描述

  4. 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 = 'HanMeimie') and age = 22 and position = 'manager';

    在这里插入图片描述
    在这里插入图片描述

    数据量比较小时:

    explain select * from employees_copy where name in('LiLei','HanMeimei','Lucy') and age = 22 and position = 'manager';
explain select * from employees_copy where (name = 'LiLei' or name = 'HanMeimie') and age = 22 and position = 'manager';

    在这里插入图片描述
    在这里插入图片描述

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

    explain select * from employees where name like 'LiLei%' and age = 22 and position = 'manager';
explain select * from employees_copy where name like 'LiLei%' and age = 22 and position = 'manager';

    在这里插入图片描述
    在这里插入图片描述

    like KK%用到的是索引下推(Index Condition Pushdown,ICP)

    对于辅助的联合索引(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这两个字段是否符合.

    mysql5.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';

    在这里插入图片描述

    explain select * from employees where name < 'a';

    在这里插入图片描述

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

    trace工具用法

    set session optimizer_trace='enabled=on',end_markers_in_json=on; -- 开启trace工具
set session optimizer_trace='enabled=off'; -- 关闭trace工具

    • name>‘a’

      select * from employees where name > 'a' order by position;
select * from information_schema.OPTIMIZER_TRACE;

-- trace字段内容
{
  "steps": [
    {
      "join_preparation": { -- 第一阶段:SQL准备阶段,格式化SQL
        "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": { -- 第二阶段:SQL优化阶段
        "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": 100009, -- 扫描行数
                    "cost": 20421 -- 查询成本
                  } /* 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 */,
                  "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": 50004, -- 索引扫描的行数
                        "cost": 60006, -- 索引使用成本
                        "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": 100009,
                      "access_type": "scan", -- 访问类型:scan为全表扫描
                      "resulting_rows": 100009,
                      "cost": 20419,
                      "chosen": true, -- 确认选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100009,
                "cost_for_plan": 20419,
                "sort_cost": 100009,
                "new_cost_for_plan": 120428,
                "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 */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`employees`.`position`",
              "items": [
                {
                  "item": "`employees`.`position`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`employees`.`position`"
            } /* clause_processing */
          },
          {
            "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 */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": { -- 第三阶段:SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 100003,
              "examined_rows": 100003,
              "number_of_tmp_files": 34,
              "sort_buffer_size": 262056,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

      结论: 使用索引idx_name_age_position的成本为60006,全表扫描的成本为20421,全表扫描的成本低于索引.所以使用全表扫描

    • name<‘a’

      select * from employees where name < 'a' order by position;
select * from information_schema.OPTIMIZER_TRACE;

-- trace字段
{
  "steps": [
    {
      "join_preparation": { -- 第一阶段:sql准备阶段,格式化sql
        "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": { -- 第二阶段:sql优化阶段
        "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": 100009, -- 扫描行数
                    "cost": 20421 -- 查询成本
                  } /* 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 */,
                  "analyzing_range_alternatives": { -- 分析各个索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "name < a" -- 索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false, -- 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false, -- 使用使用覆盖所i一年
                        "rows": 1, -- 索引扫描行数
                        "cost": 2.21, -- 索引使用成本
                        "chosen": true -- 是否使用该索引
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_name_age_position",
                      "rows": 1,
                      "ranges": [
                        "name < a"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": { -- 最优访问路径
                  "considered_access_paths": [ -- 最终选择的访问路径
                    {
                      "rows_to_scan": 1,
                      "access_type": "range", -- 访问类型:range
                      "range_details": {
                        "used_index": "idx_name_age_position" -- 使用到的索引
                      } /* range_details */,
                      "resulting_rows": 1,
                      "cost": 2.41,
                      "chosen": true, -- 确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 2.41,
                "sort_cost": 1,
                "new_cost_for_plan": 3.41,
                "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 */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`employees`.`position`",
              "items": [
                {
                  "item": "`employees`.`position`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`employees`.`position`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`employees`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_name_age_position",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`",
                "pushed_index_condition": "(`employees`.`name` < 'a')",
                "table_condition_attached": null
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": { -- 第三阶段:sql执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 0,
              "examined_rows": 0,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 262056,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

      总结: 全表扫描的成本为20421,使用索引idx_name_age_position的成本为2.41,索引成本低于全表扫描.所以使用索引.

    常见sql深入优化

    常见优化

    • case1

      explain select * from employees where name = 'LiLei' and position = 'dev' order by age;

      在这里插入图片描述

      总结: 利用最左前缀法则:中间字段不能断,因此查询只用到了name索引,从key_len=74也能看出,age索引列用在排序过程中,因此Extra字段里没有 Using filesort

    • case2

      explain select * from employees where name = 'LiLei' order by position;

      在这里插入图片描述

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

    • case3

      explain select * from employees where name = 'LiLei' order by age, position;

      在这里插入图片描述

      总结: 查询只用到索引name,age和position用于排序,Extra字段无 Using filesort

    • case4

      explain select * from employees where name = 'LiLei' order by position,age;

      在这里插入图片描述

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

    • case5

      explain select * from employees where name = 'LiLei' and age = 20 order by position,age;

      在这里插入图片描述

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

    • case6

      explain select * from employees where name = 'LiLei' order by age asc, position desc;

      在这里插入图片描述

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

    • case7

      explain select * from employees where name in ('LiLei','HanMeimei') order by age, position;

      在这里插入图片描述

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

    • case8

      explain select * from employees where name > 'a' order by name;

      在这里插入图片描述

      考虑用覆盖索引优化:

      explain select name,age,position from employees where name > 'a' order by name;

      在这里插入图片描述

    优化总结

    1. MySQL支持两种方式的排序 filesort和 index,Using index是指MySQL 扫描索引本身完成排序.index效率高,filesort效率低.
    2. order by满足两种情况会使用Using index
      1. order by 语句使用索引最左前列
      2. 使用where子句与order by子句 条件列组合满足索引最左前列
    3. 尽量在 索引列上完成排序,遵循 **索引建立(索引创建的顺序)**时的最左前缀法则.
    4. 如果order by的条件不在索引列就会产生Using filesort
    5. 能用覆盖索引尽量使用覆盖索引
    6. 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,那么使用双路排序模式
    示例验证下两种排序方式

    • 单路排序

      select * from employees where name = 'LiLei' order by position;
select * from information_schema.OPTIMIZER_TRACE;

-- trace字段(只展示排序信息)
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": { -- 文件排序信息
              "rows": 1, -- 预计扫描行数
              "examined_rows": 1, -- 参与排序的行
              "number_of_tmp_files": 0, -- 使用临时文件的个数,这个值如果为0代表全部使用的sort_buffer内存排序,否则使用磁盘文件排序
              "sort_buffer_size": 262056, -- 排序的缓存大小,单位byte
              "sort_mode": "<sort_key, packed_additional_fields>" -- 排序方式,这里使用的单路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }

    • 双路排序

      双路排序需要调整max_length_for_sort_data的值

      set max_length_for_sort_data = 10; -- 表中长度肯定超过10字节
select * from employees where name = 'LiLei' order by position;
select * from information_schema.OPTIMIZER_TRACE;

-- trace字段(只展示排序信息)
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": { -- 文件排序信息
              "rows": 1, -- 预计扫描行数
              "examined_rows": 1, -- 参与排序的行
              "number_of_tmp_files": 0, -- 使用临时文件的个数,这个值如果为0代表全部使用sort_buffer内存排序,否则使用磁盘文件排序
              "sort_buffer_size": 262136, -- 排序缓存的大小,单位是byte
              "sort_mode": "<sort_key, rowid>" -- 排序方式,这里使用的是双路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }

    单路排序详细过程:

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

    双路排序详细过程:

    1. 从索引name找到第一个满足name = 'LiLei’条件的主键id
    2. 根据主键id去取整条记录,把排序字段position和主键id这两个字段放到sort_buffer中
    3. 从索引name找到下一个满足 name = 'LiLei’条件的主键id
    4. 重复步骤2,3直到不满足name = ‘LiLei’
    5. 对sort_buffer中的字段position和主键id按照字段position进行排序
    6. 遍历排序号的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很多参数设置都是做过优化的,不要轻易调整.

    索引设计原则

    1. 代码先行,索引后上

      不要建完表马上就建立索引,一般应该等到主体业务功能开发完毕,把设计到该表相关sql都要拿出来分析之后再建立索引

    2. 联合索引尽量覆盖条件

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

    3. 不要再小基数字段上建立索引

      索引基数是指这个字段在表里总共有多少个不同的值,比如一张表总共有100W行记录,其中性别字段,其值不是男就是女,那么该字段的基数就是2.如果对这种小基数字段建立索引的话,效率还不如全表扫描,因为你的索引树里就包含男和女两种值,根本没办法进行快速的二分查找,那用索引就没有太大的意义了.一般建立索引,尽量使用那些基数比较大的字段,就是不同的值比较多的字段,那么才能发挥出B+树快速二分查找的优势来.

    4. 长字符串尽量采用前缀索引

      尽量对字段类型较小的列设计索引,因为字段类型较小的话,占用的磁盘空间也会比较小,此时你再搜索的时候性能也会较好

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

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

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

      假如order by name,那么此时你的name因为再索引树里仅仅包含了前20个字符,所以这个排序是没办法用上索引的,group by也是同理.

    5. where与order by冲突时优先where

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

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

    6. 基于慢sql查询做优化

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

.番茄炒蛋
关注
  • 14
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySql索引优化
12-14
在列上创建索引可以优化查询的速度，但缺点是减慢写入数据的速度。 　创建索引的优点： 　第一，通过创建性索引，可以保证数据库表中每一行数据的性。 　第二，可以大大加快数据的检索速度，这也是创建索引的主要...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
mysql 索引优化
GuoWangWang11的博客
07-12 3729
mysql索引优化
mysql索引优化
LJJ1338的博客
09-10 1942
SQL如下： CREATE TABLE IF NOT EXISTS staffs( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(24) NOT NULL DEFAULT "" COMMENT'姓名', age INT NOT NULL DEFAULT 0 COMMENT'年龄', pos VARCHAR(20)...
MySQL索引优化总结
何哥的博客
09-30 6181
前言：相信大家都知道索引可以大大提高MySQL的检索速度，但是真正在平时工作中写SQL，真的会考虑到这条SQL如何能够用上索引提升执行效率？本篇博客详细的介绍了索引优化的20个原则，只要在工作中能够随时应用到，相信你写出的SQL能够命中索引，效率杠杠的。 一、索引的分类 1.1、普通索引、主键索引和唯一索引 1.2、聚集索引和非聚集索引 1.3、联合索引最左匹配原则 1.4、索引覆盖和回表 1.5、前缀索引 1.6、索引合并 1.7、索引下推 二、索引优化规则 查询的条...
MySQL索引优化（超详细）
最新发布
weixin_55816781的博客
05-10 6362
MySQL索引优化，涉及到索引介绍，性能分析，查询优化，慢查询日志。
mysql索引优化助记口诀
张步云的专栏
12-25 2960
口诀 不等not null还有or，索引全中也无效； 大等小等is null，全索命中才有效； 除此全匹最高效，最左匹配要记牢； 大小跳索百分号，后面用索也无效； 索无函数和计算，才能保证索有效。 解释 sql中只要包含了!= not null or ，会导致sql中全部索引失效。但是 is null >= <= 不会。 >= <= is null只会有两种情况：只有在命中了全部索引，也就是组合索引中的索引全用到了才会都有效并且是全部有效，否则就全无效。 组合索引中除了上面的特例外，
【MySQL】深入理解MySQL索引优化器原理（MySQL专栏启动）
小明的Java问道之路
11-08 8537
本文将解读MySQL数据库查询优化器（CBO）的工作原理。简单介绍了MySQL Server的组成，MySQL优化器选择索引额原理以及SQL成本分析，最后通过 select 查询总结整个查询过程。
MySQL索引优化的几种方式
weixin_45750855的博客
11-11 4813
索引高性能优化 1. 使用独立的索引列 ​ 错误的查询语句会使得索引无效… select user_id from user where user_id-1=4; 第一个例子中，对索引列user_id进行了一次方程运算，然后进行查询，然而这样的操作MySQL无法自动识别，导致主键索引失效了。 select date_col from t where TO_DAYS(CURRENT_DATE) -TO_DAYS(data_col)<=15; select date_col from t where
05-VIP-Mysql索引优化实战二.pdf
11-30
Mysql索引优化实战二 本文档主要介绍了Mysql索引优化的实战经验，着重于分页查询优化和Join关联查询优化。 一、分页查询优化 在实际业务系统中，分页功能是非常常见的，对于大表的分页查询，执行效率往往非常低。...
MySQL 索引优化全攻略
12-14
所谓索引是为特定的mysql字段进行... 　EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句. 　使用方法，在select语句
04-VIP-Mysql索引优化实战一.pdf
11-30
04-VIP-Mysql索引优化实战一.pdf
mysql索引优化分享
05-29
关于mysql索引一些优化介绍与创建原则，还有对order by排序的算法的介绍等等
Mysql索引优化解决方案.doc
03-07
Mysql索引优化解决方案.doc
mysql索引优化.xmind
05-10
mysql索引优化思维导图

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

.番茄炒蛋

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值