学习笔记1：数据库优化（优化的关联方面）

1.超高的qps和tps
    qps：每秒处理的查询量
    tps：每秒传输事务处理个数
    优化sql，慢查询

2.大量的并发和超高的cpu使用率
大量的并发
    数据库连接数占满（max_connections）
超高的cpu使用率
    cpu资源耗尽而宕机
    
3.磁盘io    
    磁盘io性能突然下降（使用更快的磁盘设备）
    调整大量消耗磁盘性能的计划任务


4.网卡流量：
    网卡io被占满
    如何避免无法连接数据库
    （1.减少从服务器数量。2页面缓存。3sql。4分离业务网络和服务网络）

5.大表
    单表记录超过千万行
    单表数据超过10G

6.大事务
    
===================================================
服务器硬件
    cpu：    64位cpu运行在64位系统下
            cpu计算密集型场景和复杂sql：频率越高越好
            并发量高：数量比频率（cpu质量）更加重要
            mysql版本：5.0前版本，没用，新版本，可以选择
            32位|64位：
    内存：    越多越好，最好是能把数据100%缓存就可以了
            内存的主频和服务器的主频类似的，频率越高，速度越快
            选择主板所能使用的最高频率内存
            
    磁盘配置|选择：
                PCIe->ssd（固态硬盘，成本大）->Raid（硬盘阵列）->普通硬盘->SAN（网络存储）
            （1）传统机器硬盘                1.存储容量，2.传输速度，3访问时间，4主轴转速，5物理尺寸
            （2）raid增强传统硬盘
            （3）固态硬盘ssd和pcie卡：大量随机io场景。数据量大于内存cache，热数据量。        支持更好的并发
            （4）网络存储和san：数据库备份
    
    网络性能：    服务器和数据库是网络连接的，内网下，带宽足够，不过也是看服务器的数量。
                网络质量：  采用高性能高带宽的网络接口设备和交换机
                            对多个网卡进行绑定，增强可用性和贷款
                            进行网络隔离
        延迟
        吞吐量（带宽）：
        
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

服务器系统
    window
    
    linux-centos系统参数优化：
        (/etc/sysctl.conf):    net.core.somaxconn=65535                每个端口最大的监听队列长度
                            net.core.netdev_max_backlog=65535        在每个网络接口接收数据包的速率比内核处理机器包的速率快的时候，允许未发送到队列的数据包最大的数目
                            net.ipv4.tcp_max_syn_backlog=65535        还未获得对方链接的请求可保存在队列中的最大数目，对于超过这个大小的链接请求，就会抛弃
                    
                            net.ipv4.tcp_fin_timeout=10        用于控制tcp链接处理的等待状态的时间，对于链接比较频繁的系统，有大量的链接处于等待状态，减少状态的timeout时间，加快tcp链接回收速度
                            net.ipv4.tcp_tw_reuse=1
                            net.ipv4.tcp_tw_recycle=1        加快tcp链接的回收
                            
                            net.core.wmem_default = 8388608    tcp链接的缓冲默认值和最大值
                            net.core.rmem_default = 8388608
                            net.core.rmem_max = 16777216
                            net.core.wmem_max = 16777216
        (/etc/security/limit.conf):
                            ext3/4
                            
                            xfs（推荐选择使用）
        文件系统：


---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------

