MySQL性能优化
——SQL优化的一些建议
对于WEB应用来说,数据库操作的性能会影响整个应用的性能,这一点在使用过程中体现的越来越明显。那么,性能优化只是数据库DBA的事情吗?当然不是!对于提高数据库的性能,不能只是依靠DBA,程序员现在也需要去关心。因为当程序员操作数据库时,比如查询数据使用的SQL语句,我们所使用的语句是会影响到数据库的性能。
这里针对程序员操作过程中的SQL给一些简单的建议。
尽量不使用SELECT *
从数据库表中查询所有列的内容,通常可以使用*。但是你要知道的是,从数据库里读取的数据越多,查询就会越慢。因此我们必须养成想要查询什么就返回什么的习惯。也就是说,尽量不使用*。
比如,查询雇员信息的SQL语句对比。
图1 使用SELECT *查询时间
图2 不使用SELECT * 时间对比
从上面的图示中可以看出,对于EMP表的15行数据的查询,使用 * 和直接查询所需的empno和ename,时间上是有区别的,那么数据量大了之后,查询效率是有提升的。
查询结果集中只要一行数据时建议使用LIMIT 1
某些查询时,满足条件的数据有多行,但是实际只要其中一条数据进行判断就行。比如:查询是否有10号部门的员工。对于这个查询来说,10号部门的员工只有1名和有多名效果是一样的。因此下面的两个语句:
SELECT * FROM EMP WHERE DEPTNO=10;
SELECT 1FROM EMP WHERE DEPTNO=10 LIMIT 1;
其中第二条语句比第一条语句更有效率。
原因是:查询时,查询结果集是需要游标提取数据行。如果有“LIMIT 1”的限制,MySQL引擎会在找到一条数据后停止检索,不会继续查找下一条符合记录的数据,这种情况下是可以提高性能的。
使用查询缓存
DBMS设计时,是有查询缓存的。大多数MySQL服务器都开启了查询缓存。当出现相同的查询被多次执行时,查询结果会被放到缓存中方便后面查询。这样可以通过直接查询缓存结果来提高查询的效率。
比如:多次查询结果集行数。
图3 查询缓存的使用
但是对于程序员来说,我们不经意的某些SQL语句的写法是不会使用缓存的。
注意:查询缓存不会存储有不确定结果的查询。因此,任何一个包含不确定函数(比如NOW()或CURRENT_DATE())的查询不会被缓存。同样地,CURRENT_USER()或CONNECTION_ID()这些由不同用户执行,将会产生不同的结果的查询也不会被缓存。事实上,查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表、mysql 数据库中的表或者任何一个有列级权限的表的查询。
学会使用EXPLAIN
EXPLAIN关键字可以帮助我们分析查询语句或是表结构的性能瓶颈,这样你就可以知道MySQL是如何出来SQL语句的。
图4 使用EXPLAIN
创建索引
查询过程中,某些字段总是作为检索的条件,那么建议考虑创建索引。
图5 索引的使用
从图5 可以看出,为“last_name”创建索引前后性能差了大约4倍。但是索引都能帮我们提高检索效率吗?(不一定)
使用 ENUM 代替 VARCHAR
MySQL中的ENUM 类型是非常快且紧凑的。实际上ENUM保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。
比如“性别”,你知道这些字段的取值是有限而且固定的,就只有男性或女性(M或F),那么应该使用 ENUM 代替 VARCHAR比较好。
小结:今天简单谈了一下SQL语句可以进行的性能优化,实际工作中还有很多可以进行优化的内容,希望大家可以总结实践结果,我们一起交流。书本中一般写原理性、通用性的内容,面对不同场景的经验和解决方案就需要通过实践去总结了。
领取专属 10元无门槛券
私享最新 技术干货