MySQL优化指南

前言：MySQL的优化指南针对的是数据量大的情况下，数据量不够大的话没必要纠结优化的问题。但是当数据量变大之后，很多地方都是需要优化的，不然就会出现很多问题，最显著的现象是查询和修改变慢，即响应时间变长，所以本文的优化默认是数据量较大的情况。

Part1：增删改查

插入：

提高MySQL的更新/插入效率，减少写操作的等待时间。使用批量插入数据。使用顺序主键，如自增主键。使用replace 语句代替insert语句。

删除：

truncate删除后不记录mysql日志，不可以恢复数据。如果没有外键关联，innodb执行truncate是先drop table(原始表)，再创建一个跟原始表一样空表，速度要远远快于delete逐条删除行记录。

truncate table删除表后，optimize table尤其重要，表空间可以得到释放。DELETE 操作是会锁表的，所以最好是少量分批DELETE。删除数据的速度和创建的索引数量是成反比的，所以删除数据之前先删除索引，然后删除数据，删除数据后再重新创建索引。

更新：

尽量不要修改主键字段。当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。尽量最小化对于含有UPDATE触发器的表的UPDATE操作。避免UPDATE将要复制到其他数据库的列。 避免UPDATE建有很多索引的列。 避免UPDATE在WHERE子句条件中的列。

替换：

尽量使用replace 语句代替insert/update语句。UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的字段都更新了。表中必须有唯一有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。REPLACE=DELETE+INSERT。

Part2：查找优化

查看MySQL整体状态

可以查看整个数据库的运行状态，查看系统的变量设置，查看当前的一些进程执行的状态等

开启慢查询日志

设置慢日志开启: set global slow_query_log = ON;

查看日志启动状态：show variables like “slow%”;

查询long_query_time 的值：show variables like “long%”;

explain查询分析

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句：

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

这里需要注意的是索引长度(key_len)越小越好，读取的行数(rows)越小越好，查询使用类型(type)是ref最好其次是range，查询的额外信息(Extra)是using index最好其次是where。

profiling查询分析

通过profiling命令得到更准确的SQL执行消耗系统资源的信息。可以采取针对性的优化措施。

select @@profiling;

打开profiling查询分析：set profiling = 1;

关闭数：set profiling=0

尽量不要使用select * from ，能明确多少数据就查多少个

是否扫描了过多的数据。最简单的衡量查询开销三个指标如下：响应时间；扫描的行数；返回的行数。扫描的行数越小响应时间就越短，主要是优化扫描的范围。

使用分页语句：limit start , count 或者条件 where子句

如果是有序的查询，可使用ORDER BY

开启查询缓存

这是提高性最有效的方法之一。但是如果查询中有不确定数据,例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的

衡量打开缓存是否对系统有性能提升是一个整体的概念。

通过缓存命中率判断, 缓存命中率 = 缓存命中次数 (Qcache_hits) / 查询次数 (Com_select)、

通过缓存写入率, 写入率 = 缓存写入次数 (Qcache_inserts) / 查询次数 (Qcache_inserts)

通过命中-写入率 判断, 比率 = 命中次数 (Qcache_hits) / 写入次数 (Qcache_inserts), 高性能MySQL中称之为比较能反映性能提升的指数,一般来说达到3:1则算是查询缓存有效,而最好能够达到10:1

Part3：索引优化

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。

最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)

表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

定义有外键的数据列一定要建立索引。

询中很少涉及的列，重复值比较多的列不要建立索引。

定义为text、image和bit的数据类型的列不要建立索引。

经常存取的列避免建立索引

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

in 和 not in 也要慎用，否则会导致全表扫描。

对于连续的数值，能用 between 就不要用 in 了：

like %keyword 索引失效，使用全表扫描。

like keyword% 索引有效。

like %keyword% 索引失效，也无法使用反向索引。

在 where 子句中使用参数，也会导致全表扫描。强制查询使用索引：

尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

在使用索引字段作为条件时，如果该索引是复合索引（多列索引），那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

Part4：表的设计

选择合适的数据类型，设计规范化表，消除数据冗余（以使用正确字段类型最明显）

尽量使用可以正确存储数据的最小数据类型。

尽量选择简单的类型，数字型的比较比字符型的快很多，对于状态字段，可以尝试使用 ENUM 来存放，可以极大的降低存储空间，而且拓展也很方便。

尽量避免NULL，除非必要，可以用NOT NULL+DEFAULT代替。

时间类型尽量使用TIMESTAMP类型。

浮点数存在误差问题；

对货币等对精度敏感的数据，应该用定点数表示或存储；

编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

要注意浮点数中一些特殊值的处理。

TEXT只能储存纯文本文件。

尽量不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。效率来说基本是char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替char，char和varchar可以有默认值，text不能指定默认值

固定精度的小数，也不建议使用DECIMAL

建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择。

确定不会使用负数的字段，建议添加unsigned定义。

关于text与blob我们有些看法建议：

BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.

在不必要的时候避免检索大型的BLOB或TEXT值。

把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

强烈反对在数据库中存放 LOB 类型数据

在数据冗余和处理速度之间找到合适的平衡点。合理的冗余可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

外键是最高效的一致性维护方法。但是外键是有性能问题的，不能过分追求。能不用就不用，用代码来做一致性。

数据库的表越少越好

表的字段越少越好

字段中的组合主键、组合索引越少越好

字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

对记录多的表进行拆分。（几百-上千万级别的表）

需要拆分的表分为动态表和相对静态表。动态表拆分到不同库，静态表存在于公共库。从公共库同步到分库。实现表的连接。

按照年、月、地域等来分割，或者根据时间范围、和很固定又清晰的字段值范围等，具有确定的分割标志来分割。

Part5：存储过程

存储过程减少了网络传输、处理及存储的工作量，且经过编译和优化，执行速度快，易于维护，且表的结构改变时，不影响客户端的应用程序

使用存储过程，视图，函数有助于减少应用程序中SQL复制的弊端，因为现在只在一个地方集中处理SQL。

参考资料：《高性能MySQL》