对于MySQL来说,如果表里已经存在key(a,b),则key(a)为冗余索引,需要删除。
建立索引时,多考虑建立复合索引,并把区分度最高的字段放在最前面。
比如 select * from goods where goods_no = 'aaa' and state=1;
这种情况我们只需要建了一个复合索引就可以,这就相当于创建了(goods_no ,state)、(goods_no )两个索引,这就是最佳左前缀特性。
ALTER TABLE `goods` ADD INDEX `idx_goodsno_state` (`goods_no`,`state`) USING BTREE;
对串列进行MySql索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
索引不是越多越好,索引也要占据空间,同时维护索引也需要消耗时间。
where条件里等号左右字段类型必须一致,否则无法利用索引
数据类型不一致会导致索引失效
索引列不要使用函数或表达式,否则无法利用索引。如where length(name)='Admin'或where user_id+2=10023。
再比如我们会在create_tm添加索引,便于按照时间查询,这样情况下,就不要在列上进行格式化
SELECT IFNULL(count(1),0) as sfmSum FROM mg_order_new m1
where DATE_FORMAT(m1.create_tm, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
select * from _t where a=1 order by b desc, c asc
desc 和asc混用时会导致索引失效,所以跟产品经理沟通时,尽量不要存在这种排序
select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1
在索引上,避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等
KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10
范围查询还有“IN、between”
KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10
优化:可以(order_status, created_at)互换前后顺序
假如我们确定记录只有一条,那还是要习惯加上limit 1, 这样在找到一条数据后就直接返回了,不会继续扫描表;
默认情况下,MySQL对所有GROUP BY col1,col2…的字段进行排序。如果查询包括GROUP BY,想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序
//隐式排序
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mgwhere mg.state = 1 group by goods_no, serial_num//添加ORDER BY NULL禁止排序
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mg where mg.state = 1 group by goods_no, point_price ORDER BY NULL
所以在需要分组并不需要对结果进行排序的情况下,我们可以禁止隐式排序
上面的例子在MySQL 5.7及更低版本生效,GROUP BY在某些条件下隐式排序。 在MySQL 8.0中,不再发生这种情况,因此不再需要在末尾指定ORDER BY NULL来抑制隐式排序。
不过目前公司线上环境通用的还是MySQL 5.7
否则会产生间隙锁,内部扩大锁定范围,导致系统性能下降,产生死锁。
具体原理见 多线程update导致的mysql死锁问题处理方法_出世&入世的博客-CSDN博客
例:SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='hechunyang');
子查询在MySQL5.5版本里,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表t2,当外表的数据很大时,查询速度会非常慢。
在MariaDB10/MySQL5.6版本里,采用join关联方式对其进行了优化,这条SQL会自动转换为
但请注意的是:优化只针对SELECT有效,对UPDATE/DELETE子查询无效,故生产环境应避免使用子查询
多表关联left join其他表的时候,如果以其他表的字段作为查询条件都会产生临时表Using temporary; 这会使得性能受到影响
把非直接关联的表改为直接关联,可以通过改为不作为查询条件的子查询(不要在where后面使用子查询),
//优化前,出现了文件排序和临时表问题。EXPLAIN SELECT video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,member.nickname
from app_recommend_controller
left join video on app_recommend_controller.video_id= video.id
left join member on member.id= video.member_idWHERE video.display= 1 AND video.game_id= '9930'
ORDER BY video.upload_time desc LIMIT 0,20
```//优化后EXPLAIN SELECT video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,(select nickname form member where id= video.id) as nickname #这部分代替原来的内连接查询出来的昵称from app_recommend_controllerleft join video on app_recommend_controller.video_id= video.id WHERE video.display= 1 AND video.game_id= '9930'ORDER BY app_recommend_controller.video_id desc LIMIT 0,20```
通过把非直接关联表member 从join查询 改为 不作为查询条件的子查询,来优化 Using temporary
在使用order by关键字的时候,如果待排序的内容不能由所使用的索引直接完成排序的话,那么mysql有可能就要进行文件排序。
优化:
1、修改逻辑,不在mysql中使用order by而是在应用中自己进行排序。
2、使用mysql索引,将待排序的内容放到索引中,直接利用索引的排序。
因为union all不需要去重,节省数据库资源,提高性能。
因为过长的事务会导致锁数据较久,MySQL内部缓存、连接消耗过多等问题。
因为alter table会产生表锁,期间阻塞对于该表的所有写入,对于业务可能会产生极大影响。
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id<>3则可使用id>3 or id<3来代替。
低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效查询
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
select * from _t where a = 1 and b = 2 order by id desc limit 10000, 10;
对于大分页,越往后性能越差。
优化:把上一次的最后一条数据,也即上面的id传过来,然后做“id < xxx”处理