基于代价的慢查询冗余建议

5.7；还有，这些转换回刊的绑定倍数都可以微过固定式。为了计数成提议的总回刊，还必并不需要参见一些汇总资料集，如列于数据集量大小不一、元数据集和编目接收者等。MySQL的回刊冗余探头三维主体如下上图简述：

回刊三维

3、基于回刊的编目自由先以择

还是再次拿上述的SQL select * from sync_test1 where name like 'Bobby%' and dt> '2021-07-06'为例，我们刚才MySQL冗余探头是如何根据回刊三维自由先以择编目的。首先，我们从外部在建列于时自组四个候先以编目。

Create Table: CREATE TABLE 于大sync_test1于大 (

于大id于大 int(11) NOT NULL AUTO_INCREMENT,

于大cid于大 int(11) NOT NULL,

于大phone于大 int(11) NOT NULL,

于大name于大 varchar(10) NOT NULL,

于大address于大 varchar(255) DEFAULT NULL,

于大dt于大 datetime DEFAULT NULL,

PRIMARY KEY (于大id于大),

KEY 于大IX_name于大 (于大name于大),

KEY 于大IX_dt于大 (于大dt于大),

KEY 于大IX_dt_name于大 (于大dt于大,于大name于大),

KEY 于大IX_name_dt于大 (于大name于大,于大dt于大)

) ENGINE=InnoDB

通过再次执行explain看成MySQL最终自由先以择了IX_name编目。

mysql> explain select * from sync_test1 where name like 'Bobby%' and dt> '2021-07-06';

+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+

1 | SIMPLE | sync_test1 | NULL | range | IX_name,IX_dt,IX_dt_name,IX_name_dt | IX_name | 12 | NULL | 572 | 36.83 | Using index condition; Using where |

+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+

然后再次打开MySQL扫描冗余探头Trace功能。可以看成，不曾有自由先以择其他三个编目的原因均是因为在其他三个编目上适用range scan的回刊均>= IX_name。

mysql> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACEG;

*************************** 1. row ***************************

