科普文：软件架构数据库系列之【MySQL优化器之统计信息、直方图梳理】 ...

概叙

关于MySQL统计信息现状，可参考：科普文：软件架构数据库系列之【MySQL统计信息的现状和发展】-CSDN博客
如上图所示，MySQL作为C/S架构的RDBMS，在客户端发送一条SQL命令后，要经过如上过程，完成服务端mysqld进程对SQL命令的处理。
TCP/IP网络通讯可参考：
科普文：Java基础系列之【以太网和以太网交换机】_java中交换机-CSDN博客
科普文：Java基础系列之【互联网基石：TCP/IP四层模型】_tcp和wss-CSDN博客
科普文：Java基础系列之【互联网基石：TCP/IP四层模型各层协议介绍】-CSDN博客
科普文：Java基础系列之【互联网基石：TCP/IP四层模型下数据帧是怎样传输的】-CSDN博客
MySQL的C/S架构可参考：科普文：软件架构数据库系列之【MySQL和服务器】-CSDN博客
客户端的ORM可参考：科普文：Java基础mybatis系列之【mybatis框架中SQL 实行流程分析】-CSDN博客
实战：MyBatis适配多种数据库：MySQL、Oracle、PostGresql等_mybatis oracle-CSDN博客
科普文：深入明白Mybatis-CSDN博客
SQL命令可参考：科普文：软件架构数据库系列之【MySQL数据库SQL命令和SQL标准梳理】-CSDN博客
详细梳理如下：
客户端：mysql客户端命令、jdbc/odbc等应用程序。
连接器：处理客户端连接、举行权限验证、线程池管理等。负责监听和管理客户端的连接以及线程处理等。每一个连接到MySQL服务器的请求都会被分配一个连接线程。连接线程负责与客户端的通讯，接受客户端发送的命令而且返回服务器处理的效果。

分析器（剖析器）：SQL词法与语法分析，比方语义和语法的分析和检查，以及对象访问权限检查等。生成SQL剖析树。

优化器：依据SQL剖析树、统计信息、数据字典，在CBO优化器的综合决定下生成SQL的实行计划。使用数据库的统计信息决定SQL语句的最佳实行方式。使用索引还是全表扫描的方式访问表，多表连接的实现方式等。优化器是决定查询性能的关键组件，而数据库的统计信息是优化器判断的基础。

实行器：依据优化器生成的SQL实行计划，实行SQL，通过存储引擎接口从存储引擎将数据加载到内存举行处理，并将最终SQL实行效果返回给连接器。这一过程有Nestedloop/Hash join等表连接、index merge、ICP、MRR等优化策略参与详细的数据处理利用。根据实行计划调用相应的实行模块获取数据，并返回给客户端。对于MySQL而言，会根据表的存储引擎调用不同的接口获取数据。假如数据已经被缓存，可以直接从缓冲区获取。

存储引擎：存储数据、主要负责和利用系统、磁盘之间的IO利用。存储引擎是对底层物理数据实行实际利用的组件，为服务器层提供各种利用数据的API。MySQL支持插件式的存储引擎，包罗InnoDB、MyISAM、Memory等。
详细的MySQL体系结构，参考：
科普文：软件架构数据库系列之【MySQL5.6体系结构】_mysql 5.6 存储结构-CSDN博客
科普文：软件架构数据库系列之【图解MySQL】-CSDN博客
科普文：软件架构数据库系列之【MySQL存储引擎InnoDB】-CSDN博客
科普文：软件架构数据库系列之【MySQL前世今生及其体系结构概叙】_mysql8.0 8.1 8.2 8.3-CSDN博客
分析器（剖析器）可参考：科普文：软件架构数据库系列之【MySQL的sql_mode参数】-CSDN博客
优化器可参考：科普文：软件架构数据库系列之【MySQL剖析器和优化器】_mysql剖析器优化器-CSDN博客
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch】-CSDN博客
优化器策略可参考：
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--ICP索引下推】_查询优化器 optimizer-CSDN博客
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--MRR 优化器】-CSDN博客
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch-- Index Merge 索引归并 优化器】-CSDN博客
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--Hash Join和BNL（Block Nested Loop）优化器】-CSDN博客
实行器可参考：
科普文：软件架构数据库系列之【MySQL8新特性--实行器之火山模型（Iterator Model）】-CSDN博客
科普文：软件架构数据库系列之【经典的火山模型实行引擎Vastbase】-CSDN博客
科普文：软件架构数据库系列之【MySQL 8.0 Server层源码概叙】作者 | 道客-CSDN博客
实行计划可参考：
科普文：软件架构数据库系列之【MySQL控制查询优化器Hints】-CSDN博客
科普文：软件架构数据库系列之【MySQL实行计划Extra梳理】-CSDN博客
实战：搞懂SQL实行流程、SQL实行计划解读和SQL优化_sql的实行流程-CSDN博客
实战：万字小结MySQL慢缘故起因分析_mysql 数据库突然变慢-CSDN博客
MySQL的内存模型和磁盘模型可参考：
科普文：软件架构数据库系列之【MySQL5.7的InnoDB引擎存储结构分析：buffer+disk】_mysql 5.7 innodb存储引擎架构-CSDN博客
科普文：软件架构数据库系列之【MySQL 中的 7 种日记介绍】_mysql5.7 redo日记的范例-CSDN博客
科普文：软件架构数据库系列之【详解InnoDB重做日记Redlog】-CSDN博客
科普文：软件架构数据库系列之【详解InnoDB双写（Doublewrite Buffer）】-CSDN博客
科普文：软件架构数据库系列之【详解InnoDB事务日记(redo log和undo log) 】_redolog和undolog-CSDN博客
科普文：软件架构数据库系列之【详解InnoDB逻辑序列号LSN (log sequence number)】_log sequence number (lsn)-CSDN博客
科普文：软件架构数据库系列之【图解InnoDB规复recovery过程】_innodb-tools-CSDN博客
科普文：软件架构数据库系列之【详解InnoDB规复recovery过程】-CSDN博客
上面基本将SQL命令经过的环节都梳理了一边，包罗：网络通讯、ORM框架、以及整个MySQL体系的组件。这里面还缺少了很紧张的环节“索引”和“锁”，背面将会详细说明。着实上面的梳理原本是想在“SQL命令实行流程”中做梳理的，这里就先放一部门出来。
SQL命令最简单可以分成读写两种命令：select作为读命令、DDL/DML作为写命令。这两种SQL命令固然都要经过如上图所示的环节，但是在详细实行上还是有很大区别，稍后再详说。
MySQL实行SQL过程中，优化器使用基于CBO（成本的优化方式Cost-based Optimization），以SQL语句/SQL剖析树作为输入，使用内置的成本模型和数据字典信息以及存储引擎的统计信息决定使用哪些步调实实际行计划。
本文主要介绍这一过程中CBO的依据统计信息、直方图。
优化器

