性能调优4. Mysql索引优化实战二

1. 数据准备

‌‌‌　　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();

2. Mysql如何选择合适的索引

2.1. 前言

‌‌‌　　看下面例子。

‌‌‌　　EXPLAIN select * from employees where name > 'a';

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

‌‌‌　　
‌‌‌　　对于上面这两种 name>‘a’ 和 name>‘zzz’ 的执行结果，name>'a’下不走索引，name>'zzz’下又走索引。跟MySQL的cost成本计算有关。

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

2.2. trace工具用法

‌‌‌　　当前会话中开启trace。

‌‌‌　　session optimizer_trace="enabled=on";

‌‌‌　　当前会话中关闭trace。

‌‌‌　　set session optimizer_trace="enabled=off";

‌‌‌　　例子

‌‌‌　　set session optimizer_trace="enabled=on",end_markers_in_json=on;

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

‌‌‌　　select * from information_schema.OPTIMIZER_TRACE;

‌‌‌　　set session optimizer_trace="enabled=off";

	end_markers_in_json=on，就是在输出信息，json对象结束地方做个标记，如`/* steps */`

	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优化阶段，比如去掉where 1=1，查询条件全部满足联合索引，但无序下，会排序查询条件让其走联合索引。能优化的就优化，减少时间
        "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": 100140,‐‐-扫描行数
                    "cost": 10104.4‐--查询成本，cost成本。跟很多东西有关，普通索引下比如回表查找数据效率情况，只是相对值没有单位。不会真正执行sql语句，只是预估值，不是很耗时间。
                  } /* 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,‐‐-是否使用覆盖索引
                        "in_memory": 1,
                        "rows": 50070,---索引扫描行数
                        "cost": 17524.8,---索引使用成本
                        "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": 100140,
                      "access_type": "scan",---访问类型：为scan，全表扫描
                      "resulting_rows": 100140,
                      "cost": 10102.2,
                      "chosen": true,‐‐-确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100140,
                "cost_for_plan": 10102.2,
                "sort_cost": 100140,
                "new_cost_for_plan": 110242,
                "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": { ‐-‐第三阶段：SQL执行阶段
        "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": 100140,
              "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 */
‌‌‌　　}

‌‌‌　　结论

	全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描。

3. 常见的SQL优化

3.1. LIKE优化

3.1.1. LIKE以通配符开头或者LIKE’%字符串%'，MySQL索引失效会变成全表扫描操作

‌‌‌　　这些查询操作MySQL底层应该没做啥优化。

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

解决方式

‌‌‌　　1. 使用覆盖索引，MySQL在评估后就可能走索引来的快。

‌‌‌　　EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei';

‌‌‌　　EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%'

‌‌‌　　2. 如果不能使用覆盖索引则可能需要借助搜索引擎。

3.1.2. 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'

3.1.2.1. 索引下推（Index Condition Pushdown，ICP）

‌‌‌　　LIKE KK%其实就是用到了索引下推优化。

‌‌‌　　简单说，原本只能通过索引过滤掉不符合索引条件的数据，再回表查找完整数据进行比对过滤，不符合非索引条件的数据。索引下推后，在索引树中过滤掉不符合非索引条件的数据，再回表查找。
‌‌‌　　如果一个查询用到联合索引，一个条件查询会导致后续索引无序，但是MySQL又使用了这些索引，可能是使用了索引下推。

