07MySQL 的优化处理

数据库应用优化

在对数据库进行访问时，要尽可能避免重复检索，如果能一次就完成的查询，就不要多次去访问。例如，我们对 company 数据库进行访问，查询 intern 表中编号为 1 的实习生姓名和年龄，在这之后又需要查询该实习生的部门编号，这时我们也许会分成两步去查询：

# 查询实习生姓名和年龄
SELECT name,age FROM intern WHERE id = 1;

# 查询该实习生的部门编号
SELECT dept_id FROM intern WHERE id = 1;

其实这样的查询，我们完全可以只向该数据库提交一次请求就能完成，然后把检索的结果放到变量中以备后用：

SELECT @1_name := name,@1_age := age,@1_dept := dept_id FROM intern WHERE id = 1;

SELECT @1_name;

SELECT @1_age;

我们可以在应用端增加 CACHE 层来减轻数据库的负担。它是一种 MySQL 构架设计的可扩展设计方式，这种设计方式既提高了数据库的扩展性，又提高了数据的存取能力，这使得从数据库中读取的数据更加可靠，查询效率更高。在 CACHE 中通常放着一些访问频繁但变更不多的数据。 比如，用户可以在应用端建一个二级数据库，把一些访问频率高的数据放入其中，然后让这个二级数据库与主数据库的数据同步更新。这样的方式能够减少主数据库的访问压力。

在实际应用中，我们常常采用负载均衡的方式对数据库进行优化。其原理是将固定的负载量分布到不同的服务器上，以减轻一台服务器的负担，在 MySQL数据库中，实际中常用的方法有两种，一是利用 MySQL 复制分流查询，就是让多台服务器来承担查询，主服务器和其他服务器通过复制使得数据同步。其二是采用分布式构架，对于数据量大和负载情况高时，利用分布式构架在多台服务器之间分布数据，这样可让多台服务器负载均衡，从而提高访问执行效率。

数据表的优化

在设计表时，我们尽量避免字段有 NULL 出现，我们可以使用 0 去代替 NULL。尽量使用 INT 而不是 BIGINT。使用枚举或整数代替字符串类型。尽量使用 TIMESTAMP 而非 DATETIME。

尽量不要包含多个列的排序，尽量不使用 LIKE 语句，不要在列上进行运算。

不要使用 NOT IN，可以用 NOT EXISTS 来代替 NOT IN。

优化表中的数据类型

在设计数据表的字段大小时，可能因考虑不全造成字段有冗余，这样会造成磁盘空间的浪费，那么我们在日常应用中，该如何解决这个问题呢。

用 company 数据库中的 intern 表来讲解。对于已经存在的数据表，我们先使用 PROCEDURE ANALYSE() 函数来查看数据表中的字段是否有需要优化的列。

# 查看 intern 表中是否有需要优化的列
SELECT * FROM intern PROCEDURE ANALYSE()\G;

在 Optimal_fieldtype 这一行会提出一个优化建议，这里显示为 ENUM 枚举类型是因为我们数据表中插入的数据太少了，导致判断不精准，当数据量足够大时，它能够更精准提出优化建议。现在我们看一看 1.row 中的内容，我们可以看到它的 Min_length 和 Max_length 是同样大小的，像这样的字段我们就可以进行优化，比如把 INT 型修改为 SMALLINT 型，就可以减少冗余空间了。

# 将 intern 表中的 id 字段大小修改为 SMALLINT
ALTER TABLE intern MODIFY id SMALLINT(2);

DESCRIBE intern;

对于已经存在的数据表来说，定期检查表，能够更好提高表的性能。

使用 ANALYZE TABLE <表名> 来分析和存储表的关键字分布，该分析结果可以让 SQL 生成正确的执行计划。例如，我们分析一下 company 数据库中的 employee 表的情况。

ANALYZE TABLE employee;

我们还应该定期检查表，看一张表或者多张表中是否有错误，但是 CHECK TABLE <表名> 只对 MyISAM 表有用。例如，我们创建一张名为 t 的表，然后对这张表进行检查。

CREATE TABLE t(id INT,name CHAR(15)) ENGINE=MyISAM;

CHECK TABLE t;

可以使用 OPTIMIZE TABLE <表名> 去定期优化表，这条命令对于 MyISAM 表和 InnoDB 表都是适用的。例如，我们优化一下 t 表吧。

OPTIMIZE TABLE t;

索引的优化

索引并不是越多越好。建立过多的索引会降低写入语句的效率，并且在某些情况下不能提高查询效率，因为索引越多，在查询时，首先要分析使用哪一个索引进行查询，索引过多会浪费大量的时间来分析，所以在表中创建索引时，我们要根据查询有针对性的创建，考虑在 WHERE 和 ORDER BY 命令上涉及的列建立索引，可根据 EXPLAIN 来查看是否用了索引还是全表扫描。

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

值分布很少的字段不适合做索引。字符字段只做前缀索引。字符字段最好不要做主键。尽量不用 UNIQUE，由程序保证约束。

使用短索引，可以节省查询时间，节省磁盘空间和 I/O 操作。

查询的性能优化

要合理的优化 SQL 语句，首先我们要了解各种 SQL 语句执行的状态。通过以下命令我们能够查看 SQL 语句的执行频率。

# 当前连接的统计结果
SHOW SESSION STATUS;

# 自数据库上次启动到现在的统计结果
SHOW GLOBAL STATUS;

执行以上两个命令，我们可以看到，查询列表中有很多参数，我们只需关注和查询相关的参数就可以了：

