收藏本站 收藏本站
积木网首页 - 软件测试 - 常用手册 - 站长工具 - 技术社区
积木学院 > 数据库 > MySQL > 正文

MySQL数据库数据类型详解与性能对比

来源:本站原创 日期:2008-08-20 23:37
blackartrockMySQL学习笔记(一)

Table of Contents

1. MySQL数据库的数据
1.1. 数据值类型(data type)
1.1.1. 数值
1.1.2. 字符串
1.1.3. 日期和时间
1.2. 列类型(column type)
1.2.1. 数值类的数据列类型
1.2.2. 字符串类数据列类型
1.2.3. 日期,时间型数据列类型
1.3. 唯一编号
1.4. 字符集支持
1.4.1. MySQL4.1以前版本
1.4.2. MySQL4.1以后版本
1.4.3. 各级字符集的查询方法
1.4.4. Unicode支持
1.5. 如何选择数据列类型?
1.6. 表达式操作符
1.7. 类型转换
2. 查询优化
2.1. 索引
2.2. 查询优化程序
2.3. 数据列类型与查询效率
2.4. 有效地加载数据
2.5. 调度和锁定
2.6. 服务器优化
2.7. 硬件优化
3. 数据库管理
3.1. 数据目录
3.2. MySQL数据表在系统中表现形式
3.3. 数据表最大尺寸限制
3.4. 状态文件和日志文件
3.5. 调整MySQL数据目录位置

Chapter 1. MySQL数据库的数据

Table of Contents

1.1. 数据值类型(data type)
1.1.1. 数值
1.1.2. 字符串
1.1.3. 日期和时间
1.2. 列类型(column type)
1.2.1. 数值类的数据列类型
1.2.2. 字符串类数据列类型
1.2.3. 日期,时间型数据列类型
1.3. 唯一编号
1.4. 字符集支持
1.4.1. MySQL4.1以前版本
1.4.2. MySQL4.1以后版本
1.4.3. 各级字符集的查询方法
1.4.4. Unicode支持
1.5. 如何选择数据列类型?
1.6. 表达式操作符
1.7. 类型转换

MySQL数据库是由数据组成的,为了能方便管理和使用这些数据,我们把这些数据进行分类,形成各种数据类型,有数据值的类型,有表中数据列的类型,有数据表的类型。理解MySQL的这些数据类型能使我们更好地使用MySQL数据库。下面对各种数据类型进行简单的介绍。

1.1. 数据值类型(data type)

对MySQL中数据值的分类,有数值型、字符型、日期型和空值等,这和一般的编程语言的分类差不多。

1.1.1. 数值

MySQL中的数值分整型和浮点型两种。MySQL支持科学记数法。整型可以是十进制,也可是十六进制数。

1.1.2. 字符串

MySQL支持以单或双引号包围的字符序列。如“MySQL tutorial”、‘Mysql Database’。

MySQL能识别字符串中的转义序列,转义序列用反斜杠(\)表示。下面是一个转义序列列表。

Table 1.1. 转义序列

转义序列 含义
\0 NUL(ASCII的0值)
\' 单引号
\" 双引号
\b 后退符
\n 换行符
\r 回车符
\t 制表符
\\ 反斜杠
\Z Ctrl+Z

如果字符串本身包含有单双引号,则用以下三种方法中的一种来表示:

  • 字符串的引号和字符串两端的引号双同,则双写该引号。如:'mysql''s test'。

  • 用与字符串的引号不同的引号把字符串引起来,如:"mysql's test"。

  • 用反斜杠转义引号,如:"mysql\' test",'mysql\' test'。这样就不用理会字符串两端的是单引号还是双引号了。

字符串可由一个十六进制数表示,如0x61表示字符"a"。由MySQL 4.0开始,字符串值也可用ANSI SQL表示法X'val'来表示。如X'61'表示字符"a"。

从MySQL 4.1开始,可以为字符串值专门指定一个字符集。

1.1.3. 日期和时间

MySQL默认按“年-月-日”的顺序显示日期。

1.2. 列类型(column type)

MySQL 数据库的表是一个二维表,由一个或多个数据列构成。每个数据列都有它的特定类型,该类型决定了MySQL如何看待该列数据,我们可以把整型数值存放到字符类型的列中,MySQL则会把它看成字符串来处理。MySQL中的列类型有三种:数值类、字符串类和日期/时间类。从大类来看列类型和数值类型一样,都是只有三种。但每种列类型都还可细分。下面对各种列类型进行详细介绍。

1.2.1. 数值类的数据列类型

数值型的列类型包括整型和浮点型两大类。

Table 1.2. 数值类数据列类型

数据列类型 存储空间 描述
TINYINT 1字节 非常小的正整数,带符号:-128~127,不带符号:0~255
SMALLINT 2字节 小整数,带符号:-32768~32767,不带符号:0~65535
MEDIUMINT 3字节 中等大小的整数,带符号:-8388608~8388607,不带符号:0~16777215
INT 4字节 标准整数,带符号:-2147483648~2147483647,不带符号:0~4294967295
BIGINT 8字节 大整数,带符号:-9223372036854775808~9233372036854775807,不带符号:0~18446744073709551615
FLOAT 4字节 单精度浮点数,最小非零值:+-1.175494351E-38,最大非零值:+-3.402823466E+38
DOUBLE 8字节 双精度浮点数,最小非零值:+-2.2250738585072014E-308,最大非零值:+-1.7976931348623157E+308
DECIMAL M+2字节 以字符串形式表示的浮点数,它的取值范围可变,由M和D的值决定。

1.2.1.1. 整型数据列类型

MySQL有五种整型数据列类型,即TINYINT,SMALLINT,MEDIUMINT,INT和BIGINT。它们之间的区别是取值范围不同,存储空间也各不相同。在整型数据列后加上UNSIGNED属性可以禁止负数,取值从0开始。

声明整型数据列时,我们可以为它指定个显示宽度M(1~255),如INT(5),指定显示宽度为5个字符,如果没有给它指定显示宽度,MySQL会为它指定一个默认值。显示宽度只用于显示,并不能限制取值范围和占用空间,如:INT(3)会占用4个字节的存储空间,并且允许的最大值也不会是999,而是 INT整型所允许的最大值。

1.2.1.2. 浮点型数据列类型

MySQL有三种浮点型数据列类型,分别是:FLOAT,DOUBLE和DECIMAL。浮点类数据类型有一个最大可表示值和一个最小非零可表示值,最小非零可表示值决定了该类型的精确度。

MySQL 4.0.2版之后,FLOAT和DOUBLE都可以指定UNSIGNED属性。当指定该属性时,取值范围不平移到正数区间,而只是简单地把浮点类型的负数部份去掉。

浮点类型也有M(1~255)和D(1~30,且不能大于M-2)。分别表示显示宽度和小数位数。M和D在FLOAT和DOUBLE中是可选的,默认,当 MySQL版本大于3.23.6时,FLOAT和DOUBLE类型将被保存为硬件所支持的最大精度。DECIMAL的M和D值在MySQL3.23.6后可选,默认D值为0,M值为10。

1.2.1.3. 如何选择数值类数据列类型?

为了节省存储空间和提高数据库处理效率,我们应根据应用数据的取值范围来选择一个最适合的数据列类型。如果把一个超出数据列取值范围的数存入该列,则 MySQL就会截短该值,如:我们把99999存入SMALLINT(3)数据列里,因为SMALLINT(3)的取值范围是-32768~32767,所以就会被截短成32767存储。显示宽度3不会影响数值的存储。只影响显示。

对于浮点数据列,存入的数值会被该列定义的小数位进行四舍五入。如把一个1.234存入FLOAT(6.1)数据列中,结果是1.2。

