MySQL数据库表类型 (存储引擎)的选择
-------------------------------表类型 (存储引擎)的选择-------------------------------
--1.MySQL 存储引擎概述
1.和大多数数据库不同，MySQL 中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎;
2.其中 InnoDB 和 BDB 提供事务安全表，其他存储引擎都是非事务安全表。
3.创建新表不指定表的存储引擎，则新表是默认存储引擎(InnoDB)的.
4.如果需要修改默认的存储引擎，则可以在参数文件中设置 default-table-type
5.查看当前的默认存储引擎:mysql> show variables like 'table_type';
6.查询当前数据库支持的存储引擎:
1.SHOW ENGINES \G;--查看当前支持哪些存储引擎
2.SHOW VARIABLES LIKE 'have%';--同一
3.show engines;--查看mysql现在已提供什么存储引擎
4.show variables like '%storage_engine%';--查看mysql当前默认的存储引擎
7.修改一个已经存在的存储引擎：alter table [tableName] engine = [engineName];
--问题：MySQL为什么要把InnoDB定为默认的存储引擎?
因为InnoDB存储引擎十分优秀。
--2.各种存储引擎的特性(常用)
--MyISAM
优势：访问速度快
--每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：
.frm(存储表定义);
.MYD(MYData，存储数据);
.MYI (MYIndex，存储索引)。
MyISAM 类型的表提供修复的工具，可以用 CHECK TABLE 语句来检查 MyISAM 表的健康，
并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。
表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。
mysql> check table t_book;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| db_book.t_book | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (0.02 sec)
--MyISAM 的表又支持 3 种不同的存储格式，分别是：
静态(固定长度)表;
动态表;
压缩表。
--静态表
静态表是默认的存储格式。
静态表中的字段都是非变长字段，这样每个记录都是固定长度的，
这种存储方式的
--优点是存储非常迅速，容易缓存，出现故障容易恢复;
--缺点是占用的空间通常比动态表多。
静态表的数据在存储的时候会按照列的宽度定义补足空格，
但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。
example：
mysql> create table Myisam_char (name char(10)) engine=myisam;
mysql> insert into Myisam_char values('abcde'),('abcde '),(' abcde'),(' abcde ');
mysql> select name,length(name) from Myisam_char;
+---------+--------------+
| name | length(name) |
+---------+--------------+
| abcde | 5 |
| abcde | 5 |
| abcde | 7 |
| abcde | 7 |
+---------+--------------+
4 rows in set (0.00 sec)--插入记录后面的空格都被去掉了，前面的空格保留
--动态表;
动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，
但是频繁地更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句
或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。
--压缩表：
压缩表由 myisampack 工具创建，占据非常小的磁盘空间。
因为每个记录是被单独压缩的，所以只有非常小的访问开支。
--InnoDB
--1.自动增长列
InnoDB 表的自动增长列可以手工插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值。
LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条记录，
那么返回的是第一条记录使用的自动增长值。
example：
mysql> create table autoincre_demo
-> (d1 smallint not null auto_increment,
-> d2 smallint not null,
-> name varchar(10),
-> index(d2,d1)
-> )engine=myisam;
mysql> insert into autoincre_demo(d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3') ,(4,'4');
mysql> select * from autoincre_demo;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
| 1 | 2 | 2 |
| 1 | 3 | 3 |
| 1 | 4 | 4 |
| 2 | 2 | 2 |
| 2 | 3 | 3 |
| 2 | 4 | 4 |
+----+----+------+
6 rows in set (0.00 sec)
--2.外键约束
当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。
--3.存储方式
--InnoDB 存储表和索引有以下两种方式：
1.使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引
保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。
2.使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但是每个
表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd
文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件
的位置，以此来将表的 IO 均匀分布在多个磁盘上。
--注意：即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB 把内部数据词典和未作日志放在这个文件中。
--MEMORY
MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。
MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。
--给 MEMORY 表创建索引的时候，可以指定使用 HASH 索引还是 BTREE 索引：
mysql> create index mem_hash USING HASH on tab_memory (city_id) ;
mysql> drop index mem_hash on tab_memory;
服务器需要足够内存来维持所有在同一时间使用的 MEMORY 表，
当不再需要 MEMORY 表的内容之时，要释放被MEMORY表使用的内存，
应该执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表(使用 DROP TABLE 操作)。
每个 MEMORY 表中可以放置的数据量的大小，受到 max_heap_table_size 系统变量的约
束，这个系统变量的初始值是 16MB，可以按照需要加大。此外，在定义 MEMORY 表的时候，
可以通过 MAX_ROWS 子句指定表的最大行数。
--MERGE
MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，MERGE
表本身并没有数据，对 MERGE 类型的表可以进行查询、更新、删除的操作，这些操作实际
上是对内部的实际的 MyISAM 表进行的。可以对 MERGE 表进行 DROP 操作，
这个操作只是删除 MERGE 的定义，对内部的表没有任何的影响。
mysql> create table payment_2016(
country_id smallint,
payment_date datetime,
amount DECIMAL(15,2),
KEY idx_fk_country_id (country_id)
)engine=myisam;
mysql> create table payment_2017(
country_id smallint,
payment_date datetime,
amount DECIMAL(15,2),
KEY idx_fk_country_id (country_id)
)engine=myisam;
mysql> CREATE TABLE payment_all(
country_id smallint,
payment_date datetime,
amount DECIMAL(15,2),
INDEX(country_id)
)engine=merge union=(payment_2016,payment_2017) INSERT_METHOD=LAST;
-- INSERT_METHOD=LAST 表示将记录插入到payment_all表的最后一行;
--如果插入的时候指定为payment_2016表时，插入操作不会自动将记录放到payment_2016表的后面，而是插入到payment_2017表的后面;
--这也是 MERGE 表和分区表的区别，MERGE 表并不能智能地将记录写到对应的表中，而分区表是可以的。
--3.如何选择合适的存储引擎
参考《常用存储引擎的对比》表;
选择使用哪种存储引擎才是最佳方案也不是绝对的，这需要根据用户各自的应用进行测试，从而得到最适合自己的结果。
MyISAM：是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一，以读操作和插入操作为主。
InnoDB：对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。(InnoDB存储引擎是十分优秀的存储引擎)
MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。
MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，
其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。
MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE： 表的优点在于可以突破对单个 MyISAM 表大小的限制，
并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。
这对于诸如数据仓储等 VLDB 环境十分适合。
-------------------------------选择合适的数据类型-------------------------------
--1.CHAR与VARCHAR
CHAR 与 VARCHAR的比较
-- CHAR VARCHAR
字符类型 固定长度 可变长度
处理速度 快 慢
缺点 浪费存储空间
(程序需要对行尾空格进行处理)
适用范围 长度变化不大并且
对查询速度有较高要求的数据
--在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同：
1.MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
2.MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或 VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。
3.InnoDB 存储引擎：建议使用 VARCHAR 类型，InnoDB主要的性能因素是数据行使用的存储总量。
--2.TEXT 与 BLOB
BLOB 能用来保存二进制数据，比如照片;
TEXT 只能保存字符数据，比如一篇文章或者日记。
TEXT: TEXT、MEDIUMTEXT、LONGTEXT
BLOB: BLOB、MEDIUMBLOB、LONGBLOB3
BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。
删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。
为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避
免因为“空洞”导致性能问题。
example：
mysql> create table t (id varchar(100),context text);
mysql> insert into t values(1,repeat('haha',100)); --使用repeat函数向t表中插入大量数据
mysql> insert into t values(2,repeat('haha',100));
mysql> insert into t values(3,repeat('haha',100));
mysql> insert into t select * from t;
mysql> exit --退出到操作系统下，查看表 t 的物理文件大小
mysql> delete from t where id=1; --从t表中删除id为1的数据;
mysql> exit --再次退出到操作系统下，查看表 t 的物理文件大小
$ du -sh t.* --发现t表的数据量并没有因为删除而减少,存在'空洞'
mysql> OPTIMIZE TABLE t;--OPTIMIZE[??pt?ma?z] 优化
$ du -sh t.* --再次查看t表的大小,可以发现“空洞”空间已经被回收
--使用合成的(Synthetic)索引来提高大文本字段(BLOB 或 TEXT)的查询性能
example：
mysql> create table t (id varchar(100),context blob,hash_value varchar(40));
mysql> insert into t values(1,repeat('beijing',2),md5(context)); --hash_value列用来存储context列的MD5散列值
mysql> insert into t values(2,repeat('beijing',2),md5(context));
mysql> insert into t values(3,repeat('beijing 2008',2),md5(context));
mysql> select * from t;
mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));--通过id=3的md5散列值来查询
--前缀索引：对 BLOB 或者 CLOB 字段进行模糊查询
mysql> create index idx_blob on t(context(100)); --只为字段的前 n 列创建索引
mysql> desc select * from t where context like 'beijing%' \G;
--注意：这里的查询条件中，“%”不能放在最前面，否则索引将不会被使用
1.在不必要的时候避免检索大型的 BLOB 或 TEXT 值;慎用select * ;
2.把 BLOB 或 TEXT 列分离到单独的表中;减少主表中的碎片，可以得到固定长度数据行的性能优势。
它还可以使主数据表在运行 SELECT * 查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。
--3.浮点数与定点数
在 MySQL 中 float、double(或 real)用来表示浮点数。
在 MySQL 中，decimal (或 numberic)用来表示定点数。
定点数实际上是以字符串形式存放的,所以定点数可以更加精确的保存数据.
--浮点数与定点数的区别
mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
mysql> insert into test values(131072.32,131072.32);
mysql> select * from test;
+-----------+-----------+
| c1 | c2 |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)2
--浮点数的比较也是一个普遍存在的问题
public class Test{
public static void main(String[] args) throws Exception{
System.out.println("7.22-7.0 = " + (7.22f-7.0f));
}
}
Result: 7.22-7.0 = 0.21999979
所以在编程中应尽量避免浮点数的比较，如果非要使用浮点数比较则最好使用范围比较而不要使用“==”比较
--注意： 在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则：
1.浮点数存在误差问题;
2.对货币等对精度敏感的数据，应该用定点数表示或存储;
3.在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较;
4.要注意浮点数中一些特殊值的处理。
--提供浮点数精确运算的算法：
import java.math.BigDecimal;
/**
*support exact arithmetic
*@param v1
*@param v2
*/
public class Test{
public static void main(String[] args) throws Exception{
System.out.print("7.22-7.0=" + subtract(7.22,7.0));
}
public static double subtract(double v1,double v2){
BigDecimal b1 = new BigDecimal(Double.toString(v1));
BigDecimal b2 = new BigDecimal(Double.toString(v2));
return b1.subtract(b2).doubleValue();
}
}
--4.日期类型选择
1.根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年
份”，那么用 1 个字节来存储的 YEAR 类型完全可以满足，而不需要用 4 个字节来
存储的 DATE 类型。这样不仅仅能节约存储，更能够提高表的操作效率。
2.如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，
而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。
3.如果记录的日期需要让不同时区的用户使用，那么最好使用 TIMESTAMP，因为日
期类型中只有它能够和实际时区相对应。
-------------------------------字符集-------------------------------
--1.字符集概述
ASCII:American Standard Code for Information Interchange -> ISO-646
--2.Unicode 简述
Unicode:双字节编码
Unicode 2.0等同于ISO/IEC 10646-1:1993。
Unicode 3.0等同于ISO/IEC 10646-1:2000。
Unicode 4.0等同于ISO/IEC 10646:2003。
--3.汉字及一些常见字符集
GB2312-80、GB13000、GBK、GB18030
常用字符集比较
-- 字符集 是否定长 编码方式 其他说明
ACSII 是 单字节 7 位编码 最早的奠基性字符集
ISO-8859-1/latin1 是 单字节 8 位编码 西欧字符集，经常被一些程序员用来转码
GB2312-80 是 双字节编码 早期标准，不推荐再使用
GBK 是 双字节编码 虽然不是国标，但支持的系统不少
GB18030 否 2 字节或 4 字节编码 开始有一些支持，但数据库支持的还少见
UTF-32 是 4 字节编码 UCS-4 原始编码，目前很少采用
UCS-2 是 2 字节编码 Windows 2000 内部用 UCS-2
UTF-16 否 2 字节或 4 字节编码 Java 和 Windows XP/NT 等内部使用 UTF-16
UTF-8 否 1 至 4 字节编码 互联网和 UNIX/Linux 广泛支持的 Unicode 字符集;MySQLServer 也使用 UTF-8
--4.怎样选择合适的字符集
(1)满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，
就应该选择 Unicode 字符集。对 MySQL 来说，目前就是 UTF-8。
(2)如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。
假如已有数据是 GBK 文字，如果选择 GB2312-80 为数据库字符集，就很可能出现某些文字无法正确导入的问题。
(3)如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择
双字节定长编码的中文字符集，比如 GBK。因为，相对于 UTF-8 而言，GBK 比较“小”，
每个汉字只占2个字节，而UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O、数据库cache，
以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数
据，那么选择 UTF-8 更好，因为 GBK、UCS-2、UTF-16 的西文字符编码都是 2 个字节,会造成很大不必要的开销。
(4)如果数据库需要做大量的字符运算，如比较、排序等，选择定长字符集可能更好，
因为定长字符集的处理速度要比变长字符集的处理速度快。
(5)如果所有客户端程序都支持相同的字符集，应该优先选择该字符集作为数据库字
符集。这样可以避免因字符集转换带来的性能开销和数据损失。
--5.MySQL 支持的字符集简介
MySQL 服务器可以支持多种字符集，在同一台服务器、同一个数据库、甚至同一个表
的不同字段都可以指定使用不同的字符集，相比 Oracle 等其他数据库管理系统，在同一个
数据库只能使用相同的字符集，MySQL 明显存在更大的灵活性。
查看所有可用的字符集：show character set;
显示所有的字符集和该字符集默认的校对规则:desc information_schema.character_sets;
每个字符集至少对应一个校对规则。可以用“SHOW COLLATION LIKE '***';
”命令或者查看 information_schema.COLLATIONS;SHOW COLLATION LIKE 'gbk%';
--6.MySQL 字符集的设置
MySQL 的字符集和校对规则有 4 个级别的默认设置：
服务器级、
数据库级、
表级和字
段级。
它们分别在不同的地方设置，作用也不相同。
1.服务器字符集和校对规则
查询当前服务器的字符集：show variables like 'character_set_server';
查询当前服务器的校对规则：show variables like 'collation_server';
2.数据库字符集和校对规则
设置数据库字符集的规则是：
1) 如果指定了字符集和校对规则，则使用指定的字符集和校对规则;
2)如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则;
3)如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则。
3.表字符集和校对规则
设置表的字符集的规则和上面基本类似;
推荐在创建表的时候明确指定字符集和校对规则，避免受到默认值的影响。
显示表的字符集和校对规则：show create table t_book \G;
4.列字符集和校对规则
一般遇到这种情况的几率比较小，这只是 MySQL 提供给我们一个灵活设置的手段。
5.连接字符集和校对规则
客户端和服务器之间交互的字符集和校对规则的设置
通常情况下，基本不需要用户强制指定字符串字符集。
--7.字符集的修改步骤
字符集的修改不能直接通过“alter database character set ***”或者“alter table tablename character set ***”
命令进行，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。
已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成。
-- 模拟的是将 latin1 字符集的数据库修改成 GBK 字符集的数据库的过程
(1)导出表结构：
mysqldump -uroot -p --default-character-set=gbk -d databasename> createtab.sql
--default-character-set=gbk 表示设置以什么字符集连接，
-d 表示只导出表结构，不导出数据。
(2)手工修改 createtab.sql 中表结构定义中的字符集为新的字符集。
(3)确保记录不再更新，导出所有记录。
mysqldump -uroot -p --quick --no-create-info --extended-insert
--default-character-set=latin1 databasename> data.sql
--quick：该选项用于转储大的表。
它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中。
--extended-insert：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件更小，重载文件时可以加速插入。
--no-create-info：不写重新创建每个转储表的 CREATE TABLE 语句。
--default-character-set=latin1：按照原有的字符集导出所有数据，这样导出的文件中，
所有中文都是可见的，不会保存成乱码。
(4)打开 data.sql，将 SET NAMES latin1 修改成 SET NAMES gbk。
(5)使用新的字符集创建新的数据库。
create database databasename default charset gbk;
(6)创建表，执行 createtab.sql。
mysql -uroot -p databasename < createtab.sql
(7)导入数据，执行 data.sql。
mysql -uroot -p databasename < data.sql