欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库表类型 (存储引擎)的选择

程序员文章站 2022-07-02 20:11:13
-------------------------------表类型 (存储引擎)的选择------------------------------- --1.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