优化器是数据库的一个核心子系统，你也可以把他明白为MySQL数据库中的一个核心模块或者一个核心功能模块。
优化器也是基于特定模型、各种配置和统计信息举行选择，因此也不可能总是得到最佳实行方式。
MySQL Server可以分为两部门：服务器层以及存储引擎层。此中，优化器工作在服务器层，位于存储引擎API之上。
优化器的工作过程从语义上可以分为四个阶段：

• 逻辑转换，包罗否定消除、等值通报和常量通报、常量表达式求值、外连接转换为内连接、子查询转换、视图归并等；
  
• 优化准备，比方索引ref和range访问方法分析、查询条件扇出值（fan out，过滤后的记录数）分析、常量表检测；
  
• 基于成本优化，包罗访问方法和连接次序的选择等；
  
• 实行计划改进，比方表条件下推、访问方法调整、排序避免以及索引条件下推。
  

1.逻辑转换

MySQL优化器起首可能会以不影响效果的方式对查询举行转换，转换的目标是实验消除某些利用从而更快地实行查询。

显然，查询条件中的1=1是完全多余的。没有须要为每一行数据都实行一次盘算；删除这个条件也不会影响最终的效果。实行EXPLAIN语句之后，通过SHOW WARNINGS命令可以检察逻辑转换之后的SQL语句，从上面的效果可以看出1=1已经不存在了。
下表列出了一些逻辑转换的示例：

   SHOW WARNINGS命令输出中的Message显示了优化器怎样限定查询语句中的表名和列名、应用了重写和优化规则后的查询语句以及优化过程的其他信息。
  目前只有SELECT语句相关的额外信息可以通过SHOW WARNINGS语句举行检察，其他语句（DELETE、INSERT、REPLACE 和UPDATE）显示的信息为空。
  2.优化准备

这个需要通过optimizer_trace优化器跟踪来进一步了解。
优化器跟踪输出主要包含了三个部门：

• join_preparation，准备阶段，返回了字段名扩展之后的SQL语句。对于1=1这种多余的条件，也会在这个步调被删除；
  
• join_optimization，优化阶段。此中condition_processing中包含了各种逻辑转换，等值通报（equality_propagation）。别的constant_propagation表现常量通报，trivial_condition_removal表现无效条件移除；
  
• join_execution，实行阶段。
  

优化器跟踪还可以显示其他基于成本优化的过程，后续我们还会使用该功能。关闭优化器跟踪的方式如下：SET optimizer_trace="enabled=off";

1. mysql> SET optimizer_trace="enabled=on";
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> select * from t1 where id>1 and id<5 and f1=f2;
5. +----+----+----+------+
6. | id | f1 | f2 | c1   |
7. +----+----+----+------+
8. |  2 |  2 |  2 |    2 |
9. |  3 |  3 |  3 |    3 |
10. |  4 |  4 |  4 |    4 |
11. +----+----+----+------+
12. 3 rows in set (0.01 sec)
14. mysql> select * from information_schema.optimizer_trace\G
15. *************************** 1. row ***************************
16.                             QUERY: select * from t1 where id>1 and id<5 and f1=f2
17.                             TRACE: {
18.   "steps": [
19.     {
20.       "join_preparation": {  // 准备阶段、完成SQL改写。
21.         "select#": 1,
22.         "steps": [
23.           {
24.             "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`t1`.`c1` AS `c1` from `t1` where ((`t1`.`id` > 1) and (`t1`.`id` < 5) and (`t1`.`f1` = `t1`.`f2`))"
25.           }
26.         ]
27.       }
28.     },
29.     {
30.       "join_optimization": {
31.         "select#": 1,
32.         "steps": [
33.           {
34.             "condition_processing": {
35.               "condition": "WHERE",
36.               "original_condition": "((`t1`.`id` > 1) and (`t1`.`id` < 5) and (`t1`.`f1` = `t1`.`f2`))",
37.               "steps": [
38.                 {
39.                   "transformation": "equality_propagation",
40.                   "resulting_condition": "((`t1`.`id` > 1) and (`t1`.`id` < 5) and multiple equal(`t1`.`f1`, `t1`.`f2`))"
41.                 },
42.                 {
43.                   "transformation": "constant_propagation",
44.                   "resulting_condition": "((`t1`.`id` > 1) and (`t1`.`id` < 5) and multiple equal(`t1`.`f1`, `t1`.`f2`))"
45.                 },
46.                 {
47.                   "transformation": "trivial_condition_removal",
48.                   "resulting_condition": "((`t1`.`id` > 1) and (`t1`.`id` < 5) and multiple equal(`t1`.`f1`, `t1`.`f2`))"
49.                 }
50.               ]
51.             }
52.           },
53.           {
54.             "substitute_generated_columns": {
55.             }
56.           },
57.           {
58.             "table_dependencies": [
59.               {
60.                 "table": "`t1`",
61.                 "row_may_be_null": false,
62.                 "map_bit": 0,
63.                 "depends_on_map_bits": [
64.                 ]
65.               }
66.             ]
67.           },
68.           {
69.             "ref_optimizer_key_uses": [
70.             ]
71.           },
72.           {
73.             "rows_estimation": [
74.               {
75.                 "table": "`t1`",
76.                 "range_analysis": {
77.                   "table_scan": {
78.                     "rows": 9991,
79.                     "cost": 1007.45
80.                   },
81.                   "potential_range_indexes": [
82.                     {
83.                       "index": "PRIMARY",
84.                       "usable": true,
85.                       "key_parts": [
86.                         "id"
87.                       ]
88.                     }
89.                   ],
90.                   "setup_range_conditions": [
91.                   ],
92.                   "group_index_skip_scan": {
93.                     "chosen": false,
94.                     "cause": "not_group_by_or_distinct"
95.                   },
96.                   "skip_scan_range": {
97.                     "potential_skip_scan_indexes": [
98.                       {
99.                         "index": "PRIMARY",
100.                         "usable": false,
101.                         "cause": "query_references_nonkey_column"
102.                       }
103.                     ]
104.                   },
105.                   "analyzing_range_alternatives": {
106.                     "range_scan_alternatives": [
107.                       {
108.                         "index": "PRIMARY",
109.                         "ranges": [
110.                           "1 < id < 5"
111.                         ],
112.                         "index_dives_for_eq_ranges": true,
113.                         "rowid_ordered": true,
114.                         "using_mrr": false,
115.                         "index_only": false,
116.                         "in_memory": 1,
117.                         "rows": 3,
118.                         "cost": 0.560834,
119.                         "chosen": true
120.                       }
121.                     ],
122.                     "analyzing_roworder_intersect": {
123.                       "usable": false,
124.                       "cause": "too_few_roworder_scans"
125.                     }
126.                   },
127.                   "chosen_range_access_summary": {
128.                     "range_access_plan": {
129.                       "type": "range_scan",
130.                       "index": "PRIMARY",
131.                       "rows": 3,
132.                       "ranges": [
133.                         "1 < id < 5"
134.                       ]
135.                     },
136.                     "rows_for_plan": 3,
137.                     "cost_for_plan": 0.560834,
138.                     "chosen": true
139.                   }
140.                 }
141.               }
142.             ]
143.           },
144.           {
145.             "considered_execution_plans": [
146.               {
147.                 "plan_prefix": [
148.                 ],
149.                 "table": "`t1`",
150.                 "best_access_path": {
151.                   "considered_access_paths": [
152.                     {
153.                       "rows_to_scan": 3,
154.                       "access_type": "range",
155.                       "range_details": {
156.                         "used_index": "PRIMARY"
157.                       },
158.                       "resulting_rows": 3,
159.                       "cost": 0.860834,
160.                       "chosen": true
161.                     }
162.                   ]
163.                 },
164.                 "condition_filtering_pct": 100,
165.                 "rows_for_plan": 3,
166.                 "cost_for_plan": 0.860834,
167.                 "chosen": true
168.               }
169.             ]
170.           },
171.           {
172.             "attaching_conditions_to_tables": {
173.               "original_condition": "((`t1`.`f2` = `t1`.`f1`) and (`t1`.`id` > 1) and (`t1`.`id` < 5))",
174.               "attached_conditions_computation": [
175.               ],
176.               "attached_conditions_summary": [
177.                 {
178.                   "table": "`t1`",
179.                   "attached": "((`t1`.`f2` = `t1`.`f1`) and (`t1`.`id` > 1) and (`t1`.`id` < 5))"
180.                 }
181.               ]
182.             }
183.           },
184.           {
185.             "finalizing_table_conditions": [
186.               {
187.                 "table": "`t1`",
188.                 "original_table_condition": "((`t1`.`f2` = `t1`.`f1`) and (`t1`.`id` > 1) and (`t1`.`id` < 5))",
189.                 "final_table_condition   ": "((`t1`.`f2` = `t1`.`f1`) and (`t1`.`id` > 1) and (`t1`.`id` < 5))"
190.               }
191.             ]
192.           },
193.           {
194.             "refine_plan": [
195.               {
196.                 "table": "`t1`"
197.               }
198.             ]
199.           }
200.         ]
201.       }
202.     },
203.     {
204.       "join_execution": {
205.         "select#": 1,
206.         "steps": [
207.         ]
208.       }
209.     }
210.   ]
211. }
212. MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
213.           INSUFFICIENT_PRIVILEGES: 0
214. 1 row in set (0.01 sec)
216. mysql> SET optimizer_trace="enabled=off";
217. Query OK, 0 rows affected (0.00 sec)
219. mysql>

