1數(shù)據(jù)庫調(diào)優(yōu)本單元教學(xué)目標(biāo)了解數(shù)據(jù)庫調(diào)優(yōu)的重要意義了解數(shù)據(jù)庫調(diào)優(yōu)的技術(shù)、方法、原理掌握數(shù)據(jù)庫分區(qū)、分表、建索引技術(shù)能夠編寫出高效的SQL語句2分區(qū)定義：將一個(gè)大表的數(shù)據(jù)分段劃分在多個(gè)位置存放可以是同一塊磁盤也可以在不同的機(jī)器分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個(gè)位置了。3分區(qū)類型MySQL支持RANGE，LIST，HASH，KEY分區(qū)類型，其中以RANGE最為常用Range（范圍）這種模式允許將數(shù)據(jù)劃分不同范圍。例如可以將一個(gè)表通過年份劃分成若干個(gè)分區(qū)。Hash（哈希）這種模式允許通過對(duì)表的一個(gè)或多個(gè)列的HashKey進(jìn)行計(jì)算，最后通過這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。Key（鍵值）Hash模式的一種延伸，這里的HashKey是MySQL系統(tǒng)產(chǎn)生的。List（預(yù)定義列表）這種模式允許系統(tǒng)通過預(yù)定義的列表的值來對(duì)數(shù)據(jù)進(jìn)行分割。4查看是版本否支持分區(qū)showvariableslike'%partition%‘如果變量的值是YES，那么支持分區(qū)如果變量值是空白，則不支持分區(qū)5分區(qū)腳本--創(chuàng)建range分區(qū)表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);--插入數(shù)據(jù)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分區(qū)腳本解釋PARTITIONBYRANGE(id)對(duì)表中的id字段以RANGE方式進(jìn)行分區(qū)操作分區(qū)的字段必須是表中主鍵（如果表中有主鍵）PARTITIONp0VALUESLESSTHAN(3)分區(qū)字段的值<3時(shí)，記錄將存放在p0分區(qū)PARTITIONp1VALUESLESSTHAN(6)3<=分區(qū)字段的值<6時(shí)，記錄將存放在p1分區(qū)7數(shù)據(jù)表的分區(qū)信息可以在information_schema庫的PARTITIONS表中檢索到如可檢索到剛剛創(chuàng)建的User表分區(qū)信息8刪除表分區(qū)刪除表分區(qū)語法：ALTERTABLE+表名

+DROPPARTITION+分區(qū)名下面語句刪除user表的P4分區(qū)ALTERTABLEuserDROPPARTITIONP4刪除表分區(qū)時(shí)，分區(qū)上的數(shù)據(jù)會(huì)被刪除

9對(duì)已存在的表進(jìn)行分區(qū)操作的語法：ALTERTABLE+表名+分區(qū)語句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添加分區(qū)操作用myorder.sql腳本初始化環(huán)境建表，然后可用下面語句對(duì)此表加上分區(qū)操作：ALTERTABLEtest.myorderPARTITIONBYRANGE(YEAR(order_time))(PARTITIONY1VALUESlessthan(YEAR('2007-01-01')),PARTITIONY2VALUESlessthan(YEAR('2010-01-01')),PARTITIONY3VALUESlessthan(YEAR('2015-01-01')),PARTITIONY4VALUESlessthanMAXVALUE);12課堂練習(xí)創(chuàng)建一新聞表：按要求對(duì)其進(jìn)行分區(qū)，然后用存儲(chǔ)過程往其插入100000條數(shù)據(jù)，最后測(cè)試各分區(qū)上的數(shù)據(jù)的檢索時(shí)間是否相同。表結(jié)構(gòu)：新聞ID，INT類型，自增，主鍵新聞標(biāo)題，VARCHAR（50）類型新聞內(nèi)容，VARCHAR（500）類型創(chuàng)建時(shí)間，DATETIME類型表分區(qū)：以新聞ID為字段，對(duì)其進(jìn)行分區(qū)，5000以下記錄進(jìn)入NEWS1區(qū)5000-20000之間記錄進(jìn)入NEWS2區(qū)20000以上記錄進(jìn)入NEWS3區(qū)13分表定義：將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表垂直分割縱向，減少表的尺寸（表的字段）水平分割橫向分割表，減少一張表的記錄數(shù)14水平分割一個(gè)表里面的數(shù)據(jù)太多，把其分成幾個(gè)子表來存儲(chǔ)這些數(shù)據(jù)每個(gè)子表的結(jié)構(gòu)是一樣的，但數(shù)據(jù)不同表分區(qū)的一個(gè)變種15水平分割策略1、預(yù)先估計(jì)某個(gè)大表的數(shù)據(jù)量，按實(shí)際情況將其均分為固定數(shù)量表。2、按時(shí)間拆分。3、按每個(gè)表固定記錄行數(shù)拆分。4、將很久之前的數(shù)據(jù)遷移到一張歷史表。16垂直分割對(duì)表的垂直劃分來減少目標(biāo)表的寬度使某些特定的列被劃分到特定的子每個(gè)子表都包含了其中的列所對(duì)應(yīng)的行17垂直分割原理