数据库存储引擎    
mysql（插件式存储引擎）
    
        【客户端】        ：java,php
        【mysql服务层】 ：连接管理器：查询缓存 ，查询解析 ， 查询优化
                select语句：获取存储文件中的数据，根据过滤条件过滤。
        【存储引擎层】      
                innodb_file_per_table（推荐on）    :  
                                            比较:系统表空间无法简单的收缩文件大小
                                            on -》则会为每个innodb表建立一个以    tablename.ibd 扩展的系统文件，独立的表空间
                                            off-》存储到共享系统表空间 ibdatax
                                            off：使用表空间进行数据存储，系统表空间会差生io瓶颈
                
                redolog：存储已提交事务，顺序写入，事务持久性    show variables like 'innodb_log_buffer_size'
                undolog：存储未提交事务，随机读写
                锁：作用管理共享资源的并发访问
                    用于实现事务的隔离性
                【共享锁（读锁）】
                【独占锁（写锁）】
                【锁的粒度】：锁的策略，被加锁的最小单位，行级锁，页级锁，表级锁
                【阻塞】不同锁之间兼容性的关系，一个事务中的锁需要等待另一个事务中的锁释放，他所占用的资源，阻塞事务占用被阻塞事务的资源。
                【死锁】两个或两个以上的事务在执行过程中，相互占用的对方的等待资源，而产生的一种异常，多格式，相互占用对方的等待资源。
                        死锁可由系统自动处理
                        
        【选择存储引擎】：除了特殊需求外，尽量选择innodb        
            【全文索引】：mysql5.7前，只有MYISAM支持
            ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);
            在MySql中创建全文索引之后，现在就该了解如何使用了。众所周知，在数据库中进行模糊查询是使用like关键字进行查询的，例如：
            SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;
            那么，我们在使用全文索引也这样使用吗？当然不是，我们必须使用特有的语法才能使用全文索引进行查询，例如，我们想要在article表的title和content列中全文检索指定的查询字符串，我们可以如下编写SQL语句：
            SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);
            强烈注意：MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效。
                    此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。
                    如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。
            注：目前，使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。
                另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。
            注：如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。                
                
            【事务】
            【备份】
            【崩溃恢复】
            【存储引擎的特性】：不要混合使用存储引擎
                混合使用存储引擎，myisam，innodb，事务中，回滚只有innodb可以恢复，myisam不会恢复
                无法实现完全的在线热备
                
                

---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
                
数据库参数配置
    【mysql获取配置信息路径】：
            【命令行参数】： mysqld_safe --datadir=/data/sql_data
            【配置文件】:读取配置文件的顺序
                        mysqld --help --verbose | grep -A 1 'Default options'
                        /etc/my.cnf   /etc/mysql/my.cnf    /usr/local/mysql/etc/my.cnf   ~/.my.cnf
    【mysql配置参数作用域】：
            【全局参数】：mysql下进行配置
                            set global 参数名=参数值;
                            set @@global.参数名:=参数值;
            【会话参数】：mysql下进行配置
                            set [session] 参数名=参数值;
                            set @@session.参数名:=参数值;
    【内存配置相关参数】：
            【确定主要为操作系统保留多少内存】：尽量数据库使用专用服务器
            【确定可以使用的内存上限】：
                【确定mysql每个连接可以使用的内存】：
                    为每个线程分配的，有100个线程*分配的值，考虑是否会内存浪费/溢出
                    sort_buffer_size:（排序缓存区的大小，查询排序操作时分配内存，查询需要排序，mysql会分配sort_buffer_size指定大小的全部内存，而不管排序是否需要）
                    join_buffer_size:（连接缓冲区的大小，mysql 每个线程所使用的连接缓冲区的大小，如果一个查询关联多张表，那么就会为每个关联分配一个连接缓冲区，所以
                                        每个查询可能有多个连接缓冲，所以也不能设置过大）
                    
                    read_buffer_size:（对一个myisam表进行扫描时所分配的读缓冲池的大小,设置参数一定要是4k倍数 ）
                    read_rnd_buffer_size:（索引缓冲区的大小，有查询需要时为该缓冲区分配内存）

                【如何为缓冲池分配大小】：
                    innodb_buffer_pool_size:    {总内存-（每个线程所需要的内存*连接数）-系统保留内存}
                                                尽量服务器内存的75%以上
                    key_buffer_size:myisam表索引所用的大小
            
    【I/O配置相关参数】：    
                innodb在事务提交时，会写入事务日志，而不是每次都把修改后的数据刷新到数据文件中，这样会提高i/o
                innodb_log_file_size：控制单个事务日志的大小（事务繁忙，可以设置大点）
                innodb_log_file_in_groug：控制事务文件的个数
                【事务日志的总大小】：= innodb_log_file_in_groug*innodb_log_file_size
                innodb_log_buffer_size：日志缓冲区的大小。（事务提交，并不是每次提交都写入事务日志中，而是先写到事务日志的缓冲区，然后刷新到磁盘中）
                
                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数据文件和日志文件如何跟文件系统进行交互。影响innodb的写入读取数据     
                innodb_file_per_table:    【1】：控制innodb控制表空间，1，为每个表单独建立表空间。2，使用系统表空间
                innodb_doublewrite：    【1】：控制是否启用双写缓冲，双写缓冲增加数据安全性            
                            
                            
                            
    【安全相关配置参数】
            expire_logs_days：        指定自动清理binlog日志的天数
            max_allowed_packet：    控制mysql可以接受包的大小
            skip_name_resolve：        禁用DNS查找（建议关闭）
            sysdate_is_now:            确保sysdate()返回确定性日期
            read_only:                禁止非super权限的用户写权限
            skip_slave_start:        禁用slave自动恢复
            sql_model:                设置mysql所使用的sql模式
                    【strict_trans_tables】:如果给定的数据不能插入事务存储引擎中，则中断当前操作
                    【on_engine_subtitution】:如果在createtable语句中，所指的存储引擎不可用的情况下
                    【no_zero_date】：不能把 0000-0000-0000 的日期写入表中
                    【no_zero_in_date】：不接收 0000-0000-0000 的日期写入表中


    【其他常用配置参数】
            sync_binlog：控制mysql如何向磁盘刷新binlog
                        0                    =》mysql不主动刷新，而是由操作系统决定什么时候刷新cache
                        大于0,>0       =》两次刷新到磁盘的操作之间，间隔的多少次二进制日志的写操作
                        
            tmp_tanle_size：        控制内存临时表大小
            max_heap_table_size：    控制内存临时表大小
            max_connections：        控制允许的最大连接数（2000）


