MySQL基础及开发注意事项课件

1、内容概要数据类型与处理函数存储引擎及常用存储引擎特点及存储结构应用层性能优化MYSQL复制第1页，共31页。数据类型使用原则选择表示数据的最小类型选择简单的数据类型尽量避免NULL数据类型第2页，共31页。整数类型TinyINT(M): 带符号的范围是-128到127。 无符号的范围是0到255。1字节。SmallINT(M): 带符号的范围是-32768到32767 无符号的范围是0到65535。2字节。MediumINT(M): 带符号的范围是-8388608到8388607。 无符号的范围是0到16777215。3字节。INT(M): 带符号的范围是-2147483648到2147483

2、647。 无符号的范围是0到4294967295。4字节。BigINT(n): 带符号的范围是-9223372036854775808到9223372036854775807。 无符号的范围是0到18446744073709551615。8字节。第3页，共31页。字符类型Char(M): 长度固定为创建表时声明的长度M。长度可以为从0到255的任何值。 当保存CHAR值时，在它的右边填充空格以达到指定的长度。 当检索到CHAR值时，尾部的空格被删除。 在存储或检索过程中不进行大小写转换。 占用空间(M*每字符字节长度)，UTF8为3字节。VarChar(M): 列中的值为可变长字符串。长度可以

3、指定为0到65,535之间的值。 VARCHAR的最大有效长度由最大行大小和使用的字符集确定。 整体最大长度是65,532字节。 VARCHAR值保存时不进行填充。 当值保存和检索时尾部的空格仍保留，符合标准SQL。 占用空间(M*每字符字节长度+长度记录位)。第4页，共31页。字符串函数 MySQL中字符串连接方法，使用CONCAT() 或CONCAT_ WS()函数，语法如下： CONCAT(string1,string2,.) CONCAT_ WS(separator,string1,string2,.) 字符串长度统计： LENGTH(string) #返回string所占的字节数 C

4、HAR_LENGTH(string) #返回string中的字符个数 统计字符个数，就不区分是汉字还是字母或数字，也跟字符集没有关系，若统计的是字节数，则由字符是汉字、字母或数字类型，以及字符集共同决定。字符串函数第5页，共31页。日期类型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。

5、 占用8字节。DATE：用YYYY-MM-DD格式检索和显示DATE值。 支持的范围是1000-01-01到 9999-12-31。 占用4字节。第6页，共31页。日期操作函数日期操作函数 获取当前时间：NOW()，CURDATE()、CURTIME() NOW()函数精确到秒，格式：YYYY-MM-DD HH:MM:SS CURDATE函数精确到天，格式：YYYY-MM-DD CURTIME函数精确到秒，格式：HH:MM:SS日期数值的加减函数： DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type) 常用的几种

6、type类型：YEAR、MONTH、DAY、HOUR、MINUTE，其中expr可以为正数或负数，我们在开过程中，一般使用DATE_ADD()函数，若要作日期减去一个数字的方式，就使用负数。 DATEDIFF(expr1,expr2)，是返回 开始日期expr1与 结束日期expr2之间，相差的天数 ，返回值为正数或负数。返回日期某部分信息的函数：YEAR(expr1) 返回日期expr1部分的年份； MONTH(expr1) 返回日期expr1部分的月份；DAY(expr1)返回expr1部分的天数；WEEKDAY(expr1)返回expr1对应的星期数字第7页，共31页。类型转换类型转换函

7、数 字符串转换成日期方式，DATE_FORMAT()或STR_TO_DATE()， 两个函数的格式如下： DATE_FORMAT(expr1,format) STR_TO_DATE(expr1, format) 常用的日期格式YYYY-MM-DD HH:MM:SS 对应的format为 %Y-%m-%d %H:%i:%S 通用的类型转换函数： CAST(expr AS type) CONVERT(expr,type) CONVERT(expr USING transcoding_name) 第8页，共31页。注意事项数据类型转换规范基本原则： 在所有 Query 的 Where 条件中必须使用

8、和过滤字段完全一致的数据类型，杜绝任何隐式类型转换，避免造成因为数据类型不匹配而导致 Query 执行计划的出错，造成性能问题.详细说明：1 所有 Where 条件的字段上不允许使用函数做类型转换，如有需要转换类型，只能转换过滤值,而不是转换字段.2 在表连接 Query 中，如果连接条件两端的数据类型不一致，必须保证将驱动表的连接条件数据类型转换为与被驱动表一致的数据类型. 第9页，共31页。注意事项字段上添加函数使用规范基本原则： 禁止在 WHERE 条件中出现的过滤字段上，使用任何函数进行类型或格式的转换；正确的做法是把传入比较的值转换为列类型所需要的。错误的写法：SELECT user

9、name FROM gl_user WHERE DATE_FORMAT(gmt_create, %Y%m%d%H%i%s)=20090501022300;正确的写法：SELECT username FROM gl_user WHERE gmt_create=DATE_FORMAT(20090501022300, %Y-%m-%d %H:%i:s);第10页，共31页。存储引擎 MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。 InnoDB

10、：用于事务处理应用程序，具有众多特性，包括ACID事务支持。 BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。 Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。 Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。 Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据

