搜索
MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区MySQL数据库查询优化技术第14周MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X):《数据库查询优化器的艺术：原理解析与SQL性能优化》23Blog:：@那海蓝蓝Email:database_XX@126.com5Direction:DB(PostgreSQL,MySQL.etc)6MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区课程大纲：1数据库与关系代数2数据库查询优化技术总揽3查询优化技术理论与MySQL实践（一）------子查询的优化（一）4查询优化技术理论与MySQL实践（二）------子查询的优化（二）5查询优化技术理论与MySQL实践（三）------视图重写与等价谓词重写6查询优化技术理论与MySQL实践（四）------条件化简7查询优化技术理论与MySQL实践（五）------外连接消除、嵌套连接消除与连接消除8查询优化技术理论与MySQL实践（六）------数据库的约束规则与语义优化9查询优化技术理论与MySQL实践（七）------非SPJ的优化10MySQL物理查询优化技术概述、表扫描与连接算法与MySQL多表连接优化实践11MySQL索引的利用、优化12查询优化的综合实例------TPCH实践13关系代数对于数据库的查询优化的指导意义14MySQL的优化技术总结15MySQL优化的问题分析、方法和思考TheQueryOptimizationTechniqueOfMySQL(5.6.X)数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区Lesson14:OtherOptimization------MySQL的优化技术总结TheQueryOptimizationTechniqueOfMySQL(5.6.X)数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)硬件层的优化2存储引擎优化3表设计优化4InnoDB优化5库级优化6数据获取方式的优化7利用索引优化8暂存中间结果的三种优化方式9控制查询优化器10重新组织数据1112其他连接技术MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)硬件层的优化1CPU:个数/核数/频率/线程数/一级cache/二级cache2内存:容量与64-bits/带宽3I/O:seek(100次/秒)/read/write(10–20MB/s)4网络:带宽/传输协议MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)存储引擎优化1InnoDB：1)innodb_buffer_pool_size：cachingdataandindexesinmemory，可配置为可用内存的（非物理内存）的50%--60%2)innodb_buffer_pool_instances：配合innodb_buffer_pool_size使用，把buf分区，增加并发度2MyISAM：1)key_buffer_size：IndexblocksforMyISAMtablesarebufferedandaresharedbyallthreads，可配置为可用内存的10-%-20%2)table_open_cache：Thenumberofopentablesforallthreads.使用“SHOWGLOBALSTATUSLIKE'Opened_tables';”命令检查“Opened_tables”的值，太小则改大Ifyoumakethevaluetoolarge(forexample,morethan50%ofthemachine'stotalmemory),yoursystemmightstarttopageandbecomeextremelyslow.ThisisbecauseMySQLreliesontheoperatingsystemtoperformfilesystemcachingfordatareads,soyoumustleavesomeroomforthefilesystemcache.---正确选择存储引擎MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)储存引擎优化秘诀：磁盘类数据库，瓶颈在IO；必须优先调整和IO有关的参数。解决IO瓶颈的方法，就是缓存；必须优先调整和缓存相关的参数。MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)表设计优化1表的存储引擎选择：事务型选InnoDB，非事务型选MyISAM等2表的压缩选择：压缩的表IO少，CPU空闲IO瓶颈大课采取压缩3表结构符合第三范式：更新类应用可让表的个数多些单表的列少一些；分析类的应用可让表个数少些单表的列多些4表数据物理分布策略：尽量让表的数据分散在不同的物理存储设备上。利用表空间技术把数据分散5表数据逻辑分布策略：利用分区技术把数据从逻辑上即分开6表的数据类型选择：数字类型优于字符类型；长度小的优于长度大的。变长的VARCHAR优于定长的CHAR。BLOB类型用BINARYVARCHAR替代，替代不了则用单独的表存放。如果比较BLOB类，则新建字段其值等于用MD5()处理后的结果。BOLB类型甚至不存放到数据库内部，数据库只存储BLOB的路径。7启用完整性约束：使用NOTNULL标识字段;设置defaultvalue。8其他：列名不超过18个字符。使用samplecharacterset（如用latin1，尽量少用utf-8等，因为utf-8等占用的空间是latin1的3倍）MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)优化1单表容量优化：OPTIMIZETABLEstatementtoreorganizethetableandcompactanywastedspace。2单表统计数据优化：ANALYZETABLEtpch.customer;3启用压缩：测试各级压缩哪种有效4应用尽量使用短事务减少使用长事务：应用程序控制5事务管理：5.1写操作多：SETAUTOCOMMIT=0oraSTARTTRANSACTIONstatement,followedbyaCOMMITstatementaftermakingallthechanges.5.2读操作多：SETAUTOCOMMIT=16加大日志文件和日志缓存：innodb_log_buffer_size+innodb_log_file_sizeMySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)优化7主键建立：使用最重要且是最常用的列作主键，务必建立主键而不是使用InnoDB默认方式8主键列的数据类型不要太长：浪费存储空间和内存，浪费其他索引的空间和内存9有效建立索引：除主键外，尽量建立联合索引而不是多个单列上分别建立secondaryindex10删除数据释放空间：UseTRUNCATETABLEtoemptyatable,notDELETEFROMtbl_name.11数据刷出的方式：InsomeversionsofGNU/LinuxandUnix,flushingfilestodiskwiththeUnixfsync()call(whichInnoDBusesbydefault)andsimilarmethodsissurprisinglyslow.Ifdatabasewriteperformanceisanissue,conductbenchmarkswiththeinnodb_flush_methodparametersettoO_DSYNC.其他刷出方式参考：optimization.html#optimizing-innodb-storage-layoutMySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)库级优化1同一个库中表不要太多：设置table_open_cache和max_connections来调整。WithMyISAMtables,oneextrafiledescriptorisrequiredforthedatafileforeachclientthathasthetableopen.(Bycontrast,theindexfiledescriptorissharedbetweenallsessions.)MySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)库级优化2启用查询缓存：适用于特定场景.Ifyouoftenhaverecurringqueriesfortablesthatarenotupdatedfrequently,enablethequerycache:[mysqld]query_cache_type=1query_cache_size=10MMySQL数据库查询优化技术讲师那海蓝蓝DATAGURU专业数据分析社区TheQueryOptimizationTechniqueOfMySQL(5.6.X)库级优化3使用长连接：避免频繁使用短连接.推荐启用连接池.设置thread_cache_size：8+(max_connections/100)--defaultvalueThisvariablecanbeincreasedtoimproveperformanceifyouhavealotofnewconnections.Normally,thisdoesnotprovideanotableperformanceimprovementifyouhaveagoodthreadimplementation.However,ifyourserverseeshundredsofconnectionspersecond