‌‌‌　　对于辅助的联合索引(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的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

3.2. Order by与Group by优化

3.2.1. 例子说明

‌‌‌　　如果排序能借助索引排好序，直接走索引取数据时候已经是排好序的，无疑是最快的，就不需要借助内存和磁盘空间进行排序。

‌‌‌　　例1

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  AND position ='dev' ORDER BY age;

‌‌‌　　分析

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

‌‌‌　　2. key只能判断WHERE条件是否使用了索引。这里ORDER BY其实使用了age索引，可以通过Extra分析。

‌‌‌　　例2

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY position

‌‌‌　　分析

‌‌‌　　从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age不符合最左前缀原理，出现了Using filesort。

‌‌‌　　例子3

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY age, position

‌‌‌　　分析

‌‌‌　　查找只用到索引name，age和position用于排序符合最左前缀原理，无Using filesort。

‌‌‌　　例4

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY  position,age

‌‌‌　　分析

‌‌‌　　和例3中explain的执行结果一样，但是出现了Using filesort，因为联合索引的创建顺序为name,age,position，但是排序的时候先按position，然后再age排序，颠倒位置了，不使用索引。

‌‌‌　　例5

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age=18  ORDER BY  position,age

‌‌‌　　等同于
‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age=18  ORDER BY position

‌‌‌　　分析

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

‌‌‌　　例6

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY age ASC, position DESC

‌‌‌　　分析

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

‌‌‌　　例7

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','zhuge') ORDER BY age , position

‌‌‌　　

‌‌‌　　分析

‌‌‌　　根据in查出的数据后，进行排序是排不了序列。

‌‌‌　　例8

‌‌‌　　EXPLAIN SELECT * FROM employees WHERE name > 'a' ORDER BY name

‌‌‌　　

‌‌‌　　分析

‌‌‌　　数据量太大，虽然走索引时候已经排好序，但是最后要回表查找完整数据，不如全表扫描在进行排序，所以不走索引。

‌‌‌　　可以用覆盖索引优化。

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

3.2.2. 优化总结

‌‌‌　　MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本完成排序，索引已经是排好序的，取索引数据时就已经是排好序的。filesort则需要借助内存或者磁盘再排序数据。index效率高，filesort效率低。

‌‌‌　　Order by使用Using index的两种情况。

‌‌‌　　1. Order by语句使用索引最左前列。

‌‌‌　　2. 使用where子句与Order by子句条件列组合满足索引最左前列。

‌‌‌　　优化技巧

‌‌‌　　1. 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。如果Order by的条件不在索引列上，就会产生Using filesort。

‌‌‌　　2. 能用覆盖索引尽量用覆盖索引触发index。

‌‌‌　　3. Group by与Order by很类似，其实质是先排序后分组(根据字段先后排序)，遵照索引创建顺序的最左前缀法则。对于Group by的优化如果不需要排序的可以加上Order by null禁止排序。

‌‌‌　　注意

‌‌‌　　where高于having，能写在where中的限定条件就不要去having限定了。

3.2.3. Using index和Using filesort区别

‌‌‌　　Using index借助索引排好序，取数据，加载是辅助索引的数据。Using filesort加载是聚簇索引组织的表数据(主键索引)，数据去排序。Innodb存储引擎下两者都是从磁盘ibd文件去读数据。

3.2.4. Using filesort文件排序原理

‌‌‌　　如果Order BY 能够使用索引中记录已经排好序的特性，不然就有借助内存或磁盘空间进行排序，这就是Using filesort。

3.2.4.1. 原理

‌‌‌　　MySQL有个排序缓冲区内存sort_buffer。会将要排序数据，加载到sort_buffer内存进行排序。如果数据量小，那么一次完全加载在内存中排好序，然后在加载数据。

‌‌‌　　数据量太大，则每次将一部分数据加载到缓冲区内存，缓冲区内存满后进行排序。将排好序的数据块，放到磁盘临时文件。最终会将这些临时文件多路归并排序，写到磁盘中。这也是磁盘排序，然后加载数据到内存。

‌‌‌　　sort_buffer相关参数

‌‌‌　　sort_buffer_size：用于控制排序缓冲区大小，默认值为 256K，最小可以设置为 32K，最大可以设置为 4G。

‌‌‌　　max_sort_length：用于限制排序单个字段的最大长度，超过就会截取。默认值为 1024 字节，最小可以设置为 4 字节，最大可以设置为 8M。

‌‌‌　　max_length_for_sort_data：比对排序记录长度(长度计算方式看排序模式)，根据长度判断使用什么排序模式。默认值为 1024 字节，最小可设置为 4 字节，最大可设置为 8M。

3.2.4.2. 内部排序和外部排序

‌‌‌　　内部排序是对排序缓冲区中的记录进行排序，是内存排序。

‌‌‌　　外部排序是对磁盘文件中，已经局部排好序的记录，进行全局归并排序，是磁盘文件排序。

3.2.4.3. ‌‌‌排序模式

‌‌‌　　表示是排序缓冲区或磁盘文件中会写入哪些内容。

3.2.4.3.1. <sort_key, additional_fields>

‌‌‌　　表示排序时候，写入缓冲区或磁盘文件中，除了要写入排序字段（sort_key），还要写入存储引擎返回给 server 层的所有字段）。

‌‌‌　　写入排序缓冲区中记录计算方式如下：

‌‌‌　　1. 排序字段（sort_key）：排序字段内容，会进行编码以节省存储空间，可能包含一个或多个排序字段。

‌‌‌　　2. 字段 NULL 标记区域：创建表时，没有指定 NOT NULL 的字段，在这个区域会有 1 bit 用于标记记录中该字段内容是否为 NULL（标记区域的长度暂时不清楚）。

‌‌‌　　重点说明： 如果某个字段内容为 NULL，该字段在字段 NULL 标记区域对应的 NULL 标记位设置为 1，字段不会占用排序缓冲区的额外空间。

‌‌‌　　3. char长度：char 字段长度，占用 1 字节或 2 字节。记录字段内容实际占用长度。

‌‌‌　　4. char 内容：char字段内容，以最大长度存储，不会去掉内容尾部的空格。

‌‌‌　　5. varchar 长度：varchar字段长度，占用 1 字节或 2 字节。记录字段内容实际占用长度。

‌‌‌　　6. varchar 内容：varchar 字段内容，占用空间为字段最大长度。如果字段实际内容长度小于定义的最大长度，剩余空间留空。

‌‌‌　　7. 除 blob 类型之外的其它字段：指是的除 tinyblob、mediumblob、blob、longblob、tinytext、mediumtext、text、longtext、json、geometry 之外的其它类型字段，只需要把字段内容写入排序缓冲区，不需要写入字段长度。

‌‌‌　　触发条件：

‌‌‌　　1. 存储引擎返回给 server 层的字段中(可以认为查询的字段)不能包含 blob 类型字段。

‌‌‌　　2. 排序字段（sort_key）长度之和 + additional_fields 所有字段最大长度之和(查询返回所有字段的最大长度之和）必须小于等于系统变量 max_length_for_sort_data 的值

‌‌‌　　好处:

‌‌‌　　如果缓冲区能一次加载完要排序记录或者如果排序缓冲区不能一次加载完要排序记录，就需要借助磁盘进行排序。

‌‌‌　　排序缓冲区或者磁盘这排序结果的最终文件（out_file）存放的就是已经排好序的所有记录，只需要从存储引擎读取一次数据，读取其中需要的字段返回给客户端就可以了。

‌‌‌　　缺点：

‌‌‌　　浪费磁盘空间，不管字段实际内容多少，都按字段设置占用长度计算内容长度。

3.2.4.3.2. <sort_key, packed_additional_fields>

‌‌‌　　<sort_key, packed_additional_fields> 表示排序缓冲区或磁盘文件中，除了要存入排序字段（sort_key），还要存入存储引擎返回给 server 层的所有字段（packed_additional_fields），并且会尽可能使用最少的空间存放待排序记录。

‌‌‌　　写入排序缓冲区中记录计算方式如下：
　
　

‌‌‌　　1. 排序字段（sort_key）：排序字段内容，会进行编码以节省存储空间，可能包含一个或多个排序字段。

‌‌‌　　2. 记录长度：存储排序缓冲区的记录中，除排序字段（sort_key）之外的长度，也就是记录长度 ~ 除 blob 类型之外的其它字段的长度。

‌‌‌　　3. 字段 NULL 标记区域：创建表时，没有指定 NOT NULL 的字段，在这个区域会有 1 bit 用于存储记录中该字段内容是否为 NULL（标记区域的长度暂时不清楚）。

‌‌‌　　重点说明： 如果某个字段内容为 NULL，该字段在字段 NULL 标记区域对应的 NULL 标记位设置为 1，字段不会占用排序缓冲区的额外空间。

‌‌‌　　4. char 长度：char 字段长度，占用 1 字节或 2 字节。为了节省空间，只写入 char 字段实际内容到排序缓冲区，所以需要记录字段内容长度。

‌‌‌　　5. char 内容：char 字段实际内容，以实际长度存储，会去掉内容尾部的空格。

‌‌‌　　6. varchar 长度：varchar 字段内容长度，占用 1 字节或 2 字节。

‌‌‌　　7. varchar 内容：varchar 字段实际内容。

‌‌‌　　8. 除 blob 类型之外的其它字段：指是的除 tinyblob、mediumblob、blob、longblob、tinytext、mediumtext、text、longtext、json、geometry 之外的其它类型字段，只需要把字段内容写入排序缓冲区，不需要写入字段长度。

‌‌‌　　触发条件：

‌‌‌　　1. 存储引擎返回给 server 层的字段中(可以认为查询的字段)不能包含 blob 类型字段。

‌‌‌　　2. packed_additional_fields 所有字段最大长度之和(查询返回字段所有最大长度之和)必须小于等于 65535 字节。
‌‌‌　　因为只写入字段实际内容到排序缓冲区或磁盘文件，不同记录长度可能会不一样，这就需要把每条记录的长度记下来，MySQL 用 2 个字节来保存记录长度，而 2 字节无符号整数能够表示的最大数字为 65535。

‌‌‌　　3. 排序字段（sort_key）长度之和 + 存储记录长度的 2 字节 + packed_additional_fields 所有字段最大长度之和(查询返回的所有字段最大长度之和)，必须小于等于系统变量 max_length_for_sort_data 的值

‌‌‌　　4. 可压缩字段最大长度之和必须大于 12 字节(比如字符串字段的内容，这种模式下会去除空格只保留实际内容)，如果可节省的空间太小，也就没必要折腾了。

‌‌‌　　好处:

‌‌‌　　1. 如果缓冲区能一次加载完要排序记录或者如果排序缓冲区不能一次加载完要排序记录，就需要借助磁盘进行排序。

‌‌‌　　排序缓冲区或者磁盘这排序结果的最终文件（out_file）存放的就是已经排好序的所有记录，只需要从存储引擎读取一次数据，读取其中需要的字段返回给客户端就可以了。

‌‌‌　　2. 进行排序字符串字段以实际内容为主

‌‌‌　　缺点：

‌‌‌　　加载所有返回的字段进行排序，有点浪费磁盘空间，以空间换时间。

3.2.4.3.3. <sort_key, rowid>

‌‌‌　　<sort_key, rowid> 表示排序缓冲区或磁盘文件中，除了要存入排序字段（sort_key），还要存入记录的主键 ID（rowid）。

‌‌‌　　写入排序缓冲区中记录计算方式如下：

‌‌‌　　1. 排序字段（sort_key）：排序字段内容，会进行编码以节省存储空间，可能包含一个或多个排序字段。

‌‌‌　　2. 主键ID：排序字段对应行记录的主键ID。

‌‌‌　　原理：

‌‌‌　　先加载排序记录到排序缓冲区排序，将排好序的数据根据主键ID，回表一条条查找完整数据返回。一般情况下该模式只用到内存排序。排好序后的数据都在内存。除非排序的数据量很大才会借助磁盘排序，将排好序的数据放在磁盘，在加载到内存根据主键ID回表查数据。

‌‌‌　　触发条件：

‌‌‌　　不能使用前面两种排序模式下，会使用。

‌‌‌　　好处：

‌‌‌　　记录数量不太多的情况下，使用排序缓冲区就能够存储所有待排序记录进行排序，能够在一定程度上避免使用磁盘文件排序。

3.2.4.3.4. 三种排序模式区别

‌‌‌　　<sort_key, additional_fields> 排序记录的。每个字符串字段都以其最大长度占用存储空间，存在空间浪费。

‌‌‌　　<sort_key, packed_additional_fields> 是 <sort_key, additional_fields> 的改进版，解决了空间浪费的问题。每个字符串字段都以其实际长度占用存储空间。

‌‌‌　　前面两种排序模式，以空间换时间，虽然不需要两次访问存储引擎，让文件排序逻辑整体上更简单，但是记录数量多起来之后，很多时候需要磁盘文件存储排序结果，而磁盘 IO 会导致排序效率低下。

‌‌‌　　<sort_key, rowid> 需要两次访问存储引擎，但只写入排序字段（sort_key）和主键 ID 到排序缓冲区，一定程度上避免了使用磁盘文件存放排序结果，某些情况下可能会比前两种排序模式更优。

3.2.4.3.5. 单路排序和双路双路排序

‌‌‌3.2.4.3.5.1. 单路排序

‌‌‌　　单路排序：一次性取出满足条件行的所有查询字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >。

‌‌‌3.2.4.3.5.2. 双路排序

‌‌‌　　双路排序（又叫回表排序模式）：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >。

‌‌‌‌‌3.2.4.3.5.3. 单路排序和双路排序的触发条件

‌‌‌　　MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总长度之合(大体可以这么理解)，来判断使用哪种排序模式。

‌‌‌　　如果查询字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；

‌‌‌　　如果查询字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。

‌‌‌‌‌3.2.4.3.5.4. 单路排序和双路排序的过程

‌‌‌　　单路排序的详细过程：

‌‌‌　　1. 从主键索引找到满足 name = ‘zhuge’ 条件的主键 id。

‌‌‌　　2. 根据主键 id 取出查询字段的值，存入 sort_buffer 中。

‌‌‌　　3. 从主键索引找到下一个满足 name = ‘zhuge’ 条件的主键 id。

‌‌‌　　4. 重复步骤 2、3 直到不满足 name = ‘zhuge’。

‌‌‌　　5. 对 sort_buffer 中的数据按照字段 position 进行排序。

‌‌‌　　6. 返回结果给客户端。

‌‌‌　　双路排序的详细过程：

‌‌‌　　1. 从主键索引找到第一个满足 name = ‘zhuge’ 的主键id。

‌‌‌　　2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中。

‌‌‌　　3. 从主键索引 取下一个满足 name = ‘zhuge’ 记录的主键 id。

‌‌‌　　4. 重复 3、4 直到不满足 name = ‘zhuge’。

‌‌‌　　5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序。

‌‌‌　　6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出查询字段的值返回给客户端。

‌‌‌‌‌3.2.4.3.5.4. 单路排序和双路排序的优化技巧

‌‌‌　　单路排序会把所有需要查询的字段都放到 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 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

‌‌‌　　注意

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

3.3. 分页查询优化

‌‌‌　　业务系统实现分页功能可能会用如下SQL实现。

‌‌‌　　select * from employees limit 10000,10;

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

3.3.1. 优化技巧

3.3.1.1. 根据自增且连续的主键排序的分页查询(不实用)

‌‌‌　　该SQL语句的执行计划是全表扫描取数据。

‌‌‌　　EXPLAIN select * from employees limit 90000,5;

‌‌‌　　employees表主键是自增的，则上面的SQL可以改写如下MySQL的自增主键默认都是按主键排序的。

‌‌‌　　select * from employees1 where id > 90000 limit 5;

‌‌‌　　查看该语句执行计划也是全表扫描，但是行数大大减少，执行效率更高。

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

满足条件

‌‌‌　　1. 需要主键自增且连续：表中可能某些记录被删后，主键空缺不连续，导致结果不一致。

‌‌‌　　例如

‌‌‌　　表中删除几条数据，让id不在连续，按主键方式分页查询结果是不正确的。

‌‌‌　　select * from employees where id > 9000 limit 5;

‌‌‌　　正确的分页查询结果。

‌‌‌　　select * from employees limit 9000,5;

‌‌‌　　2. 数据需要主键排序：如果 SQL语句 Order by 非主键的字段，分页查询结果是不正确的。

‌‌‌　　select * from employees where id > 9000  ORDER BY `name` LIMIT 5;

‌‌‌　　select * from employees ORDER BY `name` limit 9000,5;

3.3.1.2. 根据非主键字段排序的分页查询

‌‌‌　　根据主键分页查询优化条件太苛刻，正常不会用到，常用到是根据非主键字段排序的分页查询。

‌‌‌　　优化关键

‌‌‌　　1. 对于分页查询能走索引下，尽量走索引。

‌‌‌　　2. 对于不能走索引的，让排序的核心代码走索引，排序时返回的字段尽可能少，如让排序和分页操作先查出主键，然后根据主键查到对应的记录。

‌‌‌　　例如

‌‌‌　　原来的分页查询执行计划如下

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

‌‌‌　　当前联合索引(name, age, position)，ORDER BY name，符合最左前缀原理查询。却没有走name索引。可能是，扫描索引数据，并回表查找完整数据，成本比扫描全表的成本更高，所以优化器放弃使用索引。

‌‌‌　　修改语句如下，借助覆盖索引，让排序核心代码走索引，过滤一部分数据，在根据主键查到对应的记录。

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

‌‌‌　　执行计划如下，可以看到进行分页的核心代码走索引

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

‌‌‌　　执行时间比较

‌‌‌　　执行时间：0.024s

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

‌‌‌　　执行时间：0.064s

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

3.4. Join关联查询优化

3.4.1. 数据准备

‌‌‌　　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插入数据
‌‌‌　　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插入数据
‌‌‌　　drop procedure if exists insert_t2;
‌‌‌　　delimiter ;;
‌‌‌　　create procedure insert_t2()
‌‌‌　　begin
‌‌‌　　declare i int;
‌‌‌　　set i=1;
‌‌‌　　while(i<=9000)do
‌‌‌　　insert into t2(a,b) values(i,i);
‌‌‌　　set i=i+1;
‌‌‌　　end while;
‌‌‌　　end;;
‌‌‌　　delimiter ;
‌‌‌　　call insert_t2();

3.4.2. MySQL的表关联常见两种算法

3.4.2.1. 嵌套循环连接 Nested-Loop Join(NLJ) 算法

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

‌‌‌　　例如

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

‌‌‌　　从执行计划中可以看到这些信息

‌‌‌　　1. 驱动表是 t1，被驱动表是 t2。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表这边t2和t1数据相差不大下，MySQL可能会选错，选择大表做驱动表)。使用 inner join 时，排在前面的表并不一定就是驱动表。

‌‌‌　　2. 当使用left join时，左表是驱动表，右表是被驱动表。

‌‌‌　　3. 当使用right join时，右表时驱动表，左表是被驱动表。

‌‌‌　　4. 当使用join时，MySQL一般会选择数据量比较小的表作为驱动表，大表作为被驱动表。

‌‌‌　　5. 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

‌‌‌　　执行流程

‌‌‌　　上面SQL的大致流程如下：

‌‌‌　　1. 从表 t1 中读取一行数据（如果t1表有查询过滤条件的，会从过滤结果里取出一行数据）。

‌‌‌　　2. 从第 1 步的数据中，取出关联字段 a，到表 t2 中查找。

‌‌‌　　3. 取出表 t2 中满足条件的行，跟 t1 中获取到的结果合并，作为结果返回给客户端。

‌‌‌　　4. 重复上面 3 步。

‌‌‌　　磁盘扫描次数

‌‌‌　　假设被驱动表走索引下，整个过程会读取 t1 表的所有数据(磁盘扫描10000行)，根据每行数据中字段 a 的值，走索引获取 t2 表中的对应行数据，不考虑索引比对需要加载的索引数据，最终找到叶节点就1次磁盘IO扫描，可能数据不完整需要回表查找数据也要次磁盘IO扫描，可以简单认为最终只扫描 t2 表一行完整数据。即取出t2表对应行的所有数据，也要磁盘扫描10000次。因此整个过程磁盘扫描了 10000*2 行。

‌‌‌　　如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，每次驱动表取一行数据都要扫描被驱动表的数据找到关联数据，扫描磁盘次数就是10000*10000.这种情况下MySQL会选择 Block Nested-Loop Join(BNL)算法。

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

‌‌‌　　把驱动表的部分或者全部数据读入到缓冲区join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，返回关联的数据。

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

‌‌‌　　从执行计划中可以看到这些信息

‌‌‌　　1. 驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表这边t2和t1数据相差不大下，MySQL可能会选错，选择大表做驱动表)。使用 inner join 时，排在前面的表并不一定就是驱动表。

