MySQL 数据库性能的优化是 MySQL 数据库发展的必经之路, MySQL 数据库性能的优化也是 MySQL 数据库前进的见证。记录一些MySQL优化的一些细节
选取最适用的字段属性(出处)
MySQL 可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),
显然给数据库增加了不必要的空间,甚至使用VARCHAR
这种类型也是多余的,因为CHAR(6)
就可以很好的完成任务了。同样的,如果可以的话,我们应该使用MEDIUMINT
而不是BIGIN
来定义整型字段。
另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOT NULL
,这样在将来执行查询的时候,数据库不用去比较NULL
值。
对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM
类型。因为在 MySQL 中,ENUM
类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。
使用连接(JOIN)来代替子查询(Sub-Queries)(出处)
MySQL 从4.1开始支持 SQL 的子查询。这个技术可以使用SELECT
语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID
取出来,然后将结果传递给主查询,如下所示:
DELETE FROM customerinfo |
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN
).. 替代。例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:
SELECT * FROM customerinfoWHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) |
如果使用连接(JOIN
).. 来完成这个查询工作,速度将会快很多。尤其是当salesinfo
表中对CustomerID
建有索引的话,性能将会更好,查询如下:
SELECT * FROM customerinfoLEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID |
连接(JOIN
).. 之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
使用联合(UNION
)来代替手动创建的临时表(出处)
MySQL 从 4.0 的版本开始支持 UNION
查询,它可以把需要使用临时表的两条或更多的 SELECT
查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用 UNION
来创建查询的时候,我们只需要用 UNION
作为关键字把多个 SELECT
语句连接起来就可以了,要注意的是所有 SELECT
语句中的字段数目要想同。下面的例子就演示了一个使用 UNION
的查询。
SELECT Name, Phone FROM client |
事务(出处)
尽管我们可以使用子查询(Sub-Queries
)、连接(JOIN
)和联合(UNION
)来创建各种各样的查询,但不是所有的数据库操作都可以只用一条或少数几条 SQL 语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来。设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是:要么语句块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中数据的一致性和完整性。事物以BEGIN
关键字开始,COMMIT
关键字结束。在这之间的一条 SQL 操作失败,那么,ROLLBACK
命令就可以把数据库恢复到BEGIN
开始之前的状态。
BEGIN; |
事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰。
不用以下操作
显示或隐式的类型转换
比如
SELECT id FROM table WHERE id='1'
再如在WHERE
子句中numeric
型和int
型的列相比较就属于隐式转换使用非同类型的列进行等值查询
在WHERE子句中的
"="
左边表达式进行函数、算术运算或其他表达式运算使用前缀为
%
的LIKE
使用负向查询,如
NOT
,!=
,<>
,!>
,1<
,NOT EXISTS
,NOT IN
以及NOT LIKE
比如
NOT IN
会把空和NULL
给查出来在数据库中跑大查询
单条
SQL
语句同时更新多个表使用跨库查询
建议拆分成单表简单查询,通过程序进行关联
避免使用以下操作
避免大事务
事务要简单,整个事务的时间长度不要太长。
拆分复杂SQL
为多个小SQL
,避免大事务避免使用:触发器、函数、存储过程、视图
避免在数据库中进数学运算
MySQL
不擅长数学运算和逻辑判断避免取出大字段且无用的内容
SELECT
只获取必要的字段,尽量少使用SELECT *
避免使用大表的
JOIN
避免一次更新太多数据
比如,对数据的更新要打散后批量更新
尽量避免使用子查询,建议将子查询转换成关联查询
但由于子查询不使用索引,在关联查询也不使用索引的情况下,子查询是要优于关联查询的。因此至于是使用关联查询还是子查询则需要使用
EXPLAIN
对SQL
进行分析才是万全之策
替换使用
用
IN
代替OR
OR
的效率没有IN
的效率高IN
条件里面的数据的个数建议控制在500个以内要学会使用
EXISTS
代替IN
,EXISTS
在一些场景查询会比IN
快用
UNION ALL
代替UNION
使用
EXISTS
来判断记录是否存在,而不使用SELECT COUNT(1)
来判断记录是否存在
SQL 查找是否”存在”
现在的做法
SELECT count(*) FROM table WHERE a = 1 AND b = 2 |
然后对结果进兴判断
优化SQL
SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1 |
不再使用count,而是改用LIMIT 1,让数据库查询时遇到一条就返回,不要再继续查找还有多少条了。
业务代码中直接判断是否非空即可。