MySQL 8.0 Reference Manual(讀書筆記51節--Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions(1))

来源:https://www.cnblogs.com/xuliuzai/p/18199960
-Advertisement-
Play Games

本文首發於公眾號:Hunter後端 原文鏈接:MySQL面試必備三之事務 這一篇筆記介紹一下 MySQL 的事務,面試中常被問到關於事務的幾個問題如下: 事務是什麼 為什麼需要事務,事務有什麼作用 事務的特點 事務可能帶來哪些問題 事務有哪些隔離級別,這些隔離級別都可以解決哪些問題 可重覆讀隔離級別 ...


The MySQL query optimizer has different strategies available to evaluate【ɪˈvæljueɪt 評價;評估;估計;】 subqueries:

• For a subquery used with an IN, = ANY, or EXISTS predicate, the optimizer has these choices:

   • Semijoin • Materialization • EXISTS strategy

• For a subquery used with a NOT IN, <> ALL or NOT EXISTS predicate, the optimizer has these choices:

  • Materialization【實現;具體化;物質化;】 • EXISTS strategy

For a derived table, the optimizer has these choices (which also apply to view references and common table expressions):

• Merge the derived table into the outer query block • Materialize the derived table to an internal temporary table

【A limitation on UPDATE and DELETE statements that use a subquery to modify a single table is that the optimizer does not use semijoin or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery.】

1 Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations

A semijoin is a preparation-time transformation that enables multiple execution strategies such as table pullout, duplicate weedout, first match, loose scan, and materialization. The optimizer uses semijoin strategies to improve subquery execution, as described in this section.

For an inner join between two tables, the join returns a row from one table as many times as there are matches in the other table. But for some questions, the only information that matters is whether there is a match, not the number of matches. Suppose that there are tables named class and roster that list classes in a course curriculum and class rosters (students enrolled in each class), respectively. To list the classes that actually have students enrolled, you could use this join:

SELECT class.class_num, class.class_name
 FROM class
 INNER JOIN roster
 WHERE class.class_num = roster.class_num;

However, the result lists each class once for each enrolled student. For the question being asked, this is unnecessary duplication of information.

Assuming that class_num is a primary key in the class table, duplicate suppression is possible by using SELECT DISTINCT, but it is inefficient to generate all matching rows first only to eliminate duplicates later.

The same duplicate-free result can be obtained by using a subquery:

SELECT class_num, class_name
 FROM class
 WHERE class_num IN
 (SELECT class_num FROM roster);

Here, the optimizer can recognize that the IN clause requires the subquery to return only one instance of each class number from the roster table. In this case, the query can use a semijoin; that is, an operation that returns only one instance of each row in class that is matched by rows in roster.

The following statement, which contains an EXISTS subquery predicate, is equivalent to the previous statement containing an IN subquery predicate:

SELECT class_num, class_name
 FROM class
 WHERE EXISTS
 (SELECT * FROM roster WHERE class.class_num = roster.class_num);

In MySQL 8.0.16 and later, any statement with an EXISTS subquery predicate is subject to the same semijoin transforms as a statement with an equivalent IN subquery predicate.

Beginning with MySQL 8.0.17, the following subqueries are transformed into antijoins:

• NOT IN (SELECT ... FROM ...)

• NOT EXISTS (SELECT ... FROM ...).

• IN (SELECT ... FROM ...) IS NOT TRUE

• EXISTS (SELECT ... FROM ...) IS NOT TRUE.

• IN (SELECT ... FROM ...) IS FALSE

• EXISTS (SELECT ... FROM ...) IS FALSE.

In short, any negation of a subquery of the form IN (SELECT ... FROM ...) or EXISTS (SELECT ... FROM ...) is transformed into an antijoin.

An antijoin is an operation that returns only rows for which there is no match. Consider the query shown here:

SELECT class_num, class_name
 FROM class
 WHERE class_num NOT IN
 (SELECT class_num FROM roster);

