mysql百万级别数据查询心得

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">作为第一次接触百万级别数据的查询的我来说一直觉得这是很神奇很强大的一门技术，总以为需要进行表分割啊，负载均衡之类的才能做到查询优化。其实不然，这个级别的数据查询我们只需要有效的利用索引，书写比较高效的sql就能做到（所谓比较高效其实就是尽量少用is not null  ， 不要使用like ‘%XX’等会造成索引失效的sql啦，这个度娘上很多例子的）。</span>

就我自己的案例讲讲一些注意事项吧：

这是我的sql语句（还有很多没优化的）：

select * from(
	SELECT
		`REPTILE_AUDIO_INFO`.`name` AS `name`,
		`REPTILE_AUDIO_INFO`.`id` AS `id`,
		`REPTILE_AUDIO_INFO`.`content` AS `content`,
		`REPTILE_AUDIO_INFO`.`type` AS `type`,
		`REPTILE_AUDIO_INFO`.`categoryId` AS `categoryId`,
		`REPTILE_AUDIO_INFO`.`status` AS `status`,
		`REPTILE_AUDIO_INFO`.`area` AS `area`,
		`REPTILE_AUDIO_INFO`.`tags` AS `tags`,
		`REPTILE_AUDIO_INFO`.`keywords` AS `keywords`,
		`REPTILE_AUDIO_INFO`.`sourceUrl` AS `sourceUrl`,
		`REPTILE_AUDIO_INFO`.`cover` AS `cover`,
		`REPTILE_AUDIO_INFO`.`ownerUrl` AS `visitPath`,
		`REPTILE_AUDIO_INFO`.`createDate` AS `createTime`,
		`REPTILE_AUDIO_INFO`.`isHot` AS `ishot`,
			si.id AS sectionId,
			`si`.`parentId` AS `sectionParent`,
			`si`.`sectionName` AS `sectionName`
	FROM
		`REPTILE_AUDIO_INFO`
	JOIN `SECTION_CONTENT` `sc` ON `sc`.`contentId` = `REPTILE_AUDIO_INFO`.`id`
	JOIN `SECTION_INFO` `si` ON `si`.`id` = `sc`.`sectionId`

	UNION ALL
		SELECT
			`REPTILE_AUDIO_INFO`.`name` AS `name`,
			`REPTILE_AUDIO_INFO`.`id` AS `id`,
			`REPTILE_AUDIO_INFO`.`content` AS `content`,
			`REPTILE_AUDIO_INFO`.`type` AS `type`,
			`REPTILE_AUDIO_INFO`.`categoryId` AS `categoryId`,
			`REPTILE_AUDIO_INFO`.`status` AS `status`,
			`REPTILE_AUDIO_INFO`.`area` AS `area`,
			`REPTILE_AUDIO_INFO`.`tags` AS `tags`,
			`REPTILE_AUDIO_INFO`.`keywords` AS `keywords`,
			`REPTILE_AUDIO_INFO`.`sourceUrl` AS `sourceUrl`,
			`REPTILE_AUDIO_INFO`.`cover` AS `cover`,
			`REPTILE_AUDIO_INFO`.`ownerUrl` AS `visitPath`,
			`REPTILE_AUDIO_INFO`.`createDate` AS `createTime`,
			`REPTILE_AUDIO_INFO`.`isHot` AS `ishot`,
			si.id AS sectionId,
			`si`.`parentId` AS `sectionParent`,
			`si`.`sectionName` AS `sectionName`
		FROM
			`REPTILE_AUDIO_INFO`
		JOIN `SECTION_CATEGORY` `sca` ON `REPTILE_AUDIO_INFO`.`categoryId` = `sca`.`categoryId`
		JOIN `SECTION_INFO` `si` ON `si`.`id` = `sca`.`sectionId`
	
UNION ALL
	
		SELECT
			`rvi`.`name` AS `name`,
			`rvi`.`id` AS `id`,
			`rvi`.`description` AS `content`,
			`rvi`.`type` AS `type`,
			`rvi`.`category` AS `categoryId`,
			`rvi`.`status` AS `status`,
			`rvi`.`area` AS `area`,
			`rvi`.`tags` AS `tags`,
			`rvi`.`keywords` AS `keywords`,
			`rvi`.`sourceUrl` AS `sourceUrl`,
			`rvp`.`visitPath` AS `cover`,
			`rvm`.`visitPath` AS `visitPath`,
			`rvi`.`createTime` AS `createTime`,
			`rvi`.`hotLevel` AS `ishot`,
			 si.id AS sectionId,
			`si`.`parentId` AS `sectionParent`,
			`si`.`sectionName` AS `sectionName`
		FROM
			`REPTILE_VIDEO_INFO` `rvi`
		LEFT JOIN `REPTILE_VIDEO_MEDIA` `rvm` ON `rvi`.`id` = `rvm`.`videoId`
		LEFT JOIN `REPTILE_VIDEO_PICTURE` `rvp` ON `rvp`.`videoId` = `rvi`.`id`
		JOIN `SECTION_CONTENT` `sc` ON `sc`.`contentId` = `rvi`.`id`
	  JOIN `SECTION_INFO` `si` ON `si`.`id` = `sc`.`sectionId`
		WHERE
			sectionId = 1
		
		UNION ALL
			SELECT
				`rvi`.`name` AS `name`,
				`rvi`.`id` AS `id`,
				`rvi`.`description` AS `content`,
				`rvi`.`type` AS `type`,
				`rvi`.`category` AS `categoryId`,
				`rvi`.`status` AS `status`,
				`rvi`.`area` AS `area`,
				`rvi`.`tags` AS `tags`,
				`rvi`.`keywords` AS `keywords`,
				`rvi`.`sourceUrl` AS `sourceUrl`,
				`rvp`.`visitPath` AS `cover`,
				`rvm`.`visitPath` AS `visitPath`,
				`rvi`.`createTime` AS `createTime`,
				`rvi`.`hotLevel` AS `ishot`,
				si.id AS sectionId,
				`si`.`parentId` AS `sectionParent`,
				`si`.`sectionName` AS `sectionName`
			FROM
				`REPTILE_VIDEO_INFO` `rvi`
			LEFT JOIN `REPTILE_VIDEO_MEDIA` `rvm` ON `rvi`.`id` = `rvm`.`videoId`
			LEFT JOIN `REPTILE_VIDEO_PICTURE` `rvp` ON `rvp`.`videoId` = `rvi`.`id`
			JOIN `SECTION_CATEGORY` `sca` ON `rvi`.`category` = `sca`.`categoryId`
			JOIN `SECTION_INFO` `si` ON `si`.`id` = `sca`.`sectionId`
			WHERE
				sectionId = 1) a group by id,type

1.这个大家都知道的，对于where ，order by后面的字段再不影响修改插入的情况下一般都会加上索引

2.对于left join on后面跟着的字段也要加上索引，不然会进行全表的搜索

3.在书写 sql时我们如何查看是否用到索引呢？我们可以在sql语句前加一个explain

4.对于搜索引擎是innodb的时候使用count（）时尽量用在二级索引上，对于主键索引或者聚簇索引使用count会比二级索引 上使用count会慢差不多一倍

5.项目中还是尽量别使用myIsam引擎，不支持事务真心伤不起~