影响性能的几个方面:
1、服务器硬件
2、服务器系统
3、数据库存储引擎的选择 （插件式存储引擎 ）
MyISAM:不支持事务，表级锁。
InnoDB:事务级存储引擎，完美支持行级锁，事务ACID特性
4、数据库参数配置 （重要）
5、数据库结构设计和SQL语句
慢查询，数据库表结构设计不合理
SQL语句的编写和优化
MyISAM 缓存: 索引->内存 数据->OS
InnoDB 缓存:索引，数据 ->内存
网络对性能的影响
1、网络带宽对性能的影响
2、网路质量对性能的影响
建议:采用高性能和高带宽的网络接口设备
对多个网卡进行绑定，增强可用性和带宽
尽可能的进行网络隔离
服务器硬件对性能的影响
CPU：
64位的CPU一定要工作在64位的系统下
对于并发比较高的场景CPU的数量比频率重要
对于CPU密集性场景和复杂SQL则频率越高越好
内存:
选择主板所能使用的最高频率的内存
内存的大小对性能很重要，所以尽可能的大
I/O子系统:
PCIe->SSD->Raid10>磁盘->SAN
Mysql体系结构：
客户端:
PHP,JAVA,C API .NET以及ODBC,JDBC等
Mysql服务层 :
Select语句：如何从文件中获得我们所要查询的数据，这个具体的实现的方式则是由下一层存储引擎层来实现
存储引擎层:
注意：存储引擎是针对于表的而不是针对于库的(一个库中的不同表可以使用不同的存储引擎)
MyISAM引擎:
MySql5.5之前版本默认存储引擎
临时表:在排序、分组等操作中，当数量超过一定的大小之后，由查询优化器建立的临时表。
MyISAM 存储引擎表由MYD和MYI组成
特性:
并发性与所级别 (表级锁)，对读写混合并发性不是很好，读的时候会加共享锁。共享锁不会阻塞共享锁。
表损坏修复,没有事务的日志 出错时使用check table tablename repair table tablename修复时需要停止mysql服务。
MyISAM表支持的索引类型:全文索引，在Mysql5.7之前唯一原生支持全文索引的官方引擎
MyISAM表支持数据压缩
命令行: myisampack
限制:
版本<MySQL5.0时默认表大小为4G
如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH
版本>MySQL5.0时默认支持为256TB
适用场景:
非事务型应用
只读类应用 支持对数据压缩
空间类应用 支持空间函数
Innodb 引擎
Mysql5.5及之后版本默认存储引擎
Innodb 使用表空间进行 数据存储
innodb_file_per_table 参数
ON:独立表空间:tablename.ibd
OFF:系统表空间:ibdataX X代表数字 从1开始
系统表空间和独立表空间要如何选择:
比较:
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table 命令 收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
建议:
对Innodb使用独立表空间 （默认）
Innodb存储引擎的特性:
1、Innodb是一种事务性存储引擎
完全支持事务的ACID特性，为了实现事务原子性、一致性、持久性 Innodb 用重做日志Redo Log 和回滚日志Undo Log实现
Redo Log 主要实现持久性 由两部分组成，一个是内存中的重做日志缓存区，有一个innodb_log_buffer_size 参数决定大小。另一个是重做日志文件 innodb_log_files_in_group。
Redo Log一般是顺序写入
Undo Log随机读写
2、Innodb 支持行级锁
行级锁可以最大程度的支持并发
行级锁是由存储引擎层实现的
什么是锁:
锁对主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性
锁的类型:
共享锁(也称读锁)
独占锁(也称写锁、排它锁)
只有读锁可以兼容。
锁的粒度: 被加锁的最小单位
表级锁 Mysql服务层实现 innodb 可以使用 lock table tablename write; unlock tables;
行级锁 只在存储引擎层实现
阻塞和死锁:
什么是阻塞: 因为锁之间的兼容性的关系，在有些时刻 一个事务中的锁需要等待另一个事务中的锁释它占用的资源。 阻塞是为了确保事务的可以并发且正常运行。
什么是死锁: 两个或两个以上的事务，相互占用对方的等待资源产生异常。会被发现，系统会自动处理。
Innodb状态检查:
show engine innodb status 命令
Innodb 适用场景:
Innodb适用于大多数OLTP应用 支持全文索引、空间函数
Mysql 其他存储引擎:
csv（以CSV格式进行数据存储、所有列不可为空，不支持索引，可以对数据文件直接编辑）、Archive(以zlib对表数据进行压缩,磁盘I/O更少、数据存储在ARZ为后缀的文件中、只支持insert和select操作，只允许在自增Id列上加索引、日志和数据采集类应用适用。)、
Memory(也称HEAP存储引擎，所以数据保存在内存中。重启后数据丢失，表结构不会丢失。IO效率比MyiSAM效率高，因为MyiSAM存储引擎只有索引数据存在内存中。 支持HASH索引(只能进行等值查找)和BTree（范围查找）索引。 所有字段都为固定长度varchar(10)=char(10)，不支持BLOG和TEXT等大字段，使用表级锁。 最大大小由max_heap_table_size 参数决定，但是已经存在的表不能生效。 用于查找或者是映射表，例如邮编和地区的对应表，用于保存数据分析中产生的中间表，用户缓存周期性聚合数据的结果表) (数据易丢失，所以要求数据再可生)
MySql服务器参数:
内存配置相关参数
1、确定可以使用的内存的上限 不能超过物理内存
2、确定MySQL的每个连接使用的内存 都是为每个线程分配的
sort_buffer_size 排序缓冲区的大小
join_buffer_size 连接缓冲区的大小
read_buffer_size myisam全表扫描时所分配的读缓冲区的大小 4K的倍数
read_rnd_buffer_size 索引缓冲区大小
3、确定需要为操作系统保留多少内存
4、如何为缓存池分配内存
Innodb_buffer_pool_size 建议服务器内存的75%以上
总内存-（每个线程所需要的内存连接数）-系统保留内存
key_buffer_size 用于MyiSAM 缓存MyiSam 表数据
I/O 相关配置参数
Innodb I/O相关配置
Innodb_log_file_size 控制事务日志大小
Innodb_log_file_in_group 控制事务日志个数
事务日志总大小=
Innodb_log_file_sizeInnodb_log_file_in_group
Innodb_log_buffer_size 32~128M 配置即可
Innodb_flush_log_at_trx_commit
0 每秒进行一次log写入cache，并flush log到磁盘 会丢失事务
1 [默认]:在每次事务提交执行log写入cache，并flush log到磁盘
2 [建议]:每次事务提交，执行log数据写入到cache,每秒执行一次flush log到磁盘
Innodb_flush_method=O_DIRECT 不要缓存数据，不要预读
Innodb_file_per_table = 1 使用独立表空间
Innodb_doublewrite =1 双写缓存增加安全性，对性能影响可以接受
MyISAM I/O相关配置
delay_key_write
OFF:每次写操作后刷新键缓冲中的脏块到磁盘
ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
All:对所有MyISAM表都是用延迟键写入 系统崩溃后 会对表损坏 (需要修复 repair 命令)
安全相关配置参数
expire_logs_days 指定自动清理binlog的天数
max_allowed_packet控制MySQL可以接受的包的大小
skip_name_resolve 禁用DNS查找
sysdate_is_now 确保sysdate()返回确定性日期
read_only 禁止非super权限的用户写权限
skip_slave_start 禁用Slave自动恢复
sql_mode 设置MySQL所使用的SQL模式
strict_trans_tabls
no_engine_subtitution
no_zero_date
no_zero_in_date
only_full_group_by
其他常用配置参数
sync_binlog 控制MySQL如何向磁盘刷新binlog
tmp_table_size 和max_heap_table_size 控制内存临时表大小
max_connetions 控制允许的最大连接数 默认为100
数据库设计对性能的影响
过分的反范式化为表建立太多的列
过分的范式化造成太多的表关联
在OLTP环境中使用不恰当的分区表
使用外键保证数据的完整性
总结: 性能优化顺序
数据库结构设计和SQL语句
数据库存储引擎的选择和参数配置
系统选择及优化
硬件升级
网友评论