MySQL使用细节

一些MySQL比较细节的问题

1、order by和limit

order by必须先于limit执行，否则会报错。

2、查询主键

select DISTINCT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name='table_name' AND COLUMN_KEY='PRI';

只要把table_name替换成你自己的表即可查询

3、修改自增起始值

alter table user_info AUTO_INCREMENT=10000;

表名改成你的表，后面的数字修改成你要的就好

4、判断null

如果想选出所有值不为null的记录：

select id from user where name is not null

需要注意的是，not null 计算速度高于null。

5、用union all代替or

select id from user where name='zhangsan' or 'lisi'

这个计算效率是很低的，而替换为

select id from user where name='zhangsan'
union all
select id from user where name='lisi'

6、尽一切可能去除left join和in

这两个方法实在太占内存太慢了，最好把相关的语句拆成原子查询，然后在service层做逻辑处理

——————————分割线 2022-06-21——————————

7、日期相关的处理

1. 尽量使用时间戳
2. 为时间戳加索引
3. 不要对时间戳做函数处理以免索引失效（例如to_days(time_stamp)这种智障操作）

8、尽量采用批处理新增

逐条新增很简单但是需要多次开闭数据库连接，不好，条件允许的情况下尽量采用批处理，也就是batchUpdate。这个操作在jdbcTemplate中很容易实现，这里记录一下怎么用ibatis实现：

第一步 在mysql的链接后面加上allowMultiQueries=true
第二步代码：

@Insert("<script>"
        + "  insert into user(name, age, gender) values"
        + "<foreach collection = 'list' item='record' separator=','>"
        + "  (#{record.name}, #{record.age}, #{record.gender})"
        + "</foreach>"
        + "</script>")
int saveData(@Param("list") List<Users> list);

9、分表的一点点想法

由于在项目初期可能功能简单，所以表结构没有那么复杂。但是等到后期功能复杂度上去了，再想分表就会牵涉到很多东西，这里提供两种解决思路：

A 人力足够

硬拆也要实现，毕竟科学合理地拆表可以极大地提高效率，也能减轻数据库压力。比如某一个字段由英文逗号分隔，记录了多种数据，那这个字段就很适合单拆表。但是这种方式在项目已经进行到中后期时有巨大的压力，你需要从数据库->dao->service改一整条链路的代码，其中可能存在巨大安全风险和人力协调问题。

B 人力不够或者上下游已经限定字段

一个折衷的解决方案是对可以分表但没有分表的字段在落库之前做分词，然后搭配使用elk的索引和redis的kv来缓和模糊匹配的巨大压力，这样既不用改表结构，也不用改之前已有的存储逻辑，相当于在查询时根据不同的字段走不同的库查询，但是这样子做的弊端是降低了前端查询时的检索复杂度，相当于用检索精度换速度。

具体采用哪种方案，需要看情况决定

10、关于索引

1. 创建索引时要关注字段长度，如果字段很长比如说varchar(255)或者text这种，要用前缀索引，比如: create index idx_name on table_name(prop(16))
2. 索引字段在查询时绝对不能做函数操作，避免索引失效。而timestamp字段作为索引时可以直接和标准时间字符串做对比，也不需要做函数操作