<深入浅出MySQL>笔记之优化

发表于 |

本文主要是 <深入浅出 MySQL> 一书中对于 MySQL 优化相关内容的读书笔记，另外参考了一些其他资料得出的总结性内容。

引擎选择

查看本 MySQL server 支持的引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
mysql>  show engines\G;
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

MyISAM 引擎适合多读的情况，对于有很多写的情况，其性能很差，因为其只支持表锁。

InnoDB 引擎支持行锁和表锁，且默认情况下是行锁。且支持事务。对于有不少写的情况，优先选择 InnoDB 引擎。

问题定位

要查找自己业务数据库的问题，可以依次采取下面措施来定位

SQL 语句频率统计

打印数据库启动后运行一段时间后，各项数据统计：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show global status;
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| Aborted_clients                | 631        |
| Aborted_connects               | 7          |
| Binlog_cache_disk_use          | 0          |
| Binlog_cache_use               | 0          |
| Bytes_received                 | 3967622624 |
| Bytes_sent                     | 8593148107 |
| Com_admin_commands             | 1          |
| Com_assign_to_keycache         | 0          |
| Com_alter_db                   | 0          |
......

其中，Com_xxx 表示 xxx 语句的执行次数，通过这类选项我们可以统计数据库的增删改查次数。

例如，生产环境的次数统计数据如下

1
2
3
4
| Com_update                     | 616602     |
| Com_delete                     | 87616      |
| Com_select                     | 111538287  |
| Com_insert                     | 283173     |

查询数量远远大于更新数量。

查找慢查询语句

通过以下配置项开启慢查询日志

1
2
3
4
[mysqld]
slow_query_log=1
long_query_time=1 # 1s
slow_query_log_file=/data/mysql/mysqld-slow.log

开启后测试一下是否起效

1
mysql> SELECT SLEEP(2);

此时到 mysql 数据目录，我这里是 /data/mysql，查看 mysqld-slow.log

1
2
3
4
5
6
7
8
$ sudo cat mysqld-slow.log 
/usr/libexec/mysqld, Version: 5.1.73-log (Source distribution). started with:
Tcp port: 0  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 180913 10:28:34
# Query_time: 2.000476  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1536805714;
SELECT SLEEP(2);

可见，慢查询的 SQL 语句以及执行情况已经被记录下来。可以利用 explain 来具体分析慢查询语句的问题所在。

Explain慢查询

下面是一个典型的慢查询的语句 Expain 分析结果

1
2
3
4
5
6
7
8
9
10
11
*************************** 1. row *************************** 
id: 1
select_type: SIMPLE 
table: a
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 1000
Extra: Using where

type 表示表的连接类型，常见的有 ALL表示全表扫描、Ref 表示索引查找

key 表示使用的索引

rows 表示扫码的行数

上面的结果表明 mysql 使用的全表扫描，性能很差，我们可以为 where 字段建个索引

1
mysql> create index ind_sale_year on t_sale(year);

再次分析结果如下

1
2
3
4
5
6
7
8
9
10
11
*************************** 1. row *************************** 
id: 1
select_type: SIMPLE 
table: a
type: ref 
possible_keys: ind_sale_year
key: ind_sale_year
key_len: 2
ref: const
rows: 1
Extra: Using where

效果很明显，只需要扫描1行了。

索引

大部分 MySQL 的索引(PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) 是以 BTREE 方式存储的。

索引具有前缀特性，特别是针对多列索引的情况，从左到右部分连续涉及到的字段 where 查询都会用到索引。例如我们有个三列索引建在 (col1, col2, col3) 上，那么where 条件是 (col1) 或者 (col1, col2) 或者 (col1, col2, col3) 都会用到该索引，其他情况如 (col2)、(col1, col3) 等均不会用到索引。

对于 where 条件中有 like 的语句，只有 % 不是第一个字符的才会用到索引。例如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> explain select * from company2 where name like '%3'\G; *************************** 1. row ***************************
id: 1 
select_type: SIMPLE
table: company2 
type: ALL
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 1000
Extra: Using where 

mysql> explain select * from company2 where name like '3%'\G; *************************** 1. row ***************************
id: 1 
select_type: SIMPLE
table: company2 
type: range
possible_keys: ind_company2_name 
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where

可以发现第一个例子没有使用索引，而第二例子就能够使用索引，区别就在于 % 的位置不同，前者把 % 放到第一位就不能用到索引，而后者没有放到第一位就使用了索引。如果确实有必要搜索 %pattern%的情况，索引是无法解决的，可以考虑使用 mysql 的 全文索引

如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。例如

1
mysql> SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

假如 key_part1 的取值均匀分布在 1 - 100，那此时使用全表扫描可能更快。

此外，对于用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么也不会用到索引。例如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> show index from sales\G;
*************************** 1. row ***************************
Table: sales
Non_unique: 1
Key_name: ind_sales_year
Seq_in_index: 1
Column_name: year
Collation: A 
Cardinality: NULL 
Sub_part: NULL 
Packed: NULL
Null: 
Index_type: BTREE
Comment:

