首页 > 其他 > 详细

索引优化和索引失效

时间：2019-12-27 20:14:14      阅读：134      评论：0      收藏：0      [点我收藏+]

索引优化:

单表索引优化

技术分享图片

 

 

 

技术分享图片

 

 

 两个表(关联查询)

准备好数据库和数据

/*
SQLyog Ultimate v8.32 
MySQL - 5.5.48-log : Database - myschool
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=‘‘*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`myschool` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `myschool`;

/*Table structure for table `grade` */

DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade` (
  `GradeID` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘年级编号‘,
  `GradeName` varchar(50) NOT NULL COMMENT ‘年级名称‘,
  PRIMARY KEY (`GradeID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `grade` */

insert  into `grade`(`GradeID`,`GradeName`) values (1,‘S1‘),(2,‘S2‘),(3,‘Y2‘),(4,‘大数据‘),(5,‘云计算‘),(6,‘JDBC‘);

/*Table structure for table `result` */

DROP TABLE IF EXISTS `result`;

CREATE TABLE `result` (
  `StudentNo` int(4) NOT NULL COMMENT ‘学号‘,
  `SubjectNo` int(4) NOT NULL COMMENT ‘课程编号‘,
  `ExamDate` datetime NOT NULL COMMENT ‘考试日期‘,
  `StudentResult` int(4) NOT NULL COMMENT ‘考试成绩‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `result` */

insert  into `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) values (1000,1,‘2013-11-11 16:00:00‘,94),(1000,2,‘2012-11-10 10:00:00‘,75),(1000,3,‘2011-12-19 10:00:00‘,76),(1000,4,‘2010-11-18 11:00:00‘,93),(1000,5,‘2013-11-30 14:00:00‘,97),(1000,6,‘2012-09-13 15:00:00‘,87),(1000,7,‘2011-10-16 16:00:00‘,79),(1000,8,‘2010-11-11 16:00:00‘,74),(1000,9,‘2013-11-01 10:00:00‘,69),(1000,10,‘2012-11-11 12:00:00‘,60),(1000,11,‘2011-11-11 14:00:00‘,66),(1000,12,‘2010-11-11 15:00:00‘,82),(1000,13,‘2013-11-30 14:00:00‘,94),(1000,14,‘2012-11-11 15:00:00‘,98),(1000,15,‘2011-12-11 10:00:00‘,70),(1000,16,‘2010-09-11 10:00:00‘,74),(1001,1,‘2013-11-11 16:00:00‘,76),(1001,2,‘2012-11-10 10:00:00‘,93),(1001,3,‘2011-12-19 10:00:00‘,65),(1001,4,‘2010-11-18 11:00:00‘,71),(1001,5,‘2013-11-11 14:00:00‘,98),(1001,6,‘2012-09-13 15:00:00‘,74),(1001,7,‘2011-10-16 16:00:00‘,85),(1001,8,‘2010-11-11 16:00:00‘,69),(1001,9,‘2013-11-09 10:00:00‘,63),(1001,10,‘2012-11-11 12:00:00‘,70),(1001,11,‘2011-11-11 14:00:00‘,62),(1001,12,‘2010-11-11 15:00:00‘,90),(1001,13,‘2013-11-11 14:00:00‘,97),(1001,14,‘2012-11-11 15:00:00‘,89),(1001,15,‘2011-12-11 10:00:00‘,72),(1001,16,‘2010-09-11 10:00:00‘,90),(1002,1,‘2013-11-11 16:00:00‘,61),(1002,2,‘2012-11-10 10:00:00‘,80),(1002,3,‘2011-12-19 10:00:00‘,89),(1002,4,‘2010-11-18 11:00:00‘,88),(1002,5,‘2013-11-11 14:00:00‘,82),(1002,6,‘2012-09-13 15:00:00‘,91),(1002,7,‘2011-10-16 16:00:00‘,63),(1002,8,‘2010-11-11 16:00:00‘,84),(1002,9,‘2013-11-20 10:00:00‘,60),(1002,10,‘2012-11-11 12:00:00‘,71),(1002,11,‘2011-11-11 14:00:00‘,93),(1002,12,‘2010-11-11 15:00:00‘,96),(1002,13,‘2013-11-11 14:00:00‘,83),(1002,14,‘2012-11-11 15:00:00‘,69),(1002,15,‘2011-12-11 10:00:00‘,89),(1002,16,‘2010-09-11 10:00:00‘,83),(1003,1,‘2013-11-11 16:00:00‘,91),(1003,2,‘2012-11-10 10:00:00‘,75),(1003,3,‘2011-12-19 10:00:00‘,65),(1003,4,‘2010-11-18 11:00:00‘,63),(1003,5,‘2013-11-11 14:00:00‘,90),(1003,6,‘2012-09-13 15:00:00‘,96),(1003,7,‘2011-10-16 16:00:00‘,97),(1003,8,‘2010-11-11 16:00:00‘,77),(1003,9,‘2013-11-21 10:00:00‘,62),(1003,10,‘2012-11-11 12:00:00‘,81),(1003,11,‘2011-11-11 14:00:00‘,76),(1003,12,‘2010-11-11 15:00:00‘,61),(1003,13,‘2013-11-11 14:00:00‘,93),(1003,14,‘2012-11-11 15:00:00‘,79),(1003,15,‘2011-12-11 10:00:00‘,78),(1003,16,‘2010-09-11 10:00:00‘,96),(1004,1,‘2013-11-11 16:00:00‘,84),(1004,2,‘2012-11-10 10:00:00‘,79),(1004,3,‘2011-12-19 10:00:00‘,76),(1004,4,‘2010-11-18 11:00:00‘,78),(1004,5,‘2013-11-11 14:00:00‘,81),(1004,6,‘2012-09-13 15:00:00‘,90),(1004,7,‘2011-10-16 16:00:00‘,63),(1004,8,‘2010-11-11 16:00:00‘,89),(1004,9,‘2013-11-19 10:00:00‘,67),(1004,10,‘2012-11-11 12:00:00‘,100),(1004,11,‘2011-11-11 14:00:00‘,94),(1004,12,‘2010-11-11 15:00:00‘,65),(1004,13,‘2013-11-11 14:00:00‘,86),(1004,14,‘2012-11-11 15:00:00‘,77),(1004,15,‘2011-12-11 10:00:00‘,82),(1004,16,‘2010-09-11 10:00:00‘,87),(1005,1,‘2013-11-11 16:00:00‘,82),(1005,2,‘2012-11-10 10:00:00‘,92),(1005,3,‘2011-12-19 10:00:00‘,80),(1005,4,‘2010-11-18 11:00:00‘,92),(1005,5,‘2013-11-11 14:00:00‘,97),(1005,6,‘2012-09-13 15:00:00‘,72),(1005,7,‘2011-10-16 16:00:00‘,84),(1005,8,‘2010-11-11 16:00:00‘,79),(1005,9,‘2013-11-21 10:00:00‘,60),(1005,10,‘2012-11-11 12:00:00‘,87),(1005,11,‘2011-11-11 14:00:00‘,65),(1005,12,‘2010-11-11 15:00:00‘,67),(1005,13,‘2013-11-11 14:00:00‘,63),(1005,14,‘2012-11-11 15:00:00‘,64),(1005,15,‘2011-12-11 10:00:00‘,99),(1005,16,‘2010-09-11 10:00:00‘,97),(1006,1,‘2013-11-11 16:00:00‘,82),(1006,2,‘2012-11-10 10:00:00‘,73),(1006,3,‘2011-12-19 10:00:00‘,79),(1006,4,‘2010-11-18 11:00:00‘,63),(1006,5,‘2013-11-11 14:00:00‘,97),(1006,6,‘2012-09-13 15:00:00‘,83),(1006,7,‘2011-10-16 16:00:00‘,78),(1006,8,‘2010-11-11 16:00:00‘,88),(1006,9,‘2013-11-21 10:00:00‘,89),(1006,10,‘2012-11-11 12:00:00‘,82),(1006,11,‘2011-11-11 14:00:00‘,70),(1006,12,‘2010-11-11 15:00:00‘,69),(1006,13,‘2013-11-11 14:00:00‘,64),(1006,14,‘2012-11-11 15:00:00‘,80),(1006,15,‘2011-12-11 10:00:00‘,90),(1006,16,‘2010-09-11 10:00:00‘,85),(1007,1,‘2013-11-11 16:00:00‘,87),(1007,2,‘2012-11-10 10:00:00‘,63),(1007,3,‘2011-12-19 10:00:00‘,70),(1007,4,‘2010-11-18 11:00:00‘,74),(1007,5,‘2013-11-11 14:00:00‘,79),(1007,6,‘2012-09-13 15:00:00‘,83),(1007,7,‘2011-10-16 16:00:00‘,86),(1007,8,‘2010-11-11 16:00:00‘,76),(1007,9,‘2013-11-30 10:00:00‘,65),(1007,10,‘2012-11-11 12:00:00‘,87),(1007,11,‘2011-11-11 14:00:00‘,69),(1007,12,‘2010-11-11 15:00:00‘,69),(1007,13,‘2013-11-11 14:00:00‘,90),(1007,14,‘2012-11-11 15:00:00‘,84),(1007,15,‘2011-12-11 10:00:00‘,95),(1007,16,‘2010-09-11 10:00:00‘,92),(1008,1,‘2013-11-11 16:00:00‘,96),(1008,2,‘2012-11-10 10:00:00‘,62),(1008,3,‘2011-12-19 10:00:00‘,97),(1008,4,‘2010-11-18 11:00:00‘,84),(1008,5,‘2013-11-11 14:00:00‘,86),(1008,6,‘2012-09-13 15:00:00‘,72),(1008,7,‘2011-10-16 16:00:00‘,67),(1008,8,‘2010-11-11 16:00:00‘,83),(1008,9,‘2013-11-21 10:00:00‘,86),(1008,10,‘2012-11-11 12:00:00‘,60),(1008,11,‘2011-11-11 14:00:00‘,61),(1008,12,‘2010-11-11 15:00:00‘,68),(1008,13,‘2013-11-11 14:00:00‘,99),(1008,14,‘2012-11-11 15:00:00‘,77),(1008,15,‘2011-12-11 10:00:00‘,73),(1008,16,‘2010-09-11 10:00:00‘,78),(1009,1,‘2013-11-11 16:00:00‘,67),(1009,2,‘2012-11-10 10:00:00‘,70),(1009,3,‘2011-12-19 10:00:00‘,75),(1009,4,‘2010-11-18 11:00:00‘,92),(1009,5,‘2013-11-11 14:00:00‘,76),(1009,6,‘2012-09-13 15:00:00‘,90),(1009,7,‘2011-10-16 16:00:00‘,62),(1009,8,‘2010-11-11 16:00:00‘,68),(1009,9,‘2013-11-21 10:00:00‘,70),(1009,10,‘2012-11-11 12:00:00‘,83),(1009,11,‘2011-11-11 14:00:00‘,88),(1009,12,‘2010-11-11 15:00:00‘,65),(1009,13,‘2013-11-11 14:00:00‘,91),(1009,14,‘2012-11-11 15:00:00‘,99),(1009,15,‘2011-12-11 10:00:00‘,65),(1009,16,‘2010-09-11 10:00:00‘,83),(1010,1,‘2013-11-11 16:00:00‘,83),(1010,2,‘2012-11-10 10:00:00‘,87),(1010,3,‘2011-12-19 10:00:00‘,89),(1010,4,‘2010-11-18 11:00:00‘,99),(1010,5,‘2013-11-11 14:00:00‘,91),(1010,6,‘2012-09-13 15:00:00‘,96),(1010,7,‘2011-10-16 16:00:00‘,72),(1010,8,‘2010-11-11 16:00:00‘,72),(1010,9,‘2013-11-14 10:00:00‘,98),(1010,10,‘2012-11-11 12:00:00‘,73),(1010,11,‘2011-11-11 14:00:00‘,68),(1010,12,‘2010-11-11 15:00:00‘,62),(1010,13,‘2013-11-11 14:00:00‘,67),(1010,14,‘2012-11-11 15:00:00‘,69),(1010,15,‘2011-12-11 10:00:00‘,71),(1010,16,‘2010-09-11 10:00:00‘,66),(1011,1,‘2013-11-11 16:00:00‘,62),(1011,2,‘2012-11-10 10:00:00‘,72),(1011,3,‘2011-12-19 10:00:00‘,96),(1011,4,‘2010-11-18 11:00:00‘,64),(1011,5,‘2013-11-11 14:00:00‘,89),(1011,6,‘2012-09-13 15:00:00‘,91),(1011,7,‘2011-10-16 16:00:00‘,95),(1011,8,‘2010-11-11 16:00:00‘,96),(1011,9,‘2013-11-21 10:00:00‘,89),(1011,10,‘2012-11-11 12:00:00‘,73),(1011,11,‘2011-11-11 14:00:00‘,82),(1011,12,‘2010-11-11 15:00:00‘,98),(1011,13,‘2013-11-11 14:00:00‘,66),(1011,14,‘2012-11-11 15:00:00‘,69),(1011,15,‘2011-12-11 10:00:00‘,91),(1011,16,‘2010-09-11 10:00:00‘,69),(1012,1,‘2013-11-11 16:00:00‘,86),(1012,2,‘2012-11-10 10:00:00‘,66),(1012,3,‘2011-12-19 10:00:00‘,97),(1012,4,‘2010-11-18 11:00:00‘,69),(1012,5,‘2013-11-11 14:00:00‘,70),(1012,6,‘2012-09-13 15:00:00‘,74),(1012,7,‘2011-10-16 16:00:00‘,91),(1012,8,‘2010-11-11 16:00:00‘,97),(1012,9,‘2013-11-16 10:00:00‘,84),(1012,10,‘2012-11-11 12:00:00‘,82),(1012,11,‘2011-11-11 14:00:00‘,90),(1012,12,‘2010-11-11 15:00:00‘,91),(1012,13,‘2013-11-11 14:00:00‘,91),(1012,14,‘2012-11-11 15:00:00‘,97),(1012,15,‘2011-12-11 10:00:00‘,85),(1012,16,‘2010-09-11 10:00:00‘,90),(1013,1,‘2013-11-11 16:00:00‘,120),(1013,2,‘2012-11-10 10:00:00‘,69),(1013,3,‘2011-12-19 10:00:00‘,91),(1013,4,‘2010-11-18 11:00:00‘,72),(1013,5,‘2013-11-11 14:00:00‘,76),(1013,6,‘2012-09-13 15:00:00‘,87),(1013,7,‘2011-10-16 16:00:00‘,61),(1013,8,‘2010-11-11 16:00:00‘,77),(1013,9,‘2013-11-21 10:00:00‘,83),(1013,10,‘2012-11-11 12:00:00‘,99),(1013,11,‘2011-11-11 14:00:00‘,91),(1013,12,‘2010-11-11 15:00:00‘,84),(1013,13,‘2013-11-11 14:00:00‘,98),(1013,14,‘2012-11-11 15:00:00‘,74),(1013,15,‘2011-12-11 10:00:00‘,92),(1013,16,‘2010-09-11 10:00:00‘,90),(1014,1,‘2013-11-11 16:00:00‘,64),(1014,2,‘2012-11-10 10:00:00‘,81),(1014,3,‘2011-12-19 10:00:00‘,79),(1014,4,‘2010-11-18 11:00:00‘,74),(1014,5,‘2013-11-11 14:00:00‘,65),(1014,6,‘2012-09-13 15:00:00‘,88),(1014,7,‘2011-10-16 16:00:00‘,86),(1014,8,‘2010-11-11 16:00:00‘,77),(1014,9,‘2013-11-15 10:00:00‘,86),(1014,10,‘2012-11-11 12:00:00‘,85),(1014,11,‘2011-11-11 14:00:00‘,86),(1014,12,‘2010-11-11 15:00:00‘,75),(1014,13,‘2013-11-11 14:00:00‘,89),(1014,14,‘2012-11-11 15:00:00‘,79),(1014,15,‘2011-12-11 10:00:00‘,73),(1014,16,‘2010-09-11 10:00:00‘,68),(1015,1,‘2013-11-11 16:00:00‘,99),(1015,2,‘2012-11-10 10:00:00‘,60),(1015,3,‘2011-12-19 10:00:00‘,60),(1015,4,‘2010-11-18 11:00:00‘,75),(1015,5,‘2013-11-11 14:00:00‘,78),(1015,6,‘2012-09-13 15:00:00‘,78),(1015,7,‘2011-10-16 16:00:00‘,84),(1015,8,‘2010-11-11 16:00:00‘,95),(1015,2,‘2013-11-11 16:00:00‘,93),(1015,10,‘2012-11-11 12:00:00‘,79),(1015,11,‘2011-11-11 14:00:00‘,74),(1015,12,‘2010-11-11 15:00:00‘,65),(1015,13,‘2013-11-11 14:00:00‘,63),(1015,14,‘2012-11-11 15:00:00‘,74),(1015,15,‘2011-12-11 10:00:00‘,67),(1015,16,‘2010-09-11 10:00:00‘,65),(1016,1,‘2013-11-11 16:00:00‘,97),(1016,2,‘2012-11-10 10:00:00‘,90),(1016,3,‘2011-12-19 10:00:00‘,77),(1016,4,‘2010-11-18 11:00:00‘,75),(1016,5,‘2013-11-11 14:00:00‘,75),(1016,6,‘2012-09-13 15:00:00‘,97),(1016,7,‘2011-10-16 16:00:00‘,96),(1016,8,‘2010-11-11 16:00:00‘,92),(1016,9,‘2013-11-30 10:00:00‘,62),(1016,10,‘2012-11-11 12:00:00‘,83),(1016,11,‘2011-11-11 14:00:00‘,98),(1016,12,‘2010-11-11 15:00:00‘,94),(1016,13,‘2013-11-11 14:00:00‘,62),(1016,14,‘2012-11-11 15:00:00‘,97),(1016,15,‘2011-12-11 10:00:00‘,76),(1016,16,‘2010-09-11 10:00:00‘,82),(1017,1,‘2013-11-11 16:00:00‘,100),(1017,2,‘2012-11-10 10:00:00‘,88),(1017,3,‘2011-12-19 10:00:00‘,86),(1017,4,‘2010-11-18 11:00:00‘,73),(1017,5,‘2013-11-11 14:00:00‘,96),(1017,6,‘2012-09-13 15:00:00‘,64),(1017,7,‘2011-10-16 16:00:00‘,81),(1017,8,‘2010-11-11 16:00:00‘,66),(1017,9,‘2013-11-11 10:00:00‘,76),(1017,10,‘2012-11-11 12:00:00‘,95),(1017,11,‘2011-11-11 14:00:00‘,73),(1017,12,‘2010-11-11 15:00:00‘,82),(1017,13,‘2013-11-11 14:00:00‘,85),(1017,14,‘2012-11-11 15:00:00‘,68),(1017,15,‘2011-12-11 10:00:00‘,99),(1017,16,‘2010-09-11 10:00:00‘,76),(1001,1,‘1998-10-03 00:00:00‘,3000);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `StudentNo` int(4) NOT NULL COMMENT ‘学号‘,
  `LoginPwd` varchar(20) DEFAULT NULL,
  `StudentName` varchar(20) DEFAULT NULL COMMENT ‘学生姓名‘,
  `Sex` tinyint(1) DEFAULT NULL COMMENT ‘性别，取值0或1‘,
  `GradeId` int(11) DEFAULT NULL COMMENT ‘年级编号‘,
  `Phone` varchar(50) NOT NULL COMMENT ‘联系电话，允许为空，即可选输入‘,
  `Address` varchar(255) NOT NULL COMMENT ‘地址，允许为空，即可选输入‘,
  `BornDate` datetime DEFAULT NULL COMMENT ‘出生时间‘,
  `Email` varchar(50) NOT NULL COMMENT ‘邮箱账号，允许为空，即可选输入‘,
  `IdentityCard` varchar(18) DEFAULT NULL COMMENT ‘身份证号‘,
  PRIMARY KEY (`StudentNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) values (1000,‘111111‘,‘郭靖‘,1,1,‘13500000001‘,‘北京海淀区中关村大街1号‘,‘1986-12-11 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612111234‘),(1001,‘123456‘,‘李文才‘,1,2,‘13500000002‘,‘河南洛阳‘,‘1981-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198112311234‘),(1002,‘111111‘,‘李斯文‘,1,1,‘13500000003‘,‘天津市和平区‘,‘1986-11-30 00:00:00‘,‘test1@bdqn.cn‘,‘450323198611301234‘),(1003,‘123456‘,‘武松‘,1,3,‘13500000004‘,‘上海卢湾区‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612314234‘),(1004,‘123456‘,‘张三‘,1,4,‘13500000005‘,‘北京市通州‘,‘1989-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311244‘),(1005,‘123456‘,‘张秋丽 ‘,2,1,‘13500000006‘,‘广西桂林市灵川‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311214‘),(1006,‘123456‘,‘肖梅‘,2,4,‘13500000007‘,‘地址不详‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311134‘),(1007,‘111111‘,‘欧阳峻峰‘,1,1,‘13500000008‘,‘北京东城区‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311133‘),(1008,‘111111‘,‘梅超风‘,1,1,‘13500000009‘,‘河南洛阳‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311221‘),(1009,‘123456‘,‘刘毅‘,1,2,‘13500000011‘,‘安徽‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311231‘),(1010,‘111111‘,‘大凡‘,1,1,‘13500000012‘,‘河南洛阳‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311044‘),(1011,‘111111‘,‘奥丹斯‘,1,1,‘13500000013‘,‘北京海淀区中关村大街*号‘,‘1984-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198412311234‘),(1012,‘123456‘,‘多伦‘,2,3,‘13500000014‘,‘广西南宁中央大街‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311334‘),(1013,‘123456‘,‘李梅‘,2,1,‘13500000015‘,‘上海卢湾区‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311534‘),(1014,‘123456‘,‘张得‘,2,4,‘13500000016‘,‘北京海淀区中关村大街*号‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311264‘),(1015,‘123456‘,‘李东方‘,1,4,‘13500000017‘,‘广西桂林市灵川‘,‘1976-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323197612311234‘),(1016,‘111111‘,‘刘奋斗‘,1,1,‘13500000018‘,‘上海卢湾区‘,‘1986-12-31 00:00:00‘,‘test1@bdqn.cn‘,‘450323198612311251‘),(1017,‘123456‘,‘可可‘,2,3,‘13500000019‘,‘北京长安街1号‘,‘1981-09-10 00:00:00‘,‘test1@bdqn.cn‘,‘450323198109108311‘);

