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