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=3

union all

select a.orderid,a.status from t_order a where a.status=4

union all

select 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_test\G;

*************************** 1. row ***************************

Table: t_test

Create 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=utf8

1 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 all

select a.orderid,a.status from t_order a where a.status =5

还有一些sql语句优化方法和技巧，下次再给大家分享。

什么时候索引会失效

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2. 对于多列索引，不是使用的第一部分，则不会使用索引（即不符合最左前缀原则）
3. like查询是以%开头
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

此外，查看索引的使用情况

show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

handler_read_rnd_next:这个值越高，说明查询低效