MySQL-进阶篇 ( 索引二 — 使用：法则 + 各使用场景情况 )

MySQL-进阶篇 ( 索引二 — 使用 )

目录

• MySQL-进阶篇 ( 索引二 — 使用 )
  • 索引
    • 索引使用
      • 引出：验证索引效率的提升
      • 最左前缀法则
      • 索引失效
        • 范围查询
        • 索引列运算
        • 字符串不加引号
        • 模糊查询
        • or 连接的条件
        • 数据分布影响
      • SQL 提示
      • 覆盖索引
        • 面试思考题
      • 前缀索引
      • 单列索引与联合索引
    • 索引设计原则

索引

索引使用

引出：验证索引效率的提升

• 在未建立索引之前，执行 SQL 语句 ( WHERE 后用上非索引的条件 )，查看 SQL 的耗时

  SELECT * FROM 表名 WHERE 条件;

  • 在条件之后可以加上 ' \G ' 以防数据过多乱序
  • 若是条件非主键无索引时，就需要全表扫描，可能会使得简单语句却耗时很长

• 所以要针对字段创建索引

  create index idx_表名_字段名 on 表名(字段名); -- 创建数据结构的过程

  show index from 表名; -- 看得已有索引

  • 然后再执行刚才的 SQL 语句，就会发现耗费的执行时间少了很多

最左前缀法则

• 如果索引了多列 / 多个字段 ( 即为：联合索引 )，要遵守最左前缀法则。
• 最左前缀法则要求：
  • 查询从索引的最左列开始，并且不跳过索引中的列
  • 如果跳跃某一列，索引将部分失效 ( 后面的字段索引失效 )
• 联合索引显示样式：
  • show index from 表名 查看，如果 Key_name 有多个重复，就表示此为联合索引，后面的 Column_name 就是索引的多个字段，每个字段的 Seg_in_index 按顺序 1 2 3 ... 排序，此处最左前缀法则就体现在：
  • 要想用到这个联合索引，就要保证最左边 ( 即 Seg_in_index 为 1 的那个 ) 的字段值必须存在，且中间不能跳过 Seg_in_index 为 2 ... 的字段，否则后面的为 3 ... 的索引就会失效
• 法则实例展示：

  • 假设上述的多个字段分别为 1 — profession、2 — age、3 — status，则 SQL 查询时：explain select * from tb_user where profession = '软件工程' and age = 20 and status = '0'; 发现 key 有显示索引，则表示索引都用上了，且符合最左前缀法则

  • 再查询 explain select * from tb_user where profession = '软件工程' and age = 20; 会发现也符合法则，也有用到联合索引，根据和上一个执行计划中的 key_len 差，就可以得到 status 的索引长度

    • 从而也能找出 age 字段的索引长度

  • 若是输入 explain select * from tb_user where and age = 20 and status = '0'; 不符合最左前缀法则 ( 最左边的没出现 )，看计划也会发现 key 为空 NULL，也就表示了联合索引没生效，是 type 为 ALL 的全表扫描

  • 输入 explain select * from tb_user where profession = '软件工程' and status = '0'; 虽符合法则，但跳跃了中间一列，则执行计划中的 key_len 仅为 profession 的索引长度，即 status 并未走索引 ( 索引部分失效 )

  • 若是输入explain select * from tb_user where age = 20 and status = '0' and profession = '软件工程'; 查询走了联合索引，且根据索引长度可知，三个字段都有用上，即最左前缀法则要求的是必须存在，而与 SQL 中的顺序无碍

索引失效

范围查询

• 最左前缀法则下，在联合索引中，如果出现范围查询 (>, <)，范围查询右侧的列索引失效
• 实例演示：
  • explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0'; 由 key_len 可知 status 没有用到部分失效了，即因为 age 用的是范围查询，所以右侧的 status 索引失效了
  • explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0'; 由 key_len 可知此时三个字段都用到了联合索引
• 则解决方法为，在允许的情况下，范围的符号尽量都为 '>=' 或 '<=' 的运算符

索引列运算

• 不要在索引列上进行运算操作，否则索引将失效
• 实例演示：
  • 已有 phone 字段的索引的前提下，执行语句 explain select * from tb_user where phone = '12563417895'; 可得 key 索引有效
  • 若是想要获取电话是 '95' 结尾的信息，可以选择借用截取函数，执行语句 explain select * from tb_user where substring(phone, 10, 2) = '95'; 查看执行计划可知，key 为空 NULL，type 为 ALL 全表查询，表示由于使用了函数运算致使索引已经失效

