mysql 索引 (Navicat添加索引)

索引类型：NORMAL   索引方法：BTREE

一般这样子设置就OK了

 

一.sql查询语句

SELECT
	bp.id AS post_id,
	`bp`.`title`,
	bp.img AS bbs_img,
	`bp`.`content`,
	`bp`.`follow`,
	`bp`.`reply`,
	`bp`.`top`,
	`bp`.`time`,
	`bp`.`click`,
	`u`.`nickname`,
	`u`.`img`,
	u.id AS uid,
	bpf.id AS follow_id,
	`bu`.`is_moderator`,
	`bp`.`up`,
	`bp`.`down`,
	`pu`.`type` 
FROM
	`edu_bbs_post` `bp`
	INNER JOIN `edu_user` `u` ON `u`.`id` = bp.add_user_id 
	AND u.deleted = 0 
	AND u.state = 0
	LEFT JOIN `edu_bbs_post_follow` `bpf` ON `bpf`.`post_id` = bp.id 
	AND bpf.user_id = '103'
	LEFT JOIN `edu_bbs_user` `bu` ON `bu`.`plate_id` = bp.plate_id 
	AND bu.user_id = '103' 
	AND bu.is_moderator = 1 
	AND bu.deleted = 0
	LEFT JOIN `edu_bbs_post_updown` `pu` ON `pu`.`post_id` = bp.id 
	AND pu.user_id = '103' 
WHERE
	( bp.deleted = 0 AND bp.audit = 1 AND bp.plate_id = '4' ) 
ORDER BY
	`bp`.`top` DESC,
	`bp`.`id` DESC 
	LIMIT 0,
	12;

1.2查看是否有索引  命令:DESC  

 

二.添加索引 按照上面一个一个加

alter table  `edu_bbs_post` add key plate(`plate_id`);

alter table  `edu_bbs_post_follow` add key post_id(`post_id`);
alter table  `edu_bbs_post_follow` add key user_id(`user_id`);
alter table  `edu_bbs_post_follow` add key post_user(`post_id`,`user_id`);

alter table  `edu_bbs_user` add key user_plate(`user_id`,`plate_id`);

alter table  `edu_bbs_post_updown` add key post_user(`post_id`,`user_id`);

2.1 注意

plate(`plate_id`);

plate=索引名称

plate_id=要加索引的字段

2.2 联合索引  查询多个字段时，这样子写会自动判断用那个索引

alter table  `edu_bbs_post_updown` add key post_user(`post_id`,`user_id`);