构建高性能服务器 -- 数据库篇

写在前面

服务器数据,如用户相关数据,都需要保存起来,随时取出再次使用。当然,现在的内存数据库也已经发展到相对可靠的阶段了,但是,我们这里还是讨论一个传统关系型数据库在构建高性能服务器方面的使用,如MySQL。

利其器

友好的MySQL状态报告

俗话说,工欲善其事,必先利其器。所以,在服务器端数据库性能优化方面,我们先介绍下一个利器 — mysqlreport

当然,在MySQL命令行中,我们可获取当前数据库的状态:

1
2
mysql> show status;
mysql> show innodb status;

那么还需要mysqlreport干什么呢?当然,身为资深码农,我们一直以高标准要求,所以mysqlreport是一款友好方式的数据库状态检测工具,各方面甩直白的MySQL命令行好几条街。

我们利用mysqlreport获取下当前状态:

1
mysql> mysqlreport --user USER_NAME --password PASSWORD

具体状态结果很长,就不放了,有兴趣的同学可以自己去试下。

解释查询

SQL语句的性能如何该如何知晓呢?解释查询explain横空出世。

explain的使用方式非常简单,举个简单例子,我们用以下语句创建数据表:

1
2
3
4
5
create table test (
id int(11) not null auto_increment,
name varchar(255) not null,
primary key (id)
);

然后我们用存储过程向test数据表中增加一些数据。首先创建function:

1
delimiter //
create function rs(n int)
returns varchar(255)
begin
	declare chars char(26) default 'abcdefghijklmnopqrstuvwxyz';
	declare res varchar(1024) default '';
	declare i int default 0;
	repeat
	set i = i + 1;
	set res = concat(res, substring(chars, floor(1 + rand() * 26), 1));
	until i = n end repeat;
	return res;
end
//

