Mysql数据库常用优化技巧

MySQL优化四大方向

优化MySQL所在服务器内核参数(一般由运维人员完成)

对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整

对SQL语句以及表优化(开发人员进行)

运维+架构的优化

一、系统内核参数优化 大多数MySQL都部署在linux系统上,所以操作系统的一些参数也会影响到MySQL性能,以下对Linux内核参数进行适当优化

net.ipv4.tcp_fin_timeout = 30
# TIME_WAIT超时时间,默认是60s
net.ipv4.tcp_tw_reuse = 1    
# 1表示开启复用,允许TIME_WAIT socket重新用于新的TCP连接,0表示关闭
net.ipv4.tcp_tw_recycle = 1   
# 1表示开启TIME_WAIT socket快速回收,0表示关闭
net.ipv4.tcp_max_tw_buckets = 4096   
# 系统保持TIME_WAIT socket最大数量,如果超出这个数,系统将随机清除一些TIME_WAIT并打印警告信息
net.ipv4.tcp_max_syn_backlog = 4096
# 进入SYN队列最大长度,加大队列长度可容纳更多的等待连接
#在Linux系统中,如果进程打开的文件句柄数量超过系统默认值1024,就会提示“too many files open”信息,所以要调整打开文件句柄限制。
#重启永久生效:
# vi /etc/security/limits.conf  
* soft nofile 65535
* hard nofile 65535
当前用户立即生效:
# ulimit -SHn 65535 

二、数据库配置优化

MySQL应用最广泛的有两种存储引擎:一个是MyISAM,不支持事务处理,读性能处理快,表级别锁。另一个是InnoDB,支持事务处理(ACID属性),设计目标是为大数据处理,行级别锁。 表锁:开销小,锁定粒度大,发生死锁概率高,相对并发也低。 行锁:开销大,锁定粒度小,发生死锁概率低,相对并发也高。 为什么会出现表锁和行锁呢?主要为保证数据完整性。举个例子,一个用户在操作一张表,其他用户也想操作这张表,那么就要等第一个用户操作完,其他用户才能操作,表锁和行锁就是这个作用。否则多个用户同时操作一张表,肯定会数据产生冲突或者异常。 根据这些方面看,使用InnoDB存储引擎是最好的选择,也是MySQL5.5+版本默认存储引擎。每个存储引擎相关运行参数比较多,以下列出可能影响数据库性能的参数。

max_connections = 151
# 同时处理最大连接数,建议设置最大连接数是上限连接数的80%左右
sort_buffer_size = 2M
# 查询排序时缓冲区大小,只对order by和group by起作用,建议增大为16M
open_files_limit = 1024 
# 打开文件数限制,如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时,程序会无法连接数据库或卡死

MyISAM参数默认值:

key_buffer_size = 16M
# 索引缓存区大小,一般设置物理内存的30-40%
read_buffer_size = 128K  
# 读操作缓冲区大小,建议设置16M或32M
query_cache_type = ON
# 打开查询缓存功能
query_cache_limit = 1M  
# 查询缓存限制,只有1M以下查询结果才会被缓存,以免结果数据较大把缓存池覆盖
query_cache_size = 16M  
# 查看缓冲区大小,用于缓存SELECT查询结果,下一次有同样SELECT查询将直接从缓存池返回结果,可适当成倍增加此值

InnoDB参数默认值 :

