剖析单条查询sql实战

1.构建表结构

create database yizhen_wqf_help;
use yizhen_wqf_help;
CREATE TABLE `image_info` (
  `PATIENT_IMAGE_PK` int(11) NOT NULL AUTO_INCREMENT ,
  `HOSPITAL_FK` int(11) DEFAULT NULL ,
  `PATIENT_FK` int(11) DEFAULT NULL ,
  `IMAGE_NO` varchar(20) DEFAULT NULL ,
  `CHECK_NO` varchar(20) DEFAULT NULL ,
  `MODALITY` varchar(20) DEFAULT NULL ,
  `IMAGE_SIZE` decimal(50,2) DEFAULT NULL ,
  `IMAGE_NUM` int(11) DEFAULT NULL ,
  `IMAGE_PATH_OUT` longtext ,
  `IMAGE_PATH_IN` longtext ,
  PRIMARY KEY (`PATIENT_IMAGE_PK`),
  KEY `idx_hos` (`HOSPITAL_FK`),
  KEY `idx_hos_checkno` (`HOSPITAL_FK`,`CHECK_NO`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;
CREATE TABLE `check_info` (
  `CHECK_APPLY_PK` int(11) NOT NULL AUTO_INCREMENT ,
  `PATIENT_HOSPITAL_FK` int(11) DEFAULT NULL ,
  `DEPARTMENT_FK` int(11) DEFAULT NULL ,
  `IMAGE_NO` varchar(20) DEFAULT NULL ,
  `CHECK_NO` varchar(20) DEFAULT NULL ,
  `APPLY_HOSPITAL_FK` int(11) DEFAULT NULL ,
  `APPLY_HOSPITAL` varchar(100) DEFAULT NULL ,
  `APPLY_DEPARTMENT_FK` int(11) DEFAULT NULL ,
  `APPLY_DEPARTMENT` varchar(100) DEFAULT NULL ,
  `ADM_ID` varchar(20) DEFAULT NULL ,
  `ADM_ID_ISS` varchar(20) DEFAULT NULL ,
  `ENROL_TIME` datetime DEFAULT NULL ,
  `VIDEO_ROOM_FK` int(11) DEFAULT NULL ,
  `MEDICAL_STATUS` varchar(20) DEFAULT NULL ,
  `PATIENT_NAME` varchar(100) DEFAULT NULL,
  `SEX` varchar(10) DEFAULT NULL,
  `DOCTOR_NAME` varchar(64) DEFAULT NULL ,
  `VIDEO_ROOM_NAME` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CHECK_APPLY_PK`),
  KEY `idx_hos_checkno` (`APPLY_HOSPITAL_FK`,`CHECK_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.导入历史数据

使用source命令分别导入两张表数据

3.大量查询制造慢日志以及系统压力

多次执行下面的慢SQL，并且监控服务器性能

select count(1) from check_info ta  
left join image_info tb 
on ta.apply_hospital_fk=tb.hospital_fk and ta.check_no=tb.check_no 
where ta.apply_hospital_fk=990042 limit 1;