DECIMAL 与FLOAT和DOUBLE的区别是:DECIMAL类型的值是以字符串的形式被储存起来的,它的小数位数是固定的。它的优点是,不会象FLOAT和 DOUBLE类型数据列那样进行四舍五入而产生误差,所以很适合用于财务计算;而它的缺点是:由于它的存储格式不同,CPU不能对它进行直接运算,从而影响运算效率。DECIMAL(M,D)总共要占用M+2个字节。

1.2.1.4. 数值类数据列的属性

  • ZEROFILL属性适用于所有数值类数据列类型,作用是,如果数值的宽度小于定义的显示宽度,则在数值前填充0。

  • UNSIGNED属性不允许数据列出现负数。

  • AUTO_INCREMENT属性可生成独一无二的数字序列。只对整数类的数据列有效。

  • NULL和NOT NULL属性设置数据列是否可为空。

  • DEFAULT属性可为数据列指定默认值。

1.2.2. 字符串类数据列类型

字符串可以用来表示任何一种值,所以它是最基本的类型之一。我们可以用字符串类型来存储图象或声音之类的二进制数据,也可存储用gzip压缩的数据。下表介绍了各种字符串类型:

Table 1.3. 字符串类数据列类型

类型 最大长度 占用存储空间
CHAR[(M)] M字节 M字节
VARCHAR[(M)] M字节 L+1字节
TINYBLOD,TINYTEXT 2^8-1字节 L+1字节
BLOB,TEXT 2^16-1字节 L+2
MEDIUMBLOB,MEDIUMTEXT 2^24-1字节 L+3
LONGBLOB,LONGTEXT 2^32-1字节 L+4
ENUM('value1','value2',...) 65535个成员 1或2字节
SET('value1','value2',...) 64个成员 1,2,3,4或8字节

L+ 1、L+2是表示数据列是可变长度的,它占用的空间会根据数据行的增减面则改变。数据行的总长度取决于存放在这些数据列里的数据值的长度。L+1或L+2 里多出来的字节是用来保存数据值的长度的。在对长度可变的数据进行处理时,MySQL要把数据内容和数据长度都保存起来。

如果把超出字符串最大长度的数据放到字符类数据列中,MySQL会自动进行截短处理。

ENUM和SET类型的数据列定义里有一个列表,列表里的元素就是该数据列的合法取值。如果试图把一个没有在列表里的值放到数据列里,它会被转换为空字符串(“”)。

字符串类型的值被保存为一组连续的字节序列,并会根据它们容纳的是二进制字符串还是非二进制字符而被区别对待为字节或者字符:

  • 二进制字符串被视为一个连续的字节序列,与字符集无关。MySQL把BLOB数据列和带BINARY属性的CHAR和VARCHAR数据列里的数据当作二进制值。

  • 非二进制字符串被视为一个连续排列的字符序列。与字符集有关。MySQL把TEXT列与不带BINARY属性的CHAR和VARCHAR数据列里的数据当作二进制值对待。

在MySQL4.1以后的版本中,不同的数据列可以使用不同的字符集。在MySQL4.1版本以前,MySQL用服务器的字符集作为默认字符集。

非二进制字符串,即我们通常所说的字符串,是按字符在字符集中先后次序进行比较和排序的。而二进制字符串因为与字符集无关,所以不以字符顺序排序,而是以字节的二进制值作为比较和排序的依据。下面介绍两种字符串的比较方式:

  • 二进制字符串的比较方式是一个字节一个字节进行的,比较的依据是两个字节的二进制值。也就是说它是区分大小写的,因为同一个字母的大小写的数值编码是不一样的。

  • 非二进制字符串的比较方式是一个字符一个字符进行的,比较的依据是两个字符在字符集中的先后顺序。在大多数字符集中,同一个字母的大小写往往有着相同的先后顺序,所以它不区分大小写。

二进制字符串与字符集无关,所以无论按字符计算还是按字节计算,二进制字符串的长度都是一样的。所以VARCHAR(20)并不表示它最多能容纳20个字符,而是表示它最多只能容纳可以用20个字节表示出来的字符。对于单字节字符集,每个字符只占用一个字节,所以这两者的长度是一样的,但对于多字节字符集,它能容纳的字符个数肯定少于20个。

1.2.2.1. CHAR和VARCHAR

CHAR和VARCHAR是最常用的两种字符串类型,它们之间的区别是:

  • CHAR是固定长度的,每个值占用相同的字节,不够的位数MySQL会在它的右边用空格字符补足。

  • VARCHAR是一种可变长度的类型,每个值占用其刚好的字节数再加上一个用来记录其长度的字节即L+1字节。

CHAR(0)和VARCHAR(0)都是合法的。VARCHAR(0)是从MySQL4.0.2版开始的。它们的作用是作为占位符或用来表示各种on/off开关值。

如何选择CHAR和VARCHAR,这里给出两个原则:

  • 如果数据都有相同的长度,选用VARCHAR会多占用空间,因为有一位用来存储其长度。如果数据长短不一,选用VARCHAR能节省存储空间。而CHAR不论字符长短都需占用相同的空间,即使是空值也不例外。

  • 如果长度出入不大,而且是使用MyISAM或ISAM类型的表,则用CHAR会比VARCHAR好,因为MyISAM和ISAM类型的表对处理固定长度的行的效率高。

在一个数据表里,只要有一个数据列的长度是可变的,则所有数据列的长度将是可变的。MySQL会进行自动地转换。一个例外,CHAR长度小于4的不会进行自动转换,因为MySQL会认为这样做没必要,节省不了多少空间。反而MySQL会把大量长度小的VARCHAR转换成CHAR,以减少空间占用量。

1.2.2.2. BLOB和TEXT

BLOB是二进制字符串,TEXT是非二进制字符串。两者都可存放大容量的信息。

有关BLOB和TEXT索引的建立:

  • BDB表类型和MySQL3.23.2以上版本的MyISAM表类型允许在BLOB和TEXT数据列上建立索引。

  • ISAM、HEAP和InnoDB表不支持大对象列的索引。

使用BLOB和TEXT应注意的问题:

  • 由于这两个列类型所存储的数据量大,所以删除和修改操作容易在数据表里产生大量的碎片,需定期运行OPTIMIZE TABLE以减少碎片和提高性能。

  • 如果使用的值非常巨大,就需对服务器进行相应的优化调整,增加max_allowed_packet参数的值。对那些可会用到变些巨大数据的客户程序,也需加大它们的数据包大小。

1.2.2.3. ENUM和SET

ENUM和SET都是比较特殊的字符串数据列类型,它们的取值范围是一个预先定义好的列表。ENUM或SET数据列的取值只能从这个列表中进行选择。ENUM和SET的主要区别是:

  • ENUM只能取单值,它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。例如:ENUM("N","Y")表示,该数据列的取值要么是"Y",要么就是"N"。

  • SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。

ENUM和SET的值是以字符串形式出现的,但在内部,MySQL以数值的形式存储它们。

  • ENUM的合法取值列表中的字符串被按声明定义的顺序被编号,从1开始。

  • SET 的编号不是按顺序进行编号的,SET中每一个合法取值都对应着SET值里的一个位。第一个合法取值对应0位,第二个合法取值对应1位,以此类推,如果数值形式的SET值等于0,则说明它是一个空字符串,如果某个合法的取值出现在SET数据列里,与之对应的位就会被置位;如果某个合法的取值没有出现在SET 数据列里,与之对应的位就会被清零。正因为SET值与位有这样的对应关系,所以SET数据列的多个合法取值才能同时出现并构成SET值。

1.2.2.4. 字符串类型数据列的字符集属性

在MySQL 4.1以前的版本,字符串数据列的字符集由服务器的字符决定,MySQL 4.1版以后的版本可对每个字符串数据列指定不同的字符串。如果按默认方式设置,可按数据列、数据表、数据库、服务器的顺序关联字符串的字符集,直到找一个明确定义的字符集。

1.2.3. 日期,时间型数据列类型

