mysql优化培训爱可生信息技术

MySQL开发与优 : MySQL开发与命名字段引擎编码选表设计-命名多字节表设计-字段数值字符二进制时间类其他类数值1-128to0to3-8,388,608to0to 2字3-8,388,608to0to 4字 -2,147,483,648

0to8-to0数值类型-非精确浮范4-3.402823466E+38to-1.175494351E-01.175494351E-38toDOUBLE

8字 -1.7976931348623157E+308to02.2250738585072014E-308数值类型-精确浮字符名范M M+1ORM+2字 65,535字节真实字 65,535字节16,777,215字节 4,294,967,295字节名 M+1ORM+2字 65,533字节真实字 65,535字节16,777,215字节 4,294,967,295字节时间名范3'1000-01-01'to'9999-12- 3字 '-838:59:59'to8'1000-01-0100:00:00'to'9999-12- 4字 '1970-01-0100:00:00'tomid-year11901to2155(for1970to2069(for其他类型-比特位BIT(1-大小(最大9个字节引擎常用常用引擎 多种级别的 最可靠性NDB(MySQLKEY→VALUE可持续99.999其EXCEL和MySQL编码单字节多字节索引唯一索普通索部分聚簇例`Percentage`float(4,1)NOTNULLdefault'0.0',PRIMARYKEY(`CountryCode`,`Language`),Keyidx_language(`Language`), B-语句响应时执行时传送读语句一些好的SELECT*FROMtWHEREYEAR(d)>=→SELECT*FROMtWHEREd>='1994-01-→SELECT*FROMCountryJOINSELECT*FROMtWHEREid=→SELECT*FROMtWHEREid=SELECT*FROMtWHERElength(column_t)=→SELECT*FROMtWHERE读语句一些好的SELECT*FROMtWHEREnameLIKE→SELECT*FROMtWHEREnameLIKESELECT*FROMtWHEREname>='de'ANDname<SELECT*FROMtWHERE→SELECT*FROMtWHERE1LIMITSELECT*FROMCountryWHERENameLIKE→SELECTNameFROMCountryWHERENameLIKE示例修改语SELECTDISTINCASEWHENb.ne_id='1403000000011001'THENb.ENDASCASEWHENb.ne_id='1403000000011006'THENb.ENDASFROMtb1aINNERJOINtb1bUSINGORDERBYa.stop_time对b表扫描次数减小到表优点减少对原磁盘表的要做适当的更写语句INSERTINTOt(id,name)VALUES(1,'Bea');INSERTINTOt(id,name)VALUES(2,'Belle');INSERTINTOt(id,name)→→INSERTINTOt(id,name)INSERTINTOt(id,name)INSERTINTOt(id,name)两者的性能第一第二 0m0m0m注意索UPDATESETcreate_time=注意优化以下语SELECT*FROMDELETEFROMtable_name;DROPTABLEtable_name;过函动态异常函数-例子DELIMITERCREATEFUNCTION`t_girl`.`func_rand_string`(f_numTINYINTUNSIGNED,f_typeTINYINTUNSIGNED)RETURNSVARCHAR(32)--TranslatethenumbertoDECLAREiINTUNSIGNEDDEFAULT0;WHILEi<f_numIFf_type=1ELSEIFf_type=2THENSETv_result=ELSEIFf_type=3ENDSETi=i+ENDRETURN函数-例子 FUNCTION`test`.`t-cursor`() DECLAREdoneINTDEFAULTDECLAREcCURSORFORSELECTidFROMeventWHERE1LIMITDECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENIFdone=1THENLEAVEloop1;ENDIF;FETCHcINTOENDLOOPloop1;CLOSERETURNv_result;动态两种----直接赋动态SQL-例子DROPPROCEDUREIFEXISTSd_ytt2;DELIMITER//CREATEPROCEDUREd_ytt2(field1VARCHAR(20),table1VARCHAR(20),)SET@stmt_text=concat("select",field1,"from",table1,"WHERE",field1,"=?"动态SQL-例子DROPPROCEDUREIFEXISTSd_ytt3;DELIMITER//CREATEPROCEDUREd_ytt3(field1VARCHAR(20),table1VARCHAR(20),)SET@stmt_text=concat("select",field1,"from",table1,"WHERE",field1,"=",异常DECLAREhandler_typeHANDLERFORcondition_value[,...]||SQLSTATE[VALUE]|||NOT|异常DELIMITERDECLAREuidBIGINTUNSIGNED;DECLAREendedINT(1);DECLAREduplicate_handlerINT(1)DEFAULTSELECT'Error-terminating'ASIF(duplicate_handler=1)THENSELECT'DuplicateKeyError'ASENDDECLARECONTINUEHANDLERFORDUPLICATE_KEYSETDUPLICATE=触发临时行表触发语CREATETRIGGER{BEFORE|AFTER{INSERT|UP