天天看点
返回

MySQL性能优化小技巧（必会）

文章目录

    • 索引管理优化
      • 整合DDL语句
      • 去除重复索引
      • 删除不用的索引
      • 监控无效的索引
    • 索引列的改进
      • 数据类型的优化
        • BIGINT 和INT的区别
        • DATETIME和TIMESTAMP的区别
        • 什么时候用ENUM
        • 什么时候使用NULL或NOT NULL
        • 隐含的变换
      • 列的类型
        • IP地址
        • MD5
    • 其他SQL优化
      • 减少SQL语句
        • 删除内容重复的SQL语句
        • 删除重复执行的SQL语句
        • 删除不必要的SQL语句
        • 缓存SQL结果
        • MySQL缓存
        • 应用程序缓存
      • 简化SQL语句
        • 改进列
        • 改进连接操作
        • 重写子查询
        • 理解视图（views）所带来的影响

除了添加索引带来的sql性能提升外，我们还可以：

  • 去除重复的索引
  • 找到没有被使用的或者无效的索引
  • 改进索引
  • 减少SQL语句
  • 简化SQL语句
  • 缓存选项

索引管理优化

索引的管理过程，即索引创建和维护的方式，能够影响到SQL语句的性能。

整合DDL语句

在将索引添加到MySQL表的过程中我们知道DDL语句是阻塞性的。

由于ALTER语句的阻塞性影响，执行ALTER语句时需要为表创建一个新的副本。

在ALTER大型表时，这个操作将消耗大量的时间和磁盘存储空间。

但是，在Mysql5.1及以后版本，innoDB插件及第三方存储引擎已经帮我们解决了这个问题，因为这时ALTER已经不再执行全表复制操作。

把多条ALTER语句整合成一条SQL语句是一种简单的优化改进。举个例子：

现在我们需要添加一个新的索引、修改一个索引以及添加新的一列，可以分别运行下面这些SQL命令：

ALTER TABLE test ADD index(username);
ALTER TABLE test DROP index name,ADD index name(last_name,first_name);
ALTER TABLE test ADD COLUMN last_visit DATE NULL;

也就是说需要执行三条SQL语句，如果我们把它合成一条：

这就是最简单的优化，只不过和减少和sql执行数，我们就能够大幅度提升管理任务的性能。

去除重复索引

重复的索引有两个主要的影响：

  1. 所有DML语句都会运行得更慢，因为需要做更多工作来保持数据和索引的一致性。
  2. 数据库的磁盘占用量将会更大，这将导致备份和恢复需要的时间增加。

我们知道innoDB下主码是不需要索引的（主码就是隐藏索引），我们做一个实验：

CREATE TABLE test(
id INT  unsigned not null,
first_name varchar(30) not null,
last_name varchar(30) not null,
joined date not null,
primary key(id),
index(id)
);

这张表中id列上定义的索引是一个重复索引，应该被移除。

怎么快速找出重复索引呢？

当一个给定索引的最左边部分被包含在其他索引中时就会产生重复索引。

重新建一张表：

CREATE TABLE test(
id INT  unsigned not null,
first_name varchar(30) not null,
last_name varchar(30) not null,
joined date not null,
primary key(id),
index name1(last_name),
index name2(last_name,first_name)
);

name1这个索引是多余的，因为此索引所在的列已经被包含在索引name2的最左边部分里面了。

我们也可以借助Maatkit的mk-duplicate-index-checker命令来检查重复索引，具体怎么用我就不在这里写了。

删除不用的索引

重复的索引是一定不会被使用到的，除此之外有些索引虽然没有重复，但也有可能不会被使用，我们要做的就是找出来并删除它。

在Google提供的MySQL补丁（下载网址：http://code.google.com/p/google-mysql-tools/wiki/Mysql5Patches）中提供了一个指令：show index_statistics ，这个指令可以监控SQL执行情况并找到没有被使用的索引。

如果你没有这个补丁，你就要先收集所有执行过的SQL语句，再使用这些SQL语句为所有SQL语句捕捉和聚合QEP，然后对每个表进行分析，才能得到未使用的索引的信息。

监控无效的索引

当定义多列索引时，一定要注意确定所指定的每一列是否真的有效。

我们可以通过分析指定表上的所有SQL语句的key_len列（在执行语句前全部加上EXPLAIN）来找到那些可能包含没有使用到的列的索引，然后更新索引（把没用到的列剔除掉）。

索引列的改进

我们可以使用特定的数据类型或者列类型来使用更小的磁盘空间（节省大量的磁盘空间）。

当磁盘空间消耗变少时我们就能减少I/O的开销并且使得更多的索引数据可以被打包装载进入可用的系统内存中。

数据类型的优化

BIGINT 和INT的区别

当一个主码被定义为BIGINT AUTO_INCREMENT数据类型时，我们知道一个INT UNSIGNED AUTO_INCREMENT数据类型能够支持的最大值是43亿。

也就是说，除非你要存的数可能大于43亿，否则请你使用INT，把BIGINT改为INT能为我们降低50%的存储消耗，即从8字节变为4字节。

DATETIME和TIMESTAMP的区别

记录一个日期或者时间的值时可以用一个纪元值吗？这关系到我们使用哪个类型，如果存储的值是纪元值（从1997年1月1日到现在的秒数），我们就用TIMESTAMP,否则我们只能使用DATETIME，你必须记住：DATETIME占8字节，而TIMESTAMP占4字节。

所以，能用TIMESTAMP就用它。

TIMESTAMP是有缺点的：默认值是0，不支持null，所以这个列必须有值！！

什么时候用ENUM

ENUM适合存储静态的代码值。比如性别：

gender1 varchar(6) not null
gender2 ENUM('male','female') not null