‌‌‌　　2. 当使用left join时，左表是驱动表，右表是被驱动表。

‌‌‌　　3. 当使用right join时，右表时驱动表，左表是被驱动表。

‌‌‌　　4. 当使用join时，MySQL一般会选择数据量比较小的表作为驱动表，大表作为被驱动表。

‌‌‌　　5. 使用了 BNL算法。一般 join 语句中，如果执行计划 Extra 中出现 Using join buffer 则表示使用的 join 算法是 BNL。

‌‌‌　　执行流程

‌‌‌　　上面SQL的大致流程如下：

‌‌‌　　1. 把 t2 的所有数据放入到 join_buffer 中。

‌‌‌　　2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比。

‌‌‌　　3. 返回满足 join 条件的数据。

‌‌‌　　磁盘扫描次数

‌‌‌　　整个过程对表 t1 和 t2的所有数据都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 9000(表 t2 的数据总量) =19000。

‌‌‌　　内存比对次数

‌‌‌　　join_buffer里的数据是无序的，极端情况下找到符合数据，需要对表 t1 中的每一行，做 9000次判断，极端下内存中的判断次数是9000 * 10000= 9000 万次。

‌‌‌　　分段放

‌‌‌　　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 表。磁盘扫描次数多了次t1表所有数据的扫描，内存比对次数跟一次性加载到缓冲区比对没差。