---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
            
数据库结构设计和sql语句
    【数据库设计对设计的影响】
            【过分的反范式化为表建立太多的列】
            【过分的范式化造成太多的表关联】：mysql最多允许关联61张表
            【OLTP环境中使用不恰当的分区表】
            【使用外键保存数据完整性】：不要使用



---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------

性能优化顺序
    1.数据库结构设计和sql语句
    2.数据库存储引擎和参数配置
    3.系统选择和优化
    4.硬件升级


---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------

参数优化
    innodb_buffer_pool_size                越大越好。考虑其他程序的占用，redis等，一般是内存的75%左右，按实际情况分配
    
    innodb_buffer_pool_instances        将innodb_buffer_pool划分为不同的instance，大内存的时候可以设置，提供并发io，降低锁的征用、
    
    innodb_log_file_size    
    
    innodb_log_buffer_size                先写入innodb_log_buffer，buffer写满或事务提交，刷新数据。大事务频繁，增加innodb_log_buffer_size大小
    
    innodb_thread_concurrency            默认安装配置下，innodb_thread_concurrency = 0，innodb内部自己控制并发数。
                                        实际情况下，大并发下innodb_thread_concurrency设置为cpu的核心数

    innodb_io_capacity                    innodb每秒后台进程处理IO操作的数据页上限
                                        innodb_buffer_pool_size总的io处理能力上限
                                        innodb_buffer_pool_instances分割成多个内存块时，每个内存块的IO处理能力为：innodb_io_capacity/innodb_buffer_pool_instances

    innodb_max_dirty_pages_pct            innodb从innodb buffer中刷新脏页的比例
                                        刷新脏页，产生checkpoint
                                        脏页刷新innodb_max_dirty_pages_pct * innodb_io_capacity
                                        
    innodb_flush_method                    刷新数据的方式
                                        O_DSYNC：使用O_SYNC打开和刷新log文件，使用fsync()刷新数据文件。
                                        O_DIRECT：使用O_DIRECT打开数据文件，使用fsync()刷新日志文件和数据文件。

    innodb_file_per_table                不同的表空间可以灵活设置数据目录的地址
                                        避免共享表空间产生的IO竞争

    innodb_flush_log_at_trx_commit        关联    innodb_flush_method
                                        0：每秒将log buffer的内容写事务日志并且刷新到磁盘；
                                        1：每个事务提交后，将log_buffer的内容写事务日志并数据磁盘；
                                        2：每个事务提交，将log_buffer内容写事务日志，但不进行数据刷盘
                                        
    sync_binlog                            测试下，非主项目，可以写1000等等，每1000条日志，刷新binlog
                                        刷新binlog的数目
                                        双1模式，即：innodb_flush_log_at_trx_commit = 1，sync_binlog = 1，这样主备的数据是一致的，不会丢失数据
                                        双一模式，可以保证一致，不过效率影响



系统优化
    NUMA                                linux内的程序内存访问分配

    malloc                                向系统申请分配指定size个字节的内存空间
    
    网卡优化
    
    内存插法                     6根内存在4通道里的插法为：2/2/1/1，简称42插法
                                        6根内存在4通道里的插法为：2/2/2/0，简称33插法
                                        HP/DELL/华为/英业达对比：
                                        HP/DELL/华为保持42插法性能会比33插法性能高。NUMA开启，QPS提升8-20%；NUMA关闭，QPS值能提升12-38%。
                                        英业达无论何种插法都表现良好