前面以案例的形式介绍了什么是index merge,以及它的使用场景。本文将介绍index merge实现的主要数据结构以及MySQL如何评估index merge的成本。在开始本文之前,需要先理解Range访问相关的数据结构介绍:SEL_ARG结构,SEL_TREE结构。 1. 概述:index merge的
前面以案例的形式介绍了什么是index merge,以及它的使用场景。本文将介绍index merge实现的主要数据结构以及MySQL如何评估index merge的成本。在开始本文之前,需要先理解Range访问相关的数据结构介绍:SEL_ARG结构,SEL_TREE结构。
1. 概述:index merge的数据结构
index merge的主要数据结构仍然是存放在SEL_TREE中:
class SEL_TREE :public Sql_alloc { ... List<sel_imerge> merges; ... };</sel_imerge>
在merges这个list中存放了所有可能的index merge。本文将从几个案例,来看看SEL_TREE/SEL_IMERGE如何代表一个index merge访问方式。本文将不再重复介绍SEL_ARG/SEL_TREE的Range相关结构。
SEL_IMERGE的主要成员是一个SEL_TREE的链表,每一个SEL_TREE代表了一个独立的索引条件,这个链表中多个条件共同构成这个index merge。我们通过两个案例看看一个SEL_TREE如何表示一个index merge。(这里需要注意,SEL_TREE既可以代表一个RANGE条件,也可以代表一个index merge;代表Range时,其merges成员为空)。
2. 案例1:简单的index merge
SELECT * FROM tmp_sel_tree where ( key1_part1 = 1 or (key1_part2 = 2 and key2_part1 = 3) ) or ( key3_part1 = 5 )
这是一个多个索引的index merge,且没有任何的range可以使用。or条件的三个分支,分表可以使用一个独立的索引,其构成的SEL_TREE结构如下:
SEL_TREE | |-->List<sel_imerge> merges; | | / SEL_TREE-> SEL_ARG(key1_part1 = 1) \ SEL_IMERGE1 | SEL_TREE-> SEL_ARG(key2_part1 = 3) \ SEL_TREE-> SEL_ARG(key3_part1 = 5)</sel_imerge>
3. 案例2:单个查询多个index merge
SELECT * FROM tmp_sel_tree where ( key1_part1 = 1 or (key1_part2 = 2 and key2_part1 = 3) ) and ( key3_part1 = 5 or key2_part1 = 5)
这个案例中,And条件两边都可以各自使用index merge,MySQL可以选择其中任何一个执行。对应的SEL_TREE中,将会有多个SEL_IMERGE对象,每个SEL_IMERGE对象里面存储了多个独立的可以使用索引的条件(有独立的SEL_TREE表示):
SEL_TREE | \-->List<sel_imerge> merges; | | / SEL_TREE-> SEL_ARG(key1_part1 = 1) | SEL_IMERGE1 | SEL_TREE-> SEL_ARG(key2_part1 = 3) | \ SEL_TREE-> SEL_ARG(key3_part1 = 5) | | / SEL_TREE-> SEL_ARG(key2_part1 = 5) \ SEL_IMERGE2 | \ SEL_TREE-> SEL_ARG(key3_part1 = 5)</sel_imerge>
MySQL会在选择执行计划时,逐一评估每个SEL_IMERGE的成本,然后选择最优的执行计划。
4. 成本计算
MySQL在计算index merge的成本时,分开考虑了ROR和non-ROR的场景。所以这里先单独介绍一下什么是ROR,后面再介绍MySQL如何区别对待ROR的成本计算。
4.1 什么是Rowid-Ordered Retrieval
Rowid-Ordered Retrieval简称ROR。看下面的说明。有基于索引的查询:
"key_1=c_1 AND ... AND key_n=c_n"
该索引定义为:(key_1, ..., key_N [,a_1, ..., a_m]),且主键列为(a_1, ..., a_m, b1, ..., b_k),并且n >= N。
那么这个查询就是一个ROR查询。简单说明:对于该索引左前缀(key_1,...key_n)都是定值,对应该值的子树顺序是按照剩余索引列来排序的,而剩余的索引列又都是主键最左前缀,所以子树的顺序恰好同主键顺序相同。
(这一段可以参考函数is_key_scan_ror的注释和实现部分)
示例:
CREATE TABLE `tmp_index_merge` ( `id` int(11) NOT NULL, `key1_part1` int(11) NOT NULL, `key1_part2` int(11) NOT NULL, `key2_part1` int(11) NOT NULL, `key2_part2` int(11) NOT NULL, `key2_part3` int(11) NOT NULL, `key3_part1` int(11) NOT NULL DEFAULT '4', PRIMARY KEY (`id`), KEY `ind2` (`key2_part1`,`key2_part2`,`key2_part3`), KEY `ind1` (`key1_part1`,`key1_part2`,`id`), KEY `ind3` (`key3_part1`,`id`) ) ENGINE=InnoDB; explain select * from tmp_index_merge where (key1_part1 = 4333 and key1_part2 = 1657) or (key3_part1 = 2877); j+----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ | 1 | SIMPLE | tmp_index_merge | index_merge | ind1,ind3 | ind1,ind3 | 8,4 | NULL | 2 | Using union(ind1,ind3); Using where | +----+-------------+-----------------+-------------+---------------+-----------+---------+------+------+-------------------------------------+ 这就是一个ROR的index查询。ROR在Explain的执行计划中并没有任何体现,通过在代码中设置断点可以观察到。在函数get_best_disjunct_quick中,代码会跳到标签skip_to_ror_scan处执行。
在对index merge的成本评估时,只有所有的SEL_TREE子树都是ROR的,对应的SEL_IMERGE才是ROR的。后面我们将看看ROR和non-ROR在成本评估上的不同。
4.2 成本概述
一个index merge是由多个SEL_TREE子树组成,每个SEL_TREE对应一个range操作(参考),所以每个SEL_TREE成本仍然会按照range操作类似各自计算成本,并累加。
各个SEL_TREE子树各自获取ROWIDs后,MySQL需要对这些ROWID进行去重,最后根据ROWID获取所有数据。去重操作其实是一个对多组ROWID归并排序的问题。对于ROR和non-ROR场景归并排序复杂度略有不同。对于non-ROR的场景,需要先进行分组排序,然后合并。而对于ROR,因为ROWID是顺序的,所以前面的分组排序就省略了,直接做合并操作,这让non-ROR和ROR在成本计算上有较大的不同。
在完成去重之后,最后是根据ROWID取出主键的成本(对应的二级索引里面取出的ROWID)。
一个细节:如果某个SEL_TREE对应的索引恰好是主键索引时,那么MySQL会在其他SEL_TREE子树扫描时,直接判断扫描出来的ROWID是否在主键对应的SEL_TREE的range内,如果这个ROWID已经存在,则不在记录。这样可以尽可能的减少归并排序的元素个数。我们称这部分成本味"二级ROWID过滤成本"。
4.3 SEL_TREE子树的成本
这部分成本计算与range成本计算相同(参考),这里会将多个子树成本单独计算并累加。
for (every SEL_TREE IN SEL_IMERGE){ cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time); imerge_cost += (*cur_child)->read_cost; ...... }
4.4 non-ROR场景的成本计算
这里通过排序进行去重,是典型的归并排序,如果超过MySQL排序内存的限制,则是典型的外排序。先分组做红黑树排序,然后进行合并。成本分为几部分:创建红黑树、外排时磁盘读写、最后顺序读取排序结果。
4.4.1 去重复成本计算概述
这部分的成本可以完整的参考函数Unique::get_use_cost,这里做一个较为详细的补充说明。
对这个问题做一个简单的抽象:有两部分数据,第一部分有cpk_scan_records条,已排序。第二部分有non_cpk_scan_records未排序,现在需要返回去重后所有数据。单条数据大小为key_size,可用内存为max_in_memory_size。因为前面对第二部数据做了"二级ROWID过滤",所以这部分ROWID跟第一部分没有重复。因此,仅这里的第二部分数据需要进行去重。去重通过一个排序实现。
简单的说,需要对non_cpk_scan_records条记录进行外排序,最大可用内存是max_in_memory_size,单条记录大小是key_size。排序分成两部分,对部分数据做排序,然后合并。
4.4.2 二级ROWID过滤成本
如果有子树SEL_TREE是对应主键聚簇索引,另一部分子树SEL_TREE对应二级索引,那么在遍历二级索引时将取出对应的ROWID,看看是否再聚簇索引的SEL_TREE子树中,如果是,那么可以忽略这个ROWID,以免重复计算(减少后面Unique操作)。这部分的成本计算为:
imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
另外,这里记cpk_scan_records为主键聚簇索引对应的SEL_TREE返回的ROWID数量,non_cpk_scan_records为二级索引对应的所有SEL_TREE返回的ROWID数量。
4.4.3 排序比较成本
需要进行N=non_cpk_scan_records*key_size/max_in_memory_size次排序。在每次排序过程中,如果已经排序好的记录树m个,那么新增一条记录平均需要做log2(m+1)次比较操作,m取值是从1,2...N。比较操作的成本为log2((m+1)!),MySQL使用了如下公式计算log2((m+1)!):
\[n! \approx \sqrt{2{\pi}n}(\frac{n}{e})^n\]
\[\log{n!} \approx \log{\sqrt{2{\pi}n}} + n*\log{\frac{n}{e}} \]
这里log是2为底数,再使用\[log_{n}{m} = \frac{\lg{n}}{\lg{m}}\] 通过此公式底数都可以转换为10进行运算(这一部并不是必须的,不过MySQL是这样计算的)。
阶乘转换参考:斯特靈公式(口味略重,慎入)。
对应的代码段:
result+= n_full_trees * log2_n_fact(max_elements_in_tree + 1.0); result /= TIME_FOR_COMPARE_ROWID;
4.4.4 外排序时候的磁盘IO成本
在外排的时候,需要对所有的数据进行一次IO操作,成本计算如下:
293 result += DISK_SEEK_BASE_COST * n_full_trees * max_elements_in_tree / IO_SIZE; 295 result += DISK_SEEK_BASE_COST * key_size * last_tree_elems / IO_SIZE;
第一行是完整树的IO成本,第二部分是最后一个可能不完整树的IO成本。
4.4.5 合并成本
最后是合并成本,这是一个典型的归并排序,是对K个有序列表进行归并,时间复杂度为:
\[O(N*\lg{K})\]
归并过程中有一次读写操作,IO和比较成本加起来就是合并的成本:
\[\frac{total\_buf\_elems*\log(n\_buffers)}{TIME\_FOR\_COMPARE\_ROWID*\log2} + 2*\frac{total\_buf\_elems*elem\_size}{IO\_SIZE} \]
total_buf_elems是总元素个数;n_buffers子树数量;elem_size为单个元素大小。
未尽的细节:MySQL一次最多对15(MERGEBUFF2)颗子树做归并。
4.4.6 最后的读取
这时,完成了所有的排序操作,最后是读取结果到内存的成本:
result += ceil((double)key_size*nkeys/IO_SIZE);
4.4.7 根据ROWID取出记录的成本
所有非聚簇索引扫描获得ROWID后,最后仍然需要根据这些ROWID获取记录。
对于索引组织表(聚簇索引,InnoDB),这部分的成本计算较为简单,假设聚簇索引的总page为total_pages,这里二级索引取出的rowid数量为rows,该表的总记录树为total_rows,那么成本为:
(rows / total_rows) *total_pages
代码参考:
imerge_cost += get_sweep_read_cost(param, non_cpk_scan_records);
4.5 ROR场景的成本计算
ROR的时候,去重时则少了对子队列的排序,直接是对多个已经排列好的队列做合并排序。所以这里的成本计算相对简单:索引读取,合并排序,最后是根据ROWID取出所有记录的成本。
4.5.1 索引读取成本
这部分计算与索引覆盖扫描计算相同。假设单个索引块大小为BS,索引字段长度味KL,ROWID长度为RL,总是假设索引块有50%为空,如果需要扫描的记录数为RS,那么这部分成本计算为:
\[\frac{RS}{\frac{1}{2}\frac{BS}{(KL+RL)}}\]
参考函数get_index_only_read_time的实现。
4.5.2 合并排序
这次合并排序,是对多个有序列表的合并。若有K个有序列表,总记录数味N,那么其成本为:
\[O(N*\lg{K})\]
这里N为各个SEL_TREE子树对应found_records之和(MySQL这里的计算略微不同)。
4.5.3 根据ROWID取出记录的成本
这部分成本于NON-ROR场景相同,对于索引组织表(聚簇索引,InnoDB),这部分的成本计算较为简单,假设聚簇索引的总page为total_pages,这里二级索引取出的rowid数量为rows,该表的总记录树为total_rows,那么成本为:
(rows / total_rows) *total_pages
在MySQL中,对于上面表达式的rows计算做了一些不一样的处理。这里说一下主要思想,MySQL假设每个SEL_TREE完全独立,总记录数味R,如果有三个SEL_TREE子树,记对应的记录数为R(1),R(2),R(3)。如果数据都均匀分布,那么去重后总记录数为:
(R(1)+R(2)+R(3)) - R(a)*(R(1)*R(2)+R(2)+R(3)+R(1)*R(3))/R(a)^2 + R(a)*((R(1)*R(2)*R3)/R(a)^3)
MySQL这里做了一个近似:
(R(1)+R(2)+R(3)) - R(a)*((R(1)*R(2)*R3)/R(a)^3)
MySQL利用这个近似值作为上面公式的rows。到这里ROR部分成本就完成了。
5 最后
最后,如果index merge的成本比其他执行计划的成本要更小的话,那么MySQL就会选择改执行计划。案例可以参考index merge介绍。
原文地址:index merge的数据结构和成本评估, 感谢原作者分享。

