使用索引
无索引时,mysql只能一页一页去搜索匹配条件。
有索引时,通过构建的B+树路径去搜索了。
索引可以大大提高查询效率。在设计数据库表时,可以根据查询频率和查询条件来选择要创建索引的列。
需要注意的是,过多的索引会影响数据的插入和更新性能,因此需要根据实际情况进行权衡。
索引:https://www.jianshu.com/p/0f593dad0257
避免全表扫描
尽可能避免全表扫描,可以使用WHERE语句来限制查询范围。
需要注意的是,使用范围查询(例如BETWEEN、>、<等)可能会导致索引失效,因此需要谨慎使用。
尽量减少JOIN操作
JOIN操作会增加查询的复杂度和开销。可以通过合理的表设计和查询优化来避免或者减少JOIN操作,譬如增加冗余字段,还有异步冗余,利用binlog+mq实现。
利用内存操作
可以将sql的一次查询分为多次查询,可以避免mysql中的大事务,还可以将多次查询的结果,在java中进行交并集操作。
减少查询返回的列数
只查询需要的列,可以减少网络IO传输和内存开销,提高查询效率。
使用批量操作
对于批量操作(例如插入、更新、删除等),可以使用批量语句来减少网络传输和数据库连接开销,提高操作效率。
避免使用子查询
子查询会增加查询的复杂度和开销,可以使用JOIN或者临时表来代替子查询。
避免使用OR条件
OR条件可能会导致索引失效,可以使用UNION或者IN语句来代替OR条件。
能用主键多用主键
主键本身就是一种聚簇索引,用非主键的索引字段还需要回表操作。
mysql多行数据操作
可以先定位,再查询和操作,譬如order by rand() 随机排序记录,可以先将主键取出来随机排序,再将获取的主键定位记录位置,获取记录。
字段最好设置为非空
Mysql中,含有空值的列很难进行查询优化,对于null可以使用0或者特殊字符。
update锁表问题
- 现象:
当Mysql预估扫描行数超过全表总数约为20% - 30%时,即便有二级索引,也会升级为全表扫描。 - 原因:
二级索引的顺序和主键顺序一般来说是不一样的,根据二级索引的顺序回表读数据时,实际上对于主键则很可能是随机扫描,因此当需要随机扫描的数量超过一定比例时(一般是20% - 30%),则优化器会决定直接改成全表扫描。 - 解决:
可以利用其他表,让他们通过主键关联,可以连表更新,通过主键更新可以锁行,这样不会锁表了。
慢sql查看执行计划
可以使用explain查看查询是否使用了索引以及扫描的行数。
主键设计
主键设计尽量有序且唯一
避免使用using filesort
- 现象:
最近使用order by对查询结果排序,发现使用和不使用的速度差别很大,使用explain查看执行计划,Extra信息发现使用using filesort。 - 原因:
where后面的查询条件和order by后面的排序字段不同。 - 解决:
可以使用联合索引。
参考
一般在写SQL时需要注意哪些问题,可以提高查询的效率? - 黎杜的回答 - 知乎
https://www.zhihu.com/question/29619558/answer/2995456130
https://blog.51cto.com/imysql/3235169?abTest=51cto
https://blog.csdn.net/qq_38263083/article/details/124706631
https://blog.csdn.net/li1325169021/article/details/121141604
网友评论