MySQL性能优化——查询优化、结构优化

概述

当MySQL数据库的用户量和数据量非常少的时候，很难判断数据库性能的好坏。只有当长时间运行，并且有大量用户频繁操作的时候性能才能够被体现出来。

为了提高MySQL数据库的性能，需要进行一系列的优化措施。如果需要进行大量的查询操作，那么就要对查询语句进行优化，优化费时间的查询语句可以提高整体的查询速度；如果MySQL的用户很多，就需要对MySQL服务器进行优化，否则大量的用户同时连接数据库可能会导致数据库系统崩溃。

可以使用SHOW STATUS语句查询MySQL数据库的性能：

SHOW STATUS LIKE 'value'

其中value是常用的几个统计参数：

• Connections: 连接MySQL服务器的次数
• Uptime: MySQL数据库的上线时间
• Slow_queries: 慢查询的次数
• Com_insert/Com_delete/Com_update/Com_select: 增删改查的次数
• Innodb_rows_inserted/Innodb_rows_deleted/Innodb_rows_updated/Innodb_rows_seletcted: InnoDB类型表的增删改查次数

通过这些参数可以分析MySQL数据库的性能从而进行相应的优化。

优化查询

分析查询语句

分析查询语句可以了解查询语句的执行情况。可以使用EXPLAIN语句和DESCRIBE(可缩写为DESC)语句来分析，两者的使用方式是一样的，分析的结果也一样。

基本语法：

EXPLAIN SELECT 语句 ;

或：

DESCRIBE SELECT 语句 ;

比如下面用EXPLAIN语句来分析一个查询语句：

mysql> EXPLAIN SELECT * FROM p_video\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_video
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 517278
        Extra: NULL
1 row in set (0.35 sec)

下面对各个字段进行解释：

• id: 表示select语句的编号；

• select_type: 表示select语句的类型。比如：

  • SIMPLE 表示简单查询，即不包括连接查询和子查询；
  • PRIMARY 表示主查询或者是最外层的查询；
  • UNION 表示连接查询的第二个或者后面的查询语句

• table: 表示查询的表；

• type: 表示表的连接类型。比如：

  • system 表示表中只有一条记录；
  • const 表示表中有多条记录但是只从表中查询了一条记录；
  • ALL 表示对表进行了完整的扫描；
  • eq_ref 表示多表连接时，后面的表使用了UNIQUE或PRIMARY KEY；
  • ref 表示多表查询时后面的表使用了普通索引；
  • unique_subquery 表示子查询使用了UNIQUE或PRIMARY KEY；
  • index_subquery 表示子查询中使用了普通索引；
  • range 表示查询语句给出了查询范围；index表示对表中的索引进行了完整的扫描

• possible_key: 表示索引中可能使用的索引；

• key: 表示查询使用到的索引；

• key_len: 表示索引字段的长度；

• ref: 表示使用哪个列或常数与索引一起来查询记录；

• rows: 表示查询的行数；

• Extra: 表示查询过程的附件信息

索引对查询速度的影响

如果查询时不使用索引，查询语句将会查询表中的所有字段，这样一来查询的速度会很慢。如果使用索引进行查询，查询语句只查询索引字段，这样可以减少查询的记录数，从而提高查询速度。

下面对比不使用索引和使用索引的区别。现在是未使用索引查询时的情况：

mysql> EXPLAIN SELECT * FROM p_video WHERE v_title = 'CAD平面绘图示范'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_video
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 517278
        Extra: Using where
1 row in set (0.08 sec)

结果显示rows的值为517278，说明这个语句查询了517278条记录。现在在v_title字段上建立一个名为index_title的索引：