This query is rewritten internally as the antijoin SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num, which returns one instance of each row in class that is not matched by any rows in roster. This means that, for each row in class, as soon as a match is found in roster, the row in class can be discarded.

Antijoin transformations cannot in most cases be applied if the expressions being compared are nullable. An exception to this rule is that (... NOT IN (SELECT ...)) IS NOT FALSE and its equivalent (... IN (SELECT ...)) IS NOT TRUE can be transformed into antijoins.

Outer join and inner join syntax is permitted in the outer query specification, and table references may be base tables, derived tables, view references, or common table expressions.

In MySQL, a subquery must satisfy these criteria to be handled as a semijoin (or, in MySQL 8.0.17 and later, an antijoin if NOT modifies the subquery):

• It must be part of an IN, = ANY, or EXISTS predicate that appears at the top level of the WHERE or ON clause, possibly as a term in an AND expression. For example:

SELECT ...
 FROM ot1, ...
 WHERE (oe1, ...) IN
 (SELECT ie1, ... FROM it1, ... WHERE ...);

 Here, ot_i and it_i represent tables in the outer and inner parts of the query, and oe_i and ie_i represent expressions that refer to columns in the outer and inner tables.

In MySQL 8.0.17 and later, the subquery can also be the argument to an expression modified by NOT, IS [NOT] TRUE, or IS [NOT] FALSE.

• It must be a single SELECT without UNION constructs.

• It must not contain a HAVING clause.

• It must not contain any aggregate functions (whether it is explicitly or implicitly grouped).

• It must not have a LIMIT clause.

• The statement must not use the STRAIGHT_JOIN join type in the outer query.

• The STRAIGHT_JOIN modifier must not be present.

• The number of outer and inner tables together must be less than the maximum number of tables permitted in a join.

• The subquery may be correlated or uncorrelated. In MySQL 8.0.16 and later, decorrelation【解(抗,去)相關;】 looks at trivially【平凡地;平凡;瑣細地;】 correlated predicates in the WHERE clause of a subquery used as the argument to EXISTS, and makes it possible to optimize it as if it was used within IN (SELECT b FROM ...). The term trivially correlated means that the predicate is an equality predicate, that it is the sole predicate in the WHERE clause (or is combined with AND), and that one operand is from a table referenced in the subquery and the other operand is from the outer query block.

• The DISTINCT keyword is permitted but ignored. Semijoin strategies automatically handle duplicate removal.

• A GROUP BY clause is permitted but ignored, unless the subquery also contains one or more aggregate functions.

• An ORDER BY clause is permitted but ignored, since ordering is irrelevant【ɪˈreləvənt 無關的;不相關的;無關緊要的;】 to the evaluation of semijoin strategies.

If a subquery meets the preceding criteria, MySQL converts it to a semijoin (or, in MySQL 8.0.17 or later, an antijoin if applicable) and makes a cost-based choice from these strategies:

• Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query

.• Duplicate Weedout: Run the semijoin as if it was a join and remove duplicate records using a temporary table.

• FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.

• LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.

• Materialize the subquery into an indexed temporary table that is used to perform a join, where the index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.

Each of these strategies can be enabled or disabled using the following optimizer_switch system variable flags:

• The semijoin flag controls whether semijoins are used. Starting with MySQL 8.0.17, this also applies to antijoins.

• If semijoin is enabled, the firstmatch, loosescan, duplicateweedout, and materialization flags enable finer control over the permitted semijoin strategies.

• If the duplicateweedout semijoin strategy is disabled, it is not used unless all other applicable strategies are also disabled.

• If duplicateweedout is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic pruning during greedy search, which can be avoided by setting optimizer_prune_level=0.

These flags are enabled by default.

The optimizer minimizes differences in handling of views and derived tables. This affects queries that use the STRAIGHT_JOIN modifier and a view with an IN subquery that can be converted to a semijoin. The following query illustrates this because the change in processing causes a change in transformation, and thus a different execution strategy:

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
 FROM t2);
SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

