云数据库十大经典案例总结与反思-罗龙九-阿里系

aDB•玄惭出自天龙八部•2010年加入淘宝DBA•2012年加入阿里云RDS•负责RDS线上的稳定•历年RDS双11的负责人•RDS专家服务组负责人一索引SQL优化案例三锁例四延迟参数优化案例六conn100%案例七cpu100%案例八iops100%案例九disk100%案例十mem100%例一索引dFROMuserWHEREmoLIMITmysql>explainSELECTuidFROM`user`WHEREmoIMIT0,1;************************1.row***************************id:1select_type:SIMPLEtable:usertype:ALLpossible_keys:NULLkey:NULLkey_len:NULLrows:707250Extra:Usingwhere执行时间mysql>SELECTuidFROM`user`WHEREmoIMIT0,1;CREATETABLE`user`(`uid`int(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'id',`pid`int(11)unsignedNOTNULLDEFAULT'0',`email`char(60)NOTNULL,`name`char(32)NOTNULLDEFAULT'',`mo`char(11)NOTNULLDEFAULT'',PRIMARYKEY(`uid`),UNIQUEKEY`email`(`email`),KEY`pid`(`pid`))ENGINE=InnoDBENGINE=InnoDBAUTO_INCREMENT=972600DEFAULTCHARSET=utf8;验证mo字段的过滤性mysql>selectcount(*)fromuserwheremo+----------+|count(*)|+----------+|0|+----------+1rowinset(0.05sec)mysql>altertableuseraddindexind_mo(mo);mysql>SELECTuidFROM`user`WHEREmoIMIT0,1;Emptyset(0.05sec)mysql>explainSELECTuidFROM`user`WHEREmoIMIT0,1\G;**********************1.row***************************id:1select_type:SIMPLEtable:usertype:indexpossible_keys:ind_mokey:ind_mokey_len:33ref:NULLdex例mysql>explainextendedselectuidfrom`user`wheremoimit0,1;mysql>showwarnings;Warning1：Cannotuseindex'ind_mo'duetotypeorcollationconversiononfield'mo'Note：select`user`.`uid`AS`uid`from`user`where(`user`.`mo`limit0,1LT)ENGINE=InnoDB;例mysqlexplainSELECTuidFROMuserWHEREmo=LIMIT0,1\G;***************************1.row***************************id:1select_type:SIMPLEtable:usertype:refpossible_keys:ind_mokey:ind_mokey_len:33ref:constrows:1Extra:Usingwhere;Usingindexmysql>SELECTuidFROM`user`WHEREmo=LIMIT0,1;Emptyset(0.00sec)12341234通过explain查看sql的执行计划及隐士转换数据类型类型以及字符集定义不当导致段库字段定义与应用程序参数定义出现不一致不支持函数索引,避免在查询条件加入函数:date(a.gmt_create)SQL审核所有上线的sql都要经过严格的审核，创建合适的索引案例二分页优化案例|Query|51|Sendingdata|select*案例二分页优化案例普通写法：ql优化写法：selectt1.*frombuyert1,(selectidfrombuyersellerid=100limit100000，5000)t2wheret1.id=t2.id;注意：需要在t表的sellerid字段上创建索引，id为表的主键createindexind_selleridonbuyer(sellerid);原始语句：selectid,...frombuyerwheresellerId=765922982andgmt_modified>='1970-01-01andgmt_modified<='2013-06-05limit255000,5000；优化后语句：selectt2.*案例二from(selectidfrombuyerwheresellerId=765922982andgmt_modifiedandgmt_modified<='2013-06-05limit255000,5000)t1,buyert2wheret1.id=t2.id查询时间：60Sà0.2S分页优化案例案例二子查询优化案例nameFROMemployeesWHEREemp_noINSELECTempnoFROMsalaries000WHEREsalary=5000);SELECTfirst_nameFROMemployeesemp,(SELECTemp_noFROMsalaries_2000WHEREsalary=5000)salWHEREemp.emp_no=sal.emp_no;化化Limit写法，避免分页查询给数据库带来性能影响使用Mysql5.6的版本，可以避免麻烦的子查询改写字段用SELECT*查询所有字段数据，只查询需要的字段数据112233、案例三锁级锁Innodb与Myisam级锁新Query|111|Usersleep|selectid,sleep(100)fromt_myisam|tingfortablelevellockupdatetmyisamsetidwhereidQueryWaitingfortablelevellockupdatet_myisamsetid=2whereid=1|Altertablet_myisamengine=innodb;ck数据库连接状态：QueryQueryQueryQueryQueryatetimedbdb据库中的运行时间加长3.选择升级到MySQL5.6版本，支持onlineddl期执行上述操作，比如创建索引，添加字段；3.结构变更期间，监控数据库的线程状态是否存在lockwait；4.ApsaraDB支持在DDL变更中加入waittimeout；1122||案例四只读实例架构数据库需要升级到5.6版本最多支持5个节点采用MySQL复制原生实现数据同步案例四DDL导致延迟ectloaddatadeletefromupdatefromTips通过执行showprocesslist查看连接的状态；锁会阻塞复制线程导致复制延迟；案例四资源问题导致延迟Tips压力：同步压力+只读业务压力否达到瓶颈；2.二看线程状态是否有锁；2.只读实例的规格不低于主实例；3.大事务拆分为小事务；4.DDL变更期间观察是否有大查询；1122、案例五参数优化案例五一个参数引发的血案背景介绍：地的业务系统迁移上云在rds上运行时间明显要比线下自建数据库运行时间要慢1倍割接延期的风险关键词：案例五一个参数引发的血案1、数据库跨平台迁移(PG->MySQL、ORALCE->MySQL)案例五一个参数引发的血案优化器版本：用户5.6，RDS的版本5.6ITCHmergeonindexmergeuniononindexmergesortuniononindexmergeintersectiononenginecondition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoinonloosescanonfirstmatchonbquerymaterializationcostbasedonuseindexextensionson案例五一个参数引发的血案确定SQL执行计划：rows=39900*1*1*140*285*1*1*1*1*1*1*1案例五一个参数引发的血案数配置用户配置：join_buffer_size=128Mread_rnd_buffer_size=128Mtmp_table_size=128Mjoin_buffer_size=1Mtmp_table_size=256K案例五一个参数引发的血案验证阶段：tmp_table_size由256K调整至128MB2.查看数据库版本和优化器规则；3.对比参数设置；4.对比硬件配置；1.Query_cache_size；4.Back_log1122六、案例六cpu100%案例六cpu100%慢SQL问题慢SQL问题引，子查询，隐士转换，分页改写等优化；发和管理运维优化锁等待；化，弹性升级，读写分离，数据库拆分等方式优化；Cpu100%最佳实践112233七、案例七conn100%案例七conn100%慢SQL问题慢SQL问题引，子查询，隐士转换，分页改写等优化；发和管理运维优化锁等待；池参数配置超出实例最大连接数；2.弹性升级RDS的规格配置；Conn100%最佳实践112233八、案例八iops100%慢SQL慢SQL问题.通过优化索引，子查询，隐士转换，分页改写等优化；DDL1.内存规格不足，弹性升级RDS的规格配置；Iops100%最佳实践112233九、案例九disk100%案例九disk100%成：数据文件，日志文件，临时文件问题问题1.采用optimizetable收缩表空间；