数据库业务积累ing

场景一：使用建立了索引字段进行内/外链接索引失效场景

最近在做系统的性能优化，那么sql的优化是必不可少的一部分，索引的使用是sql优化中非常重要的一点。所以提供了几个例子来看一下mysql执行过程中，索引的情况。
这里创建了两张表做个例子：
表一test1： 包含字段id(自增主键),test2_id(外键)
表二test2：包含字段id(自增主键)，name

ALTER TABLE `testDB`.`test1` 
ADD CONSTRAINT `fk_test2_test1` FOREIGN KEY (`test2_id`) REFERENCES `testDB`.`test2` (`id`);

接下来来分析一下几种sql语句的执行过程

1. EXPLAIN select * from test1 left join test2 on test1.test2_id = test2.id;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test1		index		fk_test2_test1	5		2	100.00	Using index
1	SIMPLE	test2		ALL	PRIMARY				2	100.00	Using where; Using join buffer (hash join)

2. EXPLAIN select * from test1 left join test2 on test1.test2_id = test2.id where test1.id =2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test1		const	PRIMARY	PRIMARY	4	const	1	100.00
1	SIMPLE	test2		const	PRIMARY	PRIMARY	4	const	1	100.00

3. EXPLAIN select * from test1 left join test2 on test1.test2_id = test2.id where test1.test2_id =2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test1		ref	fk_test2_test1	fk_test2_test1	5	const	1	100.00	Using index
1	SIMPLE	test2		const	PRIMARY	PRIMARY	4	const	1	100.00

4. EXPLAIN select * from test1 left join test2 on test1.test2_id = test2.id WHERE test2.id = 2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test2		const	PRIMARY	PRIMARY	4	const	1	100.00
1	SIMPLE	test1		ref	fk_test2_test1	fk_test2_test1	5	const	1	100.00	Using index

5. EXPLAIN select * from test1 left join test2 on test1.test2_id = test2.id and test2.id = 2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test1		index		fk_test2_test1	5		2	100.00	Using index
1	SIMPLE	test2		const	PRIMARY	PRIMARY	4	const	1	100.00	Using where

sql分析

在分析以下sql的索引生效情况时，我想先说明一下Mysql的执行顺序

FROM > ON > JOIN > WHERE > GROUP BY > WITH CUBE or WITH ROLLUP > HAVING > SELECT > DISTINCT > ORDER BY > TOP

• 分析第一条sql执行情况可以发现test1设置的外键生效，test2表的索引没有生效。test1的外键索引为什么会被触发的？
• 对比第一条跟第二条执行情况，我们添加了test1的where 主键条件筛选，此时，test1与test2表的主键索引生效。对比第一条，test2的主键索引是如何触发的？test1表的外键索引在key列为什么不显示了？是失效了吗？
• 对比第二条跟第三条执行情况，添加了test1的外键条件筛选，此时，test1外键索引生效，test2主键索引生效。对比第一条，test2的主键索引是如何触发的？
• 对比第三条跟第四条执行情况，可以发现对表test1与test2表执行顺序有变化，按照mysql的执行顺序，where条件筛选test2.id 应该在test1的索引后执行才对，为什么第四条test2优先执行了？
• 对比第二条跟第四条执行情况，结合第一条where条件上是否设置了主键筛选，是否影响key值的显示？
• 对比第四条跟第五条执行情况，第五条执行中对于test1表的type是index，第四条执行中test1表的type是ref。可以明显的看到，第四条的执行结果要优于第五条的执行结果。