MySQL的日期时间类型有:DATE,DATETIME,TIME,TIMESTAMP和YEAR,下表是这些类型的取值范围和存储空间要求:

Table 1.4. 日期,时间类型列

类型 取值范围 存储空间 零值表示法
DATE 1000-01-01~9999-12-31 3字节(MySQL3.23版以前是4字节 ) 0000-00-00
TIME -838:59:59~838:59:59 3字节 00:00:00
DATETIME 1000-01-01 00:00:00~9999-12-31 23:59:59 8字节 0000-00-00 00:00:00
TIMESTAMP 19700101000000~2037年的某个时刻 4字节 00000000000000
YEAR YEAR(4):1901~2155 YEAR(2):1970~2069 1字节 0000
MySQL总是把日期和日期里的年份放在最前面,按年月日的顺序显示。

1.2.3.1. DATE、TIME、DATATIME数据列类型

DATE、TIME和DATATIME类型分别存放日期值、时间值、日期和时间值的组合。它们的格式分别是“CCYY-MM-DD”、“hh:mm:ss”、“CCYY-MM-DD hh:mm:ss”。

DATATIME里的时间值和TIME值是有区别的,DATATIME里的时间值代表的是几点几分,TIME值代表的是所花费的时间。当向TIME数据列插值时,需用时间的完整写法,如12分30秒要写成“00:12:30”。

1.2.3.2. TIMESTAMP数据列类型

TIMESTAMP数据列的格式是CCYYMMDDhhmmss,取值范围从19700101000000开始,即1970年1月1号,最大到2037年。它的特点是能把数据行的创建或修改时间记录下来:

  • 如果把一个NULL值插入TIMESTAMP列,这个数据列就将自动取值为当前的日期和时间。

  • 在创建和修改数据行时,如果没有明确对TIMESTAMP数据列进行赋值,则它就会自动取值为当前的日期和时间。如果行中有多个TIMESTAMP列,只有第一个会自动取值。

  • 如果对TIMESTAMP设置一个确定的日期和时间值,则会使TIMESTAMP的自动取值功能失效。

TIMESTAMP默认的列宽是14,可指定列宽,以改变显示效果。但不论你指定的列宽如何,MySQL都是以4字节来存储TIMESTAMP值,也总是以14位精度来计算。

如果需要把创建时间和最近一次修改时间同时记录下来,可以用两个时间戳来记录,一个记录创建时间,一个记录修改时间。不过需记住两件事,一是要把记录修改时间的TIMESTAMP数据列放在最前面,这样才会自动取值;二是创建一条新记录时,要用now()函数来初始化创建时间TIMESTAMP数据列,这样,该TIMESTAMP数据列就不会再变化。

1.2.3.3. YEAR

YEAR 是一种单字节的数据列类型,YEAR(4)的取值范围是1901~2155,YEAR(2)的取值范围是1970~2069,但只显示最后两位数。 MySQL能自动把两位数字年份转换成四位数字的年份,如97和14分被转换成1997和2014。转换规则是这样的:

  • 年份值00~69将被转换成2000~2069;

  • 年份值70~99将被转换成1970~1999。

00被转换成0000,而不是2000。因为数值00也就是0,而0值是YEAR的一个合法取值。

1.3. 唯一编号

在数据库应用,我们经常要用到唯一编号,以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。MySQL支持多种数据表,每种数据表的自增属性都有差异,这里将介绍各种数据表里的数据列自增属性。

  • ISAM表

    • 如果把一个NULL插入到一个AUTO_INCREMENT数据列里去,MySQL将自动生成下一个序列编号。编号从1开始,并1为基数递增。

    • 把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做,还是以插入NULL值为好。

    • 当插入记录时,没有为AUTO_INCREMENT明确指定值,则等同插入NULL值。

    • 当插入记录时,如果为AUTO_INCREMENT数据列明确指定了一个数值,则会出现两种情况,情况一,如果插入的值与已有的编号重复,则会出现出错信息,因为AUTO_INCREMENT数据列的值必须是唯一的;情况二,如果插入的值大于已编号的值,则会把该插入到数据列中,并使在下一个编号将从这个新值开始递增。也就是说,可以跳过一些编号。

    • 如果自增序列的最大值被删除了,则在插入新记录时,该值被重用。

    • 如果用UPDATE命令更新自增列,如果列值与已有的值重复,则会出错。如果大于已有值,则下一个编号从该值开始递增。

    • 如果用replace命令基于AUTO_INCREMENT数据列里的值来修改数据表里的现有记录,即AUTO_INCREMENT数据列出现在了 replace命令的where子句里,相应的AUTO_INCREMENT值将不会发生变化。但如果replace命令是通过其它的PRIMARY KEY OR UNIQUE索引来修改现有记录的(即AUTO_INCREMENT数据列没有出现在replace命令的where子句中),相应的 AUTO_INCREMENT值--如果设置其为NULL(如没有对它赋值)的话--就会发生变化。

    • last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值,则该函数返回0。

    其它数据表的自动编号机制都以ISAM表中的机制为基础。

  • MyISAM数据表

    • 删除最大编号的记录后,该编号不可重用。

    • 可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。

    • 可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。

    • 可使用复合索引在同一个数据表里创建多个相互独立的自增序列,具体做法是这样的:为数据表创建一个由多个数据列组成的PRIMARY KEY OR UNIQUE索引,并把AUTO_INCREMENT数据列包括在这个索引里作为它的最后一个数据列。这样,这个复合索引里,前面的那些数据列每构成一种独一无二的组合,最末尾的AUTO_INCREMENT数据列就会生成一个与该组合相对应的序列编号。

  • HEAP数据表

    • HEAP数据表从MySQL4.1开始才允许使用自增列。

    • 自增值可通过CREATE TABLE语句的 AUTO_INCREMENT=n选项来设置。

    • 可通过ALTER TABLE语句的AUTO_INCREMENT=n选项来修改自增始初值。

    • 编号不可重用。

    • HEAP数据表不支持在一个数据表中使用复合索引来生成多个互不干扰的序列编号。

  • BDB数据表

    • 不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。

    • 可重用编号。

    • 支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

  • InnDB数据表

    • 不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。

    • 不可重用编号。

    • 不支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

在使用AUTO_INCREMENT时,应注意以下几点:

  • AUTO_INCREMENT是数据列的一种属性,只适用于整数类型数据列。

  • 设置AUTO_INCREMENT属性的数据列应该是一个正数序列,所以应该把该数据列声明为UNSIGNED,这样序列的编号个可增加一倍。

  • AUTO_INCREMENT数据列必须有唯一索引,以避免序号重复。

  • AUTO_INCREMENT数据列必须具备NOT NULL属性。

  • AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束,如TINYINT数据列的最大编号是127,如加上UNSIGNED,则最大为255。一旦达到上限,AUTO_INCREMENT就会失效。

  • 当进行全表删除时,AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时:

    delete from table_name;
    or
    truncate table table_name

    这是因为进行全表操作时,MySQL实际是做了这样的优化操作:先把数据表里的所有数据和索引删除,然后重建数据表。如果想删除所有的数据行又想保留序列编号信息,可这样用一个带where的delete命令以抑制MySQL的优化:

    delete from table_name where 1;

    这将迫使MySQL为每个删除的数据行都做一次条件表达式的求值操作。

  • 强制MySQL不复用已经使用过的序列值的方法是:另外创建一个专门用来生成AUTO_INCREMENT序列的数据表,并做到永远不去删除该表的记录。当需要在主数据表里插入一条记录时,先在那个专门生成序号的表中插入一个NULL值以产生一个编号,然后,在往主数据表里插入数据时,利用LAST_INSERT_ID()函数取得这个编号,并把它赋值给主表的存放序列的数据列。如:

    insert into id set id = NULL;
    insert into main set main_id = LAST_INSERT_ID();
  • 可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。

  • 要重新排列现有的序列编号,最简单的方法是先删除该列,再重建该,MySQL会重新生连续的编号序列。

  • 在不用AUTO_INCREMENT的情况下生成序列,可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的 LAST_INSERT_ID(expr)去插入或修改一个数据列,紧接着又调用不带参数的LAST_INSERT_ID()函数,则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作:

    先创建一个只有一个数据行的数据表:
    create table seq_table (id int unsigned not null);
    insert into seq_table values (0);
    接着用以下操作检索出序列号:
    update seq_table set seq = LAST_INSERT_ID( seq + 1 );
    select LAST_INSERT_ID();
    通过修改seq+1中的常数值,可生成不同步长的序列,如seq+10可生成步长为10的序列。

    该方法可用于计数器,在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK,UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。