3.4.3. 优化技巧

3.4.3.1. 关联字段加索引

‌‌‌　　关联核心优化让MySQL做join操作时，被驱动表(大表)关联字段做索引，让其选择NLJ算法。

3.4.3.2. 小表驱动大表

‌‌‌　　大小表区分指的是，根据条件过滤后，参与关联的结果集大小(计算参与 join 的各个字段的总数据量)。

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

3.4.3.2.1. straight_join

‌‌‌　　如果遇到join情况下，MySQL自动选择驱动表不是小表，可以使用straight_join，功能同join类似，强制让左边的表来驱动右边的表。

‌‌‌　　例如

‌‌‌　　下面SQL语句代表指定t2表作为驱动表。

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

‌‌‌　　注意

‌‌‌　　1. straight_join只适用于inner join类似的操作，并不适用于left join，right join。（因为left join，right join已经指定了表执行顺序，确定了驱动表）。

‌‌‌　　2. 尽可能让优化器去判断选择的驱动表，使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

3.4.3.3. join尽量不超过3张表

3.4.3.3. 关联查询in和exsits优化

‌‌‌　　小表驱动大表，即小的数据集驱动大的数据集，关联的字段要做索引。关联查询的in和exsits正常可以用join替代吧，这边了解就行。

‌‌‌　　假设t1表和t2表根据a字段做关联查询。