mysql> CREATE INDEX index_title ON p_video(v_title);
Query OK, 0 rows affected (13.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在v_title字段上已经有了索引，然后再分析查询语句执行情况：

mysql> EXPLAIN SELECT * FROM p_video WHERE v_title = 'CAD平面绘图示范'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_video
         type: ref
possible_keys: index_title
          key: index_title
      key_len: 402
          ref: const
         rows: 258639
        Extra: Using index condition
1 row in set (0.02 sec)

结果显示rows的值为258639，其查询速度自然比查询517278条记录要快。possible_key和key的值都是index_title，说明查询使用了index_title索引。

使用索引查询

1. 查询语句中使用LIKE关键字时，字符串不以通配符“%”开始

在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”，那么索引将不会被使用，反之将会使用。

mysql> EXPLAIN SELECT * FROM p_video WHERE v_title LIKE 'CAS平面%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_video
         type: range
possible_keys: index_title
          key: index_title
      key_len: 402
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM p_video WHERE v_title LIKE '%CAS平面'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_video
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 517278
        Extra: Using where
1 row in set (0.00 sec)

第一个查询中rows的值为1，而第二个查询中rows为517278，差距已然很明显了。

2. 查询语句中使用多列索引时，只有查询条件中使用了这些字段中第一个字段时，索引才会生效

多列索引是指在表的多个字段上创建一个索引。下面在video表的vname和vtags字段上创建一个多列索引，然后查看索引使用情况：

mysql> CREATE INDEX index_name_tags ON video(vname, vtags);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM video WHERE vname = '用windows自带画图软件画出蕾姆1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: video
         type: ref
possible_keys: index_name_tags
          key: index_name_tags
      key_len: 203
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM video WHERE vtags = '鼠绘 画图 蕾姆'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: video
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

第一个查询语句的查询条件使用了vname字段，显示rows的值为1，而且显示使用了index_name_tags索引；第二个查询语句使用了vtags字段，显示rows的值为6，而且显示使用的索引为NULL，说明查询没有使用索引。

3. 查询语句中使用OR关键字时，只要有一个条件的列不是索引，那么索引就不生效

下面验证：

mysql> EXPLAIN SELECT * FROM p_video WHERE v_title = '用windows自带画图软件画出蕾姆1' OR v_desc = 'hello'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_video
         type: ALL
possible_keys: index_title
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 517278
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM p_video WHERE v_title = '用windows自带画图软件画出蕾姆1' OR v_id = 7\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p_video
         type: index_merge
possible_keys: PRIMARY,index_title
          key: index_title,PRIMARY
      key_len: 402,8
          ref: NULL
         rows: 2
        Extra: Using union(index_title,PRIMARY); Using where
1 row in set (0.00 sec)

第一个查询中没有使用索引，因为v_desc字段上没有索引；第二个查询中使用了index_title和PRIMARY索引，因为v_name和v_id字段上都有索引。

优化子查询

子查询可以使查询语句很灵活但效率不高。MySQL会为内层查询结果建立一个临时表，然后外层查询再在临时表中查询记录，查询完毕后需要撤销这些临时表，因此子查询速度会收到一定限制。而连接查询则不需要建立临时表，查询速度比子查询要快，因此可以考虑在MySQL中用连接查询代替子查询。

优化数据库结构

将字段很多的表分解成多个表

有些表在设计时设置了很多字段，而且这个表中有些字段的使用频率很低。当这个表的数据量很大时，查询的速度就会很慢。对于这种字段特别多而且有些字段使用频率很低的表，可以将其分解成多个表。

增加中间表

有时候需要经常查询某两个表中的几个字段。如果经常进行联表查询，会降低MySQL数据库的查询速度。对于这种情况，可以建立中间表来提高查询速度。
先分析经常需要同时查询哪几个表中的哪些字段，然后将这些字段建立一个中间表，并将原来那几个表的数据插入到中间表中，之后就可以使用中间表来进行查询和统计了。

增加冗余字段

设计数据库表时尽量让表达到三范式。但是有时为了提高查询速度，可以有意识地增加冗余字段。表的规范化程度越高，表与表之间的关系就越多，查询时可能经常需要在多个表之间进行连接查询，而进行连接查询会降低查询速度。
比如，学生信息存储在学生表student中，院系信息存在department表中，通过student表中的dept_id字段与department表建立关联关系。如果需要查询一个学生所在院系的名称，必须从student表中找到dept_id，然后根据dept_id在department表中找到院系名称。如果经常要进行这个操作，连接查询会浪费很多时间。因此可以在student表中增加一个冗余字段dept_name用来存储学生所在的院系名称，这样就不用进行频繁的连接查询了。
分解表、增加中间表和增加冗余字段都浪费了一定的磁盘，但从数据库性能开看，增加少量冗余来提高查询速度是可以接受的。

优化插入记录的速度

1. 禁用索引

插入记录时，MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据，这些排序将严重影响插入记录的速度。因此，可以在插入记录之前禁用索引，待所有记录有插入完毕再开启索引。而对于新建的表，可以先不创建索引，等到所有记录导入后再创建索引，可以可以提高导入数据的速度。

禁用索引：

ALTER TABLE 表名 DISABLE KEYS;

启用索引：

ALTER TABLE 表名 ENABLE KEYS;

2. 禁用唯一性检查

插入数据时，MySQL会对插入的数据进行唯一性校验，这种校验也会降低插入记录的速度。可以在插入记录前禁用唯一性检查，等到记录插入完毕再开启。

禁用唯一性检查：

SET UNIQUE_CHECKS=0;

开启唯一性检查：

SET UNIQUE_CHECKS=1;

3. 优化insert语句

插入多条记录时，有两种INSERT语句的写法，一种是个一个INSERT语句插入多条记录：

INSERT INTO `mytable` (`id`, `name`, `sex`)
VALUES
    ('2', 'foo', 'W'),
    ('3', 'bar', 'M'),
    ('4', 'baz', 'M');

第二种是一个INSERT语句只插入一条记录，执行多个INSERT语句来插入多个记录：

INSERT INTO `mytable` (`id`, `name`, `sex`) VALUES ('2', 'foo', 'W');
INSERT INTO `mytable` (`id`, `name`, `sex`) VALUES ('3', 'bar', 'M');
INSERT INTO `mytable` (`id`, `name`, `sex`) VALUES ('4', 'baz', 'M');

第一种方式减少了与数据库之间的连接等操作，要比第二种方式要快。当插入大量数据时，建议使用第一种方式。

分析表、检查表和优化表

分析表的作用是分析关键字的分布；检查表是检查表是否存在错误；优化表是消除删除或者更新造成的空间浪费。

1. 分析表

MySQL中使用ANALYZE TABLE语句来分析表，基本语法：

ANALYZE TABLE 表名1[,表名2...];

使用ANALYZE TABLE分析表的过程中，数据库会对表加一个只读锁，在分析期间，只能读取表中的记录而不能更新和删除。ANALYZE TABLE对Innodb和MyISAM类型的表起作用。

使用ANALYZE TABLE语句分析p_video表：

mysql> ANALYZE TABLE p_video;
+-------------------------------+---------+----------+----------+
| Table                         | Op      | Msg_type | Msg_text |
+-------------------------------+---------+----------+----------+
| paint_heyyoyorang_com.p_video | analyze | status   | OK       |
+-------------------------------+---------+----------+----------+
1 row in set (4.02 sec)

• Table: 表示表的名称；
• Op: 表示执行的操作。analyze表示进行分析操作，check表示进行检查操作，optimize表示进行优化操作；
• Msg_type: 表示信息类型，通常值为状态、警告、错误和信息这四者之一；
• Msg_text: 显示信息

检查表和优化表只有也会出现这4列信息。

2. 检查表

MySQL中使用CHECK TABLE语句来检查表，该语句可以检查InnoDB和MyISAM类型的表是否存在错误，也可以检查视图是否存在错误，语法如下：

CHECK TABLE 表名1[,表名2...] [option];

其中option有5个参数且只对MyISAM类型的表有效，分别是QUICK、FAST、CHANGED、MEDIUM和EXTENDED，执行效率也依次降低。
该语句在执行过程中也会给表加上只读锁。

3. 优化表

MySQL中使用OPTIMIZE TABLE语句来优化表。该语句对InnoDB和MyISAM类型的表都有效，但是只能优化表中的VARCHAR、BLOB或TEXT类型的字段。

基本语法：

OPTIMIZE TABLE 表名1[,表名2...];

如果一个表中使用TEXT或BLOB这样的数据类型，那么更新、删除操作后，以前分配的磁盘空间不会自动回收，就会造成磁盘空间的浪费。使用OPTIMIZE TABLE语句就可以将这些磁盘碎片整理出来。OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。