mysql中添加索引的3种方法及使用注意事项详解
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、MySQL 创建索引的三种方法</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.1 在新建表时创建索引</a></li><li><a href="#_lab2_0_1">1.2 在已存在表上通过CREATE INDEX添加索引</a></li><li><a href="#_lab2_0_2">1.3 通过ALTER TABLE修改表结构添加索引</a></li></ul><li><a href="#_label1">二、MySQL 索引类型与适用场景</a></li><ul class="second_class_ul"></ul><li><a href="#_label2">三、索引使用注意事项与限制</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_3">有效使用索引的场景</a></li><li><a href="#_lab2_2_4">索引失效的常见情况</a></li></ul><li><a href="#_label3">四、索引的优缺点</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_5">优点</a></li><li><a href="#_lab2_3_6">缺点</a></li></ul><li><a href="#_label4">五、InnoDB 与 MyISAM 索引差异</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">六、索引优化工具：EXPLAIN</a></li><ul class="second_class_ul"></ul><li><a href="#_label6">七、总结</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_7">正确结论：</a></li></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、MySQL 创建索引的三种方法</h2><p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.1 在新建表时创建索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- ① 普通索引
CREATE TABLE t_dept (
no INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NULL,
sex VARCHAR(2) NULL,
info VARCHAR(20) NULL,
INDEX index_no(no)
);
-- ② 唯一索引
CREATE TABLE t_dept (
no INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NULL,
sex VARCHAR(2) NULL,
info VARCHAR(20) NULL,
UNIQUE INDEX index_name(name)
);
-- ③ 全文索引（全文索引字段通常为 TEXT 或 VARCHAR，且 InnoDB 在 MySQL 5.6+ 才支持全文索引，早期版本仅 MyISAM 支持。）
CREATE TABLE t_dept (
no INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NULL,
sex VARCHAR(2) NULL,
info TEXT NULL,-- 全文索引字段建议用 TEXT
FULLTEXT INDEX index_info(info)
);
-- ④ 多列（复合）索引
CREATE TABLE t_dept (
no INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NULL,
sex VARCHAR(2) NULL,
info VARCHAR(20) NULL,
INDEX idx_no_name(no, name)
);
</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>1.2 在已存在表上通过CREATE INDEX添加索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 普通索引
CREATE INDEX idx_name ON t_dept(name);
-- 唯一索引
CREATE UNIQUE INDEX idx_name ON t_dept(name);
-- 全文索引
CREATE FULLTEXT INDEX idx_info ON t_dept(info);
-- 多列索引
CREATE INDEX idx_name_no ON t_dept(name, no);
</pre></div>
<blockquote><p>⚠️ 此方式不能用于主键或外键约束，仅用于普通/唯一/全文索引。</p></blockquote>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>1.3 通过ALTER TABLE修改表结构添加索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 普通索引
ALTER TABLE t_dept ADD INDEX idx_name(name);
-- 唯一索引
ALTER TABLE t_dept ADD UNIQUE INDEX idx_name(name);
-- 全文索引
ALTER TABLE t_dept ADD FULLTEXT INDEX idx_info(info);
-- 多列索引
ALTER TABLE t_dept ADD INDEX idx_name_no(name, no);</pre></div>
<blockquote><p>✅ 推荐在生产环境中使用 <code>ALTER TABLE</code>，语义更清晰，且支持更多选项（如指定索引类型）。</p></blockquote>
<p class="maodian"><a name="_label1"></a></p><h2>二、MySQL 索引类型与适用场景</h2>
<table><thead><tr><th>索引类型</th><th>关键字</th><th>特点</th><th>适用场景</th></tr></thead><tbody><tr><td><strong>普通索引</strong></td><td><code>INDEX</code> / <code>KEY</code></td><td>加速查询，允许重复值</td><td>高频 WHERE、ORDER BY 字段</td></tr><tr><td><strong>唯一索引</strong></td><td><code>UNIQUE</code></td><td>值唯一，自动校验重复</td><td>身份证号、邮箱、订单号等需唯一字段</td></tr><tr><td><strong>主键索引</strong></td><td><code>PRIMARY KEY</code></td><td>特殊的唯一索引，一张表仅一个</td><td>表的主标识（自增 ID 等）</td></tr><tr><td><strong>全文索引</strong></td><td><code>FULLTEXT</code></td><td>支持关键词全文检索</td><td>文章内容、商品描述等大文本字段</td></tr><tr><td><strong>复合索引</strong></td><td><code>INDEX(col1, col2, ...)</code></td><td>多字段组合索引</td><td>多条件联合查询（注意最左前缀原则）</td></tr></tbody></table>
<p class="maodian"><a name="_label2"></a></p><h2>三、索引使用注意事项与限制</h2>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>有效使用索引的场景</h3>
<ul><li><code>WHERE column = ?</code></li><li><code>WHERE column LIKE 'abc%'</code>（前缀匹配）</li><li><code>ORDER BY column</code>（无表达式）</li><li>复合索引遵循 <strong>最左前缀原则</strong>：<code>INDEX(A,B,C)</code> 可用于 <code>(A)</code>、<code>(A,B)</code>、<code>(A,B,C)</code> 查询</li></ul>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>索引失效的常见情况</h3>
<table><thead><tr><th>场景</th><th>示例</th><th>是否走索引</th></tr></thead><tbody><tr><td>使用函数</td><td><code>WHERE DAY(create_time) = '2024-01-01'</code></td><td>❌</td></tr><tr><td>不等号查询</td><td><code>WHERE status != 1</code></td><td>❌（部分情况可能走）</td></tr><tr><td>通配符开头</td><td><code>WHERE name LIKE '%john'</code></td><td>❌</td></tr><tr><td>类型隐式转换</td><td><code>WHERE user_id = '123'</code>（user_id 为 INT）</td><td>❌</td></tr><tr><td>OR 条件未全覆盖索引</td><td><code>WHERE name = 'a' OR age = 20</code>（仅 name 有索引）</td><td>❌</td></tr></tbody></table>
<p class="maodian"><a name="_label3"></a></p><h2>四、索引的优缺点</h2>
<p class="maodian"><a name="_lab2_3_5"></a></p><h3>优点</h3>
<ul><li>显著提升 <strong>SELECT</strong> 查询速度</li><li>加速 <strong>JOIN</strong>、<strong>ORDER BY</strong>、<strong>GROUP BY</strong> 操作</li><li>唯一索引保障数据 <strong>完整性</strong></li></ul>
<p class="maodian"><a name="_lab2_3_6"></a></p><h3>缺点</h3>
<ul><li><strong>降低写性能</strong>：INSERT/UPDATE/DELETE 需同步更新索引</li><li><strong>占用磁盘空间</strong>：索引文件可能接近甚至超过数据本身</li><li><strong>维护成本高</strong>：过多索引增加优化器负担</li></ul>
<blockquote><p>📌 <strong>建议</strong>：</p>
<ul><li>单表索引数量 ≤ 5~8 个（MySQL 限制最多 16 个）</li><li>仅为 <strong>高频查询字段</strong> 建索引</li><li>避免为低区分度字段建索引（如性别、状态 0/1）</li></ul></blockquote>
<p class="maodian"><a name="_label4"></a></p><h2>五、InnoDB 与 MyISAM 索引差异</h2>
<table><thead><tr><th>特性</th><th>InnoDB</th><th>MyISAM</th></tr></thead><tbody><tr><td>主键索引</td><td>聚簇索引（数据按主键物理存储）</td><td>非聚簇索引</td></tr><tr><td>全文索引</td><td>MySQL 5.6+ 支持</td><td>原生支持</td></tr><tr><td>行级锁</td><td>基于索引实现</td><td>不支持行锁</td></tr><tr><td>崩溃恢复</td><td>支持事务回滚</td><td>无事务</td></tr></tbody></table>
<blockquote><p>🔥 <strong>关键点</strong>：InnoDB 的行锁是通过索引实现的！<strong>无索引的 UPDATE 会锁全表</strong>。</p></blockquote>
<p class="maodian"><a name="_label5"></a></p><h2>六、索引优化工具：EXPLAIN</h2>
<p>使用 <code>EXPLAIN</code> 分析 SQL 执行计划：</p>
<div class="jb51code"><pre class="brush:sql;">EXPLAIN SELECT * FROM t_dept WHERE name = 'IT'; </pre></div>
<p>重点关注字段：</p>
<ul><li><code>type</code>：访问类型（<code>const</code> > <code>ref</code> > <code>range</code> > <code>index</code> > <code>ALL</code>）</li><li><code>key</code>：实际使用的索引</li><li><code>rows</code>：扫描行数（越小越好）</li><li><code>Extra</code>：是否出现 <code>Using filesort</code> / <code>Using temporary</code></li></ul>
<p class="maodian"><a name="_label6"></a></p><h2>七、总结</h2>
<blockquote><p>索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。</p>
<p>注：</p>
<p>索引不是万能的！索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许 多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进 行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE选项的作用将非 常明显。</p>
<p>另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内 容，为它建立索引就没有太大的实际效果。</p>
<p>从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但MySQL把同一个数据表里的索引总数限制为16个。</p>
<p>1. InnoDB数据表的索引</p>
<p>与MyISAM数据表相比，索引对InnoDB数据的重要性要大得多。在InnoDB数据表上，索引对InnoDB数据表的重要性要在得多。在 InnoDB数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的基础。”数据行级锁定”的意思是指在事务操作的执行过程中锁定正 在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到(但不限于)SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE命令以及INSERT、UPDATE和DELETE命令。</p>
<p>出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合 适的索引可供锁定的时候才能发挥效力。</p>
<p>2. 限制</p>
<p>如果WEHERE子句的查询条件里有不等号(WHERE coloum != …)，MySQL将无法使用索引。</p>
<p>类似地，如果WHERE子句的查询条件里使用了函数(WHERE DAY(column) = …)，MySQL也将无法使用索引。</p>
<p>在JOIN操作中(需要从多个数据表提取数据时)，MySQL只有在主键和外键的数据类型相同时才能使用索引。</p>
<p>如果WHERE子句的查询条件里使用比较操作符LIKE和REGEXP，MySQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说， 如果查询条件是LIKE ‘abc%’，MySQL将使用索引；如果查询条件是LIKE ‘%abc’，MySQL将不使用索引。</p>
<p>在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。(虽然如此，在涉及多个数据表查询里，即使有索引可用，那些索引在加快 ORDER BY方面也没什么作用)</p>
<p>如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含的净是些诸如”0/1″或”Y/N”等值，就没 有必要为它创建一个索引。</p>
<p></p>
<p>普通索引、唯一索引和主索引</p>
<p>1. 普通索引</p>
<p>普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。</p>
<p>2. 唯一索引</p>
<p>普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个”员工个人资料”数据表里可能出现两次或更多次。</p>
<p>如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简 化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在 某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯 一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。</p>
<p>3. 主索引</p>
<p>在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY而不是UNIQUE。</p>
<p>4. 外键索引</p>
<p>如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。</p>
<p>5. 复合索引</p>
<p>索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使 用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。</p>
<p>6. 索引的长度</p>
<p>在为CHAR和VARCHAR类型的数据列定义索引时，可以把索引的长度限制为一个给定的字符个数(这个数字必须小于这个字段所允许的最大字符个数)。这 么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置 为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。</p>
<p>在为BLOB和TEXT类型的数据列创建索引时，必须对索引的长度做出限制；MySQL所允许的最大索引长度是255个字符。</p>
<p></p>
<p>全文索引</p>
<p>文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成 的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。</p>
<p>这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数 据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：</p>
<p>ALTER TABLE tablename ADD FULLTEXT(column1, column2)</p>
<p>有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：</p>
<p>SELECT * FROM tablename</p>
<p>WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)</p>
<p>上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。</p></blockquote>
<p><strong>注：InnoDB 数据表是支持全文索引（FULLTEXT INDEX）的</strong>，但 <strong>有版本限制</strong>。</p>
<p class="maodian"><a name="_lab2_6_7"></a></p><h3>正确结论：</h3>
<table><thead><tr><th>MySQL 版本</th><th>InnoDB 是否支持 FULLTEXT 索引？</th></tr></thead><tbody><tr><td><strong>MySQL 5.6 之前</strong>（如 5.5、5.1）</td><td>❌ <strong>不支持</strong>，仅 MyISAM 支持</td></tr><tr><td><strong>MySQL 5.6 及以后</strong>（包括 5.7、8.0、8.4 等）</td><td>✅ <strong>完全支持</strong></td></tr></tbody></table>
頁:
[1]