‌‌‌　　in

‌‌‌　　当t2表的数据集小于t1表的数据集时，小表驱动大表，可以使用in，in优于exists。

	#t1表与t2表的a字段应建立索引
‌‌‌　　select * from t1 where a in (select a from t2)

	#等价于：
‌‌‌　　for(select a from t2){
‌‌‌　　select * from t1 where t1.a = t2.a
‌‌‌　　}	

‌‌‌　　exists

‌‌‌　　当t1表的数据集小于t2表的数据集时，exists优于in。

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

	#t1表与t2表的a字段应建立索引
‌‌‌　　select * from t1 where exists (select 1 from t2 where t2.a = t1.a)
	
	#等价于:
‌‌‌　　for(select * from t1){
	select * from t2 where t2.a = t1.a
‌‌‌　　}

‌‌‌　　1. EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别。

‌‌‌　　2. EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比。

‌‌‌　　3. EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析。

3.5. count(*) 查询优化

‌‌‌　　字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)。

‌‌‌　　count(字段)>count(主键 id)：count(字段)统计走二级索引，二级索引叶节点存储数据比主键索引叶节点存储数据少，加载比对更快，所以count(字段)>count(主键 id)，但是差距很小。5.7还是以后count(主键 id)做了优化，有二级索引会走二级索引。