ENUM带来的优点：

  1. 一个ENUM列利用隐含的检查限制提供了额外的数据完整性支持。
  2. 一个ENUM列仅使用1字节来存储255个不同的值
  3. ENUM列的值更具有可读性。

如果是MYSQL5.1以前，修改ENUM值的范围执行的是ALTER DDL语句，会阻塞其他语句的执行，但5.1以后经过了优化则不会阻塞其他语句执行。

什么时候使用NULL或NOT NULL

除非能够确定这个列允许出现一个未知的值，否则最好把它声明为NOT NULL。

如果我们声明为NOT NULL，那么这个列在一个索引中时可以占用更小的空间并简化索引的处理过程（NULL!=NULL你懂的），还能给该列的数据添加额外的完整性约束检查保证所有行在此列上都有数据。

隐含的变换

当我们做表连接时我们要保证正两张表的连接字段数据类型相同，因为如果不相同是允许进行类型转换的，而类型转换会为我们带来额外的开销。比如INT SIGNED和INT UNSIGEND就会触发类型转换。

列的类型

当这些列属于某个索引时，如果我们能改进这些列的数据类型将会带来更高效的存储。

IP地址

我们存储IPv4地址时往往使用INT UNSIGNED数据类型，而这个类型占4字节。而不会SQL优化的人则会将列定义为varchar(15)，这样平均占用12字节。你会发现我们节约了2/3的列数据占用空间。

为此，我们可以通过INET_ATON()和INET_NTOA()函数进行字符串和数字值之间的转换。

MySQL性能优化小技巧（必会）
MySQL性能优化小技巧（必会）

这个优化技巧只适用于IPv4。因为IPv6是128bit（16字节），我们只能够用BINARY(16)来存储。

MD5

用char(32)来存储MD5是一个常见的优化技巧。而没学过优化的一定用的varchar(32)（PS：我就是），varchar(32)会花费额外的不必要的开销。

我们还可以进一步优化：用BINARY(16)来存储，对长度为32的MD5使用UNHEX()函数压缩，HEX()函数解压。

MySQL性能优化小技巧（必会）

其他SQL优化

对关系型数据库而言最有效的SQL优化方法就是完全删除不需要执行的SQL语句。

对于一个高度优化的应用程序而言，占总执行时间最大比重的是网络开销。

去除SQL语句能够减少应用程序的处理时间。

减少SQL语句

有一些简单的技巧可以用来减少SQL语句：

  1. 删除内容重复的SQL语句
  2. 删除重复执行的SQL语句
  3. 删除不必要的SQL语句
  4. 缓存SQL语句的返回结果

删除内容重复的SQL语句

在业务开发中我们往往会在同一个的DAO方法中发现一些重复的SQL语句，我们可以通过启动全面查询日志来分析所有执行的SQL语句并找出必要的，对于不必要的就删除掉。

删除重复执行的SQL语句

N+1问题是外层循环为每一行都生成一个SQL语句导致的。通过集合处理我们能做到一条SQL语句实现每次一块，减少重复执行。

MySQL性能优化小技巧（必会）
MySQL性能优化小技巧（必会）
MySQL性能优化小技巧（必会）

使用应用程序框架可能是造成不必要的重复SQL语句的主要原因（比如hibernate，你根本就不知道它是怎么执行查询的，因为它已经封装好了，因此hibernate的优化是很难的，除非你对它的源码十分熟悉能修改它的源码才能做到优化）。

N+1问题的复现：

MySQL性能优化小技巧（必会）

优化解决：

MySQL性能优化小技巧（必会）

删除不必要的SQL语句

当应用程序不断修改和增加功能时就可能会产生不必要的SQL语句，例如：

  • 不再需要的选择信息。
  • 仅仅在给定函数的某些路径上用到的选择信息。
  • 可以从之前的SQL语句中选择的信息。

缓存SQL结果

当普通数据的变化率相对较低时，缓存SQL结果能够为你的应用程序带来性能提升和对数据库服务器的可扩展性，比如hibernate的懒加载机制就是这么做的。

MySQL缓存

MySQL查询缓存能够为读操作频繁的环境带来性能提升，且在不需要其他应用程序开销的情况下就可以实现：

MySQL性能优化小技巧（必会）
MySQL性能优化小技巧（必会）

可以看出第二个查询减少了很多执行工作。

由于查询缓存的粗略性，对给定表的任意数据的改变都会导致所有使用那个表的缓存的SQL语句无效。

所以如果写操作比读操作频繁，最好不要开缓存。

应用程序缓存

给应用程序添加缓存（ehcache或者redis）能够大幅度减少不必要的SQL语句执行。

我们可以把特定SQL语句缓存在内存或者本地文件中，或者在应用程序对象关系映射（ORM）层引入延迟对象实例化（hibernate的懒加载机制）.

简化SQL语句

把该删的都删了之后，我们还可以简化SQL语句。

简化语句时需要考虑一下问题：

  • 查询中所有的列都是必须的吗？
  • 表的连接操作能被省去吗？
  • 在给定的函数中，连接或where条件限制对其他SQL语句是必要的吗？

改进列

简化过程的一个重要要求就是按顺序截取指定执行函数中所有SQL语句。

使用采样过程不能找到所有可能的改进点。

MySQL性能优化小技巧（必会）

改进连接操作

MySQL性能优化小技巧（必会）
MySQL性能优化小技巧（必会）

重写子查询

在某些环境下，子查询的性能比直接使用一般的表连接要慢得多：

MySQL性能优化小技巧（必会）

理解视图（views）所带来的影响

MySQL性能优化小技巧（必会）
SQL优化系列
上一篇: 小威老师脚手架解析七
下一篇: MySQL内置函数中的日期和时间函数详解