复制代码

3.基于成本优化

MySQL优化器采用基于成本的优化方式，简化的步调如下：

• 为每个利用指定一个成本；
  
• 盘算每个可能的实行计划各个步调的成本总和；
  
• 选择总成本最小的实行计划。
  

为了找到最佳实行计划，优化器需要比力不同的查询方案。随着查询中表的数量增加，可能的实行计划会出现指数级增长；由于每个表都可能使用全表扫描或者不同的索引访问方法，连接查询可能使用恣意次序。对于少量表的连接查询（通常少于7到10个）可能不会产生问题，但是更多的表可能会导致查询优化的时间比实行时间还要长。
以是优化器不可能遍历全部的实行方案，一种更灵活的优化方法是允许用户控制优化器在查找最佳查询计划时的遍进水平。一般来说，优化器评估的计划越少，则编译查询所花费的时间就越少；但另一方面，由于优化器忽略了一些计划，因此可能找到的不是最佳计划。
控制优化水平
MySQL提供了两个系统变量，可以用于控制优化器的优化水平：

• optimizer_prune_level， 基于返回行数的评估忽略某些实行计划，这种开导式的方法可以极大地淘汰优化时间而且很少丢失最佳计划。因此，该参数的默认设置为1；假如确认优化器错过了最佳计划，可以将该参数设置为0，不过这样可能导致优化时间的增加。
  
• optimizer_search_depth，优化器查找的深度。假如该参数大于查询中表的数量，可以得到更好的实行计划，但是优化时间更长；假如小于表的数量，可以更快完成优化，但可能得到的不是最优计划。比方，对于12、13个或者更多表的连接查询，假如将该参数设置为表的个数，可能需要几小时或者几天时间才能完成优化；假如将该参数修改为3或者4，优化时间可能少于1分钟。该参数的默认值为62；假如不确定是否合适，可以将其设置为0，让优化器主动决定搜索的深度。
  

设置成本常量
MySQL优化器盘算的成本主要包罗I/O成本和CPU成本，每个步调的成本由内置的“成本常量”举行估计。别的，这些成本常量可以通过系统数据库（mysql）中的server_cost和engine_cost两个表举行查询和设置。
成本优化器：MySQL :: MySQL 8.4 Reference Manual :: 10.9.5 The Optimizer Cost Model
统计信息：MySQL :: MySQL 8.4 Reference Manual :: 10.9.6 Optimizer Statistics 
server_cost中存储的是常规服务器利用的成本估计值：

cost_value为空表现使用default_value。此中，

• disk_temptable_create_cost和disk_temptable_row_cost代表了在基于磁盘的存储引擎（比方InnoDB）中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。
  
• key_compare_cost代表了比力记录键的评估成本。增加该值将导致需要比力多个键值的查询计划变得更加昂贵。比方，实行filesort排序的查询计划比通过索引避免排序的查询计划相对更加昂贵。
  
• memory_temptable_create_cost和memory_temptable_row_cost代表了在MEMORY存储引擎中使用内部临时表的评估成本。增加这些值会使得优化器倾向于较少使用内部临时表的查询计划。
  
• row_evaluate_cost代表了盘算记录条件的评估成本。增加该值会导致检查许多数据行的查询计划变得更加昂贵。比方，与读取少量数据行的索引范围扫描相比，全表扫描变得相对昂贵。
  

engine_cost中存储的是特定存储引擎相关利用的成本估计值：

engine_name表现存储引擎，“default”表现全部存储引擎，也可以为不同的存储引擎插入特定的数据。cost_value为空表现使用default_value。此中，

• io_block_read_cost代表了从磁盘读取索引或数据块的成本。增加该值会使读取许多磁盘块的查询计划变得更加昂贵。比方，与读取较少块的索引范围扫描相比，全表扫描变得相对昂贵。
  
• memory_block_read_cost与io_block_read_cost类似，但是它表现从数据库缓冲区读取索引或数据块的成本。
  