innodb_buffer_pool_size = 128M
# 索引和数据缓冲区大小,建议设置物理内存的70%左右
innodb_buffer_pool_instances = 1    
# 缓冲池实例个数,推荐设置4个或8个
innodb_flush_log_at_trx_commit = 1  
# 关键参数,0代表大约每秒写入到日志并同步到磁盘,数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘,I/O开销大,执行完SQL要等待日志读写,效率低。2代表只把日志写入到系统缓存区,再每秒同步到磁盘,效率很高,如果服务器故障,才会丢失事务数据。对数据安全性要求不是很高的推荐设置2,性能高,修改后效果明显。
innodb_file_per_table = OFF  
# 是否共享表空间,5.7+版本默认ON,共享表空间idbdata文件不断增大,影响一定的I/O性能。建议开启独立表空间模式,每个表的索引和数据都存在自己独立的表空间中,可以实现单表在不同数据库中移动。
innodb_log_buffer_size = 8M  
# 日志缓冲区大小,由于日志最长每秒钟刷新一次,所以一般不用超过16M
#1:MySQL 默认的最大连接数为 100,可以在 mysql 客户端使用以下命令查看
mysql> show variables like 'max_connections';
​
#2:查看当前访问Mysql的线程
mysql> show processlist;
​
#3:设置最大连接数
mysql>set global max_connections = 5000;
#最大可设置16384,超过没用
​
#4:查看当前被使用的connections
mysql>show global status like 'max_user_connections'
​
#5:展示属于当前用户的线程
show [full] processlist;
​
#6:查询是否锁表
show open tables where in_use>0;
​
#7:查看被锁住的
select * from information_schema.innodb_locks;
​
#8:查看等待锁定
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

三、MySQL语句性能优化

基础优化

1、尽量不要保留NULL值

最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库。在MYSQL中对于INT类型而言,EMPTY是0,而NULL是空值。

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, NULL 不占用空间。

可以在num上设置默认值0,确保表中num列没有NULL值,然后这样查询:

select id from t where num = 0

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

select id from t where num is null

2、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

3、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

4、使用ENUM而不是VARCHAR

ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。

如果我们有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,我们知道这些字段的取值是有限而且固定的,那么,我们应该使用 ENUM 而不是 VARCHAR。

5、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

6、select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

7、不要写一些没有意义的查询,如需要生成一个空表结构:

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

create table #t(…)

8、当只要一行数据时使用LIMIT 1

加上LIMIT 1可以增加性能。MySQL数据库引擎会在查找到一条数据后停止搜索,而不是继续往后查询下一条符合条件的数据记录。

9、Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

索引相关优化

1、对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

如:id<>3则可使用id>3 or id<3来代替。

3、应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or Name = 'admin'

可以这样查询:

select id from t where num = 10
union all
select id from t where Name = 'admin'

4、in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from t where num in(1,2,3)

对于连续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

5、下面的查询也将导致全表扫描:

select id from t where name like ‘%abc%’

若要提高效率,可以考虑全文检索。

6、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where num = @num

可以改为强制查询使用索引:

select id from t with(index(索引名)) where num = @num

应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num/2 = 100

应改为:

select id from t where num = 100*2

7、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

应改为:

select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

9、创建复合索引

比如有一条语句是这样的:

select * from users where area='beijing' and age=22

如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。

