MySQL高级篇之索引的使用

索引使用

1. 单列索引和联合索引

• 单列索引：即一个索引只包含单个列

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

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

• 注意：多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

2. 最左匹配原则（重要）

• 最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。如果索引关联了多列（联合索引），则查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）。

• 案例：

  -- 建立索引
  CREATE INDEX idx_user_pro_age_status ON tb_user(profession,age,status);
  1
  2

  1. 在查询语句中，要想使用联合索引，必须满足最左前缀原则，即索引的最左列必须存在（该案例中 profession 必须存在）

     

  2. 在满足索引最左列存在的情况下，可以缺少末尾的索引列

     
     

  3. 若跳过中间列，则索引将部分失效，即后面的字段索引会失效

     

     可以看到，虽然使用了联合索引，但是 key_len 为47，与2中的第二种情况一样，说明 status 列的索引失效了。

  4. 若查询条件不存在最左列

     

• 在满足最左前缀原则，且不跳过索引中的列时，查询条件顺序打乱不会影响执行顺序。

  最主要是因为 MySQL 中有查询优化器 explain，所以 sql 语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划。

  -- 如在索引 idx_user_pro_age_status 中，索引顺序为 profession ==> age ==> status
  CREATE INDEX idx_user_pro_age_status ON tb_user(profession,age,status);
  
  -- 则下面三条语句的执行顺序一样
  SELECT * FROM tb_user WHERE profession='软件工程' AND age=31 AND status='0';
  SELECT * FROM tb_user WHERE age=31 AND profession='软件工程' AND status='0';
  SELECT * FROM tb_user WHERE status='0' AND profession='软件工程' AND age=31;
  1
  2
  3
  4
  5
  6
  7

• 联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。（可以用 >= 或者 <= 来规避索引失效问题）

  

  key_len 长度为49，说明 status 索引列失效，验证了范围查询右侧的索引列失效。可以使用 >= 来规避

  

3. 索引失效情况

1. 在索引列上进行运算操作，索引将失效。如：

   

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

   

3. 模糊查询

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

     

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

     

   • 如果头部和尾部都采用模糊匹配，索引也会失效。（只要使用了头部模糊匹配，索引就会失效）

4. 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。

   

   

5. 如果 MySQL 评估使用索引比全表更慢，则不使用索引。如查询的数据大部分都满足查询条件，此时全表查询的效率会索引高。

   

4. SQL 提示

• 如果一个查询语句中，有多个索引可以使用（如同时存在单列索引和联合索引），默认情况 MySQL 会自己判断使用哪个索引。SQL 提示是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

  -- 使用索引（只是建议，具体会不会使用还要靠 MySQL 自己判断）
  EXPLAIN SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession="软件工程";
  -- 不使用哪个索引
  EXPLAIN SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) WHERE profession="软件工程";
  -- 必须使用哪个索引
  EXPLAIN SELECT * FROM tb_user FORCE INDEX(idx_user_pro) WHERE profession="软件工程";
  1
  2
  3
  4
  5
  6

• USE 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，FORCE就是无论如何都强制使用该索引。

5. 覆盖索引（重要）

• 覆盖索引又可以称为索引覆盖，即 select 的数据列只从索引中就能得到，不必读取数据行，即只需扫描索引就可以得到查询结果。

• 好处：

  • 索引的大小通常远小于数据行大小，所以如果只需要读取索引就能获取需要的数据，那么 MySQL 会极大的减少数据访问量。
  • 索引是按照值的顺序存储的。
  • InnoDB 的二级索引叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，那么可以避免对主键索引的二次查询（**即避免回表查询。尽量使用覆盖索引，减少 select ***）。

• 并不是所有类型的索引都是称为覆盖索引。覆盖索引必须要存储列的值， Hash 索引、空间索引和全文索引这三类不存储列值的索引都不能作为覆盖索引，所以 MySQL 中只能使用 B+Tree 索引可以做覆盖索引。

  

  MySQL 的查询优化器 explain 会在执行查询前判断，是否有一个索引可以覆盖所有的查询列。

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

  注意：覆盖索引可以提升执行的效率和节省时间，但不适用于需要查询多个列数据的场景；在查询列较少的情况下，使用覆盖索引可以明显提升。

• 面试题

  一张表，有四个字段（id, username, password, status），由于数据量大，如何进行最优的SQL语句优化？

  SELECT id,username,password FROM tb_user WHERE username = 'itcast';
  1

  给 username 和 password 字段建立联合索引，则不需要回表查询，直接覆盖索引。

6. 前缀索引

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

  CREATE INDEX idx_xxxx ON table_name(columnn(n));
  1

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

• 求选择性公式：

SELECT COUNT(DISTINCT email) / COUNT(*) FROM tb_user;
SELECT COUNT(DISTINCT SUBSTRING(email, 1, 5)) / COUNT(*) FROM tb_user;
1
2

• show index 里面的 sub_part 可以看到接取的长度