酸性属性には、原子性、一貫性、分離、耐久性が含まれ、データベース設計の基礎です。 1.原子性は、トランザクションが完全に成功するか、完全に失敗することを保証します。 2.一貫性により、データベースがトランザクションの前後に一貫性を保証します。 3.分離により、トランザクションが互いに干渉しないようにします。 4.永続性により、トランザクションの提出後にデータが永久に保存されることが保証されます。

MySQLは、データベース管理システム(DBMS)であるだけでなく、プログラミング言語にも密接に関連しています。 1)DBMSとして、MySQLはデータを保存、整理、取得するために使用され、インデックスを最適化するとクエリのパフォーマンスが向上する可能性があります。 2)SQLとPythonに埋め込まれたプログラミング言語とSQLalchemyなどのORMツールを使用すると、操作を簡素化できます。 3)パフォーマンスの最適化には、インデックス、クエリ、キャッシュ、ライブラリ、テーブル分割、およびトランザクション管理が含まれます。

MySQLはSQLコマンドを使用してデータを管理します。 1.基本コマンドには、select、挿入、更新、削除が含まれます。 2。高度な使用には、参加、サブクエリ、および集計関数が含まれます。 3.一般的なエラーには、構文、ロジック、パフォーマンスの問題が含まれます。 4。最適化のヒントには、インデックスの使用、Select*の回避、制限の使用が含まれます。

