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

《MySQL基础技能与原理―-基础技能篇.ppt》由会员分享，可在线阅读，更多相关《MySQL基础技能与原理―-基础技能篇.ppt（28页珍藏版）》请在三一办公上搜索。

1、MySQL基础技能与原理基础技能,MySQL DBA Team,彭立勋（http:/）,内容概要,1.MySQL开发流程规范2.MySQL数据类型与处理函数3.MySQL高级特性4.MySQL与Oracle差异比较5.MySQL调优基础,MySQL开发流程规范,线上文档：B2B-OPS命名规范：数据库/表/字段命名对象设计规范：数据类型，表设计，约束使用SQL编写规范：绑定变量与替代变量，数据类型转换，表连接，分页查询等,MySQL数据类型与处理函数,数值类型与处理函数字符类型与处理函数时间类型与处理函数类型处理小技巧官方文档：,数值类型与处理函数,BIT(M):位字段类型。M表示每个值的位数

2、，范围为从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。无符号的范

3、围是0到。8字节。Decimal(m,n):M是总位数，D是小数点(标度)后面的位数。实际上是VARCHAR存储。,数值类型与处理函数,算数符运算：SELECT expr;数学函数：ABS(x)，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值时，在它的右边填

4、充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除。在存储或检索过程中不进行大小写转换。占用空间(M*每字符字节长度)，UTF8为3字节。VarChar(M):列中的值为可变长字符串。长度可以指定为0到65,535之间的值。VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。占用空间(M*每字符字节长度+长度记录位)。,字符类型与处理函数,CHAR_LENGTH(str)：返回值为字符串str的长度，长度的单位为字符。LENGTH(str)：返回值为字符串st

5、r 的长度，单位为字节。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转化为

6、大写字母的字符。,日期类型与处理函数,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字节。,日期类型与处理函数,DATE_SUB/DATE_ADD：对时间进行

7、加减。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,类型处理小技巧,IP存储：INET_ATON(expr)，将IP转换为整数。INET_NTOA(expr)，将整数转换为IP。mysql SELECT INET_ATON(209.207.224.40);-3520061480mysql SELECT I

8、NET_NTOA(3520061480);-209.207.224.40判断IP段：INET_ATON(209.207.224.1)=IP AND IP=INET_ATON(209.207.224.255);VARCHAR存储：(3*4+3)+1=16位，UTF8字符集15*3+1=46字节INT存储：4字节,MySQL高级特性,存储过程基础触发器基础分区表基础应用场景,存储过程基础,官方文档：变量定义顺序必须是：存储函数变量游标定义游标异常程序主体定义务必加上授权：CREATE DEFINER=rootlocalhost PROCEDURE sp();存储过程相关权限：被操作表的相关权限及E

9、XECUTE(执行权限)，ALTER ROUTINE(修改权限)，CREATE ROUTINE(创建权限)。存储过程/删除内不能调用存储过程，但可以调用函数。不能使用动态游标，CURSOR中不能有动态的表名。查看创建存储过程/函数的语句：SHOW CREATE PROCEDURE/FUNCTION ps;查看所有存储过程/函数：SHOW PROCEDURE/FUNCTION STATUS LIKE ps;调用存储过程：CALL sp();调用存储函数：SELECT sp();,触发器基础,官方文档：定义务必加上授权：CREATE DEFINER=rootlocalhost Trigger tg

10、r();定义语句：CREATE DEFINER=rootlocalhost Trigger tgr()AFTER/BEFORE INSERT/UPDATE/DELETE ON table FOR EACH ROW;数据调用：NEW.*（更新后数据）OLD.*（更新前数据）行级触发器，每一行都会触发动作内部可以调用存储过程和函数每种类型的Trigger在一张表上只能建立一个,分区表基础,官方文档：分区类型：RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。HASH分区：基

11、于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。子分区：子分区是分区表中每个分区的再次分割。,分区表基础Range分区,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。CREATE TABLE employees(id INT NOT NULL,s

12、tore_id INT NOT NULL)PARTITION BY RANGE(store_id)(PARTITION p0 VALUES LESS THAN(6),/15 PARTITION p1 VALUES LESS THAN(11),/610 PARTITION p2 VALUES LESS THAN(16),/1115 PARTITION p3 VALUES LESS THAN MAXVALUE/16MAX)；,分区表基础List分区,LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值。LIST分区通过”VALUES IN(value_list)”的方式来定