TRACE: {

"rows_estimation": [

{

"table": "于大sync_test1于大",

"range_analysis": {

"table_scan": {

"rows": 105084,

"cost": 21628

},

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "IX_name",

"ranges": [

"Bobbyu0000u0000u0000u0000u0000

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows": 572,

"cost": 687.41,

"chosen": true

},

{

"index": "IX_dt",

"ranges": [

"0x99aa0c0000

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows": 38698,

"cost": 46439,

"chosen": false,

"cause": "cost"

},

{

"index": "IX_dt_name",

"ranges": [

"0x99aa0c0000

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows": 38292,

"cost": 45951,

"chosen": false,

"cause": "cost"

},

{

"index": "IX_name_dt",

"ranges": [

"Bobbyu0000u0000u0000u0000u0000

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows": 572,

"cost": 687.41,

"chosen": false,

"cause": "cost"

}

],

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

}

},

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "IX_name",

"rows": 572,

"ranges": [

"Bobbyu0000u0000u0000u0000u0000

]

},

"rows_for_plan": 572,

"cost_for_plan": 687.41,

"chosen": true

}

}

比如说我们根据回刊三维来先于一下回刊的计数每一次：

回头仅有列于成像的回刊：io_cost + cpu_cost = （数据集页个数 * io_block_read_cost）+ (数据集至多 * row_evaluate_cost + 1.1) = （data_length / block_size + 1）+ (rows * 0.2 + 1.1) = (9977856 / 16384 + 1) + (105084 * 0.2 + 1.1) = 21627.9。 回头二级编目IX_name的回刊：io_cost + cpu_cost = (将近以内至多 * io_block_read_cost + 1) + (数据集至多 * row_evaluate_cost + 0.01) = (572 * 1 + 1) + (572*0.2 + 0.01) = 687.41。 回头二级编目IX_dt的回刊：io_cost + cpu_cost = (将近以内至多 * io_block_read_cost + 1) + (数据集至多 * row_evaluate_cost + 0.01) = (38698 * 1 + 1) + (38698*0.2 + 0.01) = 46438.61。 回头二级编目IX_dt_name的回刊: io_cost + cpu_cost = (将近以内至多 * io_block_read_cost + 1) + (数据集至多 * row_evaluate_cost + 0.01) = (38292 * 1 + 1) + (38292 * 0.2 + 0.01) = 45951.41。 回头二级编目IX_name_dt的回刊：io_cost + cpu_cost = (将近以内至多 * io_block_read_cost + 1) + (数据集至多 * row_evaluate_cost + 0.01) = (572 * 1 + 1) + (572*0.2 + 0.01) = 687.41。

多余指明

计数结果在小数上有偏差，因为MySQL适用%g打印浮点数，小数则会以最短的方式也输成。 除“+1.1 +1”这种调节倍数外，Cost计数还则会成现+0.01, 它是为了不可要index scan和range scan成现Cost的垄断。 Cost计数是基于MySQL的绑定参数固定式，如果Cost Model参数改变，optimizer_switch的先以项相异，数据集分布相异都则会造成了最终Cost的计数结果相异。 data_length可转发information_schema.tables，block_size绑定16K。

4、基于回刊的编目举荐就让法

如果就让借助MySQL冗余探头给迟转发计数成最佳编目，那么必并不需要单纯地在业务范围列于上替换成所有候先以编目。对于中央线上业务范围来说，从外部替换成编目的整整空间内成本太高，是必定接受的。MySQL冗余探头先以最佳编目用到的数据集是编目元数据集和汇总资料集，所以我们就让年前提可以通过给它打包括候先以编目的这些数据集，而非单纯替换成编目的这种方式也来解决疑虑。

通过侧重调研MySQL的编码构件和冗余探头时序，我们推断出是十分困难的：一部份长期存在于Server层的frm文件；还有，比如编目；也设；另一部份长期存在于Engine层；还有，或者通过子程序Engine层的接口变量来获自取，比如编目；还有某个加的相异倍数个数、编目分之一据的关键字大小不一等。编目关的的接收者，如下上图简述：

基于回刊的编目举荐就让法

因为MySQL本身就全力支持自；也设读取动力，所以编目举荐就让法是开放性一个全力支持虚；也编目的读取动力，在它上面建立联系打包括候先以编目的空列于，再次搜集抽样数据集，计数成汇总资料集打包括给冗余探头，让冗余探头先以成最优编目，整个子程序关系如下上图简述：

基于回刊的编目举荐就让法

三、编目举荐解决疑虑

因为读取动力本身极为具备仅有面打包括增倍数的控制能力，从外部在MySQL Server层修改也不能维护，所以我们将整个编目举荐该系统拆分别为全力支持虚；也编目的Fakeindex读取动力和仅有面打包括增倍数的Go-Server两部份，主体架构上图如下：

架构上图

首先简要讲解一下Fakeindex读取动力，这是一个轻量级的读取动力，统筹将编目的关的接口微传到Go-Server部份。因为它必须适用C++解决疑虑，与Go-Server间长期存在跨构词子程序的疑虑，我们适用了Go原生的轻量级RPC开放性技术+cgo来不可要替换成重量级的RPC开放性，也不可替换成第三方贫乏打包。变量子程序信道如下简述，MySQL冗余探头子程序Fakeindex的C++变量，参数转换成C构词，然后通过cgo子程序到Go构词的方式也，再次通过Go只用的RPC客户端向增倍数端策划子程序。

子程序信道

比如说将重点阐明开放性语义Go-Server部份，主要时序方法如下。

1、年后置校验

首先根据经验法则，排除一些不全力支持通过替换成编目来提高转发经济性的桥段，如查该系统库的SQL，非select、update、delete SQL等。

2、提自取极为极其重要加名

这一步提自取SQL只用来替换成编目的候先以加名，除了自由先以择给成现今where；还有的加替换成编目，MySQL对顺序、催化、列于相互连接、催化变量（如max）也全力支持适用编目来提高转发经济性。我们对SQL微过构词大树二阶，在大树节点的where、join、order by、group by、催化变量；还有提自取加名，作为编目的候先以加。倍数得注意的是，对于某些SQL，还必需转化列于构件才能可靠地提自取，比如：

select * from tb1, tb2 where a = 1，加a归属tb1还是tb2自取决于谁唯一打包括加a。 select * from tb1 natural join tb2 where tb1.a = 1，在自然相互连接；还有，tb1和tb2绑定适用了相近加名微过相互连接，但SQL；还有并不曾有暴露成这些只可用替换成编目的加。

3、分解成候先以编目

将提自取成的极为极其重要加名微过仅有排加即打包括所有的编目组合，如加A、B、C的所有编目组合是['A', 'B', 'C', 'AB', 'AC', 'BA', 'BC', 'CA', 'CB', 'ABC', 'ACB', 'BAC', 'BCA', 'CAB', 'CBA']，但还必需排除一些编目才能受益所有的候先以编目，比如：

现今长期存在的编目，如长期存在AB，必需排除AB、A，因为MySQL全力支持适用年前缀编目。 将近远将近编目长度3072二进制限制的编目。 一些暂时不全力支持的编目，如带地理分布数据集类型加的空间内编目。

4、数据集搜集

从外部从业务范围数据集库搜集，数据集分别为元数据集、汇总资料集、抽样数据集三部份：

元数据集：即列于的；也设数据集，打包括加；也设、编目；也设，可通过show create table获自取。 汇总资料集：如列于的至多、列于数据集大小不一、编目大小不一，可以通过转发infromation_schema.tables获自取；已长期存在编目的cardinality（极为极其重要倍数：即编目加的相异倍数个数，倍数越加，编目冗余优点越加明显），可以通过转发mysql.innodb_index_stats列于获自取。 抽样数据集：候先以编目为；也编目，搜集的汇总资料集极为打包括；也编目的数据集，这里我们通过搜集原列于的抽样数据集来计数成；也编目的汇总资料集。

数据集搜集

比如说讲解抽样数据集的采样插值，好的采样插值应尽远将近或许搜集到符合标准原列于数据集分布的抽样。比如基于均匀分布随机采样的方式也select * from table where rand() < rate，然而它则会给中央线上数据集库造成了大量I/O的疑虑，严重时可随之而来数据集库超载。所以我们适用了基于块的采样方式也：它参见了MySQL 8.0的直方上图采样插值，如对于一张100万的列于，搜集10万至多，根据主配体的很小倍数远将近倍数将列于数据集均分别为100个区间，每个区间自取一块1000至多据集，搜集数据集的SQL，最后将搜集到的数据集放入采样列于；还有。编码如下：

select A,B,C,id from table where id>= 1000 and id

select A,B,C,id from table where id>= 10000 and id

5、汇总资料集计数

比如说举例指明两个开放性汇总资料集的计数方式也。首先是records_in_range，冗余探头在处置以内转发时，如果可以用编目，就则会子程序该变量估计回头该编目可过滤成的至多，以此要求最终先以用的编目。

比如，对于SQLselect * from table1 where A> 100 and B < 1000，候先以编目A、B来说，冗余探头则会子程序此变量在编目页A上估计A> 100有多少至多，在编目页B上估计B

其次是可用计数编目区分度的cardinality。如果从外部有所区别上述恒等式：抽样加上相异倍数个数 * (原列于至多 / 抽样列于至多)， 如上述的候先以编目A，根据抽样汇总成共计有100个相异倍数，那么在原列于；还有，该加有多少相异倍数？一般以为是10,000 =100 *（1,000,000/100,000）。但这样计数不适用某些桥段，比如状态码字段，或许最多100个相异倍数。针对该疑虑，我们替换成曲线和两趟计数来不可要用到，时序如下：

第一趟计数：自取抽样数据集一半来汇总A的相异倍数个数R1，区间[min_id, min_id+(max_id - min_id) / 2]。 第二趟计数：自取所有抽样仅有A的相异倍数个数R2，区间[min_id, max_id] 计数曲线：R2/R1。 判断曲线：如果曲线之比1.1，为固定倍数100，否则根据采样比例扫描，为10,000。

汇总资料集计数

6、候先以编目回刊就是这两项

这一步让冗余探头老大助我们从候先以编目；还有先以成最佳编目，主要方法如下：

建打包括候先以编目的列于：将候先以编目放入原列于；也设，并把读取动力改成Fakeindex，在举荐动力的mysqld上创立列于。 通过在举荐动力mysqld上explain format=json SQL，获自取冗余探头自由先以择的编目。

倍数得注意的是，MySQL列于最多建64个编目（二级编目），计数所有候先以编目的或许时，适用的是增幅比基准还恐怖的仅有排加插值。如下上图简述，随着加数的减小，候先以编目总数急剧上升，在5个候先以加时的编目组合总数就将近了MySQL远将近倍数，显然不能满足一些复杂SQL的所必需。汇总美团中央线上编目加数分布后，我们推断出，95%以上的编目加数都

候先以编目回刊就是这两项

但即便最多举荐3加编目，在5个候先以加时其排加总数85=也正因如此64。这里我们适用归并就让法。如下上图简述，将所有候先以编目拆分到多个列于；还有，适用两次计数，先让MySQL冗余探头先以成SB一的最佳编目，可适用并行计数保证针对性，再次MySQL先以成SB一所有最佳编目的最佳编目，该提议可以最多全力支持4096个候先以编目，转化远将近编目3加限制，可以全力支持计数成17个候先以加的最佳编目。

候先以编目回刊就是这两项

四、举荐质量保证

为了受益编目举荐质量基本上的主体数据集，我们适用美团数据集库最近一周的中央线下迟转发数据集，共计246G、约3万个SQL模板用例做了一个近期检验。

要求质量保证

从结果可以看成，该系统基本能构成到大部份的迟转发。但还是则会成现无效的举荐，基本上原因如下：

编目举荐计数成的Cost严重贫乏抽样数据集的质量，在当列于数据集分布不均或数据集倾斜时则会造成了汇总资料功能强大现误差，造成了举荐成错误编目。 编目举荐该系统本身长期存在瑕疵，从而造成了举荐成错误编目。 MySQL冗余探头自身长期存在的瑕疵，造成了举荐成错误编目。

因此，我们在业务范围替换成编目年前后减小了编目的理论上性正确性和优点扫描两个方法，整个时序如下简述：

仅有信道

1、理论上性正确性

因为目年前还等同于大规模数据集库储存较慢催化的控制能力，所以无法适用明晰的储存数据集做正确性。我们类似于地并不认为，如果举荐编目在业务范围库上自受益较好的优点，那么在抽样库也则会自受益极好优点。通过显然地在抽样库上单纯再次执行SQL，并替换成编目来正确性其理论上性，正确性结果示范如下：

理论上性正确性

2、优点扫描

毕竟适用采样数据集正确性的局限性，所以当在生激发存环境编目替换成完毕之年前，则会当即对替换成的编目微过优点扫描。一各个方面通过explain正确性编目年前提被显然用到，以及Cost年前提减少；另一各个方面用Flink即时尾随该数据集库的仅有量SQL访问数据集，通过对比编目替换成年前后，该SQL的单纯再次执行整整来判断编目年前提理论上。如果推断出阶段机动性各个方面的某种原因，则当即发成出现异常，亦非到DBA和生产管理人员。分解成的刊告如下：

优点扫描

3、CAD生存环境

当举荐信道成现疑虑时，从外部在中央线上取证正确性疑虑的话，很容易给业务范围带给安仅有隐患，同时也提高了该系统的稳定性。对此我们搭建了离中央线CAD生存环境，来进行数据集库储存开放性了和生激发存环境一样的数据集源，并明晰复刻了中央线上举荐信道的各个方法，在CAD生存环境延时所致情形，复现疑虑、取证根因，间歇正确性简化提议后再次上中央线到生产该系统，进而大幅度冗余现阶段该系统，大幅提高举荐质量。

CAD生存环境

4、检验情形库

在上中央线每一次；还有，多半则会成现简化提议修复了一个Bug，带给了更为多Bug的原因。能否做好编目举荐控制能力的回归检验，从外部要求了举荐质量的稳定性。于是，我们参见了阿里寒的开放性技术提议，著手开放性一个来使完备的检验情形库可用衡量编目举荐增倍数控制能力强弱。但考虑到阻碍MySQL编目自由先以择的心理因素众多，各心理因素间的组合，SQL的复杂性，如果人为去内部设计检验用例是是不切实际的，我们通过下加方式也电子化搜集检验用例：

来进行美团中央线上的丰富数据集，以阻碍MySQL编目自由先以择的心理因素特征为抓弓，从外部从仅有量SQL和迟SQL；还有抽自取最单纯的情形，大幅度更为另行现阶段检验情形库。 在生产的举荐该系统信道上埋点，备用搜集所致情形，移入到现阶段的检验情形库。 对于现阶段数据集不曾有构成到的极端桥段，适用人为构造的提议，多余检验用例。

检验情形库

五、迟转发治水服务于

我们主要从整整维度的三个方向将迟转发并行编目举荐，倡导治水：

迟转发治水服务于

1、过去-上曾迟转发

这类迟转发分属过去激发的，并且长期以来长期存在，总数少，治水推动力欠缺，可通过搜集上曾迟转发会话推断出，分别为两类并行：

开放性数据集库：该类迟转发多半则会被周期性地追捧，如迟转发另行闻刊、翌年刊，可从外部将冗余要求提年前分解成成来，并行它们，一并服务于治水。 比如说数据集库：可将冗余要求从外部并行数据集库模拟器的迟转发模块，让生产自助地自由先以择治水哪些迟转发。

2、现今-另行增迟转发

这类迟转发分属当年前激发的，总数较少，分属治水的重点，也可通过即时搜集迟转发会话推断出，分别为两类并行：

阻碍程度一般的迟转发：可通过即时系统性迟转发会话，对比上曾迟转发，比对成另行增迟转发，并分解成冗余要求，为适用者创立数据集库几率项，第一整整治水。 阻碍程度很小的迟转发：该类多半则会随之而来数据集库出现异常，如迟转发造成了数据集库Load过高，可通过超载检验根因该系统，比对成具体情况的迟转发SQL，并分解成冗余要求，及时接入到超载处置群人，提高超载处置整整。

3、预见-潜在迟转发

这类转发分属当年前还不曾被；也设成迟转发，随着整整挺进或许变成演变成迟转发，对于一些开放性业务范围来说，多半则会随之而来超载，分属他们治水的重点，分别为两类并行：

未上中央线的准迟转发：概念内部设计匆忙上中央线而替换成的另行的准迟转发，可并行发布年前的功能强大检验SMT，Java概念内部设计可通过 agentmain的暂由方式也阻拦被检验用例构成到的SQL，再次通过经验+explain比对成迟转发，并分解成冗余要求，给适用者在所必需管理该系统上创立瑕疵任务，解决后才能发布上中央线。 已上中央线的准迟转发：该类分属当年前再次执行整整较短时间的SQL，随着列于数据集量的减小，则会演变成迟转发，最相似的就是仅有列于成像，这类可通过减小迟转发固定式参数log_queries_not_using_indexes记录到迟会话，并分解成冗余要求，为适用者创立数据集库几率项，第一整整治水。

六、概念内部设计调试原因

当年前，主要以另行增迟转发为突破点，重点为仅有列于成像举荐冗余要求。目年前我们现今色度并行了一小部份业务范围，共计系统性了六千多条迟转发，举荐了一千多条高效编目要求。另外，美团外部的生产同学们也可通过数据集库模拟器自助策划SQL冗余要求工单，如下上图简述：

RDS模拟器策划

另外在美团外部，我们现今和数据集库出现异常打通，解决疑虑了超载推断出、根因系统性、解决提议的电子化处置，极大地提高了超载处置经济性。比如说是一个示范情形，当数据集库战斗群人遭遇出现异常，我们则会拉一个超载群人，先通过根因定位该系统，如果比对成迟转发造成了的，则会竟然子程序SQL冗余要求该系统，举荐成编目，整个处置时序是分钟最高级别，都则会在群人；还有接入最另行消息。如下上图简述：

出现异常检验

七、预见都市著手

毕竟美团旅客量激发近亿最高级别的迟转发数据集，为了解决疑虑对它们的检验系统性，我们还必并不需要提高该系统大规模的数据集并发处置的控制能力。另外，当年前该该系统还是针对单SQL的冗余，不曾有考虑到维护另行编目带给的回刊，如分之一用额外的闪存空间内，使写转换变迟，也不曾有毕竟MySQL先以错编目随之而来其他SQL的机动性某种原因。对于业务范围或者DBA来说，我们更为多关心的是整个数据集库或者战斗群人层面的冗余。

专业人士如阿里寒的DAS则是站在当年前的角度顾及，综合性考虑到各个心理因素，输成必并不需要创立的另行编目、必并不需要改写的编目、必并不需要删除的编目，解决疑虑数据集库机动性远将近化大幅提高，同时远将近化提高闪存空间内能量消耗。预见我们也将大幅度冗余和简化，解决疑虑类似基于Workload的当年前冗余。

>>>>参见资料

MySQL Writing a Custom Storage Engine MySQL Optimizer Guide MySQL 直方上图 Golang cgo 阿里寒-DAS之基于Workload的当年前备用冗余实践中 SQL检验冗余，以后就都交给数据集库地方议会增倍数DAS吧

作者丨粟含

来源丨公众号：美团开放性技术团队（ID：meituantech）

dbaplus社会群体人热烈欢迎广大开放性技术管理人员写稿，写稿的网站：editor@dbaplus.cn

>>>>更为多数据集库干货

来自工行、；还有行、光大、民生、

平安、汇丰、微众、

阿里、的产品寒、二进制的数据集库实战交友，

分布式先以型、移入整修、智能运维等疑难，

老大你捷足先登！

>>>>明晰版方式也

追捧【dbaplus社会群体人】公众号，后台无视【220318】获自取4期精先以“数据集库”意念直播回看和PPT。

户外活动举荐

2022 Gdevops仅有球灵活运维峰则会·北京西站将于6翌年17日举办户外活动，精先以数据集库热门公共政策，共计同探讨数据集库回头向开放性技术融合及投产下的过关斩将和突破，部份公共政策抢先剧微：

【平安银行】数据集库智能化运维实践中之超载自愈 【哔哩哔哩】B站大型户外活动；也象的数据集库保障 【浙江移动】“AN”狂潮下数据集库智能运维的实践中与思考 【vivo】万级比如说规模下的数据集库只用性保障实践中 【短时间弓】短时间弓在NewSQL数据集库的探索与实践中 （持续更为另行……）

点击链接了解更为多峰则会接收者及刊名：_track=SOHU

追捧公众号【dbaplus社会群体人】，获自取更为多原创开放性技术撰文和精先以工具明晰版

。

湖北白癜风医院哪个好
云南白癜风医院哪里好
天津看白癜风去什么医院最好
重庆哪里无痛人流好
北京看妇科的医院哪家好