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

《数据库高级应用技术08-(数据库调优).ppt》由会员分享，可在线阅读，更多相关《数据库高级应用技术08-(数据库调优).ppt（36页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 高等职业技术院校教材高等职业技术院校教材 数据库高级应用技术数据库高级应用技术 主编：温立辉主编：温立辉1 数据库调优数据库调优本单元教学目标本单元教学目标了解数据库调优的重要意义了解数据库调优的技术、方法、原理掌握数据库分区、分表、建索引技术能够编写出高效的SQL语句2 分区分区n定义：n将一个大表的数据分段划分在多个位置存放n可以是同一块磁盘也可以在不同的机器n分区后，表面上还是一张表，但数据散列到多个位置了。3 分区类型分区类型nMySQL支持RANGE，LIST，HASH，KEY分区类型，n其中以RANGE最为常用nRange（范围）n这种模式允许将数据划分不同范围。n例如可以将一个

2、表通过年份划分成若干个分区。nHash（哈希）n这种模式允许通过对表的一个或多个列的Hash Key进行计算，n最后通过这个Hash码不同数值对应的数据区域进行分区。nKey（键值）nHash模式的一种延伸，这里的Hash Key是MySQL系统产生的。nList（预定义列表）n这种模式允许系统通过预定义的列表的值来对数据进行分割。4 查看是版本否支持分区查看是版本否支持分区nshow variables like%partition%n如果变量的值是YES，那么支持分区n如果变量值是空白，则不支持分区 5 分区脚本分区脚本n-创建range分区表nDROP TABLE IF EXISTS u

3、ser;nCREATE TABLE IF NOT EXISTS user(nid int(11)NOT NULL AUTO_INCREMENT COMMENT 用户ID,nname varchar(50)NOT NULL DEFAULT COMMENT 名称,nsex int(1)NOT NULL DEFAULT 0 COMMENT 0为男，1为女,nPRIMARY KEY(id)n)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1nPARTITION BY RANGE(id)(nPARTITION p0 VALUES LESS THAN(

4、3),nPARTITION p1 VALUES LESS THAN(6),nPARTITION p2 VALUES LESS THAN(9),nPARTITION p3 VALUES LESS THAN(12),nPARTITION p4 VALUES LESS THAN MAXVALUEn);n-插入数据nINSERT INTO user(name,sex)VALUES(tank,0)n,(zhang,1),(ying,1),(zhang,1),(ying,0),(test1,1),(tank2,1)n,(tank1,1),(test2,1),(test3,1),(test4,1),(tes

5、t5,1),(tank3,1)n,(tank4,1),(tank5,1),(tank6,1),(tank7,1),(tank8,1),(tank9,1)n,(tank10,1),(tank11,1),(tank12,1),(tank13,1),(tank21,1),(tank42,1);6 分区脚本解释分区脚本解释nPARTITION BY RANGE(id)n对表中的id字段以RANGE方式进行分区操作n分区的字段必须是表中主键（如果表中有主键）nPARTITION p0 VALUES LESS THAN(3)n分区字段的值 3 时，记录将存放在p0分区nPARTITION p1 VALUE

6、S LESS THAN(6)n3=分区字段的值 6 时，记录将存放在p1分区7 数据表的分区信息数据表的分区信息n可以在information_schema库的PARTITIONS表中检索到n如可检索到刚刚创建的User表分区信息 8 删除表分区删除表分区n删除表分区语法：nALTER TABLE+表名表名+DROP PARTITION+分区名分区名n下面语句删除user表的P4分区nALTER TABLE user DROP PARTITION P4n n删除表分区时，分区上的数据会被删除删除表分区时，分区上的数据会被删除 9 对已存在的表进行分区对已存在的表进行分区 n操作的语法：nALT