The optimizer first looks at the view and converts the IN subquery to a semijoin, then checks whether it is possible to merge the view into the outer query. Because the STRAIGHT_JOIN modifier in the outer query prevents semijoin, the optimizer refuses the merge, causing derived table evaluation using a materialized table.

EXPLAIN output indicates the use of semijoin strategies as follows:

• For extended EXPLAIN output, the text displayed by a following SHOW WARNINGS shows the rewritten query, which displays the semijoin structure. From this you can get an idea about which tables were pulled out of the semijoin. If a subquery was converted to a semijoin, you should see that the subquery predicate is gone and its tables and WHERE clause were merged into the outer query join list and WHERE clause.

• Temporary table use for Duplicate Weedout is indicated by Start temporary and End temporary in the Extra column. Tables that were not pulled out and are in the range of EXPLAIN output rows covered by Start temporary and End temporary have their rowid in the temporary table.

• FirstMatch(tbl_name) in the Extra column indicates join shortcutting.

• LooseScan(m..n) in the Extra column indicates use of the LooseScan strategy. m and n are key part numbers.

• Temporary table use for materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of .

n MySQL 8.0.21 and later, a semijoin transformation can also be applied to a single-table UPDATE or DELETE statement that uses a [NOT] IN or [NOT] EXISTS subquery predicate, provided that the statement does not use ORDER BY or LIMIT, and that semijoin transformations are allowed by an optimizer hint or by the optimizer_switch setting.

2 Optimizing Subqueries with Materialization

 The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index contains unique values to eliminate duplicates and make the table smaller.

Subquery materialization uses an in-memory temporary table when possible, falling back to on-disk storage if the table becomes too large.

If materialization is not used, the optimizer sometimes rewrites a noncorrelated subquery as a correlated subquery. For example, the following IN subquery is noncorrelated (where_condition involves only columns from t2 and not t1):

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

The optimizer might rewrite this as an EXISTS correlated subquery:

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

Subquery materialization using a temporary table avoids such rewrites and makes it possible to execute the subquery only once rather than once per row of the outer query.

For subquery materialization to be used in MySQL, the optimizer_switch system variable materialization flag must be enabled.With the materialization flag enabled, materialization applies to subquery predicates that appear anywhere (in the select list, WHERE, ON, GROUP BY, HAVING, or ORDER BY), for predicates that fall into any of these use cases:

• The predicate has this form, when no outer expression oe_i or inner expression ie_i is nullable. N is 1 or larger.

(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

• The predicate has this form, when there is a single outer expression oe and inner expression ie. The expressions can be nullable.

oe [NOT] IN (SELECT ie ...)

• The predicate is IN or NOT IN and a result of UNKNOWN (NULL) has the same meaning as a result of FALSE.

The following examples illustrate how the requirement for equivalence of UNKNOWN and FALSE predicate evaluation affects whether subquery materialization can be used. Assume that where_condition involves columns only from t2 and not t1 so that the subquery is noncorrelated.

This query is subject to materialization:

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

Here, it does not matter whether the IN predicate returns UNKNOWN or FALSE. Either way, the row from t1 is not included in the query result.

An example where subquery materialization is not used is the following query, where t2.b is a nullable column:

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
 WHERE where_condition);

The following restrictions apply to the use of subquery materialization:

• The types of the inner and outer expressions must match. For example, the optimizer might be able to use materialization if both expressions are integer or both are decimal, but cannot if one expression is integer and the other is decimal.

• The inner expression cannot be a BLOB.

Use of EXPLAIN with a query provides some indication of whether the optimizer uses subquery materialization:

• Compared to query execution that does not use materialization, select_type may change from DEPENDENT SUBQUERY to SUBQUERY. This indicates that, for a subquery that would be executed once per outer row, materialization enables the subquery to be executed just once.

• For extended EXPLAIN output, the text displayed by a following SHOW WARNINGS includes materialize and materialized-subquery.

