mysql体系结构及原理(innodb)

MySQLMySQLMySQLMySQL体系结构及原理(innodb)(innodb)(innodb)(innodb)六个问题：宏观认识1.1.1.1.为什么deletedeletedeletedeletefromtable_namefromtable_namefromtable_namefromtable_name速度，3MB/S3MB/S3MB/S3MB/S左右alter一张表，有多快呢？rename呢？六个问题：宏观认识2.2.2.2.为什么innodbinnodbinnodbinnodb系统ioioioio能力为2002002002001.本文所有内容，围绕mysql5.1版本。Innodb版本为plugin版本。六个问题：宏观认识3.3.3.3.为什么机械磁盘的iops=180iops=180iops=180iops=1801.为什么移动硬盘，在copy数据，声音很大。有人用ssd的移动硬盘么？U盘没声音？六个问题：宏观认识4.Insert4.Insert4.Insert4.Insert一条记录，会产生物理ioioioio读操作么？1.Update呢，会有物理读io操作么..六个问题：宏观认识5.merge5.merge5.merge5.merge为什么只适用于非唯一索引的insertinsertinsertinsert？INSERTBUFFERANDADAPTIVEHASHINDEX-------------------------------------Ibuf:size738,freelistlen16770,segsize17509,28472919inserts,28349854mergedrecs,15294548merges六个问题：宏观认识6.6.6.6.为什么建立索引的字段，不允许默认为NULLNULLNULLNULL？应用程序mysql、dbproxy宏观认识OSOSOSOSlinux、windows、unix、mac硬件内存、CPU、外设、系统(数据)总线、网卡环境网络环境、上下游环境1.每一层出现问题，都有可能导致整个系统异常。2.系统的处理能力，取决于每一层自身的处理能力。外存储分类：机械磁盘((((磁介质))))、电介质存储存储的性能吞吐量衡量顺序读、写能力。每秒，读写量大小。MB/SIOPS(吞吐率)衡量随机读、写能力。每秒，IO次数。IOPSOLAP系统，顺序读写需求高。看吞吐量。iqiyi.com。视频网站。OLTP系统，随机读写需求高。看IOPS。baifubai.com。数据库系统。每秒，顺序读写量远远大于随机读写量。思考：如何测试，随机读写、顺序读写？15KRPMSAS盘，15K/60=250转/秒宏观认识1.Innodb为什么要存2份数据。事务日志和ibd?2.oltp数据库设计只因数据库随机io不给力?存储的性能普通机械硬盘结构图SSDFusionIOiodrive/PCI-E宏观认识设备IOPS接口7200RPMSATAdrives~90IOPSSATAII15kRPMSCSIdrives~180IOPSSASIntelX25-MG2(MLC)~8,600IOPSSATAIIioDrive,aPCI-ExpresscardwithFlashwithFlash140,000ReadIOPS,135,000WriteIOPS PCIeFusion-ioioDriveOctal1,180,000+RandomRead/WriteIOPSPCIe宏观认识存储的性能宏观认识存储的性能宏观认识1.10K,2.5寸，sas盘，随机iops，能跑到300么?2.拍桌子，计算，还是测试？1.存储的读写能力，受限于存储的自身处理能力。2.就整个系统而言，同样受限于接口的吞吐。3.writethrough模式，5块ssd，做raid5，RAID卡是否能发挥效率，还是会拖后腿，成为瓶颈。宏观认识1.SELECTusernaFROMfc.userinfoWHEREpa=2;如果query_cache命中;该sql有没有进入语法解析流程?2.Innodb做语法解析了么?MySQL体系结构MySQL体系结构MySQL体系结构内存用户clientmysqldservermysql–uuser_name–puser_passwd–h10.290.38.25–P9598innodbbufferpool各种处理线程磁盘数据文件日志MySQL体系结构innodb_additional_mem_poolinnodbbufferpoolinnodb_log_bufferdatapageinsertbufferthreadkey_buffer自适应哈希索引insertbufferindexpage锁信息per-threadbufferssort_bufferjoin_bufferbinlogcacheread_bufferlogthreadreadthreadwritethread锁监控线程错误监控线程master线程binlogibd*errorlog全日制slowlogrelaylogib_logfile内存连接线程io/dump/sqlthreadquerycacheMySQL体系结构innodb_additional_mem_poolinnodbbufferpoolinnodb_log_bufferdatapageinsertbufferthread自适应哈希索引insertbufferindexpage锁信息logthreadreadthreadwritethread锁监控线程错误监控线程master线程系统表空间内存独立表空间事务日志系统表空间：ibdata1、ibdata2innodb_data_file_path=ibdata1:500M;ibdata2:50M:autoextendinnodb_data_file_path=ibdata1:500M;ibdata2:50M:autoextendinnodb_data_file_path=ibdata1:500M;ibdata2:50M:autoextendinnodb_data_file_path=ibdata1:500M;ibdata2:50M:autoextend独立表空间：id_user.ibd、id_user.frminnodb_file_per_tableinnodb_file_per_tableinnodb_file_per_tableinnodb_file_per_table事物日志：ib_logfile0、ib_logfile1MySQL体系结构系统表空间内存独立表空间事务日志系统表空间：insertbuffer、doubelwrite、数据字典、undo独立表空间：表数据、索引innodbbufferpool1.insertbuffer在内存中，还是在磁盘中?2.聚集索引、非聚集索引在磁盘中？自适应hash在磁盘中？Innodb有几个线程Innodb内部，有loop,backgroundloop,flushloop,suspendloop等后台循环。insertbufferthreadlogthreadreadthreadwritethread锁监控线程错误监控线程master线程master_thread(){master_thread(){master_thread(){master_thread(){gotoloop;gotoloop;gotoloop;gotoloop;loop:loop:loop:loop:for(i=0;i10;i++){for(i=0;i10;i++){for(i=0;i10;i++){for(i=0;i10;i++){thread_sleep(1);thread_sleep(1);thread_sleep(1);thread_sleep(1);dologbufferflush;dologbufferflush;dologbufferflush;dologbufferflush;if(last_one_second_ios5%innodb_io_capacity)if(last_one_second_ios5%innodb_io_capacity)if(last_one_second_ios5%innodb_io_capacity)if(last_one_second_ios5%innodb_io_capacity)domerge5%insertbufferdomerge5%insertbufferdomerge5%insertbufferdomerge5%insertbufferif(modified_pctdirty_pct)if(modified_pctdirty_pct)if(modified_pctdirty_pct)if(modified_pctdirty_pct)dobufferpoolflush100%dobufferpoolflush100%dobufferpoolflush100%dobufferpoolflush100%elseifelseifelseifelseif当前所需的dirtypageflushdirtypageflushdirtypageflushdirtypageflush速度大于过去20s20s20s20s平均的刷脏页的速度，dobufferpoolflush100%dobufferpoolflush100%dobufferpoolflush100%dobufferpoolflush100%if(nouseractivity)if(nouseractivity)if(nouseractivity)if(nouseractivity)gotobackgroundloopgotobackgroundloopgotobackgroundloopgotobackgroundloop}}}}}}}}If(last_ten_second_iosinnodb_io_capacity)If(last_ten_second_iosinnodb_io_capacity)If(last_ten_second_iosinnodb_io_capacity)If(last_ten_second_iosinnodb_io_capacity)dobufferpoolflush100%dobufferpoolflush100%dobufferpoolflush100%dobufferpoolflush100%domerge5%insertbufferdomerge5%insertbufferdomerge5%insertbufferdomerge5%insertbufferdologbuferflush;dologbuferflush;dologbuferflush;dologbuferflush;dofullpurge;undopagedofullpurge;undopagedofullpurge;undopagedofullpurge;undopage删除。If(modified_pct70%)If(modified_pct70%)If(modified_pct70%)If(modified_pct70%)dobufferpoolflush100%;dobufferpoolflush100%;dobufferpoolflush100%;dobufferpoolflush100%;elseelseelseelsedobufferpoolflush10%;dobufferpoolflush10%;dobufferpoolflush10%;dobufferpoolflush10%;dofuzzycheckpointdofuzzycheckpointdofuzzycheckp