160 lines
6.1 KiB
Markdown
160 lines
6.1 KiB
Markdown
## 优化方式
|
||
数据量
|
||
- SQL 执行后返回给客户端的数据量的大小
|
||
- 数据量越大需要扫描的 I/O 次数越多，数据库服务器的 IO 瓶颈
|
||
取数据的方式
|
||
- 数据在缓存还是磁盘
|
||
- 能否通过全局索引快速寻址
|
||
- 是否结合谓词条件命中全局索引加速扫描
|
||
数据加工的方式
|
||
- 排序、子查询、聚合、关联等需要将数据取到临时表中，再对数据加工
|
||
- 对于数据量多的计算，会消耗大量 CPU 资源
|
||
- 是否选择了合适的 join 方式
|
||
## 优化思路
|
||
减少数据扫描（磁盘访问）
|
||
- 尽量在查询中加入一些可以提前过滤数据的位于条件，减少数据扫描量，对查询更加友好
|
||
- 扫描大表数据时是否可以命中索引，减少回表，避免全表扫描
|
||
返回更少数据（减少网络传输或磁盘访问）
|
||
减少交互次数（减少网络传输）
|
||
- 将数据存放在更快的地方
|
||
- 某条查询涉及大表，无法进一步优化，如果返回数据量不大但访问频率很高，可以将返回的数据放在应用端缓存或 redis 缓存中，提高存取速度
|
||
减少服务器开销（CPU 和内存）
|
||
避免大事务
|
||
利用更多资源
|
||
## 优化案例
|
||
- 数据分页优化
|
||
```sql
|
||
select * from table_name where type = ? limit ?,?;
|
||
```
|
||
偏移 id
|
||
```sql
|
||
lastId = 0 or min(id)
|
||
do {
|
||
select * from table_name where type = ? and id > {#lastId} limit ?;
|
||
lastId = max(id)
|
||
} while (isNotEmpty)
|
||
```
|
||
分段查询
|
||
优点：可并行查询，分段互不依赖
|
||
缺点：依赖连续性，数据过于分散代价较高
|
||
```sql
|
||
minId = min(id) maxId = max(id)
|
||
for(int i = minId; i <= maxId; i+=pageSize){
|
||
select * from table_name where type = ? and id between i and i+ pageSize;
|
||
}
|
||
```
|
||
- 优化 groupBy
|
||
将不需要的数据在 groupBy 之前过滤掉
|
||
```sql
|
||
select job, avg(sal) from table_name group by job having job = 'manager'
|
||
```
|
||
|
||
```sql
|
||
select job, avg(sal) from table_name where job = 'manager' group by job
|
||
```
|
||
- 范围查询
|
||
联合索引中如果某个列存在范围查询（大于小于），其右边的列是否还有意义
|
||
设计时需要结合业务需求
|
||
- 优化 order by
|
||
索引
|
||
```sql
|
||
KEY `idx_account_trade_date_time` (`account_number`,`trade_date_time`)
|
||
KEY `idx_trade_date_time` (`trade_date_time`)
|
||
KEY `idx_create_time` (`create_time`)
|
||
```
|
||
慢 SQL
|
||
```sql
|
||
select id,,,creator,modifier,create_time,update_time from satatement where (account_number = 'XXX' and create_time > '2024-04-24 00:00:00' and create_time < '2024-04-25 00:00:00' and dc_flag = 'C') order by trade_date_time desc, id desc limit 0,1000;
|
||
```
|
||
|
||
```sql
|
||
select id,,,creator,modifier,create_time,update_time from satatement where (account_number = 'XXX' and create_time > '2024-04-24 00:00:00' and create_time < '2024-04-25 00:00:00' and dc_flag = 'C') order by create_time desc, id desc limit 0,1000;
|
||
```
|
||
MySQL 使不使用索引与所查列无关，只与索引本身，where 条件，order by group by 字段有关，索引的作用为查找和排序
|
||
- 业务拆分
|
||
```sql
|
||
select * from order where status = 'S' and update_time < now-5min limit 500
|
||
```
|
||
业务数据增长，status 无法走索引，结合业务特性，对数据获取按日期拆分
|
||
```sql
|
||
date = now; minDate = now - 10 day
|
||
while(date > minDate) {
|
||
select * from order where order_date = #{date} and status = 'S' and update_time < now-5min limit 500
|
||
date = date + 1
|
||
}
|
||
```
|
||
## 数据库结构优化
|
||
- 范式优化：表的设计合理化（3 NF），消除冗余（节省空间）
|
||
- 反范式优化：适当添加冗余（减少 join）
|
||
- 拆分表：分区将数据在物理上分隔开，不同分区数据表存储于不同磁盘中，查询时只需到表分区中扫描，无需全表扫描，同时分散了磁盘 IO
|
||
## SQL 语句优化
|
||
1. 避免子查询
|
||
2. 用 in 替换 or
|
||
3. 读取适当的记录 limit M，N，不要读取多余的记录
|
||
4. 禁止不必要的 order by 排序
|
||
5. 总和查询禁止排重用 union all
|
||
6. 避免随机取数据
|
||
7. 将多次插入换成批量插入
|
||
8. 只返回必要的列，select 具体字段
|
||
9. 区分 in 和 exist
|
||
10. 优化 group by
|
||
11. 尽量使用数字型字段
|
||
12. 优化 join 语句
|
||
## 大表优化
|
||
- 分库分表
|
||
- 读写分离
|
||
- 数据定期归档
|
||
## 索引的优缺点
|
||
优点
|
||
- 提高查询语句执行效率，减少 IO 操作次数
|
||
- 创建唯一性索引，保证数据库中每一行数据的唯一性
|
||
- 加索引的列会进行排序，使用分组和排序子句可以减少时间
|
||
缺点
|
||
- 索引需要占用一定空间
|
||
- 创建索引和维护索引需要时间
|
||
- 对表中数据进行修改时，索引也要动态维护，降低了更新效率
|
||
## 索引结构
|
||
## 数据行格式
|
||
MySQL 有 4 种存储格式：
|
||
1. Compact
|
||
2. Redundant（5.0 版本前，已废弃）
|
||
3. Dynamic（MySQL 5.7 默认格式）
|
||
4. Compressed
|
||
Dynamic 下，对于处理行溢出，仅存放溢出页内存地址
|
||
## 索引设计原则
|
||
哪些情况适合建索引
|
||
- 数据有唯一性限制
|
||
- 频繁 where 条件
|
||
- 进场 group by 和 order by 字段，都有时建议联合索引
|
||
- 经常 distinct 字段
|
||
- 多表连接时的字段
|
||
- 数量最好不要超过 3 张
|
||
- 多表查询时 where 条件建索引
|
||
- 对连接字段建索引，且数据类型保持一致
|
||
- 确定数据范围情况下使用数据类型较小的字段，索引也会占空间
|
||
- 字符串建索引使用前缀
|
||
- 节省空间
|
||
- 可定位到相同前缀，然后通过主键查询完整字符串
|
||
- 区分度高的字段（散列性高）的字段
|
||
- 多个字段需要建索引，联合索引优于单值索引
|
||
不需要索引
|
||
- where 条件中用不到
|
||
- 数据量小，小于 1000 条
|
||
- 大量重复数据的列
|
||
- 避免在经常更新的表或字段
|
||
- 不建议主键使用无序的值
|
||
- 不定义冗余或重复索引
|
||
- 已有联合索引不要单独再建单索引
|
||
## 索引优化之 MRR
|
||
## 索引下推
|
||
idx (name, age)
|
||
```sql
|
||
select * from tuser where name like '张%' and age=10;
|
||
```
|
||
直接在联合索引里面过滤过滤两次完后在回表
|
||
条件
|
||
- 只能用于 range、ref、eq_ref、ref_or_null 访问方法
|
||
- 只使用 InnoDB 和 MyISAM
|
||
- 对存储引擎来说，只适用于二级索引（辅助索引）
|
||
- 引用了子查询的条件不能下推
|
||
- 引用了存储函数的条件不能下推，存储引擎无法调用存储函数 |