‌‌‌　　count(1)>count(字段)：count(字段)还是count主键(id)会先取出字段到内存，判断不为null等，然后用计数器加下累计值返回结果。count(1)不需要取出字段统计，就用常量1做统计。其会通过cost计算，决定是全表扫描统计还是直接扫描索引统计。

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

‌‌‌　　count(字段)统计走不了索引需要全表扫描，跟count(主键 id)差不了。

‌‌‌　　注意

‌‌‌　　1. count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

‌‌‌　　2. count(*)是例外，MySQLl并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。

‌‌‌　　3. 根据某个字段count不会统计字段为null值的数据行。

‌‌‌　　执行效率

	#临时关闭mysql查询缓存，为了查看sql多次执行的真实时间，8.0开始去除了缓存功能
 
‌‌‌　　set global query_cache_size=0;
‌‌‌　　set global query_cache_type=0;


‌‌‌　　EXPLAIN select count(1) from employees1;
‌‌‌　　EXPLAIN select count(id) from employees1;
‌‌‌　　EXPLAIN select count(name) from employees1;
‌‌‌　　EXPLAIN select count(*) from employees1;

‌‌‌　　四个SQL的执行计划一样，说明这四个SQL执行效率应该差不多。

3.5.1. 优化技巧

‌‌‌　　这些优化只能适用于无条件的count的优化。