1.4. 字符集支持

MySQL4.1以前版本服务器只能使用单一字符集,从MySQL4.1版本开始,不仅服务器能够使用多种字符集,而且在服务器、数据库、数据表、数据列以及字符串常数多个级别上设置不同的字符集。

1.4.1. MySQL4.1以前版本

MySQL4.1 以前版本的字符集由服务器默认指定,默认值是编译系统时指定的字符集,该字符集也可通过在启动服务器时指定--default-character- set来修改。这种修改会对数据表的索引造成影响,因为索引的顺序是和字符集有关的,修改字符集会使这个已排序的顺序产生错误。要解决该问题,我们要用修改后的字符集的排序顺序重建表的索引。重建索引有以下几种方法:

  • 用mysqldump导出数据,再清除表里的内容,最后用导出文件重新导入。数据表的索引将在导入数时重建。该方法适用于所有数据表类型。

  • 删除索引,然后重建。用alter table命令或drop index和create index命令来完成。该方法也适用于所有数据表类型。但该方法需要我们了解重建索引的精确定义。

  • MyISAM 数据表的索引可以用myisamchk程序的--recover和--quick选项加上一个用来设定新字符集的--set-character-set 选项进行重建。还可以用mysqlcheck程序的--repair和--quick选项或者一个带QUICK选项的REPLACE TABLE语句来重建索引,这种方式较方便。

1.4.2. MySQL4.1以后版本

MySQL4.1以后的版本对字符集的支持好了很多,具有以下新增功能:

  • 支持服务器同时使用多种字符集。

  • 允许在服务器,数据库,数据表,数据列等多级别上设置不同的字符集。

    • 服务器的默认字符集在编译时选定,但可在启动服务器时用--default-character-set选项来更改。

    • 用ALTER DATABASE db_name DEFAULT CHARACTER SET charset来设置数据库字符集。 如果只有default参数,则使用服务器的字符集。

    • 用CREATE TABLE table_name(...) CHARACTER SET = charset设置数据表字符集。如果charset为default,则使用数据表所在数据库的字符集作为数据表的字符集。

    • 在数据列中,可用CHARACTER SET charset属性来设置数据列的字符集。charset不能是default,如果没有该属性,则默认使用数据表的字符集。允许设置字符集的数据列有char,varchar(不带binary属性)及TEXT类型。

    • 用_charset str转换字符串常数的字符集。如:_utf8 'mysql',_latinl 'oracle'。该方法只适用于括在引号内的字符串,其它十六进制常数 、字符串表达式等可用CONVERT()函数进行转换,如:SELECT CONVERT( str USING charset)。

  • 通过MySQL提供的函数可进行字符集转换和查询。

  • 新增的COLLATE操作符使我们可按某一种字符集的排序顺序来处理另一种字符集的数据。如:SELECT a from t ORDER BY a COLLATE utf-8;

  • 用SHOW CHARACTER SET命令可显示服务器支持的字符集列表。

  • 当服务器转换到另一种字符集时,会自动对索引进行重新排序。

  • 通过UTF-8和UCS2字符集提供了Unicode支持。

MySQL现在还不支持:1,在同一个字符串里混用不同字符集的字符;2,在同一个数据列里混用不同的字符集。

1.4.3. 各级字符集的查询方法

  • 服务器级

    SHOW CHARACTER SET;可查出可供使用的所有字符集。
    SHOW VARIABLES LIKE 'character_set';可查出服务器的默认字符集。
  • 可查出数据库级的字符集。

    SHOW CREATE DATABASE db_name;
  • 两条命令可查出数据表的字符集。

    SHOW CREATE TABLE table_name;
    SHOW TABLE STATUS LIKE 'table_name'
  • 以下几命令可查出数据列的字符集:

    DESCRIBE table_name;
    SHOW COLUMNS FROM table_name;
    SHOW CREATE TABLE table_name;
  • 用CHARSET()函数可确定特定字符串,字符串表达式或数据列值相关联的字符串的字符集。如:SELECT CHARSET(str)。

1.4.4. Unicode支持

MySQL提供两种字符集来支持Unicode。一个是UTF-8,一种可变长的编码格式,需用1至4个字节来表示一个字符;另一个是UCS2,该字符集中的每个字符需要用两个字节来表示。

1.5. 如何选择数据列类型?

选择正确的数据列类型能大大提高数据库的性能和使数据库具有高扩展性。在选择数据列类型时,请从以下几个方面考虑:

  • 存放到数据列中的数据类型。

  • 数据值的取值范围。

  • 考虑性能和处理效率。

    • 数值操作比字符操作快。

    • 小类型的处理速度比大类型快。

    • 不同数据表中固定长度类型和可变长度类型的处理效率是不同的。

      可变长度类型在经过删除和修改操作后容易产生碎片,降低系统性能,需定期运行OPTIMIZE TABLE命令以优化数据表。

      固定长度类型由于有固定的长度,所以容易确定每条记录的起始点,可加快数据表的修复速度。

      在MyISAM和ISAM表中使用固定长度类型数据列有助改善数据库性能。

      在InnoDB表中,固定长度和可变长度数据列类型都以相同方式存储,所以固定长度数据列类型并没有性能优势,反而由于可度长度数据列类型由于占用存储空间较少,所以处理速度会快些。

    • 可索引类型能加快数据的查询速度。

    • 明确指定数据列的NOT NULL属性可使MySQL在检索过程中不用去判断数据列是否是NULL,所以可加快处理速度。

  • 数据如何进行比较,是否区分大小写。

  • 是否要在数据列上建立索引。

1.6. 表达式操作符

Table 1.5. 算术操作符

操作符 语法 含义
+ a + b 相加
- a - b 相减
- - a 求负
* a * b 乘法
/ a / b 除法
% a % b 求余

Table 1.6. 逻辑操作符

操作符 语法 含义
AND 或 && a AND b 或 a && b 逻辑与,若两个操作数同时为真,则为真
OR 或 || a OR b 或 a || b 逻辑或,只要有一个操作数为真,则为真
XOR a XOR b 逻辑异或,若有且仅有一个操作数为真,则为真
NOT 或 ! NOT a 或 !a 逻辑非,若操作数为假,则为真

Table 1.7. 位操作符

操作符 语法 含义
& a & b 按位与,若操作数同位同为1,则该位为1
| a | b 按位或,若操作数同位有一位为1,则该位为1
^ a ^ b 按拉异或,若操作数同一位分别为1和0,则该位为1
<< a << b 把a中的各个位左移b个位置
>> a >> b 把a中的各个位右移b个位置

Table 1.8. 比较操作符