MySQLは、データの保存と管理に適した効率的なリレーショナルデータベース管理システムです。その利点には、高性能クエリ、柔軟なトランザクション処理、豊富なデータ型が含まれます。実際のアプリケーションでは、MySQLはeコマースプラットフォーム、ソーシャルネットワーク、コンテンツ管理システムでよく使用されますが、パフォーマンスの最適化、データセキュリティ、スケーラビリティに注意を払う必要があります。

SQLとMySQLの関係は、標準言語と特定の実装との関係です。 1.SQLは、リレーショナルデータベースの管理と操作に使用される標準言語であり、データの追加、削除、変更、クエリを可能にします。 2.MYSQLは、SQLを運用言語として使用し、効率的なデータストレージと管理を提供する特定のデータベース管理システムです。

INNODBは、レドログと非論的なものを使用して、データの一貫性と信頼性を確保しています。 1.レドログは、クラッシュの回復とトランザクションの持続性を確保するために、データページの変更を記録します。 2.Undologsは、元のデータ値を記録し、トランザクションロールバックとMVCCをサポートします。

説明コマンドのキーメトリックには、タイプ、キー、行、および追加が含まれます。 1)タイプは、クエリのアクセスタイプを反映しています。値が高いほど、constなどの効率が高くなります。 2)キーは使用されているインデックスを表示し、nullはインデックスがないことを示します。 3)行はスキャンされた行の数を推定し、クエリのパフォーマンスに影響します。 4)追加の情報を最適化する必要があるというFilesortプロンプトを使用するなど、追加情報を提供します。

