MySQL基础技能与原理―-基础技能篇

MySQL基础技能与原理

——基础技能

MySQLDBATeam

彭立勋（.penglixun.）MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第1页!内容概要1.MySQL开发流程规范2.MySQL数据类型与处理函数3.MySQL高级特性4.MySQL与Oracle差异比较5.MySQL调优基础MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第2页!MySQL开发流程规范线上文档：B2B-OPS命名规范：数据库/表/字段命名对象设计规范：数据类型，表设计，约束使用SQL编写规范：绑定变量与替代变量，数据类型转换，表连接，分页查询等MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第3页!数值类型与处理函数BIT(M):位字段类型。M表示每个值的位数，范围为从1到64。M默认为1。TinyINT(M):带符号的范围是-32768到32767。

无符号的范围是0到65535。1字节。SmallINT(M):带符号的范围是-32768到32767。

无符号的范围是0到65535。2字节。MediumINT(M):带符号的范围是-8388608到8388607。

无符号的范围是0到16777215。3字节。INT(M):带符号的范围是-2147483648到2147483647。

无符号的范围是0到4294967295。4字节。BigINT(n):带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。8字节。Decimal(m,n):

M是总位数，D是小数点(标度)后面的位数。实际上是VARCHAR存储。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第4页!字符类型与处理函数Char(M):长度固定为创建表时声明的长度M。长度可以为从0到255的任何值。当保存CHAR值时，在它的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除。在存储或检索过程中不进行大小写转换。占用空间(M*每字符字节长度)，UTF8为3字节。VarChar(M):列中的值为可变长字符串。长度可以指定为0到65,535之间的值。VARCHAR的最大有效长度由最大行大小和使用的字符集确定。

整体最大长度是65,532字节。VARCHAR值保存时不进行填充。

当值保存和检索时尾部的空格仍保留，符合标准SQL。

占用空间(M*每字符字节长度+长度记录位)。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第5页!日期类型与处理函数TIMESTAMP：以'YYYY-MM-DDHH:MM:SS'格式检索和显示TIMESTAMP值。支持的范围为‘1970-01-0100:00:00’到2037年。占用4字节DATETIME：以'YYYY-MM-DDHH:MM:SS'格式检索和显示DATETIME值。

支持的范围为'1000-01-0100:00:00'到'9999-12-3123:59:59'。

占用8字节。DATE：用'YYYY-MM-DD'格式检索和显示DATE值。

支持的范围是'1000-01-01'到'9999-12-31'。

占用4字节。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第6页!类型处理小技巧IP存储：INET_ATON(expr)，将IP转换为整数。INET_NTOA(expr)，将整数转换为IP。mysql>SELECTINET_ATON('209.207.224.40');->3520061480mysql>SELECTINET_NTOA(3520061480);->'209.207.224.40‘判断IP段：INET_ATON('209.207.224.1')<=IPANDIP<=INET_ATON('209.207.224.255');VARCHAR存储：(3*4+3)+1=16位，UTF8字符集15*3+1=46字节INT存储：4字节MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第7页!存储过程基础官方文档：dev.mysql./doc/refman/5.1/zh/stored-procedures.html变量定义顺序必须是：存储函数变量游标定义游标异常程序主体定义务必加上授权：CREATEDEFINER=`root`@`localhost`PROCEDUREsp();存储过程相关权限：被操作表的相关权限及EXECUTE(执行权限)，ALTERROUTINE(修改权限)，CREATEROUTINE(创建权限)。存储过程/删除内不能调用存储过程，但可以调用函数。不能使用动态游标，CURSOR中不能有动态的表名。查看创建存储过程/函数的语句：SHOWCREATEPROCEDURE/FUNCTIONps;查看所有存储过程/函数：SHOWPROCEDURE/FUNCTIONSTATUS[LIKEps];调用存储过程：CALLsp();调用存储函数：SELECTsp();MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第8页!分区表基础官方文档：dev.mysql./doc/refman/5.1/zh/partitioning.html分区类型：RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。子分区：子分区是分区表中每个分区的再次分割。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第9页!分区表基础—List分区LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值。LIST分区通过”VALUESIN(value_list)”的方式来定义每个分区，其中”value_list”是一个通过逗号分隔的整数列表。CREATETABLEemployees(idINTNOTNULL,store_idINT)PARTITIONBYLIST(store_id)PARTITIONp1VALUESIN(3,5,6,9,17),PARTITIONp2VALUESIN(1,2,10,11,19,20),PARTITIONp3VALUESIN(4,12,13,14,18),PARTITIONp4VALUESIN(7,8,15,16))；MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第10页!分区表基础—子分区子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区，每个分区必须有相同数量的子分区。CREATETABLEts(idINT,purchasedDATE)PARTITIONBYRANGE(YEAR(purchased))SUBPARTITIONBYHASH(TO_DAYS(purchased))(PARTITIONp0VALUESLESSTHAN(1990)(SUBPARTITIONs0,SUBPARTITIONs1),PARTITIONp1VALUESLESSTHAN(2000)(SUBPARTITIONs2,SUBPARTITIONs3));MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第11页!MySQL与Oracle差异比较优化器差异SQL方言差异常用功能差异MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第12页!SQL方言差异选择部分行：Oracle使用Rownum，MySQL使用LIMITstart,end;执行函数：Oracle需要SELECTf()FROMdual;MySQL可以直接SELECTf();复制数据：Oracle必须有as，CREATETABLEtASSELECT*FROMtable;MySQL的as是可选，CREATETABLEtSELECT*FROMtable;MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第13页!MySQL调优基础索引优化基础Schema设计优化SQL书写优化MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第14页!Schema设计优化TEXT/BLOB字段分开单表存储与原表主键一一对应。经常查询的字段与不经常查询的字段分开存储，用主键一一对应，例如帖子标题和内容。频繁进行统计的SQL，可以转化为表存储，将查询压力分散到更新时。频繁的对表进行count也可以转化为统计表存储，《计数表的技巧》。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第15页!MySQL数据类型与处理函数数值类型与处理函数字符类型与处理函数时间类型与处理函数类型处理小技巧官方文档：dev.mysql./doc/refman/5.1/zh/column-types.html