/*Table structure for table `subject` */

DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject` (
  `SubjectNo` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘课程编号‘,
  `SubjectName` varchar(50) DEFAULT NULL COMMENT ‘课程名称‘,
  `ClassHour` int(4) DEFAULT NULL COMMENT ‘学时‘,
  `GradeID` int(4) DEFAULT NULL COMMENT ‘年级编号‘,
  PRIMARY KEY (`SubjectNo`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

/*Data for the table `subject` */

insert  into `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) values (1,‘高等数学-1‘,110,1),(2,‘高等数学-2‘,110,2),(3,‘高等数学-3‘,100,3),(4,‘高等数学-4‘,130,4),(5,‘C语言-1‘,110,1),(6,‘C语言-2‘,110,2),(7,‘C语言-3‘,100,3),(8,‘C语言-4‘,130,4),(9,‘JAVA第一学年‘,110,1),(10,‘JAVA第二学年‘,110,2),(11,‘JAVA第三学年‘,100,3),(12,‘JAVA第四学年‘,130,4),(13,‘数据库结构-1‘,110,1),(14,‘数据库结构-2‘,110,2),(15,‘数据库结构-3‘,100,3),(16,‘数据库结构-4‘,130,4),(17,‘C#基础‘,130,1);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

为了测试方便,这几张表暂时没有任何的主外键关系,Studnet表中的StudentNo,grade表中的GradeID,subject表中的SubjectNo都是它们所在表中的主键。

主键会默认生成一个主键索引

这次就拿Student(学生表)和Grade(年级表)来进行测试

第一种:当关联条件使用的不是主键列

执行EXPLAIN语句

#查询所有年级的所有学生姓名
EXPLAIN  SELECT StudentName FROM student LEFT JOIN grade  ON student.GradeId=grade.GradeId;

技术分享图片

 

分析:

第一行:student表的type=ALL等于进行了全盘扫描,因为左连接想要的就是student表中的所有数据。

第二行:type=eq_ref，key中使用了"PRIMARY"主键索引,达到的效果可以说是很好了,无需再进行优化了

结论:如果在连接查询当中.关联条件使用的是从表的主键,则可以直接使用从表的主键索引,无需在进行优化

 

第二种:当关联条件使用的不是主键列

先将Grade表中GradeID的主键标识给去除,然后执行刚才执行的EXPLAIN语句

技术分享图片

 

 

 分析:由于没有使用任何的索引,这条sql对两个表进行了全盘扫描,对Student进行全盘扫描刚才介绍过了,但是对Grade进行全盘扫描我们接受不了,因为不是所有的列我们都用上了;

tip:这种情况Extra中还有可能使用连接缓存using join buffer

进行索引优化

在LEFT JOIN语句右边的表作为从表,在RIGHT JOIN语句左边的是从表,给从表中的关联列添加索引

CREATE INDEX gradeid ON grade(GradeID);#给grade(从表)的GradeId添加索引

重新执行EXPLAIN语句

技术分享图片

 

 

这样进行优化之后,和之前gradeID作为主键的效果一样,就这就做到了不依赖于主键也能进行索引优化,(当然主键生成的索引和自己创建的所以都是差不多的)

多个表进行关联查询的时候创建索引的思路也是这样的,给从表的关联列添加索引

索引失效

还是使用上面案例的Studnet表,StudentNO是该表的主键

执行EXPLAIN,条件列一共有三个StudentName,gradeid,sex

EXPLAIN SELECT  StudentName,Sex,GradeId  FROM student WHERE StudentName=‘郭靖‘ AND gradeid=1 AND sex=1 ;

技术分享图片

 

 

 由于没有使用任何的索引,只查询一条或者多条记录就对全盘进行扫描,这样的sql在真实的场景中是不允许发送的。

接下来我们个这三个字段按照顺序添加一个复合索引student_ngs

CREATE INDEX student_ngs ON student(StudentName,GradeId,Sex);

 

执行结果:技术分享图片

 

 

 

在Mysql中,查询优化器会根据复合索引的顺序进行顺序优化,优化完的顺序才是真实的属性

比如一张表的索引是 index(1,2,3,4) 你sql的where条件是 4,3,2,1 这种情况下还是会使用到所有的索引

1.完全匹配

意思就是说:所要查询的列都是索引中包含的列

2.最左前缀原则

它的意思通俗理解就是:如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

不在乎顺序,哪怕第一个索引列是最后一个WHERE条件,只要前面没有存在触发索引失效的条件,mysql会进行查询优化处理,不会因为顺序乱导致索引失效!

正常情况:根据索引的顺序,查询了所有列

EXPLAIN SELECT  *  FROM student WHERE StudentName=‘郭靖‘ AND gradeid=1 AND sex=1;

技术分享图片

 

 

 异常情况一:只根据gradeid和sex进行查询,不包含索引最左侧的StudentNmae

 

EXPLAIN SELECT  *  FROM student WHERE  gradeid=1 AND sex=1 ;

 

技术分享图片

 

 

异常情况二:缺少中间的gradeid条件进行查询 

EXPLAIN SELECT  *  FROM student WHERE StudentName=‘郭靖‘  AND sex=1;

技术分享图片

 

 

 上面的两种异常情况就会发生索引失效的情况,应为它违反了最左前缀原则

3.不在索引列上做任何操作(计算、函数、类型转换(自动or手动))

举例:将StudentName使用LEFT()函数进行截取之后作为条件 

 

EXPLAIN SELECT  *  FROM student WHERE LEFT(StudentName,2)=‘郭靖‘;

 

技术分享图片

 

 

 这样类似的情况都会导致索引失效而转向全表扫描

4.字符串不加单引号导致索引失效

如下图所示,在Student表中有一条StudentName列为100的记录,但是StudentName列的类型为varchar(20)

技术分享图片

 

 

 接下来根据100这个"名字"来进行查询,下面两条sql语句返回的结果是相同的,那是因为mysql底层为我们做了自动类型装换,将int类型值转换成了varchar

 

SELECT  *  FROM student WHERE StudentName=100;
SELECT  *  FROM student WHERE StudentName=‘100‘;

但是将上面两条sql语句加上EXPLAIN进行查看它们分别显示的消息

技术分享图片

 

 

 因为mysql底层为我们做了自动类型装换,根据第3条所说的,发生了自动类型的转换导致了索引失效

5.存储引擎不能使用索引中范围条件右边的列

 

 

 

语句中的一个字段进行了范围查询例如">","<","in"等等,那么在这个列右边的所有索引都会失效

 

EXPLAIN SELECT  *  FROM student WHERE StudentName=‘郭靖‘ AND gradeid>1;

EXPLAIN SELECT  *  FROM student WHERE StudentName=‘郭靖‘ AND gradeid>1 AND sex=1;

 

上面那两条语句如果只为了查询的话,结果有可能是不一样的,但是在前面加上EXPLAIN之后,显示的信息是一摸一样的,都只用上了前两个索引,

第一条sql是没有加sex条件

第二条sql是由于gradeid是区间性的不单单只是一个取值,导致了sex索引的失效

 

技术分享图片

 

 

 6.索引列使用"<>"或者"!="时会导致索引失效

情况一:第一个索引列使用"!="时,一个索引都不会生效,从而转为全盘扫描

EXPLAIN SELECT  *  FROM student WHERE StudentName!=‘郭靖‘ AND gradeid=1 AND sex=1;

技术分享图片

 

 

 情况二:非第一个索引列使用"!=时",从使用的哪一个索引列开始往后,所有的索引都不生效(包括当前列的索引)

EXPLAIN SELECT  *  FROM student WHERE StudentName=‘郭靖‘ AND gradeid!=1 AND sex=1;

技术分享图片

 

 

7.索引列使用"IS NOT NULL"时也会导致索引失效

EXPLAIN SELECT  *  FROM student WHERE StudentName IS NOT NULL AND gradeid=1 AND sex=1 ;

技术分享图片

 

 8.使用like模糊查询时,以通配符开头的情况

例如:StudnetName like ‘_郭‘ 或者 ‘%郭‘

EXPLAIN SELECT  *  FROM student WHERE StudentName LIKE ‘%郭靖‘ AND gradeid=1 AND sex=1;

技术分享图片

上面的情况就会导致索引失效 ,而通配符在后面的情况就不会导致索引失效,如下所示

EXPLAIN SELECT  *  FROM student WHERE StudentName LIKE ‘郭靖%‘ AND gradeid=1 AND sex=1;

技术分享图片

 

 

在实际开发中,怎样解决以通配符开头引发的索引失效?

就是所要查询的列都要是索引列(包括主键索引),只要其中有一个列不是主键列,还是会导致索引失效

select 后面的列都是索引列,还是会使用索引

EXPLAIN SELECT StudentName,sex,GradeId,StudentNo#主键列 
FROM student WHERE StudentName LIKE ‘%郭靖‘;

技术分享图片

 

 在上面的Sql的Select之后添加一个不是索引列的列(Phone),就会导致索引失效

EXPLAIN SELECT StudentName,sex,GradeId,Phone,StudentNo#主键列 
FROM student WHERE StudentName LIKE ‘%郭靖‘;

 

技术分享图片

 

 

最好使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*的使用

索引优化和索引失效

原文：https://www.cnblogs.com/yjc1605961523/p/12109139.html
踩
(0)
赞
(0)
   
举报
评论 一句话评论（0）
分享档案
更多>
2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)
最新文章
更多>
教程昨日排行
更多>
友情链接
汇智网   PHP教程   插件网  
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣，分享程序人生！