分享关于数据库优化经验

我们在开发过程中，多多少少都会接触稍微复杂一点的业务，那么往往也关系到多表的查询，而就在此时我们也头疼多表查询带来的性能问题，在此我分享我这些年自己的优化经验。

1、在sql语句中我们很多时候会使用子查询，如：

select a.col1,a.col2,a.col3,a.col4,(select b.col2 from table2 b where a.col1=b.col1) bcol from table a order by a.create_time;

问题：这条语句我们能完成自身的业务，但当数据量大的时候就会出现性能查的问题，因为在使用子查询的时候数据库需要创建临时表，而后执行完成还要执行销毁操作，对此对于数据库增加了很大的负担。

解决：对于多表查询，我们可以采用关联查询，性能相对提升了，但还是有很大问题，如：

select a.col1,a.col2,a.col3,a.col4,b.col2 bcol from table a,table b where a.col1=b.col1 order by a.create_time;

2、在多表查询业务中，我们是否可以和对其数据库表设计采用反范式化设计，我想很多朋友都知道，数据库适当的冗余是能解决很多关联查询的问题，从而得到更可观的性能，那么我说说我这边的想法，以空间换取时间：

在a、b、c三张表中a表主业务大表，其中有百万级数据，而b、c表为小表各存有几千或者几万条数据，当我们通过关联查询时间为10秒，20秒，我相信大家都无法接受的，那么我们是否可以通过业务展示层考虑将b、c表的部分业务数据冗余在a表中的，比如查询的列表数据为a表的数据有10个字段，b、c表中的数据各2个，那么我们可以讲b、c表中需要在查询列表展示的数据冗余在a表中，那么这就变成了单表查询，性能又再一次的提升。当然它的瓶颈就在与对修改操作时你需要维护多张表。

3、很多朋友都会说，我在sql优化时首先想到了为表创建索引，那么你是否去真实测试过你的sql语句命中了索引了吗？又是否命中了多个索引呢？

这时候mysql数据库就为我们提供了执行计划这个工具：explain

语法：explain + sql

执行后会查询出相关信息：

我们就关注(此处内容出处 https://www.cnblogs.com/lukexwang/articles/7060950.html)：

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。

key： 实际使用的索引。如果为NULL，则没有使用索引。

key_len：使用的索引的长度。

rows：MYSQL认为必须检查的用来返回请求数据的行数。

如何判断一个sql语句充分用到了索引呢？那就是key_len的长度了，下面说明下怎么去计算：

latin1一个字符占用1个字节，gbk一个字符占用2个字节，utf8一个字符占用3个字节

char，int，datetime,需要有是否为空的标记,这个标记占用1个字节(对于not null的字段来说,则不需要这1字节);

varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节。

综上，下面来看一些例子:

列类型	KEY_LEN	备注
id int	key_len = 4+1	int为4bytes,允许为NULL,加1byte
id bigint not null	key_len=8	bigint为8bytes
user char(30) utf8	key_len=30*3+1	utf8每个字符为3bytes,允许为NULL,加1byte
user varchar(30) not null utf8	key_len=30*3+2	utf8每个字符为3bytes,变长数据类型,加2bytes
user varchar(30) utf8	key_len=30*3+2+1	utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
detail text(10) utf8	key_len=30*3+2+1	TEXT截取部分,被视为动态列类型。

 备注：key_len只指示了where中用于条件过滤时被选中的索引列，是不包含order by/group by这一部分被选中的索引列的。

如果你的索引是复合索引，那么多个索引的总长度为key_len时，你的索引就充分利用到了。

附网上的索引使用口诀：

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效（范围查询放最后）；

LIKE百分写最右，覆盖索引不写星；

不等空值还有OR，索引失效要少用；

VAR引号不可丢，SQL高级也不难。

4、最左前缀原则：

我们创建一个复合索引由col1、col2、col3组成。

select * from table where col1 = 'a';  --命中1个索引（col1）

select * from table where col2 = 'b';  --没有命中索引

select * from table where col2 = 'b' and col3 = 'c';   --没命中索引

select * from table where col1 = 'a' and col3 = 'c';  --只命中了1个索引（col1 ）

从而可以得出上面的口诀：带头大哥不能死，中间兄弟不能断。

5、我们往往一个业务需要查询多种数据，如果说数据格式很相似，在此我向各位推荐使用union all，通过一条语句查询出我们所需要的所有数据，减少与数据库连接次数，从而提升性能。另在where条件中的in和or我们也可以考虑使用。

6、mysql中还存在预热的功能，大部分项目都会有热点数据的存在，也就是常常被查询的数据，数据库会将其放置到内存中为提高性能做的优化，而数据库在重启时热点数据会被清空，那么这时候就要我们手动的去预热来尽快的满足我们的需要：

获得数据库里面的库和对应的表，来进行预热（5.0以上版本）：SELECT table_schema, table_name FROM db.tables

7、数据库避免使用null，可以使用一个默认值去替换，同时在查询的时候不要在where条件语句中写任何计算的语句，尽可能将所有的数据都通过应用程序去获取计算好再传入到sql语句参数中执行。

8、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

另外再说一些相关的知识点：

where 子句中使用 != 、 <> 、or、in、not in、like很大可能会放弃索引，检索全表数据，性能极差。

充分考虑 union all、exists的使用。

查询语句中将明确需要的数据字段一一写明，不要使用 select * 去查询数据。

可能并不完善或者说并不正确，还请路过的大神指点一二，大家共同探讨进步。