字符串不加引号

• 字符串类型字段使用时，不加引号，索引将失效
• 实例演示：
  • 已有 phone 字段的索引的前提下，执行语句 select * from tb_user where phone = 12563417895;，虽然 phone 字段为 varchar，但是仍能够查询到
    • 再执行语句 explain select * from tb_user where phone = 12563417895;，就会发现 key 为 NULL 索引没有生效 ( 虽然 possible_keys 表示可能会用到的非空，但实际上的仍为 NULL 空 )，仍是 type 为 ALL 的全表查询
  • 就算是联合索引中执行语句 explain select * from tb_user where profession = '软件工程' and age = 20 and status = 0; status 字段为字符串类型，执行计划根据 key_len 值可得，status 并没有生效

模糊查询

• 如果仅仅是尾部模糊查询，索引不会生效。如果是头部模糊查询，索引失效
• 实例演示：
  • 执行语句 explain select * from tb_user where profession like '软件%'; 可见索引有效 ( 索引可以是使用的上面的联合索引，看自行的设置 )
  • 但是执行语句 explain select * from tb_user where profession like '%工程'; 就会发现索引失效了，key 为 NULL
  • 就算是前后都加上了索引的语句 explain select * from tb_user where profession like '%件%'; 仍是索引失效
• 即大数据情况下一定要尽量避开放在前面的模糊查询，因为失效了就是全表查询了

or 连接的条件

• 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
• 只用两侧都有索引的时候才能生效
• 实例演示：
  • 执行语句 explain select * from tb_user where id = 10 or age = 23;，其中 id 为主键有主键索引，age 无索引 ( 或不符合索引原则 ) 的话，整个索引是不会用到的，key 为 NULL
  • 就算是联合索引，条件为 phone = '12585247963' or age = 23; 的话，索引还是失效 key 为 NULL，因为 and 时成立，但换为 or 后，age 相当于单拿出来，仍是不生效
• 想解决的话，就给后面没有索引的新建一个索引，然后再 explain 就会看到 key 中有两个索引了

数据分布影响

• 如果 MySQL 评估使用索引比全表扫描更慢，则就不使用索引
• 实例演示：
  • 若是表中的年龄 age 已有索引
  • 执行语句 explain select * from tb_user where age >= 20; 可知 key 有索引有效
  • 但执行 explain select * from tb_user where age >= 0; 的话就会发现 key 并没有索引而是空 NULL，这是因为整个表中的数据 age 都是大于零的，SQL 就认为走索引效率还不如走全表扫描 ( 因为占大多数的数据都是符合条件的 )，所以干脆就使用了全表扫描而非索引
  • 可以理解为若是符合条件的、查出来的占多数，就是全表扫描，否则就是索引 ( 大多数如此，具体原因此处不做了解 )
  • is null 和 is not null 语句也是同上理论，null 的多，就是 is null 语句查到的多，就是全表扫描

SQL 提示

• 引出

  • 若是字段 profession 有一个联合索引还有一个单列索引，执行语句 explain select * from tb_user where profession = '软件工程'; 就会发现 possible_keys 可能的索引中两个都有，但实际的 key 是在 MySQL 自行优化下的结果，还是选择了联合索引
    • 此处联合索引和单列索引都先用默认的常规索引创建方式即可
  • 但若是有多个索引想自行指定的话就是要用到 SQL 提示了

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

• SQL 提示在这里主要介绍三个 ( 使用方法都是直接加在语句中的 from 表名后面 )：

  • use index(索引名) —— 建议使用的索引 ( 具体使用的还是 MySQL 来定 )：

    explain select * from tb_user use index(单列索引名) where profession = '软件工程'; 得可能和实际所用的索引都只有单列索引了

  • ignore index(索引名) —— 不使用哪个索引：

    explain select * from tb_user ignore index(单列索引名) where profession = '软件工程'; 得可能和实际所用的索引都不会有此单列索引

  • force index(索引名) —— 必须使用的索引：

    explain select * from tb_user force index(单列索引名) where profession = '软件工程'; 得可能和实际所用的索引都强制为此单列索引了

覆盖索引

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

