在上一篇《MySQL数据库定义&MySQL索引使用分析》一文中,我们对MySQL数据库进行了简单介绍。MySQL作为一款运行速度快,颇受技术开发者们欢迎的数据库也存在自身的隐患。

MySQL在高速运转的工作中,随着容量和负载的不断增加,其性能也难免会出现问题。例如知名企业谷哥、雅虎等都有使用MySQL,这类公司的表几近有数十亿行,面对大量数据堆积,运维工作者需实时进行性能调整来确保MySQL能够不间停的高速运转。所以为确保MySQL能够一直处于全速运转的状态,保证性能稳定,杜绝常见问题发生是十分必要的。本文将对MySQL数据库的性能优化展开介绍。

使用索引查询

关于索引的分类在上一篇文章已有介绍,合理使用索引可以增加查询速度,这一点毋庸置疑。而索引作为一种数据位置参考的方法,本身并不包含任何数据内容,如果确保索引能够匹配上负载请求,那么在系统运行过程中就要不断创建、更新索引。另外,索引本身会对硬盘内存有占用,创建过多索引会降低插入、删除的速度,增加数据库的提取,所以在创建索引前应权衡好利弊,合理添加索引。

选用适用字段

MySQL可以很好的支持大数据量的存取,但通常来说,数据库中的字段长度越小,查询速度也就越快,因此在创建表的时候,表中字段的长度够用就可以了。如性别、年龄,鉴于数据行数据处理速度比文本型数据处理速度快,我们可以将这些文本数据设置为tinyint或int。

另外,为了提高查询效率,应尽量把字段设置为NOT NULL,暂且不说NULL本身也是占空额外空间的,含有空值的列会使查询工作变得十分复杂,且含有空值的列不被列入索引,为优化工作带来很多麻烦。如果真的需要存储NULL,那么可以选用一个特殊值或空串代替。

使用JOIN代替子查询

MySQL从4.1开始支持SQL的子查询。通过子查询这项技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件应用在另一个查询中,例如,我们要将一个班级中没有提交作业的同学筛选出来,这类任务就可以通过子查询技术来实现。使用子查询可以有效避免事物或锁死并提升查询速度,但是相较而言使用连接(JOIN)MySQL便不需要在内存中创建临时表来完成这个逻辑上的查询工作,所以连接(JOIN)更有效率一些。

尽量用UNION ALL代替UNION

UNION ALL与UNION的差异主要在于UNION应用于两个或多个SELECT语句的结果集,使用时需要将结果集合后再进行唯一性过滤操作,这一过程大量增加CPU运算,导致资源消耗与延迟。UNION ALL的速度相对较快但不支持去重功能,如果没有去重业务或结果集没有重复数据,尽量选用UNION ALL。

通常情况下,虽然要求子集的并集过滤重复数据,但鉴于脚本的特殊性,是不会有重复记录存在的,这时使用UNION ALL是毫无问题的。

尽量避免使用SELECT

使用SELECT会多出很多无用的列,这些列可能不在索引查找范围之内,而针对这些列创建索引又会增加高昂的成本,增加使用覆盖索引的可能性,消耗不必要的CPU、IO、内存。

尽量不使用ORDER BY RAND

如果要随机获取记录数,在MySQL里最简单的方法肯定是ORDER BY RAND了,但是主要是因为ORDER BY RAND会导致using filesort,使查询类型会变成全表扫描,导致索引失效,从而这种方法只能在表记录极少的情况下使用。

合理使用IN和NOT IN

IN 和 NOT IN 是比较常用的关键字,使用IN写出来的SQL比较清晰易懂。但是当SQL试图转换多个表连接时,如果转换不成功就必须先执行IN内的子查询,再查询外层的表记录。如果转换成功则可以直接将多个表连接在一起进行查询,由此可见,SQL使用IN就会增加一个转换的过程。而且含有分组统计的SQL是完全不支持转化的,所以在SQL中不建议采用IN操作符。由于 NOT IN 不能应用表的索引,所以 NOT IN 操作往往是不被推荐使用的。

另外还有两个关键字EXISTS和NOT EXISTS。IN和EXISTS的主要区别在于驱动顺序,如果是EXISTS,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

关于NOT EXISTS和NOT IN ,当我们在对表进行差集操作时,可以配合子查询使用NOT EXISTS或NOT IN ,使结果更加清晰简单。但是应用中如果查询语句使用了NOT IN 那么内外表都需要进行扫描,没有用到索引。而NOT EXISTS 的子查询依然能用到表上的索引。所以用NOT EXISTS的速度要比NOT IN快。

关于MySQL的应用还有很多需要注意的地方,本文先就MySQL优化做上述几点简单介绍,希望能对大家有所帮助。