mysql 性能优化（四）多表优化

两张表

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL COMMENT '主键',
  `cid` int(11) NOT NULL COMMENT '课程id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='老师表';


CREATE TABLE `course` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(20) NOT NULL COMMENT '课程名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表'; 

 INSERT INTO teacher
VALUES
	( 1, 2 ),
	( 2, 1 ),
	( 3, 3 );
INSERT INTO course
VALUES
	( 1, 'java' ),
	( 2, 'python' ),
	( 3, 'html' );
# 关联查询 java
SELECT * FROM teacher t LEFT JOIN course c ON t.cid = c.id WHERE c.`name`='java';

优化

如何加索引？

• 小表驱动大表：select ... where 小表.x = 大表.x

  例如小表10条数据，大表100条数据，相当于2层for循环，每过滤一次小表数据，对应大表全部数据

  for(int i = 0;i <10;i++){
  	
  	for(int u = 0;u<100;u++)	{
  	}
  }
  

  反之，

  for(int i = 0;i <100;i++){
  	
  	for(int u = 0;u<10;u++)	{
  	}
  }
  

  以上结果是一样的，一共是1000次，但是对于双层循环来讲，一般建议小的循环放在外层，大的在内层， 这样的编程原则，效率最高

  • 当编写 ... ON t.cid = c.id 时将数据量小的放在左侧（假设t表数据小）

• 索引加在经常使用的字段上

  • 如上所述，小表的使用频率最高，每条数据都会执行100次。（示例中t表最小，t.cid加索引 ）

• 一般左外链接给左表加索引，右外链接给右表加索引

进行优化

• 原始执行计划
  
• using join buffer：mysq自动使用了连接缓存，就是sql性能太差了
• 添加索引

  ALTER TABLE teacher add INDEX  idx_t_cid(cid);
  
  explain SELECT * FROM teacher t LEFT JOIN course c ON t.cid = c.id WHERE c.`name`='java';
  

• 执行计划
• 查询条件增加索引，可以看到索引级别增加很多

  ALTER TABLE course add INDEX  idx_c_name(name);
  

  

多张表

同2张表一样

• 小表驱动大表：select ... where 小表.x = 大表.x
• 索引加在经常使用的字段上