少侠！如何写一手好 SQL ？
阅读本文大概需要 7 分钟。
来源 | 编码砖家
链接 | cnblogs.com/xiaoyangjia/p/11267191.html
博主（编码砖家）负责的项目主要采用阿里云数据库MySQL，最近频繁出现慢SQL告警，执行时间最长的竟然高达5分钟。导出日志后分析，主要原因竟然是没有命中索引和没有分页处理 。
其实这是非常低级的错误，我不禁后背一凉，团队成员的技术水平亟待提高啊。改造这些SQL的过程中，总结了一些经验分享给大家，如果有错误欢迎批评指正。
select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20，prePageMinId是上一页数据记录的最小ID。虽然当时查询速度还凑合，随着数据不断增长，有朝一日必定不堪重负。max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%
show variables like '%max_connections%';show variables like '%max_user_connections%';
[mysqld]max_connections = 100max_used_connections = 20
查询耗时0.5秒
充分利用但不滥用索引，须知索引也消耗磁盘和CPU。 不推荐使用数据库函数格式化数据，交给应用程序处理。 不推荐使用外键约束，用应用程序保证数据准确性。 写多读少的场景，不推荐使用唯一索引，用应用程序保证唯一性。 适当冗余字段，尝试创建中间表，用应用程序计算中间结果，用空间换时间。 不允许执行极度耗时的事务，配合应用程序拆分成更小的事务。 预估重要数据表（比如订单表）的负载和数据增长态势，提前优化。
如果长度能够满足，整型尽量使用tinyint、smallint、medium_int而非int。
如果字符串长度确定，采用char类型。
如果varchar能够满足，不采用text类型。
精度要求较高的使用decimal类型，也可以使用BIGINT，比如精确两位小数就乘以100后保存。
is not null的判断。普通索引：最基本的索引。 组合索引：多个字段上建立的索引，能够加速复合查询条件的检索。 唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值。 组合唯一索引：列值的组合必须唯一。 主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key约束。 全文索引：用于海量文本的查询，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查询精度以及扩展性不佳，更多的企业选择Elasticsearch。
分页查询很重要，如果查询数据量超过30%，MYSQL不会使用索引。 单表索引数不超过5个、单个索引字段数不超过5个。 字符串可使用前缀索引，前缀长度控制在5-8个字符。 字段唯一性太低，增加索引没有意义，如：是否删除、性别。 合理使用覆盖索引，如下所示： select loginname, nickname from member where login_name = ?
update status=0 FROMcoupon WHERE expire_date <= #{currentDate} and status=1;如果大量优惠券需要更新为不可用状态，执行这条SQL可能会堵死其他SQL，分批处理伪代码如下：int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
ListbatchIdList = 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 ++;
}
操作符<>优化
select id from orders where amount != 100;如果金额为100的订单极少，这种数据分布严重不均的情况下，有可能使用索引。鉴于这种不确定性，采用union聚合搜索结果，改写方法如下：(select id from orders where amount > 100) union all(select id from orders where amount < 100 and amount > 0)
OR优化
select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;
(select id，product_name from orders where mobile_no = '13421800407') union(select id，product_name from orders where user_id = 100);
IN优化
IN适合主表大子表小，EXIST适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了。 尝试改为join查询，举例如下：
select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';
不做列运算
select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';
select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';
避免Select all
SELECT *，它会进行全表扫描，不能有效利用索引。Like优化
SELECT column FROM table WHERE field like '%keyword%';
SELECT column FROM table WHERE field like 'keyword%';
Join优化
驱动表和被驱动表尽可能增加查询条件，满足ON的条件而少用Where，用小结果集驱动大结果集。 被驱动表的join字段上加上索引，无法建立索引的时候，设置足够的Join Buffer Size。 禁止join连接三个以上的表，尝试增加冗余字段。
Limit优化
select * from orders order by id desc limit 100000,10 耗时0.4秒select * from orders order by id desc limit 1000000,10耗时5.2秒
select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10耗时0.5秒
select id from orders where id between 1000000 and 1000010 order by id desc耗时0.3秒
其他数据库
推荐阅读：
最近面试BAT，整理一份面试资料《Java面试BATJ通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。
朕已阅 
评论