mysql查询优化相关技术

  1. 查询优化
    1. SQL查询执行顺序
    2. Where条件相关性能问题
    3. 子查询(in/exist)相关
    4. 分组排序相关
  2. 索引优化
  3. 事务

0x01 SQL查询执行顺序

执行顺序 SQL关键字 执行内容 说明
8 SELECT 根据选择的字段,结果写入虚拟表T8
9 DISTINCT 字段 对SELECT的结果T8执行去重后, 写入虚拟表T9
1 FROM 左表 as a 对FROM中的左表与右表执行笛卡尔积,生成虚拟表T1 每步操作结果都会生成一个虚拟表, 这里用T加执行顺序号来命名
3 LEFT JOIN 右表 as b 如指定了左外连接,会将左表中存在,但右表不存在的行,添加到T2表,生成虚拟表T3; 如果FROM中包含多个表, 则会将T3与下一表重复执行步骤1~3 Join类型: Inner: 内联接, 等值连接 Outer: 外连接, 常用的Left join(以左表记录为基准), Right join(以右表记录为基准)
2 ON a.id = b.id 对T1表应用on条件筛选, 符合条件的行写入虚拟表T2 Inner jojn中on 与where条件效果相同; left join中on与where条件效果不同;
4 WHERE 条件 对T3表应用where条件过滤, 符合条件的记录写入T4表
5 GROUP BY 字段 根据group by中字段 , 对T4执行分组,生成虚拟表T5 注意:不在GROUP BY中的字段,如果出现在SELECT中,都要使用聚合类函数,不推荐下面的写法: Select a,b, sum(c) from t Group by a
6 WITH ROLLUP [CUBE] 对T5结果,按不同维度执行统计,生成虚拟表T6 GROUP BY ROLLUP(A,B,C) 结果 (A,B,C),(A,B), (A) GROUP BY CUBE(A,B,C)结果 (A,B,C),(A,B)(A,C),(B,C)(A),(B),(C)
7 HAVING 条件 对T6结果执行过滤,结果写入虚拟表T7
10 ORDER BY 字段 对DISTINCT后的结果T9, 执行排序后写入虚拟表T10 这部分可以使用字段别名, 字段运算或按字段顺序号执行排序操作
11 LIMIT 对T10取出指定行数据记录后,返回最终结果
Read more   2020/4/17 posted in  规范 SQL

编程范式

收集常用的编程的范式

spring boot+mybatis编程范式

  1. 创建mybatis和数据库映射文件 mapper/xxx.xml
  2. 创建mybatis的mapper文件和mapper/xxx.xml结合: @Mapper
  3. 创建Service文件调用mybatis的mapper文件操作数据库
2019/9/30 posted in  规范

整理现行规范中没有明确的规范

sonarqube 检测3A以上

可以使用docker搭建sonarqube检测服务器: https://github.com/SonarSource/docker-sonarqube

java

遵守阿里巴巴的p3c规范:https://github.com/alibaba/p3c

php

遵守psr规范: https://psr.phphub.org/

c#

无官方,暂行:https://gist.github.com/zhuqling/a2700703d088b8746f0c

数据库

  1. 字段长度尽量使用2的次幂,做计算机字节长度对齐,比如 varchar(16)

redis

  1. 通用的redis key不用加项目前缀
  2. 多个单词或者值之间使用:进行分割,符合阿里云Redis开发规范,同时在rdm等客户端中可以显示层级
2018/4/13 posted in  规范

58到家MySQL军规升级版

一、基础规范

  • 表存储引擎必须使用InnoDB

  • 表字符集默认使用utf8,必要时候使用utf8mb4

解读:

(1)通用,无乱码风险,汉字3字节,英文1字节

(2)utf8mb4是utf8的超集,有存储4字节例如表情符号时,使用它

Read more   2018/4/13 posted in  规范

华为JAVA编程规范

规范一:【避免在程序中使用魔鬼数字,必须用有意义的常量来标识。】

是否是魔鬼数字要基于容易阅读和便于全局替换的原则。

比如:0、1作为某种专业领域物理量枚举数值时必须定义常量,严禁出现类似NUMBER_ZERO的“魔鬼常量”。

规范二:【明确方法的功能,一个方法仅完成一个功能。】

Read more   2018/4/13 posted in  规范

完整阿里云 Redis 开发规范

