optoverview.html
来自「sqlite3源码,适合作为嵌入式（embedded）」· HTML 代码 · 共 537 行 · 第 1/2 页
HTML
537 行
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>The SQLite Query Optimizer Overview</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <a name=""></a><h1> The SQLite Query Optimizer Overview</h1><p> This document provides a terse overview of how the query optimizer for SQLite works. This is not a tutorial. The reader is likely to need some prior knowledge of how database engines operate in order to fully understand this text.</p><a name="where_clause"></a><h2>1.0 WHERE clause analysis</h2><p> The WHERE clause on a query is broken up into "terms" where each term is separated from the others by an AND operator.</p><p> All terms of the WHERE clause are analyzed to see if they can be satisfied using indices. Terms that cannot be satisfied through the use of indices become tests that are evaluated against each row of the relevant input tables. No tests are done for terms that are completely satisfied by indices. Sometimes one or more terms will provide hints to indices but still must be evaluated against each row of the input tables.</p><p> The analysis of a term might cause new "virtual" terms to be added to the WHERE clause. Virtual terms can be used with indices to restrict a search. But virtual terms never generate code that is tested against input rows.</p><p> To be usable by an index a term must be of one of the following forms:</p><blockquote><pre><b> </b><i>column</i><b> = </b><i>expression</i><b> </b><i>column</i><b> > </b><i>expression</i><b> </b><i>column</i><b> >= </b><i>expression</i><b> </b><i>column</i><b> < </b><i>expression</i><b> </b><i>column</i><b> <= </b><i>expression</i><b> </b><i>expression</i><b> = </b><i>column</i><b> </b><i>expression</i><b> > </b><i>column</i><b> </b><i>expression</i><b> >= </b><i>column</i><b> </b><i>expression</i><b> < </b><i>column</i><b> </b><i>expression</i><b> <= </b><i>column</i><b> </b><i>column</i><b> IN (</b><i>expression-list</i><b>) </b><i>column</i><b> IN (</b><i>subquery</i><b>) </b><i>column</i><b> IS NULL</b></pre></blockquote><p> If an index is created using a statement like this:</p><blockquote><pre> CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);</pre></blockquote><p> Then the index might be used if the initial columns of the index (columns a, b, and so forth) appear in WHERE clause terms. All index columns must be used with the <tt><b><big>=</big></b></tt> or <tt><b><big>IN</big></b></tt> operators except for the right-most column which can use inequalities. For the right-most column of an index that is used, there can be up to two inequalities that must sandwich the allowed values of the column between two extremes.</p><p> It is not necessary for every column of an index to appear in a WHERE clause term in order for that index to be used. But there can not be gaps in the columns of the index that are used. Thus for the example index above, if there is no WHERE clause term that constraints column c, then terms that constraint columns a and b can be used with the index but not terms that constraint columns d through z. Similarly, no index column will be used (for indexing purposes) that is to the right of a column that is constrained only by inequalities. For the index above and WHERE clause like this:</p><blockquote><pre> ... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'</pre></blockquote><p> Only columns a, b, and c of the index would be usable. The d column would not be usable because it occurs to the right of c and c is constrained only by inequalities.</p><a name="between_opt"></a><h2>2.0 The BETWEEN optimization</h2><p> If a term of the WHERE clause is of the following form:</p><blockquote><pre><b> </b><i>expr1</i><b> BETWEEN </b><i>expr2</i><b> AND </b><i>expr3</i><b></b></pre></blockquote><p> Then two virtual terms are added as follows:</p><blockquote><pre><b> </b><i>expr1</i><b> >= </b><i>expr2</i><b> AND </b><i>expr1</i><b> <= </b><i>expr3</i><b></b></pre></blockquote><p> If both virtual terms end up being used as constraints on an index, then the original BETWEEN term is omitted and the corresponding test is not performed on input rows. Thus if the BETWEEN term ends up being used as an index constraint no tests are ever performed on that term. On the other hand, the virtual terms themselves never causes tests to be performed on input rows. Thus if the BETWEEN term is not used as an index constraint and instead must be used to test input rows, the <i>expr1</i> expression is only evaluated once.</p><a name="or_opt"></a><h2>3.0 The OR optimization</h2><p> If a term consists of multiple subterms containing a common column name and separated by OR, like this:</p><blockquote><pre><b> </b><i>column</i><b> = </b><i>expr1</i><b> OR </b><i>column</i><b> = </b><i>expr2</i><b> OR </b><i>column</i><b> = </b><i>expr3</i><b> OR ...</b></pre></blockquote><p> Then the term is rewritten as follows:</p><blockquote><pre><b> </b><i>column</i><b> IN (</b><i>expr1</i><b>,</b><i>expr2</i><b>,</b><i>expr3</i><b>,</b><i>expr4</i><b>,...)</b></pre></blockquote><p> The rewritten term then might go on to constraint an index using the normal rules for <tt><b><big>IN</big></b></tt> operators. Note that <i>column</i> must be the same column in every OR-connected subterm, although the column can occur on either the left or the right side of the <tt><b><big>=</big></b></tt> operator.</p><p> Suppose the OR clause consists of multiple subterms as follows:</p><blockquote><pre><b> </b><i>expr1</i><b> OR </b><i>expr2</i><b> OR </b><i>expr3</i><b></b></pre></blockquote><p> If every subterm of an OR clause is separately indexable and the transformation to an IN operator described above does not apply, then the OR clause is coded so that it logically works the same as the following:</p><blockquote><pre><b> rowid IN (SELECT rowid FROM </b><i>table</i><b> WHERE </b><i>expr1</i><b> UNION SELECT rowid FROM </b><i>table</i><b> WHERE </b><i>expr2</i><b> UNION SELECT rowid FROM </b><i>table</i><b> WHERE </b><i>expr3</i><b>)</b></pre></blockquote><p> The implemention of the OR clause does not really use subqueries. A more efficient internal mechanism is employed. The implementation also works even for tables where the "rowid" column name has been overloaded for other uses and no longer refers to the real rowid. But the essence of the implementation is captured by the statement above: Separate indices are used to find rowids that satisfy each subterm of the OR clause and then the union of those rowids is used to find all matching rows in the database.</p><a name="like_opt"></a><h2>4.0 The LIKE optimization</h2><p> Terms that are composed of the LIKE or GLOB operator can sometimes be used to constrain indices. There are many conditions on this use:</p><p> <ol> <li>The left-hand side of the LIKE or GLOB operator must be the name of an indexed column.</li> <li>The right-hand side of the LIKE or GLOB must be a string literal that does not begin with a wildcard character.</li> <li>The ESCAPE clause cannot appear on the LIKE operator.</li> <li>The build-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.</li> <li>For the GLOB operator, the column must use the default BINARY collating sequence.</li> <li>For the LIKE operator, if case_sensitive_like mode is enabled then the column must use the default BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must use the built-in NOCASE collating sequence.</li> </ol></p><p> The LIKE operator has two modes that can be set by a pragma. The default mode is for LIKE comparisons to be insensitive to differences of case for latin1 characters. Thus, by default, the following expression is true:</p><blockquote><pre> 'a' LIKE 'A'</pre></blockquote><p> By turned on the case_sensitive_like pragma as follows:</p><blockquote><pre> PRAGMA case_sensitive_like=ON;</pre></blockquote><p> Then the LIKE operator pays attention to case and the example above would evaluate to false. Note that case insensitivity only applies to latin1 characters - basically the upper and lower case letters of English in the lower 127 byte codes of ASCII. International character sets are case sensitive in SQLite unless a user-supplied collating sequence is used. But if you employ a user-supplied collating sequence, the LIKE optimization describe here will never be taken.</p><p> The LIKE operator is case insensitive by default because this is what the SQL standard requires. You can change the default behavior at⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?