阅读本文大概需要 6 分钟。
1.1 影响资料库查询速度的四个因素
1.2 风险分析
QPS: QueriesPerSecond意思是「每秒查询率」,是一台伺服器每秒能够相应的查询次数,是对一个特定的查询伺服器在规定时间内所处理流量多少的衡量标准。TPS: 是 TransactionsPerSecond的缩写,也就是事务数/秒。它是软体测试结果的测量单位。客户机在发送请求时开始计时,收到伺服器响应后结束计时,以此来计算使用的时间和完成的事务个数。
QPS: QueriesPerSecond意思是「每秒查询率」,是一台伺服器每秒能够相应的查询次数,是对一个特定的查询伺服器在规定时间内所处理流量多少的衡量标准。
QueriesPerSecond
TransactionsPerSecond
Tips: 最好不要在主库上资料库备份,大型活动前取消这样的计划。
sql
QPS
TPS
max_connection
100
CPU
IO
1.3 网卡流量:如何避免无法连接资料库的情况
select*
1.4 大表带来的问题( 重要)
重要
1.4.1 大表的特点
10
G
1.4.2 大表的危害
1.慢查询:很难在短时间内过滤出需要的数据 查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁碟 io -> 降低磁碟效率
io
2.对 DDL影响:
DDL
建立索引需要很长时间:
MySQL-v<5.5
MySQL-v>=5.5
mysql
修改表结构需要长时间的锁表:会造成长时间的主从延迟(480秒延迟)
1.4.3 如何处理资料库上的大表
分库分表把一张大表分成多个小表
难点:
1.5 大事务带来的问题( 重要*)*
1.5.1 什么是事务
1.5.2事务的 ACID属性
ACID
1、原子性( atomicity):全部成功,全部回滚失败。银行存取款。 2、一致性(consistent):银行转账的总金额不变。3、隔离性(isolation):
1、原子性( atomicity):全部成功,全部回滚失败。银行存取款。
atomicity
2、一致性(consistent):银行转账的总金额不变。
隔离性等级:
READ UNCOMMITED
READ COMMITED
REPEATABLE READ
InnoDB的默认隔离等级
SERIALIZABLE
查看系统的事务隔离级别: show variables like%iso%;
show variables like%iso%
开启一个新事务: begin;
begin
提交一个事务: commit;
commit
修改事物的隔离级别: setsession tx_isolation=read-committed;
setsession tx_isolation=read-committed;
4、持久性( DURABILITY):从资料库的角度的持久性,磁碟损坏就不行了
DURABILITY
edolog机制保证事务更新的一致性和持久性
edolog
1.5.3 大事务
运行时间长,操作数据比较多的事务;
风险:锁定数据太多,回滚时间长,执行时间长。
解决思路:
SELECT
2.1 影响性能的几个方面
MyISAM
InnoDB
资料库结构设计和SQL语句。(重点优化)
2.2 MySQL体系结构
分三层:客户端->服务层->存储引擎
MySQL
插件式的存储引擎
2.3 InnoDB存储引擎
MySQL5.5及之后版本默认的存储引擎: InnoDB。
MySQL5.5
2.3.1 InnoDB使用表空间进行数据存储。
show variables likeinnodb_file_per_table
如果innodbfileper_table 为 ON 将建立独立的表空间,文件为tablename.ibd;
如果innodbfileper_table 为 OFF 将数据存储到系统的共享表空间,文件为ibdataX(X为从1开始的整数);
.frm :是伺服器层面产生的文件,类似伺服器层的数据字典,记录表结构。
.frm
2.3.2 (MySQL5.5默认)系统表空间与( MySQL5.6及以后默认)独立表空间
MySQL5.6
optimeze table
强烈建立对Innodb 使用独立表空间,优化什么的更方便,可控。
2.3.3 系统表空间的表转移到独立表空间中的方法
或者 Altertable 同样可以的转移,但是无法回收系统表空间中占用的空间。
Altertable
2.4 InnoDB存储引擎的特性
2.4.1 特性一:事务性存储引擎及两个特殊日志类型:Redo Log 和 Undo Log
Innodb
RedoLog
UndoLog
Redo Log: 实现事务的持久性(已提交的事务)。 Undo Log: 未提交的事务,独立于表空间,需要随机访问,可以存储在高性能io设备上。
Undo日志记录某数据被修改前的值,可以用来在事务失败时进行 rollback; Redo日志记录某数据块被修改后的值,可以用来恢复未写入 data file的已成功事务更新的数据。
Undo
rollback
Redo
data file
2.4.2 特性二:支持行级锁
2.5 什么是锁
2.5.1 锁
2.5.2 锁类型
2.5.3 锁的粒度
MySQL的事务支持不是绑定在MySQL伺服器本身, 而是与存储引擎相关
而是与存储引擎相关
将table_name加表级锁命令: locktable table_name write; 写锁会阻塞其它用户对该表的『读写』操作,直到写锁被释放: unlock tables;
locktable table_name write
写锁会阻塞其它用户对该表的『读写』操作,直到
unlock tables
2.5.4 阻塞和死锁
(1)阻塞是由于资源不足引起的排队等待现象。 (2)死锁是由于两个对象在拥有一份资源的情况下申请另一份资源,而另一份资源恰好又是这两对象正持有的,导致两对象无法完成操作,且所持资源无法释放。
2.6 如何选择正确的存储引擎
参考条件:
Innobd
总结: Innodb 大法好。
注意: 尽量别使用混合存储引擎,比如回滚会出问题在线热备问题。
注意:
2.7 配置参数
2.7.1 内存配置相关参数
确定可以使用的内存上限。内存的使用上限不能超过物理内存,否则容易造成内存溢出;(对于32位操作系统,MySQL只能试用3G以下的内存。)确定MySQL的 每个连接 单独 使用的内存。
确定可以使用的内存上限。
单独
sort_buffer_size #定义了每个线程排序缓存区的大小,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存); join_buffer_size #定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲,导致一个查询产生了多个连接缓冲; read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小,MySQL有查询需要时会为其分配内存,其必须是4k的倍数; read_rnd_buffer_size #索引缓冲区大小,MySQL有查询需要时会为其分配内存,只会分配需要的大小。
注意: 以上四个参数是为一个线程分配的,如果有100个连接,那么需要×100。
注意:
MySQL资料库实例: ①MySQL是 单进程多线程(而oracle是多进程),也就是说 MySQL实例在系统上表现就是一个服务进程,即进程; ②MySQL实例是线程和内存组成,实例才是真正用于操作资料库文件的;一般情况下一个实例操作一个或多个资料库;集群情况下多个实例操作一个或多个资料库。
MySQL资料库实例:
单进程多线程
如何为缓存池分配内存:
Innodb_buffer_pool_size,定义了Innodb所使用缓存池的大小,对其性能十分重要,必须足够大,但是过大时,使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁碟中;
Innodb_buffer_pool_size
总内存-(每个线程所需要的内存*连接数)-系统保留内存
key_buffer_size,定义了MyISAM所使用的缓存池的大小,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间;
key_buffer_size
select sum(index_length) from information_schema.talbes where e``ngine=``myisam
注意: 即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表。
max_connections 控制允许的最大连接数, 一般2000更大。
max_connections
不要使用外键约束保证数据的完整性。
2.8 性能优化顺序
从上到下:
作者:唐立勇
出处:https://segmentfault.com/a/1190000013672421
·END·
程序员的成长之路
路虽远,行则必至