关于MySQL索引优化实战

秋天的一亩三分地

已于 2023-06-22 22:04:11 修改

阅读量446

点赞数

分类专栏： MySQL专栏文章标签： mysql 数据库 java sql

于 2023-05-14 18:24:04 首次发布

本文链接：https://blog.csdn.net/weixin_45925485/article/details/130671561

版权

MySQL专栏专栏收录该内容

6 篇文章 1 订阅

订阅专栏

关于MySQL索引优化实战

示例表：
一、综合示例
- 1、联合索引第一个字段用范围有可能不会走索引
- 2、强制走索引
- 3、覆盖索引优化
- 4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
- 5、like KK% 一般情况都会走索引
二、索引下推（Index Condition Pushdown，ICP）
- 概念
- 范围查找MySQL未使用索引下推优化
- MySQL选择合适的索引
三、常见SQL深入优化
- Order by与Group by优化
- 优化总结：
四、Using filesort文件排序原理详解
- filesort文件排序方式
- - 单路排序：
  - 双路排序（回表排序模式）：
- 验证各种排序方式：
- - 单路排序的详细过程：
  - 双路排序的详细过程：
五、索引设计原则
六、MySQL慢查询
七、索引设计实战
八、分页查询优化
- 1、根据自增且连续的主键排序的分页查询
- 2、根据非主键字段排序的分页查询
九、Join关联查询优化
- MySQL的表关联常见有两种算法
- - 1、嵌套循环连接 Nested-Loop Join(NLJ) 算法
  - 2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
- 对于关联SQL的优化
- - 1、关联字段加索引：
  - 2、小表驱动大表：
- straight_join
- - 1、straight_join 只适用于 inner join：
  - 2、尽可能让优化器去判断：
- 小表定义：
- in和exsits优化
十、count(*)查询优化
- 常见的优化方法
十一、阿里巴巴MySQL规范选读
- MySQL数据类型选择

示例表：

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('autumn',i),i,'dev');  
    set i=i+1;                       
  end while;
end;
delimiter;
call insert_emp();

一、综合示例

1、联合索引第一个字段用范围有可能不会走索引

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

当范围涉及的数量大时，不会走索引

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

当范围涉及的数量小时，会走索引

联合索引若第一个字段就使用范围查找可能不会走索引，MySQL 内部可能判断第一个字段若范围比较大，结果集也会很大，回表效率不高，因而选择全表扫描

2、强制走索引

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

虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描行 rows 看上去也少了些，但最终查找效率不一定比全表扫描高，因为回表的效率不高
验证：
避免缓存造成误差，先关闭查询缓存：

set global query_cache_size=0;  
set global query_cache_type=0;

SELECT * FROM employees WHERE name < 'LiLei';

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 = 'HanMeimei') AND age = 22 AND position ='manager';

验证：
将 employees 表复制一张 employees_copy 的表，里面保留几条记录

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 = 'HanMeimei') 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';

二、索引下推（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 是无序的，无法有效利用索引。
在 MySQL 5.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' ;

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

对于上述这两种 name>‘a’ 和 name>‘z’ 的执行结果，使用 **trace工具 **可查看 MySQL 最终如何选择索引。
开启 trace 工具会影响 MySQL 性能，所以只能临时分析 SQL 时使用，用完之后需立即关闭。