如果我们创建了(area, age, salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。

因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

比如复合索引(a,b,c),abc都是排好序的,在任意一段a的下面b都是排好序的,任何一段b下面c都是排好序的

复合索引的生效原则是:从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用; 造成断点的原因:

前边的任意一个索引没有参与查询,后边的全部不生效。 前边的任意一个索引字段参与的是范围查询,后面的不会生效。 断点跟索引字字段在SQL语句中的位置前后无关,只与是否存在有关。

比如:

where a=3 and b=45 and c=5 .... #这种三个索引顺序使用中间没有断点,全部发挥作用;
where a=3 and c=5... #这种情况下b就是断点,a发挥了效果,c没有效果
where b=3 and c=4... #这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何

10、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

11、不要过度索引,不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度。

12、使用短索引 对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

13、like语句操作 一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。

假设查询内容的关键词为'abc':

SELECT * FROM tab_name WHERE index_column LIKE 'abc%' #索引是有效的
SELECT * FROM tab_name WHERE index_column LIKE '%abc' #索引是无效的
SELECT * FROM tab_name WHERE index_column LIKE '%cba' #索引是无效的
SELECT * FROM tab_name WHERE index_column LIKE '%abc%' #索引是无效的

表连接优化

表连接算法

Nested Loop Join(NLJ)算法:

首先介绍一种基础算法:NLJ,嵌套循环算法。循环外层是驱动表,循坏内层是被驱动表。

驱动表会驱动被驱动表进行连接操作。首先驱动表找到第一条记录,然后从头扫描被驱动表,逐一查找与驱动表第一条记录匹配的记录然后连接起来形成结果表中的一条记。

被驱动表查找完后,再从驱动表中取出第二个记录,然后从头扫描被驱动表,逐一查找与驱动表第二条记录匹配的记录,连接起来形成结果表中的一条记录。

重复上述操作,直到驱动表的全部记录都处理完毕为止。这就是嵌套循环连接算法的基本思想,伪代码如下。

    foreach row1 from t1
        foreach row2 from t2
            if row2 match row1 //row2与row1匹配,满足连接条件
                join row1 and row2 into result //连接row1和row2加入结果集

首先加载t1,然后从t1中取出第一条记录,之后加载t2表,与t2表中的记录逐个匹配,连接匹配的记录。

Block Nested Loop Join(BNLJ)算法:

再介绍一种高级算法:BNLJ,块嵌套循环算法,可以看作对NLJ的优化。

大致思想就是建立一个缓存区joinbuffer,一次从驱动表中取多条记录,然后扫描被驱动表,被驱动表的每一条记录都尝试与缓冲区中的多条记录匹配,如果匹配则连接并加入结果集。

缓冲区越大,驱动表一次取出的记录就越多。这个算法的优化思路就是减少内循环的次数从而提高表连接效率。

如果 joinbuffer 维护的是一个哈希表的话,每次查找做一次判断就能找到数据,效率提升飞快,其实这就是 hash join 了,MySQL 8.0 已支持。

影响性能的因素

1、内循环的次数:

现在考虑这么一个场景,当t1有100条记录,t2有10000条记录。那么,t1驱动t2与t2驱动t1,他们之间在效率上孰优孰劣?

如果是单纯的分析指令执行次数,他们都是100*10000,但是考虑到加载表的次数呢。

首先分析t1驱动t2,t1表加载1次,t2表需要加载100次。然后分析t2驱动t1,t2表首先加载1次,但是t1表要加载10000次。所以,t1驱动t2的效率要优于t2驱动t1的效率。

由此得出,小表驱动大表能够减少内循环的次数从而提高连接效率。

另外,如果使用Block Nested Loop Join算法的话,通过扩大一次缓存区的大小也能减小内循环的次数。

由此又可得,设置合理的缓冲区大小能够提高连接效率

2、快速匹配:

扫描被驱动表寻找合适的记录可以看做一个查询操作,如何提高查询的效率呢?建索引啊!

由此还可得出,在被驱动表建立索引能够提高连接效率

3、排序:

假设t1表驱动t2表进行连接操作,连接条件是t1.id=t2.id,而且要求查询结果对id排序。现在有两种选择,方式一[...ORDER BY t1.id],方式二[...ORDER BY t2.id]。如果我们使用方式一的话,可以先对t1进行排序然后执行表连接算法,如果我们使用方式二的话,只能在执行表连接算法后,对结果集进行排序(Using temporary),效率自然低下。

由此最后可得出,优先选择驱动表的属性进行排序能够提高连接效率。

最佳实践:直接让MySQL去判断

但是,表的记录数是会变化的,有没有一劳永逸的写法?当然有啦,MySQL自带的Optimizer会优化内连接,优化策略就是上面讲的小表驱动大表。所以,以后写内连接不要纠结谁内连接谁了,直接让MySQL去判断吧。

临时表优化

MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。

临时表存储

MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的MyISAM存储引擎; 一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表导出到磁盘临时表; Linux平台上缺省是/tmp目录,/tmp目录小的系统要注意啦。

使用临时表的场景

1)ORDER BY子句和GROUP BY子句不同, 例如:

ORDERY BY price GROUP BY name

2)在JOIN查询中,ORDER BY或者GROUP BY使用了不是第一个表的列 例如:

SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3)ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)

4)SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL,结果会很小,请直接使用内存临时表,不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下,我们没有必要使用这个选项,让MySQL服务器选择即可。

直接使用磁盘临时表的场景

1)表包含TEXT或者BLOB列; 2)GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列; 3)使用UNION或者UNION ALL时,SELECT子句中包含大于512字节的列;

临时表相关配置

tmp_table_size:指定系统创建的内存临时表最大大小; http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size

max_heap_table_size: 指定用户创建的内存表的最大大小; http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size

