作为一名程序员,少不了要写一些sql语句,但每个人写出来的sql执行效率还是有差距的,功力深厚的人,写的sql简洁而且高效,初学者,往往只是实现功能,至于性能问题,可能无从下手。在这里我将之前在sql优化方面的一些技巧和高效写法,给大家总结了一下,不说能百分百解决sql性能问题,基本上能解决百分之八十以上的sql性能问题。
in子查询优化
原始语句
select a.depno,a.status from t_test_1 a where a.depno in (select b.depno from t_test_2 b where b.name='test')
一般功力稍微深厚一点的人,会将in改写成exists
select a.depno,a.status from t_test_1 a where exists (select 1 from t_test_2 b where a.depno=b.depno and b.name='test')
但是这种写法不是最优的写法,性能最高的写法是将in改成inner join
select a.depno,a.status from t_test_1 a inner join t_test_2 b on a.depno=b.depno where b.name='test'select a.depno,a.status from t_test_1 a inner join (select depno from t_test2 where name='test') b on a.depno=b.depno
in非子查询优化
有很多业务场景 ,例如要查询某些订单状态,就有如下sql语句
select a.orderid,a.status from t_order a where a.status in (3,4,5)
大家都知道sql语句走索引,可以用range scan方式去扫描索引,那可以将in改写成>=,<=
select a.orderid,a.status from t_order a where a.status >=3 and a.status <=5
还有一种方式,将in改写成=
select a.orderid,a.status from t_order a where a.status=3union allselect a.orderid,a.status from t_order a where a.status=4union allselect a.orderid,a.status from t_order a where a.status=5
这种写法之后,你的sql语句就会走索引,执行效率会提升很大,当然这种写法只适合某些特定的业务场景
like语句优化
有一些业务场景需要用到like去做查询条件,例如
select a.orderid,a.status from t_order a where a.orderid like '%20190108%'
这个写法,在因为第一个字符是通配符,会不走索引,导致查询效率非常差,在这里强烈建议首字符,不要使用通配符
select a.orderid,a.status from t_order a where a.orderid like '20190108%'
隐式转换
在数据库里,有时候会将字符串隐式转成数字,然后进行比较,在这里举一个例子来说明
mysql> show create table t_testG;*************************** 1. row *************************** Table: t_testCreate Table: CREATE TABLE `t_test` ( `id` char(10) NOT NULL, `status` char(1) DEFAULT NULL, `name` char(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `status` (`status`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> explain select * from t_test where status='1';+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+| 1 | SIMPLE | t_test | NULL | ref | status | status | 4 | const | 2 | 100.00 | NULL |+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> mysql> explain select * from t_test where status=1;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t_test | NULL | ALL | status | NULL | NULL | NULL | 5 | 20.00 | Using where |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 3 warnings (0.00 sec)
看到了吧,查询条件用status=1的时候,你的select语句是不走索引的,这是因为数据库将status取出来的字符串隐式转换成数字,然后再去过滤数据,我换一个种写法,可能更清晰明了
mysql> explain select * from t_test where CAST(status AS SIGNED)=1;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t_test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
这个隐式转换,相当于在status用cast做一次字符串到数字转换,因为字段上用了函数,因此是不会走索引的。大家在写sql语句的时候,一定要注意这个隐式转换,在日常运维过程中,碰到很多这种坑。
is null优化
对于写sql还在用is null的同学,我无话可说,要不就是前人给你挖的坑,要不就是你自己给自己挖的坑,如果是前者还能理解,如果是后者,真的不值得同情。在此特别强调,在建表或者添加字段时,每个字段都必须有默认值,这样你写sql语句时,就不需要用is null这种写法,只要你用=做查询条件,就有办法去走索引,提升查询效率。
or优化
在这里还是举上面的例子
select a.orderid,a.status from t_order a where a.status =4 or a.status=5
类似这种sql语句,优化还是很简单,将其改成union all方式即可
select a.orderid,a.status from t_order a where a.status =4 union allselect a.orderid,a.status from t_order a where a.status =5
还有一些sql语句优化方法和技巧,下次再给大家分享。