深入浅出MySQL学习笔记（二）

参考文献

第七章、表类型&＃xff08;存储引擎&＃xff09;的选择

1.两种查看当前引擎的方法&＃xff1a;
【1】 SHOW ENGINES \G
【2】SHOW VARIABLES LIKE ‘have%’&＃xff1b;
2.改变已有表的存储引擎&＃xff1a;
可以使用 ALTER TABLE 语句&＃xff0c;将一个已经存在的表修改成其他的存储引擎&＃xff1a; alter table ai engine &＃61; innodb&＃xff1b;

3.几种常用存储引擎&＃xff1a;

MyISAM引擎

&＃xff08;1&＃xff09;概述&＃xff1a;
MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键&＃xff0c;其优势是访问的速度快&＃xff0c;对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。
&＃xff08;2&＃xff09;组成&＃xff1a;
每个 MyISAM 在磁盘上存储成 3 个文件&＃xff0c;其文件名都和表名相同&＃xff0c;但扩展名分别是&＃xff1a;
 .frm&＃xff08;存储表定义&＃xff09;&＃xff1b;
 .MYD&＃xff08;MYData&＃xff0c;存储数据&＃xff09;&＃xff1b;
 .MYI &＃xff08;MYIndex&＃xff0c;存储索引&＃xff09;。
数据文件和索引文件可以放置在不同的目录&＃xff0c;平均分布 IO&＃xff0c;获得更快的速度。
&＃xff08;3&＃xff09;修复&＃xff1a;
可以用 CHECK TABLE 语句来检查 MyISAM 表的健康&＃xff1b;
并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表&＃xff1b;
&＃xff08;4&＃xff09;表的三种存储格式&＃xff1a;
【1】静态&＃xff08;固定长度&＃xff09;表&＃xff1a;
优点是存储非常迅速&＃xff0c;容易缓存&＃xff0c;出现故障容易恢复&＃xff1b;
缺点是占用的空间通常比动态表多&＃xff1b;
【2】动态表&＃xff1a;
其中包含变长字段&＃xff0c;记录不是固定长度的&＃xff1b;
优点是占用的空间相对较少&＃xff1b;
缺点是频繁地更新删除记录会产生碎片&＃xff0c;需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能&＃xff0c;并且出现故障的时候恢复相对比较困难。
【3】压缩表&＃xff1a;由 myisampack 工具创建&＃xff0c;占据非常小的磁盘空间&＃xff1b;

InnoDB引擎

&＃xff08;1&＃xff09;概述&＃xff1a;
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。
但是对比 MyISAM的存储引擎&＃xff0c;InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
&＃xff08;2&＃xff09;InnoDB的特点&＃xff1a;
①自动增长列&＃xff1a;auto_increment
InnoDB 表的自动增长列可以手工插入&＃xff0c;但是插入的值如果是空或者 0&＃xff0c;则实际插入的将是自动增长后的值&＃xff1b;
可以通过“ ALTER TABLE *** AUTO_INCREMENT &＃61; n&＃xff1b; ”语句强制设置自动增长列的初识值&＃xff0c;默认从 1 开始&＃xff1b;
对于 InnoDB 表&＃xff0c;自动增长列必须是索引。如果是组合索引&＃xff0c;也必须是组合索引的第一列&＃xff0c;但是对于 MyISAM 表&＃xff0c;自动增长列可以是组合索引的其他列&＃xff0c;这样插入记录后&＃xff0c;自动增长列是按照组合索引的前面几列进行排序后递增的&＃xff1b;
②外键约束
MySQL 支持外键的存储引擎只有 InnoDB&＃xff0c;在创建外键的时候&＃xff0c;要求父表必须有对应的索引&＃xff0c;子表在创建外键的时候也会自动创建对应的索引。
在创建索引的时候&＃xff0c;可以指定在删除、更新父表时&＃xff0c;对子表进行的相应操作&＃xff0c;包括&＃xff1a;RESTRICT、CASCADE、SET NULL 和 NO ACTION。其中&＃xff1a;
【1】RESTRICT 和 NO ACTION 相同&＃xff0c;是指限制在子表有关联记录的情况下父表不能更新&＃xff1b;
【2】CASCADE 表示父表在更新或者删除时&＃xff0c;更新或者删除子表对应记录&＃xff1b;
【3】SET NULL 则表示父表在更新或者删除的时候&＃xff0c;子表的对应字段被 SET NULL&＃xff1b;
选择后两种方式的时候要谨慎&＃xff0c;可能会因为错误的操作导致数据的丢失。