• 实例演示：

  • 前提，id 有主键索引，profession、age、status 为联合索引
    • 先前在查看执行计划时，都是主要看 type、key、key_len，在这里我们要关注的是 Extra
  • 执行 explain select * from tb_user where profession = '软件工程' and age = 20 and status = '0'; 语句，并将星号依次替换为
    • id, profession
    • id, profession, age
    • id, profession, age, status
    • id, profession, age, status, name
  • 会发现执行计划前面的部分都一样，只有最后一句有 name 的查询的执行计划的 Extra 一列值不是 Using where; Using index 而是 Using index condition
    • 不一样的 MySQL 的版本展现的 Extra 一列的信息不一定相同
  • null 就是什么索引也没用，是回表查询
  • using index condition：查找使用了索引，但是需要回表查询数据
  • using where; using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
    • 回表：二级索引找到主键，用主键值再到主键索引拿数据
  • 由此可得：using where; using index 的效率性能更高一些

面试思考题

一张表，有四个字段 ( id, username, password, status )，由于数据量大，需要对以下 SQL 语句进行优化，该如何进行才是最优方案：select id, username, password from tb_user where username = 'itcast';

• 新建一个 username 和 password 的联合索引，这样也就用不到回表查询了

前缀索引

• 引出：

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

• 前缀索引就是，在上述情况下可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率

• 语法：

  • create index id_xxxx on table_name(column(n));
    • column(n) 是指提取出 column 字段的前面 n 个字符来构建索引

• 前缀长度：

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

• 求取选择性的语句：

  • 首先要知道当前表结构总记录数，然后用不重复的除以总数：select count(distinct email) / count(*) from tb_user;

  • email 前几个字符的话，就用函数截取，更换 n 查看截取不同字符时的选择性，根据需求选择合适的 n：select count(distinct substring(email, 1, n)) / count(*) from tb_user;

    • count email 得到的是非空的总数，distinct 去重

  • 根据计算创建合适的索引 ( 根据计算选要截取的字符数 n )：create index idx_email_n on tb_user(email(n));

  • show index from tb_user; 查看看到有此索引，并且 Sub_part 值就是截取的字符数 n

• 执行流程：

  • 假设执行：select ...... where email = '......';
  • 辅助索引，用前 n 个字符构建索引后，查询时只比较前 n 个字符 ( 即使相同，也是按顺序先拿第一个 )，找到主键后，聚集索引获取整条数据，然后将这整条数据的 email 拿出来和 SQL 语句的 email 全部进行比较，对了就返回 ( 前 n 个没有重复的话 )
  • 若是再到辅助索引中看叶子链表的下一个是否也是前面部分相同的，还有相同的话就也拿来到聚集索引中拿 email 整数据全部与 SQL 中的进行比较，多个相同的话就多条数据全留下组装后再进行返回

单列索引与联合索引

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

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

• 若是由执行计划知，单列和联合中 SQL 选择了单列索引，那么很有可能还是需要回表查询，若是单列都是唯一性了，那么可以创建新查询所需联合索引就可以加上 unique 了

  • 但就算根据语句新建了合适的索引，SQL 可能还是不选用，此时就要在语句的表名后加上 force(新建索引) 了

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

• 联合索引情况：

  • 假设创建了 create unique index idx_phone_name on tb_user(phone, name); 联合索引
  • 因为联合是多个字段，所以构建好的 B+Tree 上的键值对就是创建联合时的多个字段，每一个节点都存了一组 phone 和 name，在排序时就是先排 phone，phone 相同的再根据 name 进行排序，叶子仍是挂主键
  • 此时如果查询是 id, phone, name 的话，在上述的二级索引中可以全部查询到，就无需回表，已经覆盖索引了
  • 注意：此处因为创建时为 (phone, name) ，根据最左前缀法则可知，在查询时 SQL 语句中也要有 phone，否则查询时此索引就会失效，所以在创建索引时要考虑好顺序
    • 需要查询的可能性高的在创建时放前面

索引设计原则

• 针对于数据量较大，且查询比较频繁的表要建立索引
  • 数据量较大：表数据超过百万级别
• 针对于常作为查询条件 ( where )、排序 ( order by )、分组 ( group by ) 操作的字段建立索引
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  • 区分度高：身份证等每个人不同的数据
• 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
  • 如存放的一篇文章 ( 全部查询的话会浪费大量的磁盘 IO )
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  • 联合索引查询语句也勿忘要符合最左前缀法则
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
  • 只建立有必要的索引，没必要的索引除了影响效率，还会占用磁盘空间
• 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询