本文主要介绍在使用阿里云Redis的开发规范,从下面几个方面进行说明。

  • 键值设计
  • 命令使用
  • 客户端使用
  • 相关工具

通过本文的介绍可以减少使用Redis过程带来的问题。

一、键值设计

1、key名设计

可读性和可管理性

Read more   2018/4/13 posted in  规范 Redis

阿里巴巴官方最新Redis开发规范!

本文主要介绍在使用阿里云Redis的开发规范,从下面几个方面进行说明。

  1. 键值设计
  2. 命令使用
  3. 客户端使用
  4. 相关工具

通过本文的介绍可以减少使用Redis过程带来的问题。

一、键值设计

1、key名设计

可读性和可管理性

以业务名(或数据库名)为前缀(防止key冲突),用冒号分隔,比如业务名:表名:id

ugc:video:1

简洁性

保证语义的前提下,控制key的长度,当key较多时,内存占用也不容忽视,例如:

user:{uid}:friends:messages:{mid}简化为u:{uid}:fr:m:{mid}。

不要包含特殊字符

反例:包含空格、换行、单双引号以及其他转义字符

2、value设计

拒绝bigkey

防止网卡流量、慢查询,string类型控制在10KB以内,hash、list、set、zset元素个数不要超过5000。

反例:一个包含200万个元素的list。

非字符串的bigkey,不要使用del删除,使用hscan、sscan、zscan方式渐进式删除,同时要注意防止bigkey过期时间自动删除问题(例如一个200万的zset设置1小时过期,会触发del操作,造成阻塞,而且该操作不会不出现在慢查询中(latency可查)),查找方法和删除方法 选择适合的数据类型

例如:实体类型(要合理控制和使用数据结构内存编码优化配置,例如ziplist,但也要注意节省内存和性能之间的平衡) 反例:

set user:1:name tom
set user:1:age =19
set user:1:favor football

正例:

hmset user:1 name tom age =19 favor football

控制key的生命周期

redis不是垃圾桶,建议使用expire设置过期时间(条件允许可以打散过期时间,防止集中过期),不过期的数据重点关注idletime。

二、命令使用

1、O(N)命令关注N的数量

例如hgetall、lrange、smembers、zrange、sinter等并非不能使用,但是需要明确N的值。有遍历的需求可以使用hscan、sscan、zscan代替。

2、禁用命令

禁止线上使用keys、flushall、flushdb等,通过redis的rename机制禁掉命令,或者使用scan的方式渐进式处理。

3、合理使用select

redis的多数据库较弱,使用数字进行区分,很多客户端支持较差,同时多业务用多数据库实际还是单线程处理,会有干扰。

4、使用批量操作提高效率

原生命令:例如mget、mset。

非原生命令:可以使用pipeline提高效率。

但要注意控制一次批量操作的元素个数(例如500以内,实际也和元素字节数有关)。

注意两者不同:

原生是原子操作,pipeline是非原子操作。

pipeline可以打包不同的命令,原生做不到

pipeline需要客户端和服务端同时支持。

5、不建议过多使用Redis事务功能

Redis的事务功能较弱(不支持回滚),而且集群版本(自研和官方)要求一次事务操作的key必须在一个slot上(可以使用hashtag功能解决)

6、Redis集群版本在使用Lua上有特殊要求

1、所有key都应该由 KEYS 数组来传递,redis.call/pcall 里面调用的redis命令,key的位置,必须是KEYS array, 否则直接返回error,"-ERR bad lua script for redis cluster, all the keys that the script uses should be passed using the KEYS arrayrn" 2、所有key,必须在1个slot上,否则直接返回error, "-ERR eval/evalsha command keys must in same slotrn"

7、monitor命令

必要情况下使用monitor命令时,要注意不要长时间使用。

三、客户端使用

1、避免多个应用使用一个Redis实例

不相干的业务拆分,公共数据做服务化。

2、使用连接池

可以有效控制连接,同时提高效率,标准使用方式:

执行命令如下:

Jedis jedis = null;
try {
    jedis = jedisPool.getResource();
 //具体的命令
    jedis.executeCommand()
} catch (Exception e) {
    logger.error("op key {} error: " + e.getMessage(), key, e);
} finally {
 //注意这里不是关闭连接,在JedisPool模式下,Jedis会被归还给资源池。
 if (jedis != null)
        jedis.close();
}

3、熔断功能

高并发下建议客户端添加熔断功能(例如netflix hystrix)