1. mysql> explain format=json select * from t1 where id>1 and id<1000 \G;
2. *************************** 1. row ***************************
3. EXPLAIN: {
4.   "query_block": {
5.     "select_id": 1,
6.     "cost_info": {
7.       "query_cost": "200.14"
8.     },
9.     "table": {
10.       "table_name": "t1",
11.       "access_type": "range",
12.       "possible_keys": [
13.         "PRIMARY"
14.       ],
15.       "key": "PRIMARY",
16.       "used_key_parts": [
17.         "id"
18.       ],
19.       "key_length": "4",
20.       "rows_examined_per_scan": 998,
21.       "rows_produced_per_join": 998,
22.       "filtered": "100.00",
23.       "cost_info": {
24.         "read_cost": "100.34",
25.         "eval_cost": "99.80",
26.         "prefix_cost": "200.14",
27.         "data_read_per_join": "23K"
28.       },
29.       "used_columns": [
30.         "id",
31.         "f1",
32.         "f2",
33.         "c1"
34.       ],
35.       "attached_condition": "((`testdb`.`t1`.`id` > 1) and (`testdb`.`t1`.`id` < 1000))"
36.     }
37.   }
38. }
39. 1 row in set, 1 warning (0.00 sec)
41. ERROR:
42. No query specified
44. mysql> explain format=json select * from t1 where id>1 and id<10 \G;
45. *************************** 1. row ***************************
46. EXPLAIN: {
47.   "query_block": {
48.     "select_id": 1,
49.     "cost_info": {
50.       "query_cost": "1.86"
51.     },
52.     "table": {
53.       "table_name": "t1",
54.       "access_type": "range",
55.       "possible_keys": [
56.         "PRIMARY"
57.       ],
58.       "key": "PRIMARY",
59.       "used_key_parts": [
60.         "id"
61.       ],
62.       "key_length": "4",
63.       "rows_examined_per_scan": 8,
64.       "rows_produced_per_join": 8,
65.       "filtered": "100.00",
66.       "cost_info": {
67.         "read_cost": "1.06",
68.         "eval_cost": "0.80",
69.         "prefix_cost": "1.86",
70.         "data_read_per_join": "192"
71.       },
72.       "used_columns": [
73.         "id",
74.         "f1",
75.         "f2",
76.         "c1"
77.       ],
78.       "attached_condition": "((`testdb`.`t1`.`id` > 1) and (`testdb`.`t1`.`id` < 10))"
79.     }
80.   }
81. }
82. 1 row in set, 1 warning (0.00 sec)
84. ERROR:
85. No query specified
87. mysql>
88. mysql> explain format=json select * from t1 where id>1 and id<4000 \G;
89. *************************** 1. row ***************************
90. EXPLAIN: {
91.   "query_block": {
92.     "select_id": 1,
93.     "cost_info": {
94.       "query_cost": "800.97"
95.     },
96.     "table": {
97.       "table_name": "t1",
98.       "access_type": "range",
99.       "possible_keys": [
100.         "PRIMARY"
101.       ],
102.       "key": "PRIMARY",
103.       "used_key_parts": [
104.         "id"
105.       ],
106.       "key_length": "4",
107.       "rows_examined_per_scan": 3998,
108.       "rows_produced_per_join": 3998,
109.       "filtered": "100.00",
110.       "cost_info": {
111.         "read_cost": "401.17",
112.         "eval_cost": "399.80",
113.         "prefix_cost": "800.97",
114.         "data_read_per_join": "93K"
115.       },
116.       "used_columns": [
117.         "id",
118.         "f1",
119.         "f2",
120.         "c1"
121.       ],
122.       "attached_condition": "((`testdb`.`t1`.`id` > 1) and (`testdb`.`t1`.`id` < 4000))"
123.     }
124.   }
125. }
126. 1 row in set, 1 warning (0.00 sec)
128. ERROR:
129. No query specified
131. mysql> explain format=json select * from t1 where id>1 and id<8000 \G;
132. *************************** 1. row ***************************
133. EXPLAIN: {
134.   "query_block": {
135.     "select_id": 1,
136.     "cost_info": {
137.       "query_cost": "1000.65"
138.     },
139.     "table": {
140.       "table_name": "t1",
141.       "access_type": "range",
142.       "possible_keys": [
143.         "PRIMARY"
144.       ],
145.       "key": "PRIMARY",
146.       "used_key_parts": [
147.         "id"
148.       ],
149.       "key_length": "4",
150.       "rows_examined_per_scan": 4995,
151.       "rows_produced_per_join": 4995,
152.       "filtered": "100.00",
153.       "cost_info": {
154.         "read_cost": "501.15",
155.         "eval_cost": "499.50",
156.         "prefix_cost": "1000.65",
157.         "data_read_per_join": "117K"
158.       },
159.       "used_columns": [
160.         "id",
161.         "f1",
162.         "f2",
163.         "c1"
164.       ],
165.       "attached_condition": "((`testdb`.`t1`.`id` > 1) and (`testdb`.`t1`.`id` < 8000))"
166.     }
167.   }
168. }
169. 1 row in set, 1 warning (0.00 sec)
171. ERROR:
172. No query specified
174. mysql>
175. mysql> explain format=json select * from t1 where id>1 and id<10000 \G;
176. *************************** 1. row ***************************
177. EXPLAIN: {
178.   "query_block": {
179.     "select_id": 1,
180.     "cost_info": {
181.       "query_cost": "1000.65"
182.     },
183.     "table": {
184.       "table_name": "t1",
185.       "access_type": "range",
186.       "possible_keys": [
187.         "PRIMARY"
188.       ],
189.       "key": "PRIMARY",
190.       "used_key_parts": [
191.         "id"
192.       ],
193.       "key_length": "4",
194.       "rows_examined_per_scan": 4995,
195.       "rows_produced_per_join": 4995,
196.       "filtered": "100.00",
197.       "cost_info": {
198.         "read_cost": "501.15",
199.         "eval_cost": "499.50",
200.         "prefix_cost": "1000.65",
201.         "data_read_per_join": "117K"
202.       },
203.       "used_columns": [
204.         "id",
205.         "f1",
206.         "f2",
207.         "c1"
208.       ],
209.       "attached_condition": "((`testdb`.`t1`.`id` > 1) and (`testdb`.`t1`.`id` < 10000))"
210.     }
211.   }
212. }
213. 1 row in set, 1 warning (0.00 sec)
215. ERROR:
216. No query specified
218. mysql>

复制代码

t1表上只有id这一个主键索引，统共1w条数据。

上面SQL效果可以看到：where 条件从 id>1 and id<10,   id>1 and id<1000,   id>1 and id<4000, id>1 and id<8000,    id>1 and id<10000去观察cost_info的成本变革。无论where条件扫描的数据范围是多少，始终是走access_type=range的主键索引的范围扫描。

 接下来我们将数据行比力的成本常量row_evaluate_cost从0.1改为1，而且革新内存中的值：

可以看到主键的实行计划很稳定，修改前后成本消耗一样，还是走主键的范围扫描（理论上全部扫描还要走索引，成本是更高的）。

逼迫不走主键索引，比走主键索引，从成本差不多1000.36=1000.65。

留意事项
在使用 FORCE INDEX /IGNORE INDEX 关键字时，需要留意以下几点：

• 逼迫索引可能会导致性能下降：逼迫 MySQL 使用一个不是最优的索引可能会导致查询性能下降，因此在使用前需要慎重思量。
  