7、ER TABLE+表名+分区语句10 myorder.sql脚本脚本nUSE test;nDROP TABLE IF EXISTS test.myorder;nCREATE TABLE test.myorder(n id int(10)unsigned NOT NULL,n goods_id int(10)unsigned NOT NULL,n price float NOT NULL,n order_time datetime NOT NULLn)ENGINE=InnoDB DEFAULT CHARSET=utf8;nINSERT INTO MYORDER VALUES(1000,2000,

8、100,2005-03-04 00:00:00);nINSERT INTO MYORDER VALUES(1001,2001,200,2008-06-08 00:00:00);nINSERT INTO MYORDER VALUES(1002,2002,300,2011-08-09 00:00:00);nINSERT INTO MYORDER VALUES(1003,2003,400,2014-01-08 00:00:00);nINSERT INTO MYORDER VALUES(1004,2004,500,2016-03-02 00:00:00);nINSERT INTO MYORDER VA

9、LUES(1005,2005,600,2016-05-04 00:00:00);11 添加分区操作添加分区操作n用myorder.sql脚本初始化环境建表，n然后可用下面语句对此表加上分区操作：nALTER TABLE test.myorder PARTITION BY RANGE(YEAR(order_time)(nPARTITION Y1 VALUES less than(YEAR(2007-01-01),nPARTITION Y2 VALUES less than(YEAR(2010-01-01),nPARTITION Y3 VALUES less than(YEAR(2015-01-0

10、1),nPARTITION Y4 VALUES less than MAXVALUEn);12 课堂练习课堂练习n创建一新闻表：n按要求对其进行分区，n然后用存储过程往其插入100000条数据，n最后测试各分区上的数据的检索时间是否相同。n表结构：n新闻ID，INT类型，自增，主键n新闻标题，VARCHAR（50）类型n新闻内容，VARCHAR（500）类型n创建时间，DATETIME类型n表分区：n以新闻ID为字段，对其进行分区，n5000以下记录进入NEWS1区n5000-20000之间记录进入NEWS2区n20000以上记录进入NEWS3区 13 分表分表n定义：n将一个大表按照一定的规

11、则分解成多张具有独立存储空间的实体表n垂直分割n纵向，减少表的尺寸（表的字段）n水平分割n横向分割表，减少一张表的记录数 14 水平分割水平分割n一个表里面的数据太多，把其分成几个子表来存储这些数据n每个子表的结构是一样的，但数据不同n表分区的一个变种 15 水平分割策略水平分割策略n1、预先估计某个大表的数据量，按实际情况将其均分为固定数量表。n2、按时间拆分。n3、按每个表固定记录行数拆分。n4、将很久之前的数据迁移到一张历史表。16 垂直分割垂直分割n对表的垂直划分来减少目标表的宽度n使某些特定的列被划分到特定的子n每个子表都包含了其中的列所对应的行 17 垂直分割原理垂直分割原理 18

12、 垂直分割案例垂直分割案例nArticle表：n有content与image两个字段n为TEXT与BLOB两种大字段类型，n两个字段被读取的频率相对较少，n从性能方面考虑n可以对此表进行垂直切片，n把content与image两个字段分离到另一个子表中n同时，在子表中保留article_id子段，n作为关联原主表的外键19 垂直分割案例垂直分割案例n原主表：n分割后子表：20 课堂练习课堂练习n把销售订单表，垂直分片成：订单、销售两张子表，写出SQL建表脚本。销售订单表21 建索引建索引n定义：n索引是对数据库表中一列或多列的值进行排序的一种结构，n使用索引可快速访问数据库表中的特定信息。22

13、 索引优点索引优点n第一，可以大大加快数据的检索速度n这也是创建索引的最主要的原因。n第二，可以加速表和表之间的连接，n特别是在实现数据的参考完整性方面特别有意义。n第三，在使用分组和排序子句进行数据检索时n可以显著减少查询中分组和排序的时间。n第四，在查询的过程中，使用优化隐藏器n提高系统的性能。23 索引不足索引不足n第一，创建索引和维护索引要耗费时间n这种时间随着数据量的增加而增加n第二，索引需要占物理空间n每一个索引还要占一定的物理空间n第三，对表中的数据进行写操作时n数据表上会重建相关索引n降低了数据写操作的速度24 索引设计原则索引设计原则n经常与其他表进行连接的表，在连接字段上应

14、该建立索引n经常出现在Where子句中的字段，特别是大表的字段，应该建立索引n索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引n频繁进行写操作的表，不要建立太多的索引n删除无用的索引，避免对造成负面影响25 创建单列索引创建单列索引 n语法格式：nALTER TABLE+表名表名+ADD INDEX+索引名索引名+(索引列索引列)n为myorder表的goods_id字段添加名为goods_id_index的单列索引nALTER TABLE myorder ADD INDEX goods_id_index(goods_id)26 创建混合索引创建混合索引 n语法格式：nALTER

15、 TABLE+表名表名+ADD INDEX+索引名索引名+(列列1,列列2,列列3)n为myorder表的price、order_time字段添加名为ordertime_price_index的混合索引 nALTER TABLE myorder ADD INDEX ordertime_price_index(price,order_time)27 查询索引查询索引 n语法格式：nSHOW INDEX FROM+表名表名n查询为上面myorder表创建的索引信息nSHOW INDEX FROM myorder28 删除索引删除索引 n语法格式(二选一)nDROP INDEX+索引名索引名+ON+

16、表名表名nALTER TABLE+表名表名+DROP INDEX+索引名索引名n删除myorder表上的ordertime_price_index索引nDROP INDEX ordertime_price_index ON myordernALTER TABLE myorder DROP INDEX ordertime_price_index 29 课堂练习课堂练习n创建一张教师表，结构如下n用存储过程往其插入100000条数据n然后为工资字段建立索引n最后测试在索引创建前与创建后的条件下n以工资作为检索条件的查询时间是否相同n表结构：n主键：id intn姓名：name varchar(20

17、)n年龄：age varchar(20)n生日：birthday daten工资：salary int30 SQL语句优化语句优化n对于海量数据，不科学的SQL语句和合格的SQL语句之间的速度差别可以达到上百倍，n可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性 31 SQL语句优化原则语句优化原则n1.对查询进行优化，要尽量避免全表扫描n应考虑在 where 及 order by 涉及的列上建立索引n2.应尽量避免在 where 子句中使用!=或 操作符n将引擎放弃使用索引而进行全表扫描。n3.应尽量避免在 where 子句中使用 or 来连接条件n

18、如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描n4.应尽量避免在 where 子句中对字段进行表达式操作n这将导致引擎放弃使用索引而进行全表扫描。n5.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算n系统将可能无法正确使用索引32 SQL语句优化原则语句优化原则n6.索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引n否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致n7.Update 语句，如果只更改1、2个字段，不要Update全部字段n否则频繁调用会引起明显的性能消耗，同时带来大量日志n8

19、.select count(*)from table；不带任何条件的count会引起全表扫描n没有任何业务意义，要杜绝n9.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销33 SQL语句优化原则语句优化原则n10.尽可能的使用 varchar代替char，n变长字段存储空间小，可以节省存储空间n11.任何地方都不要使用 select*from t，n用具体的字段列表代替“*”，不要返回用不到的任何字段n12.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，n先 truncate table，然后 drop table，这

20、样可以避免系统表的较长时间锁定n13.尽量避免使用游标，因为游标的效率较差n如果游标操作的数据超过1万行，那么就应该考虑改写n14.尽量避免大事务操作，提高系统并发能力34 单元作业单元作业(1)n建立一个关于系、学生、班级、学会等诸信息的关系数据库n学生：学号、姓名、出生年月、系名、班号、宿舍区n班级：班号、专业名、系名、人数、入校年份n系：系名、系号、系办公地点、人数n学会：学会名、成立年份、办公地点、人数、学会简介、学会logon表之间的关系如下：n一个系有若干专业n每个专业每年只招一个班n每个班有若干学生n一个系的学生住在同一宿舍区n每个学生可参加若干学会n每个学会有若干学生35 单元作业单元作业(2)n1）按要求为关系表添加分区：n为学生表的学号字段添加S1、S2、S3三个分区n为班级表的班号字段添加C1、C2两个分区n为系表的系号字段添加D1、D2两个分区n2）按要示为关系表添加索引：n为学生表的姓名字段添加单列索引n为班级表的专业名、系名字段添加混合索引n为系表的系名添加单列索引n3）学会表的的学会简介字段为TEXT类型的大字段，学会logo为BLOB类型的大字段，且以上两个字段被检索的频率较低，把以上两个字段从学会表分离。36