MySQL语句使用记录（一）

MySQL语句使用记录（一）

1. 删除数据库中所有的表

   生成一条处理批量SQL的语句，复制出来执行。

select concat('DROP TABLE IF EXISTS ', table_name, ';') from information_schema.tables where table_schema='test';

2. 开启optimizer_trace追踪

show variables like 'optimizer_trace%';
set session optimizer_trace="enabled=on", end_markers_in_json=on;
set session optimizer_trace_offset=-30, optimizer_trace_limit=30;
# 执行SQL语句
select username,password from user where add_time=1635041744 limit 1000;
# 查看分析结果
select * from information_schema.OPTIMIZER_TRACE limit 30;

3. 使用profile

# profile是用来分析当前会话中sql语句执行的资源消耗情况的工具
show variables like 'profiling%';

4. 联合索引的使用

（1）创建测试表，添加测试数据

create table union_index_test(
	id int PRIMARY key auto_increment,
	c1 varchar(10),
	c2 varchar(10),
	c3 varchar(10),
	c4 varchar(10),
	c5 varchar(10)
) engine=innodb default charset=utf8mb4;

insert into union_index_test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into union_index_test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into union_index_test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into union_index_test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into union_index_test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

（2）创建联合索引

create index idx_test_c1234 on union_index_test(c1, c2, c3, c4);
show index from union_index_test;

（3）分析不同情况下索引的使用情况

# 情况1：在执行常量等值查询时，改变列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化
explain select * from union_index_test where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from union_index_test where c1='a1' and c2='a3' and c3='a2' and c4='a4';
explain select * from union_index_test where c1='a1' and c2='a4' and c3='a3' and c4='a2';
explain select * from union_index_test where c1='a4' and c2='a3' and c3='a2' and c4='a1';

# 情况2：当出现范围查询时，type=range，范围右边索引列失效，范围当前位置的索引是有效的
explain select * from union_index_test where c1='a1' and c2='a2';
explain select * from union_index_test where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
# 范围右边索引列失效是有顺序的，如果c3有范围c4失效；如果c4有范围没有失效的索引列，从而会使用全部索引
explain select * from union_index_test where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
# 如果c1使用范围，索引失效，全表扫描，违背了最左前缀法则。解决方法是使用索引覆盖，在最左前缀法则中，如果最左前列的索引失效，后面的索引都失效
explain select * from union_index_test where c1>'a1' and c2='a2' and c3='a3' and c4='a4';

# 情况3：最左前缀法则，中间的索引不能断开，因此索引只用到了c1和c2
explain select * from union_index_test where c1='a1' and c2='a2' and c4='a4' order by c3; 
# 查找只用到c1和c2索引，c3索引则用于排序
explain select * from union_index_test where c1='a1' and c2='a2' order by c3;
# 查询只用到了c1和c2索引，c4进行排序，跳过了c3，出现了Using filesort
explain select * from union_index_test where c1='a1' and c2='a2' order by c4;

# 情况4：查询只用到了c1, c2和c3用于排序，无Using filesort
explain select * from union_index_test where c1='a1' and c5='a5' order by c2,c3;
# 如果c2和c3的顺序颠倒了，出现Using filesort
explain select * from union_index_test where c1='a1' and c5='a5' order by c3,c2;
# 查询增加了c5执行结果一样，因为c5并没有创建索引
explain select * from union_index_test where c1='a1' and c2='a2' order by c2,c3;
explain select * from union_index_test where c1='c1' and c2='a2' and c5='a5' order by c2,c3;
# 因为c2是常量，排序中被优化，索引没有颠倒
explain select * from union_index_test where c1='a1' and c2='a2' and c5='a5' order by c3,c2;

# 情况5：只用到了c1，c2和c3用于分组，c4与c1断开
explain select * from union_index_test where c1='a1' and c4='a4' group by c2,c3;
# 在group by分组时如果交换了c3和c2，结果出现Using temporary和Using filesort
explain select * from union_index_test where c1='a1' and c4='a4' group by c3,c2;