我们在开发过程中,多多少少都会接触稍微复杂一点的业务,那么往往也关系到多表的查询,而就在此时我们也头疼多表查询带来的性能问题,在此我分享我这些年自己的优化经验。
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 * 去查询数据。
可能并不完善或者说并不正确,还请路过的大神指点一二,大家共同探讨进步。