11、库。十分适合于分布式环境或数据集市环境。 Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。 Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件）， Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。 第11页，共31页。MyISAM MyISAM 特点 数据存储方式简单，使用 B+ Tree 进行索引 使用三个文件定义一个表：.MYI .MYD .frm 少碎片、支持大文件、能够进行索引压缩 二进制层次的文

12、件可以移植 (Linux Windows) 访问速度飞快，是所有MySQL文件引擎中速度最快的 不支持一些数据库特性，比如 事务、外键约束等 Table level lock，性能稍差，更适合读取多的操作 表数据容量有限，一般建议单表数据量介于 50w200w 第12页，共31页。MyISAMMyISAM 索引结构第13页，共31页。InnoDBInnoDB 特点使用 Table Space 的方式来进行数据存储 (ibdata1, ib_logfile0) 支持 事务、外键约束等数据库特性 Rows level lock , 读写性能都非常优秀 能够承载大数据量的存储和访问 拥有自己独立的缓

13、冲池，能够缓存数据和索引 在关闭自动提交的情况下，与MyISAM引擎速度差异不大第14页，共31页。InnoDBInnoDB 数据结构第15页，共31页。InnoDB实现两种B+Tree索引，一种是列值为Key，主键位置为Value即 (列值, 主键位置) 的非主键索引（Secondary Index），另一种是主键索引，两种索引的每个叶子节点都有一个双向指针分别指向前驱和后继节点。主键索引即聚集索引（Cluster Index），它不仅有主键，而且有主键所属的全部数据，所以在InnoDB中，主键索引即数据。在InnoDB中，即使用户不指定主键，InnoDB也会生成一个隐含主键，这种情况下，I

14、nnoDB的性能比采用序列主键性能下降30%左右。InnoDB第16页，共31页。应用优化设计合理的数据表结构：适当的数据冗余对数据表建立合适有效的数据库索引数据查询：编写简洁高效的SQL语句第17页，共31页。Schema设计优化TEXT/BLOB字段分开单表存储与原表主键一一对应。经常查询的字段与不经常查询的字段分开存储，用主键一一对应，例如帖子标题和内容。频繁进行统计的SQL，可以转化为表存储，将查询压力分散到更新时。频繁的对表进行count也可以转化为统计表存储选择合适的数据类型：如果能够定长尽量定长使用 ENUM 而不是 VARCHAR,ENUM类型是非常快和紧凑的，在实际上，其保存

15、的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美 。为了避免联表查询，有时候可以适当的数据冗余，比如邮箱、姓名这些不容易更改的数据最好给每个字段都设定 default 值第18页，共31页。索引优化索引建立原则（一） 一般针对数据分散的关键字进行建立索引，比如ID、QQ， 像性别、状态值等等建立索引没有意义 字段唯一，最少，不可为null 对大数据量表建立聚集索引，避免更新操作带来的碎片。 尽量使用短索引，一般对int、char/varchar、date/time 等 类型的字段建立索引 需要的时候建立联合索引，但是要注意查询SQL语句的编写 谨

16、慎建立 unique 类型的索引（唯一索引） 大文本字段不建立为索引，如果要对大文本字段进行检索， 可以考虑全文索引 频繁更新的列不适合建立索引第19页，共31页。索引建立原则（二） order by 字句中的字段，where 子句中字段，最常用的sql 语句中字段，应建立索引。 唯一性约束，系统将默认为改字段建立索引。 对于只是做查询用的数据库索引越多越好，但对于在线实时 系统建议控制在5个以内。 索引不仅能提高查询SQL性能，同时也可以提高带where字句 的update，Delete SQL性能。 Decimal 类型字段不要单独建立为索引，但覆盖索引可以包 含这些字段。 只有建立索引以

17、后，表内的行才按照特地的顺序存储，按照 需要可以是asc或desc方式。 如果索引由多个字段组成将最用来查询过滤的字段放在前面 可能会有更好的性能。索引优化第20页，共31页。索引优化MySQL只在认为走索引可以筛去85%以上数据的时候，才使用索引，如果达不到这个标准，索引是无意义的。前缀索引：MySQL可以利用索引的前向部分，但不可以利用后向部分，不支持反向索引。 例如：SELECT col2 FROM table WHERE col1=1;可以使用索引(col1,col2)，但不可以使用(col2,col1).不等比较的优化：如果索引的前向部分在WHERE中是等于，那么可以使用索引，如果索

18、引的前向部分再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)避免排序。但条件是col2, =, =, , =, IF NULL和BETWEEN 将会

19、使用 索引， 如果对某个索引字段进行 LIKE 查询，使用 LIKE %abc% 不能使用索引，使用 LIKE abc% 将能够使用索引 如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法 使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也将失效 使用 Explain 语句来帮助改进我们的SQL语句第22页，共31页。编写高效的 SQL （二） 不要在where 子句中的“=”左边进行算术或表达式运算，否则系统将 可能无法正确使用索引 尽量不要在where条件中使用函数，否则将不能使用索引 避免使用 select *, 只取需要的字段 对于大数据量的查询，尽量避免在SQL语句中使用order by 字句，避免 额为的开销，替代为使用ADO.NET 来实现。 如果插入的数据量很大，用select into 替代 insert into 能带来更好的性能 采用连接操作，避免过多的子查询，产生的CPU和IO开销 只关心需要的表和满足条件的数据 适当使用临