MySQL基础技能与原理——基础技能

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,MySQL,基础技能与原理,基础技能,MySQL DBA Team,彭立勋（）,1,内容概要,1. MySQL,开发流程规范,2. MySQL,数据类型与处理函数,3. MySQL,高级特性,4. MySQL,与,Oracle,差异比较,5. MySQL,调优基础,2,MySQL,开发流程规范,线上文档：,B2B-OPS,命名规范：数据库,/,表,/,字段命名,对象设计规范：数据类型，表设计，约束使用,SQL,编写规范：绑定变量与替代变量，数据类型转换，表连接，分页查询等,3,MySQL,数据类型与处理函数,数值类型与处理函数,字符类型与处理函数,时间类型与处理函数,类型处理小技巧,官方文档：,4,数值类型与处理函数,BIT(M):,位字段类型。,M,表示每个值的位数，范围为从,1,到,64,。,M,默认为,1,。,TinyINT(M):,带符号的范围是,-32768,到,32767,。,无符号的范围是,0,到,65535,。,1,字节,。,SmallINT(M):,带符号的范围是,-32768,到,32767,。,无符号的范围是,0,到,65535,。,2,字节,。,MediumINT(M):,带符号的范围是,-8388608,到,。,无符号的范围是,0,到,。,3,字节,。,INT(M):,带符号的范围是,-2147483648,到,2147483647,。,无符号的范围是,0,到,4294967295,。,4,字节,。,BigINT(n):,带符号的范围是,-92233728,到,92233727,。,无符号的范围是,0,到,184467445,。,8,字节,。,Decimal(m,n):,M,是总位数，,D,是小数点,(,标度,),后面的位数。实际上是,VARCHAR,存储。,5,数值类型与处理函数,算数符运算：,SELECT expr;,数学函数：,ABS(x),，,x,的绝对值。,CEIL(x),，返回不小于,X,的最小整数值。,FLOOR(x),，返回不大于,X,的最大整数值,CRC32(x),，计算循环冗余码校验值。,RAND(),，返回,01,直接随机浮点数。,SIGN(x),，返回,x,的符号,TRUNCATE(X,D),，返回,X,被舍去至小数点后,D,位的数字。,6,字符类型与处理函数,Char(M):,长度固定为创建表时声明的长度,M,。长度可以为从,0,到,255,的任何值。,当保存,CHAR,值时，在它的右边填充空格以达到指定的长度。,当检索到,CHAR,值时，尾部的空格被删除。,在存储或检索过程中不进行大小写转换。,占用空间,(M*,每字符字节长度,),，,UTF8,为,3,字节。,VarChar(M):,列中的值为可变长字符串。长度可以指定为,0,到,65,535,之间的值。,VARCHAR,的最大有效长度由最大行大小和使用的字符集确定。,整体最大长度是,65,532,字节。,VARCHAR,值保存时不进行填充。,当值保存和检索时尾部的空格仍保留，符合标准,SQL,。,占用空间,(,M*,每字符字节长度,+,长度记录位,),。,7,字符类型与处理函数,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,转化为大写字母的字符。,8,日期类型与处理函数,TIMESTAMP,：以,YYYY-MM-DD HH:MM:SS,格式检索和显示,TIMESTAMP,值。,支持的范围为,1970-01-01 00:00:00,到,2037,年。,占用,4,字节,DATETIME,：以,YYYY-MM-DD HH:MM:SS,格式检索和显示,DATETIME,值。,支持的范围为,1000-01-01 00:00:00,到,9999-12-31 23:59:59,。,占用,8,字节,。,DATE,：用,YYYY-MM-DD,格式检索和显示,DATE,值。,支持的范围是,1000-01-01,到,9999-12-31,。,占用,4,字节,。,9,日期类型与处理函数,DATE_SUB/DATE_ADD,：对时间进行加减。,CURDATE(),：将当前日期按照,YYYY-MM-DD,或,YYYYMMDD,格式的值返回。,NOW(),：返回当前日期和时间值，,其格式为,YYYY-MM-DD HH:MM:SS,或,YYYYMMDDHHMMSS,。,组合使用：,mysql SELECT DATE_ADD(1998-01-02, INTERVAL 31 DAY);,- 1998-02-02,10,类型处理小技巧,IP,存储,：,INET_ATON(expr),，将,IP,转换为整数。,INET_NTOA(expr),，将整数转换为,IP,。,mysql SELECT INET_ATON(209.207.224.40);,- 3520061480,mysql SELECT INET_NTOA(3520061480);,- 209.207.224.40,判断,IP,段：,INET_ATON(209.207.224.1) = IP AND IP ,db.sql,提交方式,：,MySQL,默认自动提交，,Oracle,默认手动,提交，,MySQL,大批量操作最好改变为手动提交，,SET AUTOCOMMIT=0,SQL,缓存,：,MySQL,只能缓存结果集，不能缓存,SQL,解析结果,数据库对象,：,Oracle,将数据库对象编译存储，直接执行二进制码，,MySQL,只存储代码，临时解析执行，所以,MySQL,触发器、存储过程等对象创建时仅检查语法，并不检查逻辑,24,MySQL,调优基础,索引优化基础,Schema,设计优化,SQL,书写优化,25,索引优化基础,MySQL,只在认为走索引可以筛去,85%,以上数据的时候，才使用索引，如果达不到这个标准，索引是无意义的。,前缀索引,：,MySQL,可以利用索引的前向部分，但不可以利用后向部分，不支持反向索引。,例如：,SELECT col2 FROM table WHERE col1=1;,可以使用索引,(col1,col2),，但不可以使用,(col2,col1).,不等比较的优化,：如果索引的前向部分在,WHERE,中是等于，那么可以使用索引，如果索引的前向部分再,WHERE,中是不等比较，那么不可以为后面的等于比较使用索引,例如：,SELECT col1 FROM table WHERE col2=1 AND col3 10;,可以完全使用索引,(col2,col3),，但只可以使用,(col3,col2),的前缀，,排序的优化,：可以在条件是等于的时候继续使用索引排序，或者条件中的不等于字段就是排序字段。,例如：,SELECT col1 FROM table WHERE col2=1 AND col30 ORDER BY col3,，可以完全使用索引,(col2,col3),避免排序。但条件是,col21 AND col3=0 ORDER BY col3,时则只能使用,col2,排除记录，不可以用来排序。,GROUP BY/DISTINCT,也是以排序为基础，优化同上。,26,Schema,设计优化,TEXT/BLOB,字段分开单表存储与原表主键一一对应。,经常查询的字段与不经常查询的字段分开存储，用主键一一对应，例如帖子标题和内容。,频繁进行统计的,SQL,，可以转化为表存储，将查询压力分散到更新时。,频繁的对表进行,count,也可以转化为统计表存储，,计数表的技巧,。,27,SQL,书写优化,尽量将子查询转化为链接查询，除非子查询只返回极少的记录。,不要在条件里写不必要的条件。,没有必要排序的分组采用,ORDER BY null,指定不排序。,OR,条件若不能正确的走索引，则拆分为两条,SQL,。,不在乎重复的情况下，,UNION ALL,可以避免,UNION,要做的排序。,没必要的时候不要做外连接，内连接效率比外连接高。,28,