trace工具用法：
开启 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;

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": 99970,	--扫描行数
                    "cost": 20349		--查询成本
                  } /* 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": 49985,						--索引扫描行数
                        "cost": 59983,						--索引使用成本
                        "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": 99970,
                      "access_type": "scan",			--访问类型：为scan，全表扫描
                      "resulting_rows": 99970,
                      "cost": 20347,
                      "chosen": true,							--确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 99970,
                "cost_for_plan": 20347,
                "sort_cost": 99970,
                "new_cost_for_plan": 120317,
                "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": 30,
              "sort_buffer_size": 262056,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

由 trace 内容可以得出结论：全表扫描的成本低于索引扫描，所以 MySQL 最终选择全表扫描

同理：

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

上述执行结果的 trace 字段则是相反：索引扫描的成本低于全表扫描，所以 MySQL 最终选择索引扫描

关闭 trace 工具：

set session optimizer_trace="enabled=off";

注：上述操作是经过成本预估得出的结果，大多数情况下是准确的，但无法保证一定准确。

三、常见SQL深入优化

Order by与Group by优化

Case1：

最左前缀法则：中间字段不能断，因此查询用到了 name 索引，从 key_len=74 也可看出；age 索引列则用在排序过程中，因为 Extra 字段里没有 using filesort。
Case 2：

key_len=74，说明查询使用了 name 索引，由于用了 position 进行排序，跳过了 age，因而出现 Using filesort。

Case 3：

查询只用到索引 name，而 age 和 position 则用于排序，无 Using filesort。

Case 4：
和 Case 3 的执行结果一样，但是出现了 Using filesort ，因为索引的创建顺序为 name、age、position，但是排序的时候 age 和 position 位置颠倒，断掉了索引。
Case 5：

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

虽然排序的字段列与索引顺序一样，且 order by 默认升序，但 position desc 却是降序，导致与索引的排序方式不同，从而产生 Using filesort。MySQL 8 以上版本有降序索引可以支持该种查询方式。

Case 7：
对于排序来说，多个相等条件也是范围查询。
Case 8：
可用覆盖索引优化：

优化总结：

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，则使用双路排序模式。

验证各种排序方式：

查看此条 SQL 对应 trace 结果如下（以下为排序部分）：

set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name = 'autumn' order by position;
select * from information_schema.OPTIMIZER_TRACE;

"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,				--使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
        "sort_buffer_size": 262056,			--排序缓存的大小，单位Byte
        "sort_mode": "<sort_key, packed_additional_fields>"		--排序方式，这里用的单路排序
      } /* filesort_summary */
    }
  ] /* steps */
} /* join_execution */

employees 表所有字段长度总和肯定大于 10 字节：

set max_length_for_sort_data = 10;
select * from employees where name = 'autumn' order by position;
select * from information_schema.OPTIMIZER_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": 0,
          "examined_rows": 0,
          "number_of_tmp_files": 0,
          "sort_buffer_size": 262136,
          "sort_mode": "<sort_key, rowid>"			--排序方式，这里用的双路排序
        } /* filesort_summary */
      }
    ] /* steps */
  } /* join_execution */
}

关闭 trace 工具：

set session optimizer_trace="enabled=off";

单路排序的详细过程：

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

双路排序的详细过程：

从索引 name 找到第一个满足 name = ‘autumn’ 条件的主键id
根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
从索引 name 取下一个满足 name = ‘autumn’ 条件的主键 id
重复 2、3 直到不满足 name = ‘autumn’
对 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 很多参数设置都是做过优化的，切勿轻易调整。

五、索引设计原则

1、代码先行，索引后上
建完表立刻建立索引是错误的做法，应该等到主体业务功能开发完毕，将涉及到该表相关的 SQL 集中出来进行分析后再建立索引。

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

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

若对这种小基数字段建立索引的话，不如全表扫描，因为在索引树里就包含男和女两种值，无法进行快速的二分查找，在此使用索引毫无意义。

一般建立索引，尽量使用基数比较大的字段，即值的种类比较多的字段，才能发挥出 B+ 树快速二分查找的优势。

4、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，例如 tinyint 等，因为字段类型较小，则占用磁盘空间也会比较小，此时在搜索时性能也相对好些。

但并非绝对，很多时候需要针对 varchar(255) 这类字段建立索引，此时多占用一些磁盘空间则变成必要。

对于 varchar(255) 这类的大字段可能会比较占用磁盘空间，因此需考虑优化，例如针对此字段的前 20 个字符建立索引，即将此字段里每个值的前 20 个字符放在索引树里，类似于 KEY index(name(20),age,position)。

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

但若 order by name 时，name 会因为在索引树里仅包含前 20 个字符，导致此排序无法使用索引， group by 亦同理。

5、where与order by冲突时优先where
一般是让 where 条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行 where 筛选往往可以最快速度筛选出需要的少部分数据，之后再排序，这样成本可能会小很多。

6、基于慢SQL查询做优化
可以根据监控后台的一些慢 SQL，针对这些慢 SQL 查询做特定的索引优化。

六、MySQL慢查询

