《数据库高级应用技术》-数据库高级应用技术08-(数据库调优)

1数据库调优本单元教学目标了解数据库调优的重要意义了解数据库调优的技术、方法、原理掌握数据库分区、分表、建索引技术能够编写出高效的SQL语句2分区定义：将一个大表的数据分段划分在多个位置存放可以是同一块磁盘也可以在不同的机器分区后，表面上还是一张表，但数据散列到多个位置了。3分区类型MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用Range（范围）这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。Hash（哈希）这种模式允许通过对表的一个或多个列的HashKey进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。Key（键值）Hash模式的一种延伸，这里的HashKey是MySQL系统产生的。List（预定义列表）这种模式允许系统通过预定义的列表的值来对数据进行分割。4查看是版本否支持分区showvariableslike'%partition%‘如果变量的值是YES，那么支持分区如果变量值是空白，则不支持分区5分区脚本--创建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);6分区脚本解释PARTITIONBYRANGE(id)对表中的id字段以RANGE方式进行分区操作分区的字段必须是表中主键（如果表中有主键）PARTITIONp0VALUESLESSTHAN(3)分区字段的值<3时，记录将存放在p0分区PARTITIONp1VALUESLESSTHAN(6)3<=分区字段的值<6时，记录将存放在p1分区7数据表的分区信息可以在information_schema库的PARTITIONS表中检索到如可检索到刚刚创建的User表分区信息8删除表分区删除表分区语法：ALTERTABLE+表名

+DROPPARTITION+分区名下面语句删除user表的P4分区ALTERTABLEuserDROPPARTITIONP4删除表分区时，分区上的数据会被删除

9对已存在的表进行分区操作的语法：ALTERTABLE+表名+分区语句10myorder.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');11添加分区操作用myorder.sql脚本初始化环境建表，然后可用下面语句对此表加上分区操作：ALTERTABLEtest.myorderPARTITIONBYRANGE(YEAR(order_time))(PARTITIONY1VALUESlessthan(YEAR('2007-01-01')),PARTITIONY2VALUESlessthan(YEAR('2010-01-01')),PARTITIONY3VALUESlessthan(YEAR('2015-01-01')),PARTITIONY4VALUESlessthanMAXVALUE);12课堂练习创建一新闻表：按要求对其进行分区，然后用存储过程往其插入100000条数据，最后测试各分区上的数据的检索时间是否相同。表结构：新闻ID，INT类型，自增，主键新闻标题，VARCHAR（50）类型新闻内容，VARCHAR（500）类型创建时间，DATETIME类型表分区：以新闻ID为字段，对其进行分区，5000以下记录进入NEWS1区5000-20000之间记录进入NEWS2区20000以上记录进入NEWS3区13分表定义：将一个大表按照一定的规则分解成多张具有独立存储空间的实体表垂直分割纵向，减少表的尺寸（表的字段）水平分割横向分割表，减少一张表的记录数14水平分割一个表里面的数据太多，把其分成几个子表来存储这些数据每个子表的结构是一样的，但数据不同表分区的一个变种15水平分割策略1、预先估计某个大表的数据量，按实际情况将其均分为固定数量表。2、按时间拆分。3、按每个表固定记录行数拆分。4、将很久之前的数据迁移到一张历史表。16垂直分割对表的垂直划分来减少目标表的宽度使某些特定的列被划分到特定的子每个子表都包含了其中的列所对应的行17垂直分割原理

18垂直分割案例Article表：有content与image两个字段为TEXT与BLOB两种大字段类型，两个字段被读取的频率相对较少，从性能方面考虑可以对此表进行垂直切片，把content与image两个字段分离到另一个子表中同时，在子表中保留article_id子段，作为关联原主表的外键19垂直分割案例原主表：分割后子表：20课堂练习把销售订单表，垂直分片成：订单、销售两张子表，写出SQL建表脚本。销售订单表21建索引定义：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。22索引优点第一，可以大大加快数据的检索速度这也是创建索引的最主要的原因。第二，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第三，在使用分组和排序子句进行数据检索时可以显著减少查询中分组和排序的时间。第四，在查询的过程中，使用优化隐藏器提高系统的性能。23索引不足第一，创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加第二，索引需要占物理空间每一个索引还要占一定的物理空间第三，对表中的数据进行写操作时数据表上会重建相关索引降低了数据写操作的速度24索引设计原则经常与其他表进行连接的表，在连接字段上应该建立索引经常出现在Where子句中的字段，特别是大表的字段，应该建立索引索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引频繁进行写操作的表，不要建立太多的索引删除无用的索引，避免对造成负面影响25创建单列索引语法格式：ALTERTABLE+表名

+ADDINDEX+

索引名+(索引列)为myorder表的goods_id字段添加名为goods_id_index的单列索引ALTERTABLEmyorderADDINDEXgoods_id_index(goods_id)

26创建混合索引语法格式：ALTERTABLE+表名+ADDINDEX+

索引名

+(列1,列2,列3)为myorder表的price、order_time字段添加名为ordertime_price_index的混合索引

ALTERTABLEmyorderADDINDEXordertime_price_index(price,order_time)

27查询索引语法格式：SHOWINDEXFROM+

表名查询为上面myorder表创建的索引信息SHOWINDEXFROMmyorder28删除索引语法格式(二选一)DROPINDEX+

索引名+ON+

表名ALTERTABLE+表名

+DROPINDEX+索引名删除myorder表上的ordertime_price_index索引DROPINDEXordertime_price_indexONmyorderALTERTABLEmyorderDROPINDEXordertime_price_index

29课堂练习创建一张教师表，结构如下用存储过程往其插入100000条数据然后为工资字段建立索引最后测试在索引创建前与创建后的条件下以工资作为检索条件的查询时间是否相同表结构：主键：idint姓名：namevarchar(20)年龄：agevarchar(20)生日：birthdaydate工资：salaryint30SQL语句优化对于海量数据，不科学的SQL语句和合格的SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性31SQL语句优化原则1.对查询进行优化，要尽量避免全表扫描应考虑在where及orderby涉及的列上建立索引2.应尽量避免在where子句中使用!=或<>操作符将引擎放弃使用索引而进行全表扫描。3.应尽量避免在where子句中使用or来连接条件如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描4.应尽量避免在where子句中对字段进行表达式操作这将导致引擎放弃使用索引而进行全表扫描。5.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算系统将可能无法正确使用索引32SQL语句优化原则6.索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致7.Update语句，如果只更改1、2个字段，不要Update全部字段否则频繁调用会引起明显的性能消耗，同时带来大量日志8.selectcount(*)fromtable；不带任何条件的count会引起全表扫描没有任何业务意义，要杜绝9.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销33SQL语句优化原则10.尽可能的使用varchar代替char，变长字段存储空间小，可以节省存储空间11.任何地方都不要使用select*fromt，用具