Q & A研讨会“DevOps MySQL查询调优概论”

首先,我要感谢大家参加我的12月5日,2019年研讨会”介绍为DevOps MySQL查询调优”。记录和幻灯片是可用的网络研讨会页面。

这里有答案的提问参与者,我无法提供在网络研讨会。

问:如何找到存储执行计划和优化器元数据存储在mysql数据字典(例如PS, sys模式)?

答:优化器创建查询执行计划每次当MySQL服务器执行查询。这些计划都没有存储。

然而,一些信息,优化器使用,创建执行计划,存储和可用。它包括。

1. 索引统计信息。你可以找到使用细节显示指数命令:
   
   MySQL

   mysql >显示指数从员工;+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | + |表Non_unique | Key_name | Seq_in_index | Column_name |基数排序| | Sub_part | |空包装| Index_type |评论| Index_comment | + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -主要+员工| | 0 | | 1 |列出|零零| | | 299556 | | BTREE | | | | | 1 |员工first_name | 1 | first_name |零零| | | 1196 | | BTREE | | | | | 1 |员工first_name | 2 | last_name |零零| | | 280646 | | BTREE | | | + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 3行组(0 00秒)

   1 2 3 4 5 6 7 8 9

   mysql> 显示 指数 从 雇员; + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | 表 |Non_unique|Key_name|Seq_in_index|Column_name| 排序 |基数|Sub_part|包装| 零 |Index_type| 评论 |Index_comment| + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + |员工|0| 主 |1|列出|一个|299556年| 零 | 零 || BTREE ||| |员工|1|first_name|1|first_name|一个|1196年| 零 | 零 || BTREE ||| |员工|1|first_name|2|last_name|一个|280646年| 零 | 零 || BTREE ||| + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 3 行 在 集 (0,00sec)

   
   或通过查询information_schema由表:
   
   壳牌

   从information_schema mysql > select *。统计TABLE_SCHEMA =“员工”和table_name =的员工;+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME |基数排序| | SUB_PART包装| | NULLABLE | INDEX_TYPE |评论| INDEX_COMMENT | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | + | def |员工员工主要员工| 0 | | | 1 |列出|零零| | | 299556 | | BTREE | | | | | def |员工员工| 1 | |员工first_name | 1 | first_name |零零| | | 1196 | | BTREE | | | | | def |员工员工| 1 | |员工first_name | 2 | last_name |零零| | | 280646 | | BTREE | | | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 3行组(0 00秒)

   1 2 3 4 5 6 7 8 9

   mysql > 选择 * 从 information_schema 由 在哪里 TABLE_SCHEMA=“员工” 和 table_name=“员工” ; + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | 排序 | 基数 | SUB_PART | 包装 | 可以为空 | INDEX_TYPE | 评论 | INDEX_COMMENT | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | def | 员工 | 员工 | 0 | 员工 | 主 | 1 | 列出 | 一个 | 299556年 | 零 | 零 | | BTREE | | | | def | 员工 | 员工 | 1 | 员工 | first_name | 1 | first_name | 一个 | 1196年 | 零 | 零 | | BTREE | | | | def | 员工 | 员工 | 1 | 员工 | first_name | 2 | last_name | 一个 | 280646年 | 零 | 零 | | BTREE | | | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 3 行 在 集 ( 0 , 00 证券交易委员会 )

2. 另外对于InnoDB表,您可以查询mysql.innodb_index_stats表中存储的物理数据引擎通过优化器:
   
   壳牌

   从mysql mysql > select *。在nodb_index_stats where database_name='employees' and table_name='employees'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | employees | employees | PRIMARY | 2019-12-12 18:22:40 | n_diff_pfx01 | 299556 | 20 | emp_no | | employees | employees | PRIMARY | 2019-12-12 18:22:40 | n_leaf_pages | 886 | NULL | Number of leaf pages in the index | | employees | employees | PRIMARY | 2019-12-12 18:22:40 | size | 929 | NULL | Number of pages in the index | | employees | employees | first_name | 2019-12-12 21:49:02 | n_diff_pfx01 | 1196 | 20 | first_name | | employees | employees | first_name | 2019-12-12 21:49:02 | n_diff_pfx02 | 280646 | 20 | first_name,last_name | | employees | employees | first_name | 2019-12-12 21:49:02 | n_diff_pfx03 | 298471 | 20 | first_name,last_name,emp_no | | employees | employees | first_name | 2019-12-12 21:49:02 | n_leaf_pages | 460 | NULL | Number of leaf pages in the index | | employees | employees | first_name | 2019-12-12 21:49:02 | size | 545 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 8 rows in set (0,01 sec)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   mysql > 选择 * 从 mysql .innodb_index_stats 在哪里 database_name=“员工” 和 table_name=“员工” ; + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | 员工 | 员工 | 主 | 2019年- - - - - -12- - - - - -12 18 : 22 : 40 | n_diff_pfx01 | 299556年 | 20. | 列出 | | 员工 | 员工 | 主 | 2019年- - - - - -12- - - - - -12 18 : 22 : 40 | n_leaf_pages | 886年 | 零 | 数量 的 叶 页面 在 的 指数 | | 员工 | 员工 | 主 | 2019年- - - - - -12- - - - - -12 18 : 22 : 40 | 大小 | 929年 | 零 | 数量 的 页面 在 的 指数 | | 员工 | 员工 | first_name | 2019年- - - - - -12- - - - - -12 21 : 49 : 02 | n_diff_pfx01 | 1196年 | 20. | first_name | | 员工 | 员工 | first_name | 2019年- - - - - -12- - - - - -12 21 : 49 : 02 | n_diff_pfx02 | 280646年 | 20. | first_name , last_name | | 员工 | 员工 | first_name | 2019年- - - - - -12- - - - - -12 21 : 49 : 02 | n_diff_pfx03 | 298471年 | 20. | first_name , last_name , 列出 | | 员工 | 员工 | first_name | 2019年- - - - - -12- - - - - -12 21 : 49 : 02 | n_leaf_pages | 460年 | 零 | 数量 的 叶 页面 在 的 指数 | | 员工 | 员工 | first_name | 2019年- - - - - -12- - - - - -12 21 : 49 : 02 | 大小 | 545年 | 零 | 数量 的 页面 在 的 指数 | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 8 行 在 集 ( 0 , 01 证券交易委员会 )

   
   之间的区别显示指数输出和mysql.innodb_index_stats表内容信息显示指数绝对是虚拟和计算在每一个访问的数据mysql.innodb_index_stats表的物理存储和更新只有当InnoDB存储引擎更新统计数据。
