一、服务器硬件的优化
提升硬件设备,例如选择尽量高频率的内存(频率不能高于主板的支持)、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。
CPU的选择:
对于数据库并发比较高的场景,CPU的数量比频率重要。
对于CPU密集型场景和频繁执行复杂SQL的场景,CPU的频率越高越好。
MySQL数据库配置优化
(1) innodb_buffer_pool_size 表示缓冲池字节大小,推荐值为物理内存的50%~80%。
(2) innodb_flush_log_at_trx_commit=1 用来控制redo log刷新到磁盘的策略。
(3)sync_binlog=1每提交1次事务同步写到磁盘中,可以设置为n。
(4)innodb_max_dirty_pages_pct=30 脏页占innodb_buffer_pool_size的比例时,触发刷脏页到磁盘。 推荐值为25%~50%。
(5) innodb_io_capacity=200 后台进程最大IO性能指标,默认200,如果SSD,调整为5000~20000
(6) innodb_data_file_path 指定innodb共享表空间文件的大小。
(7)long_qurey_time=0.3 慢查询日志的阈值设置,单位秒。
(8)binlog_format=row mysql复制的形式,row为MySQL8.0的默认形式。
(9)max_connections=200 调高该参数则应降低interactive_timeout、wait_timeout的值,过大,实例恢复时间长;过小,造成日志切换频繁。
二、CentOS系统针对mysql的参数优化
本节只提及部分重要的参数,更全面的参数优化,可以查看这本书。
内核相关参数(/etc/sysctl.conf)
以下参数可以直接放到sysctl.conf文件的末尾。
1.增加监听队列上限:
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
2.加快TCP连接的回收:
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
3.TCP连接接收和发送缓冲区大小的默认值和最大值:
net.core.wmem_default = 87380
net.core.wmem_max = 16777216
net.core.rmem_default = 87380
net.core.rmem_max = 16777216
4.减少失效连接所占用的TCP资源的数量,加快资源回收的效率:
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
5.单个共享内存段的最大值:
kernel.shmmax = 4294967295
- 这个参数应该设置的足够大,以便能在一个共享内存段下容纳整个的Innodb缓冲池的大小。
- 这个值的大小对于64位linux系统,可取的最大值为(物理内存值-1)byte,建议值为大于物理内存的一半,一般取值大于Innodb缓冲池的大小即可。
6.控制换出运行时内存的相对权重:
vm.swappiness = 0
这个参数当内存不足时会对性能产生比较明显的影响。(设置为0,表示Linux内核虚拟内存完全被占用,才会要使用交换区。)
Linux系统内存交换区:
在Linux系统安装时都会有一个特殊的磁盘分区,称之为系统交换分区。
使用 free -m 命令可以看到swap就是内存交换区。
作用:当操作系统没有足够的内存时,就会将部分虚拟内存写到磁盘的交换区中,这样就会发生内存交换。
如果Linux系统上完全禁用交换分区,带来的风险:
- 降低操作系统的性能 容
- 易造成内存溢出,崩溃,或都被操作系统kill掉
增加资源限制(/etc/security/limit.conf)
打开文件数的限制(以下参数可以直接放到limit.conf文件的末尾):
-
soft nofile 65535
-
hard nofile 65535
*:表示对所有用户有效
soft:表示当前系统生效的设置(soft不能大于hard )
hard:表明系统中所能设定的最大值
nofile:表示所限制的资源是打开文件的最大数目
65535:限制的数量
以上两行配置将可打开的文件数量增加到65535个,以保证可以打开足够多的文件句柄。
注意:这个文件的修改需要重启系统才能生效。
三 、磁盘调度策略
1.cfq (完全公平队列策略,Linux2.6.18之后内核的系统默认策略)
该模式按进程创建多个队列,各个进程发来的IO请求会被cfq以轮循方式处理,对每个IO请求都是公平的。该策略适合离散读的应用。
2.deadline (截止时间调度策略)
deadline,包含读和写两个队列,确保在一个截止时间内服务请求(截止时间是可调整的),而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象,deadline对数据库类应用是最好的选择。
3.noop (电梯式调度策略)
noop只实现一个简单的FIFO队列,倾向饿死读而利于写,因此noop对于闪存设备、RAM及嵌入式系统是最好的选择。
4.anticipatory (预料I/O调度策略)
本质上与deadline策略一样,但在最后一次读操作之后,要等待6ms,才能继续进行对其它I/O请求进行调度。它会在每个6ms中插入新的I/O操作,合并写入流,用写入延时换取最大的写入吞吐量。anticipatory适合于写入较多的环境,比如文件服务器。该策略对数据库环境表现很差。
查看调度策略的方法:
cat /sys/block/devname/queue/scheduler
修改调度策略的方法:
echo > /sys/block/devname/queue/scheduler
四、MySQL表结构与SQL优化
索引优化规则
结合笔者的上一篇博客——《彻底搞懂MySQL的索引》,可以很容易理解索引优化的原理。
1.使用最左前缀规则
如果使用联合索引,要遵守最左前缀规则。即要求使用联合索引进行查询,从索引的最左前列开始,不跳过索引中的列并且不能使用范围查询(>、<、between、like)。
索引失效示例
2.模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)
假如索引列code的值为’AAA’,‘AAB’,‘BAA’,‘BAB’,如果where code like '%AB’条件,由于条件前面是模糊的,所以不能利用索引的顺序,必须逐个查找,看是否满足条件。这样会导致全索引扫描或者全表扫描。
如果是where code like ‘A%’,就可以查找code中A开头的数据,当碰到B开头的数据时,就可以停止查找了,因为后面的数据一定不满足要求,这样可以提高查询效率。
3.不要过多创建索引
过多的索引会占用更多的空间,而且每次增、删、改操作都会重建索引。
在一般的互联网场景中,查询语句的执行次数远远大于增删改语句的执行次数,所以重建索引的开销可以忽略不计。但在大数据量导入时,可以考虑先删除索引,批量插入数据,然后添加索引。
尽量扩展索引,比如现有索引(a),现在又要对(a,b)进行索引,那么只需要修改索引(a)即可,避免不必要的索引冗余。
4.索引长度尽量短
短索引可以节省索引空间,使查找的速度得到提升,同时内存中也可以装载更多的索引键值。
太长的列,可以选择建立前缀索引
5.索引更新不能频繁
更新非常频繁的数据不适宜建索引,因为维护索引的成本。
6.索引列不能参与计算
不要在索引列上做任何的操作,包括计算、函数、自动或者手动类型的转换,这样都会导致索引失效。
比如,where from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成where create_time = unix_timestamp(’2014-05-29’)。
查询时的优化
小表驱动大表
第一张表是全表索引(要以此关联其他表),其余表的查询类型type为range(索引区间获得),也就是6 * 1 * 1,共遍历查询6次即可;
建议使用left join时,以小表关联大表,因为使用join的话,第一张表是必须全扫描的,以少关联多就可以减少这个扫描次数.
这里所说的表的type,指的是explain执行计划中的结果字段。详情点击查看,explain的属性详解与提速百倍的优化示例
避免全表扫描
mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描。在查询的时候,如果对索引使用不等于的操作将会导致索引失效,进行全表扫描
避免mysql放弃索引查询
如果mysql估计使用全表扫描要比使用索引快,则不使用索引。(最典型的场景就是数据量少的时候)
使用覆盖索引,少使用select*
需要用到什么数据就查询什么数据,这样可以减少网络的传输和mysql的全表扫描。
尽量使用覆盖索引,比如索引为name,age,address的组合索引,那么尽量覆盖这三个字段之中的值,mysql将会直接在索引上取值(using index),并且返回值不包含不是索引的字段。
如果查询select的列过多,覆盖索引的效率会大大下降,这时可以考虑放弃覆盖索引查询。
order by的索引生效
order by排序应该遵循最佳左前缀查询,如果是使用多个索引字段进行排序,那么排序的规则必须相同(同是升序或者降序),否则索引同样会失效。
不正确的使用导致索引失效
如果查询中有某个列的范围查询,则其右边所有列都无法使用索引。
for update锁表
A, B两个事务分别使用select … where … for update进行查询时:
- A事务执行查询操作的时候,如果这个查询结果为空,无论where条件是否是索引字段,B事务执行查询操作时,不会被阻塞。
- A事务执行查询操作的时候,当where条件是索引字段,则B事务执行同样的查询时会被行加锁阻塞;当where条件不是索引字段,则B事务执行有结果集的查询,都会被阻塞。
for update操作一定要谨慎,之前笔者就遇到过for update产生gap锁,导致后续请求阻塞的问题。
之后的博客单独介绍MySQL的锁机制,同时讲解下更多死锁的情况。
其他优化
开启慢查询
开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,更好的优化数据库系统的性能。
实时获取有性能问题的SQL
利用information_schema数据库的processlist表,实时查看执行时间过长的线程,定位需要优化的SQL。
例如下面的SQL的作用是查看正在执行的线程,并按Time倒排序,查看执行时间过长的线程。
select * from information_schema.processlist where Command != ‘Sleep’
order by Time desc;
垂直分割
“垂直分割”是一种把数据库中的表,按列变成几张表的方法。这样可以降低表的复杂度和字段的数目,从而达到优化的目的。
示例一:
在Users表中有一个字段是address,它是可选字段,并且不需要经常读取或是修改。
那么,就可以把它放到另外一张表中,这样会让原表有更好的性能。
示例二:
有一个叫 “last_login”的字段,它会在每次用户登录时被更新,每次更新时会导致该表的查询缓存被清空。
所以,可以把这个字段放到另一个表中。
这样就不会影响对用户ID、用户名、用户角色(假设这几个属性并不频繁修改)的不停地读取了,因为查询缓存会增加很多性能。
拆分执行时间长的DELETE或INSERT语句
避免在生产环境上执行会锁表的DELETE或INSERT的操作。一定把其拆分,或者使用LIMIT条件也是一个好的方法。
拆分大SQL
下面是一个示例:
while (1) { //每次只做1000条 mysql_query(“DELETE FROM logs WHERE log_date
<= ‘2009-11-01’ LIMIT 1000”); if (mysql_affected_rows() == 0) { //
没得可删了,退出! break; } // 每次都要休息一会儿 usleep(50000); }
五、 MySql的数据结构B+Tree
数据库索引存放在磁盘上,当数据量过大,利用索引查询的时候,就需要按需加载(足一加载磁盘页,也就是索引树的节点)。
B+TREE:多叉自平衡查找树,所有数据节点都位于叶子节点,叶子节点之间依靠指针相连。
联合索引底层数据结构:
最左前缀原则
根据上面的图解,不难理解最左前缀的原理。
对于username,age,birth联合索引,只有当索引用到username时,才会继续使用下面的索引,不然整个索引无效。
所以:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。
InnoDB主键索引和非主键索引的区别
1.主键索引结构叶子节点存储的是整行数据。
2.非主键索引结构叶子节点存储的是主键值(相当于联合索引里面只有一个索引,见联合索引图)。—这么做的目的(一致性和节省存储空间)
- 一致性:对于数据的修改,只要修改主键对应的数据即可,这样就能保住数据是一致的。
- 节省存储空间:非主键索引不用存储整行的数据。
有一个注意点是非主键索引其实是查二次索引,第一次查到的是主键值,然后依靠主键值再去查到数据,这也就是为什么普通索引执行效率就比主键索引要低的原因。
关于回表查询
命中索引后,数据库必须使用主键值去去聚集索引中查找其他行数据,这就是回表。
关于覆盖索引
查找的叶子节点已经包含了需要查询的数据,不需要再次进行回表查询。
关于索引数量
索引并非是越多越好,创建索引也需要耗费资源,一是增加了数据库的存储空间,二是在插入和删除时
要花费较多的时间维护索引.(注意:查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,使用全表扫描)
InnoDB与MyISAM的区别
1.InnoDB 支持事务,MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;
2.InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;
3.InnoDB 是聚集索引,MyISAM 是非聚集索引。
4.InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
5.InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
MySIAM索引(非聚簇索引)和InnoDB索引(聚簇索引)
MyISAM索引文件和数据是分离的。索引存储文件指针–>指向数据文件。
InnoDB
- 数据文件本身就是索引文件–即索引和数据在一起。
- 表的数据文件本身就是按B+Tree组织的一个索引结构文件。
- 聚簇索引-叶子节点包含了完整的数据记录。
- InnoDB必须要主键的原因:表的数据文件本身就是按B+Tree组织的一个索引结构文件,如果实在没有主键,MySql会默认选一个字段。理论上,MySql必须要设置一个主键的,并且推荐使用整型的自增主键