• 确保索引的存在：在使用 FORCE INDEX 关键字时，需要确保指定的索引是存在的，否则会导致语法错误。
  
• 谨慎使用：逼迫不使用索引是一种绕过优化器的方法，一般情况下并不建议使用，除非有明确的理由和测试效果支持。
   
  

末了，记得将row_evaluate_cost的还原成默认设置并重新连接数据库：

1. update mysql.server_cost
2. set cost_value= null
3. where cost_name='row_evaluate_cost';
5. flush optimizer_costs;

复制代码

备注：不要容易修改成本常量，由于这样可能导致许多查询计划变得更糟！在大多数生产情况下，保举通过添加优化器提示（optimizer hint）控制查询计划的选择。
 
4.实行计划改进

MySQL提供了一个系统变量 optimizer_switch，用于控制优化器的优化举动。
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch】-CSDN博客
该变量支持全局和会话级别的设置，可以在运行时举行更改。

1. SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

复制代码

此中，command可以是以下形式：

• default，将全部优化举动设置为默认值。
  
• opt_name=default，将指定优化举动设置为默认值。
  
• opt_name=off，禁用指定的优化举动。
  
• opt_name=on，启用指定的优化举动。
  

科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--ICP索引下推】_查询优化器 optimizer-CSDN博客
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--MRR 优化器】-CSDN博客
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch-- Index Merge 索引归并 优化器】-CSDN博客
科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--Hash Join和BNL（Block Nested Loop）优化器】-CSDN博客
固然通过系统变量optimizer_switch可以控制优化器的优化策略，但是一旦改变它的值，后续的查询都会受到影响，除非再次举行设置。
另一种控制优化器策略的方法就是优化器提示（Optimizer Hint）和索引提示（Index Hint），它们只对单个语句有用，而且优先级比optimizer_switch更高。
优化器提示使用 /*+ … */ 解释风格的语法，可以对连接次序、表访问方式、索引使用方式、子查询、语句实行时间限定、系统变量以及资源组等举行语句级别的设置。
科普文：软件架构数据库系列之【MySQL控制查询优化器Hints】-CSDN博客

优化器的作用

优化器按照一定原则生成它认为的SQL命令在当前情形下最有用的实行路径，其作用就是为了生成SQL的实行计划。
优化器的不敷和局限性


MySQL优化器可以很好地处理大部门查询语句，尤其是简单查询。随着MySQL版本的更新，对于复杂查询的实现也更加高效，比方MySQL 8.0支持提供了哈希连接（Hash Join）算法，更换之前基于块的嵌套循环连接（Block Nested-Loop Join），可以极大地提升多表连接的性能。
尽管云云，MySQL优化器目前仍旧存在一些局限性，某些情况下的实现并不是最优方案。我们需要了解这些限定，并通过改写查询或者采用其他方法优化性能。

• 不支持并行实行。MySQL采用单进程多线程模型，不支持多核并行实行特性。可以在应用层拆分查询，实现多个SQL语句的并行查询。
  
• UNION的限定。优化器选项derived_condition_pushdown可以将查询条件下推到子查询内部，包罗使用了UNION子句的派生表，但不是全部的外部查询子句都可以下推，比方LIMIT子句。
  

1. CREATE TABLE t1 (
2.   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3.   c1 INT,
4.   KEY i1 (c1)
5. );
7. CREATE TABLE t2 (
8.   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
9.   c1 INT,
10.   KEY i1 (c1)
11. );
13. EXPLAIN FORMAT=TREE
14. SELECT * FROM
15. (  SELECT id, c1 FROM t1
16.    UNION ALL
17.    SELECT id, c1 FROM t2) v
18. WHERE c1 = 12;
20. -> Table scan on v  (cost=2.16..3.42 rows=2)
21.     -> Union all materialize  (cost=0.90..0.90 rows=2)
22.         -> Covering index lookup on t1 using i1 (c1=12)  (cost=0.35 rows=1)
23.         -> Covering index lookup on t2 using i1 (c1=12)  (cost=0.35 rows=1)
26. EXPLAIN FORMAT=TREE
27. SELECT * FROM
28. (  SELECT id, c1 FROM t1
29.    UNION ALL
30.    SELECT id, c1 FROM t2) v
31. WHERE c1 = 12
32. LIMIT 10;
34. -> Limit: 10 row(s)  (cost=2.16..3.42 rows=2)
35.     -> Table scan on v  (cost=2.16..3.42 rows=2)
36.         -> Union all materialize  (cost=0.90..0.90 rows=2)
37.             -> Covering index lookup on t1 using i1 (c1=12)  (cost=0.35 rows=1)
38.             -> Covering index lookup on t2 using i1 (c1=12)  (cost=0.35 rows=1)

复制代码

• 不允许更新和查询同一张表。（字面意思就是update的表不能出现在from语句中，缘故起因是mysql对子查询的支持是比力单薄的 。）比方：
  

1. UPDATE t1 AS o
2. SET c1 = (SELECT count(*) FROM t1 AS i WHERE i.id=o.id);
4. -- SQL 错误 [1093] [HY000]: You can't specify target table 'o' for update in FROM clause
5. -- 这个问题可以使用派生表解决：
7. UPDATE t1
8. JOIN (SELECT id, count(*) tot FROM t1 GROUP BY id) AS i
9. USING (id)
10. SET t1.c1 = i.tot;

复制代码

MySQL实行update时的[ERROR 1093]处理方法_mysql update 1093-CSDN博客
优化器分类

传统关系型数据库里面的优化器分为CBO和RBO两种。
RBO--- Rule_Based Potimizer  基于规则的优化器

RBO   RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为实行计划（比如在RBO里面，有这么一条规则：有索引使用索引。那么全部带有索引的表在任何情况下都会走索引）以是，RBO现在被许多数据库抛弃（oracle默认是CBO，但是仍旧保存RBO代码，MySQL只有CBO）
CBO---Cost_Based Potimizer   基于成本的优化器

CBO  RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定实行计划。并没有思量目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不实用于该SQL，那么很可能选出来的实行计划就不是最优实行计划了。
CBO在会从目标诸多的实行路径中选择一个成本最小的实行路径来作为实行计划。这里的成本他实际代表了MySQL根据相关统计信息盘算出来目标SQL对应的步调的IO，CPU等消耗。也就是意味着数据库里的成本实际上就是对于实行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息盘算出来的。(盘算过程比力复杂)。
成本优化器：MySQL :: MySQL 8.4 Reference Manual :: 10.9.5 The Optimizer Cost Model
统计信息：MySQL :: MySQL 8.4 Reference Manual :: 10.9.6 Optimizer Statistics 

CBO的一些基本概念

基数Cardinality

基数Cardinality是CBO特有的概念，它是指指定集合包含的记录数，说白了就是指定效果集的行数。Cardinality和成本值的估计息息相关，由于MySQL的指定效果集所消耗的io资源可以近似看做随着该效果集的递增而递增。
可选择率selectivity

