腾讯大讲堂48-数据库查询优化浅析

腾讯大讲堂第四十八期研发管理部大讲堂主页：与讲师互动：数据库查询优化浅析Alexyang无线产品部20081013提纲应用级查询优化SQL语句的解析方式索引的本质以及调优分析执行计划提纲应用级查询优化SQL语句的解析方式索引的本质以及调优分析执行计划一、需要性能/查询优化的原因影响性能的因素：整个系统环境软件：操作系统、中间件、应用程序、数据库硬件：CPU、内存、磁盘、网络上面任何一个都可能成为系统的性能瓶颈系统级性能优化的点操作系统：虚拟内存、文件系统格式、系统参数（LINUX）中间件：日志、部署时去掉打印信息（打印信息对性能影响较大)、工作线程数、连接池、JAVA垃圾回收模式、JAVA内存设置应用程序：使用连接池、合理的业务设计、尽可能不要对数据库操作进行同步、JAVA程序的性能优化（参考网络资源）数据库：工作线程、I/O线程、内存配置等、索引的利用、SQL改写、服务器代码改进这里我们主要考虑以下两个点应用层存在问题。数据库层存在问题。合理的逻辑设计以某实际应用系统为例，原来任务管理模块并发用户数只有15个，该模块用来显示所有待处理的任务，以及每个任务的里程碑个数。经过检查，发现其实现如下：执行语句SELECTCOUNT(*)FROMTASK;selecttask_idfromtaskorderbytask_iddesc;对结果集中的每条记录{SELECT*FROMTASK_STEPWHERETASK_STEP.TASK_ID=?}分析：如果TASK表有10000条记录，需要向数据库发送10000个查询。假设通讯使用了100ms，每个查询的执行时间是20ms。使用的时间为：(100ms+20ms)*10000=1200s=20min解决方案selecttask_step.*fromtask,task_stepwheretask_step.task_id=task.task_idorderbytask.task_iddesc;备注：1）继续对task_step.*处理，减少网络通讯量。2）默认看到自己的任务，提供连接查询其他任务。尽量使用数据库提供的功能在某实际应用系统中，对于每个任务、日记等对象都有一个唯一的ID，该系统使用数据库的一个表的一条整型记录来维持。当新建一个任务时，取用该表的当前记录值，操作完毕后加1。为了防止不同对象使用相同ID，使用如下方法来保证：inttemp=0;synchronized{执行SELECT*FROMOBJECTID;temp=结果集中的idupdate结果集中的id（id+1）}insertintotaskvalues(temp,...);解决方案createsequencemyseqincrementby1;insertintotaskvalues(myseq.nextval,...);二、SQL语句的解析方式SQL语句的处理过程语法/词法分析负责解析SQL命令和过程性语句块过滤注释,解析参数使用成熟的工具（如YACC/LEX）生成灵活的语法规则定义便于语法的扩展与修改生成统一格式的语法树对应的语法结构Select*fromt1,t2,t3;par_from_tv_list_struct结构par_from_tv_list_t*from_tv_list;par_from_tv_t*from_tvpar_from_tv_list_struct结构par_from_tv_list_t*from_tv_list;par_from_tv_t*from_tvpar_from_tv_list_struct结构par_from_tv_t*from_tvpar_from_tv_struct结构T1par_from_tv_struct结构T2par_from_tv_struct结构T3规则2规则1语义分析数据库对象名字解析合法性检查内部ID的转换权限检查语法树数据结构的简化与预处理DDL语句分解为对系统表的DML视图对象的替换等代价优化器接受语义分析的输入统计信息的分析数据分布统计直方图代价的计算基于统计信息操作符号的选择基于成本的最优执行路径选择生成优化的执行计划基于操作符的执行计划物理操作符功能简单专一,完成特定的数据库操作如:连接,过滤,排序等执行计划操作符的组合,构成树型的执行计划执行时,数据从叶子向根流动在根汇集成结果集,或者得到结果状态计划被自动缓存,以备重用实例分析select*fromA,B,C,DwhereA.a＝B.bandB.b1=C.candC.c1=D.dandA.a1123三、索引的本质以及调优需要了解的概念索引聚集索引、非聚集索引唯一索引、非唯一索引单列索引、组合索引Clusteredtable/indexlusteredindexonaclusteredtablelusteredindexonaclusteredtablelusteredindexonaclusteredtablelusteredindexonaclusteredtable索引调优的6个知识点1、将索引和数据存放到不同的文件组2、组合索引的使用3、唯一索引与非唯一索引的差异4、非聚集索引的作用5、是不是使用非聚集索引的查询都需要进行聚集的查询6、创建索引的规则执行计划单表执行计划多表执行计划多表连接执行计划1、嵌套连接2、HASH连接3、合并连接4、反合并连接5、半连接6、反半连接谢谢!