共计 6078 个字符，预计需要花费 16 分钟才能阅读完成。
1、数据库物理机采购
CPU：64 位 CPU，一台机器 2 -16 颗 CPU。至少 2 - 4 颗，L2（缓存）越大越好
内存：96-128G，MySQL 3- 4 个实例。32-64G，1- 2 实例
硬盘：机械：选 SAS，数量越多越好，转速越高越好 15k
性能：SSD（高并发）> SAS（普通业务线上）>SATA（线下）
选 SSD：使用 SSD 或者 PCIe SSD 设备，可提升上千倍的 IOPS 效率。
随机 IO：SAS 单盘能力 300IOPS SSD 随机 IO：单盘能力可达 35000IOPS Flashcache HBA 卡
raid 磁盘阵列：4 快盘：RAID0>RAID1(推荐)>RAID5(少用)>RAID1
主库选择 raid10，从库可选 raid5/raid0/raid10，从库配置等于或大于主库
网卡：使用多块网卡 bond，以及 buffer，tcp 优化
千兆网卡及千兆、万兆交换机
提示：
数据库属于 IO 密集型服务，硬件尽量不要使用虚拟化。
Slave 硬件要等于或大于 Master 的性能
2、企业案例：
百度：某部门 IBM 服务器为 48 核 CPU，内存 96GB，一台服务器跑 3~4 个实例：
sina：服务器是 DELL R510 居多，CPU 是 E5210，48GB 内存，硬盘 12*300G SAS，做 RAID10
3、服务器硬件配置调整
(1)服务器 BIOS 调整：
提升 CPU 效率参考设置：
a. 打开 Perfirmance Per Watt Optimeized（DAPC）模式，发挥 CPU 最大性能，数据库通常需要高运算量
b. 打开 CIE 和 C States 等选项，目的也是为了提升 CPU 效率
c. Memory Frequency（内存频率）选择 Maximum Performance（最佳性能）
d. 内存设置菜单中，启动 Node Interleaving，避免 NUMA 问题
(2)阵列卡调整：
a. 购置阵列卡同时配备 CACHE 及 BBU 模块(机械盘)
b. 设置阵列写策略为 WEB，甚至 OFRCE WB（对数据安全要求高）（wb 指 raid 卡的写策略：会写（write back））
c. 严禁使用 WT 策略，并且关闭阵列预读策略
2 操作系统层面优化
1. 操作系统及 MySQL 实例选择
1. 一定要选择 x86_64 系统，推荐使用 CentOS6.8 linux，关闭 NUMA 特性
2. 将操作系统和数据分开，不仅仅是逻辑上，还包括物理上
3. 避免使用 Swap 交换分区
4. 避免使用软件磁盘阵列
5. 避免使用 LVM 逻辑卷
6. 删除服务器上未使用的安装包和守护进程
2. 文件系统层优化
(1)调整磁盘 Cache mode
启用 WCE=1(Write Cache Enable),RCD=0(Read Cache Disable)模式
命令：sdparm -s WCE=1,RCD=0 -S /dev/sdb
(2)采用 Linux I/O scheduler 算法 deadlinedeadline 调度参数 对于 Centos Linux 建议 read_expire = 1/2 write_expire
echo 500 > /sys/block/sdb/queue/iosched/read_expire
echo 1000 > /sys/block/sdb/queue/iosched/write_expire
Linux I/ O 调度方法 Linux deadline io 调度算法
(3)采用 xfs 文件系统 业务量不是很大也可采用 ext4，业务量很大，推荐使用 xfs：调整 XFS 文件系统日志和缓冲变量 XFS 高性能设置
(4)mount 挂载文件系统 增加：async,noatime,nodiratime,nobarrier 等
noatime
访问文件时不更新 inode 的时间戳，高并发环境下，推线显示应用该选项，可以提高系统 I / O 性能
async
写入时数据会先写到内存缓冲区, 只到硬盘有空档才会写入磁盘, 这样可以提升写入效率！风险为若服务器宕机或不正常，会损失缓冲区中未写入磁盘的数据 解决办法：服务器主板电池或加 UPS 不间断电源
nodiratime
不更新系统上的 directory inode 时间戳，高并发环境，推荐显示该应用，可以提高系统 I / O 性能
nobarrier
不使用 raid 卡上电池
(5)Linux 内核参数优化 1. 将 vm，swappiness 设置为 0 -10 2. 将 vm，dirty_background_ratio 设置为 5 -10，将 vm，dirty_ratio 设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间 I / O 写，产生严重等待
3. 优化 TCP 协议栈
减少 TIME_WAIT，提高 TCP 效率
net.ipv4.tcp_tw_recyle=1
net.ipv4.tcp_tw_reuse=1
减少处于 FIN-WAIT- 2 连接状态的时间，使系统可以处理更多的连接 net.ipv4.tcp_fin_timeout=2
减少 TCP KeepAlived 连接侦测的时间，使系统可以处理更多的连接。
net.ipv4.tcp_keepalived_time=600
提高系统支持的最大 SYN 半连接数（默认 1024）
net.ipv4.tcp_max_syn_backlog = 16384
减少系统 SYN 连接重试次数(默认 5)
net,ipv4.tcp_synack_retries = 1
net.ipv4.tcp_sync_retries = 1
在内核放弃建立的连接之前发送 SYN 包的数量
net.ipv4.ip_local_prot_range = 4500 65535
允许系统打开的端口范围
4. 网络优化
优化系统套接字缓冲区
Increase TCP max buffer size
net.core.rmem_max=16777216 #最大 socket 读 buffer
net.core.wmem_max=16777216 #最大 socket 写 buffer
net.core.wmem_default = 8388608 #该文件指定了接收套接字缓冲区大小的缺省
值(以字节为单位)
net.core.rmem_default = 8388608
优化 TCP 接收 / 发送缓冲区
Increase Linux autotuning TCP buffer limits
net.ipv4.tcp_rmem=4096 87380 16777216
net.ipv4.tcp_wmem=4096 65536 16777216
net.ipv4.tcp_mem = 94500000 915000000 927000000
优化网络设备接收队列
net.core.netdev_max_backlog=3000
5. 其他优化
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_max_tw_buckets = 360000
提示：面试的时候说框架，然后说一两个小的优化参数即可 更多内核可以参考“跟老男孩学运维书的第三章”以及我们的博客，近期将会更新
3MySQL 数据库层面优化
my.cnf 参数优化
此优化主要针对 innodb 引擎
如果采用 MyISAM 引擎，需要 key_buffer_size 加大。
强烈推荐采用 innodb 引擎，default-storage-engine=Innodb
调整 innodb_buffer_pool_size 大小，考虑设置为物理内存的 50%-60% 左右
根据实际需要设置 inno_flush_log_at_trx_commit，sync_binlog 的值。
如果要需要数据不能丢失，那么两个都设为 1. 如果允许丢失大一点数据，
则可分别设为 2 和 0，在 slave 上可设为 0
设置 innodb_file_per_table = 1，使用独立表空间
设置 innodb_data_file_path = ibdata1:1G:autoextend, 不要使用默认的 10%
设置 innodb_log_file_size=256M, 设置 innodb_log_files_in_group=2，基本可
满足 90% 以上的场景；
不要将 innodb_log_file_size 参数设置太大，这样可以更快同时又更多的磁盘空间，
丢掉多的日志通常是好的，在数据库崩溃后可以降低恢复数据库的事件
设置 long_query_time = 1 记录那些执行较慢的 SQL，用于后续的分析排查；
根据业务实际需要，适当调整 max_connection（最大连接数 max_connection_error
（最大错误数，建议设置为 10 万以上，而 open_files_limit、innodb_open_files、
table_open_cache、table_definition_cache 这几个参数则可设为约 10 倍于
max_connection 的大小；）不要设置太大，会将数据库撑爆
tmp_table_szie、max_heap_table_size、sort_buffer_size、
join_buffer_size、read_buffer_size、read_rnd_buffer_size 等都是
每个连接 session 分配的，因此不能设置过大
建议关闭 query cache 功能或降低设置不要超过 512M
更多内核参数：my-innodb-heavy-4G.cnf 配置文件参数介绍
MySQL 工具 mysqlreport 我们可以使用工具来分析 MySQL 的性能 如何才能做到网站高并发访问? 生产环境中对于防范 DDOS 攻击的讨论 面试可能会问到 DOOS 攻击防护
关于库表的设计规范
推荐 utf- 8 字符集，虽然有人说谈没有 latin1 快
固定字符串的列尽可能多用定长 char，少用 varchar
存储可变长度的字符串使用 VARCHAR 而不是 CAHR— 节省空间，因为固定
长度的 CHAR，而 VARCHAR 长度不固定（UTF8 不愁此影响）
所有的 InnoDB 表都设计一个无业务的用途的自增列做主键
字段长度满足需求前提下，尽可能选择长度小的
字段属性尽量都加 NOT NULL 约束
对于某些文本字段，例如“省份”或者“性别”我们可以将他们定义为 ENUM 类型尽可能不使用 TEXT/BLOB 类型，确实需要的话，建议拆分到子表中，不要和主表
放在一起，避免 SELECT * 的时候读性能太差。
读取数据时，只选取所需要的列，不要每次都 SELECT * 避免产生严重的随机读
问题，尤其是读到一些 TEXT/BLOB 类型，确实需要的话，建议拆分到子表中，不要
和主表放在一起，避免 SELECT * 的时候读性能太差
对一个 VARCHAR（N）列创建索引时，通常取其 50%（甚至更小）左右长度创建前缀
索引就足以满足 80% 以上的查询需求了，没必要创建整列的全长度索引。
多用符合索引，少用多个独立索引，尤其是一些基础（Cardinality）太小
（如果说：该列的唯一值总数少于 255）的列就不要创建独立索引了。
4SQL 语句的优化
索引优化 1)白名单机制一百度，项目开发啊，DBA 参与，减少上线后的慢 SQL 数据 抓出慢 SQL, 配置 my.cnf
long_query_time = 2
log-slow-queries=/data/3306/slow-log.log
log_queries_not_using_indexs
按天轮询：slow-log.log
2)慢查询的日志分析工具——mysqlsla 或 pt-query-digest（推荐）
pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter
3)每天晚上 0 点定时分析慢查询，发到核心开发，DBA 分析，及高级运维，CTO 的邮箱里 DBA 分析给出优化建议 –> 核心开发确认更新 –>DBA 线上操作处理 4)定期使用 pt-duplicate-key-checker 检查并删除重复的索引 定期使用 pt-index-usage 工具检查并删除使用频率很低的索引 5)使用 pt-online-schema-change 来完成大表的 ONLINE DDL 需求 6)有时候 MySQL 会使用错误的索引，对于这种情况使用 USE INDEX 7)使用 explain 及 set profile 优化 SQL 语句
大的复杂的 SQL 语句拆分成多个小的 SQL 语句 子查询，JOIN 连表查询，某个表 4000 万条记录 数据库是存储数据的地方，但不是计算数据的地方 对数据计算，应用类处理，都要拿到前端应用解决。禁止在数据库上处理 搜索功能，like‘%oldboy%’一般不要用 MySQL 数据库 使用连接 (JOIN) 来代替子查询（Sub_Queries）避免在整个表上使用 cout(*)，它可能锁住整张表 多表联接查询时，关联字段类型尽量一致，并且都要有索引。在 WHERE 子句中使用 UNION 代替子查询 多表连接查询时，把结果集小的表（注意，这里是指过滤后的结果集，不一样是全表数据量小的）作为驱动表
爬虫获取数据的过程
5 网站集群架构上的优化
1. 服务器上跑多实例，2- 4 个（具体需要看服务器的硬件信息）
2. 主从复制一主五从，采用 mixed 模式，尽量不要跨机房同步（进程远程读本地写）
3. 定期使用 pt-table-checksum、pt-table-sync 来检查并修复 mysql 主从复制的数据差异
4. 业务拆分：搜索功能，like ‘%oldboy% ‘ 一般不要用 MySQL 数据库
5. 业务拆分：某些业务应用使用 nosql 持久化存储，例如：memcached、redis、ttserver
例如粉丝关注，好友关系等
6. 数据库前端必须要加 cache，例如：memcached，用户登录，商品查询
7. 动态的数据库静态化，整个文件静态化，页面片段静态化
8. 数据库集群与读写分离。一主多从，通过程序或 dbproxy 进行集群读写分离
9. 单表超过 800 万，拆库拆表。人工拆表拆库（登录、商品、订单）
10. 百度、阿里国内前三公司，会选择从库进行备份，对数据库进行分库分表
6MySQL 流程、制度控制优化
任何一次人为数据库记录的更新，都要走一个流程：
a. 人的流程：开发 –> 核心开发 –> 运维或 DBA
b. 测试流程：内网测试 –>IDC 测试 –> 线上执行
c. 客户端管理，phpmyadmin
MySQL 基础安全
1. 启动程序 700，属主和用户组为 MySQL
2. 为 MySQL 超级用户 root 设置密码
3. 如果要求严格可以删除 root 用户，创建其他管理用户，例如 admin
4. 登录时尽量不要在命令行暴露密码，备份脚本中如果有密码，给设置 700，
属主和密码组为 mysql 或 root
5. 删除默认存在的 test 库
6. 初始删除无用的用户，只保留
| root | 127.0.0.1 |
| root | localhost |
7. 不要一个用户管理所有的库，尽量专库专用户
8. 清理 mysql 操作日志文件~/.mysql_history（权限 600，可以不删）
9. 禁止开发获得到 web 连接的密码，禁止开发连接操作生产对外的库
10.phpmyadmin 安全
11. 服务器禁止设置外网 IP
12. 防 SQL 注入（WEB）php.ini 或 web 开发插件监控，waf 控制