Mysql 奇技淫巧

Mysql group by 分组获取每个分组的统计数据, 默认只能统计最小分组的信息

 select name, type, count(name) nameCount ,sum(id) as idSum from test group by name, type with rollup;

mysql bit_or() 联合group by 做统计

#统计顾客每一次到店里买过的所有商品, goods_int_number 可以为长整型, bigint, 也可以为int, 运算时使用二进制或运算, 转换为二进制后每一位对应一种商品,用bit_or()对整数做或运算统计分组信息
select customer_id, bit_or(good_int_number) from order_one_time group by customer_id;


#统计顾客每一次到店里必买的商品, goods_int_number 可以为长整型, bigint, 也可以为int, 运算时使用二进制或运算, 转换为二进制后每一位对应一种商品, 用bit_and()对整数做与运算统计分组信息

select customer_id, bit_and(good_int_number) from order_one_time group by customer_id;
	

使用group by type_id order by null 提高分组查询效率, group by type_id 之后默认要排序, 可以使用 group by type_id order by null 禁止分组后默认排序

	 select * from test group by type order by null;

优化order by 排序查询

• where 条件和 order by 使用相同的索引
• order by 的顺序和索引顺序相同
• order by 的字段都是升序或者降序

	#key_par1,key_part2 是联合索引里的两个字段,
	#并且key_part1, key_part2 的顺序和联合索引顺序一致, 全部升序
    select * from test order by key_part1 , key part2;
	
	#where 条件使用了联合索引中的最左一列, order by 使用了从最左一列开始排序, 并且全部使用了降序, where 和 order by 使用相同索引
	select * from test where key_part1 = some_where order by key_part1 desc, key_part2 desc

	# select 查询字段和索引覆盖字段相同无需回表查询
	select key_part1, key_part2 from test where key_part1 = some_where and key_part = some_where order by key_part1 desc, key_part2 desc 

order by下列情形不能使用索引

	# 排序字段升序降序混用
	select * from test where key_part1 = some_where order by key_part1 asc, key_part2 desc

	# where 和 order by 使用不同的索引
	select * from test where key1 = some_where order by key2 
	
	# order by 使用了两个索引
	select * from test order by key1, key2
	

or 条件查询使用索引

	# or 条件两侧必须是两个独立的索引, or 不能使用复合索引
	select * from test where key1 = some_where or key2 = some_where;

强制使用索引

	select * from test use index(id_type) where id > 0;
	select * from test ignore index(primary) where id > 0;
	select * from test force index(id_type) where id > 0;