Mysql实例 数据库优化--结构和性能优化

三.数据库结构设计

当开发人员设计好表语句后，就需要运维工程师进行服务部署，项目上线。这里应该根据需求进行预估访问量，再进行配置的选择和结构设计。

项目初期访问量一般是寥寥无几，此阶段Web+数据库单台部署足以应对在1000左右的QPS（每秒查询率）。考虑到单点故障，应做到高可用性，可采用MySQL主从复制+Keepalived实现双机热备。主流HA软件有：Keepalived（推荐）、Heartbeat。

四.数据库性能优化

硬件配置选择

硬盘 磁盘寻道能力（磁盘I/O）,以目前高转速SCSI硬盘(7200转/秒)为例，服务器硬盘读200M/S，写120M/S。而固态硬盘读1500MB/S，写800MB/S，这个差距是非常明显的。修改磁盘调度算法为deadline，echo ‘deadline’ >/sys/block/sda/queue/scheduler，查看结果# cat /sys/block/sda/queue/scheduler noop anticipatory [deadline] cfq

MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一。解决这一制约因素可以考虑以下几种解决方案：

1. 使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，性能会很差
2. 资金充足，可单独对数据库服务器选择固态方式来提高性能

CPU CPU对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用8颗Intel Xeon 3.6GHz的CPU。数据库对于CPU的需求没有内存这么大，通常64G内存，只需要8核CPU就可以了。如果是单实例的mysql，可以在/etc/grub.conf配置文件中，加入参数numa=off，禁用numa功能。

内存 物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于8GB，用以应对高速增长的咨询等信息。内存方面可以关闭swap功能， echo 0 >/proc/sys/vm/swappiness ，关闭swap功能

Linux内核有一个特性，会从物理内存中划分出缓存区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制，等满足条件时（如缓存区大小到达一定百分比或者执行sync命令）才会同步到磁盘。

也就是说物理内存越大，分配缓存区越大，缓存数据越多，建议物理内存至少富裕50%以上。

数据库配置优化

MySQL应用最广泛的有两种存储引擎：一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID属性），设计目标是为大数据处理，行级别锁。

表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。 行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。

用表锁和行锁，主要为保证数据完整性。例如，一个用户在操作一张表，其他用户也想操作这张表，那么就要等第一个用户操作完，其他用户才能操作，表锁和行锁就是这个作用。否则多个用户同时操作一张表，肯定会数据产生冲突或者异常。

根据这些方面看，使用InnoDB存储引擎是最好的选择，也是MySQL5.5+版本默认存储引擎。每个存储引擎相关运行参数比较多，以下列出可能影响数据库性能的参数。

公共参数默认值

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

MyISAM参数默认值

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

InnoDB参数默认值

1. #索引和数据缓冲区大小，建议设置物理内存的70%左右
2. innodb_buffer_pool_size = 128M
3. #缓冲池实例个数，推荐设置4个或8个
4. innodb_buffer_pool_instances = 1
5. #关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。
6. innodb_flush_log_at_trx_commit = 1
7. #是否共享表空间，5.7+版本默认ON，共享表空间idbdata文件不断增大，影响一定的I/O性能。建议开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。
8. innodb_file_per_table = OFF
9. #日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M
10. innodb_log_buffer_size = 8M

数据库安全优化

数据库安全是项目中最重要的部分，信息泄露会造成重大事故，所以要重视安全问题，防止信息被盗取、破坏。

具体建议

1. 数据库使用 mysql 用户启动，权限为700
2. 登录时在命令行暴漏密码，备份脚本中如果有密码，给设置 700，属主和用户组为 mysql或root
3. 初始删除无用的用户，只保留 root 127.0.0.1 和 root localhost
4. 授权用户对应的主机不要用%，权限不要给 all，最小化授权，从库只给
5. select 权限
6. 删除默认存在的 test 库
7. 为mysq用户设置复杂密码
8. 不要给一个用户管所有的库，尽量专库专用户
9. 清理 mysql 操作日志文件~/mysql_history
10. 禁止开发获取到 web 连接的密码，禁止开发连接操作生产对外的库
11. 服务器禁止设置外网IP
12. 防 SQL 注入（WEB）， php.ini 配置文件里面设置