1.影响数据库性能的几个方面
服务器硬件
服务器系统
数据库存储引擎的选择（MySQL的插件式存储引擎）
MyISAM：不支持事务，表级锁。
InnoDB：事务级存储引擎，完美支持行级锁，事务ACID特性。
数据库参数配置
数据库结构设计和SQL语句的编写和优化
2.CPU资源和可用内存大小（服务器硬件）
对MySQL性能有影响的硬件资源：CPU资源和可用内存大小
（1）目前版本的MySQL不支持多CPU对同一SQL的并发处理。
（2）内存的大小直接影响了数据库的效率。
（3）MyISAM把索引缓存到内存中，而数据通过操作系统来进行缓存。
（4）InnoDB会同时在内存中缓存数据和索引，从而提高数据库运行效率。
（5）内存虽然是越多越好，但是对性能的影响是有限的，并不能通过增加内存来无限的增加性能。
（6）在读取数据时，先读取缓存，缓存没有再读取硬盘；在写入数据时，也可以将数据写入缓存，然后将多次写入变成一次写入，将数据一次性从缓存写入硬盘。
（7）选择内存时应该选择服务器主板支持的最大内存频率，频率越高速度越快。
3.磁盘的配置和选择
无论如何，数据最终都要在磁盘上实现永久存储，所以IO子系统比内存更加重要。
常用的磁盘IO系统：
使用传统机器硬盘：
特点：最常见，使用最多，价格性对低，存储空间较大，读写速度较慢。
传统机器硬盘读取数据的过程：
移动磁头到磁盘表面上的正确位置
等待磁盘旋转，使的所需的数据在磁头之下
等待磁盘旋转过去，所有所需的数据都被磁头读出
（1，2为访问时间。3为传输时间）
如何选择传统机器硬盘：
存储容量
传输速度
访问时间
主轴转速
物理尺寸
使用RAID增强传统机器硬盘的性能：
概述：RAID是磁盘冗余队列的简称。简单来说RAID的作用就是可以把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。
常用RAID级别：
RAID 0：最早出现的RAID模式，也称之为数据条带。是组建磁盘阵列中最简单的一种形式，只需要2块以上的硬盘即可，成本低，可以提高整个磁盘的性能和吞吐量。RAID 0没有提供冗余或错误修复能力，但是实现成本是最低的。
RAID 1：又称为磁盘镜像，原理是把一个磁盘的数据镜像到另一个磁盘上，也就是说数据在写入一块磁盘的同时，会在另一块闲置的磁盘上生成镜像文件，在不影响性能情况下最大限度的保证系统的可靠性和可修复性。
RAID 5：又称之为分布式奇偶校验磁盘阵列。通过分布式奇偶检验块把数据分散到多个磁盘上，这样如果任何一个盘数据失效，都可以从奇偶校验块中重建。但是如果两块磁盘失效，则整个卷的数据都无法恢复。
RAID 10 ：又称分片的镜像。它是对磁盘先做RAID 1之后对两种RAID 1的磁盘再做RAID 0，所以对读写都有良好的性能，相对于RAID 5 重建起来更简单，速度也更快。
使用固态存储SSD和PCIE卡：
特点：相比机械磁盘固态磁盘有更好的随机读写性能，能更好的支持并发，但是也更容易损坏。
使用场景：
适用于存在大量随机I/O的场景
适用于解决单线程负载的I/O瓶颈
使用网络存储NAS和SAN：
含义：NAS和SAN是两种外部文件存储设备加载到服务器上的方法。
区别：
SAN设备通过光纤连接到服务器，设备通过块接口访问，服务器可以将其当做硬盘使用。顺序读写快，随机读写慢。
NAS设备使用网络连接，通过基于文件的协议如NFS或SMB来访问。
网络存储使用的场景：
并不适合MySQL数据库存储数据文件。
适合数据库备份
4.MySQL体系结构
客户端
MySQL服务层：包括连接管理器、查询缓存、查询解析、查询优化器。比如select语句也是在MySQL服务层来实现的。
存储引擎层：存储引擎是针对于表的而不是针对于库的（一个库中的不同表可以使用不同的存储引擎）
5.MySQL常用存储引擎之MyISAM
1.MySQL5.5之前版本默认存储引擎
2.MyISAM存储引擎表由MYD（数据文件）和MYI（索引文件）组成
3.MyISAM的特性：
并发性与所级别
表损坏修复
MyISAM表支持的索引类型（全文索引）
MyISAM表支持数据压缩
4.MyISAM的限制：
版本<MySQL5.0时默认表大小为4G
如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH
版本>MySQL5.0时默认支持为256TB
5.适用场景：
非事务型应用
只读类应用
空间类应用
6.MySQL常用存储引擎之Innodb
Innodb使用表空间进行 数据存储
参数：innodb_file_per_table
参数为on，则表示独立表空间：tablename.ibd；
参数为OFF，则表示系统表空间：ibdataX.
系统表空间和独立表空间要如何选择：
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table命令收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
建议：
对Innodb使用独立表空间
把原来存在于系统表空间中的表转移到独立表空间中的方法：
使用mysqldump导出所有数据库表数据
停止MySQL服务，修改参数，并删除Innodb相关文件
重启MySQL服务，重建Innodb系统表空间
重新导入数据
Innodb存储引擎的特性
Innodb是一种事务型存储引擎
完全支持事务的ACID特性
日志类型：Redo Log 和 Undo Log
Redo Log 实现事务的持久性 存储的是已经提交的事务（提交失败或者回滚），顺序写入。
Undo Log 实现的是未提交的事务，随机读写。
Innodb支持行级锁
行级锁可以最大程度的支持并发
行级锁是由存储引擎层实现的
什么是锁
锁的主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性
锁的类型
共享锁（读锁）
独占锁（写锁）
锁的粒度
表级锁
行级锁
阻塞和死锁
什么是阻塞：事务中的兼容性关系，比如一个事务的锁需要等待另一个事务的锁的释放。
什么是死锁：两个或者两个以上的事务，在执行过程中，相互占用了对方等待的资源。数据库系统会自动发现死锁，可以由系统自动处理。处理方式是：将死锁中占用资源最少的事务回滚，能使其他事务进行下去。
Innodb状态检查
提供了一个独特的性能监控工具：show engine innodb status。如果想使用要在两次间隔时间至少30秒。
使用场景
MySQL5.7版本之后开始支持全文索引和空间函数。
7.MySQL常用存储引擎之CSV
文件系统存储特点：
数据以文本方式存储在文件中
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息
特点：
以CSV格式进行数据存储
所有列必须都是不能为NULL的
不支持索引，不适合大表，不适合在线处理
可以对数据文件直接编辑，保存文本文件内容
使用场景：
适合作为数据交换的中间表。可以将电子表格（excel）存储为CSV文件，存储到MySQL数据目录下。
8.MySQL常用存储引擎之Archive
文件系统存储特点：
以zlib对表数据进行压缩，磁盘I/O更少
数据存储在ARZ为后缀的文件中
Archive存储引擎的特点：
只支持insert和select操作
只允许在自增ID列上加索引
使用场景：
日志和数据采集类应用
9.MySQL常用存储引擎只Memory
文件系统存储特点：
也称为HEAP存储引擎，所以数据保存在内存中。
功能特点：
支持HASH索引和BTree索引
HASH索引适合等值查找
BTree索引适合范围查找
所有字段都为固定长度 varchar（10）= char（10）
不支持BLOG和TEXT等大字段
Memory存储引擎使用表级锁
最大大小由max_heap_table_size参数决定
容易混淆的概念：
Memory存储引擎表：
临时表：
系统使用临时表：
超过限制使用Myisam临时表
未超限制使用Memory表
create temporary table 建立的临时表：
使用场景：
用于查找或者是映射表，例如邮编和地区的对应表
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表
10.MySQL常用存储引擎之Federated
特点：
提供了访问远程MySQL服务器上表的方法
本地不存储数据，数据全部放到远程服务器上
本地需要保存表结构和远程服务器的连接信息
如何使用：
默认禁止，启用需要在启动时增加federated参数
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
user_name,password:本地连接到远程的用户名和密码
host_name:远程MySQL服务器的IP
port_num:远程MySQL服务器的端口号
db_name:我们所要在本地去映射的服务器的名字
tbl_name:映射的表的名字
使用场景：
偶尔的统计分析及手工查询
11.如何选择正确的存储引擎
参考条件：
事务
备份
崩溃恢复
存储引擎的特有特性
12.MySQL服务器参数
MySQL获取配置信息路径：
命令行参数：mysqld_safe -- datadir=/data/sql_data
配置文件
MySQL配置参数的作用域
全局参数
set global 参数名=参数值；
set @@global.参数名 ：=参数值；
会话参数
set【session】参数名 = 参数值；
set@@session.参数名：= 参数值；
内存配置相关参数：
确定可以使用的内存的上限
确定需要为操作系统保留多少内存
如何为缓存池分配内存
Innodb_buffer_pool_size
总内存 - （每个线程所需要的内存*连接数）- 系统保留内存
key_buffer_size（MyISAM）
确定MySQL的每个连接使用的内存
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
安全相关配置参数：
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_tables
no_engine_subtitution
no_zero_date
no_zero_in_date
only_full_group_by
网友评论