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

索引

索引使用

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

• 在未建立索引之前，执行 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 值时，它可以更好地确定哪个索引最有效地用于查询

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 用于PostgreSQL索引维护的有用查询
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 索引的一些总结
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset

法则 索引 场景 情况 MySQL法则 索引 场景 情况 前缀 法则 索引mysql 索引 情况mysql 索引explain情况mysql 索引 情况mysql order 索引 常见 情况mysql 索引 场景 类型mysql 索引 场景mysql 索引 场景innodb mysql 索引 情况