可根据监控后台的一些慢 SQL，再针对这些慢 SQL 查询做特定的索引优化。
关于慢 SQL 查询可参考：MySQL 慢查询资料

七、索引设计实战

以社交场景 APP 举例，搜索好友时就涉及到对用户信息的筛选，即对用户 user 表搜索，这个表一般来说数据量会比较大，先不考虑分库分表的情况，例如筛选地区(省市)、性别、年龄、身高、爱好之类的，有的 APP 可能还有用户评分，比如用户的受欢迎程度评分，可能还会根据评分来排序等等。

对于后台程序来说除了过滤用户的各种条件，还需要分页之类的处理，可能会生成类似 SQL 语句执行：

select xx from user where xx=xx and xx=xx order by xx limit xx,xx

对于这种情况如何合理设计索引，比如用户可能经常会根据省市优先筛选同城的用户，还有根据性别去筛选，此时应该设计一个联合索引 (province,city,sex) ，因为这些字段查询太频繁了。

假设又有用户根据年龄范围去筛选了，比如 where province=xx and city=xx and age>=xx and age<=xx，先尝试着把 age 字段加入联合索引 (province,city,sex,age)，注意，一般这种范围查找的条件都要放在最后，联合索引范围之后条件的是不能用索引的，但是对于当前这种情况依然用不到 age 这个索引字段，因为用户没有筛选 sex 字段，因此需将其优化成如下：where province=xx and city=xx and sex in (‘female’,‘male’) and age>=xx and age<=xx

对于爱好之类的字段也可以类似 sex 字段处理，所以可以把爱好字段也加入索引 (province,city,sex,hobby,age)

假设可能还有一个筛选条件，比如要筛选最近一周登录过的用户，一般大家肯定希望跟活跃用户交友，这样能尽快收到反馈，对应后台 SQL 可能是这样：

where province=xx and city=xx and sex in (‘female’,‘male’) and age>=xx and age<=xx and latest_login_time>= xx

此时无法直接将 latest_login_time 字段加入索引，例如 (province,city,sex,hobby,age,latest_login_time) 。对此可以试着再设计一个字段 is_login_in_latest_7_days，用户如果一周内有登录值就为 1，否则为 0，因此就可以把索引设计成 (province,city,sex,hobby,is_login_in_latest_7_days,age) 来满足上述场景。

一般来说，通过这么一个多字段的索引是能够过滤掉绝大部分数据的，就保留小部分数据下来基于磁盘文件进行 order by 语句的排序，最后基于 limit 进行分页，那么一般性能还是比较高的。

不过有时可能用户会这么来查询，就查下受欢迎度较高的女性，比如 SQL：where sex = ‘female’ order by score limit xx,xx，那么上面那个索引是很难用上的，不能把太多的字段以及太多的值都用 in 语句拼接到 SQL 里的，对此可以再设计一个辅助的联合索引，比如 (sex,score)，这样就能满足查询要求了。

核心思想：尽量利用一两个复杂的多字段联合索引，抗下 80% 以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询，保证这种大数据量表的查询尽可能多的都能充分利用索引，这样就能保证查询速度和性能了！
索引设计实战.png

八、分页查询优化

多数情况下业务系统实现分页功能可能使用的 SQL：

select * from employees limit 10000,10;

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

1、根据自增且连续的主键排序的分页查询

首先是一个根据自增且连续主键排序的分页查询的例子：

select * from employees limit 90000,5;

该 SQL 表示查询从第 90001 开始的五行数据，没添加单独 order by，表示通过主键排序。表 employees ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001 开始的五行数据，如下：

select * from employees where id > 90000 limit 5;

查询的结果是一致的。对比执行计划：

EXPLAIN select * from employees limit 90000,5;

EXPLAIN select * from employees where id > 90000 limit 5;

显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。
但此条改写的 SQL 在很多场景并不实用，因为表中可能某些记录被删后主键空缺，导致结果不一致，如下图试验所示（先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL）：

两条 SQL 的结果并不一样，因此若主键不连续，无法使用上面描述的优化方法。
另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。
所以这种改写得满足以下两个条件：

主键自增且连续
结果是按照主键排序的

2、根据非主键字段排序的分页查询

SQL 如下：

select * from employees ORDER BY name limit 90000,5;

EXPLAIN select * from employees ORDER BY name limit 90000,5;