操作符 语法 含义
= a = b 若两个操作数相等,则为真
<=> a <=> b 若两个操作数相等,则为真,可用于NULL值比较
!= 或 <> a != b 或 a <> b 若两个操用数不等,则为真
< a < b 若a小于b,则为真
<= a <= b 若a小于或等于b,则为真
> a > b 若a大于b,则为真
>= a > b 若a大于或等于b,则为真
IN a IN (b1,b2,...) 若a等于b1,b2,...中的某一个,则为真
BETWEEN a BETWEEN b AND c 若a在b和c之间(包括b和c),则为真
NOT BETWEEN a NOT BETWEEN b AND c 若a不在b和c之间(包括b和c),则为真
LIKE a LIKE b SQL模式匹配,若a匹配b,则为真
NOT LIKE a NOT LIKE b SQL模式匹配,若a不匹配b,则为真
REGEXP a REGEXP b 正则表达式匹配,若a匹配b,则为真
NOT REGEXP a NOT REGEXP b 正则表达式匹配,若a不匹配b,则为真
IS NULL a IS NULL 若a为NULL,则为真
IS NOT NULL a IS NOT NULL 若a不为NULL,则为真
LIKE模式匹配中的“%”匹配任意个字符,“_”匹配一个字符。匹配不区分大小写字符。

Table 1.9. 操作符优先级(由高至低排列)

操作符
BINARY,COLLATE
NOT、!
^
XOR
-(一元求负操作符)、~(一元取反操作符)
*、/、%
+、-
<<、>>
&
|
<、<=、=、<=>、!=、<>、>=、>、IN、IS、LIKE、REGEXP、RLIKE
BETWEEN、CASE、WHEN、THEN、ELSE
AND、&&
OR、||
:=

1.7. 类型转换

在MySQL的表达式中,如果某个数据值的类型与上下文所要求的类型不相符,MySQL则会根据将要进行的操作自动地对数据值进行类型转换。如:

1 + '2'      会转换成1 + 2 = 3
1+ 'abc' 会转换成1 + 0 = 1 由于abc不能转换成任何的值,所以默认为0

MySQL会根据表达式上下文的要求,把字符串和数值自动转换为日期和时间值

对于超范围或非法的值,MySQL也会进行转换,但转换出来的结果是错误的。出现该情况时,MySQL会提示警告信息,我们可捕获该信息以进行相应的处理。

Chapter 2. 查询优化

数据库是数据的集合,与数学的集合论有密不可分的关系。

为提高查询速度,我们可以:

  • 对数据表添加索引,以加快搜索速度;

  • 通过编程技巧最大限度地利用索引;

  • 优化查询语句,以使服务器最快响应多客户的请求。

  • 研究硬件处理过程,减少物理约束。

2.1. 索引

索引技术是关系数据查询中最重要的技术。如果要加提升数据库的性能,索引优化是首先应该考虑的。因为它能使我们的数据库得到最大性能方面的提升。

索引的优点:

  • 没有索引的表是没有排序的数据集合,如果要查询数据需进行全表扫描。有索引的表是一个在索引列上排序了数据表,可通过索引快速定位记录。在MyISAM和 ISAM数据表中,数据行保存在数据文件中,索引保存在索引文件中。BDB与InnoDB数据表把数据与索引放在同一个文件中。

  • 在多表关联查询中,索引的作用就更大。如果没有索引,在最坏的情况下,全表扫描的次数可能是各表数据行的组合个数,可能是一个天文数字。这样的查询是破坏性的,可能会造成数据库瘫痪。

  • 对于使用了MIN()或是MAX()函数的查询,如果相关的数据列上有索引,MySQL能直接找到该最大、最小值的行,根本不用一个一个地去检查数据行。

  • 索引加快ORDER BY 和 GROUP BY子句的操作。

  • 当在数值型数据列上查询数据,而该列有索引,索引能使MySQL根本不用去读取数据行,直接从索引取值。

索引的缺点:

  • 索引需占用磁盘空间。

  • 索引会减慢在索引数据列上的插入、删除和修改操作。

索引列的选择

  • 索引应该创建在搜索、排序、分组等操作所涉及的数据列上。也就是说,在where子句,关联检索中的from子句、order by或group by子句中出现过的数据列最适合用来创建索引。

  • 尽量使用唯一索引,它能使索引发挥最好的效能。

  • 尽量用比较短的值进行索引。当对字符串进行索引时,应该指定一个前缀长度,比如对字符串的前10位或20位的字符进行排序,而不用把整个字符串几十个字符用来索引排序。这样能减少磁盘I/O,提高处理速度。最重要的一点是,键值越短,索引缓冲区里容纳的键值也就越多,而MySQL同时保存在内存里的索引越多,索引缓冲区的命中率也就越高。当然,只对数据列第一个字符进行索引是没什么意义的。

  • 充分利用最左前缀。所谓最左前缀也就是在复合索引中最边的索引列。如复合索引(a,b,c) ,其中a就是最左前缀。它是使用率最高的索引,需认真选择。

  • 不要建太多索引,索引是会消耗系统资源的,要适可而止。

  • 索引主要用于<、<=、=、>=、>、BETWEEN等的比较操作中,所以索引应该建立在与这样操作相关的数据列上。

  • 利用慢查询日志来找出性能差的查询,通过mysqldumpslow可查看该日志。针对性能差的查询可利用索引来加快查询速度。

2.2. 查询优化程序

当我们发一条查询命令时,MySQL分对它进行分析,以优化查询。把explain语名放到查询前面可显示查询的执行路线,对优化查询提供有用的信息。以下几个原则可帮助系统挑选和使用索引:

  • 尽量对同类型的数据列进行比较。如:VARCHAR(5)和VARCHAR(5)是同类型的,CHAR(5)和VARCHAR(5)是不同类型的。

  • 尽量让索引的数据列在比较表达式中单独出现,不要把它包含在函数或复杂表达式。否则索引会不起作用。

  • 尽量不要在LIKE模式的开头使用通配符。如:%string%。

  • 对于MyISAM和BDB数据表,用ANALYZE TABLE语句让服务器对索引键值的分布进行分析,为优化程序提供更有价值的信息。另一个方法是用myisamchk --analyze(适用于MyISAM表)或isamchk --analyze(适用于ISAM表)命令。

  • 用EXPLAIN语句来分析查询语句的执行效率。检查查询所使用的索引是不是能够迅速地排除不符合条件的数据行,如果不是,可以试着用STRAIGHT_JOIN强制各有关数据表按指定顺序进行关联。

  • 尝试查询的不同写法,比较运行情况。

  • 不要滥用MySQL的类型自动转换功能。自动转换会减慢查询的速度并会使有关的索引失效。

2.3. 数据列类型与查询效率

选用适当的数据列类型有助于提高查询命令的执行速度,下面是几点关于如何选择合适数据列类型的建议:

  • 尽量选用尺寸较小的数据列。这样能节约磁盘空间和加快查询速度。如果较短的数据列上建有索引,则索引的处理速度会进一步提高。

  • 针对数据列类型,尽量选择最适用的数据表类型。如固定长度数据列在MyISAM或ISAM数据表中的速度是最快的,所以在这样数据表中尽量使用char类型而不是varchar类型来保存字符串数据。对于InnoDB数据表类型,由于varchar类型可有效减少占用空间,从而减少磁盘I/O,所以使用 varchar类型是有利的。对于BDB类型数据表,使用定长和不定长列类型的区别就不大,可任选一种。

  • 尽量把数据列声明为NOT NULL,以节约存储空间和加快处理速度。

  • 对于取值范围有限的数据列,考虑使用ENUM数据列类型。ENUM数据列类型在MySQL中的处理速度是很快。

  • 使用PROCEDURE ANALYSE()语句来分析数据表,它会对数据列的声明提出建议,我们可根据建议进行修改。

    select * from table_name PROCEDURE ANALYSE();
    select * from table_name PROCEDURE ANALYSE(16,256); #(16,256)含义是:如果某列的不同取值在16个以上或长度超过256字节, 就不提出使用ENUM的建议。
  • 用OPTIMIZE TABLE语句对容易出现碎片的数据表进行整理。包含可变长数据列的数据表都会产生碎片,从而占用多余的磁盘空间和影响查询速度。所以要定期运行 OPTIMIZE TABLE语句以防止数据表查询性能降低。但该语句只对MyISAM数据表有效。对各种数据表通用的碎片整理方法是这样的:先用工具程序 mysqldump导出数据表,再删除数据表后重建,如:

    $ mysqldump --opt db_name table_name > dump.sql
    $ mysql db_name < dump.sql
  • 把非结构化和变化大的数据放在BLOB数据列里,定期用OPTIMIZE TABLE命令优化。

  • 人为地给数据表增加一个数据列,以充当索引。做法是这样的,先根据数据表里的其它数据列计算出一个散列值,并保存在一个数据列里,然后通过搜索散列值来检索数据行。注意,该技巧只适用于精确匹配型查询。散列值在大于,小于等的操作中不起作用。散列值可以MD5()(适用于3.23及以上版本),SHA1() (适用于4.0.1及以上版本),CRC32()(适用于4.1及以上版本)等函数生成。使用散列值支检索BLOB和TEXT值的做法比直接检索BLOB 和TEXT本身的做法快。

  • 尽量避免对大尺寸的BLOB值进行检索。如果要检索都应该通过它的上面提到散列值先进行筛选。而不应该盲目地在网络中传送大量BLOB值。

  • 如果把BLOB值剥离到另外一个数据表里去,可实现数据表中其它数据列转变成固定长度数据列的话。就即可减少数据表碎片,又可使在原始表中的select *查询不会把大尺寸的BLOB值不必要地通过网络传送。

