MySQL语句调优整理

1、select检查

1.1、用户自定义函数

SQL语句的select后面使用了自定义函数,SQL返回多少行,那么函数就会被调用多少次,这是非常影响性能的。

#getOrderNo是用户自定义一个函数用户来根据order_sn来获取订单编号
select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction where status = 1 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';

1.2、text类型检查

如果select出现text类型的字段,就会消耗大量的网络和IO带宽,由于返回的内容过大超过max_allowed_packet设置会导致程序报错,需要评估谨慎使用。

#表request_log的中content是text类型。
select user_id, content, status, url, type from request_log where user_id = 32121;

1.3、group_concat谨慎使用

gorup_concat是一个字符串聚合函数,会影响SQL的响应时间,如果返回的值过大超过了max_allowed_packet设置会导致程序报错。

select batch_id, group_concat(name) from buffer_batch where status = 0 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';

1.4、内联子查询

在select后面有子查询的情况称为内联子查询,SQL返回多少行,子查询就需要执行过多少次,严重影响SQL性能。

select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status  from member_info m where status = 1 and create_time between '2020-09-02 10:00:00' and '2020-10-01 10:00:00';

2、from检查

2.1、表的链接方式

在MySQL中不建议使用Left Join,即使ON过滤条件列索引,一些情况也不会走索引,导致大量的数据行被扫描,SQL性能变得很差,同时要清楚ON和Where的区别。

SELECT a.member_id,a.create_time,b.active_time FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id where  b.`status` = 1
and a.create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' limit 100, 0;

3、where检查

3.1、索引列被运算

字段被运算,会导致索引失效

#device_no列上有索引,由于使用了ltrim函数导致索引失效
select id, name , phone, address, device_no from users where ltrim(device_no) = 'Hfs1212121';
#balance列有索引,由于做了运算导致索引失效
select account_no, balance from accounts where balance + 100 = 10000 and status = 1;

3.2、类型转换

int字段,传char值是可以走索引的,但是char的字段,传int值是不能走索引的

#user_id是bigint类型,传入varchar值发生了隐式类型转换,可以走索引。
select id, name , phone, address, device_no from users where user_id = '23126';
#card_no是varchar(20),传入int值是无法走索引
select id, name , phone, address, device_no from users where card_no = 2312612121;

4、group by检查

4.1、前缀索引

如果一个字符串太长,可以截取前面一部分作为索引,这就是前缀索引,group by后面的列有索引,索引可以消除排序带来的CPU开销,如果是前缀索引,是不能消除排序的。

#device_no字段类型varchar(200),创建了前缀索引。
mysql> alter table users add index idx_device_no(device_no(64));

mysql> select device_no, count(*) from users where create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' group by device_no;

5、limit检查

5.1、limit m,n要慎重

对于limit m, n分页查询,越往后面翻页即m越大的情况下SQL的耗时会越来越长,对于这种应该先取出主键id,然后通过主键id跟原表进行Join关联查询。

6、其他

6.1、NOT NULL属性

根据业务含义,尽量将字段都添加上NOT NULL DEFAULT VALUE属性,如果列值存储了大量的NULL,会影响索引的稳定性。

6.2、时间列索引

对于默认字段created_at(create_time)、updated_at(update_time)这种默认就应该创建索引,这一般来说是默认的规则。

整理自: 三太子敖丙