数据库高级应用技术08-(数据库调优).ppt

1 高等职业技术院校教材 数据库高级应用技术 主编 温立辉wenlihui2004 2 数据库调优 本单元教学目标了解数据库调优的重要意义了解数据库调优的技术 方法 原理掌握数据库分区 分表 建索引技术能够编写出高效的SQL语句 3 分区 定义 将一个大表的数据分段划分在多个位置存放可以是同一块磁盘也可以在不同的机器分区后 表面上还是一张表 但数据散列到多个位置了 4 分区类型 MySQL支持RANGE LIST HASH KEY分区类型 其中以RANGE最为常用Range 范围 这种模式允许将数据划分不同范围 例如可以将一个表通过年份划分成若干个分区 Hash 哈希 这种模式允许通过对表的一个或多个列的HashKey进行计算 最后通过这个Hash码不同数值对应的数据区域进行分区 Key 键值 Hash模式的一种延伸 这里的HashKey是MySQL系统产生的 List 预定义列表 这种模式允许系统通过预定义的列表的值来对数据进行分割 5 查看是版本否支持分区 showvariableslike partition 如果变量的值是YES 那么支持分区如果变量值是空白 则不支持分区 6 分区脚本 创建range分区表DROPTABLEIFEXISTS user CREATETABLEIFNOTEXISTS user id int 11 NOTNULLAUTO INCREMENTCOMMENT 用户ID name varchar 50 NOTNULLDEFAULT COMMENT 名称 sex int 1 NOTNULLDEFAULT 0 COMMENT 0为男 1为女 PRIMARYKEY id ENGINE InnoDBDEFAULTCHARSET utf8AUTO INCREMENT 1PARTITIONBYRANGE id PARTITIONp0VALUESLESSTHAN 3 PARTITIONp1VALUESLESSTHAN 6 PARTITIONp2VALUESLESSTHAN 9 PARTITIONp3VALUESLESSTHAN 12 PARTITIONp4VALUESLESSTHANMAXVALUE 插入数据INSERTINTO user name sex VALUES tank 0 zhang 1 ying 1 zhang 1 ying 0 test1 1 tank2 1 tank1 1 test2 1 test3 1 test4 1 test5 1 tank3 1 tank4 1 tank5 1 tank6 1 tank7 1 tank8 1 tank9 1 tank10 1 tank11 1 tank12 1 tank13 1 tank21 1 tank42 1 7 分区脚本解释 PARTITIONBYRANGE id 对表中的id字段以RANGE方式进行分区操作分区的字段必须是表中主键 如果表中有主键 PARTITIONp0VALUESLESSTHAN 3 分区字段的值 3时 记录将存放在p0分区PARTITIONp1VALUESLESSTHAN 6 3 分区字段的值 6时 记录将存放在p1分区 8 数据表的分区信息 可以在information schema库的PARTITIONS表中检索到如可检索到刚刚创建的User表分区信息 9 删除表分区 删除表分区语法 ALTERTABLE 表名 DROPPARTITION 分区名下面语句删除user表的P4分区ALTERTABLEuserDROPPARTITIONP4删除表分区时 分区上的数据会被删除 10 对已存在的表进行分区 操作的语法 ALTERTABLE 表名 分区语句 11 myorder sql脚本 USEtest DROPTABLEIFEXISTS test myorder CREATETABLE test myorder id int 10 unsignedNOTNULL goods id int 10 unsignedNOTNULL price floatNOTNULL order time datetimeNOTNULL ENGINE InnoDBDEFAULTCHARSET utf8 INSERTINTOMYORDERVALUES 1000 2000 100 2005 03 0400 00 00 INSERTINTOMYORDERVALUES 1001 2001 200 2008 06 0800 00 00 INSERTINTOMYORDERVALUES 1002 2002 300 2011 08 0900 00 00 INSERTINTOMYORDERVALUES 1003 2003 400 2014 01 0800 00 00 INSERTINTOMYORDERVALUES 1004 2004 500 2016 03 0200 00 00 INSERTINTOMYORDERVALUES 1005 2005 600 2016 05 0400 00 00 12 添加分区操作 用myorder sql脚本初始化环境建表 然后可用下面语句对此表加上分区操作 ALTERTABLEtest myorderPARTITIONBYRANGE YEAR order time PARTITIONY1VALUESlessthan YEAR 2007 01 01 PARTITIONY2VALUESlessthan YEAR 2010 01 01 PARTITIONY3VALUESlessthan YEAR 2015 01 01 PARTITIONY4VALUESlessthanMAXVALUE 13 课堂练习 创建一新闻表 按要求对其进行分区 然后用存储过程往其插入100000条数据 最后测试各分区上的数据的检索时间是否相同 表结构 新闻ID INT类型 自增 主键新闻标题 VARCHAR 50 类型新闻内容 VARCHAR 500 类型创建时间 DATETIME类型表分区 以新闻ID为字段 对其进行分区 5000以下记录进入NEWS1区5000 20000之间记录进入NEWS2区20000以上记录进入NEWS3区 14 分表 定义 将一个大表按照一定的规则分解成多张具有独立存储空间的实体表垂直分割纵向 减少表的尺寸 表的字段 水平分割横向分割表 减少一张表的记录数 15 水平分割 一个表里面的数据太多 把其分成几个子表来存储这些数据每个子表的结构是一样的 但数据不同表分区的一个变种 16 水平分割策略 1 预先估计某个大表的数据量 按实际情况将其均分为固定数量表 2 按时间拆分 3 按每个表固定记录行数拆分 4 将很久之前的数据迁移到一张历史表 17 垂直分割 对表的垂直划分来减少目标表的宽度使某些特定的列被划分到特定的子每个子表都包含了其中的列所对应的行 18 垂直分割原理 19 垂直分割案例 Article表 有content与image两个字段为TEXT与BLOB两种大字段类型 两个字段被读取的频率相对较少 从性能方面考虑可以对此表进行垂直切片 把content与image两个字段分离到另一个子表中同时 在子表中保留article id子段 作为关联原主表的外键 20 垂直分割案例 原主表 分割后子表 21 课堂练习 把销售订单表 垂直分片成 订单 销售两张子表 写出SQL建表脚本 销售订单表 22 建索引 定义 索引是对数据库表中一列或多列的值进行排序的一种结构 使用索引可快速访问数据库表中的特定信息 23 索引优点 第一 可以大大加快数据的检索速度这也是创建索引的最主要的原因 第二 可以加速表和表之间的连接 特别是在实现数据的参考完整性方面特别有意义 第三 在使用分组和排序子句进行数据检索时可以显著减少查询中分组和排序的时间 第四 在查询的过程中 使用优化隐藏器提高系统的性能 24 索引不足 第一 创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加第二 索引需要占物理空间每一个索引还要占一定的物理空间第三 对表中的数据进行写操作时数据表上会重建相关索引降低了数据写操作的速度 25 索引设计原则 经常与其他表进行连接的表 在连接字段上应该建立索引经常出现在Where子句中的字段 特别是大表的字段 应该建立索引索引应该建在小字段上 对于大的文本字段甚至超长字段 不要建索引频繁进行写操作的表 不要建立太多的索引删除无用的索引 避免对造成负面影响 26 创建单列索引 语法格式 ALTERTABLE 表名 ADDINDEX 索引名 索引列 为myorder表的goods id字段添加名为goods id index的单列索引ALTERTABLEmyorderADDINDEXgoods id index goods id 27 创建混合索引 语法格式 ALTERTABLE 表名 ADDINDEX 索引名 列1 列2 列3 为myorder表的price order time字段添加名为ordertime price index的混合索引ALTERTABLEmyorderADDINDEXordertime price index price order time 28 查询索引 语法格式 SHOWINDEXFROM 表名查询为上面myorder表创建的索引信息SHOWINDEXFROMmyorder 29 删除索引 语法格式 二选一 DROPINDEX 索引名 ON 表名ALTERTABLE 表名 DROPINDEX 索引名删除myorder表上的ordertime price index索引DROPINDEXordertime price indexONmyorderALTERTABLEmyorderDROPINDEXordertime price index 30 课堂练习 创建一张教师表 结构如下用存储过程往其插入100000条数据然后为工资字段建立索引最后测试在索引创建前与创建后的条件下以工资作为检索条件的查询时间是否相同表结构 主键 idint姓名 namevarchar 20 年龄 agevarchar 20 生日 birthdaydate工资 salaryint 31 SQL语句优化 对于海量数据 不科学的SQL语句和合格的SQL语句之间的速度差别可以达到上百倍 可见对于一个系统不是简单地能实现其功能就可 而是要写出高质量的SQL语句 提高系统的可用性 32 SQL语句优化原则 1 对查询进行优化 要尽量避免全表扫描应考虑在where及orderby涉及的列上建立索引2 应尽量避免在where子句中使用 或操作符将引擎放弃使用索引而进行全表扫描 3 应尽量避免在where子句中使用or来连接条件如果一个字段有索引 一个字段没有索引 将导致引擎放弃使用索引而进行全表扫描4 应尽量避免在where子句中对字段进行表达式操作这将导致引擎放弃使用索引而进行全表扫描 5 不要在where子句中的 左边进行函数 算术运算或其他表达式运算系统将可能无法正确使用索引 33 SQL语句优化原则 6 索引是复合索引 那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引否则该索引将不会被使用 并且应尽可能的让字段顺序与索引顺序相一致7 Update语句 如果只更改1 2个字段 不要Update全部字段否则频繁调用会引起明显的性能消耗 同时带来大量日志8 selectcount fromtable 不带任何条件的count会引起全表扫描没有任何业务意义 要杜绝9 尽量使用数字型字段 若只含数值信息的字段尽量不要设计为字符型 这会降低查询和连接的性能 并会增加存储开销 34 SQL语句优化原则 10 尽可能的使用varchar代替char 变长字段存储空间小 可以节省存储空间11 任何地方都不要使用select fromt 用具体的字段列表代替 不要返回用不到的任何字段12 如果使用到了临时表 在存储过程的最后务必将所有的临时表显式删除 先truncatetable 然后droptable 这样可以避免系统表的较长时间锁定13 尽量避免使用游标 因为游标的效率较差如果游标操作的数据超过1万行 那么就应该考虑改写14 尽量避免大事务操作 提高系统并发能力 35 单元作业 1 建立一个关于系 学生 班级 学会等诸信息的关系数据库学生 学号 姓名 出生年月 系名 班号 宿舍区班级 班号 专业名 系名 人数 入校年份系 系名 系号 系办公地点 人数学会 学会名 成立年份 办公地点 人数 学会简介 学会logo表之间的关系如下 一个系