mysql获取表索引信息的方法 mysql获取表索引类型的技巧-小浪学习网

<p>要快速查看<a >mysql</a>表的索引信息和类型，最直接的方法是使用show index from table_name命令，它能详细列出索引名称、涉及的列和索引类型（如btree、hash等），而通过查询information_schema.statistics表则可实现更灵活的元数据检索，两种方法结合expl<a >ai</a>n分析查询执行计划，可全面掌握索引的使用情况与性能影响，从而进行有效优化。</p> <p><img src=”https://img.php.cn/upload/article/001/503/042/175504782778141.jpeg” alt=”mysql获取表索引信息的方法 mysql获取表索引类型的技巧”></p> <p>要快速查看MySQL表的索引信息和它们的类型，最直接的方法是使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SHOW INDEX FROM table_name;</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>命令。它能给你一个关于表上所有索引的详细概览，包括索引的名称、涉及的列以及最重要的——索引的类型。如果需要更灵活的查询或从系统视图层面获取信息，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>information_schema.STATISTICS</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>表也是一个非常强大的<a >工具</a>。</p> <h3>解决方案</h3> <p>获取MySQL表索引信息和类型主要有两种途径，各有侧重。</p> <p>首先，最常用也最直观的是 <div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SHOW INDEX FROM table_name;</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 命令。这个命令会返回一个结果集，其中包含了表上定义的所有索引的详细元数据。它会列出很多列，但我们主要关注几个关键的：</p> <ul> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Key_name</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>: 索引的名称。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>column_name</pre><div class=”contentsignin”></div></div>: 索引包含的列名。对于复合索引，同一<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Key_name</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>会有多行，每行对应一个列。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Index_type</pre><div class=”contentsignin”></div></div>: 这就是我们想知道的索引类型，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BTREE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>HASH</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FULLTEXT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SPATIAL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>等。在InnoDB存储引擎中，绝大多数索引都是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BTREE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>类型的。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Non_unique</pre><div class=”contentsignin”></div></div>: 如果是0，表示这是一个唯一索引；如果是1，表示是非唯一索引。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Cardinality</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>: 索引中唯一值的估计数量。这个值越高，索引的区分度就越好，对查询优化器的帮助也越大。</li> </ul> <p>举个例子，如果你有一个名为<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>users</pre><div class=”contentsignin”></div></div>的表，想看看它的索引：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>SHOW INDEX FROM users;</pre><div class=”contentsignin”></div></div><p>你可能会看到类似这样的输出：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:php;toolbar:false;’>Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed NULL Index_type Comment Index_comment ——- ———– ———– ————— ———– ———– ———– ———– ——- ——- ———– ——- ————- users 0 PRIMARY 1 id A 100000 NULL NULL BTREE users 1 idx_username 1 username A 98000 NULL NULL BTREE users 1 idx_status_created_at 1 status A 5 NULL NULL BTREE users 1 idx_status_created_at 2 created_at A 100000 NULL NULL BTREE</pre><div class=”contentsignin”></div></div><p>从这个结果里，我们可以清楚地看到<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>PRIMARY</pre><div class=”contentsignin”></div></div>索引（主键）和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>idx_username</pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>idx_status_created_at</pre><div class=”contentsignin”></div></div>等自定义索引，它们都是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BTREE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>类型。</p> <p>其次，对于需要更灵活的查询，或者在应用程序中批量获取索引信息时，查询<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>information_schema.STATISTICS</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>表是更好的选择。<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>information_schema</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>是一个虚拟数据库，包含了MySQL服务器的元数据信息。</p> <p>你可以这样查询：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE, NON_UNIQUE, CARDINALITY FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ‘your_database_name’ AND TABLE_NAME = ‘your_table_name’;</pre><div class=”contentsignin”></div></div><p>将<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>your_database_name</pre><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>your_table_name</pre><div class=”contentsignin”></div></div>替换成你实际的数据库名和表名。这种方式的优势在于你可以通过sql语句进行更复杂的过滤、连接和聚合操作，比如查找某个数据库中所有表的索引，或者统计某种类型的索引数量。不过，需要注意的是，直接查询<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>information_schema</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>在大型数据库中可能会比较慢，因为它需要从内部数据字典中读取信息。</p> <h3> <a >为什么</a>需要了解索引类型？它对查询性能有何影响？</h3> <p>坦白说，很多时候我们创建索引，可能只是习惯性地在WHERE子句涉及的列上加一个，或者在JOIN的列上加一个，但很少会深入思考“索引类型”这个东西。但它真的挺重要的，尤其是在你遇到一些难以解释的慢查询时，回过头来看看索引类型，可能会发现一些端倪。</p> <p>MySQL（特别是InnoDB）最常见的索引类型是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BTREE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>（B-Tree），它也是默认的。B-Tree索引的结构决定了它在处理等值查询（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>=</pre><div class=”contentsignin”></div></div>）、范围查询（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>></pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><</pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div>）、排序（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ORDER BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>）以及前缀匹配（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE ‘abc%'</pre><div class=”contentsignin”></div></div>）时表现优秀。它的数据是经过排序的，并且以树状结构存储，这使得数据库可以快速定位到所需的数据范围。可以说，大部分我们日常使用的场景，BTREE都能很好地覆盖。</p> <p>然而，还有一些其他类型，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>HASH</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FULLTEXT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SPATIAL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>HASH</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>索引在MySQL中比较特殊，它主要用于Memory存储引擎表，或者作为InnoDB内部的“自适应哈希索引”（Adaptive Hash Index），而不是我们手动创建的常规索引。如果一个列上能用哈希索引，那么等值查询的性能会非常快，因为它直接通过哈希值定位数据，理论上是O(1)的复杂度。但它的缺点也很明显：不支持范围查询，不支持排序，也不支持部分匹配。你无法在哈希索引上执行<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col > 10</pre><div class=”contentsignin”></div></div>或者<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ORDER BY col</pre><div class=”contentsignin”></div></div>。所以，除非你明确知道自己的查询模式完全是等值查找，否则通常不会主动选择哈希索引。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FULLTEXT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>索引，顾名思义，是为全文搜索设计的。如果你需要在文本字段（如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>TEXT</pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>VARCHAR</pre><div class=”contentsignin”></div></div>）中进行关键词搜索，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>MATCH (column) AGAINST (‘keyword’)</pre><div class=”contentsignin”></div></div>，那么<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>FULLTEXT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>索引是你的不二之选。它有自己一套复杂的算法来处理分词、停用词、相关性排序等，和普通索引完全不同。</p> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SPATIAL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>索引则用于地理空间数据类型，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>POINT</pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LINESTRING</pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>POLYGON</pre><div class=”contentsignin”></div></div>等。如果你在处理地图、位置信息等场景，需要进行空间查询（例如查找某个区域内的所有点），那就需要用到<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SPATIAL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>索引。</p> <p>了解这些索引类型，能帮助我们更好地理解查询优化器为什么会选择某个索引，或者为什么某个查询没有走我们预期的索引。比如，你对一个字段做了范围查询，但如果那个字段上只有哈希索引，那么这个索引就不会被使用，查询可能会退化为全表扫描。或者，你尝试在BTREE索引上做<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE ‘%keyword%'</pre><div class=”contentsignin”></div></div>这样的模糊匹配，你会发现它也无法利用索引，因为BTREE索引是按从左到右的顺序<a >排列</a>的，开头的通配符让它无从下手。所以，知道索引类型，就是知道索引的“脾气”和“擅长”，这样我们才能更好地“使唤”它。</p> <h3>如何判断一个索引是否被有效使用？有哪些常见误区？</h3> <p>判断一个索引是否被有效使用，最核心、最权威的工具就是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>语句。当你对一个<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SELECT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>查询感到疑惑，或者它运行得很慢时，第一步就应该是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>它。</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>EXPLAIN SELECT column1, column2 FROM your_table WHERE condition_column = ‘value’ ORDER BY another_column;</pre><div class=”contentsignin”></div></div><p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的输出结果有很多列，其中几个关键的能告诉你索引的使用情况：</p> <ul> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>type</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：这是最重要的列之一，它显示了MySQL如何扫描表来查找所需数据。理想情况是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>const</pre><div class=”contentsignin”></div></div>（常量）、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>eq_ref</pre><div class=”contentsignin”></div></div>（唯一索引查找）、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ref</pre><div class=”contentsignin”></div></div>（非唯一索引查找）或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>range</pre><div class=”contentsignin”></div></div>（范围查找）。最差的是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ALL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，这意味着全表扫描。如果你的查询返回<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ALL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，但你期望它走索引，那肯定有问题。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>key</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：显示MySQL实际使用的索引名称。如果这里是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，表示没有使用索引。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>key_len</pre><div class=”contentsignin”></div></div>：显示MySQL使用的索引的长度。对于复合索引，这可以帮助你判断索引的哪些部分被使用了。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>rows</pre><div class=”contentsignin”></div></div>：MySQL估计需要检查的行数。这个数字越小越好。</li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：提供了额外的信息，比如“using index”（表示使用了覆盖索引，查询所需的所有列都在索引中，无需回表）、“Using where”（表示在存储引擎层进行了筛选）、“Using filesort”（表示需要对结果进行排序，通常是性能瓶颈）、“Using temporary”（表示需要创建临时表，也通常是性能瓶颈）。</li> </ul> <p>如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>显示<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>key</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>为<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>NULL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，或者<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>type</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>是<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ALL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，或者<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>中出现了<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using filesort</pre><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using temporary</pre><div class=”contentsignin”></div></div>，那通常意味着索引没有被有效利用，或者利用得不够充分。</p> <p>关于索引使用的常见误区，我见过不少，也踩过一些坑：</p> <ul> <li> <strong>函数或表达式在索引列上：</strong> 这是一个经典错误。如果你在WHERE子句中对索引列使用了函数，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE date(created_at) = ‘2023-01-01′</pre><div class=”contentsignin”></div></div>，或者进行了计算<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE price * 1.1 > 100</pre><div class=”contentsignin”></div></div>，那么这个索引通常会失效。因为索引存储的是原始值，经过函数或计算后的值无法直接通过索引查找。正确的做法是把函数或计算放到等号的另一边，比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE created_at BETWEEN ‘2023-01-01 00:00:00’ AND ‘2023-01-01 23:59:59′</pre><div class=”contentsignin”></div></div>。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div>语句以通配符开头：</strong> <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE name LIKE ‘%john%'</pre><div class=”contentsignin”></div></div>这样的查询，因为通配符在字符串开头，索引无法进行前缀匹配，所以会失效。只有<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE name LIKE ‘john%'</pre><div class=”contentsignin”></div></div>才能有效利用索引。</li> <li> <strong><a >隐式类型转换</a>：</strong> 如果你把一个字符串和数字类型的列进行比较，MySQL可能会进行隐式类型转换，这可能导致索引失效。例如，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE int_column = ‘123’</pre><div class=”contentsignin”></div></div>，如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>int_column</pre><div class=”contentsignin”></div></div>是整数类型，MySQL可能会将其转换为字符串再比较，或者反过来，导致索引无法使用。保持数据类型一致性很重要。</li> <li> <strong>复合索引的“最左前缀原则”：</strong> 如果你有一个复合索引<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2, col3)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，那么这个索引可以用于<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>col1</pre><div class=”contentsignin”></div></div>，或者<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2)</pre><div class=”contentsignin”></div></div>，或者<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2, col3)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的查询。但如果你只查询<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>col2</pre><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>col3</pre><div class=”contentsignin”></div></div>，或者查询<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col2, col3)</pre><div class=”contentsignin”></div></div>，那么这个索引就无法完全利用，甚至根本不被使用。这就是最左前缀原则。理解这一点对于设计复合索引至关重要。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件：</strong> 在某些情况下，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件可能会导致索引失效，因为它可能需要扫描多个索引或全表扫描。不过，Mysql优化器现在也越来越智能，对于一些简单的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件，如果每个条件都能使用独立的索引，它可能会使用“索引合并”（Index Merge）策略。但复杂<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OR</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>还是需要警惕。</li> <li> <strong>索引选择性（Cardinality）低：</strong> 如果一个列的唯一值很少（比如性别列），那么即使你为它创建了索引，优化器也可能觉得全表扫描更快，从而放弃使用这个索引。<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SHOW INDEX</pre><div class=”contentsignin”></div></div>结果中的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Cardinality</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>列可以帮助你判断。</li> </ul> <h3>优化索引时，除了类型和使用情况，还需要考虑哪些因素？</h3> <p>优化索引可不是个简单活，它像是一门艺术，需要经验、直觉，还得结合实际业务场景。除了前面提到的索引类型和通过<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>判断使用情况，还有几个关键点是优化时必须考虑的：</p> <ul> <li> <strong>索引的基数（Cardinality）和选择性：</strong> 这是个非常重要的指标。基数是指一个列中不重复值的数量。选择性则是基数与总行数的比率。一个高基数的列（比如用户ID、<a >邮箱</a>地址）是创建索引的理想选择，因为索引能很快地定位到少数几行。而低基数的列（比如性别、状态码），即使建了索引，优化器也可能觉得全表扫描更划算。<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SHOW INDEX FROM table_name</pre><div class=”contentsignin”></div></div>结果中的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Cardinality</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>列就反映了这一点。如果一个索引的基数很低，它很可能无法带来预期的性能提升。</li> <li> <strong>索引的维护成本：</strong> 索引并不是越多越好。每一个索引都会占用磁盘空间，并且在数据进行插入（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>INSERT</pre><div class=”contentsignin”></div></div>）、更新（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>UPDATE</pre><div class=”contentsignin”></div></div>）和删除（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>delete</pre><div class=”contentsignin”></div></div>）操作时，数据库也需要同时维护这些索引。索引越多，写操作的开销就越大，性能可能反而下降。所以，在创建新索引时，要权衡它带来的读性能提升是否值得其写入成本。那些几乎不被查询但又有很多更新的表，可能就不适合建太多索引。</li> <li> <strong>覆盖索引（Covering Index）：</strong> 这是一个非常强大的优化手段。如果一个查询所需的所有列（包括<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SELECT</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>列表中的列、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div>子句中的列、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ORDER BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>GROUP BY</pre><div class=”contentsignin”></div></div>中的列）都能在索引中找到，那么数据库就不需要再去回表（访问实际的数据行）获取数据了。这会大大减少I/O操作，显著提升查询速度。<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>结果中的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>列显示<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using index</pre><div class=”contentsignin”></div></div>就表示使用了覆盖索引。设计覆盖索引通常意味着创建复合索引，将查询中涉及的所有列都包含进去。</li> <li> <strong>复合索引的列顺序：</strong> 对于复合索引（多列索引），列的顺序至关重要，因为它直接影响到“最左前缀原则”的有效性。通常，我们建议将选择性最高的列放在复合索引的最前面，这样可以最快地缩小搜索范围。然后，再根据查询模式，将那些经常用于过滤、排序或分组的列按顺序添加到后面。这是一个需要仔细规划的地方，没有放之四海而皆准的规则，得根据具体的查询模式来定。</li> <li> <strong>聚簇索引与二级索引（InnoDB特有）：</strong> 在InnoDB存储引擎中，主键就是聚簇索引。这意味着表的实际数据行是按照主键的顺序物理存储的。所有非主键索引（二级索引）的叶子节点存储的不是行指针，而是对应行的主键值。这意味着通过二级索引查询时，首先找到主键值，然后再通过主键值去聚簇索引中找到完整的行数据，这个过程称为“回表”。如果查询只需要二级索引中的列（即覆盖索引），就不会发生回表。理解这一点对优化非常重要，因为它解释了为什么主键查询通常比二级索引查询更快，以及为什么覆盖索引如此高效。</li> <li> <strong>识别和删除冗余或未使用的索引：</strong> 随着时间的推移，业务需求可能会变化，一些索引可能变得不再需要，或者被其他更优的索引所覆盖。这些冗余索引不仅占用空间，还增加了写操作的开销。定期审查和删除未使用的索引是一个很好的习惯。可以通过查询<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>information_schema.SCHEMA_STATISTICS</pre><div class=”contentsignin”></div></div>或通过性能监控工具来识别那些几乎没有被使用的索引。</li> <li> <strong>外键上的索引：</strong> 如果你的表之间有外键关系，确保外键列上有索引是至关重要的。这不仅能加速<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>JOIN</pre><div class=”contentsignin”></div></div>操作，还能提高参照完整性检查的效率，避免在删除或更新父表记录时出现性能问题。MySQL在外键创建时通常会自动创建索引，但检查一下总没错。</li> </ul> <p>总的来说，索引优化是一个持续的过程，它需要你对数据库的工作原理有深入的理解，并结合实际的业务场景和查询模式进行调整。没有银弹，只有不断地测试、分析和迭代。</p>

文章版权归作者所有，未经允许请勿转载。

THE END