当前位置：首页 > news >正文

【MySQL | 第八篇】索引的使用

news 2026/4/30 9:31:18

一、索引的使用规则

1.最左前缀法则

2.范围查询

3.索引的失效情况

3.1索引列运算

3.2字符串不加引号

3.3模糊查询

3.4or连接的条件

3.5数据分布影响

4.SQL提示

5.覆盖索引⭐⭐⭐⭐⭐

6.前缀索引

7.单列索引与联合索引

二、索引的涉及原则

一、索引的使用规则

1.最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

explain select * from tb user where profession = '软件工程' and age = 31 and status = '0'; explain select * from tb_user where profession= '软件工程' and age = 31; explain select * from tb_user where profession = '软件工程'; explain select * from tb_user where age = 31 and status = '0'; explain select * from tb_user where status = '0';

根据最左前缀法则，从索引的最左列profession开始，后面紧接着的是age、status，如果将profession字段去掉，直接按age、status或只根据status查询，那么该索引就会失效。

2.范围查询

联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程' and age > 30 and status='0'; explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

第一个sql语句出现了>范围查询语句，因此尽管满足最左前缀法则，依然会使右侧的列索引失效。因此在使用复合索引时，如果业务允许的情况下，尽量使用>=来进行判断。

3.索引的失效情况

3.1索引列运算

不要在索引列上进行运算操作，索引将失效。

3.2字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

3.3模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

3.4or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会使用。

也就是如果要使用索引，就要求使用or连接的前面以及后面涉及到的列必须都有索引。

3.5数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

4.SQL提示

SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index():

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index():

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index():

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

5.覆盖索引⭐⭐⭐⭐⭐

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

using index condition：查找使用了索引，但是name字段需要回表查询数据。
using where；using indx：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。

6.前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。

此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：
CREATE INDEX idx_xxxx ON 表名(column(n));
提取column字段的前n个字符作为前缀索引。
前缀长度：
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email) / count(*) from tb_user; select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

7.单列索引与联合索引

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更好，会选择这个效率更高的索引来完成本次查询，而不会同时使用两个，因此此次查询还是涉及了回表查询。

创建联合索引时，索引字段的顺序是有要求的，如果我们要查询数据并且要求用到这个索引时，必须满足最左前缀原则。

二、索引的涉及原则

1.针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。