3.5.1.1. 查询MySQL自己维护的总行数

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

‌‌‌　　但对于InnoDB存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制，后面会讲)，查询count需要实时计算。

3.5.1.2. show table status

‌‌‌　　如果只需要知道表总行数的估计值可以用如下SQL查询，性能很高。

3.5.1.3. 将总数维护到Redis里

‌‌‌　　插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性。

‌‌‌　　redis和数据库双写一致不可能达到100%，相当于分布式事务。

3.5.1.4. 增加数据库计数表

‌‌‌　　插入或删除表数据行的时候同时维护计数表，让增删和计数在同一个事务里操作，MySQL本地事务可以保证100%一致。

4. 索引设计实战

‌‌‌　　原则

‌‌‌　　1. 让查询尽可能走索引。

‌‌‌　　2. 范围查找的条件都要放在联合索引最后：对于联合索引如(province,city,age)，之前讲过联合索引范围查找之后，后续的条件是不能触发索引的。

‌‌‌　　where province=xx and city=xx and age>=xx and age<=xx

‌‌‌　　2. 对于没法触发后列的联合索引，将前列基数较小的没有用于查询的索引写成常量触发后列索引。如一个联合索引(province,city,sex,age)。

‌‌‌　　原本查询条件

‌‌‌　　where province=xx and city=xx and age>=xx and age<=xx

