走进索引，看一看数据库查询（一）

最新推荐文章于 2022-11-17 11:17:11 发布

笔下天地宽

最新推荐文章于 2022-11-17 11:17:11 发布

阅读量233

点赞数 1

本文链接：https://blog.csdn.net/zsah2011/article/details/115655680

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

上一篇文章简单的说了下数据库索引的结构，接下来当然要看一看索引到底是咋用的，要是建立了索引却没用，那不是太亏了不是？当然，慢一点不要就，就怕进行大批量的操作，数据量又不少，最后一直在加载表数据到缓存，直接把缓存挤爆了，那就惨了，哈哈~

那怎么判断一个查询语句有没有用索引呢？加了索引，难道就会用到？肯定不是啊，背面试题的时候，肯定要记，哦，索引加上左右‘%’会失效，使用聚合函数会失效等等等等。

那SQL到底用没用索引呢？这个要MySQL来告诉我们。每一个SQL执行前，MySQL会对这个SQL语句进行分析，然后确认查询方式，争取使用最快的速度查询目标数据！而在分析过程中之后，执行之前，自然有个东东，也就是咋执行，书面语就是执行计划！好吧，我们先来瞅瞅MySQL的执行计划到底是啥。

首先，先创建两个表。

- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `stu_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
  `stu_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生编号',
  `school_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校编码',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_code`(`stu_code`) USING BTREE COMMENT '学生编码索引'
) ENGINE = InnoDB AUTO_INCREMENT = 1446881 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `teacher_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师工号',
  `teacher_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师名称',
  `school_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `code`(`teacher_code`) USING BTREE COMMENT '教师索引',
  UNIQUE INDEX `name`(`teacher_name`) USING BTREE COMMENT '名称索引'
) ENGINE = InnoDB AUTO_INCREMENT = 42100 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;


-- 多对多，师生关系表
CREATE TABLE `relation`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) NULL DEFAULT NULL,
  `student_id` int(11) NULL DEFAULT NULL,
  `remark` varchar(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `key`(`teacher_id`, `student_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

创建两个存储过程，批量写数据，写的存储过程比较简单，可以尝试优化存储过程，使用批量插入，插入速度会快很多。我已经插入完成了，就不改了哈。

-- 存储过程插入学生表
CREATE PROCEDURE `batchInStu`( i INT )
BEGIN 
		DECLARE a int DEFAULT 1;
		while a <= i DO
			START TRANSACTION;
			INSERT INTO student(stu_name, stu_code, school_code, create_time) VALUES (CONCAT('张三',a),REPLACE(UUID(),'-',''),20,CURRENT_TIMESTAMP);
			COMMIT;
			SET a = a+1;
			end while;
end

搞定！学生表的话我大概插入了100多万条数据，教师表插入100条数据左右。基础设施建设完毕，正题开始！

如上图，查询某个SQL的查询效率，是否使用索引等信息，可以在整个查询语句之前，添加一个单词，explain！

查询的结果，一共有12个字段。

1.id列

这个其实就是查询的序列号，序号越大，执行的优先级越高，相同的话就从上往下执行。这个一般使用多级查询或者关联子查询才会用到。

2.select_type 列

select_type一般分为六种simple，primary，subquery，derived，union和union result。

simple：简单的select查询，查询中不包含子查询或者union。

primary：查询中包含子查询，最外层的查询呗标记为primary。

subquery：表示在select中包含含子查询，举个例子：explain SELECT id ,(SELECT min(teacher_code) from teacher) as num from student where id <25; 这个查询teacher表的select_type就是subquery；