MySQL查询数据过程探索

等值匹配原则

  • 通过主键查找,不需要回表,因为主键下面的叶子节点记录本行的所有数据。

  • 通过唯一索引查找,如果你select的不是这个唯一索引而是类似select *这样非次唯一索引列,那么需要回表,通过主键找到本行所有数据

  • 通过联合索引,情况同唯一索引,如果select的内容不是此索引包含的列,需回表。

假设一张表demo  id,age,name,telephone三列  对age,name建立了联合索引。那么
select * from demo where age = 18需要回表吗?
需要。因为telephone不在次索引中,还需通过主键去查找telephone的值。
而select name from demo where age = 18就不需要回表了,因为此索引中包含name列的值。

最左前缀匹配原则,仅针对联合索引

  • 这个规则就像盖楼房,得一层盖好再盖另一层,不可能直接盖3楼,mysql来了也不行。
  • 示例表结构,注意联合索引的顺序是addr,age,name
CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `name` varchar(11) DEFAULT NULL COMMENT '姓名',
  `addr` varchar(50) DEFAULT NULL COMMENT '地址',
  `telephone` varchar(20) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`telephone`),
  KEY `union` (`addr`,`age`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  • 直接盖三楼,不可能,还是会给你从开始盖的,全表扫描。

    explain select * from demo where name = 'mysql';

    image-20210812134615629

  • 就要一楼和二楼,三楼没钱盖了,用到了此联合索引,但是可能不完整,看key_len的值
    image-20210812134947023

  • 有钱,全盖,索引长度完整,和计划的楼房高度一致
    image-20210812135132038

范围查询规则

  • 表结构

    CREATE TABLE `demo` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `age` int(3) DEFAULT NULL COMMENT '年龄',
      `stu_id` int(3) DEFAULT NULL COMMENT '姓名',
      `tel` int(3) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `union` (`age`,`stu_id`),
      KEY `saddad` (`tel`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    
  1. 一个范围查询,注意key_len,这个也可以从侧面看它用了几个索引
    image-20210813103145964

  2. 多个范围查询
    image-20210813103511769
    image-20210813112442388

    mysql认为可能还用到了唯一索引,但实际并没有用到,还是只用到了联合索引,
    再看key_len的长度和情况1一样,所以验证结论,where后只有第一个范围查
    找才生效(如果第一个索引失效,则顺延)。如果有联合索引,仅最左侧的索引字段生效.
    
  3. 范围查询+等值匹配

    • 优先有索引的等值查询
      image-20210813105433443
      image-20210813111816384

    • where后是联合索引
      image-20210813111253140

      mysql先去union的索引树找age等于1的,然后按范围去排序stu_id。
      最左匹配原则,也就是前面的记录必须是确定的,这样子才能继续对后面的数据判断。
      

order by优化

image-20210813120850180

大概懂什么意思了,尽量order索引,因为索引本来就是排好序的,select啥呢,还是索引包含的列或者id,为啥呢,此索引树种的叶子节点就保存这索引列的值和id,为啥还有id,因为回表的时候需要id来找整行数据

Q.E.D.


一个热爱生活的95后精神小伙