2.4. 有效地加载数据

有时我们需大量地把数据加载到数据表,采用批量加载的方式比一个一个记录加载效率高,因为MySQL不用每加载一条记录就刷新一次索引。下面介绍几个有助于加快数据加载的操作:

  • 使用LOAD DATA语句要比INSERT语句的加载速度快。

  • LOAD DATA比LOAD DATA LOCAL语句的效率高。前者可由服务器直接从本地磁盘读取加载数据,后者需由客户程序去读取文件并通过网络传送到服务器。

  • 如果一定要用INSERT语句,应尽量在一条语句中插入多个数据行。

  • 如果必须使用多条INSERT语句,则应尽量把它们集中在一起放到一个事务中进行处理,而不是在自动提交模式下执行它们:如:

    BEGIN;
    INSERT INTO table_name values (...);
    INSERT INTO table_name values (...);
    INSERT INTO table_name values (...);
    ...
    COMMIT;

    对于不支持事务的表,应对表进行写锁定,然后在表锁定期间对表进行INSERT操作,如:

    LOCK TABLES table_name WRITE;
    INSERT INTO table_name ...;
    INSERT INTO table_name ...;
    INSERT INTO table_name ...;
    ...
    UNLOCK TABLES;
  • 利用客户/服务器通信协议中的压缩功能以减少网络传输的数据量。但该压缩会消耗大量的系统资源,所以小心使用。

  • 尽量让MySQL插入默认值。不要在INSERT中写太多值,以减少网络传输量和服务器端的语法分析时间。

  • 对于MyISAM和ISAM数据表,如果需加载大量数据,应先建立一个没索引的表,加载数据后再创建索引。该方法不适用于InnoDB或BDB数据表。

禁用和重新激活索引的方法有两种:

  • 使用ALTER TABLE语句的DISABLE KEYS和ENABLE KEYS命令,如:

    ALTER TABLE table_name DISABLE KEYS;
    ALTER TABLE table_name ENABLE KEYS;
  • 使用myisamchk或isamchk工具。如:

    $ myisamchk --keys-used=0 table_name                   #禁止
    $ myisamchk --recover --quick --key-used=n table_name #激活
    n是用来表明需要激活索引的位掩码,第0位对应第一个索引,如果有三个索引, n值就是7(二进制111)。 索引编号可以下命令确定:
    $ myisamchk --description table_name

2.5. 调度和锁定

在很多客户一起查询数据表时,如果使客户能最快地查询到数据就是调度和锁定做的工作了。在MySQL中,我们把select操作叫做读,把对数据表修改增加的操作(INSERT,UPDATE,REPLACE...)叫做写。MySQL的基本调度策略可以归纳为以下两条:

  • 写入请求将按它们到达服务器的顺序进行处理;

  • 写操作的优先级要高于读操作。

MyISAM 和ISAM数据表的调度策略是在数据表锁的帮助下实现的,在客户程序要访问数据表之前,需获得相应的锁,在完成对数据表的操作后,再释放该锁。锁的管理通常由服务器管理,也可人为地用LOCK TABLES和UNLOCK TABLES命令来申请和释放锁。写操作时,需要申请一个独占性的锁,也就是说在写操作其间,该表只能由写操作的客户使用。读操作时,客户必须申请一个允许其他客户对数据表进行写操作的锁,以确保客户在读的过程中数据表不会发生改变。但读操作锁不是独占的,可有多个读操作同时作用于同一个数据表。

通过一些修饰符可影响调度策略,如LOW_PRIORITY(用于DELETE,INSERT,LOAD DATA,REPLACE,UPDATE语句)、HIGH_PRIORITY(用于SELECT语句)、DELAYED(用于INSERT和 REPLACE语句)。它们的作用是这样的:

  • LOW_PRIORITY会使写操作的优先级降低到读操作以下,也就是说读操作会阻塞该级别的写操作,SELECT的HIGH_PRIORITY有类似的作用。

  • INSERT 语句中的DELAYED修饰会使插入操作被放入一个“延迟插入”队列。并返回状态信息给客户,使客户程序可在新数据行还没插入到数据表之前继续执行后面的操作。如果一直有客户读该数据表,新数据行会一直待在队列中,直到数据表没有读操作时,服务器才会把队列中的数据行真正插入到数据表中。该语句可用在以下场合,在一个有冗长查询的数据表中插入数据,而你又不想被阻塞,你就可发出INSERT DELAYED语句,把插入操作放入服务器“延迟插入”队列,你无需等待就马上可进行接下来的操作。

  • 当一个数据表里从未进行过删除操作或刚刚对它进行过碎片整理的情况下,用INSERT语句插入的数据行只会被添加到数据表的末尾,而不会插入到数据表的中间位置。这样,对于 MyISAM表,MySQL允许在有其它客户正在读操作的时间进行写操作。我们称之这并发插入。要使用该技巧,需注意以下两个问题:

    • 不要在INSERT语句中使用LOW_PRIORITY修饰符。

    • 读操作应用LOCK TABLES ... READ LOCAL而不是用LOCK TABLES ... READ语句来进行数据表读锁定。LOCAL关键字只对数据表中已存在行进行锁定,不会阻塞把新行添加到数据表末尾。

BDB数据表使用页面级操作锁,InnoDB数据表使用数据行级操作锁。所以这两种表的并发性比MyISAM和ISAM数据表这种表级锁的并发性会好很多。其中InnoDB的并发性最好。综上所述,我们可得出以下结论:

  • MyISAM和ISAM数据表的检索速度最快,但如果在检索和修改操作较多的场合,会出锁竞争的问题,造成等待时间延长。

  • BDB和InnoDB数据表能在有大量修改操作的环境下提供很好的并发性,从而提供更好的性能。

  • MyISAM和ISAM数据表由于进行表级锁定,所以不会出现死锁现象,BDB和InnoDB数据表则存在死锁的可能性。

2.6. 服务器优化

优化原则:

  • 内存里的数据要比磁盘上的数据访问起来快;

  • 站数据尽可能长时间地留在内存里能减少磁盘读写活动的工作量;

  • 让索引信息留在内存里要比让数据记录的内容留在内存里更重要。