4、合理的加密

设置合理的密码,如有必要可以使用SSL加密访问(阿里云Redis支持)

5、淘汰策略

根据自身业务类型,选好maxmemory-policy(最大内存淘汰策略),设置好过期时间。

默认策略是volatile-lru,即超过最大内存后,在过期键中使用lru算法进行key的剔除,保证不过期数据不被删除,但是可能会出现OOM问题。

其他策略如下:

allkeys-lru:根据LRU算法删除键,不管数据有没有设置超时属性,直到腾出足够空间为止。

allkeys-random:随机删除所有键,直到腾出足够空间为止。

volatile-random:随机删除过期键,直到腾出足够空间为止。

volatile-ttl:根据键值对象的ttl属性,删除最近将要过期数据。如果没有,回退到noeviction策略。

noeviction:不会剔除任何数据,拒绝所有写入操作并返回客户端错误信息"(error) OOM command not allowed when used memory",此时Redis只响应读操作。

四、相关工具

1、数据同步

redis间数据同步可以使用:redis-port

2、big key搜索

redis大key搜索工具

3、热点key寻找

内部实现使用monitor,所以建议短时间使用facebook的redis-faina 阿里云Redis已经在内核层面解决热点key问题

五、删除bigkey

下面操作可以使用pipeline加速。redis 4.0已经支持key的异步删除,欢迎使用。

1、Hash删除: hscan + hdel

public void delBigHash(String host, int port, String password, String bigHashKey) {
 Jedis jedis = new Jedis(host, port);
 if (password != null && !"".equals(password)) {
        jedis.auth(password);
    }
 ScanParams scanParams = new ScanParams().count(100);
 String cursor = "0";
 do {
 ScanResult<Entry<String, String>> scanResult = jedis.hscan(bigHashKey, cursor, scanParams);
 List<Entry<String, String>> entryList = scanResult.getResult();
 if (entryList != null && !entryList.isEmpty()) {
 for (Entry<String, String> entry : entryList) {
                jedis.hdel(bigHashKey, entry.getKey());
            }
        }
        cursor = scanResult.getStringCursor();
    } while (!"0".equals(cursor));

 //删除bigkey
    jedis.del(bigHashKey);
}

2、List删除: ltrim

public void delBigList(String host, int port, String password, String bigListKey) {
 Jedis jedis = new Jedis(host, port);
 if (password != null && !"".equals(password)) {
        jedis.auth(password);
    }
 long llen = jedis.llen(bigListKey);
 int counter = 0;
 int left = 100;
 while (counter < llen) {
 //每次从左侧截掉100个
        jedis.ltrim(bigListKey, left, llen);
        counter += left;
    }
 //最终删除key
    jedis.del(bigListKey);
}

3、Set删除: sscan + srem

public void delBigSet(String host, int port, String password, String bigSetKey) {
 Jedis jedis = new Jedis(host, port);
 if (password != null && !"".equals(password)) {
        jedis.auth(password);
    }
 ScanParams scanParams = new ScanParams().count(100);
 String cursor = "0";
 do {
 ScanResult<String> scanResult = jedis.sscan(bigSetKey, cursor, scanParams);
 List<String> memberList = scanResult.getResult();
 if (memberList != null && !memberList.isEmpty()) {
 for (String member : memberList) {
                jedis.srem(bigSetKey, member);
            }
        }
        cursor = scanResult.getStringCursor();
    } while (!"0".equals(cursor));

 //删除bigkey
    jedis.del(bigSetKey);
}

4、SortedSet删除: zscan + zrem

public void delBigZset(String host, int port, String password, String bigZsetKey) { 
 Jedis jedis = new Jedis(host, port);
 if (password != null && !"".equals(password)) {
        jedis.auth(password);
    }
 ScanParams scanParams = new ScanParams().count(100);
 String cursor = "0";
 do {
 ScanResult<Tuple> scanResult = jedis.zscan(bigZsetKey, cursor, scanParams);
 List<Tuple> tupleList = scanResult.getResult();
 if (tupleList != null && !tupleList.isEmpty()) {
 for (Tuple tuple : tupleList) {
                jedis.zrem(bigZsetKey, tuple.getElement());
            }
        }
        cursor = scanResult.getStringCursor();
    } while (!"0".equals(cursor));

 //删除bigkey
    jedis.del(bigZsetKey);
}
2016/8/31 posted in  规范