注意:最终的系统创建的内存临时表大小是取上述两个配置值的最小值。

表的设计原则

使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有: 1)创建索引:在ORDER BY或者GROUP BY的列上创建索引; 2)分拆很长的列:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。

优化建议

1、尽量使用表变量来代替临时表。

2、避免频繁创建和删除临时表,以减少系统表资源的消耗。例如,当需要重复引用大型表或常用表中的某个数据集时。

3、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

4、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

5、临时表不要过大,如果过大导致内存空间不够,会写入磁盘,导致磁盘IO过高。

Sql语句优化工具

慢日志

如果发现系统慢了,又说不清楚是哪里慢,那么就该用这个工具了。只需要为mysql配置参数,mysql会自己记录下来慢的sql语句。

在默认情况下,MySQL的慢日志记录是关闭的,我们可以通过将设置slow_query_log=1来打开MySQL的慢查询日志

配置很简单,参数文件里配置:

slow_query_log=1
​
slow_query_log_file=/home/slow.txt
​
long_query_time = 2
​
#就可以在/home/slow.txt里找到执行时间超过2秒的语句了,根据这个文件定位问题吧。

也可以使用命令配置:

#查看慢查询日志是否开启 OFF关闭 ON开启
show variables like 'slow_query_log'
#开启慢查询日志
set global slow_query_log = on
​
#慢查询日志所存储的文件位置
show variables like 'slow_query_log_file'
#设置慢查询日志位置
set global slow_query_log_file = '/home/slow.txt'
​
#是否吧没有使用索引的sql记录到慢查询日志当中 PFF关闭 ON开启
show variables like 'log_queries_not_using_indexes'
#设置吧没有使用索引的sql记录添加到慢查询日志当中
set global log_queries_not_using_indexes = on;
​
#查看long_query_time设置的时间
show variables like 'long_query_time';
#超过多少秒之后的查询记录到慢查询日志当中
set [session|global] long_query_time = 1

总结下哪些SQL能被MySQL慢日志记录:

不会记录MySQL中的管理维护命令,除非明确设置log_slow_admin_statements=1 SQL执行时间必须超过long_query_time,(不包括锁等待时间) 参数log_queries_not_using_indexes设置为1,且SQL没有用到索引,同时没有超过log_throttle_queries_not_using_indexes 参数的设定 查询examine的行数必须超过min_examined_row_limit

慢查询日志的分析工具

mysqldumpslow慢日志文件可能会很大,让人去看是很难受的事。这时候我们可以通过mysql自带的工具来分析。

# -s参数 通过什么方式来排序
-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
            al: average lock time  # 根据锁定时间
            ar: average rows sent  # 根据返回行数
            at: average query time # 根据返回时间
             c: count
             l: lock time
             r: rows sent
             t: query time  
-r           reverse the sort order (largest last instead of first)
​
# -t 查看前多少条日志
-t NUM       just show the top n queries
-a           don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   grep: only consider stmts that include this string
-h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
           default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           don't subtract lock time from total time

而且这个工具可以实现简单的排序,让我们有的放矢。

mysqldumpslow /home/slow.txt -t 2 -s al

主要功能是, 统计不同慢sql的

  • 出现次数(Count),
  • 执行最长时间(Time),
  • 累计总耗费时间(Time),
  • 等待锁的时间(Lock),
  • 发送给客户端的行总数(Rows),
  • 扫描的行总数(Rows),
  • 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示) mysqlsla, hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具)

myprofi, 纯php写的一个开源分析工具.项目在 sourceforge 上. http://myprofi.sourceforge.net/

Anemometer 是一个图形化显示从MySQL慢日志的工具。结合pt-query-digest,Anemometer可以很轻松的帮你去分析慢查询日志,让你很容易就能找到哪些SQL需要优化。

https://github.com/box/Anemometer

使用EXPLAIN关键字

使用EXPLAIN关键字可以使我们知道MySQL是如何处理SQL语句的,这样可以帮助我们分析我们的查询语句或是表结构的性能瓶颈

EXPLAIN的查询结果还会告诉我们索引主键是如何被利用的,数据表是如何被搜索或排序的....等等。