In MySQL 8.0.21 and later, MySQL can also apply subquery materialization to a single-table UPDATE or DELETE statement that uses a [NOT] IN or [NOT] EXISTS subquery predicate, provided that the statement does not use ORDER BY or LIMIT, and that subquery materialization is allowed by an optimizer hint or by the optimizer_switch setting.

3 Optimizing Subqueries with the EXISTS Strategy

Certain optimizations are applicable to comparisons that use the IN (or =ANY) operator to test subquery results. This section discusses these optimizations, particularly with regard to the challenges that NULL values present. The last part of the discussion suggests how you can help the optimizer.

Consider the following subquery comparison:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL evaluates queries “from outside to inside.” That is, it first obtains the value of the outer expression outer_expr, and then runs the subquery and captures the rows that it produces.

A very useful optimization is to “inform” the subquery that the only rows of interest are those where the inner expression inner_expr is equal to outer_expr. This is done by pushing down an appropriate equality into the subquery's WHERE clause to make it more restrictive. The converted comparison looks like this:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

After the conversion, MySQL can use the pushed-down equality to limit the number of rows it must examine to evaluate the subquery.

More generally, a comparison of N values to a subquery that returns N-value rows is subject to the same conversion. If oe_i and ie_i represent corresponding outer and inner expression values, this subquery comparison:

(oe_1, ..., oe_N) IN
 (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

Becomes:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
 AND oe_1 = ie_1
 AND ...
 AND oe_N = ie_N)

For simplicity, the following discussion assumes a single pair of outer and inner expression values.

The “pushdown” strategy just described works if either of these conditions is true:

• outer_expr and inner_expr cannot be NULL.

• You need not distinguish【dɪˈstɪŋɡwɪʃ 區分;辨別;分清;使有別於;使出眾;認出;看清;】 NULL from FALSE subquery results. If the subquery is a part of an OR or AND expression in the WHERE clause, MySQL assumes that you do not care. Another instance where the optimizer notices that NULL and FALSE subquery results need not be distinguished is this construct:

... WHERE outer_expr IN (subquery)

In this case, the WHERE clause rejects the row whether IN (subquery) returns NULL or FALSE.

Suppose that outer_expr is known to be a non-NULL value but the subquery does not produce a row such that outer_expr = inner_expr. Then outer_expr IN (SELECT ...) evaluates as follows:

• NULL, if the SELECT produces any row where inner_expr is NULL

• FALSE, if the SELECT produces only non-NULL values or produces nothing

In this situation, the approach of looking for rows with outer_expr = inner_expr is no longer valid. It is necessary to look for such rows, but if none are found, also look for rows where inner_expr is NULL. Roughly speaking, the subquery can be converted to something like this:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
 (outer_expr=inner_expr OR inner_expr IS NULL))

The need to evaluate the extra IS NULL condition is why MySQL has the ref_or_null access method:

mysql> EXPLAIN
 SELECT outer_expr IN (SELECT t2.maybe_null_key
 FROM t2, t3 WHERE ...)
 FROM t1;
*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: t1
...
*************************** 2. row ***************************
 id: 2
 select_type: DEPENDENT SUBQUERY
 table: t2
 type: ref_or_null
possible_keys: maybe_null_key
 key: maybe_null_key
 key_len: 5
 ref: func
 rows: 2
 Extra: Using where; Using index
...

The unique_subquery and index_subquery subquery-specific access methods also have “or NULL” variants.

The additional OR ... IS NULL condition makes query execution slightly more complicated【ˈkɑːmplɪkeɪtɪd 複雜的;難懂的;】 (and some optimizations within the subquery become inapplicable), but generally this is tolerable【ˈtɑːlərəbl 可接受的;可容忍的;還可以的;可忍受的;過得去的;尚好的;】.

The situation is much worse when outer_expr can be NULL. According to the SQL interpretation of NULL as “unknown value,” NULL IN (SELECT inner_expr ...) should evaluate to:

• NULL, if the SELECT produces any rows

• FALSE, if the SELECT produces no rows