Temporaryを使用すると、MySQLクエリに一時テーブルを作成する必要があることが示されています。これは、異なる列、またはインデックスされていない列を使用して順番に一般的に見られます。インデックスの発生を回避し、クエリを書き直し、クエリのパフォーマンスを改善できます。具体的には、expliect出力に使用を使用する場合、MySQLがクエリを処理するために一時テーブルを作成する必要があることを意味します。これは通常、次の場合に発生します。1)個別またはグループビーを使用する場合の重複排除またはグループ化。 2)Orderbyに非インデックス列が含まれているときに並べ替えます。 3)複雑なサブクエリを使用するか、操作に参加します。最適化方法には以下が含まれます。1)OrderbyとGroupB


ホットAIツール

Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。

Undress AI Tool
脱衣画像を無料で

Clothoff.io
AI衣類リムーバー

AI Hentai Generator
AIヘンタイを無料で生成します。

人気の記事

ホットツール

mPDF
mPDF は、UTF-8 でエンコードされた HTML から PDF ファイルを生成できる PHP ライブラリです。オリジナルの作者である Ian Back は、Web サイトから「オンザフライ」で PDF ファイルを出力し、さまざまな言語を処理するために mPDF を作成しました。 HTML2FPDF などのオリジナルのスクリプトよりも遅く、Unicode フォントを使用すると生成されるファイルが大きくなりますが、CSS スタイルなどをサポートし、多くの機能強化が施されています。 RTL (アラビア語とヘブライ語) や CJK (中国語、日本語、韓国語) を含むほぼすべての言語をサポートします。ネストされたブロックレベル要素 (P、DIV など) をサポートします。

SAP NetWeaver Server Adapter for Eclipse
Eclipse を SAP NetWeaver アプリケーション サーバーと統合します。

WebStorm Mac版
便利なJavaScript開発ツール

MinGW - Minimalist GNU for Windows
このプロジェクトは osdn.net/projects/mingw に移行中です。引き続きそこでフォローしていただけます。 MinGW: GNU Compiler Collection (GCC) のネイティブ Windows ポートであり、ネイティブ Windows アプリケーションを構築するための自由に配布可能なインポート ライブラリとヘッダー ファイルであり、C99 機能をサポートする MSVC ランタイムの拡張機能が含まれています。すべての MinGW ソフトウェアは 64 ビット Windows プラットフォームで実行できます。

VSCode Windows 64 ビットのダウンロード
Microsoft によって発売された無料で強力な IDE エディター