可选择率也是CBO特有的概念，它是指施加指定条件后返回效果集的记录数占未施加任何谓词条件的原始效果集的记录数的比率(谓词条件，可以明白为where等限定词举行限定)
selectivity(可选择率) = 施加指定谓词条件后返回效果集的记录数 / 未施加指定谓词条件的效果集的记录数。
我们可以通过可选择率的估计，来确定一个列上是否需要添加索引，实际上，MySQL的CBO也是通过可选择率来确定是否走索引，值越大在做表连接的时候，就越有机会选择这个索引。
基数Cardinality的总结

1. 列值代表的是此列中存储的唯一值的个数（假如此列为primary key 则值为记录的行数）
2. 列值只是个估计值，并禁绝确。
3. 列值不会主动更新，需要通过Analyze table来更新一张表或者mysqlcheck -Aa来举行更新整个数据库。
4. 列值的大小影响Join时是否选用这个Index的判断。

怎样检察MySQL的成本？

实行一次where的成本，可以通过查询当前会话的last_query_cost来盘算当前的查询成本

1. mysql> select * from t1 where id=1;
2. +----+----+----+------+
3. | id | f1 | f2 | c1   |
4. +----+----+----+------+
5. |  1 |  1 |  1 |    1 |
6. +----+----+----+------+
7. 1 row in set (0.00 sec)
9. mysql> show status like '%last_query_cost%';
10. +-----------------+----------+
11. | Variable_name   | Value    |
12. +-----------------+----------+
13. | Last_query_cost | 1.000000 |
14. +-----------------+----------+
15. 1 row in set (0.00 sec)
17. 这个结果表示，MySQL大概要做1.000000页的随机查找才能完成查询，这是根据一系列的统计信息得来的。每个表或者索引的页面个数，索引的基数（Cardinality），索引和数据行的长度，索引分布，优化器在估算成本的时候不考虑缓存，他预设每次都需要一次磁盘IO。
20. mysql> select count(*) from t1;
21. +----------+
22. | count(*) |
23. +----------+
24. |    10000 |
25. +----------+
26. 1 row in set (0.00 sec)
28. mysql> show status like '%last_query_cost%';
29. +-----------------+-------------+
30. | Variable_name   | Value       |
31. +-----------------+-------------+
32. | Last_query_cost | 1005.349000 |
33. +-----------------+-------------+
34. 1 row in set (0.00 sec)
36. mysql> select * from t1 where f1 in (5,100,671);
37. +-----+-----+-----+------+
38. | id  | f1  | f2  | c1   |
39. +-----+-----+-----+------+
40. |   5 |   5 |   5 |    5 |
41. | 100 | 100 | 100 |  100 |
42. | 671 | 671 | 671 |  671 |
43. +-----+-----+-----+------+
44. 3 rows in set (0.01 sec)
46. mysql> show status like '%last_query_cost%';
47. +-----------------+-------------+
48. | Variable_name   | Value       |
49. +-----------------+-------------+
50. | Last_query_cost | 1005.349000 |
51. +-----------------+-------------+
52. 1 row in set (0.00 sec)
54. mysql>

复制代码

MySQL统计信息

MySQL统计信息是指数据库通过采样、统计出来的表、索引的相关信息，比方，表的记录数、聚集索引page个数、字段的Cardinality....。
MySQL在生成实行计划时,需要根据索引的统计信息举行估算,盘算出最低代价（或者说是最小开销）的实行计划.MySQL支持有限的索引统计信息，因存储引擎不同而统计信息收集的方式也不同. MySQL官方关于统计信息的概念介绍几乎等同于无，不过对于已经接触过其它范例数据库的同学而言，明白这个概念应该不在话下。
备注：本文以MySQL8.4为准，背面的验证均在MySQL8.4环境中完成。

统计信息概叙

相对于其它数据库而言，MySQL统计信息无法手工删除。MySQL 8.0之前的版本，MySQL是没有直方图的。
1． 优化器使用统计信息为SQL选择实行计划。
2． MySQL8.0之前的版本没有数据直方图也无法手工删除统计信息。MySQL8.0开始支持直方图，直方图是对统计信息的增强。
3． 在服务器层有优化器，却没有保存数据和索引统计信息。统计信息由存储引擎实现，不同的存储引擎会存储不同的统计信息。
4． 统计信息分为索引统计信息，表统计信息；InnoDB统计信息分为持久化统计信息和非持久化统计信息两类。
表统计信息是数据库基于成本的优化器最紧张的参考信息；统计信息禁绝确，优化器可能给出不够优化的实行计划或者是错误的实行计划。
假如后期有 SQL 走的实行计划不对，或者不是最优的，那就可以断定相关统计信息太旧了，需要及时更新。比如偶然候多表 JOIN 的次序不对，导致查询服从变差，需要人工介入等等。
持久化统计信息在服务器重启期间持久化，从而实现更大的计划稳定性和更一致的查询性能。持久统计信息还提供了控制和灵活性，还有以下额外好处:

• 可以使用innodb_stats_auto_recalc配置选项来控制表发生重大更改后统计信息是否主动更新。
  
• 您可以在CREATE TABLE和ALTER TABLE语句中使用STATS_PERSISTENT、STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句来配置各个表的优化统计信息。
  
• 您可以在mysql中查询优化器统计数据。mysql.innodb_table_stats和mysql.innodb_index_stats表。
  
• 可以检察last_update在mysql.innodb_table_stats和mysql.Innodb_index_stats表，检察上一次更新统计信息的时间。
  
• 您可以手动修改mysql.innodb_table_stats和mysql.Innodb_index_stats表逼迫实行一个特定的查询优化计划，或者在不修改数据库的情况下测试更换计划。
  

默认情况下，持久化优化器统计特性是启用的(innodb_stats_persistent=ON)。

1. mysql> show variables like '%innodb_stats%';
2. +--------------------------------------+-------------+
3. | Variable_name                        | Value       |
4. +--------------------------------------+-------------+
5. | innodb_stats_auto_recalc             | ON          |
6. | innodb_stats_include_delete_marked   | OFF         |
7. | innodb_stats_method                  | nulls_equal |
8. | innodb_stats_on_metadata             | OFF         |
9. | innodb_stats_persistent              | ON          |
10. | innodb_stats_persistent_sample_pages | 20          |
11. | innodb_stats_transient_sample_pages  | 8           |
12. +--------------------------------------+-------------+
13. 7 rows in set, 1 warning (0.01 sec)

复制代码

非持久性优化器统计信息将在每次服务器重启和一些其他利用之后清除，并在下一次访问表时重新盘算。因此，在重新盘算统计信息时可能产生不同的估计，从而导致实行计划的不同选择和查询性能的变革。
统计信息语法

除了成本常量之外，MySQL优化器在优化的过程中还会使用数据字典和存储引擎中的统计信息。比方表的数据量、索引、索引的唯一性以及字段是否可以为空都会影响到实行计划的选择，包罗数据的访问方法和表的连接次序等。
MySQL会在一样平常利用过程中大抵统计表的大小和索引的基数（Cardinality），我们也可以使用ANALYZE TABLE语句手动更新表的统计信息和索引的数据分布。

