mysql数据库优化框架体系
优化要有框架和体系：
根据用户访问流程优化集群，根据OSI7层模型，从下往上优化数据库！
1、硬件层面优化
2、操作系统层面优化
3、mysql数据库层面优化
4、网站集群架构优化
5、mysql流程、制度控制优化
6、mysql安全优化
1、硬件层面优化
1.1数据库物理机采购：
1.2服务器硬件配置调整
1.2.1服务器BIOS调整
1.2.2阵列卡调整
2、软件优化
2.1操作系统层面优化
2.1.1操作系统及mysql实例的选择
2.1.2文件系统层优化
2.1.3linux内核参数优化
2.2mysql数据库层面优化
2.2.1my.cnf参数优化
2.2.2关于库表的设计规范
2.2.3SQL语句的优化
3、网站集群架构上的优化
4、流程，制度，安全优化1、硬件层面优化
2、操作系统层面优化
3、mysql数据库层面优化
4、网站集群架构优化
5、mysql安全优化
6、mysql流程、制度控制优化
1、硬件层面优化
1.1数据库物理机采购：
a、CPU：64位CUP，一台机器2-16颗CPU。至少2-4颗，L2越大越好。
b、mem：96G-128G。3-4个实例。32G-64G，跑1-2实例。
c、disk（磁盘IO）
机械盘：选SAS，数量越多越好，转数越高越好15K。
性能：ssd（高并发） > sas（普通业务线上） > sata（线下）
选SSD：使用SSD或者PCIE SSD设备，可提升上千倍的IOPS效率。
随机IO：SAS单盘能力300IOPS SSD随机IO：单盘能力可达35000IOPS Flashcache HBA卡
b、raid阵列卡（磁盘IO）至少4块盘：RAID0>RAID10（推荐）>RAID5（少用）>RAID1
e、网卡 多块网卡bond，以及buffer，tcp优化。
f、千兆网线及千兆交换机。
g、数据库服务器器尽量不用虚拟化。
h、slave硬件要等于或大于master的性能。案例：
百度：某部门IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例;
sina：服务器是DELL R510居多，CPU是E5210,48GB内存，磁盘12*300G SAS做RAID10
1.2服务器硬件配置调整
1.2.1服务器BIOS调整：
提升CPU效率参考设置：
a、打开Performance Per Watt Optimized（DAPC）模式，发挥CPU最大性能，数据库通常需要高运算量；
b、关闭C1E和C States等选项，目的也是为了提升CPU效率；
c、Memory Frequency（内存频率）选择Maximum Performance（最佳性能）；
d、内存设置菜单中，启用Node Interleaving，避免NUNA问题；1.2.2阵列卡调整：
a、购置阵列卡同时配备CACHE及BBU模块（机械盘）
b、设置阵列写策略为WB，甚至FPRCE WB（对数据安全要求高）（wb指raid卡的写策略：回写（write back））
c、严禁使用WT策略，并且关闭阵列预读策略；2、操作系统层面优化
2.1操作系统及mysql实例选择
1、一定要选择x86_64系统，推荐使用CentOS6.8 linux，关闭NUMA特性。
2、将操作系统和数据分区分开，不仅仅是逻辑上，还包括物理上。
3、避免使用swap交换分区。
4、避免使用软件磁盘阵列。
5、避免使用LVM逻辑卷。
6、删除服务器上未使用的安装包和守护进程。2.1.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算法deadline
采用deadline调度参数
对于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
http://blog.csdn.net/wsdc0521/article/details/50173581
3、采用xfs文件系统，业务量不是很大也可以采用ext4，业务量很大推荐xfs；调整xfs文件系统日志和缓冲变量
5、mount挂在文件系统增加：async，noatime，nodiratime，nobarrier（不使用raid卡上电池）等选项；2.1.3linux内核参数优化
1、将vm.swappiness设置为0-10；
2、将vm.dirty_background_ratio设置为5-10，将vm.dirty_background_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 KeepAlive连接侦测的时间，使系统处理更多的连接。
net.ipv4.tcp_keepalive_time=600
#提高系统支持的最大SYN半连接数（默认1024）
net.ipv4.tcp_max_syn_backlog=16384
#减少系统SYN连接重试次数（默认5）
net.ipv4.tcp_synack_retries=1
net.ipv4.tcp_syn_retries=1
#在内核放弃建立连接之前发送SYN包的数量
##允许系统打开的端口范围
net.ipv4.ip_local_port_range=4500 65535
4、网络优化
#优化系统套接字缓冲区
#Incease 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=8388609
#优化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
net.core.somaxconn=32768
5、其他优化
net.ipv4.tcp_timestamps=0
net.ipv4.tcp_max_orphans=3276800
net.ipv4.tcp_max_tw_buckets=3600002.2mysql数据库层面优化
2.2.1my.cnf参数优化
1.如果采用MyISAM引擎，需要key_buffer_size加大。
2.强烈推荐采用InnoDB引擎，dafault-storage-engine=InnoDB，下面的优化主要针对innodb引擎。
3.调整innodb_buffer_pool_size大小，考虑设置为物理内存的50%~60%左右；
4.根据实际需要设置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求数据不能丢失，那么两个都要设置为1.如果允许丢一点数据，则可分别设为2和0。在slave可设为0。
5.设置innodb_file_per_table=1,使用独立表空间
6.设置innodb_data_file_path=indata1:1G:autoextend，不要用默认的10M；
7.设置innodb_log_file_size=256M，设置innodb_log_files_in_group=2，基本可满足90%以上的场景；
不要将innodb_log_file_size参数设置太大，这样可以更快同时又更多的磁盘空间-丢掉更多的日志通常是好的，在数据库崩溃后可以降低恢复数据库的时间。
8、设置long_query_time=1,记录那些执行较慢的SQL，用于后续的分析排查；
9、根据业务实际需要，适当调整max_connection（最大连接数）、max_connction_error（最大错误，建议设置为10万以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connction的大小）；
10、tmp_table_size、max_head_table_size、sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等都是每个连接session分配置的，因此不能设置过大；
11、建议关闭query cache功能或降低设置不要超过512M；
更多：http://blog.51cto.com/oldboy/1726517
参数配置好了，可以有一些软件，给你建议，是不是合理。（mysqlreport）
2.2.2关于库表的设计规范
0、推荐utf-8字符集，虽然有人说谈没有latin1快。
1、固定字符串的列尽可能多用定长char，少用varchar。
存储可变长度的字符串时使用VARHAR而不是CHAR-节省空间，因此固定长度的CHAR，而VARCHAR长度不固定（UTF8不受此影响）。
2、所有的InnoDB表都要设计一个无业务用途的自增列做主键；
3、字段长度满足需求的前提下，尽可能选择长度小的；
4、字段属性尽量加上NOT NULL约束；
对于某些文本字段，例如“省份”或者“性别”，我们可以将他们定义为ENUM类型。
5、尽可能不使用TEXT/BLOG类型，确实需要的话，建议拆分到字表中，不要和主表放在一起，避免SELECT * 的时候读性能太差。
6、读数据时，只选取所需要的列，不要每次都SELECT *，避免产生严重的随机读问题，尤其是读到一些TEXT/BLOG列；如果其他数据经常被用于查询时，而BLOG/TEXT数据不是，就把BLOG/TEXT数据从其他数据分离出来。
7、对一个VARCHAR(N)列创建索引时，通常取50%（甚至更小）左右长度创建前缀索引就足以满足80%以上的查询需求了，没必要创建整列的全长度索引；
8、多用复合索引，少用多个独立索引，尤其是一些基数（Cardinality）太小（比如说，该列的唯一值总数少于255）的列就不要创建独立索引了。3、SQL语句的优化
a、索引优化
1)白名单机制--百度，项目开发，DBA参与，减少上线后的慢SQL数量。
抓出慢SQL
配置my.cnf
long_query_time=2
log-slow-queries=/data/3306/slow-log.log
log_query_not_using_indexes
按天轮询：slow-log.log
2)慢查询日志分析工具--mysqlsla或pt-query-digest(推荐)
pt-query-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfilter比较
3)每天晚上0点定时分析查询，发到核心开发，DBA分析，及高级运维，CTO的邮箱里。
DBA分析给出优化建议-->核心开发确认更改-->DBA线上操作处理。
4)定期使用pt-duplicate-key-checker检查并删除重复的索引。
定期使用pt-index-usage工具检查并删除使用频率很低的索引；
5)使用pt-online-sxhema-change来完成大表的ONLINE DDL需求；
6)有时候MySQL会是使用错误的索引,对于这种情况使用USE INDEX。
7)使用explain及set profile优化语句。
b.打的复杂的SQL语句拆分成多个小的SQL语句。
子查询，JOIN连表查询，某个表4000万条记录。
c.数据库是存储数据的地方，但是不是计算数据的地方。
对于数据计算，应用类处理，都要拿到前端应用解决。禁止在数据库上处理。
d.搜索功能，like‘%老男孩%’，一般不要用mysql数据库。
8、使用连接（JOIN）来代替子查询（Sub-Queries）
9、避免在整个表上使用count(*)，它可能锁住整张表。
10、多表联接查询时，关联字段类型尽量一致，并且都要有索引；
在WHERE子句中使用UNION代替子查询。
11、多表连接查询时，把结果集小的表（注意，这里是指过虑后的结果集，不一定是全表数据量小的）作为驱动表
12、多表联接并且有排序时，排序字段必须有是驱动表里的，否则排序列无法用到索引；
13、尽量去掉“IN”、"OR"“<>”
14、类似分页功能的SQL，建议先用主键关联，然后返回结果集，效率会高很多。4、网站集群架构上的优化
1、服务器上跑多实例，2-4个。
2、主从复制一主五从，采用mixed模式，尽量不要跨机房同步（尽量远程写本地读）。
3、定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异；
4、业务拆分：搜索功能，like “%老男孩%”，一般不要用mysql数据库。
5、业务拆分：某些业务应用nosql持久化存储，例如：memcahcedb，redis，ttserver。粉丝关注，好友关系等等。
6、数据库前端必须要加cache。例如：memcached，用户登录，商品查询。
7、动态的数据静态化。整个文件静态化，页面片段静态化。
8、数据库集群与读写分离。一主多从。通过程序或者dbproxy进行集群读写分离。
9、单表超过800万。拆库拆表。人工拆表拆库（登录、商品、订单）
10、百度，阿里国内前三公司会这样搞。
11、选择从库进行备份。
12、对数据库进行分库分表备份。5、流程，制度优化
任何一次认为数据库记录的更新，都要走一个流程；
a、人的流程：建表增删该字段插入记录--开发-->核心开发-->运维或DBA
b、测试流程：内网测试-->IDC测试-->线上执行
c、客户端管理，PHPMYADMIN.#6、安全优化
1、启动程序设置700，属主和用户组为mysql
2、为mysql超级用户root设置密码
3、如果要求严格可以删除root用户，创建其它管理用户，例如admin
4、登录时尽量不要在命令行暴露密码，备份脚本如果有密码，给设置为700，属主和用户组为mysql或root。
5、删除默认存在的test库
6、初始删除无用的用户，只保留
root 127.0.0.1
root localhost
7、授权用户对应的主机不要用%。权限不要给all，最小化授权，从库只给select。
8、不要一个用户管所有的库，尽量转库专用户
9、清理mysql操作日志文件~/.mysql_history（600，可以不删）
10、禁止开发获取到web连接的密码，禁止开发连接操作生产对外的库。
11、phpmyadmin安全
12、服务器禁止设置外网IP。
13、防SQL注入（WEB），php.ini或web开发插件控件，waf控制。http://www.cnblogs.com/yuwensong/archive/2013/03/26/2981965.html文档更新时间: 2019-02-06 17:32 作者：李延召
