大型项目MySQL性能优化实例

张翔上海爱可生信息技术有限公司MySQL高级技术顾问大型移动项目MySQL数据库性能优化项目背景应用环境：注册用户2亿，同时在线2000多万，活跃用户在230万连续高压力下单项业务操作的所有数据库响应时间和0.1s高访问量压力时的故障快速恢复少于5分钟每日大量用户LOGIN与LOGOUT（伴随庞大的信息查询）每日大量的互联网消息通信（自然人与机器人并存）MSSQL与MySQL同等压力下的极限性能对比平台环境数据库环境：单机MySQL应用环境（目前仅Master提供对外数据库服务）普通PC64位服务器，共享存储，32G内存，2路4核2.50HZcpu150GB的在线应用数据量，上TB的历史数据（统计经分等）单表最大7亿条记录，最大表容量55G平均1000个并发的Acitve连接每秒处理5000个Active的数据库R，2000个Active的数据库写W每秒1030次磁盘读，19+M磁盘数据读取量AgendaMySQL体系架构MySQL设计优化MySQL系统优化MySQL配置优化MySQL语句优化Agenda第一层MySQL体系架构MySQL体系结构MySQL存储特性通过选择存储引擎来更好的适应应用的特殊性能要求对你来说最重要的是什么?-密集读操作-复制-OLTP（联机事务处理）-在线备份-事务处理-数据仓库-性能-外键-可伸缩性-占用空间小-并发级别-行级别锁-索引类型-嵌入式-存储利用率-表级别锁-高可靠性-集群规划设计-引擎选择MyISAM引擎适用场景：•数据库端的并发数量不多（20%写80%读）•读操作比较多，而且都能很好的用到索引•SQL语句比较简单的应用•轻松达到TB级数据量存储的数据仓库规划设计-引擎选择InnoDB引擎适用场景：•数据库端的读写并发数量非常多•写操作比较多，TB级数据量应用•数据较小、索引不好利用的应用比较多（报表）•有外键、事务等需求的应用Agenda第二层MySQL设计优化MySQL数据库结构----规划设计•命名规则•字段类型•编码选择•其他注意的问题规划设计-命名规则•按照多数开发语言的命名规则。比如(myCustomer)•按照多数开源思想命名规则。比如(my_customer)•按照咱们中国人的思想。比如(我的客户)•随便的命名。比如(mycustomer)规划设计-字段类型•整型•浮点类型•时间日期类型•字符类型TINYINT、INT、BIGINTFLOAT、DOUBLEDECIMAL、NUMERICDATETIME、DATE、TIMESTAMPVARCHAR、CHARBLOB、TEXT、ENUM\规划设计-编码选择•单字节？西欧文字中欧、南欧、中东、等等latin1•多字节？中国、韩国、日本utf-8•只考虑汉字？中国gbk、gb2312ABC张あせ재욱•默认值•索引•反范式规划设计-其他问题规划设计-命名规则第三层MySQL系统优化MySQL数据库系统----选型优化越好的机器性能越好？文件系统越好性能越好？网络越好性能越好？版本越新性能越好？MySQL数据库系统—硬件与系统环境多核的CPU，主频高的CPU更大的内存，MySQL是个很喜欢内存的数据库共享磁盘,RAID阵列,ISCSI,NAS,本地磁盘,SSD使用合适的文件系统XFSZFSNTFSEXT3MySQL数据库系统—网络环境尽量将数据库整体系统部署在局域网内使用专有的网络协议SCI光缆保证网络的安全冗余双网线，提供安全冗余0.0.0.0多端口绑定监听SCIGigabitEthernetMySQL数据库系统—软件环境开启MySQL复制，实现读、写分离，负载均衡获得推荐的最新GA版本，利用BUG修复提升性能利用分区新功能进行大数据的数据拆分，等等规划设计-命名规则第四层MySQL配置优化MySQL数据库配置----全局参数设置key_buffer_sizeMyISAM索引缓冲，根据（keyreads/Key_read_requests）判断innodb_buffer_pool_sizeInnoDB数据、索引、日志缓冲最重要的引擎参数，根据（hitriatos和FILEI/O）判断wait_time_out线程连接的超时时间，尽量不要设置的很大max_connections允许服务器最大连接数，尽量不要设置很大thread_concurrency线程并发利用数量（cpu+disk）*2，根据（OS中显示的请求队列和tickets）判断注意：全局参数设置一经设置，随服务器启动预占用资源MySQL数据库配置----线程参数设置sort_buffer_size获得更快的--ORDERBY,GROUPBY,SELECTDISTINCT,UNIONDISTINCTread_rnd_buffer_size当根据键进行分类操作时获得更快的--ORDERBYjoin_buffer_sizeJoin连接使用全表扫描连接的缓冲大小，根据（Select_full_join）判断read_buffer_size全表扫描时为查询预留的缓冲大小，根据（Select_scan）判断tmp_table_size临时内存表超出设置，转化为磁盘表，根据（Created_tmp_disk_tables）判断注意：线程参数设置的小影响性能，设置的大会导致服务器swapInnoDB----专有优化参数innodb_log_file_size(默认5M)‏记录InnoDB引擎redolog的文件较大的值意味着较长的故障崩溃恢复时间Innodb_flush_method(默认fdatasync)Linux系统可以使用O_DIRECT处理数据文件，避免OS级别的CacheO_DIRECT模式提高数据文件和日志文件的IO提交性能innodb_flush_log_at_trx_commit(默认1)‏0表示每秒进行进行一次A和B操作。1表示在每次事务提交后执行一次A和B操作。2表示在每次事务提交后，执行一次B操作。A--LOG数据写到CACHEB--FLUSHLOG数据刷新到磁盘规划设计-SQL优化第五层MySQL语句优化语句优化-读语句性能差的读语句mysqlselectcount(*)astotalfromUserStatus_Logwhere1;+----------+|count(*)|+----------+|524288|+----------+1rowinset(1.68sec)mysqlInnoDB引擎随记录越大执行越慢CREATETABLE`UserStatus_Log`(`LogTime`datetimeNOTNULL,`UserId`int(11)NOTNULL,`MobileNo`bigint(20)DEFAULTNULL,`Sid`int(11)DEFAULTNULL,`OpType`tinyint(3)unsignedDEFAULTNULL,`RequestSource`smallint(6)DEFAULTNULL,KEY`IX_PS_UserStatusLog_UserId_LogTime`(`UserId`,`LogTime`),KEY`IX_PS_UserStatusLog_Sid`(`Sid`))ENGINE=InnoDBDEFAULTCHARSET=utf8;mysqlcreatetabletable_count-(table_namevarchar(64)notnulldefault''primarykey,-totalbigintunsignednotnulldefault0-)enginemyisam;QueryOK,0rowsaffected(0.01sec))mysqlselectcount(*)astotalfromUserStatus_Logwhere1into@total;QueryOK,1rowsaffected(0.36sec)mysqlinsertintotable_count(table_name,total)values(‘UserStatus_Log’,@total);QueryOK,1rowsaffected(0.00sec))mysqlselect*fromtable_countwheretable_name=‘UserStatus_Log’;+--------------------------+-----------+|table_name|total|+--------------------------+-----------+|UserStatus_Log|524288|+--------------------------+-----------+1rowinset(0.00sec)语句优化-读语句优化替代方法：mysqlexplain-selectcount(*)astotalfromUserStatus_Logwhere1;+----+--------------+----------------------+--------+------------------+---------+---------+-----------+---------+-----------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+--------------+----------------------+--------+------------------+---------+----------+----------+----------+----------------+|1|SIMPLE|UserStatus_Log|index|NULL|idx_id|4|NULL|524288|Usingindex|+----+--------------+-----------------------+--------+-------------------+--------+----------+----------+---------+-----------------+1rowinset(0.01sec)语句优化-查询分析器执行性能差的SQL分析结果虽然使用了索引，但是还是进行了全表扫描mysqlexplain-selecttable_name,totalfromtable_countwheretable_name=‘UserStatus_Log’;+----+--------------+----------------+---------+------------------+---------+----------+-----------+---------+-----------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+--------------+----------------+---------+------------------+---------+----------+----------+----------+-----------+|1|SIMPLE|table_count|system|primary|NULL|NULL|NULL|1||+----+--------------+----------------+---------+------------------+---------+----------+----------+----------+-----------+1rowinset(0.00sec)语句优化-查询分析器优化后的SQL执行性能分析因为仅有一行，这行的列值被优化器认为是常数。最多一行匹配，如const表执行，速度非常快！MySQLQueryAnalyze