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

1、MySQL根底技艺与原理根底技艺与原理根底技艺根底技艺MySQL DBA Team 彭立勋penglixun内容概要1. MySQL开发流程规范2. MySQL数据类型与处置函数3. MySQL高级特性4. MySQL与Oracle差别比较5. MySQL调优根底MySQL开发流程规范线上文档：B2B-OPS命名规范：数据库/表/字段命名对象设计规范：数据类型，表设计，约束运用SQL编写规范：绑定变量与替代变量，数据类型转换，表衔接，分页查询等MySQL数据类型与处置函数数值类型与处置函数字符类型与处置函数时间类型与处置函数类型处置小技巧官方文档：dev.mysql/doc/refman/5.

2、1/zh/column-types.html dev.mysql/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): 带符号的范围是-214748

3、3648到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

4、()，前往01直接随机浮点数。 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字节。

5、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

6、)；从字符串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-

7、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 DA

8、Y); - 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默许自动提交，默许自动提交，Oracle默许手动提交，默许手动

9、提交，MySQL大批量操作最大批量操作最好改动为手动提交，好改动为手动提交，SET AUTOCOMMIT=0SQL缓存：缓存：MySQL只能缓存结果集，不能缓存只能缓存结果集，不能缓存SQL解析结果解析结果数据库对象：数据库对象：Oracle将数据库对象编译存储，直接执行二进制码，将数据库对象编译存储，直接执行二进制码，MySQL只存只存储代码，暂时解析执行，所以储代码，暂时解析执行，所以MySQL触发器、存储过程等对象创建时仅检查触发器、存储过程等对象创建时仅检查语法，并不检查逻辑语法，并不检查逻辑MySQL调优根底索引优化根底Schema设计优化SQL书写优化索引优化根底MySQL只在以为

10、走索引可以筛去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

11、,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也是以排序为根底，优化同上。Schema设计优化TEXT/BLOB字段分开单表存储与原表主键一一对应。经常查询的字段与不经常查询的字段分开存储，用主键一一对应，例如帖子标题和内容。频繁进展统计的SQL，可以转化为表存储，将查询压力分散到更新时。频繁的对表进展coun