文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html
本人负责的项目主要采用阿里云数据库 MySQL，最近频繁出现慢 SQL 告警，执行时间最长的竟然高达 5 分钟。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html
①最大数据量
抛开数据量和并发数，谈性能都是耍流氓。MySQL 没有限制单表最大记录数，它取决于操作系统对文件大小的限制。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html
我曾经操作过超过 4 亿行数据的单表，分页查询最新的 20 条记录耗时 0.6 秒，SQL 语句大致是：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20
②最大并发数
一般要求两者比值超过 10%，计算方法如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlmax_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%
查看最大连接数与响应最大连接数：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlshow variables like '%max_connections%';
show variables like '%max_user_connections%';
在配置文件 my.cnf 中修改最大连接数：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html[mysqld]
max_connections = 100
max_used_connections = 20
③查询耗时 0.5 秒
④实施原则
- 充分利用但不滥用索引，须知索引也消耗磁盘和 CPU。
- 不推荐使用数据库函数格式化数据，交给应用程序处理。
- 不推荐使用外键约束，用应用程序保证数据准确性。
- 写多读少的场景，不推荐使用唯一索引，用应用程序保证唯一性。
- 适当冗余字段，尝试创建中间表，用应用程序计算中间结果，用空间换时间。
- 不允许执行极度耗时的事务，配合应用程序拆分成更小的事务。
- 预估重要数据表（比如订单表）的负载和数据增长态势，提前优化。
①数据类型
- 如果长度能够满足，整型尽量使用 tinyint、smallint、medium_int 而非 int。
- 如果字符串长度确定，采用 char 类型。
- 如果 varchar 能够满足，不采用 text 类型。
- 精度要求较高的使用 decimal 类型，也可以使用 BIGINT，比如精确两位小数就乘以 100 后保存。
尽量采用 timestamp 而非 datetime。
②避免空值
③Text 类型优化
索引分类如下：
- 普通索引：最基本的索引。
- 组合索引：多个字段上建立的索引，能够加速复合查询条件的检索。
- 唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值。
- 组合唯一索引：列值的组合必须唯一。
- 主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 约束。
- 全文索引：用于海量文本的查询，MySQL 5.6 之后的 InnoDB 和 MyISAM 均支持全文索引。由于查询精度以及扩展性不佳，更多的企业选择 Elasticsearch。
索引优化原则：
- 分页查询很重要，如果查询数据量超过 30%，MySQL 不会使用索引。
- 单表索引数不超过 5 个、单个索引字段数不超过 5 个。
- 字符串可使用前缀索引，前缀长度控制在 5-8 个字符。
- 字段唯一性太低，增加索引没有意义，如：是否删除、性别。
合理使用覆盖索引，如下所示：
select login_name, nick_name from member where login_name = ?
login_name, nick_name 两个字段建立组合索引，比 login_name 简单索引要更快。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html
①分批处理
update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;
如果大量优惠券需要更新为不可用状态，执行这条 SQL 可能会堵死其他 SQL，分批处理伪代码如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlint pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
if (CollectionUtils.isEmpty(batchIdList)) {
return;
}
update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
pageNo ++;
}
②操作符 <> 优化
通常 <> 操作符无法使用索引，举例如下，查询金额不为 100 元的订单：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect id from orders where amount != 100;
鉴于这种不确定性，采用 union 聚合搜索结果，改写方法如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html(select id from orders where amount > 100)
union all
(select id from orders where amount < 100 and amount > 0)
③OR 优化
在 Innodb 引擎下 OR 无法使用组合索引，比如：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect id，product_name from orders where mobile_no = '13421800407' or user_id = 100;
OR 无法命中 mobile_no + user_id 的组合索引，可采用 union，如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html(select id，product_name from orders where mobile_no = '13421800407')
union
(select id，product_name from orders where user_id = 100);
④IN 优化
尝试改为 Join 查询，举例如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect id from orders where user_id in (select id from user where level = 'VIP');
采用 Join 如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';
⑤不做列运算
通常在查询条件列运算会导致索引失效，如下所示，查询当日订单：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';
date_format 函数会导致这个查询无法使用索引，改写后：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';
⑥避免Select All
⑦Like 优化
Like 用于模糊查询，举个例子（field 已建立索引）：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlSELECT column FROM table WHERE field like '%keyword%';
这个查询未命中索引，换成下面的写法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlSELECT column FROM table WHERE field like 'keyword%';
⑧Join 优化
⑨Limit 优化
Limit 用于分页查询时越往后翻性能越差，解决的原则：缩小扫描范围，如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect * from orders order by id desc limit 100000,10
耗时0.4秒
select * from orders order by id desc limit 1000000,10
耗时5.2秒
先筛选出 ID 缩小查询范围，写法如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10
耗时0.5秒
如果查询条件仅有主键 ID，写法如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.htmlselect id from orders where id between 1000000 and 1000010 order by id desc
耗时0.3秒
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/56629.html链接：www.cnblogs.com/xiaoyangjia/p/11267191.html