实用排坑帖：SQL语句性能优化操作策略大全

久而久之性能总会变化，避免在整个表上使用count(*)，它可能锁住整张表，使查询保持一致以便后续相似的查询可以使用查询缓存，在适当的情形下使用GROUP BY而不是DISTINCT，在WHERE、GROUP BY和ORDER BY子句中使用有索引的列，保持索引简单，不在多个索引中包含同一个列。

有时候MySQL会使用错误的索引，对于这种情况使用USE INDEX，检查使用SQL_MODE=STRICT的问题，对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR。

为了避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE，不要用UPDATE去实现，不要使用MAX，使用索引字段和ORDER BY子句，LIMIT M，N实际上可以减缓查询在某些情况下，有节制地使用，在WHERE子句中使用UNION代替子查询，在重新启动的MySQL，记得来温暖你的数据库，以确保数据在内存和查询速度快，考虑持久连接，而不是多个连接，以减少开销。

基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询，当负载增加在服务器上，使用SHOW PROCESSLIST查看慢的和有问题的查询，在开发环境中产生的镜像数据中测试的所有可疑的查询。

41、MySQL备份过程：

• 从二级复制服务器上进行备份;
• 在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致;
• 彻底停止MySQL，从数据库文件进行备份;
• 如果使用MySQL dump进行备份，请同时备份二进制日志文件 – 确保复制没有中断;
• 不要信任LVM快照，这很可能产生数据不一致，将来会给你带来麻烦;
• 为了更容易进行单表恢复，以表为单位导出数据——如果数据是与其他表隔离的。
• 当使用mysqldump时请使用–opt;
• 在备份之前检查和优化表;
• 为了更快的进行导入，在导入时临时禁用外键约束。;
• 为了更快的进行导入，在导入时临时禁用唯一性检测;
• 在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长;
• 通过自动调度脚本监控复制实例的错误和延迟;
• 定期执行备份。

42、查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为查询缓冲并不自动截取首尾空格)。

43、member用mid做标准进行分表方便查询么?一般的业务需求中基本上都是以username为查询依据，正常应当是username做hash取模来分表。

而分表的话MySQL的partition功能就是干这个的，对代码是透明的;在代码层面去实现貌似是不合理的。

44、我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。

45、在所有的存储过程和触发器的开始处设置SET NOCOUNT ON，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。

46、MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL优化方法之一。当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多。

47、EXPLAIN SELECT查询用来跟踪查看效果：

使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的。

48、当只要一行数据时使用LIMIT 1 ：

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。

在这种情况下，加上LIMIT 1可以增加性能。这样一来，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

49、选择表合适存储引擎：

• myisam：应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。
• InnoDB：事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。(InnoDB有效地降低删除和更新导致的锁定)。

对于支持事务的InnoDB类型的表来说，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行SQL前调用begin，多条SQL形成一个事物(即使autocommit打开也可以)，将大大提高性能。

50、优化表的数据类型，选择合适的数据类型：

原则：更小通常更好，简单就好，所有字段都得有默认值，尽量避免null。

例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型。(mediumint就比int更合适)

比如时间字段：datetime和timestamp，datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。

因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如：在定义邮政编码这个字段时，如果将其设置为CHAR(255)，显然给数据库增加了不必要的空间。甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。

同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!