13、义每个分区，其中”value_list”是一个通过逗号分隔的整数列表。CREATE TABLE employees(id INT NOT NULL,store_id INT)PARTITION BY LIST(store_id)PARTITION p1 VALUES IN(3,5,6,9,17),PARTITION p2 VALUES IN(1,2,10,11,19,20),PARTITION p3 VALUES IN(4,12,13,14,18),PARTITION p4 VALUES IN(7,8,15,16)；,分区表基础Hash分区,在HASH分区中，MySQL自动完成分区值，你所要做

14、的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。CREATE TABLE employees(id INT NOT NULL,store_id INT)PARTITION BY HASH(store_id)PARTITIONS 4；,分区表基础子分区,子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区，每个分区必须有相同数量的子分区。CREATE TABLE ts(id INT,purchased DATE)PARTITION BY RANGE(YEAR(purchased)SUBPARTITION BY HASH

15、(TO_DAYS(purchased)(PARTITION p0 VALUES LESS THAN(1990)(SUBPARTITION s0,SUBPARTITION s1),PARTITION p1 VALUES LESS THAN(2000)(SUBPARTITION s2,SUBPARTITION s3);,高级特性应用场景,存储过程不得包含业务逻辑，一般只做临时数据订正或固定逻辑用。触发器目前只用于Otter，一般不使用。分区表适合数据量大，但查询会固定在若干个数据集中的场景，但不要把分区表混合排序使用，可能带来严重的性能故障。（BRMMS发生过）,MySQL与Oracle差异比较,

16、优化器差异SQL方言差异常用功能差异,优化器差异,MySQL对子查询会处理为临时表，所以一般JOIN效率比做子查询高。MySQL在JOIN中对ORDER BY、GROUP BY优化较弱，JOIN中的排序分组几乎都会使用Fille Sort和临时表MySQL只有RBO，没有CBO，所以总是选择类型上最优的执行计划，而不一定是开销最小的。MySQL对于OR，很多时候并不会优化成两次索引访问或合并索引，所以OR最好写成两条SQL，做UNION ALL。IN中存在NULL条件，可能会让优化器走成全表扫描。,SQL方言差异,选择部分行：Oracle使用Rownum，MySQL使用LIMIT start,

17、end;执行函数：Oracle需要SELECT f()FROM dual;MySQL可以直接SELECT f();复制数据：Oracle必须有as，CREATE TABLE t AS SELECT*FROM table;MySQL的as是可选，CREATE TABLE t SELECT*FROM table;,常用功能差异,锁差异：Oracle锁加在数据块上，InnoDB锁加在主键索因上，所以InnoDB总是有主键索引。导入导出数据：Oracle采用EXP/IMP导入导出。MySQL采用mysqldump导出，导入可以采用管道或source。mysqldump u用户 p密码 DB table

18、1 table2 db.sql提交方式：MySQL默认自动提交，Oracle默认手动提交，MySQL大批量操作最好改变为手动提交，SET AUTOCOMMIT=0SQL缓存：MySQL只能缓存结果集，不能缓存SQL解析结果数据库对象：Oracle将数据库对象编译存储，直接执行二进制码，MySQL只存储代码，临时解析执行，所以MySQL触发器、存储过程等对象创建时仅检查语法，并不检查逻辑,MySQL调优基础,索引优化基础Schema设计优化SQL书写优化,索引优化基础,MySQL只在认为走索引可以筛去85%以上数据的时候，才使用索引，如果达不到这个标准，索引是无意义的。前缀索引：MySQL可以利

19、用索引的前向部分，但不可以利用后向部分，不支持反向索引。例如：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)的前缀，排序的优化：可以在条件是等于的时候继续使用索引排序，或者

20、条件中的不等于字段就是排序字段。例如：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要做的排序。没必要的时候不要做外连接，内连接效率比外连接高。,