1.SQL优化
1.1.SQL执行顺序
| 序号 | 顺序 |
|---|---|
| 1 | FROM |
| 2 | ON |
| 3 | JOIN |
| 4 | WHERE |
| 5 | GROUP BY |
| 6 | HAVING |
| 7 | DISTINCT |
| 8 | ORDER BY |
| 9 | LIMIT |
1.2.优化方案
| 序号 | 优化 |
|---|---|
| 1 | 尽量不使用子查询 |
| 2 | 索引的数量不宜过多, 每次新增或修改数据都会新建或修改索引, 消耗性能, 因此不要超过5个 |
| 3 | 尽量不要使用*查询所有字段, 否则索引失效 |
| 4 | 使用排序时, 尽量使用有索引字段 |
| 5 | 如果两张表没有重复数据, 则尽量使用union all, 而不是union |
| 6 | select * from 表A where id in (select id from 表B) 和 select * from 表A where id exists (select id from 表B), 如果A表数据量大于B表, 则使用in, 反之使用exists |
| 7 | 尽量使用数字型字段就不要使用字符类型,能使用小的类型就使用小的类型, varchar可以根据字符串长度自动调整, 金融类数字使用decimal |
| 8 | 避免使用部分查询, 从而导致索引失效, 如: !=、<>、not in、not exists、not like、is null、is not null、or, 使用IN替代OR |
| 9 | LIKE 查询不要用%开头, 如: LIKE ‘%fra%’, 而使用LIKE ‘fra%’ |
| 10 | 不要在where的等于号左侧使用表达式和函数运算 |
| 11 | order by的时候使用字段尽量在where条件的字段 |
| 12 | 多表查询时, 小表在前, 大表在后 |
| 13 | 尽量使用别名, 减少解析的时间 |
| 14 | 使用LIMIT M OFFSET N分页查询时, 如果数据量过大时, 可以使用where id > N LIMIT M |
| 15 | join表数量进来不要超过3个 |
| 16 | 查询时where条件尽量遵循索引最左前缀原则, 即: where查询的前几列条件字段对应索引的前几列 |
2.优化数据库结构
2.1.增加冗余字段
A和B两张表属于关联表, 其中B表的一个字段field_1, 经常被A表连表查询, 这样会减少查询速度, 尤其是在数据量较大的时候, 影响性能. 如果在这种类型字段不多情况下, 可以在A表中增加field_1字段, 避免连表查询.
缺点: 当B表中field_1字段发生改变时, 就要同时改变其他表关于这个字端的冗余字段.
2.2.批量插入
2.2.1.SQL语句的选择
SQL1:
INSERT INTO table_name values(1, 'fracong1', 30);
INSERT INTO table_name values(2, 'fracong2', 31);
INSERT INTO table_name values(3, 'fracong3', 32);
SQL2:
INSERT INTO table_name values(1, 'fracong1', 30),(2, 'fracong2', 31),(3, 'fracong3', 32);
使用SQL2的速度要快于使用SQL1的速度.
2.2.2.删除索引/外键约束
索引的目的是为了加快查询速度, 每插入一条数据, 就会创建一个索引记录. 但在在插入大量数据的时候, 创建索引会极大的降低插入记录的速度. 这个时候, 可以先删除索引, 等到完全插入好数据之后, 再新建索引.
同理, 外键约束也是如此, 每次插入新的数据,都会去校验是否有外键约束, 这样也降低了插入速度.
2.2.3.使用COPY命令
可以参考之前我写的文章: Java使用Postgresql的Copy功能大量数据保存数据库
使用COPY语句导入数据的速度比使用INSERT插入数据的数度要快.
2.2.4.关闭自动提交
在允许自动独立提交时, 每插入一条数据数据, 就多了大量的事务记录, 降低了批量插入操作的速度.可以在插入前关闭自动提交, 在完成插入后, 恢复自动提交.
2.3.增加中间表
如果两个关联表查询次数较多, 可以考虑将两个表需要经常查询的字段, 集中在一个一张中间表上, 只需要查询中间表, 避免了连表查询.
缺点: 如果两张表发生变化, 这张中间表也要及时更新. 如果数据量过大, 也不要做中间表.
2.4.拆分表
以PostgreSQL为例, 最多的字段个数为1664个, 但不是说就可以建1664个, 字段越多, 查询的速度也会越慢. 通常情况下, 一张表的字段最大数量控制在20到50之间, 尽量小于20个.
如果一张表当中有部分字段属于不经常读取的字段, 可以将这部分的字段提取出来组成一个新表, 使用关联表的方式, 通过第一张表的ID查询不经常使用的字段.从而提高了第一张表查询速度.
3.优化硬件和参数
硬件方面: 一般公司不会采取优化, 因为成本过高. 不过也可以采取以下的优化: 使用较大内存, 配置高度磁盘系统, 合理分配磁盘I/O, 配置多处理器.
参数方面:
1.max_connections 最大连接数, 在内存分配合理的情况下, 配置越高的服务器, 尽可能配置多的连接数.
2.shared_buffers 缓冲区, 通常设置为10%到25%, 如果内存很大的话, 可以配置更大的缓冲区.
3.effective_cache_size 使用的最大缓存, 设置为50%为保守设置, 75%为正常设置, 该值是一个估计值, 并不占据内存.
4.work_mem: 单个连接用户使用的内存, 在使用的时候, 通常是work_mem * max_connections, 不要超过实际使用的内存.
当work_mem设置过小时, 排序的时候, 会生成几个临时文件进行结果集排序, 极大消耗性能, 速度较慢.
而设置稍大的work_mem后, 将不会生成临时文件,而是将结果集放在内存中进行比较排序, 这样可以提高性能.
5.maintenance_work_mem:主要会影响vacuum,analyze,create index,reindex等操作, 系统默认值通常为64MB, 如果上述操作比较频繁, 可以调高一点内存.
6.其他参数: wal_buffers、checkpoint_timeout、synchronous_commit、default_statistics_target、max_wal_size、min_wal_size、wal_sync_method、checkpoint_completion_target等.
