mysql rowid用法_MySQL性能优化 — 实践篇2

本文作者：何建辉（公众号：org_yijiaoqian）

点赞再看，养成习惯，微信搜一搜【一角钱小助手】关注更多原创技术文章。
本文 GitHub org_hejianhui/JavaStudy 已收录，有我的系列文章。

前言

• MySQL索引底层数据结构与算法
• MySQL性能优化原理-前篇
• MySQL性能优化-实践篇1

上一篇 《MySQL性能优化-实践篇1》我们讲了数据库表设计的一些原则，Explain工具的介绍、SQL语句优化索引的最佳实践，本篇继续来聊聊 MySQL 如何选择合适的索引。

MySQL Trace 工具

MySQL 最终是否选择走索引或者一张表涉及多个索引，最终是如何选择索引，可以使用 trace 工具来一查究竟，开启 trace工具会影响 MySQL 性能，所以只能临时分析 SQL 使用，用完之后立即关闭。

案例分析

讲 trace 工具之前我们先来看一个案例：

# 示例表
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('ZhangSan',23,'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());

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 > 'zzz' ;

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

trace工具用法

开启/关闭Trace

#开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
#关闭trace
set session optimizer_trace="enabled=off";

案例1

执行这两句sql

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

提出来trace值，详见注释

{
  "steps": [
    {
      "join_preparation": { --第一阶段：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": 3,    --扫描行数
                    "cost": 3.7    --查询成本
                  } /* 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 */
                    },
                    {
                      "index": "idx_age",
                      "usable": false,
                      "