项目中常用的19条MySQL优化
程序员的成长之路
共 3287字,需浏览 7分钟
· 2020-10-01
阅读本文大概需要 5.6 分钟。
type列,连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别
key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式
key_len列,索引长度
rows列,扫描行数。该值是个预估值
extra列,详细说明。注意常见的不太友好的值有:Using filesort, Using temporary
二、SQL语句中IN包含的值不应过多
select id from table_name where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in
了;再或者使用连接来替换。三、SELECT语句务必指明字段名称
四、当只需要一条数据的时候,使用limit 1
五、如果排序字段没有用到索引,就尽量少排序
六、如果限制条件中其他字段没有索引,尽量少用or
七、尽量用union all代替union
union
和union all
的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all
的前提条件是两个结果集没有重复数据。八、不使用ORDER BY RAND()
select id from `table_name`
order by rand() limit 1000;
select id from `table_name` t1 join
(select rand() * (select max(id) from `table_name`) as nid) t2
on t1.id > t2.nid limit 1000;
九、区分in和exists, not in和not exists
select * from 表A where id in (select id from 表B)
select * from 表A where exists
(select * from 表B where 表B.id=表A.id)
关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的sql语句?
select colname … from A表
where a.id not in (select b.id from B表)
select colname … from A表 Left join B表 on
where a.id = b.id where b.id is null
十、使用合理的分页方式以提高分页的效率
select id,name from table_name limit 866613, 20
select id,name from table_name where id> 866612 limit 20
十一、分段查询
十二、避免在 where 子句中对字段进行 null 值判断
十三、不建议使用%前缀模糊查询
LIKE “%name”
或者LIKE “%name%”
,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”
。%name%
?select id,fnum,fdst from table_name where user_name like '%zhangsan%';
。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
select id,fnum,fdst from table_name where match(user_name) against('zhangsan' in boolean mode);
注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别
十四、避免在where子句中对字段进行表达式操作
select user_id,user_project from table_name where age*2=36;
select user_id,user_project from table_name where age=36/2;
十五、避免隐式类型转换
十六、对于联合索引来说,要遵守最左前缀法则
id
,name
,school
,可以直接用id字段,也可以id
,name
这样的顺序,但是name
,school
都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面十七、必要时可以使用force index来强制查询走某个索引
十八、注意范围查询语句
between
,>
,<
等条件时,会造成后面的索引字段失效。十九、关于JOIN优化
LEFT JOIN A表为驱动表 INNER JOIN MySQL会自动找出那个数据少的表作用驱动表 RIGHT JOIN B表为驱动表
注意:MySQL中没有full join,可以用以下方式来解决
select * from A left join B on B.name = A.name where B.name is null
union all
select * from B;
尽量使用inner join,避免left join
合理利用索引
被驱动表的索引字段作为on的限制字段。
利用小表去驱动大表
巧用STRAIGHT_JOIN
inner join
是由mysql选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by
、order by
等「Using filesort」
、「Using temporary」
时。STRAIGHT_JOIN
来强制连接顺序,在STRAIGHT_JOIN
左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN
有个前提条件是该查询是内连接,也就是inner join
。STRAIGHT_JOIN
,否则可能造成查询结果不准确。推荐阅读:
Spring 如何实现 AOP,请不要再说 cglib 了!
微信扫描二维码,关注我的公众号
朕已阅
评论
金融研究 | 使用Python测量关键审计事项的「信息含量」
Tips: 公众号推送后内容只能更改一次,且只能改20字符。如果内容出问题,或者想更新内容, 只能重复推送。为了更好的阅读体验,建议阅读本文博客版, 链接地址https://textdata.cn/blog/2023-01-13-information-content-of-critical-aud
大邓和他的Python
0
金融研究(更新) | 使用Python构建关键审计事项的「信息含量」
Tips: 公众号推送后内容只能更改一次,且只能改20字符。如果内容出问题,或者想更新内容, 只能重复推送。为了更好的阅读体验,建议阅读本文博客版, 链接地址https://textdata.cn/blog/2023-01-13-information-content-of-critical-aud
大邓和他的Python
0
盘点Lombok的几个骚操作,你绝对没用过!
👉 欢迎加入小哈的星球 ,你将获得: 专属的项目实战 / Java 学习路线 / 一对一提问 / 学习打卡 / 赠书福利全栈前后端分离博客项目 2.0 版本完结啦, 演示链接:http://116.62.199.48/ ,新项目正在酝酿中
小哈学Java
0
堪称最优秀的Docker可视化管理工具——Portainer你真的会用吗?
来源:blog.csdn.net/shark_chili3007/article/details/123366179👉 欢迎加入小哈的星球 ,你将获得: 专属的项目实战 / Java 学习路线 / 一对一提问 / 学习打卡 / 赠书福利全栈前后端分离博客项目
小哈学Java
0
Apache Paimon毕业,湖仓架构的未来发展趋势!
北京时间 2024 年 4 月 16日,开源软件基金会 Apache Software Foundation(以下简称 ASF)正式宣布 Apache Paimon 毕业成为 Apache 顶级项目(TLP, Top Level Project)。经过社区的共同努力和持续创新,Apache Paim
程序源代码
0
JS的这些新特性,你都用过么?
大厂技术 高级前端 Node进阶点击上方 程序员成长指北,关注公众号回复1,加入高级Node交流群作为一门不断演进的语言,JavaScript每年都会引入新特性。这些特性的加入,能够帮助我们编写更加简洁、高效、易于维护的代码。然而,并非所有新特性
程序员成长指北
1
【深度学习】人人都能看懂的LSTM
熟悉深度学习的朋友知道,LSTM是一种RNN模型,可以方便地处理时间序列数据,在NLP等领域有广泛应用。在看了台大李宏毅教授的深度学习视频后,特别是介绍的第一部分RNN以及LSTM,整个人醍醐灌顶。本文就是对视频的记录加上了一些个人的思考。0. 从RNN说起循环神经网络(Recurrent Neur
机器学习初学者
0
我发现 Lombok的几个骚操作,哈哈好用
大家好,我是小富~前言本文不讨论对错,只讲骚操作。有的方法看看就好,知道可以这么用,但是否应用到实际开发中,那就仁者见仁,智者见智了。一万个读者就会有一万个哈姆雷特,希望这篇文章能够给您带来一些思考。耐心看完,你一定会有所收获。@onX例如 onConstructor, oMet
程序员内点事
0