针对以上几个原则,我们应该调整服务器:

  • 增加服务器的缓存区容量,以便数据在内存在停留的时间长一点,以减少磁盘I/0。下面介绍几个重要的缓冲区:

    • 数据表缓冲区存放着与打开的数据表相的信息,它的大小可由服务器参数“table_cache”设置。Opened_tables参数记录服务器进行过多少次数据表打开操作,如果该值变化很大,就可能是数据表缓冲区已满,需把一些不常用的表移出缓冲区,以腾出空打开新的数据表。可用以下命令查看 Opened_tables的值:

      SHOW STATUS LIKE 'Opened_tables';
    • 在MyISAM和ISAM数据表中,索引被缓存在“key buffer”里,它的大小由服务器参数“key_buffer_size”来控制。系统默认的大小是8M,如果内存充足的话可适当扩大该值,以使更多索引块缓存在该区里,以加快索引的速度。

    • InnoDB和BDB数据表也各有一个缓冲区,分别叫innodb_buffer_pool_size和bdb_cache_size。InnoDB还有一个日志缓冲区叫innodb_log_buffer_size。

    • 自4.0.1 开始,MySQL多了一个缓冲区,叫查询缓冲区,主要用来存放重复执行的查询文本和结果,当再次遇到相同的查询,服务器会直接从缓冲区中返回结果。该功能是内建的功能,如不想支持该功能,可在编译服务器时用configure脚本的--without-query-cache选项去掉该功能。

      查询缓冲区由三个服务器参数控制,分别是:

      1、query_cache_size    
      控制缓冲区的大小,如果该值为0,则禁用查询缓冲功能。设置方法是在选项文件中设置:
      [mysqld]
      set-variable = query_cache_size = 16M
      这样就设置了一个16M的查询缓冲区

      2、query_cache_limit
      缓冲结果集的最大容量(以字节为单位),如果查询的结果集大于该值,则不缓冲该值。

      3、query_cache_type
      缓冲区的操作模式。
      0表示不进行缓冲;
      1表示除SELECT SQL_NO_CACHE开头的查询外,其余的都缓冲;
      2表示只对以SELECT SQL_ON_CACHE开头的查询进行缓冲。

      默认情况下,按服务器的设置进行缓冲,但客户端也可通过命令改变服务器设置。客户端可直接用SELECT SQL_NO_CACHE和SELECT SQL_CACHE命令来要求服务器缓冲或不缓冲查询结果。如果不想每条查询都写参数,我们也可在客户端用SET SQL_QUERY_CACHE_TYPE = val;来改变服务器的查询缓冲行为。val可取值0,1,2或OFF,ON,或DEMAND。

  • 禁用用不着的数据表处理程序。如服务器是从源码创建,就可彻底禁用ISAM,InnoDB和BDB数据表。

  • 权限表里的权限关系应尽可能简单,当然了,是要在保证安全的前提下。

  • 在从源码创建服务器时,尽量使用静态库而不是共享库来完成其配置工作。静态库的执行速度更快,但如果要加载用户定义函数(UDF)的话,就不能使用静态库,因为UDF机制必须依赖动态库才能实现。

2.7. 硬件优化

为了提高数据运行速度,升级硬件是最直接的解决方案。针对数据库应用的特点,在升级硬件时应考虑以下内容:

  • 对于数据库服务器,内存是最重要的一个影响性能因素。通过加大内存,数据库服务器可把更多的数据保存在缓冲区,可大大减少磁盘I/O,从而提升数据库的整体性能。

  • 配置高速磁盘系统,以减少读盘的等待时间,提高响应速度。

  • 合理分布磁盘I/O,应把磁盘I/O分散在多个设备上,以减少资源竞争,提高并行操作能力。

  • 配置多处理器,MySQL是多线程的数据库,多处理器可同时执行多个线程。

Chapter 3. 数据库管理

数据库是一个复杂而又关键的系统,为确保系统安全、高效运行,需熟悉数据库内部的运作机制,掌握各种维护工具,并做好日常的管理工作。下面列举几项主要工作职责:

  • 服务器的关闭和启动;

  • 管理用户帐号;

  • 管理日志文件;

  • 数据库备份恢复;

  • 数据库优化;

  • 确保数据库数据安全;

  • 数据库软件升级。

3.1. 数据目录

数据目录是用来存放数据表和相关信息的地方,是数据库的核心。在MySQL中的数据目录根据不同平台的有一些差异:

  • 在UNIX/Linux系统上,如果用源码编译安装,数据目录的位置默认是在/usr/local/mysql/var中;

  • 在UNIX/Linux系统上,如果用二进制发行版安装,数据目录的位置默认是在/usr/local/mysql/data中;

  • 在WINDOWS系统上,数据目录的位置默认是在c:/mysql/data中;

在服务器启动时,可用--datadir=dir_name来指定数据目录,也可把它写到配置文件中。

我们还可用命令向服务器查询数据目录的位置,数据目录的变量名是datadir,如:

  • % mysqladmin variables

    如果在一台机器上同时运行多个服务器,则可根据端口的不时来查询每个服务器的数据目录,如:

    % mysqladmin --host=127.0.0.1 --port=port_number variables

    如果--host是localhost,系统则会用一个UNIX套接字去连接数据库服务器,这时要使用--socket选项,所以查询语句变成:

    % mysqladmin --host=localhost --socket=/path/to/socket variables
  • mysql> SHOW VARIABLES LIKE 'datadir';
  • 在windows NT平台上可以使用“.”作为一条命名管道连接的主机名,用--socket选项给出命名管道的名字,如:

    c:\ mysqladmin --host=. --socket=pipe_name variables
  • 配置文件的中[mysqld]段中的datadir=/path/to/datadir设置也可查询到数据目录。

  • 在mysqld程序的帮助信息里也有程序编译时默认的数据目录信息,可用以下命令显示:

    % mysqld --help

数据目录是存放数据文件的地方,每个数据库对应目录的不同文件。InnoDB数据表由于用表空间来管理数据库,所以就没这种对应关系。但也是保存在数据目录中的,在数据目录除保存数据库文件外,还可能会保存以下几类文件:

  • 服务器的配置文件,my.cnf;

  • 服务器的进程ID(PID)文件;

  • 服务器的日志文件和状态文件,这些文件对管理数据库有重要的价值;

  • DES密钥文件或服务器的SSL证书与密钥文件。

数据目录中的所有数据库全部由服务器(mysqld)来管理,客户端不直接操作数据。服务器是客户使用数据的唯一通道。

在MySQL 中,每个数据库其实就是在数据目录下一个子目录,show databases命令相当于列出数据目录中的目录清单。create database db_name命令会在数据目录下新建一个db_name的目录,以存放数据库的数据文件。所以我们也可下面的shell命令方式来建立一个空数据库:

% cd datadir
% mkdir db_name
% chmod u=rwx,go-rwx db_name

同理,删除数据库drop database db_name也就是删除数据目录中一个名为db_name的目录及目录中的数据表文件。我们也可用shell这进行操作:

% cd datadir
% rm -rf db_name
比较shell方式与drop database方式,drop database db_name命令不能删除db_name目录中创建的其它非数据表文件;由于InnoDB是表空间来管理数据表,所以不能用rm或del命令删除InnoDB的数据表。

3.2. MySQL数据表在系统中表现形式

MySQL数据表类型有:ISAM、MyISAM、MERGE、BDB、InnoDB和HEAP。每种数据表在文件系统中都有不同的表示方式,有一个共同点就是每种数据表至少有一个存放数据表结构定义的.frm文件。下面介绍每种数据表文件:

  • ISAM数据表是最原始的数据表,有三个文件,分别是:

    .frm,存放数据表的结构定义;

    .ISD,数据文件,存放数据表中的各个数据行的内空;

    .ISM,索引文件,存放数据表的所有索引信息。

  • MyISAM数据表是ISAM数据表的继承者,也有三个文件,分别是:

    .frm,结构定义文件;

    .MYD,数据文件;

    .MYI,索引文件。

  • MERGE数据表是一个逻辑结构,代表一组结构完全相同的MyISAM数据表构成的集合。它在文件系统中有二个文件,分别是:

    .frm,结构定义文件;

    .MRG,构成MERGE表的MyISAM数据表清单,每个MyISAM数据表名占一行。也就是说可通过改变该表的内容来改变MERGE数据表的结构。修改前请先刷新缓存(flush tables),但不建议这样修改MERGE数据表。

  • BDB数据表用两个文件来表示,分别是:

    .frm,结构定义文件;

    .db,数据表数据和索引文件

  • InnoDB由于采用表空间的概念来管理数据表,所以它只有一个与数据表对应.frm文件,同一目录下的其它文件表示为表空间,存储数据表的数据和索引。

  • HEAP数据表是一个存在于内存中的表,所以它的数据和索引都存在于内存中,文件系统中只有一个.frm文件,以定义结构。

