MySQL instr和like的效率 mysql like性能

转载

mob6454cc6d1c0b 2023-09-07 22:43:25

总述

在我们使用mysql的过程中，随着我们对mysql的深入了解和使用，mysql性能优化是无法避免的话题。以下总结是基于我自己的mysql使用经验和网上比较优秀的文章。

索引优化
like模糊查询，前缀%会导致索引失效：like “%市”
负向条件查询不能使用索引，负向条件包括：!=、<>、not in、not exists、not like
避免在where子句中对字段进行null值判断
避免在 where 子句中对字段进行表达式操作或者函数式操作，如：select id from scoreTable where score/2>40，否则会导致索引失效而转向全表扫描
更新十分频繁、数据区分度不高的列不宜建立索引
合理利用覆盖索引来进行查询操作，避免回表
定期查看慢日志记录，优化代码
开启慢日志会对mysql性能产生影响，但是有助于我们查看隐藏的问题，作为开发人员，应该定期查看慢日志记录，定位慢查询的sql，进而优化代码。
善用EXPLAIN对sql进行分析
以下两个字段需要重点关注
1.type type代表查询的方式（是否用到索引），主要的type有all,index,range,ref,eq_ref,const 从左到右效率依次增强
2.extra 执行情况描述
其他
任何地方都不要使用 select * from t ，用具体的字段列表代替 * ，不要返回用不到的任何字段。

字段都给定默认值，不设为null（原因参考：）
业务表拆分分为业务基本表和业务扩展表
不使用触发器
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

分库分表（垂直，水平）
分库分表是有成本的
任何分库分表的行为都会提升业务逻辑的复杂度，数据库除了承载数据的存储和访问外，协助业务更好地实现需求和逻辑也是其重要的工作之一。分库分表会带来数据的合并、查询、更新条件的分离，以及事物的分离等多种后果，业务实现的复杂度往往会翻倍或指数级上升。所以在分表分库之前，应先升级硬盘、内存、CPU、网络、版本、读写分离、负载均衡及SQL语句优化。
垂直分表一般是将表中的列按照相关性拆分开
水平分表是按照hash或者时间拆分出来不同的表，每张表的结构是一样的
缓存
mysql本身是有缓存机制的，如果开启了查询缓存，我们在查询的时候就会先去缓存查询，但此处的缓存不是指mysql本身的缓存，因为mysql缓存不能减少客户端对mysql的请求访问，因此我们可以在其他地方做缓存，本地或者redis都是很好的选择，合理加上这些缓存，能有效减轻mysql数据库压力。
读写分离
读写分离即对mysql数据库做集群，以扩展mysql的负载，适用于读操作占主要的场景。主服务器负责写，从服务器负责读（主也可以负责读）。
主从复制
主从复制是和上面读写分离配合使用的，用在从库同步主库的数据的时候。