{"id":1317,"date":"2016-01-13T14:19:03","date_gmt":"2016-01-13T06:19:03","guid":{"rendered":"http:\/\/www.821121.com\/?p=1317"},"modified":"2021-01-07T14:26:55","modified_gmt":"2021-01-07T06:26:55","slug":"%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0%e4%b9%8bsql-create-efficient-indexes","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1317","title":{"rendered":"\u5b66\u4e60\u7b14\u8bb0\u4e4bSQL-Create Efficient Indexes"},"content":{"rendered":"<h1>Create Efficient Indexes<\/h1>\n<p><!--more--><\/p>\n<h4>Comparison between \u201cIndex Merge\u201d and \u201cComposite Index\u201d<\/h4>\n<p>The \u201cIndex Merge\u201d works well when the indexes that will be merged have similar density. And \u201cComposite Index\u201d works well when the query condition (predicates in the WHERE clause) uses \u201c=\u201d operator.<\/p>\n<p>When the query condition doesn\u2019t use the first column in the composite index, the composite index will generally perform badly.<\/p>\n<h4>The characteristics of the \u201cComposite Index\u201d<\/h4>\n<p>When the leading column (the first column in the index) isn\u2019t used in the query condition, the composite index will most likely not be used. Even under some circumstances the \u201cindex skip scan\u201d can use the composite index, the performance is not very sound.<\/p>\n<p>To create a composite index, two factors should be considered. One is which columns should be included in the index, the other one is the order of the columns in the index. These two factors have great influence on the performance of the index.<\/p>\n<ul>\n<li>The relationship between the density and the order of the columns<br \/>\nIf the indexed columns will be only used using \u201c=\u201d operator, the density of the columns has little impact on the order of the columns.<\/li>\n<li>The impact of \u201c=\u201d operation on the order of the columns<br \/>\nIf the query condition doesn\u2019t use \u201c=\u201d operator for the first column in the composite index, the index will not perform well even if other columns in the index are used with \u201c=\u201d operator in the query condition.<br \/>\n\u201c=\u201d operation is more important than the density of the column when deciding the order of columns in the composite index. So to make the best use of the composite index, we need to take both the density and the column usage (\u201c=\u201d or not) into consideration.<\/li>\n<li>IN list iterator<br \/>\nSometimes, if the leading column of the composite index is used in \u201cBETWEEN...AND\u201d or \u201cLIKE\u201d operation, we can take advantage of \u201cIN list\u201d to rewrite the SQL to improve the performance.<\/li>\n<\/ul>\n<p>For example, suppose the there is one index idx_tab1 (col1, col2) on the table TAB1\u2026<\/p>\n<pre><code class=\"language-sql\">SELECT * FROM TAB1 WHERE col1 between 10 and 20 AND col2 = &#039;A&#039;;<\/code><\/pre>\n<p>If there are only limited values that meets the predicate (col1 between 10 and 20), we can rewrite the SQL as follows.. <\/p>\n<pre><code class=\"language-sql\">SELECT * FROM TAB1\nWHERE col1 IN (10, 15, 20) AND col2 = &#039;A&#039;;<\/code><\/pre>\n<p>The SQL statement above is equal to \u2026 <\/p>\n<pre><code class=\"language-sql\">SELECT * FORM TAB1 WHERE (col1=10 AND col2=\u2019A\u2019) OR (col1=15 AND col2=\u2019A\u2019) OR (col1=20 AND col2=\u2019A\u2019)<\/code><\/pre>\n<p>This way, the composite index idx_tab1 can be used well because the SQL engine can scan less of the index entries.<br \/>\nAnother example, suppose there is one index idx_tab1 (col1, col3, col2) on table TAB1,<\/p>\n<pre><code class=\"language-sql\">SELECT * FROM TAB1 WHERE col1 = &#039;A&#039; and col2=&#039;222&#039;;<\/code><\/pre>\n<p>This time, even the leading column col1 is used in the \u201c=\u201d operator, the second column col3 is not used the WHERE clause. This way, the col2=\u2019222\u2019 can only be used as the \u201cfilter\u201d to check the index entry which is not very efficient.<br \/>\nIf we know the column col3 only have several values, like 1, 2, 3, and then the SQL statement above can be rewritten as follows\u2026<\/p>\n<pre><code class=\"language-sql\">SELECT * FROM TAB1 WHERE col1 = &#039;A&#039; and col2=&#039;222&#039; and col3 in (1, 2, 3);<\/code><\/pre>\n<p>It is equal to the following SQL statement\u2026<\/p>\n<pre><code class=\"language-sql\">SELECT * FROM TAB1 WHERE (col1= &#039;A&#039; and col3=1 and col2=&#039;222&#039;) OR (col1= \u2018A\u2019 and col3=2 and col2=&#039;222&#039;) OR (col1= \u2018A\u2019 and col3=3 and col2=&#039;222&#039;)<\/code><\/pre>\n<p>This way, the column col2, col3 can be used for index entry access which is much efficient than being a data \u201cfilter\u201d.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Create Efficient Indexes<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,48],"tags":[],"class_list":["post-1317","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category-sql"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1317","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1317"}],"version-history":[{"count":2,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1317\/revisions"}],"predecessor-version":[{"id":1319,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1317\/revisions\/1319"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1317"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}