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

如对本文有疑问， 点击进行留言回复！！