• Com-select: 执行 SELECT 操作的次数，查询一次计数会增加一次。
• Com-insert: 执行 INSERT 操作的次数，对于一次性插入多行数据，仅记为一次插入。
• Com-update: 执行 UPDATE 操作的次数。
• Com-delete: 执行 DELETE 操作的次数。

在查询结果后面加上 LIMIT 来避免向数据库请求不必要的数据。 在 MySQL 中衡量开销的三个指标：响应时间、扫描行数、返回行数，这三个指标都会记录到慢日志中，检查慢日志是找出扫描过多行数查询的好办法。

在设计查询时，我们需要考虑，是否需要将一个复杂的查询分成几个简单的查询，比如需要定期清理大量数据时，我们可以将 DELETE 语句分成多个较小的查询，这样可以尽可能小的影响 MySQL 的性能。 很多高性能的应用都会将关联查询进行分解，就是可以对每一张表进行单表查询，然后将结果关联起来。

接下来为大家介绍一些常用的 SQL 优化吧。大家还记得在之前的实验中，我们学习过 ORDER BY 排序语句吧。我们想要优化排序，首先要了解 MySQL 的排序方式。使用 SHOW INDEX FROM <表名> 可以查看某表的索引情况。现在我们来看一下 employee 表的情况吧。

SHOW INDEX FROM employee\G;

简单介绍一下表中的参数：

• Non_unique 是索引是否有重复值，若无重复值则为 0，否则为 1。
• Key_name 是索引的名字。
• Seq_in_index 是索引中的列序号，从 1 开始。
• Collation 是列存储在索引中的方式，NULL 代表无分类，A 代表升序。
• Cardinality 是索引中唯一值的数目估计值。
• Sub_part 是前置索引，如果列只是部分被编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
• Packed 是说明关键字如何被压缩，若未被压缩，则为 NULL。
• Index_type 是索引类型。
• Comment 是在其列中没有描述的索引的信息。
• Index_comment 是为索引创建时提供了一个注释属性索引的任何评论。

知道索引的排序方式后，接下来简单介绍一下 MySQL 的排序方式。例如，按照部门编号分组，从 employee 表中查询 id。

EXPLAIN SELECT id FROM employee ORDER BY dep_id\G;

从图中我们可以看到，参数 Extra 的值为 Using filesort。这是 MySQL 的其中一种排序方式，是通过返回数据进行排序的。MySQL 有两种排序方式，另一种是参数 Extra 的值为 Using idex，这种方式是通过有序索引顺序直接返回有序数据。
例如，我们给 employee 表中的 dep_id 字段添加一个索引，之后我们再执行刚才的语句，就可以发现这次是按照索引来排序了。

# 给 dep_id 添加一个名为 idx_dep 的索引
ALTER TABLE employee ADD INDEX idx_dep (dep_id);

EXPLAIN SELECT id FROM employee ORDER BY dep_id\G;

为了减少额外的排序，我们尽量使用索引排序的方式直接返回有序的数据。

子查询通过嵌套的方式可以一次性完成逻辑上需要很多步骤的查询，它可以很好地避免事务或者表死锁，但是在一些情况下使用 JOIN 连接查询来代替子查询，可以提高查询效率。
例如，我们想要查询在 employee 表中但不在 intern 表中的部门编号。

# 使用子查询的方式
EXPLAIN SELECT * FROM employee WHERE dep_id NOT IN (SELECT dept_id FROM intern)\G;

使用连接查询的方式:

EXPLAIN SELECT * FROM employee e LEFT JOIN intern i ON e.dep_id = i.dept_id\G;

从执行时间上都可以看出，连接查询此时要快一些，若表中的数据足够多时，这种差异会更加明显。

当一个表上有多个索引时，有时候优化选择器不能正确的选择一个索引，而导致查询性能变慢，这时我们可以通过手动强制干预，来提高性能。
有些时候想要去指定 MySQL 去参考某一索引，而不考虑参考其他索引列时，我们可以在查询语句后面添加 USE INDEX。例如，我们指定表 employee 表参考名为 idex_id_name 的索引列，而不考虑其他索引列。

EXPLAIN SELECT COUNT(*) FROM employee USE INDEX (idx_id_name)\G;

我们可以使用 IGNORE INDEX 来忽略某些索引。 例如，我们想要忽略 idx_id_name 这个索引。

EXPLAIN SELECT COUNT(*) FROM employee IGNORE INDEX (idx_id_name)\G;

我们还可以用 FORCE INDEX 来强制使用一个指定的索引。例如，我们想要查询在 employee 表中，工资小于 6000 的人，这时我们直接查询，会发现它的性能是很低的，我们没有指定索引，选择器会默认第一个索引，第一个索引是字段 id 列，这样去查询工资，还是会扫描整张表的。

EXPLAIN SELECT * FROM employee WHERE salary < 6000\G;

从图中我们可以看出，一共扫描了五行，在这张表中一共有五行数据。
首先我们在 employee 表中，给工资添加一个名为 idx_salary 的索引。

ALTER TABLE employee ADD INDEX idx_salary (salary);

接下来我们使用 FORCE INDEX 来看看效果吧。

EXPLAIN SELECT * FROM employee FORCE INDEX(idx_salary) WHERE salary < 6000\G;

从图中我们可以看出，只扫描了一行，这节省了很多不必要查询数据的时间。
USE INDEX 和 FORCE INDEX 都是指定选择器使用某个索引，那么两者之间有何不同呢。USE INDEX 更加礼貌一点，它是建议优化器去选择指定的这个索引，实际上优化器还是会根据自己的判断来选择。而 FORCE INDEX 是命令优化器去选择该索引，优化器自己不会再去做出判断。