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()を使用する場合はできるだけ2次インデックスに使用し、プライマリ・キー・インデックスまたはクラスタ・インデックスの場合はcountを使用すると2次インデックスの場合よりも数倍遅くなります
5.プロジェクトではmyIsamエンジンを使わないようにしましょう.