18垂直分割案例Article表：有content與image兩個(gè)字段為TEXT與BLOB兩種大字段類型，兩個(gè)字段被讀取的頻率相對(duì)較少，從性能方面考慮可以對(duì)此表進(jìn)行垂直切片，把content與image兩個(gè)字段分離到另一個(gè)子表中同時(shí)，在子表中保留article_id子段，作為關(guān)聯(lián)原主表的外鍵19垂直分割案例原主表：分割后子表：20課堂練習(xí)把銷售訂單表，垂直分片成：訂單、銷售兩張子表，寫出SQL建表腳本。銷售訂單表21建索引定義：索引是對(duì)數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。22索引優(yōu)點(diǎn)第一，可以大大加快數(shù)據(jù)的檢索速度這也是創(chuàng)建索引的最主要的原因。第二，可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。第三，在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)可以顯著減少查詢中分組和排序的時(shí)間。第四，在查詢的過程中，使用優(yōu)化隱藏器提高系統(tǒng)的性能。23索引不足第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間這種時(shí)間隨著數(shù)據(jù)量的增加而增加第二，索引需要占物理空間每一個(gè)索引還要占一定的物理空間第三，對(duì)表中的數(shù)據(jù)進(jìn)行寫操作時(shí)數(shù)據(jù)表上會(huì)重建相關(guān)索引降低了數(shù)據(jù)寫操作的速度24索引設(shè)計(jì)原則經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長字段，不要建索引頻繁進(jìn)行寫操作的表，不要建立太多的索引刪除無用的索引，避免對(duì)造成負(fù)面影響25創(chuàng)建單列索引語法格式：ALTERTABLE+表名

+ADDINDEX+

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

26創(chuàng)建混合索引語法格式：ALTERTABLE+表名+ADDINDEX+

索引名

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

ALTERTABLEmyorderADDINDEXordertime_price_index(price,order_time)

27查詢索引語法格式：SHOWINDEXFROM+

表名查詢?yōu)樯厦鎚yorder表創(chuàng)建的索引信息SHOWINDEXFROMmyorder28刪除索引語法格式(二選一)DROPINDEX+

索引名+ON+

表名ALTERTABLE+表名

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

29課堂練習(xí)創(chuàng)建一張教師表，結(jié)構(gòu)如下用存儲(chǔ)過程往其插入100000條數(shù)據(jù)然后為工資字段建立索引最后測(cè)試在索引創(chuàng)建前與創(chuàng)建后的條件下以工資作為檢索條件的查詢時(shí)間是否相同表結(jié)構(gòu)：主鍵：idint姓名：namevarchar(20)年齡：agevarchar(20)生日：birthdaydate工資：salaryint30SQL語句優(yōu)化對(duì)于海量數(shù)據(jù)，不科學(xué)的SQL語句和合格的SQL語句之間的速度差別可以達(dá)到上百倍，可見對(duì)于一個(gè)系統(tǒng)不是簡單地能實(shí)現(xiàn)其功能就可，而是要寫出高質(zhì)量的SQL語句，提高系統(tǒng)的可用性31SQL語句優(yōu)化原則1.對(duì)查詢進(jìn)行優(yōu)化，要盡量避免全表掃描應(yīng)考慮在where及orderby涉及的列上建立索引2.應(yīng)盡量避免在where子句中使用!=或<>操作符將引擎放棄使用索引而進(jìn)行全表掃描。3.應(yīng)盡量避免在where子句中使用or來連接條件如果一個(gè)字段有索引，一個(gè)字段沒有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描4.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行表達(dá)式操作這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。5.不要在where子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算系統(tǒng)將可能無法正確使用索引32SQL語句優(yōu)化原則6.索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致7.Update語句，如果只更改1、2個(gè)字段，不要Update全部字段否則頻繁調(diào)用會(huì)引起明顯的性能消耗，同時(shí)帶來大量日志8.selectcount(*)fromtable；不帶任何條件的count會(huì)引起全表掃描沒有任何業(yè)務(wù)意義，要杜絕9.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷33SQL語句優(yōu)化原則10.盡可能的使用varchar代替char，變長字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間11.任何地方都不要使用select*fromt，用具