多表联查优化我总结有以下几点
- 优化sql语句
- 索引优化
- 反范式设计
- 业务代码优化
- 使用缓存
优化sql语句
sql性能分析
查看执行频次
---查看执行频次（select,insert,delete,update）
shwo global｜session status like ‘Com_______’；
注意：global是全局，session是当前会话，七个_慢查询日志
满查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10）的所有sql语句的日志。mysql的慢查询日志默认没有开启，需要改配置文件（/etc/my.cnf）中配置一下信息
#开启mysql慢日志查询的开关
slow_query_log=1
#设置慢日志的时间为2秒，sql语句执行时间超过2秒，就会记录在慢日志里
long_query_time=2
配置完毕后，重启mysql：systemctl restart mysqld.service 查看慢日志：/var/lib/mysql/localhost-slow.log
--查看是否开启慢日志
Show variables like 'slow_query%';
--查看慢日志
cat /var/lib/mysql/localhost-slow.log
注意：这里的文件名是不确定的，需要第一步命令查看慢文件存放位置：slow_query_log_file的值查询profile
慢查询只能查到大于预设值的语句，并且不能查看每条语句的耗时情况
--查询mysql是否支持profile查询
select @@have_profiling
--查询profile是否开启
select @@profiling
--开启profile
set global/session profiling=1;
--查看profile
show profiles;
--查看profile指定语句
show profile for query 查询id;
--查看profile指定语句附带cpu使用情况
show profile cpu for query 查询id;查看explain执行计划
可以查看是否用到索引，表的连接情况等信息
--查看explain执行计划
explain/desc 查询语句
各字段含义：
id：操作表的顺序（id相同，从上往下依次，id不同，值越大，越先执行）
select_type:表示select的类型，常见的有simple（简单表，不使用表连接或者子查询），primary（主查询，外层的查询），union（联合查询的第二个或者后面的查询），subquery（select/where之后包含了子查询）
type：性能由好到差为：null，system，const，eq_ref,ref,range,index,all。system为查询系统变量，const为查询主键或唯一索引，ref：用了辅助索引，index：用了索引但对索引全表扫描，all：全表扫描
possiable_key:可能用到的索引
key：实际用到的索引，为null则没有用到索引
key_len：索引使用的字节数，越小越好，和值有关系
rows：必须查询的行数
filtered：返回结果占需要读取的行数的百分比，100为最好
Extre：额外信息orderby优化
使用explain可以查看语句执行过程
①.Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
②.Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
注意：当orderby的多字段同时升序或降序走索引，不同走缓冲区，但是如果没有索引或者只有一个，不好意思，回表扫描，得到完整数据再放过缓冲区
优化原则
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- create index 索引名 on 表名(列 desc,列 asc);
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sortbuffer_size（默认256k）因为如果超过了这个值，会在磁盘文件中排序，效率低
groupby优化
- 分组操作时，可以通过索引来提高效率
- 分组操作时，索引的使用也是满足最左前缀法则的。
limit优化
在大数据量下分页查询，limit 2000000，10中第一个参数越大，耗时越长。此时查询需要2000010行，但返回只有10行，2000000行被丢弃了
- 一般分页查询时，通过创建 覆盖索引 能够提高性能，可以通过覆盖索引加子查询形式进行优化
- 在in的子查询中不能有limit，可以把子查询的语句当成一张表来多表查询
count优化
- 自己计数，可以通过redis维护总数
- count(*)>count(1)>count(主键)>count(字段)
索引优化
索引使用
--最左法则
联合查询中，索引最左边的必须存在，并且不能跳过中间某一列，否则后面的索引列失效
--范围查询
联合查询中，不能出现（>,<）可以使用<=,>=，否则后面的索引失效
--索引列运算
不要在索引列上运算，比如字符串操作函数，否则索引失效
--字符串不加引号
字符串类型列查询不加引号导致索引失效
--模糊匹配
模糊查询后面模糊不失效，前面模糊失效
--or连接的条件
or连接的条件，如果俩边都有索引，则有效，只要一边没有则失效，可以对没有索引的创建索引
--数据分布影响
如果mysql评估全表扫描比索引还快，不会走索引，会全表扫描
--SQL提示
SQL提示是优化数据库的重要手段，在sql语句中插入一些人为你的提示来优化，比如有多个索引，可以指定使用哪个索引
use index ：建议数据库使用，但数据库评估后如果不满意，可能不会采纳
ignore index:直接忽略
force index：强制使用
select * from 表名 use/ignore/force index(索引名) where 条件;
--覆盖索引
当select 列中所有返回的列都能在索引中找到，最优，如果找不到需要回表查找聚集索引，性能相对低一点，所以尽量避免select *，对语句优化，需要同时照顾select查询的列
--前缀索引
如果一个字段内容比较大，可以根据前缀创建前缀索引
create index 索引名字 on 表名(列名(前缀个数));
前缀个数取值问题：
当索引选择性越高越好，如唯一索引选择性为1，可以通过
select count(distinct substring(字段,1,前缀个数))/count(*) from 表 where 条件
前缀个数越少越好，选择性越高越好，综合选取
--单列索引和联合索引
如果条件是多个字段，推荐使用联合索引，因为不需要回表查询索引设计原则
1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。否则导致引擎放弃使用索引而进行全表扫描，非要使用null，可以在null上设置默认值0，确保表中num列没有null值
8. 尽量使用数字类型字段，若只含数值信息的字段，尽量不要设计为字符型，这样会降低查询和连接的性能，并会增加存储开销，这是因为引擎在处理查询和连接时，会逐个比较字符串中每一个字符。而对于数字型而言，只需要对比一次就可以了。
9. 对于子查询慎用 in，可以将子查询当成一张表连接查询，覆盖索引+连接查询多表中索引使用规则
- from和join，选择join
- left join的驱动表是左边，小表驱动大表，不过mysql优化器有优化，自动将结果集小的当驱动表
- 创建外键索引，如果中间表多个字段用到条件上，可以把多个外键建立联合索引
反范式设计
范式是减少数据冗余节省磁盘的，但是现在磁盘已经不值钱了，可以针对业务设计表，增加查询条件的冗余字段，尽量减少关联查询，以空间换时间
业务代码优化
- 业务尽量分化，将接口拆分成多个接口
- 减少无用字段的返回和查询
- 查询条件，结果集的处理不在数据库中加工，而在业务层加工
使用缓存
使用缓存来处理是一种取巧行为，能切实处理慢查询的问题，但同时也有局限性
- 当项目是单结点部署时，使用本地缓存
- 当项目是多结点部署时，使用分布式缓存
- 当然也可以使用多级缓存（不介绍）
本地缓存
这里介绍本地缓存中的Caffeine的原生使用和注解使用
导入坐标
<dependency>
<groupId>com.github.ben-manes.caffeine</groupId>
<artifactId>caffeine</artifactId>
<version>2.5.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-cache</artifactId>
<version>2.3.10.RELEASE</version>
</dependency>基础用法
@Test
public void Test2(){
//创建Cache
Cache<String,String> cache = Caffeine.newBuilder().build();
//存放key/value
cache.put("name","xuxin");
//存在取出value，不存在取出null
String name1 = cache.getIfPresent("name3");
//存在取出value，不存在走数据库业务逻辑
String name2 = cache.get("name2", key -> {
//数据库读数据
return "asd";
});
System.out.println(name1);
System.out.println(name2);
}注解使用
额外添加配置文件
@Configuration
@EnableCaching //开启缓存
public class Caffeineconfig {
/**
* 配置缓存管理器
* @return 缓存管理器
*/
@Bean("caffeineCacheManager")
public CacheManager cacheManager() {
CaffeineCacheManager cacheManager = new CaffeineCacheManager();
cacheManager.setCaffeine(Caffeine.newBuilder()
// 最后一次写入后经过固定时间过期12小时
.expireAfterWrite(12, TimeUnit.HOURS)
// 初始的缓存空间大小
.initialCapacity(100)
// 缓存的最大条数
.maximumSize(1000));
return cacheManager;
}
}在业务代码上添加注解
@Cacheable(value = "mapRendering", key = "#reqVO")
public Object mapRendering(MapRenderingReqVO2 reqVO) {
ResultBody resultBody = new ResultBody();
Map<String, Map> result = new HashMap<>();
....
return resultBody;
}注解使用说明
使用注解会自动按照入参把结果集封装进缓存，key为入参，value为结果集
分布式缓存
这里介绍redis
安装
导入坐标
<!--redis依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<!--连接池依赖-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
</dependency>
<!--序列化-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>yml添加配置
spring:
redis:
host: 127.0.0.1
port: 6379
lettuce:
pool:
max-wait: 100
min-idle: 0
max-idle: 8
max-active: 8在业务里注入客户端并使用
@Autowired
private StringRedisTemplate stringRedisTemplate;
@Test
void Test(){
User user = new User("xuxin","123");
String s = JSON.toJSONString(user);
stringRedisTemplate.opsForValue().set("user2",s);
String namexx = stringRedisTemplate.opsForValue().get("user");
User user1 = JSON.parseObject(namexx, User.class);
System.out.println(user1);
}
1908
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
