Mysql有关索引的总结
2019-04-25 16:52:01
<p>用mysql存储过程造了3百万多条数据，以做实验用。</p><p>如下是表结构sql：</p><pre><code><span class="sql1-reservedword">CREATE</span><span class="sql1-space"> </span><span class="sql1-reservedword">TABLE</span><span class="sql1-space"> </span><span class="sql1-delimitedidentifier">`users`</span><span class="sql1-space"> </span><span class="sql1-symbol">(
</span><span class="sql1-delimitedidentifier">`id`</span><span class="sql1-space"> </span><span class="sql1-datatype">INT</span><span class="sql1-symbol">(</span><span class="sql1-number">11</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">NOT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-space"> </span><span class="sql1-reservedword">AUTO_INCREMENT</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`name`</span><span class="sql1-space"> </span><span class="sql1-datatype">VARCHAR</span><span class="sql1-symbol">(</span><span class="sql1-number">50</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">NOT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`email`</span><span class="sql1-space"> </span><span class="sql1-datatype">VARCHAR</span><span class="sql1-symbol">(</span><span class="sql1-number">100</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">NOT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`email_verified_at`</span><span class="sql1-space"> </span><span class="sql1-datatype">TIMESTAMP</span><span class="sql1-space"> </span><span class="sql1-reservedword">NOT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-space"> </span><span class="sql1-reservedword">DEFAULT</span><span class="sql1-space"> </span><span class="sql1-function">CURRENT_TIMESTAMP</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`password`</span><span class="sql1-space"> </span><span class="sql1-datatype">VARCHAR</span><span class="sql1-symbol">(</span><span class="sql1-number">255</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-space"> </span><span class="sql1-reservedword">DEFAULT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`remember_token`</span><span class="sql1-space"> </span><span class="sql1-datatype">VARCHAR</span><span class="sql1-symbol">(</span><span class="sql1-number">20</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-space"> </span><span class="sql1-reservedword">DEFAULT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`created_at`</span><span class="sql1-space"> </span><span class="sql1-datatype">TIMESTAMP</span><span class="sql1-space"> </span><span class="sql1-reservedword">NOT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-space"> </span><span class="sql1-reservedword">DEFAULT</span><span class="sql1-space"> </span><span class="sql1-function">CURRENT_TIMESTAMP</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`updated_at`</span><span class="sql1-space"> </span><span class="sql1-datatype">TIMESTAMP</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-space"> </span><span class="sql1-reservedword">DEFAULT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-symbol">,
</span><span class="sql1-delimitedidentifier">`score`</span><span class="sql1-space"> </span><span class="sql1-datatype">DECIMAL</span><span class="sql1-symbol">(</span><span class="sql1-number">10</span><span class="sql1-symbol">,</span><span class="sql1-number">4</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">NOT</span><span class="sql1-space"> </span><span class="sql1-reservedword">NULL</span><span class="sql1-space"> </span><span class="sql1-reservedword">DEFAULT</span><span class="sql1-space"> </span><span class="sql1-string">'0.0000'</span><span class="sql1-symbol">,
</span><span class="sql1-reservedword">PRIMARY</span><span class="sql1-space"> </span><span class="sql1-reservedword">KEY</span><span class="sql1-space"> </span><span class="sql1-symbol">(</span><span class="sql1-delimitedidentifier">`id`</span><span class="sql1-symbol">),
</span><span class="sql1-reservedword">INDEX</span><span class="sql1-space"> </span><span class="sql1-delimitedidentifier">`index_user_name`</span><span class="sql1-space"> </span><span class="sql1-symbol">(</span><span class="sql1-delimitedidentifier">`name`</span><span class="sql1-symbol">),
</span><span class="sql1-reservedword">INDEX</span><span class="sql1-space"> </span><span class="sql1-delimitedidentifier">`index_user_token`</span><span class="sql1-space"> </span><span class="sql1-symbol">(</span><span class="sql1-delimitedidentifier">`remember_token`</span><span class="sql1-symbol">)
)
</span><span class="sql1-reservedword">COLLATE</span><span class="sql1-symbol">=</span><span class="sql1-string">'utf8_general_ci'
</span><span class="sql1-reservedword">ENGINE</span><span class="sql1-symbol">=</span><span class="sql1-datatype">InnoDB
</span><span class="sql1-reservedword">AUTO_INCREMENT</span><span class="sql1-symbol">=</span><span class="sql1-number">3452916</span><span class="sql1-symbol">;
</span></code></pre><p><br></p><p>索引创建语句示例：</p><pre><code><span class="sql1-reservedword">create</span><span class="sql1-space"> </span><span class="sql1-reservedword">index</span><span class="sql1-space"> </span><span class="sql1-identifier">index_user_token</span><span class="sql1-space"> </span><span class="sql1-reservedword">on</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-symbol">(</span><span class="sql1-identifier">remember_token</span><span class="sql1-symbol">)
</span></code></pre><p><br></p><p>显示表的索引：</p><pre><code><span class="sql1-reservedword">show</span><span class="sql1-space"> </span><span class="sql1-reservedword">keys</span><span class="sql1-space"> </span><span class="sql1-function">in</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-symbol">;
</span></code></pre><p><br></p><p>现在开始我的实践：</p><p>mysql版本：5.7.14<br></p><p>1. 不以通配符开始的like模糊查询。</p><pre><code><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-symbol">*</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">where</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">like</span><span class="sql1-space"> </span><span class="sql1-string">'wendy%'
</span></code></pre><p>如下不会用到索引：</p><pre><code><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-symbol">*</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">where</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">like</span><span class="sql1-space"> </span><span class="sql1-string">'%wendy%'
</span></code></pre><p><br></p><p>2. order by，group by且select查询数据列只能是order by/group by后的这些列。</p><pre><code><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">;
</span></code></pre><p>如下不会用到索引：</p><pre><code><span class="sql1-reservedword"><code><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-symbol">,</span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">score</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">asc</span><span class="sql1-symbol">;
</span></code>select</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-symbol">,</span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">score</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">asc</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">score</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">;<br></span></code><code><span class="sql1-symbol"><code><span class="sql1-reservedword"><code><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">order</span><span class="sql1-space"> </span><span class="sql1-reservedword">by</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-space"> </span><span class="sql1-reservedword">asc</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">score</span><span class="sql1-space"> </span><span class="sql1-reservedword">desc</span><span class="sql1-symbol">;</span></code></span><span class="sql1-symbol"></span></code><br></span></code></pre><p>总结：</p><blockquote><p>group by，order by，用哪列，查哪列，并且只一列，才会用索引<br></p></blockquote><p><br></p><p>3. 索引字段使用了>,=,<,>=,<=,IS NULL和BETWEEN<code></code></p><pre><code><span class="sql1-number"><code><span class="sql1-number"><code><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">remember_token</span><span class="sql1-symbol">,</span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-symbol">,</span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">email</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">where</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">score</span><span class="sql1-space"> </span><span class="sql1-symbol">></span><span class="sql1-space"> </span><span class="sql1-number">100<br></span></code><code><span class="sql1-number"><code><span class="sql1-reservedword"></span><span class="sql1-space"></span><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">remember_token</span><span class="sql1-symbol">,</span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">name</span><span class="sql1-symbol">,</span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">email</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-space"> </span><span class="sql1-reservedword">where</span><span class="sql1-space"> </span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">score</span><span class="sql1-space"> </span><span class="sql1-reservedword">is</span><span class="sql1-space"> </span><span class="sql1-reservedword">null<br></span></code></span></code></span></code></span></code></pre><div><br></div><div>4. max 和min，explain出现Select tables optimized away。</div><div><pre><code><span class="sql1-reservedword">select</span><span class="sql1-space"> </span><span class="sql1-function">min</span><span class="sql1-symbol">(</span><span class="sql1-identifier">u</span><span class="sql1-symbol">.</span><span class="sql1-identifier">score</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">from</span><span class="sql1-space"> </span><span class="sql1-tablename">users</span><span class="sql1-space"> </span><span class="sql1-identifier">u
</span></code></pre></div><div><br></div><div>5. 聚合索引与非聚合索引（也叫辅助索引）。</div><div><br></div><div><br></div>