For proper evaluation, it is necessary to be able to check whether the SELECT has produced any rows at all, so outer_expr = inner_expr cannot be pushed down into the subquery. This is a problem because many real world subqueries become very slow unless the equality can be pushed down.

Essentially【ɪˈsenʃəli 基本上;本質上;根本上;】, there must be different ways to execute the subquery depending on the value of outer_expr.

The optimizer chooses SQL compliance over speed, so it accounts for the possibility that outer_expr might be NULL:

• If outer_expr is NULL, to evaluate the following expression, it is necessary to execute the SELECT to determine whether it produces any rows:

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

It is necessary to execute the original SELECT here, without any pushed-down equalities of the kind mentioned previously.

• On the other hand, when outer_expr is not NULL, it is absolutely essential that this comparison:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

Be converted to this expression that uses a pushed-down condition:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

Without this conversion, subqueries are slow.

To solve the dilemma【dɪˈlemə 困境;(進退兩難的)窘境;】 of whether or not to push down conditions into the subquery, the conditions are wrapped within “trigger” functions. Thus, an expression of the following form:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

Is converted into:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
 AND trigcond(outer_expr=inner_expr))

More generally, if the subquery comparison is based on several pairs of outer and inner expressions, the conversion takes this comparison:

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

And converts it to this expression:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
 AND trigcond(oe_1=ie_1)
 AND ...
 AND trigcond(oe_N=ie_N)
 )

Each trigcond(X) is a special function that evaluates to the following values:

• X when the “linked” outer expression oe_i is not NULL

• TRUE when the “linked” outer expression oe_i is NULL

【Trigger functions are not triggers of the kind that you create with CREATE TRIGGER.】

Equalities that are wrapped【ræpt 包,裹(禮物等);用…包裹(或包扎、覆蓋等);用…纏繞(或圍緊);】 within trigcond() functions are not first class predicates for the query optimizer. Most optimizations cannot deal with predicates that may be turned on and off at query execution time, so they assume any trigcond(X) to be an unknown function and ignore it. Triggered equalities【ɪˈkwɑlətiz 平等;相等;均等;】 can be used by those optimizations:

• Reference optimizations: trigcond(X=Y [OR Y IS NULL]) can be used to construct ref, eq_ref, or ref_or_null table accesses.

• Index lookup-based subquery execution engines: trigcond(X=Y) can be used to construct unique_subquery or index_subquery accesses.

• Table-condition generator: If the subquery is a join of several tables, the triggered condition is checked as soon as possible.

When the optimizer uses a triggered condition to create some kind of index lookup-based access (as for the first two items of the preceding list), it must have a fallback strategy for the case when the condition is turned off. This fallback strategy is always the same: Do a full table scan. In EXPLAIN output, the fallback shows up as Full scan on NULL key in the Extra column:

mysql> EXPLAIN SELECT t1.col1,
 t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: t1
 ...
*************************** 2. row ***************************
 id: 2
 select_type: DEPENDENT SUBQUERY
 table: t2
 type: index_subquery
possible_keys: key1
 key: key1
 key_len: 5
 ref: func
 rows: 2
 Extra: Using where; Full scan on NULL key

If you run EXPLAIN followed by SHOW WARNINGS, you can see the triggered condition:

