多表联查优化

2023-10-27

多表联查优化我总结有以下几点

优化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

安装

进入官网下载安装：https://redis.io/

导入坐标

        <!--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);
    }

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

Java