此查询并没有使用 name 字段的索引（key 字段对应的值为 null），原因是扫描整个索引并查找到没索引的行（可能要遍历多个索引树）的成本比扫描全表的成本更高，所以优化器放弃使用索引。
因此关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后 SQL 的执行计划：

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

九、Join关联查询优化

-- 示例表：
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 插入一些示例数据
-- 往 t1 表插入 1万 行记录
drop procedure if exists insert_t1; 
delimiter ;;
create procedure insert_t1()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=10000)do                 
    insert into t1(a,b) values(i,i);  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t1();

-- 往 t2 表插入 100 行记录
drop procedure if exists insert_t2; 
delimiter ;;
create procedure insert_t2()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100)do                 
    insert into t2(a,b) values(i,i);  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t2();

MySQL的表关联常见有两种算法

Nested-Loop Join 算法
Block Nested-Loop Join 算法

1、嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表（执行计划结果的 id 若一样则按从上到下顺序执行 SQL），优化器一般会优先选择**小表做驱动表，**用where条件过滤完驱动表，然后再跟被驱动表做关联查询。

所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用 left join 时，左表是驱动表，右表是被驱动表；当使用 right join 时，右表时驱动表，左表是被驱动表；当使用 join 时，MySQL 会选择数据量比较小的表作为驱动表，大表作为被驱动表。
使用了 NLJ 算法。一般 join 语句中，若执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面 SQL 的大致流程如下：

从表 t2 中读取一行数据（若 t2 表有查询过滤条件的，先用条件过滤完，再从过滤结果里取出一行数据）；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

整个过程会读取 t2 表的所有数据（扫描100行），然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行（扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，即 t1 表也扫描了 100 行）。因此整个过程扫描了** 200 行**。

若被驱动表的关联字段没索引，使用NLJ算法性能会比较低，MySQL 会选择下方的 Block Nested-Loop Join 算法。

2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，将被驱动表每一行取出来跟 join_buffer 中的数据做对比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

Extra 中的 Using join buffer (Block Nested Loop) 说明该关联查询使用的是 BNL 算法。

上面 SQL 的大致流程如下：

把 t2 的所有数据放入到** join_buffer** 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000 = 100 万次。

若 t2 为大表，join_buffer 放不下：·

join_buffer 的大小由参数 join_buffer_size 设定，默认值为 256k。若放不下表 t2 的所有数据话，则使用分段放。

例如 t2 表有1000 行记录，join_buffer 一次只能放 800 行数据，执行过程则会先往 join_buffer 里放 800 行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200 行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引则选择使用 BNL 算法，而不使用 Nested-Loop Join：

若上方第二条 SQL 使用 Nested-Loop Join，则扫描行数为 100 * 10000 = 100万次，此为磁盘扫描。

而使用 BNL 磁盘扫描次数则少很多，相比于磁盘扫描，BNL 的内存计算会快得多。

因此 MySQL 对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法；若有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL 算法性能更高。

对于关联SQL的优化

1、关联字段加索引：

让 MySQL 做 join 操作时尽量选择 NLJ 算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描。即能走索引的过滤条件尽量都走索引。

2、小表驱动大表：

写多表连接 SQL 时，若明确知道哪张表是小表，则可以用 straight_join 写法固定连接驱动方式，节省 MySQL 优化器自己判断的时间。

straight_join

功能与 join 类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序：

select * from t2 straight_join t1 on t2.a = t1.a;

代表指定 MySQL 选择 t2 表作为驱动表。

1、straight_join 只适用于 inner join：

并不适用于 left join，right join，因为 left join 与 right join 已经指定了表的执行顺序。

2、尽可能让优化器去判断：

因为大部分情况下 MySQL 优化器是比人聪明的。因此使用 straight_join 时必须慎重，部分情况下人为指定的执行顺序并不一定会比优化引擎可靠。

小表定义：

在决定哪个表做驱动表时，应该将两个表按照各自的条件过滤，待过滤完成之后，再计算参与 join 的各个字段的总数据量，此时数据量小的那个表，才是“小表”，应该作为驱动表。

in和exsits优化

小表驱动大表，即小的数据集驱动大的数据集

in：当 B 表的数据集小于A表的数据集时，in 优于 exists。