3. 自8.0版:优化器统计即直方图。你可以找到通过查询详细信息information_schema.column_statistics表:
   
   壳牌

   mysql >从information_schema选择直方图。column_statistics - > table_name =“例子”\ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * *直方图:{“水桶”:[[0.6],[2,0.8],[3,1.0]],“数据类型”:“int”、“空值”:0.0,“collation-id”: 8,“最近更新”:“2018-11-07 09:07:19.791470”、“采样率”:1.0,“histogram-type”:“单”、“number-of-buckets-specified”: 3} 1行组(0.00秒)

   1 2 3 4 5 6 7 8 9

   mysql > 选择 柱状图 从 information_schema .column_statistics - - - - - -> 在哪里 table_name= '例子”\ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。 行 * * * * * * * * * * * * * * * * * * * * * * * * * * * 柱状图 : { “桶” : ( ( 1 , 0.6 ] , ( 2 , 0.8 ] , ( 3 , 1.0 ] ] , “数据类型” : “int” , “空值” : 0.0 , “collation-id” : 8 , “最近更新” : “2018-11-07 09:07:19.791470” , “采样率” : 1.0 , “histogram-type” : “单” , “number-of-buckets-specified” : 3 } 1 行 在 集 ( 0.00 证券交易委员会 )

4. 优化器跟踪:优化器执行的动作来解决optimizer_trace_limit查询。这些数据存储在内存中,默认情况下禁用和可用的information_schema.optimizer_trace表:
   
   壳牌

   mysql >设置optimizer_trace = 1;查询好,0行影响(0 00秒)mysql > select count(*)从员工first_name像“%”;+ - - - - - - - - - - - + | count(*) - - - - - - - - - - - | + + | 22039 | + - - - - - - - - - - - + 1行(0,16秒)设置mysql >从information_schema选择*。optimizer_trace \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * *查询:select count(*)从员工first_name像“%”跟踪:{“步骤”:[{“join_preparation”:{"选择"按钮#:1、“步骤”:[{“expanded_query”:“/ *选择# 1 * /选择数(0)作为“count(*)”从“员工”(“雇员”。first_name像“%”)”}]}},{ "join_optimization": { "select#": 1, "steps": [ { ...

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20. 21 22 23 24 25 26 27 28 29日 30. 31日 32

   mysql > 集 optimizer_trace=1 ; 查询 好吧 , 0 行 影响 ( 0 , 00 证券交易委员会 ) mysql > 选择 数 (*) 从 员工 在哪里 first_name 就像 “%” ; + - - - - - - - - - - - + | 数 (*) | + - - - - - - - - - - - + | 22039年 | + - - - - - - - - - - - + 1 行 在 集 ( 0 , 16 证券交易委员会 ) mysql > 选择 * 从 information_schema .optimizer_trace \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。 行 * * * * * * * * * * * * * * * * * * * * * * * * * * * 查询 : 选择 数 (*) 从 员工 在哪里 first_name 就像 “%” 跟踪 : { “步骤” : ( { “join_preparation” : { “选择#” : 1 , “步骤” : ( { “expanded_query” : “/ *选择# 1 * /选择数(0)作为“count(*)”从“员工”(“雇员”。first_name像“%”)” } ] } } , { “join_optimization” : { “选择#” : 1 , “步骤” : ( { 。 。 。

问:是否可以列出所有可能的计划可以为一个单独的查询优化器吗?如何?

答:查询执行计划创建以来每次执行查询时不可能把它们都列出来。

问:如何明确现有计划为一个特定的查询优化器或所有元数据从数据字典吗?

答:因为查询执行计划不存储任何地方,不需要明确的。

问:嘿,我一直使用mysql很长一段时间,现在我想做一个系统,将复杂的查询结合组的列,我想让他们在几秒钟完成,使用最低的内存,你的建议可能会有帮助。

这是一个复杂的问题。我建议你学习如何MySQL可以使用索引集团通过。从“通过优化”章的MySQL用户参考手册。