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

MySQL DBA Team

彭立勋（ http://www.penglixun.com ）

内容概要

• 1. MySQL 开发流程规范• 2. MySQL 数据类型与处理函数• 3. MySQL 高级特性• 4. MySQL 与 Oracle 差异比较• 5. MySQL 调优基础

MySQL 开发流程规范

• 线上文档： B2B-OPS• 命名规范：数据库 / 表 / 字段命名• 对象设计规范：数据类型，表设计，约束使用• SQL 编写规范：绑定变量与替代变量，数据类型转换，表连接，分页查询等

MySQL 数据类型与处理函数

• 数值类型与处理函数• 字符类型与处理函数• 时间类型与处理函数• 类型处理小技巧• 官方文档： http://dev.mysql.com/doc/refman/5.1/zh/column-types.html• http://dev.mysql.com/doc/refman/5.1/zh/functions.html

数值类型与处理函数

• 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 存储。

数值类型与处理函数

• 算数符运算： SELECT expr;• 数学函数： ABS(x) ， x 的绝对值。• CEIL(x) ，返回不小于 X 的最小整数值。• FLOOR(x) ，返回不大于 X 的最大整数值 • CRC32(x) ，计算循环冗余码校验值。• RAND() ，返回 0~1 直接随机浮点数。• SIGN(x) ，返回 x 的符号• TRUNCATE(X,D) ，返回 X被舍去至小数点后 D 位的数字。

字符类型与处理函数

• Char(M): 长度固定为创建表时声明的长度 M 。长度可以为从 0 到 255 的任何值。• 当保存 CHAR 值时，在它的右边填充空格以达到指定的长度。• 当检索到 CHAR 值时，尾部的空格被删除。• 在存储或检索过程中不进行大小写转换。• 占用空间 (M* 每字符字节长度 ) ， UTF8 为 3 字节。• VarChar(M): 列中的值为可变长字符串。长度可以指定为 0 到 65,535 之间的值。• VARCHAR 的最大有效长度由最大行大小和使用的字符集确定。• 整体最大长度是 65,532 字节。• VARCHAR 值保存时不进行填充。• 当值保存和检索时尾部的空格仍保留，符合标准 SQL 。• 占用空间 (M*每字符字节长度 + 长度记录位 )。

字符类型与处理函数

• 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 转化为大写字母的字符。

日期类型与处理函数

• 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 ：对时间进行加减。• 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');• -> 3520061480• mysql> SELECT INET_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 高级特性

• 存储过程基础• 触发器基础• 分区表基础• 应用场景

存储过程基础

• 官方文档： http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html• 变量定义顺序必须是：存储函数变量游标定义游标异常程序主体• 定义务必加上授权： CREATE DEFINER=`root`@`localhost` PROCEDURE sp();• 存储过程相关权限：被操作表的相关权限及 EXECUTE( 执行权限 ) ，• ALTER ROUTINE( 修改权限 ) ， CREATE ROUTINE( 创建权限 ) 。• 存储过程 / 删除内不能调用存储过程，但可以调用函数。• 不能使用动态游标， CURSOR 中不能有动态的表名。• 查看创建存储过程 / 函数的语句： SHOW CREATE PROCEDURE/FUNCTION ps;• 查看所有存储过程 / 函数： SHOW PROCEDURE/FUNCTION STATUS [LIKE ps];• 调用存储过程： CALL sp();• 调用存储函数： SELECT sp();

触发器基础

• 官方文档： http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html• 定义务必加上授权： CREATE DEFINER=`root`@`localhost` Trigger tgr();• 定义语句： CREATE DEFINER=`root`@`localhost` Trigger tgr()• AFTER/BEFORE INSERT/UPDATE/DELETE ON table FOR EACH ROW;• 数据调用： NEW.* （更新后数据） OLD.* （更新前数据）• 行级触发器，每一行都会触发动作• 内部可以调用存储过程和函数• 每种类型的 Trigger 在一张表上只能建立一个

分区表基础

• 官方文档： http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html• 分区类型：• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。• LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一

个离散值集合中的某个值来进行选择。• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达

式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

• KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

• 子分区：子分区是分区表中每个分区的再次分割。

分区表基础— Range 分区

• 每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用 VALUES LESS THAN 操作符来进行定义。

• CREATE TABLE employees (• id INT NOT NULL,• store_id INT NOT NULL• )• PARTITION BY RANGE (store_id) (• PARTITION p0 VALUES LESS THAN (6), //1~5• PARTITION p1 VALUES LESS THAN (11), //6~10• PARTITION p2 VALUES LESS THAN (16), //11~15• PARTITION p3 VALUES LESS THAN MAXVALUE // 16~MAX• ) ；

分区表基础— List 分区

• LIST 分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值。 LIST 分区通过” VALUES IN (value_list)” 的方式来定义每个分区，其中” 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自动完成分区值，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

• 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(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 差异比较

• 优化器差异• 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,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 [table1 table2…] > db.sql

• 提交方式： MySQL 默认自动提交， Oracle 默认手动提交， MySQL 大批量操作最好改变为手动提交， SET AUTOCOMMIT=0

• SQL缓存： MySQL只能缓存结果集，不能缓存 SQL解析结果

• 数据库对象： Oracle 将数据库对象编译存储，直接执行二进制码， MySQL只存储代码，临时解析执行，所以 MySQL 触发器、存储过程等对象创建时仅检查语法，并不检查逻辑

MySQL 调优基础

• 索引优化基础• Schema 设计优化• SQL书写优化

索引优化基础

• 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 col3>0 ORDER BY col3 ，可以完全使用索引 (col2,col3)避免排序。但条件是 col2<1 AND col3=0 ORDER BY col3 时则只能使用 col2排除记录，不可以用来排序。

• GROUP BY/DISTINCT也是以排序为基础，优化同上。

Schema 设计优化

• TEXT/BLOB 字段分开单表存储与原表主键一一对应。• 经常查询的字段与不经常查询的字段分开存储，用主键一一对应，例如帖子

标题和内容。• 频繁进行统计的 SQL ，可以转化为表存储，将查询压力分散到更新时。• 频繁的对表进行 count也可以转化为统计表存储，《计数表的技巧》。

SQL书写优化

• 尽量将子查询转化为链接查询，除非子查询只返回极少的记录。• 不要在条件里写不必要的条件。• 没有必要排序的分组采用 ORDER BY null 指定不排序。• OR条件若不能正确的走索引，则拆分为两条 SQL 。• 不在乎重复的情况下， UNION ALL 可以避免 UNION 要做的排序。• 没必要的时候不要做外连接，内连接效率比外连接高。