1、用连接查询代替子查询

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和连接查询。

子查询：

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，在运行外层的语句。

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。

但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗，这时可以改成连接查询。

优化：

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

2、join的表不宜过多

join表的数量不应该超过三个

例子：

select a.name,b.name,c.name,d.name
from a
inner join b on a.id = b.a_id
inner join c on c.id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id

如果join太多，mysql在选择索引的时候会非常复杂，会容易选错索引。

优化：

select a.name,b.name,c.name,d.name
from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id

如果实际业务场景中需要查询出另外几张表中的数据，可以在a、b、c表中冗余专门的字段，比如：在表a中冗余d_name字段，保存需要查询出的数据。

所以join表的数量要根据系统的实际情况决定，不能一概而论，尽量越少越好。

3、join时要注意

在涉及到多张表联合查询的时候，一般会使用join关键字，而join使用最多的是left join和inner join。

left join：求两个表的交集外加左表剩下的数

inner join：求两个表交集的数据

left join例子：

select o.id,o.code,u.name
from order o 
left join user u on o.user_id = u.id
where u.status = 1;

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用left join关联查询的时候，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

inner join例子：

select o.id,o.code,u.name
from order o
inner ioin user u on o.user_id = u.id
where u.status = 1;

如果两张表使用inner join关联，mysql会自动选择两张表汇总的小表，去驱动大表，所以性能上不会有太大的问题。

4、控制索引的数量

索引能够显著的提升查询sql的性能，但索引数量也并非越多越好。

单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个

优化：

（1）如果表中的数据太多，超过了5个怎么办？

如果系统的并发量不高，表中的数据量也不多，其实超过5个也可以，只要不超过太多就行。对于一些高并发的系统，一定不要超过5的限制。

（2）高并发系统如何优化索引数量？

能够建联合索引，就别建单个索引，可以删除无用的单个索引。

将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase等。在业务表汇总只需要建几个关键索引即可。

5、选择合理的字段类型

char表示固定字符串类型，该类型的字段存储空间是固定的，会浪费存储空间。

alter table order
add column code char(20) not null;

varchar表示变长字段类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

alter table order
add column code varchar(20) not null;

如果是长度固定的字段，比如用户手机号，一般都是11位，可以定义成char类型，长度是11字节。

但如果是企业名称字段，假如定义成char类型，就有问题了。

如果长度定义得太长，比如定义成了200字节，而实际企业长度只有50字节，则会浪费150字节的存储空间。

如果长度定义得太短，比如定义成了50字节，但实际企业名称有100字节，就会存储不下，会出现异常。

所以建议讲企业名称改成varchar类型，变长字段存储空间小，可以节省存储空间，而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

6、索引优化

sql优化中，有一个非常重要的内容就是：索引优化。很多时候sql语句，用索引和不用索引执行效率会差很多。所以索引优化被视作sql优化的首选。

可以使用explain命令，查看mysql的执行计划，从而查看sql有没有用索引。

explain select * from 'order' where code = '002';

如果sql语句没有用索引，排除没有建索引之外，最大的可能性就是索引失效了。

数据库SQL6个优化技巧（二）