然后创建存储过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
create procedure inst(n int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into test set name=rs(16);
until i = n end repeat;
commit;
set autocommit = 1;
end
//

最后调用新创建的存储过程inst:

1
call inst(100000);

这时,我们已向数据表中插入了10W条数据。这时,我们对该数据表中数据进行查询,看下效率如何该怎么办呢?我们利用explain语句进行查看:

1
explain select * from test where id = 999;

得到结果如下:

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | simple      | test  | const | primary       | primary | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

我们可以看到,结果中type为const,这表示这次查询通过索引直接找到一个匹配行,优化器认为它的时间复杂度为常量。而根据key的值primary,意味着这次查询使用了主键索引。

然后我们利用explain语句查看另外的查询语句:

1
explain select * from test where name = 'yoxxwmxbpvhyxtyo';'

得到结果为:

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 100260 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

与之前不同的是,这里type值为all,即本次查询采用了全表扫描,而key的值为null,这也理所当然,因为我们并没有将name增加索引。

经过上述简要讲述,大家应该了解到,explain语句在数据库优化过程中的确是一把利刃。

慢查询分析

在开发环境中,我们可以对所有查询语句进行explain分析,并建立适当的索引,把数据库性能调至最优。但是,在正式环境中,随着实际数据的积累,查询计算和开销有可能会增加,甚至我们发现有些索引设计并不理想。令人头疼的是,线上环境根本不可能用explain去分析查询语句。这个时候,我们需要一个自动记录下运行环境中每次查询执行时间的工具 —— MySQL慢查询日志

在MySQL中开启这项功能并不复杂,在my.cnf中增加下列配置选项:

long_query_time = 1
log-slow-queries = /data/var/mysql_slow.log

这意味着,MySQL对执行时间超过1秒的查询请求将记录在log-slow-queries路径的文件中,我们可以用MySQL提供的mysqldumpslow查看日志,也可以通过第三方工具 —— mysqlsla

当然,我们还可以在mysqlreport的友好报告中发现以下列:

Slow 1 s           722        0.2/s         0.01     %MDS: 0.08     Log:ON

这其实告诉我们,超过1秒的慢查询每秒有0.2次,占总请求数的0.08%。

善其事

正确使用索引

在影响数据库查询性能的诸多因素中,索引绝对是一个重量级的因素。

什么是索引

索引说白了,就相当于一本书的目录。很容易理解,如果我们需要寻找一本书中的某一章节,我们会先去查找目录,如此做来可以大大提高查找的效率。但是如果没有索引,即书本目录,我们寻找过程就会变成全书遍历查找。

事实上数据库索引也是一样,可以分为全表扫描(Full Table Scan)和索引扫描(Index Scan)两种。在大多数情况下,索引搜索当然要比全表扫描性能高很多,但这也不是绝对的。比如需要查找的记录占据了整个数据表的大部分,那么使用索引扫描反而比全表扫描更差。很容易想象,对于一本书,如果需要阅读绝大部分内容,那么按顺序全局阅读比按目录查找再阅读效率更高,因为查找目录也是需要开销的。

另外,为数据库数据建立索引也不是一件简单的事,索引必须对应到数据库的每一行记录,这样一来,引入数据库索引将会带来一个巨大的目录,这点存储开销也是不得不重视的。而且,除了普通索引之外,还有唯一索引,主键,全文索引等,各种索引类型都有可能带来更多额外的开销,如唯一索引插入时必须保证其唯一性,而唯一性检查也是不笔不小的开销

这么说来,全表扫描和索引扫描孰优孰劣真心不好说,说句真心话,这是你自己的事情。记住:为数据表建立索引是你自己的事情,永远不要期待有什么工具会自动帮你建立索引,没有工具会知道你未来会频繁地在哪个字段上进行条件查询,为哪个字段建立索引最优

初识索引

利其器一节中,我们讨论到,可以利用explain来进行SQL语句的性能分析。我们还是接着那时的例子,利用主键id进行查询与利用普通字段name进行查询,在查询效率上确有不同,而且一般情况下(查询绝大多数条数情况除外)前者优于后者。

那么我们现在把name字段也修改为索引呢?利用以下语句进行修改:

1
alter table test add key name(name);

然后,我们再用explain进行查看:

1
explain select * from test where name = 'yoxxwmxbpvhyxtyo';

结果稍微有点变化:

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | name          | name | 257     | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

现在是什么情况呢?我们可以看到,这时key字段的值为name,即本次查询采用了name字段的索引。另外,对于没有使用主键索引或者唯一索引的条件查询,查询结果可能有多个匹配行,所以MySQL为这种情况定义的type为ref,如本次结果中所示。

那么,如果我们进行模糊查询呢?试下吧。

1
explain select * from test where name like '%mxbpvhy';

看下结果:

+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | name | 257     | NULL | 100489 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+

哇,还是很强力的,连模糊查询都能够利用name索引,可以看到type字段中明确写着index值。

使用组合索引

实际项目过程中,查询中包含组合条件是不可避免的,如:

... where a = 1 and b = 2
... where a = 1 order by b
... where a = 1 group by b

这个时候,如果a和b字段都分别建立了索引,它们仍然不能同时发挥作用。因为一次查询只能利用一个索引。如何破?组合索引大叫:我来!

还是基于先前的数据表,我们用以下命令删除先前增加的name索引,增加name_1和name_2两个字段,同时增加name,name_1和name_2的联合索引normal_key:

1
2
3
4
alter table test drop index name;
alter table test add name_1 not null;
alter table test add name_2 not null;
alter table test add index normal_key(name, name_1, name_2);

至此,数据表table中,包含4个字段,具体情况如下:

+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(255) | NO   | MUL | NULL    |                |
| name_1 | varchar(255) | NO   |     | NULL    |                |
| name_2 | varchar(255) | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

而查看数据表中index得:

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY    |            1 | id          | A         |      100288 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | normal_key |            1 | name        | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | normal_key |            2 | name_1      | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | normal_key |            3 | name_2      | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

可看,当前数据表的除了主键索引以外,还有我们刚设置的联合索引normal_key。

这个时候,我们利用explain命令看下相关查询语句:

1
2
3
explain select * from test where name = 'abc';
explain select * from test where name = 'abc' and name_1 = "def";
explain select * from test where name = 'abc' and name_1 = 'def' and name_2 = 'ghi';

这些查询语句的结果都是使用索引,具体结果大家可以自行尝试。

这里我们要引入一个“最左前缀”这个组合索引的基本原则。这个概念可以如此解释:如果查询条件检索时,只需要匹配联合索引中的最左顺序一个或多个字段,称为最左索引原则,或者叫最左前缀。所以,我们查看如下SQL语句时,得到的结果应该是不使用索引。

1
explain select * from test where name_2 = 'ghi' and name = 'abc';

然而实际上,还是使用索引的,不得不佩服MySQL强大的SQL优化器

索引的正确使用姿势

当然,事实上,在整个数据库优化过程中,索引的利用永远是难点,所以,利用好你的explain工具,千万不要想当然

凡是都有两面性,所以索引的使用也会有代价产生,具体可以归纳为以下几点:

  • 索引会占据更多的磁盘空间,很多时候索引甚至比数据本身还要大。不这现在磁盘的空间很容易达到TB级别,所以通常磁盘空间还未写满时,计算能力的瓶颈已经显现出来的。
  • 当建立索引的字段发生更新时,会引发索引本身的更新,这将产生不小的计算开销。这直接决定使用索引的数据表必须是读多写少。
  • 索引需要我们花费一些额外的时候来维护。MyISAM和Innodb类型的数据表存储结构会有不同,所以具体维护时也有些不同,比如MyISAM的索引写缓存由于断电可能造成索引的损坏,需要我们及时地手动修复。

锁写与等待

当有多个用户并发访问数据库中某一资源的时候,为了保证并发访问的一致性,数据库必须通过锁机制来协调这些访问。而锁机制也是影响数据库性能的一个重要点

MySQL为MyISAM类型表提供了表级别的锁定,即当有请求对数据表有更新操作(如update)时,数据表就会被锁定,其他任何对当前表的操作将被排斥,甚至包括select查询。另外需要注意的是,更新操作有着默认高优先级,即当表锁释放后,更新操作将先获得锁定,然后才能轮到读取操作。此机制将可能引发以下情况:频繁更新操作将会使得读取操作长时间等待。

MySQL为Innodb类型表提供了行锁,即当有请求对数据表有更新操作时,数据表不是全表被锁定,而是更新的特定行,其他任何对当前行的操作将被排斥,但是对其他行的操作将不被限制

从概念上对比,似乎行锁要比表锁好。但这并不是绝对的,理论上就锁定本身而言,行锁定的开销并不比表锁小。比如对于update密集型的场景,行锁定并不是救世主,因为行锁单位锁定开销比表锁大,所以具体性能只能实测。

事务性表的性能

MySQL中Innodb类型表不仅支持行锁定,还支持事务。有时候,我们选择Innodb类型的数据表就是因为其支持事务。当然,如果我们没有在应用程序中使用事务操作的打算,而只是看中了其他特性,如行锁定,外键以及易于修复等,我们仍然可以使用它。

Innodb实现事务是采用预写日志方式(WAL)完成的。当有事务提交时,Innodb首先将它写到内存中的事务日志缓冲区中,随后当事务日志写入磁盘时,Innodb才更新实际数据和索引。这里,事务日志写入磁盘的时机可以分为3种:

  • innodb_flush_log_at_trx_commit=1。这个设置表示事务提交时立即将事务写入磁盘,同时实际数据和索引也更新。
  • innodb_flush_log_at_trx_commit=0。这个设置表示事务提交时不立即将事务写入磁盘,而是每隔1秒写入磁盘文件一次,并且刷新到磁盘中,同时更新实际数据和索引。这种方式如果在事务提交和事务写入磁盘之间发生mysqld崩溃的话,那么在内存中的最近1秒的数据将会丢失,注意,是永久性丢失。
  • innodb_flush_log_at_trx_commit=2。这个设置表示事务提交时立即写入磁盘,但没有刷新到磁盘,而是存放在磁盘缓冲区,每隔1秒刷新磁盘一次,同时更新数据和索引。这种方式如果发生mysqld崩溃的话,数据并不会丢失,因为存放在磁盘缓冲区的数据还是会刷新到磁盘中。但是如果操作系统崩溃的话,那就永久丢失了。

显然,第2种方式性能最高,但是丢失数据的可能性也最大。

另外,MySQL有一个选项会影响到上述3种方式的实现,它就是innodb_flush_method选项。我们可以将该选项设置为O_DIRECT,如下

innodb_flush_method = O_DIRECT

如此一来,Innodb可以直接I/O,所有的读写操作将会跳过文件系统的系统缓冲区,提高I/O性能,当然,预写日志方式也就会随之改变。

使用查询缓存

使用查询缓存的目的很简单,那就是提高数据库查询数据的性能

在默认情况下,MySQL是没有开启查询缓存功能的,我们可以进行以下配置进行开启(开启256MB的内存空间来缓存查询结果):

query_cache_size = 268435446
query_cache_type = 1
query_cache_limit = 1048576

查询缓存功能,不论是MyISAM还是Innodb类型,对于以select为主的应用显然性能会大增。但是令人遗憾的是,MySQL的缓存过期策略并不是十分令人满意。当一个数据表有更新操作后,那么涉及这个表的所有查询缓存均失效。话虽如此,但是对于select操作占绝大部分的应用,还是极大的福音。我们只要注意不要将其使用在select和update交叉混合的应用就可以了。

另外,我们可以利用mysqlreport查看查询缓存的状态,如下所示:

__ Query Cache ______________________________________________________
Memory usage   17.11k of  16.00M  %Used:   0.10
Block Fragmnt 100.00%
Hits                2     0.0/s
Inserts            13     0.0/s
Insrt:Prune      13:1     0.0/s
Hit:Insert     0.15:1

很显示,当前缓存区大小为16M,而现在使用了17K,另外,Insrt:Prume代表查询结果进入缓存的次数和过期被删除的次数的比例,当然,这个比例越大,表示查询操作与更新操作相比更频繁,也就更适合使用查询缓存。

临时表

我们利用mysqlreport时,可以发现有Using temporary的状态,如下:

__ Created Temp ________________________________________________________
Disk table         75     0.0/s
Table             296     0.0/s    Size:  16.0M
File               10     0.0/s

当然,我们这里的要求就是尽量避免使用临时表,因为无论是在磁盘(Disk table),内存(Table),还是在文件(File)中创建临时表,这都会是不小的开销

线程池

与临时表类似,我们可以从mysqlreport中看到线程的使用情况:

__ Threads _________________________________________________________
Running             1 of    1
Cached              1 of    8      %Hit:  97.44
Created             2     0.0/s
Slow                0       0/s

这里我们看到,线程池的命中情况为97.44%,当然,命中率100%当然是最好的。一个比较好的以夷伐夷是在应用中,尽量使用持久连接,这将在一定程度上减少线程的重复创建

反范式化设计

在学校的时候,数据库老师都会教我们,设计数据库的时候,要遵守一定的范式。比如说第三范式。简单的说,第三范式要求在一个数据表中,非主键字段这之间不能存在依赖关系,这样可以避免更新异常、插入异常和删除异常,保证关系的一致性,并且减少数据冗余

比如以下表的设计就是遵循范式:

(用户ID,好友ID)
(用户ID,用户昵称,用户邮箱,注册时间,联系电话)

如果这是学校考试,那绝对是满分呐。我们对这两表中的数据进行查询,比如查询某用户的其好友的昵称。这里,我们可以有两种查询方式:

  • 将两个表进行一次联合查询;
  • 先在第一个表中查询出所有好友的ID,然后在第二个表中查询这些ID对应的昵称。

显然,不论是采用哪一种方式,都需要打开两张数据表。那么,如果我们这么设计呢:

(用户ID,好友ID,用户昵称)
(用户ID,用户昵称,用户邮箱,注册时间,联系电话)

这样一来,刚刚查询好友昵称的需求只需要打开第一张表就可以完成,当然付出的代价就是数据有冗余,而且进行数据更新时,可能会对数据造成不一致。但是实际中,有多少用户会一直修改它的昵称呢?所以,实际中,引入一些反范式的设计也是可行的,当然前提是,它的性能将会提高

放弃关系型数据库

有些时候,相较于传统关系型数据库,我们采用Key-Value形式的数据库更加简单有效。具体可以阅读下相关资料 —— NoSQL