select * from A where id in (select id from B)  
#等价于：
　　for(select id from B){
      select * from A where A.id = B.id
    }

exists：当 A 表的数据集小于 B 表的数据集时，exists 优于 in。

将主查询 A 的数据，放到子查询 B 中做条件验证，根据验证结果（true 或 false）来决定主查询的数据是否保留：

select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
    for(select * from A){
      select * from B where B.id = A.id
    }
    
#A 表与 B 表的 ID 字段应建立索引

EXISTS (subquery) 只返回 TRUE 或 FALSE，因此子查询中的 SELECT * 也可以用 SELECT 1 替换，官方说法是实际执行时会忽略 SELECT 清单，因此没有区别；
EXISTS 子查询的实际执行过程可能经过了优化而不是理论上的逐条对比；
EXISTS 子查询往往也可以用 JOIN 来代替，何种最优需要具体问题具体分析。

十、count(*)查询优化

-- 临时关闭 MySQL 查询缓存，为了查看 SQL 多次执行的真实时间
set global query_cache_size=0;
set global query_cache_type=0;

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

注：上方 4 条 SQL 只有根据某个字段 count，不会统计字段为 null 值的数据行。

此 4 条 SQL 的执行计划结果相同，说明它们执行的效率应该差不多。
字段有索引：count(*) ≈ count(1) > count(字段) > count(主键 id)

字段有索引，count(字段) 统计走二级索引，二级索引存储数据比主键索引少，所以 count(字段) > count(主键 id)

字段无索引：count(*) ≈ count(1) > count(主键 id) > count(字段)

字段没有索引，count(字段) 统计走不了索引，count(主键 id) 还可以走主键索引，所以 count(主键 id) > count(字段)

count(1) 和 count(字段) 的执行过程类似，但 count(1) 不需要取出字段统计，就用常量 1 做统计；而 count(字段) 则需取出字段再统计，因此理论上 count(1) 比 count(字段) 快一点。

count() 是例外，MySQL 并不会把全部字段取出来，而是专门做了优化：不取值，按行累加。因此效率很高，就不需要用 count(列名) 或 count(常量) 来替代 count()。

对于 count(id)，MySQL 最终选择辅助索引而不是主键聚集索引：
二级索引相对主键索引存储数据更少，因此检索性能更高，MySQL 内部便做优化（5.7 版本开始优化）。

常见的优化方法

1、查询MySQL自己维护的总行数

对于 MyISAM 存储引擎的表做不带 where 条件的 count 查询性能是很高的，因为 MyISAM 存储引擎的表的总行数会被 MySQL 存储在磁盘上，查询不需要计算。

对于 InnoDB 存储引擎的表 MySQL 不会存储表的总记录行数（因为有 MVCC 机制），查询 count 需要实时计算。

2、show table status

若只需要知道表总行数的估计值可使用如下 SQL 查询，性能很高：

3、将总数维护到Redis中

插入或删除表数据行的同时，也维护 redis 里的表总行数 key 的计数值（用 incr 或 decr 命令），但此方式可能不准确，很难保证表操作和 redis 操作的事务一致性。

4、增加数据库计数表

插入或删除表数据行的同时，也维护计数表，让它们在同一个事务里操作。

十一、阿里巴巴MySQL规范选读

MySQL数据类型选择

在 MySQL 中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：

确定合适的大类型：数字、字符串、时间、二进制；
确定具体的类型：有无符号、取值范围、变长定长等。

在 MySQL 数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为 NOT NULL，避免使用 NULL。