了解MySQL数据表在文件系统中表现形式后,我们可知道,创建、修改或删除数据表,其实就是对这些文件进行操作。例如一些数据表(除InnoDB和HEAP数据表外),我们可直接在文件系统中删除相应的文件来删除数据表。

% cd datadir
% rm -f mydb/mydb.*

以上命令可删除mydb数据库中的mydb数据表。

3.3. 数据表最大尺寸限制

在MySQL中影响数据表尺寸的因素有很多,下面分别进行介绍:

  • MySQL数据表类型的不同对数据表尺寸的限制:

    • ISAM数据表中单个.ISD和.ISM文件的最大尺寸为4G;

    • MyISAM数据表中单个.MYD和.MYI文件的默认最大尺寸也是4G,但可在创建数据表时用AVG_ROW_LENGTH和MAX_ROWS选项把这个最值扩大到800万TB。

    • MERGE数据表的最大尺寸是它的各组成MyISAM数据表的最大尺寸之和。

    • BDB数据表的尺寸受限于BDB处理程序所允许的.db文件的最大尺寸。这个最大尺寸随着数据表页面尺寸(编译时确定)而变化,但即使是最小的页面尺寸(512字节),.db文件的最大尺寸也可达2TB。

    • InnoDB数据表的表空间的最大尺寸是40亿个页面,默认的页面尺寸是16K,该值可在8K到64K之间,在编译时确定。InnoDB数据表的最大尺寸也就是表空间的最大尺寸。

  • 操作系统对文件的尺寸限制,一般文件系统都对单个文件不得超过2G的限制。该约束会对数据库文件造成限制。InnoDB数据表可通过利用未格式化硬盘作为表空间来绕过该限制。

  • 对于数据和索引分开两个文件存放的数据表,其中任何一个文件达到操作系统文件的最大限制,数据库表也就达到最大尺寸。

  • 包含AUTO_INCREMENT数据列的表受到该数据列类型最大上限值的限制。

  • 由于InnoDB数据表用表空间来管理,一个表空间可同时空纳多个数据表,所以数据表的最大尺寸受系统文件和同一表空间中数据表空间的约束。

3.4. 状态文件和日志文件

在MySQL数据目录中还包含着许多状态文件和日志文件,这些文件的文件名都是以主机名加上相关后缀来命名的。下面是这些文件的一个说明列表:

Table 3.1. 状态文件和日志文件

文件类型 默认名 文件内容
进程ID文件 hostname.pid MySQL服务器进程的ID
常规查询日志 hostname.log 连接/断开连接事件和查询信息
慢查询日志 hostname-slow.log 记录查询时间很长的命令信息
变更日志 hostname.nnn 创建或修改数据表结构和内容的查询命令信息
二进制变更日志 hostname-bin.nnn 创建或修改数据表结构和内容的查询命令的二进制表示法
二进制变更日志的索引文件 hostname-bin.index 使用中的“二进制变更日志”列表
错误日志 hostname.err 记录“启动/关闭”事件和异常情况

变更日志和二进制变更日志主要用于MySQL数据库服务器的崩溃恢复中,由于变更日志记录了数据库的所有变更操作,所以可以进行事件重放。具体操作请参考相关数据库备份恢复章节。对于变更日志,我们可用--log-long-format选项来让它以扩展方式记录有关事件。扩展方式可记录谁发出查询和什么时候发出查询的信息。可使我们更好地掌握客户端的操作情况。日志记录着查询命令的所有操作,里面可能会有一些敏感信息。所以我们要确保日志文件的安全。

3.5. 调整MySQL数据目录位置

MySQL数据库的数据目录位置,包括目录里的各种文件的位置)可根据实际情况进行调整。调整的方法有两种,一种是使用符号链接;一种用服务器启动选项。下面一个列表说明了数据目录及目录中文件各自适宜采用的方法:

Table 3.2. MySQL数据目录及目录中文件位置的调整方法

调整对象 适用方法
整个数据目录 启动选项和符号链接
数据库目录 符号链接
数据表 符号链接
InnoDB数据表空间 启动选项
PID文件 启动选项
日志文件 启动选项

下面是各种调整方法的具体操作过程:

  • 在调整MySQL的数据目录时,要先停止服务器,再把数据目录移动到新的位置。接着,我们可选择在原来目录下创建一个符号链接指向新的位置,或者用启动选择 --datadir指向新的数据目录。推荐用创建符号链接的方法,因为如果那个数据目录中有my.cnf文件,相应的服务器还能找到它。

  • 数据库只能存在于MySQL数据目录中,所以只能使用符号链接的方法调整它的位置。在Linux系统的操作步骤如:

    1. 关闭服务器;

    2. 把数据库目录拷贝到新的位置;

    3. 删除原来的数据库目录;

    4. 在原来的MySQL数据目录中创建一个同名符号链接指向新的位置;

    5. 重新启动服务器。

    在windows下的操作方法不些不同,操作方法如下:

    1. 关闭服务器;

    2. 把数据库目录移动新的位置;

    3. 删除原来的数据库目录;

    4. 在原来数据目录下建一个同名的.sym文件,在文件中输入数据库新目录的全路径,如c:\mysql\newdir\mydb。这个文件就相当于Linux下的符号链接;

    5. 重启服务器。

      为了支持符号链接功能,必须用--use-symbolic-links选项启动服务器;或在选项文件的[mysqld]节中添加use-symbolic-links选项。
      MySQL必须是3.23.16以上版本且是max服务器(mysqld-max或mysqld-max-nt)。
  • 要移动数据表,必须满足以下所有条件才行:

    • MySQL的版本必须是4.0或以上的版本;

    • 操作系统必须有一个可用的realpath()调用;

    • 移动的数据表必须是MyISAM类型的数据表。

    在满足以上所有条件后,我们就可把.MYD数据文件和MYI索引文件移到新位置,再在原来位置创建两个同名符号链接指定新的位置。注意,.frm定义文件仍需留在原来的数据库目录中。

    如以上条件不能全部满足,最好不要移动数据表文件。否则一旦你运行ALTER TABLE、OPTIMIZE TABLE、REPAIR TABLE语句对移动过的数据表进行优化或修改,这样数据表就会回到原来的位置,使移动操作失效。因为这些命令的执行过程是这样的:它会先在数据目录中创建一个临时数据表并对这个临时数据表进行优化或修改,然后删除原来的数据表(这里是你为了移动数据表而创建的一个符号链接),再把临时数据表更名为原来的数据表名称。这样一来,你移走的数据表就和这个数据库完全没有关系了。基于以下的不稳定因素,如无特殊必要,不建议移动数据表。

  • InnoDB表空间是通过在选项文件中使用innodb_data_home_dir和innodb_data_file_path选项列出InnoDB表空间组成文件清单的方法来配置的,所以我们可通过修改这些选项来重新安置InnoDB表空间的组成文件。步骤如下:

    • 关闭服务器;

    • 移动组成表空间的文件;

    • 修改选项文件,指出组成表空间的文件的新位置;

    • 重启服务器。

  • 状态文件和日志文件的位置可通过选项文件或启动服务器时指定。

 

推荐阅读

 

热点信息

 
强悍的草根IT技术社区,这里应该有您想要的! 友情链接:b2b电子商务
Copyright © 2010 Gimoo.Net. All Rights Rreserved  京ICP备05050695号