「mysql进阶」索引的使用规则、设计原则

frozenpenguin
41 阅读5分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第27天，点击查看活动详情

「mysql进阶」索引的使用规则、设计原则

一、索引的使用规则

二、索引设计原则

一、索引的使用规则

1. 最左前缀法则

  • 如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左侧开始，并且不能跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）。
  • 最左列的索引存在即可，与所放的位置无关。

2. 范围查询

  • 联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。

解决办法是 替换为>=或者<=来规避索引失效问题。

3. 索引列运算

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

如：explain select * from tb_user where substring(phone, 10, 2) = '15';

4. 字符串不加引号

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

如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号

5. 模糊查询

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

如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。

6. or连接的条件

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

7. 数据分布影响

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

8. SQL提示

  • SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
例如，使用索引
explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
#use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改
​
指定不使用哪个索引
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";
​
指定必须使用哪个索引
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";
#force是无论如何都强制使用该索引

9. 覆盖索引

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

举例说明

image-20220502233208233

explain 中 extra 字段含义：

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

结合上图实际情况

  • 由于第一条SQL要查找的字段“name”没有使用索引，所以该字段值需要回表查询得到
  • 由于第二条SQL要查找的字段都是使用了索引，所以其值在二次索引直接得到

覆盖索引&回表查询过程举例说明

  • id是主键，是一个聚集索引。
  • name字段建立了普通索引，是一个二级索引（辅助索引）

执行SQL : select * from tb_user where id = 2;

image-20220502233954609

  • 根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

执行SQL：selet id,name from tb_user where name = ‘Arm’;

image-20220502234025641

  • 虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高

执行SQL：selet id,name,gender from tb_user where name = ‘Arm’;

image-20220502235800593

  • 由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

面试题：一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案？

select id, username, password from tb_user where username='itcast';
​
解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引
123

10. 前缀索引

  • 当字段类型为字符串（varchar，text等）时，有时候需要索引保存很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

解决办法

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

语法

create index idx_xxxx on table_name(columnn(n));
1

示例:

为tb_user表的email字段，建立长度为5的前缀索引

create index idx_email_5 on tb_user(email(5));
1

image-20220502235657216

前缀长度 如何选择？

  • 可以根据索引的选择性来决定，索引选择性越高则查询效率越高。@选择性是指 不重复的索引值（基数）和 数据表的记录总数的比值
  • 比如 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

求选择性公式

select count(distinct email) / count(*) from tb_user; #@distinct 先除重再统计
​
​
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
​
12345
  • show index 里面的sub_part可以看到接取的长度

前缀索引 查询流程图

image-20220502235800593

  • 注意在这次中，不是第一次回表查询就返回查询到的数据，而是继续比较还未比较完的字段值——对比下一个结点
  • 如果相同，那么继续查找
  • 如果不相同，那么返回查询到的数据。这是因为这意味着不可能存在其他前缀索引相同的行数据

11. 单列索引与联合索引

是什么

  • 单列索引：即一个索引只包含单个列
  • 联合索引：即一个索引包含了多个列

在业务场景中

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

单列索引情况（此时phone、name都建立了索引，但不是联合索引）
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';
结果是只会用到phone索引字段
123

注意事项

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。有时候联合索引存在，但是系统仍然自动选择单列索引查询，这时就需要自己指定索引类型了。

🕜联合索引查询 过程图

image-20220503001948568

二、索引设计原则

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