1、数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128, 127)	(0, 255)	小整数值
SMALLINT	2 字节	(-32 768, 32 767)	(0, 65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608, 8 388 607)	(0, 16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808, 9 223 372 036 854 775 807)	(0, 18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38, 1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0, (1.175 494 351 E-38, 3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)	0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

无符号位置：
→ →

优化建议

若整形数据没有负数，如 ID 号，建议指定为 UNSIGNED 无符号类型，容量可以扩大一倍。
建议使用 TINYINT 代替 ENUM、BITENUM、SET。
避免使用整数的显示宽度，即不要用 INT(10) 类似的方法指定字段显示宽度，直接用 INT。
DECIMAL 最适合保存准确度要求高，且用于计算的数据，比如价格。但在使用 DECIMAL 类型时，应注意长度设置。
建议使用整形类型来运算和存储实数，方法：实数乘以相应的倍数后再操作。
整数通常是最佳的数据类型，因为它速度快，且能使用 AUTO_INCREMENT。

2、日期和时间

类型	大小（字节）	范围	格式	用途
DATE	3	1000-01-01 到 9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’ 到 ‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901 到 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 到 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00 到 2038-01-19 03:14:07	YYYYMMDDhhmmss	混合日期和时间值，时间戳

优化建议

MySQL 能存储的最小时间粒度为秒。
建议用 DATE 数据类型来保存日期。MySQL 中默认的日期格式是 yyyy-MM-dd。
用 MySQL 的内建类型 DATE、TIME、DATETIME 来存储时间，而不是使用字符串。
当数据格式为 TIMESTAMP 和 DATETIME时，可以用 CURRENT_TIMESTAMP 作为默认（MySQL 5.6 以后），MySQL 会自动返回记录插入的确切时间。
TIMESTAMP 是 UTC 时间戳，与时区相关。
DATETIME 的存储格式是一个 YYYYMMDD HH:MM:SS 的整数，与时区无关，存了什么，读出来就是什么。
除非有特殊需求，一般的公司建议使用 TIMESTAMP，它比 DATETIME更节约空间，但是像阿里这样的大公司一般会用 DATETIME，因为不用考虑 TIMESTAMP 将来的时间上限问题。
有时人们把 Unix 的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，因此并不推荐。

3、字符串

类型	大小	用途
CHAR	0-255字节	定长字符串，char(n)当插入的字符数不足n时(n代表字符数)，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉
VARCHAR	0-65535 字节	变长字符串，varchar(n)中的n代表最大字符数，插入的字符数不足n时不会补充空格
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

优化建议

字符串的长度相差较大用 VARCHAR；字符串短，且所有值都接近一个长度用 CHAR。
CHAR 和 VARCHAR 适用于包括人名、邮政编码、电话号码和不超过 255 个字符长度的任意字母数字组合。那些要用来计算的数字不应使用 VARCHAR 类型保存，因为可能会导致一些与计算相关的问题，即影响计算的准确性和完整性。
尽量少用 BLOB 和 TEXT，若实在要用可以考虑将 BLOB 和 TEXT 字段单独存一张表，用 id 关联。
BLOB 系列存储二进制字符串，与字符集无关；TEXT 系列存储非二进制字符串，与字符集相关。
BLOB 和 TEXT 都不能有默认值。

注：INT 显示宽度
建表语句中的数字类型的长度并非表示此类型存储的最大长度，而是显示的最大长度。

CREATE TABLE `user`(
    `id` TINYINT(2) UNSIGNED
);

上方 SQL 中 user 表的 id 字段的类型为 TINYINT，可以存储的最大数值是255：

在存储数据时，若存入值小于等于 255，如 200，虽然超过 2 位，但是没有超出 TINYINT 类型长度，所以可以正常保存；
若存入值大于 255，如 500，MySQL 则会自动保存为 TINYINT 类型的最大值 255。

在查询数据时，不管查询结果为何值，都按实际输出，无特殊设置并不起作用。
此处 TINYINT(2) 中 2 的作用为：当需要在查询结果前填充 0 时，命令中加上 ZEROFILL 就可实现，如：

`id` TINYINT(2) UNSIGNED ZEROFILL

此时若查询结果为 5，输出结果则为 05。

若设置为 TINYINT(5)，则输出为 00005，但实际存储的值还是 5，且存储的数据不会超过 255，只是 MySQL 输出数据时在前面填充了 0。

在 MySQL 命令中，字段的类型长度 TINYINT(2) 或 INT(11) 都不会影响数据的插入，只会在使用 ZEROFILL 时才有作用，让查询结果的前面填充 0。

秋天的一亩三分地

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于MySQL索引优化实战

1、MySQL 支持两种方式的排序 filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。2、order by 满足两种情况会使用 Using index。1) order by 语句使用索引最左前缀法则。2) 使用 where 子句与 order by 子句条件列组合满足索引最左前缀法则。3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
复制链接

扫一扫