1. ANALYZE TABLE tbl_name [, tbl_name] ...;

复制代码

这些统计信息默认会持久化到数据字典表mysql.innodb_index_stats和mysql.innodb_table_stats中，也可以通过INFORMATION_SCHEMA视图TABLES、STATISTICS以及INNODB_INDEXES举行检察。
MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.1 ANALYZE TABLE Statement

1. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
2.     TABLE tbl_name [, tbl_name] ...
4. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
5.     TABLE tbl_name
6.     UPDATE HISTOGRAM ON col_name [, col_name] ...
7.         [WITH N BUCKETS]
8.     [{MANUAL | AUTO} UPDATE]
10. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
11.     TABLE tbl_name
12.     UPDATE HISTOGRAM ON col_name [USING DATA 'json_data']
14. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
15.     TABLE tbl_name
16.     DROP HISTOGRAM ON col_name [, col_name] ...

复制代码

直方图

MySQL :: MySQL 8.4 Reference Manual :: 15.7.3.1 ANALYZE TABLE Statement

1. ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c2, c3 WITH 10 BUCKETS;
2. ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS;
3. ANALYZE TABLE t DROP HISTOGRAM ON c2;

复制代码

从MySQL 8.0开始增加了直方图统计（histogram statistics），也就是字段值的分布情况。用户同样可以通过ANALYZE TABLE语句生成或者删除字段的直方图：

1. ANALYZE TABLE tbl_name
2. UPDATE HISTOGRAM ON col_name [, col_name] ...
3. [WITH N BUCKETS];
5. ANALYZE TABLE tbl_name
6. DROP HISTOGRAM ON col_name [, col_name] ...;

复制代码

此中，WITH N BUCKETS用于指定直方图统计时桶的个数，取值范围从1到1024，默认为100。
直方图统计主要用于没有创建索引的字段，当查询使用这些字段与常量举行比力时，MySQL优化器会使用直方图统计评估过滤之后的行数。
比方，以下语句显示了没有直方图统计时的优化器评估：

1. explain analyze
2. select *
3. from employee
4. where salary = 10000;
6. -> Filter: (employee.salary = 10000.00)  (cost=2.75 rows=3) (actual time=0.612..0.655 rows=1 loops=1)
7.     -> Table scan on employee  (cost=2.75 rows=25) (actual time=0.455..0.529 rows=25 loops=1)

复制代码

由于salary字段上既没有索引也没有直方图统计，因此优化器评估返回的行数为3，但实际返回的行数为1。
我们为salary字段创建直方图统计：

1. analyze table employee update histogram on salary;
2. Table        |Op       |Msg_type|Msg_text                                         |
3. -------------|---------|--------|-------------------------------------------------|
4. hrdb.employee|histogram|status  |Histogram statistics created for column 'salary'.|

复制代码

然后再次检察实行计划：

1. explain analyze
2. select *
3. from employee
4. where salary = 10000;
6. -> Filter: (employee.salary = 10000.00)  (cost=2.75 rows=1) (actual time=0.265..0.291 rows=1 loops=1)
7.     -> Table scan on employee  (cost=2.75 rows=25) (actual time=0.206..0.258 rows=25 loops=1)

复制代码

此时，优化器评估的行数和实际返回的行数一致，都是1。
MySQL使用数据字典表column_statistics存储字段值分布的直方图统计，用户可以通过查询视图INFORMATION_SCHEMA.COLUMN_STATISTICS得到直方图信息：