‌‌‌　　要触发age范围查询触发索引，条件查询里头补上sex常量查询。

‌‌‌　　where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx

‌‌‌　　3. 时间没法触发索引，对于一些时间查询，可以增加字段通过标识区分。比如要查询一周内登录用户。可以新增字段标识有登录就是1，没有就是0，来触发索引快速过滤。

‌‌‌　　如一个联合索引(province,city,sex,age)。

‌‌‌　　原本查询一周内登录用户

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

‌‌‌　　添加字段is_login_in_latest_7_days标识有没登录过，并修改联合索引为(province,city,sex,is_login_in_latest_7_days,age)。

‌‌‌　　where province=xx and city=xx and sex in ('female','male') and is_login_in_latest_7_days=1 and age>=xx and age<=xx 

‌‌‌　　4. 创建索引遵从二八法则。用一到两个联合索引即，多字段联合索引解决80%查询，可以建立少字段联合索引来解决剩下20%比较偏门的查询。

‌‌‌　　如一个联合索引(province,city,sex,age)，现在查询条件如下。

‌‌‌　　where sex = 'female' order by score limit xx,xx，

‌‌‌　　可以再建立联合索引如(sex,score)。

‌‌‌　　5. 查询同时遇到Orderr BY和Where，优先对Where查询条件进行索引优化。

5. 面试题

5.1. 为什么范围查找MySQL没有用索引下推优化？

‌‌‌　　估计应该是MySQL认为范围查找，即使用索引过滤后结果集很大，还要回表找完整数据，LIKE KK%在绝大多数情况来看，过滤后的结果集比较小，所以这里MySQL选择给 LIKEKK% 用索引下推优化，也不是绝对的，有时LIKE KK% 也不一定就会走索引下推。

5.2. 被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

‌‌‌　　没索引下，BNL磁盘扫描次数少，更快，NLJ驱动表和被驱动表都是按全表扫描比对。而BNL是先将驱动数据都加载到内存，然后扫描被驱动表数据一行行加载到内存比对联合，速度快很多。

‌‌‌　　假设：驱动表数据：10条，被驱动表数据：10条
‌‌‌　　极端下，NLJ是10*10磁盘扫描，BNL则是10+10磁盘扫描，内存比对是10*10。
‌‌‌　　但是联合字段走索引下NLJ更快基本10+10磁盘扫描成本。

5.3. 为什么对于count(id)，MySQL最终选择辅助索引而不是主键聚集索引？

‌‌‌　　因为二级索引叶节点存储的数据相对主键索引叶节点存储数据更少，检索性能应该更高，MySQL内部做了点优化(应该是在5.7版本才优化)。