当某个表被其他表创建了外键参照&＃xff0c;那么该表的对应索引或者主键禁止被删除。
在导入多个表的数据时&＃xff0c;如果需要忽略表之前的导入顺序&＃xff0c;可以暂时关闭外键的检查&＃xff1b;
同样&＃xff0c;在执行 LOAD DATA 和 ALTER TABLE 操作的时候&＃xff0c;可以通过暂时关闭外键约束来加快处理的速度&＃xff0c;关闭的命令是“SET FOREIGN_KEY_CHECKS &＃61; 0;”&＃xff0c;执行完成之后&＃xff0c;通过执行“SET FOREIGN_KEY_CHECKS &＃61; 1;”语句改回原状态。

Memory存储引擎

Merge存储引擎

引擎选择经验&＃xff1a;
【1】MyISAM&＃xff1a;默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主&＃xff0c;只有很少的更新和删除操作&＃xff0c;并且对事务的完整性、并发性要求不是很高&＃xff0c;那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
【2】 InnoDB&＃xff1a;用于事务处理应用程序&＃xff0c;支持外键。如果应用对事务的完整性有比较高的要求&＃xff0c;在并发条件下要求数据的一致性&＃xff0c;数据操作除了插入和查询以外&＃xff0c;还包括很多的更新、删除操作&＃xff0c;那么 InnoDB 存储引擎应该是比较合适的选择。
【3】MEMORY
【4】MERGE

第八章、选择合适的数据类型

在使用 MySQL 创建数据表时都会遇到一个问题&＃xff0c;如何为字段选择合适的数据类型。

1.CHAR 和 VARCHAR

CHAR属于固定长度的字符类型&＃xff0c;而 VARCHAR 属于可变长度的字符类型。
由于 CHAR 是固定长度的&＃xff0c;所以它的处理速度比 VARCHAR 快得多&＃xff0c;但是其缺点是浪费存储空间&＃xff0c;程序需要对行尾空格进行处理&＃xff0c;所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用 CHAR 类型来存储。
另外&＃xff0c;随着 MySQL 版本的不断升级&＃xff0c;VARCHAR 数据类型的性能也在不断改进并提高&＃xff0c;所以在许多的应用中&＃xff0c;VARCHAR 类型被更多地使用。

2.TEXT 与 BLOB

一般在保存少量字符串的时候&＃xff0c;我们会选择 CHAR 或者 VARCHAR&＃xff1b;
而在保存较大文本时&＃xff0c;通常会选择使用 TEXT 或者 BLOB&＃xff1b;
二者的主要差别是 BLOB 能用来保存二进制数据&＃xff0c;比如照片&＃xff1b;而 TEXT 只能保存字符数据&＃xff0c;比如一篇文章或者日记&＃xff1b;

BLOB 和 TEXT 值会引起一些性能问题&＃xff0c;特别是在执行了大量的删除操作时。
删除操作会在数据表中留下很大的“空洞”&＃xff0c;以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能&＃xff0c;建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理&＃xff0c;避免因为“空洞”导致性能问题。

3.浮点数与定点数

浮点数&＃xff1a;一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后&＃xff0c;如果插入数据的精度超过该列定义的实际精度&＃xff0c;则插入值会被四舍五入到实际定义的精度值&＃xff0c;然后插入&＃xff0c;四舍五入的过程不会报错。在 MySQL 中 float、double&＃xff08;或 real&＃xff09;用来表示浮点数。
定点数&＃xff1a;不同于浮点数&＃xff0c;定点数实际上是以字符串形式存放的&＃xff0c;所以定点数可以更加精确的保存数据。

4.日期的选择

&＃xff08;1&＃xff09;根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”&＃xff0c;则用 1 个字节来存储的 YEAR 类型完全可以满足&＃xff0c;而不需要用 4 个字节来存储的 DATE 类型。这样不仅能节约存储&＃xff0c;更能够提高表的操作效率&＃xff1b;
&＃xff08;2&＃xff09;如果要记录年月日时分秒&＃xff0c;并且记录的年份比较久远&＃xff0c;那么最好使用 DATETIME&＃xff0c;而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多&＃xff1b;
&＃xff08;3&＃xff09;如果记录的日期需要让不同时区的用户使用&＃xff0c;那么最好使用 TIMESTAMP&＃xff0c;因为日期类型中只有它能够和实际时区相对应&＃xff1b;

第九章、字符集

第十章、索引的设计和使用