dev.mysql./doc/refman/5.1/zh/functions.htmlMySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第16页!数值类型与处理函数算数符运算：SELECTexpr;数学函数：ABS(x)，x的绝对值。CEIL(x)，返回不小于X的最小整数值。FLOOR(x)，返回不大于X的最大整数值CRC32(x)，计算循环冗余码校验值。RAND()，返回0~1直接随机浮点数。SIGN(x)，返回x的符号TRUNCATE(X,D)，返回X被舍去至小数点后D位的数字。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第17页!字符类型与处理函数CHAR_LENGTH(str)：返回值为字符串str的长度，长度的单位为字符。LENGTH(str)：返回值为字符串str的长度，单位为字节。CONCAT(str1,str2,...)：返回结果为连接参数产生的字符串。CONCAT_WS(separator,str1,str2,...)：个参数是其它参数的分隔符。

分隔符的位置放在要连接的两个字符串之间。LEFT(str,len)：返回从字符串str开始的len最左字符。RIGHT(str,len)；从字符串str开始，返回最右len字符。SUBSTRING(str,pos,len)：从字符串str返回一个长度同len字符相同的子字符串，

起始于位置pos。LOWER(str)：返回字符串str变为小写字母的字符。UPPER(str)：返回字符串str转化为大写字母的字符。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第18页!日期类型与处理函数DATE_SUB/DATE_ADD：对时间进行加减。CURDATE()：将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回。NOW()：返回当前日期和时间值，

其格式为‘YYYY-MM-DDHH:MM:SS’或YYYYMMDDHHMMSS。组合使用：mysql>SELECTDATE_ADD('1998-01-02',INTERVAL31DAY);->'1998-02-02'MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第19页!MySQL高级特性存储过程基础触发器基础分区表基础应用场景MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第20页!触发器基础官方文档：dev.mysql./doc/refman/5.1/en/create-trigger.html定义务必加上授权：CREATEDEFINER=`root`@`localhost`Triggertgr();定义语句：CREATEDEFINER=`root`@`localhost`Triggertgr()AFTER/BEFOREINSERT/UPDATE/DELETEONtableFOREACHROW;数据调用：NEW.*（更新后数据）OLD.*（更新前数据）行级触发器，每一行都会触发动作内部可以调用存储过程和函数每种类型的Trigger在一张表上只能建立一个MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第21页!分区表基础—Range分区每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUESLESSTHAN操作符来进行定义。CREATETABLEemployees(idINTNOTNULL,store_idINTNOTNULL)PARTITIONBYRANGE(store_id)(PARTITIONp0VALUESLESSTHAN(6),//1~5PARTITIONp1VALUESLESSTHAN(11),//6~10PARTITIONp2VALUESLESSTHAN(16),//11~15PARTITIONp3VALUESLESSTHANMAXVALUE//16~MAX)；MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第22页!分区表基础—Hash分区在HASH分区中，MySQL自动完成分区值，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。CREATETABLEemployees(idINTNOTNULL,store_idINT)PARTITIONBYHASH(store_id)PARTITIONS4；MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第23页!高级特性应用场景存储过程不得包含业务逻辑，一般只做临时数据订正或固定逻辑用。触发器目前只用于Otter，一般不使用。分区表适合数据量大，但查询会固定在若干个数据集中的场景，但不要把分区表混合排序使用，可能带来严重的性能故障。（BRMMS发生过）MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第24页!优化器差异MySQL对子查询会处理为临时表，所以一般JOIN效率比做子查询高。MySQL在JOIN中对ORDERBY、GROUPBY优化较弱，JOIN中的排序分组几乎都会使用FilleSort和临时表MySQL只有RBO，没有CBO，所以总是选择类型上最优的执行计划，而不一定是开销最小的。MySQL对于OR，很多时候并不会优化成两次索引访问或合并索引，所以OR最好写成两条SQL，做UNIONALL。IN中存在NULL条件，可能会让优化器走成全表扫描。MySQL基础技能与原理―-基础技能篇共28页，您现在浏览的是第25页!常用功能差异锁差异：Oracle锁加在数据块上，InnoDB锁加在主键索因上，所以InnoDB总是有主键索引。导入导出数据：Oracle采用EXP/IMP导入导出。MySQL采用mysqldump导出，导入可以采用管道或source。mysqldump–u用户–p密码DB[table1table2…]>db.sql提交方式：MySQL默认自动提交，Oracle默认手动提交，MySQL大批量操作最好改变为手动提交，SETAUTOCOMMIT=0SQL缓存：MySQL只能缓存结果集，不能缓存SQL解析结果数据库对象：Oracle将数据库对象编译存储，直接执行二进制码，MySQL只存储代码，临时解析执行，所以My