08mysql高级课堂笔记数据库优化

1 识导 3 识 mysql入mysqlmysqlmysqlmysqlmysqlmysql重排auto_increment4 识入 入mysql>createtablet2likemysql>insertintot2select*from5 1.ALTERTABLE用来创建普通索引、UNIQUE索引或PRIMARYKEY索ALTERTABLEtable_nameADDINDEXindex_name(column_list)ALTERTABLEtable_nameADDUNIQUE(column_list)ALTERTABLEtable_nameADDPRIMARYKEYcreateCREATEINDEXindex_nameONtable_nameCREATEUNIQUEINDEXindex_nameONtable_namedropDROPINDEXindex_nameONaltertabletableALTERTABLEtable_nameDROPINDEXindex_nameALTERTABLEtable_nameDROPPRIMARYKEY6 mysql>createviewv_t1asselect*fromt1whereid>4andid<11;QueryOK,0rowsaffected(0.00sec)mysql>?viewALTERVIEWCREATEVIEWDROPVIEWmysql>showtables;mysql>dropview7 识 字符串函数 CONCAT(string2[,…LCASE(string2)UCASE(string2)LENGTH(stringLTRIM(string2)RTRIM(string2REPEAT(string2,count

//重复countRECE(str,search_str,rece_str)//在str中用rece_str替换search_strSUBSTRING(str,position[,length])//从str的position开始,取length个字符 8 识入 入BIN(decimal_number)CEILING(number2)FLOOR(number2)MAX(num1,num2)

9 识 入

识 入mysql>preparestmt1from'select*fromt1wheremysqlset@i=1;mysqlexecutestmt1using@i;mysqlset@i=5;mysql>executestmt1using如何删除预处理mysql>dropprepare 识 mysql>setmysql>savepointp1;mysql>savepointp2;mysql>rollbacktop1;退回到最原始的还原点:mysql>rollback 识 创建一 mysql>\dmysql>createprocedure->->set->while@i<10->select->set->end->mysql>\d;mysql>callp1();查看procedurep1()的status信息mysql>showprocedurestatus\G查看procedurep1()的具体信息:mysql>showcreateprocedure 识 修改delimiter为 mysql>\dmysql>createtriggertg1beforeinsertont1foreach>insertintot2(id)mysql>select*fromt1;mysql>select*fromt2;mysql>insertintot1 识 如何制作删除表t1后t2表中的记录也会跟着删除呢 mysql>\dmysql>createtriggertg2beforedeleteont1foreach>begindeletefromt2wheremysql>\d;mysql>\d//mysql>createtriggertg3beforeupdateont1foreach>beginupdatet2setid=new.idwheremysql>\d;mysql>show 识 MYSQL数据库自动增长的ID如何恢 deletefromtablename;truncatetable这样auto_increment就恢复成1altertabletablenameauto_increment=1; 识入 入利用GROUPBY的WITHROLLUP用BITGROUPFUNCTIONS 识入 入^匹配1个或零个$.匹配0到n个 识入 入mysql>selectname, like“”

fromt

like“mysql>selectmysql>selectfromt 巧用RAND()提取随机识入 入个函数一起和orderby能够把数据随机排序。mysql>select*fromstuorderbyrand()；mysql>select*fromstuorderbyrandlimit3; 利用GROUPBY的WITH识 使用groupby的withrollup子句可以检索 的分组聚合信息 mysql>select*from |cname|pname | | | | | | | | | | | | | | | | | | 9rowsinset(0.00 利用GROUPBY的WITH识 mysql>selectcname,pname,count(pname)fromdemogroupby |cname|pname|count(pname) ||||

| 3| 2| 3| 1 4rowsinset(0.00 利用GROUPBY的WITH识 同样使用withrollup关键字后，统计 的信息，如下。注意： rollup不可以和orderyby同时使mysql>selectcname,pname,count(pname)fromdemogroupbycname,pnamewithrollup; |cname|pname|count(pname) | | 3|| | 2|||||

|NULL 5|| 3|| 1||NULL 4||NULL|NULL 9| 7rowsinset(0.00知2.4BITGROUPFUNCTIONS知 •在使用groupby语句时可以同时用使用bit_and、mysql>selectmysql>selectid，bit_or(kind)fromorder_rabgroupbymysql>selectmysql>selectid，bit_and(kind)fromorder_rabgroupby知2.5知 mysql>createmysql>createtabletemp(idint,namechar(20),foreignkey(id)referencesoutTable(id)ondeletecascadeonupdate 2.6mysqlhelp使识导入在mysql中那么多令如何才能记得住是个问题，这里?%可以获得所有的mysql>里 ?opti因为记不住optimize的全称，这个时候可以用%reg%获得了记不住了的regexp用法查看所有用?contents可以得到所有的帮助大纲,通过这个 识导 知知 3.1.1通过showstatus命令了解各种SQL的执行频格式：mysqlshowmysql>show mysql>showglobal mysql>showstatuslike mysql>showglobalstatuslike 参数说明Com_XXX表示每个XXX执行执行只针对于 引擎的 inselect*fromtablewhere2)descselect*fromtablewheremysql> inselectcount(*)fromstumysql> inselectcount(*)fromstuwherenamelikeid:1table:stutype:key:namekey_len:50ref:NULLrows:8Extra:Usingwhere;Usingindex1rowinset(0.00sec) 每一列的简单解 id: select_type:SIMPLE表示select的类型，常见的取值有SIMPLE（）简单表，即不使用表连接或 table type:range表示表的连接类型，性能有好到差：system（表仅一行）、const（只一行匹配）、ref_or_null（同前面对null查询）、index_merge（索引合并优化）、unique_subquery（:key key_len:50索引字段的长度ref:rows Extra:Usingwhere;Usingindex知知 3.2.1索引

indexpany2_namepany2_name3.2.2MySQL 于快速找出在某个列中有一特定值的行。对相关列使用索引是提高导 操作性能的最佳途径导入1mysql>createmysql>createindex_mon然后 _id进行查询，发现使用到了复合索 inselect*fromsales2 inselect*fromsales2where识 识2where2wherenamelikeinselect*mysql>******************************************************1.rowid:1 type:ALLkey:NULLkey_len:NULLref:NULLrows:1000Extra:Using1rowinset(0.00 mysql> inselect* 2wherenamelike ***************************1.rowid:select_type: 2type:rangepossible_keys:pany2_namekey:pany2_namekey_len:11ref:rows:Extra:Usingwhere1rowinset(0.00sec)mysql>mysql>inselect*2wherenameis***************************1.rowid:select_type:SIMPLE 2type:refpossible_keys:pany2_namekey:pany2_namekey_len:11ref:(4)如果列名是索引，使用column_nameisnull将使用索引。如下知rows:Extra:Usingwhere1rowinset(0.00sec)知导 导 mysql>selectmysql>select*fromtable_namewherekey_part1>1andmysql>showindexmysql>showindexfrom***************************1.……key_name:ind_sales_yearColumn_name:year……知 mysql> inselect*fromsaleswhereyear=2001or***************************1.rowid:1table:key:NULL1row1rowin2sing.00::t11rowUsing(0.00: 识inselectinselect*fromsales2wheremoneys=1mysql>******************************************************1.rowid:1type:ALLkey:NULLkey_len: 识导 mysql> inselect* 2wherename***************************1.rowid:1 type:ALLkey:key_len:1rowinUsingt(0.00emysql>mysql>inselect*2where***************************1.rowid:select_type: 2type:ALLpossible_keys:pany2_namekey:NULLkey_len:NULLref:NULLrows:Extra:Using 识1rowinset(0.00mysql>mysql>inselect*2where***************************1.row***************************id:select_type:SIMPLEpossible_keys:pany2_namekey:pany2_namekey_len:ref:constrows:1Extra:Usingwhere2type:1rowinset(0.003.2.3 入 入mysql>mysql>showstatuslike | | | ||||Handler_read_rnd_next|2055 6rowsinset(0.00|||||||||知知导 导mysql>mysql>CHECKTABLEtbl_name[,tbl_name]…[option]…option{QUICK|FAST|MEDIUM|EXTENDED|mysql>checktable | | |Msg_type|Msg_text |sakila.sales|check| | 1rowinset(0.01 导 导mysql>mysql>optimizetable | ||Msg_type|Msg_text|sakila.sales|optimize| | 1rowinset(0.05|知知 3.4.1 loadingtheALTERTABLEtbl_nameENABLEDISABLEKEYS和ENABLEKEYS用来打开或关闭MyISAM表非 导 mysql>loaddatainfile‘/home/mysql/film_test.txt’intotablefilm_test2导 terminatedbyQueryOK,529056rowsaffected(1min55.12sec)mysql>altertablefilm_test2disablekeys;QueryOK,0rowsaffected(0.0sec)mysql>loaddatainfile‘/home/mysql/film_test.txt’intotableQueryOK,529056rowsaffected(6.34sec)mysql>altertablefilm_test2enablekeys;QueryOK,0rowsaffected(12.25 mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQuery rowsaffected(22.92 Deleted:0Skipped:0mysql>loaddatainfile‘/home/mysql/film_test4.txt’intotableQueryOK, rowsaffected(31.16sec) Deleted:0Skipped:0Warnings:0入 在导入数据前先执行setunique_checks=0，关闭唯 一性效验，在导入结束后执行setunique_checks=1， 入mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQueryOK, rowsaffected(22.92sec) Deleted:0Skipped:0Warnings:0mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotableQueryOK, rowsaffected(19.92sec) 入知•在导入数据前先执行setmit=0，关闭自动提交事 务，在导入结束后执行setmit=1，恢复自动提 入当mit=1当mit=1mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotablefilm_test4;Queryrowsaffected(22.92sec)Deleted:0Skipped:0Warnings:0当mit=0mysql>loaddatainfile‘/home/mysql/film_test3.txt’intotablefilm_test4;Queryrowsaffected(20.87Deleted:0Skipped:0 可以使用insertdelayed（马上执行）语句得到更高可以增加bulk_insert_buffer_size变量值的方法来当从一个文件中装载一个表时，使用LOADDATAmysql>mysql>inselectid,sum(moneys)fromsales2groupbyid:1table:sales2type:ALLkey:NULLref:NULLrows:1000Extra:Usingtemporary;Using1rowinset(0.003.4.3groupby导 •如果查询包含groupby但用户想要避免排序结果 的损耗，则可以使用使用orderbynull来排 导如下没有使用orderbynull来排序inselectinselectid,sum(moneys)fromsales2groupbyidordermysql>******************************************************1.rowid:1type:ALLkey:NULLref:NULLrows:1000Extra:Usingtemporary1rowinset(0.00sec)3.4.5 导入mysql> inselect*fromsales2 _idnotin(select ***************************1.row***************************id:1table:sales2type:ALLkey:NULLref:NULLrows:Extra:Using1rowinset(0.00 id:2 type:index_subquerypossible_keys:pany2_idkey:pany2_idkey_len:5ref:funcrows:2Extra:Using1rowinset(0.00sec第2/2下面是使用更有效 查询 id:2select_type:入 入type:index_subquerypossible_keys:pany2_idkey:pany2_idkey_len:5ref:funcrows:1Extra:1rowinset(0.00sec第2/2知知 知4.1知 •在MySQL中，可以使用函数PROCEDUREYSE（）对当前应用的表进行 mysql>select*fromduck_cust Field_n