数据库高级应用技术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分区表DROPTABLEIFEXISTSuser;CREATETABLEIFNOTEXISTSuser(idint(11)NOTNULLAUTO_INCREMENTCOMMENT用户ID,namevarchar(50)NOTNULLDEFAULTCOMMENT名称,sexint(1)NOTNULLDEFAULT0COMMENT0为男，1为女,PRIMARYKEY(id)ENGINE=InnoDBDEFAULTCHARSET=utf8AUTO_INCREMENT=1PARTITIONBYRANGE(id)(PARTITIONp0VALUESLESSTHAN(3),PARTITIONp1VALUESLESSTHAN(6),PARTITIONp2VALUESLESSTHAN(9),PARTITIONp3VALUESLESSTHAN(12),PARTITIONp4VALUESLESSTHANMAXVALUE);-插入数据INSERTINTOuser(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;DROPTABLEIFEXISTStest.myorder;CREATETABLEtest.myorder(idint(10)unsignedNOTNULL,goods_idint(10)unsignedNOTNULL,pricefloatNOTNULL,order_timedatetimeNOTNULL)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表之间的关系如下：一个系有若干专业每个专业每年只招一个班每个班有若干学生一个系的学生住在同一宿舍区每个学生可参加若干学会每个学会有若干学生,36,单元作业(2),1）按要求为关系表添加分区：为学生表的学号字段添加S1、S2、S3三个分区为班级表的班号字段添加C1、C2两个分区为系表的系号字段添加D1、D2两个分区2）按要示为关系表添加索引：为学生表的姓名字段添加单列索引为班级表的专业名、系名字段添加混合索引为系表的系名添加单列索引3）学会表的的学会简介字段为TEXT类型的大字段，学会logo为BLOB类型的大字段，且以上两个字段被检索的频率较低，把以上两个字段从学会表分离。,