语法格式是: EXPLAIN [EXTENDED] SELECT 语句

四、MySQL数据库运维+架构优化

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善。主要谈MySQL数据库发展周期中所面临的问题及优化方案

1、数据库表设计

项目立项后,开发部根据产品部需求开发项目,开发工程师工作其中一部分就是对表结构设计。对于数据库来说,这点很重要,如果设计不当,会直接影响访问速度和用户体验。影响的因素很多,比如慢查询、低效的查询语句、没有适当建立索引、数据库堵塞(死锁)等。当然,有测试工程师的团队,会做压力测试,找bug。对于没有测试工程师的团队来说,大多数开发工程师初期不会太多考虑数据库设计是否合理,而是尽快完成功能实现和交付,等项目有一定访问量后,隐藏的问题就会暴露,这时再去修改就不是这么容易的事了。

2、数据库部署

该运维工程师出场了,项目初期访问量不会很大,所以单台部署足以应对在1500左右的QPS(每秒查询率)。考虑到高可用性,可采用MySQL主从复制+Keepalived做双击热备,常见集群软件有Keepalived、Heartbeat。

3 、硬件配置

加大物理内存,提高文件系统性能。linux内核会从内存中分配出缓存区(系统缓存和数据缓存)来存放热数据,通过文件系统延迟写入机制,等满足条件时(如缓存区大小到达一定百分比或者执行sync命令)才会同步到磁盘。也就是说物理内存越大,分配缓存区越大,缓存数据越多。当然,服务器故障会丢失一定的缓存数据。

SSD硬盘代替SAS硬盘,将RAID级别调整为RAID1+0,相对于RAID1和RAID5有更好的读写性能(IOPS),毕竟数据库的压力主要来自磁盘I/O方面。

4、数据库架构扩展

随着业务量越来越大,单台数据库服务器性能已无法满足业务需求,该考虑加机器了,该做集群了~~~。主要思想是分解单台数据库负载,突破磁盘I/O性能,热数据存放缓存中,降低磁盘I/O访问频率。

4.1 主从复制与读写分离

因为生产环境中,数据库大多都是读操作,所以部署一主多从架构,主数据库负责写操作,并做双击热备,多台从数据库做负载均衡,负责读操作,主流的负载均衡器有LVS、HAProxy、Nginx。

怎么来实现读写分离呢?大多数企业是在代码层面实现读写分离,效率比较高。另一个种方式通过代理程序实现读写分离,企业中应用较少,常见代理程序有MySQL Proxy、Amoeba。在这样数据库集群架构中,大大增加数据库高并发能力,解决单台性能瓶颈问题。如果从数据库一台从库能处理2000 QPS,那么5台就能处理1w QPS,数据库横向扩展性也很容易。

有时,面对大量写操作的应用时,单台写性能达不到业务需求。如果做双主,就会遇到数据库数据不一致现象,产生这个原因是在应用程序不同的用户会有可能操作两台数据库,同时的更新操作造成两台数据库数据库数据发生冲突或者不一致。在单库时MySQL利用存储引擎机制表锁和行锁来保证数据完整性,怎样在多台主库时解决这个问题呢?有一套基于perl语言开发的主从复制管理工具,叫MySQL-MMM(Master-Master replication managerfor Mysql,Mysql主主复制管理器),这个工具最大的优点是在同一时间只提供一台数据库写操作,有效保证数据一致性。

4.2 增加缓存

给数据库增加缓存系统,把热数据缓存到内存中,如果缓存中有要请求的数据就不再去数据库中返回结果,提高读性能。缓存实现有本地缓存和分布式缓存,本地缓存是将数据缓存到本地服务器内存中或者文件中。分布式缓存可以缓存海量数据,扩展性好,主流的分布式缓存系统有memcached、redis,memcached性能稳定,数据缓存在内存中,速度很快,QPS可达8w左右。如果想数据持久化就选择用redis,性能不低于memcached。

工作过程:

4.3 分库

分库是根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog等库。如果业务量很大,还可将切分后的库做主从架构,进一步避免单个库压力过大。

分库分布采用 ShardingSphere https://shardingsphere.apache.org/document/current/cn/overview/

