[toc]

varchar字段排序、求和

排序varchar字段（里面装的本来就是数字），user_age字段是varchar类型，加0即可，这是最简单的方式，还有些别的字符类型还有对应的转化方法

1
2
3
select user_name,user_age from user order by user_age+0 asc;

select sum(user_age+0) from user;

timestemp设置默认值

两种方式，一种是设置为该字段最小值（1970-01-01 08:00:01 ~ 2038-01-19 11:14:07），这里不推荐设置为0000-00-00 00:00:00，不是所有版本都兼容这个值；第二种是当前时间CURRENT_TIMESTAMP

1
2
create_at timestamp null default '1970-01-01 08:00:01' comment 'test'
create_at timestamp null default CURRENT_TIMESTAMP comment 'test'

国家、语言

国家和语言的存储通常使用代码的方式

  • 国家的域名缩写长度是2，所以用char(2)；
  • 语言的缩写是zh-CN，有的是两位，所以用char(5)

性别用0和1表示

字段允许为null

  • 单列索引不会存储null值，可能会得到不符合预期的结果 
    1
    select * from user where name != 'shenjian'

单记录查询

如果明确知道只有一条结果，那么加上limit 1可以提高效率（明确告诉它，让它主动停止游标移动）

把计算放到业务层

原理是多次调用的时候传入相同的sql，才可以利用查询缓存

1
2
3
4
5
6
select * from order where date < = CURDATE()
优化后：
$curDate = date('Y-m-d');
$res = mysql_query(
    'select * from order where date < = $curDate'
);

重复数据处理

  • 查询
  • 删除
    1
    2
    3
    # 完整语句

    #

offset问题

产考：https://blog.csdn.net/AinUser/article/details/72803175

  • 下面这个命令会扫描10010条数据丢弃前10000条数据，一般适用于做分页（有排序条件，最好给排序字段建立索引），且不要把offset的值设得过大（建议不要超过10000），不然还是会很慢

    1
    select * from table limit 10 offset 10000

  • 如果只是想按id每次拿出10条数据来做处理，那么可以用下面的命令做替换

    1
    select * from table where id > 10000 limit 10

ES自动同步对数据带来性能问题

ES索引自动同步走binlog可以解决大部分的性能问题，但是携带的关联查询还是需要走sql，需要根据场景进行优化

大表数据条件删除

商品表有5000w数据，占用了50G+的磁盘空间，对失效数据进行删除后只保留了300w数据，数据库自动释放了部分空间，还有40G的磁盘占用

进行手动释放：OPTIMIZE TABLE shopify_product ，需要注意，这个时候会加读写锁，看情况使用，另外的方式是新建一个表迁移数据，删除原表（影子备份）