一、 mysql 索引实际操作总结
- 同一个字段如时间,在多个索引中存在,如一个为组合索引,一个为普通索引。索引建立先后顺序,还会影响mysql选择走那个索引;组合索引只要是等号,条件的字段的先后顺序不会影响走组合索引,如果是大于或者between就会有影响;in 不会有影响;但是not in和 <> (不等于,最好都转化成in的查询) 不会走索引:如
SELECT *
FROM `prefix_order`
WHERE ((`is_child` = 1) AND (`delflag` = 0))
AND (`status` = 2)
# AND (`goods_id` = 167880)
ORDER BY `created_at`
LIMIT 30;
# created_at 索引先建立,status+is_child+created_at,后建立,mysql(polardb 8.0)会走到created_at的普通索引上。
- 变量较少的字段,如status、is_child 等字段,一般都使用组合索引,建立普通索引没有多大效果;
- 索引字段必须和字段类型相同,除了主键索引。如普通索引是varchar类型,就不能传入int类型的字段,否则不会走索引
- mysql(polardb 8.0) update更新where条件过长,就不会走索引,会走全表扫描,除非,设置不限制长度
- 写sql主要打印出来用explain检查,是否是最优sql,防止上线后走到文件索引,直接打爆cpu。
- 要是条件没有created_at时间查询,使用时间排序,最好使用id排序,除非where条件中有带created_at时间的查询,配合order by created_at 才会快。
- 组合索引用法,重复解释第一点。
create index rds_idx_0
on prefix_clearing (uid, type, is_pay, real_money);
explain
SELECT * FROM prefix_clearing where real_money >= 11 and real_money<=2000; #不走索引,组合间断不会走索引
explain
SELECT sum(real_money) FROM prefix_clearing where real_money >= 11 and real_money<=2000; # 走索引,查询字段存在索引
explain
SELECT real_money FROM prefix_clearing where real_money >= 11 and real_money<=2000; #走索引,查询字段存在索引
explain
SELECT real_money FROM prefix_clearing ; #走索引,查询字段存在索引
explain
SELECT money FROM prefix_order_clearing ; #不走索引
explain
SELECT money,real_money FROM prefix_order_clearing ; #不走索引
explain
SELECT real_money,money FROM prefix_order_clearing ; #不走索引
#,mysql 解析sql语句的时候 ,发现查询就是real_money,并且有索引,就会走索引。查询有其他,找不到索引就不会走
二、MySQL技巧用法
- 经常会遇到,先查出数据id,在根据id拿数据,一般用 ‘IN’ 解决问题,但是如果要根据返回的id顺序,来按序输出,这时候我们可以使用’FIND_IN_SET(str1,str2)', 这个方法是返回str1在str2中文位置。
# FIND_IN_SET 比起like是更精确的查找
deep = "1, 2,12,22";
where deep like "%2%";// 返回 1,12,22
where find_in_set('2',deep); //返回 2
SELECT FIND_IN_SET('y','x,y,z'); // 返回 2 ,因为y在第二个位置,找不到返回0
//若str1和str2 任一或者全为空,则返回null
select * from or_refund_batch where id in (3541,3543,3542) order by FIND_IN_SET(id ,'3541,3543,3542');
or
select * from prefix_or_refund_batch where id in (3541,3543,3542) order by FIND_IN_SET(id ,'3541,3543,3542') desc ;
- 都等到