《MySQL基础技能与原理-基础技能@阿里巴巴.ppt》由会员分享，可在线阅读，更多相关《MySQL基础技能与原理-基础技能@阿里巴巴.ppt（28页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、MySQL基础技能与原理基础技能与原理基础技能基础技能MySQL DBA Team 彭立勋（http:/）内容概要1.MySQL开发流程规范2.MySQL数据类型与处理函数3.MySQL高级特性4.MySQL与Oracle差异比较5.MySQL调优基础MySQL开发流程规范线上文档：B2B-OPS命名规范：数据库/表/字段命名对象设计规范：数据类型，表设计，约束使用SQL编写规范：绑定变量与替代变量，数据类型转换，表连接，分页查询等MySQL数据类型与处理函数数值类型与处理函数字符类型与处理函数时间类型与处理函数类型处理小技巧官方文档：http:/ http:/ expr;数学函数：ABS(x
2、)，x的绝对值。CEIL(x)，返回不小于X 的最小整数值。FLOOR(x)，返回不大于X的最大整数值 CRC32(x)，计算循环冗余码校验值。RAND()，返回01直接随机浮点数。SIGN(x)，返回x的符号 TRUNCATE(X,D)，返回X被舍去至小数点后D位的数字。字符类型与处理函数Char(M):长度固定为创建表时声明的长度M。长度可以为从0到255的任何值。当保存CHAR值时，在它的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除。在存储或检索过程中不进行大小写转换。占用空间(M*每字符字节长度)，UTF8为3字节。VarChar(M):列中的值为可变长字符串。
3、长度可以指定为0到65,535之间的值。VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。占用空间(M*每字符字节长度+长度记录位)。字符类型与处理函数CHAR_LENGTH(str)：返回值为字符串str的长度，长度的单位为字符。LENGTH(str)：返回值为字符串str 的长度，单位为字节。CONCAT(str1,str2,.)：返回结果为连接参数产生的字符串。CONCAT_WS(separator,str1,str2,.)：第一个参数是其它参数的分隔符。分隔符的
4、位置放在要连接的两个字符串之间。LEFT(str,len)：返回从字符串str开始的len最左字符。RIGHT(str,len)；从字符串str 开始，返回最右len 字符。SUBSTRING(str,pos,len)：从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。LOWER(str)：返回字符串 str 变为小写字母的字符。UPPER(str)：返回字符串str转化为大写字母的字符。日期类型与处理函数TIMESTAMP：以YYYY-MM-DD HH:MM:SS格式检索和显示TIMESTAMP值。支持的范围为1970-01-01 00:00:00到2037年。占用4
5、字节字节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字节字节。日期类型与处理函数DATE_SUB/DATE_ADD：对时间进行加减。CURDATE()：将当前日期按照YYYY-MM-DD 或YYYYMMDD 格式的值返回。NOW()：返回当前日期和时间值，其格式为 YYYY-MM-DD HH:MM:SS 或YYYYMMD
6、DHHMMSS。组合使用：mysql SELECT DATE_ADD(1998-01-02,INTERVAL 31 DAY);-1998-02-02类型处理小技巧IP存储存储：INET_ATON(expr)，将IP转换为整数。INET_NTOA(expr)，将整数转换为IP。mysql SELECT INET_ATON(209.207.224.40);-3520061480mysql SELECT INET_NTOA(3520061480);-209.207.224.40判断IP段：INET_ATON(209.207.224.1)=IP AND IP db.sql提交方式提交方式：MySQL默
7、认自动提交，Oracle默认手动提交，MySQL大批量操作最好改变为手动提交，SET AUTOCOMMIT=0SQL缓存缓存：MySQL只能缓存结果集，不能缓存SQL解析结果数据库对象数据库对象：Oracle将数据库对象编译存储，直接执行二进制码，MySQL只存储代码，临时解析执行，所以MySQL触发器、存储过程等对象创建时仅检查语法，并不检查逻辑MySQL调优基础索引优化基础Schema设计优化SQL书写优化索引优化基础MySQL只在认为走索引可以筛去85%以上数据的时候，才使用索引，如果达不到这个标准，索引是无意义的。前缀索引前缀索引：MySQL可以利用索引的前向部分，但不可以利用后向部分
8、，不支持反向索引。例如：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)的前缀，排序的优化排序的优化：可以在条件是等于的时候继续使用索引排序，或者条件中的不等于
9、字段就是排序字段。例如：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也是以排序为基础，优化同上。Schema设计优化TEXT/BLOB字段分开单表存储与原表主键一一对应。经常查询的字段与不经常查询的字段分开存储，用主键一一对应，例如帖子标题和内容。频繁进行统计的SQL，可以转化为表存储，将查询压力分散到更新时。频繁的对表进行count也可以转化为统计表存储，计数表的技巧。SQL书写优化尽量将子查询转化为链接查询，除非子查询只返回极少的记录。不要在条件里写不必要的条件。没有必要排序的分组采用ORDER BY null指定不排序。OR条件若不能正确的走索引，则拆分为两条SQL。不在乎重复的情况下，UNION ALL可以避免UNION要做的排序。没必要的时候不要做外连接，内连接效率比外连接高。