mysql> explain select * from sales where year = 2001 or country = 'China'\G; *************************** 1. row ***************************
id: 1 
select_type: SIMPLE
table: sales 
type: ALL
possible_keys: ind_sales_year 
key: NULL
key_len: NULL 
ref: NULL
rows: 12
Extra: Using where

查看索引使用情况

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show global status like 'Handler_read%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Handler_read_first    | 299      |
| Handler_read_key      | 20554298 |
| Handler_read_next     | 17493910 |
| Handler_read_prev     | 0        |
| Handler_read_rnd      | 4269136  |
| Handler_read_rnd_next | 11003400 |
+-----------------------+----------+
6 rows in set (0.00 sec)

优化 SQL 语句

优化 select

当要查询的数据确认只有一条记录时，我们可以使用 limit 1 来提高性能，数据库引擎可以在找到一条记录后立即返回而不是再继续向后查找（实验结果并非如此）。例如

1
mysql> select * from t_user where accid = 123456 limit 1;

效果就要比下面的好

1
mysql> select * from t_user where accid = 123456;
优化 insert

  • 对于插入多行的情况，一次插入多行比分多次插入数据要快，主要是避免了多次与数据库的连接、关闭操作。如

    1
    mysql> insert into t_test(col1, col2) values(1, 2) (3, 4) (5, 6);

  • 当从文件中加载表时，使用 LOAD DATA INFILE，通常比很多 insert 的语句要快。

优化 group by

默认情况下 group by 语句会对字段进行排序，效果类似于 order by 语句。如果避免排序带来的性能消耗，可以在 group by 语句后显示指定 order by null 来禁止排序。例如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> explain select id,sum(moneys) from sales2 group by id\G; *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
Extra: Using temporary; Using filesort

mysql> explain select id,sum(moneys) from sales2 group by id order by null\G; *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
Extra: Using temporary

从上面的例子可以看出第一个 SQL 语句需要进行 filesort，而第二个 SQL 由于 ORDER BY NULL 不需要进行 filesort，而 filesort 往往非常耗费时间。

表字段类型

尽量使用短的数据类型

如果数据容量足够，尽量选用比较段的数据类型，例如可以使用 SMALLINT 就不用 INT。

尽量使用 NOT NULL

对于允许 NULL 的字段，数据库需要额外的空间来存储该字段是否为 NULL，且在涉及比较操作时，会更为复杂。因此除非特殊情况，尽量使用 NOT NULL。

定长 vs 变长

常见的非定长数据类型是 VARCHAR，TEXT，BLOB，其他基本都是定长。

对于 MyISAM 引擎，固定长度的表会提高性能，因为搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要查找主键索引。

并且，固定长度的表在 crash 之后也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

而对于 InnoDB 引擎，行存储格式并没有区分定长还是变长，所有数据行都使用指向数据列值的头指针。因此变长数据类型的优势会更大。

VARCHAR vs BLOB

如果存储的数据大小小于8KB，那使用二进制 varchar 类型，而不使用 blob。

顺序主键（InnoDB）

对于 InnoDB 引擎，如果主键的值是个完全随机值，则比较好的方式是在主键前面加上一个当前日期或者时间的前缀，那么这些有序的主键存储时就会相邻，在插入或者获取时会更快。

压缩

如果一个 blob 内存储的是很大的文本内容，可以考虑在存储时先进行压缩。

切割

水平切割

一般数据库的行数如果超过1000w 行，则读写性能会显著下降。因此可以根据一定规则来对所有数据进行水平切割，也即分表分库。分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。

垂直切割

和水平切割对应的就是垂直切割了，意思是对数据库里的列进行分类，单表不能包含太多的列，可以划分成很多张表，列少的小表相对于列多的大表性能会好很多。

一个例子是，如果表中有一些很不常用的列，可以将这些单独拎出来组成一张表。

还有个例子是，假如有一张表拥有一个 last_login 的字段，每次登录之后都会更新该字段。更新该字段意味着数据的查询缓存每次都会被清空。因此将该字段单独出来，保证查询缓存的有效性也是个不错的主意。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去 Join 他们，不然的话，这样的性能会比不分割时还要差。

自助分析

如果现有数据库表已经有一定的数据量了， 则 MySQL 的自助分析工具 PROCEDURE ANALYSE()会显得很有用处。 它会根据表中列的数据类型和实际存储的数据来分析，并给出优化建议。

定期优化

对含有 TEXT 和 BLOB 字段的表，如果经常做删除和修改记录的操作要定时执行 OPTIMIZE TABLE 功能对表进行碎片整理。

删除操作会在数据表中留下很大的『空洞』，以后填入这些『空洞』的记录在插入的性能上会有影响。为了提高性能建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为『空洞』导致性能问题。

例如

1
mysql> OPTIMIZE TABLE t_territory_action;

应用优化

  • 使用连接池
  • 增加 cache 层，如 redis、memcache 等
  • 主从读写分离
  • 分布式

参考资料

<深入浅出 MySQL>

Top 20+ MySQL Best Practices