Mycat 中间件 https://www.yuque.com/books/share/6606b3b6-3365-4187-94c4-e51116894695

4.4 分表

数据量的日剧增加,数据库中某个表有几百万条数据,导致查询和插入耗时太长,怎么能解决单表压力呢?你就该考虑是否把这个表拆分成多个小表,来减轻单个表的压力,提高处理效率,此方式称为分表。

分表技术比较麻烦,要修改程序代码里的SQL语句,还要手动去创建其他表,也可以用merge存储引擎实现分表,相对简单许多。分表后,程序是对一个总表进行操作,这个总表不存放数据,只有一些分表的关系,以及更新数据的方式,总表会根据不同的查询,将压力分到不同的小表上,因此提高并发能力和磁盘I/O性能。

分表分为垂直拆分和水平拆分:

垂直拆分:把原来的一个很多字段的表拆分多个表,解决表的宽度问题。你可以把不常用的字段单独放到一个表中,也可以把大字段独立放一个表中,或者把关联密切的字段放一个表中。

水平拆分:把原来一个表拆分成多个表,每个表的结构都一样,解决单表数据量大的问题。

4.5 分区

分区就是把一张表的数据根据表结构中的字段(如range、list、hash等)分成多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上还是一张表,但数据散列在多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘I/O读写性能,实现比较简单。

注:增加缓存、分库、分表和分区主要由程序猿来实现。

5、数据库维护

数据库维护是运维工程师或者DBA主要工作,包括性能监控、性能分析、性能调优、数据库备份和恢复等。

5.1 性能状态关键指标

专业术语:QPS(Queries Per Second,每秒查询数)和TPS(Transactions Per Second, 每秒事务数) 通过show status查看运行状态,会有300多条状态信息记录,其中有几个值帮可以我们计算出QPS和TPS,如下:

Uptime:服务器已经运行的实际,单位秒 Questions:已经发送给数据库查询数 Com_select:查询次数,实际操作数据库的 Com_insert:插入次数 Com_delete:删除次数 Com_update:更新次数 Com_commit:事务次数 Com_rollback:回滚次数

那么,计算方法来了,基于Questions计算出QPS:

mysql> show global status like 'Questions'; mysql> show global status like 'Uptime'; QPS = Questions / Uptime 基于Com_commit和Com_rollback计算出TPS:

mysql> show global status like 'Com_commit'; mysql> show global status like 'Com_rollback'; mysql> show global status like 'Uptime'; TPS = (Com_commit + Com_rollback) / Uptime 另一计算方式:

#基于Com_select、Com_insert、Com_delete、Com_update计算出QPS:
mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update'); #等待1秒再执行,获取间隔差值,第二次每个变量值减去第一次对应的变量值,就是QPS。 5.3 数据库备份 备份数据库是最基本的工作,也是最重要的,否则后果很严重,你懂得!高频率的备份策略,选用一个稳定快速的工具至关重要。数据库大小在2G以内,建议使用官方的逻辑备份工具mysqldump。超过2G以上,建议使用percona公司的物理备份工具xtrabackup,否则慢的跟蜗牛似得。这两个工具都支持InnoDB存储引擎下热备,不影响业务读写操作。

5.4 数据库修复 有时候MySQL服务器突然断电、异常关闭,会导致表损坏,无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复,myisamchk和mysqlcheck。前者只能修复MyISAM表,并且停止数据库,后者MyISAM和InnoDB都可以,在线修复。

注意:修复前最好先备份数据库。

myisamchk常用参数: -f --force 强制修复,覆盖老的临时文件,一般不使用 -r --recover 恢复模式 -q --quik 快速恢复 -a --analyze 分析表 -o --safe-recover 老的恢复模式,如果-r无法修复,可以使用此参数试试 -F --fast 只检查没有正常关闭的表

例如:myisamchk -r -q *.MYI mysqlcheck常用参数: -a --all-databases 检查所有的库 -r --repair 修复表 -c --check 检查表,默认选项 -a --analyze 分析表 -o --optimize 优化表 -q --quik 最快检查或修复表 -F --fast 只检查没有正常关闭的表

例如:mysqlcheck -r -q -uroot -p123456 weibo