1. select * from information_schema.column_statistics;
2. SCHEMA_NAME|TABLE_NAME|COLUMN_NAME|HISTOGRAM                                            |-----------|----------|-----------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
3. hrdb       |employee  |salary     |{"buckets": [[4000.00, 0.08], [4100.00, 0.12], [4200.00, 0.16], [4300.00, 0.2], [4700.00, 0.24000000000000002], [4800.00, 0.28], [5800.00, 0.32], [6000.00, 0.4], [6500.00, 0.48000000000000004], [6600.00, 0.52], [6800.00, 0.56], [7000.00, 0.600000000000000|

复制代码

删除以上直方图统计的命令如下：

1. analyze table employee drop histogram on salary;

复制代码

索引和直方图之间的区别在于：

• 索引需要随着数据的修改而更新；
  
• 直方图通过命令手动更新，不会影响数据更新的性能。但是，直方图统计会随着数据修改变得过时。
  

相对于直方图统计，优化器会优先选择索引范围优化评估返回的数据行。由于对于索引字段而言，范围优化可以得到更加准确的评估。
 
非持久化统计信息

• 统计信息没有保存在磁盘上，而是频繁的及时盘算统计信息；
  
• 每次对表的访问都会重新盘算其统计信息；
  

假设针对一张大表的频繁查询，那么每次都要重新盘算统计信息，很耗费资源。
持久化统计信息

• 把一张表在某一时候的统计信息值保存在磁盘上；
  
• 避免每次查询时重新盘算；
  
• 假如表更新不是很频繁，或者没有到达 MySQL 必须重新盘算统计信息的临界值，可直接从磁盘上获取；
  
• 纵然 MySQL 服务重启，也可以快速的获取统计信息值；
  
• 统计信息的持久化可以针对全局设置也可以针对单表设置。
  

统计信息的收集

Analyze table收集表和索引统计信息，实用于MyISAM和InnoDB
对于INNODB存储引擎，在以下情况下，会重新收集统计信息:
1.    表第一次打开的时候
2.    表修改的行高出1/16 或者新插入20亿行的时候盘算索引的统计信息
3.     实行show index或者查询information schema下的表
   information_schema.TABLES
  information_schema.STATISTICS
  information_schema.PARTITIONS
  information_schema.KEY_COLUMN_USAGE
  information_schema.TABLE_CONSTRAINTS
  information_schema.REFERENTIAL_CONSTRAINTS
  information_schema.table_constraints
   统计信息参数

MySQL :: MySQL 8.4 Reference Manual :: 17.8.10.1 Configuring Persistent Optimizer Statistics Parameters

#是否持久化统计信息，默认打开
持久性的统计存储在mysql.innodb_table_stats和mysql.innodb_index_stats中
innodb_stats_persistent_sample_pages
#当打开innodb_stats_persistent选项时，这个设置才生效
innodb_stats_transient_sample_pages
#当关闭innodb_stats_persistent选项时生效，采样page数（尤其是后者）不应该设置的太大，否则会产生额外的IO开销，但也不应设置的太小，否则会导致查询计划禁绝确
innodb_stats_auto_recalc
#用于决定是否在表上存在大量更新时（高出10%的记录更新）重新盘算统计信息。默认打开，假如关闭该选项，就需要在每次创建索引或者更改列之后，运行一次ANALYZE TABLE命令来更新统计信息，否则可能选择错误的实行计划。同样的，也可以在CREATE TABLE/ALTER TABLE命令中指定STATS_AUTO_RECALC值
innodb_stats_on_metadata
源码中，对于统计信息的收集，每个表维护一个stat_modified_counter 变量每次DML更新一行，此变量就+1，
当到达更新统计信息的阈值时，此重置为0，数据库层面，innodb_stats_on_metadata参数用来控制是否主动收集元数据信息，设置为false时不更新统计信息,在5.5中此参数默认开启；但是5.6中默认关闭，

innodb_stats_method
这个参数描述了在收集统计信息的时候，对于null的处理，这个参数可以影响到index的统计信息的对于Cardinality的处理。假如使用Nulls_unequal时，而且此时的null数量过多的话，是会影响查询性能的。
Nulls_equal：   全部Null都相当
Nulls_unequal： 全部Null互不相同
Nulls_ignored： 忽略Null      
 
一、持久化统计相关参数：

• innodb_stats_persistent ：是否开启统计信息持久化，默认开启。
  
• innodb_stats_auto_recalc ：是否主动重新盘算持久化统计信息，默认开启。
  

二、详细的更新策略为：

当一张表数据变革高出 10% 后，MySQL 会针对这张表统计信息的更新时间戳做一个判断，检查末了一次更新的时间是否高出 10 秒；假如不到 10 秒，把这张表加到一个统计信息更新队列中，到时间了再重新盘算；假如高出了 10 秒，直接重新盘算，而且更新时间戳。目前这个超时时间写死在 MySQL 代码里，暂时不能更改。不过在某些 MySQL 分支版还可以控制这个时间，比如 Percona。

• innodb_stats_include_delete_marked ：更新持久化统计信息时，是否会盘算已经标志为删除的行。
  默认是关闭的，会获取未提交的脏数据。开启这个选项，MySQL 盘算统计信息时只会思量已经提交的数据。
  

• innodb_stats_persistent_sample_pages ：用于更新持久化索引分布或者其他统计信息的随机基数页，默认 20 个。
  页数越多，统计信息也就越准确，也就有助于查询优化器选择最优的查询计划。
  

什么时候思量更改这个值呢？
1. 当查询计划不是很准确时。比如对比指定表在系统表 mysql.innodb_index_stats 的数据跟 distinct 查询的效果，假如相差太大，可以思量增加这个值。
2. 当 analyze table 变的非常慢时，可能是这个值设置的太大了，此时要思量减小这个值。
三、非持久化统计信息参数

• innodb_stats_transient_sample_pages：设置非持久化统计信息的采样页数目，默认 8 个。
  
• innodb_stats_on_metadata：当统计信息配置为非持久化时生效，默认关闭。
  参数 innodb_stats_persistent 为 0 或者建表时属性 STATS_PERSISTENT=0 才起作用。
  

当开启后，对以下元数据的访问会主动更新统计信息：

• show table status
  
• show index
  
• information_schema.tables
  
• information_schema.statistics
  

以是开启这个选项会额外增加访问表的开销，特别是大表。
还有一些其他的场景会主动更新非持久化统计信息，比如：
1. 表第一次被访问；
2. InnoDB 检测到有十六分之一的表自从上次统计信息盘算后被更新了，这时触发主动更新；
3. MySQL 客户端默认选项 --auto-rehash 打开全部 InnoDB 表，导致全部 InnoDB 表被主动更新统计信息；
四、表属性控制

STATS_AUTO_RECALC
用来指定是否要主动盘算指定 InnoDB 表的统计信息。三个值：default, 0, 1

• default：也就是默认值，依赖 server 端参数 innodb_stats_auto_recalc 的设置效果
  
• 0：表现禁用统计信息的主动重新盘算，也就是永久不重新盘算，需要手动实行 analyze table
  
• 1：表现当表数据有 10% 的数据变革后，则重新盘算持久化统计信息。
  

STATS_PERSISTENT
用来指定是否要开启指定 InnoDB 表的统计信息持久化。三个值：default, 0, 1

• default：依赖 server 端参数 innodb_stats_persistent 的设置
  
• 0：表现不需要持久化统计信息
  
• 1：表现开启持久化统计信息
  

STATS_SAMPLE_PAGES
用来指定盘算统计信息时的采样页数量。
五、手动更新统计信息

analyze table 用来手动更新表统计信息。建议在业务低峰时实行。
六、持久化表统计元数据信息

优化器通过两张元数据表里的数据来决定查询最优实行计划。
表统计信息保存在表 mysql.innodb_table_stats 里

比如表 ytt_sample_persist 的统计信息
紧张列说明：

• n_rows：表的行数
  
• clustered_index_size：主键的数据页个数
  
• sum_of_other_index_sizes：二级索引的数据页个数
  

以下例子可以看testdb库的t1表的统计信息

逼迫收集统计信息

索引统计信息保存在表 mysql.innodb_index_stats 里

比如表t1 索引统计信息

• Index_name：索引名字
  
• stat_name / stat_value：统计名字和对应的值
  
• sample_size：采样页个数
  
• stat_description：统计名字详细信息描述
  

怎样检察统计信息

索引统计信息
Show index from table或information_schema.statistics表
表统计信息
Show table statuslike或information_schema.tables表

***要留意的是，假如生产环境上，大表大数据上，要谨慎做这些线上的数据统计分析，假如在大表上面查询性能会出现大幅度抖动。
表统计信息的分析
    select * from information_schema.tables where table_schema='test'\G;
  *************************** 2. row ***************************
  TABLE_CATALOG: def     ---- 数据表登记目录
  TABLE_SCHEMA: test      ----所属数据库名字
  TABLE_NAME: t2          ----表名称
  TABLE_TYPE: BASE TABLE  ----表范例（view|base table）
  ENGINE: InnoDB          ----使用的存储引擎范例
  VERSION: 10                ------数据文件对应FRM的版本（默认10）
  ROW_FORMAT: Compact    -----行格式（compact|dynamic|fixed）
  TABLE_ROWS: 7             ---行数
  AVG_ROW_LENGTH: 2340 ---------均匀行长度
  DATA_LENGTH: 16384    ---------数据长度
  MAX_DATA_LENGTH: 0   ---------最大数据长度
  INDEX_LENGTH: 16384   ---------索引长度
  DATA_FREE: 0              -----磁盘已经分配但是未使用
  AUTO_INCREMENT: NULL    ------自增
  CREATE_TIME: 2016-03-18 15:26:55  ----创建时间
  UPDATE_TIME: NULL                ----更新时间
  CHECK_TIME: NULL                       ---创建时间
  TABLE_COLLATION: utf8_general_ci    ----字符集
  CHECKSUM: NULL                      ---校验和
  CREATE_OPTIONS:                 
  TABLE_COMMENT:                    ---表描述
  
统计信息中，我们可以判断一个表的碎片的多少，看是否要举行碎片处理
 [(data_length + Index_length) - rows *Avg_row_length] /1024/1024

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。