*************************** 1. row ***************************
 Level: Note
 Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
 <in_optimizer>(`test`.`t1`.`col1`,
 <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
 on key1 checking NULL
 where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
 trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
 `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
 from `test`.`t1`

The use of triggered conditions has some performance implications【ˌɪmpləˈkeɪʃənz (被)牽連,牽涉;含意;可能的影響(或作用、結果);暗指;】. A NULL IN (SELECT ...) expression now may cause a full table scan (which is slow) when it previously did not. This is the price paid for correct results (the goal of the trigger-condition strategy is to improve compliance, not speed).

For multiple-table subqueries, execution of NULL IN (SELECT ...) is particularly slow because the join optimizer does not optimize for the case where the outer expression is NULL. It assumes that subquery evaluations with NULL on the left side are very rare, even if there are statistics that indicate otherwise. On the other hand, if the outer expression might be NULL but never actually is, there is no performance penalty【ˈpenəlti 處罰;懲罰;刑罰;點球;(對犯規者的)判罰;不利;害處;】.

To help the query optimizer better execute your queries, use these suggestions:

• Declare a column as NOT NULL if it really is. This also helps other aspects of the optimizer by simplifying condition testing for the column.

• If you need not distinguish a NULL from FALSE subquery result, you can easily avoid the slow execution path. Replace a comparison that looks like this:

outer_expr [NOT] IN (SELECT inner_expr FROM ...)

with this expression:

(outer_expr IS NOT NULL) AND (outer_expr [NOT] IN (SELECT inner_expr FROM ...))

Then NULL IN (SELECT ...) is never evaluated because MySQL stops evaluating AND parts as soon as the expression result is clear.

Another possible rewrite:

[NOT] EXISTS (SELECT inner_expr FROM ...
 WHERE inner_expr=outer_expr)
The subquery_materialization_cost_based flag of the optimizer_switch system variable
enables control over the choice between subquery materialization and IN-to-EXISTS subquery
transformation.

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在業務實現的過程中,時常會出現且或關係邏輯的拼接。邏輯運算的組合使用,是實現複雜業務規則和決策支持系統的關鍵技術。 目前袋鼠雲的指標管理平臺、客戶數據洞察平臺、數據資產平臺都有在使用。並且,且或組件已經在 RC 5.0 中添加到組件庫,企業現在可以更加靈活地構建和實施複雜的業務規則。 本文將從前期分 ...
  • 正常上下文在複製一個一模一樣的上下文 appsettings.json添加兩個資料庫連接字元串 Program.cs裡邊一樣添加兩個 控制台遷移命令 必須加上-Context 後邊跟的是我們上下文的名稱 Add-Migration MyMigration -Context MYDBContext22 ...
  • 引言 近期我們註意到很多學生朋友通過郵件嚮導師申請報名,請註意!!!​這是無效的,請必須通過“開源之夏”官方後臺申請報名,請仔細參考這篇【報名攻略】 所以,我們特此舉辦這次宣講會,目的是向所有感興趣的學生詳細介紹Apache DolphinScheduler社區在開源之夏中提供的項目,並且解答學生朋 ...
  • Percona Toolkit 神器全攻略 Percona Toolkit 神器全攻略系列共八篇分為 文章名 文章名 Percona Toolkit 神器全攻略 Percona Toolkit 神器全攻略(實用類) Percona Toolkit 神器全攻略(配置類) Percona Toolkit ...
  • 本文分享自華為雲社區《MySQL全文索引源碼剖析之Insert語句執行過程》 ,作者:GaussDB 資料庫。 1. 背景介紹 全文索引是信息檢索領域的一種常用的技術手段,用於全文搜索問題,即根據單詞,搜索包含該單詞的文檔,比如在瀏覽器中輸入一個關鍵詞,搜索引擎需要找到所有相關的文檔,並且按相關性排 ...
  • 前言 來整理一下緩存雪崩、擊穿和穿透的問題,這個問題在面試中常出現,不是瞎說,我已經遇到幾次了 一、緩存雪崩 1.雪崩 什麼是雪崩,某度給出的解釋 雪崩 當山坡積雪內部的內聚力抗拒不了它所受到的重力拉引時,便向下滑動,引起大量雪體崩塌,人們把這種自然現象稱作雪崩。 說白了就是一部分雪因不可抗力出現問 ...
  • 創建表時應當設置not null,添加一個預設值0或''去替代null。 sum('field')的坑 若一列的所有值都是null,那麼sum函數的結果不是0,而是null,所以可能會因為值的類型相容問題,出現意料之外的情況。 null值會有NPE問題。 count('field')的坑 有null ...
  • 哪些場景下MySQL會使用索引查詢數據,哪些場景下MySQL不會使用索引查詢數據,以及如何使用索引提示來告知查詢優化器使用索引、忽略索引和強制索引索引。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...